August 20, 2016 at 6:30 am
Hi advice please?
1. Is it better to load the fact table from the dimension tables, so populate the dimensions with all records from DW and load fact with same records from those dimensions using joins.
2.Or is it better to have the dimensions only contain the main attributes (not actual records) in a list format, so say 10 rows for customer dimension and 20 rows for products in product dimension, then load the fact table from the DW and not the dimension(s).
From what I can see both ways will work although point 1 would mean adding all the records to the dimensions first then select across to the fact table which would be overkill??
And point 2 would be in effect just lists of unique customers, products...with a business key in each to point back to the fact.
Please can someone enlighten me on this one?
Thanks
Sandman
August 20, 2016 at 10:04 am
Not sure I understand your question. Are you talking about processing an SSAS cube, or loading actual DW tables?
Fact and Dimension tables are very different in terms of design. Dimension tables contain attributes (name, address etc.) of entities (customers, products etc.) while Fact tables contain the measurable facts (i.e. anything you can aggregate) and references (surrogate keys) to the relevant Dimension tables.
That being said, I am not sure what you mean because you cannot load one from the other as they contain different types of information...related, but different.
I'd encourage you to read up more about the basic concepts of dimensional modeling. If I am misinterpreting what you're trying to ask here, please elaborate more so we can try to help.
August 20, 2016 at 10:25 am
Thanks for coming back.
If we start with dimensions, are we saying all dimensions only contain unique distinct records, for example I have 10 customers and 15 products, does this mean my DimCustomer dimension will only have 10 distinct records for each customer and DimProduct 15 rows for each product? with both containing a business key to join back to the fact table which collects all the record transactions.
Thanks
August 25, 2016 at 8:08 am
sammane (8/20/2016)
If we start with dimensions, are we saying all dimensions only contain unique distinct records, for example I have 10 customers and 15 products, does this mean my DimCustomer dimension will only have 10 distinct records for each customer and DimProduct 15 rows for each product? with both containing a business key to join back to the fact table which collects all the record transactions.
Not necessarily. You could choose to track the history of certain attributes (like customer region for instance) - also called a Type-2 Dimension - which would then mean that you will potentially have multiple records for each product/customer.
This is also part of the reason for using surrogate keys in your fact table and not business keys. I'd again encourage you to read up on the basic methodology and concepts of data warehouse design. Having that knowledge is crucial to making the correct design choices.
August 25, 2016 at 8:25 am
Martin is right, depends on if you want to keep history or not. You could also you to / from dates to track your time line in your dimension tables. I would recommend taking a course in regards to SSIS / Data Warehousing, it will teach you a lot of principals. I started like you and quickly got overwhelmed, I actually started to study for the MCSA 2012/2014 - SQL certification. It's helping me transform to a VBA developer/AD Hoc SQL hack to a semi professional ware house guy.
Just my two cents.
With that said I usually build my dimensions and then my fact table. You'll have FK on your fact table and the record count is usually huge, at least from my limited experience.
Querying Microsoft SQL Server 2012/2014 - Certified
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply