Data warehouse design
Posted on 2009-04-23
I'm digging into designing a data warehouse for our ERP system, and I have a question about the fact tables. I know that the data should be split up into several fact tables (grains), depending on which data the user wants to extract from the data warehouse.
But I'm struggling with master-detail tables. I assume it's good practice to split up both the header and the detail table into 2 seperate fact tables, as the granularity is different. Is this true ?
And if so, is it advised to store summerized line information on the header fact table (like total quantity, revenue, ...) ? In my opinion this makes it more easy for the end-user to make his own queries on the fact tables, like average quantity per order per customer per period. I know this query can be based on the detail lines fact table, but this requires a more complex SQL statement as the info has to be grouped per order first.