Home › Forums › Power Pivot › Star schema vs link table to dimensions tables
Tagged: Correct schema
This topic contains 3 replies, has 3 voices, and was last updated by tomallan 7 years, 5 months ago.
-
AuthorPosts
-
December 2, 2016 at 10:29 am #6892
Good day
I have the following predicament I have 2 fact tables and a bunch of dimension tables, fact table 1 has a column called “site type” but fact table 2 does not have this column.
When i combine the two reports into the site master table by site type fact table 1 does not have any connection to this column, so the data cannot read it.
Is it better to include the site type into both fact tables or should i create a linked table with site type in it and then link all the dimension tables to the linked table?
Please see attachment
Attachments:
You must be logged in to view attached files.December 2, 2016 at 10:40 am #6894If you want to compare the measures in the facts than you need a shared dimension (or conformed dimension). So normally you have a date dimension, customer dimension, etc. These can be shared between the facts. In dimensional modeling every describing attribute should be in the dimension. When the number of different records are small I sometimes leave them in the fact. I’ve read some where that this can increase performance instead of numerous small dimension tables.
As it seems in your xls second tab you want to create a kind of a snowflake star schema? and related only via the site article dimension table. Seems not correct ot me but may I don’t understand the diagram. So I would suggest trying to create a conformed dimension between the two facts and see whether how it performs. If not try to add it as a field in the fact. not sure whether the latter is working. Never done this like this…
Good luck!
Hennie
December 2, 2016 at 11:27 am #6896So in other words is the star scheme the only schema you should use or are there other schema layouts one can you to improve performance?
Yes my site article combination “dimension table” has the “site type column” where my one fact table only had the it. Now im taking our the “unique column” and placing it in my dimension table to enable both fact tables to read the unique column.
hope this makes sense
December 2, 2016 at 5:48 pm #6901rt,
My two cents of advice is to include the site column in both fact tables (assuming that it applies to both fact tables).
The star schema approach is also preferred. My opinion says the other approach is will not be a good design.
-
AuthorPosts
The forum ‘Power Pivot’ is closed to new topics and replies.