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

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,

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.