Fact Table Load Question...
3 posters
Page 1 of 1
Fact Table Load Question...
Hello everyone, I have a question and I apologize if its a stupid question but I'm not a DBA/Data Warehouse developer, I come from the BI end user side and I'm very new to this.
Say I have a standard star schema with 3 Dimensions (Automobile Orders, Time and Product| Time and Products are hierachical) and a Fact table.
My question is, when I create the Fact table, traditionally I would have a FK that joins it to each of the Dimensions.
For simplicity sakes my Auto Orders Dim table has an Order_ID, Car Type, Sale Month and Sale Amount as the data attributes.
Next I'm doing a data merge from Auto Orders that aggregates the Autos Sold and Total Sales. In this scenario I can't merge in the PK-to-FK join since I'm doing an aggregate (vs merging in each row of data without an aggregate).
I've seen it done both ways, but I can't find any articles on if either way is right or wrong or recommended.
I would think not having a FK attached to those data points would be a problem, but I don't seem to have any unless I want to relate that data back to the source dimension in the BI tool.
Any advice or suggestions would be helpful.
Thanks
Say I have a standard star schema with 3 Dimensions (Automobile Orders, Time and Product| Time and Products are hierachical) and a Fact table.
My question is, when I create the Fact table, traditionally I would have a FK that joins it to each of the Dimensions.
For simplicity sakes my Auto Orders Dim table has an Order_ID, Car Type, Sale Month and Sale Amount as the data attributes.
Next I'm doing a data merge from Auto Orders that aggregates the Autos Sold and Total Sales. In this scenario I can't merge in the PK-to-FK join since I'm doing an aggregate (vs merging in each row of data without an aggregate).
I've seen it done both ways, but I can't find any articles on if either way is right or wrong or recommended.
I would think not having a FK attached to those data points would be a problem, but I don't seem to have any unless I want to relate that data back to the source dimension in the BI tool.
Any advice or suggestions would be helpful.
Thanks
GradStudent2015- Posts : 3
Join date : 2015-08-03
Re: Fact Table Load Question...
Hi - I don't really understand what you are describing or what the issue is that you are having. Please could you clarify?
1. You say that one of the data attributes of Auto Orders Dim table is Sale Amount - but that is a measure and belongs in a fact table not a Dim
2. What do you mean by "data merge" - are you just creating an aggregate fact table and, if so, what are the Dim keys you want on the Aggregated Fact table (i.e. how are you aggregating it compared to the base fact table)?
Which "PK-to-FK join" are you talking about and what do you mean when you talk about "merging" it?
Regards,
1. You say that one of the data attributes of Auto Orders Dim table is Sale Amount - but that is a measure and belongs in a fact table not a Dim
2. What do you mean by "data merge" - are you just creating an aggregate fact table and, if so, what are the Dim keys you want on the Aggregated Fact table (i.e. how are you aggregating it compared to the base fact table)?
Which "PK-to-FK join" are you talking about and what do you mean when you talk about "merging" it?
Regards,
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: Fact Table Load Question...
When you summarize data then Fact grain is different then the detail transactions. For example, if you summarize sales amount per month then you cannot tie individual sales Order_ID for that month for that Total amount. The good practice is to create 2 fact tables... one transaction Fact table has all the detail transactions by day and by Order_ID and another Fact table summarize sales by month. If users want to see detail of that total amount for a month then you select dates between that month from your transaction Fact.
zoom- Posts : 97
Join date : 2010-08-23
Location : Chicago
Similar topics
» Inventory Fact Table: How to load it?
» FACT table Incremental load!
» Incremental Load for fact table help
» New to DW and question about fact table
» Question for count in fact table
» FACT table Incremental load!
» Incremental Load for fact table help
» New to DW and question about fact table
» Question for count in fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum