I have one aggregated fact table in pivoted format which contains 24 Month data. Fact table having 40 measurable fields and 26 dimensions. If I unpviot it using cross table with all 40 measures then my row count will increases upto 500 millions.
To optimize it i was thinking to break table into 2 table. Both table contains all 26 dimensions and 20 measure. Then I will unpivot it and link using combination key which is having all 26 dimensions. Reason to break it into 2 different table is to minimize row count. This approach will help me to reduce row count 50%.
Please i need advice, should I go with approach that I am thinking or do we have any other approach to optimize it.
Why do you think you need to unpivot that table in the first place? And do you really need to unpivot all measure columns? Can you post a sample of your data? That could give us a better idea of your problem.
Due to some confidentiality i can not able to post sample data but I will create some scenario through which you will able to understand my issue.
Yes, there is some requirement at front end level. To achieve it i must need to unpviot it else I need to use multiple times pick and match and nested if. Due to nested if my charts taking time to display values based on selection.
