We are facing big challenge in summarizing and maintaining data over a hierarchy. Resulting into 300 to 600 million rows for a medium size implementations.
10 years ago , our team build code to create and rollup data over dimensions using global temporary tables and pivoting data using inline queries. Storing dimensions and facts in relation tables. Due to this there is data explosion happens and huge performance issues.
The application supports Project Financial and Work Management , key tables are :
- PROJECT HEADER TABLE .
- TASK table : tasks for each table with hierarchy ( work breakdown structure ). columns : TASK_ID and PARENT_TASK_ID. For top most node the parent task id is NULL.
-- Table storing budgets for the tasks . mainly at the lowest task level.
- FACT table sotring rollups for 5 to 8 dimensions ( project , task , currency , time , etc ) and facts ( budget , cost , revenue , bill amount , commitments, etc )
Key question :
-- The business requirement requires the ability to support changing the task hierarchy constantly and also the number of level in the hierarchy is user defined. can OLAP handle such constant changes and what kind of sizing is required for 500 to 600 million rows in fact table. If it is stored in relational data model.