Can anyone help me in selecting the right data model.
Model 1. A Fact Table at the heart of the model and all dimensions connecting to it.
Model 2. A flattened Fact Table with most of the dimensions merged into it (wherever possible/feasible)
The second approach seems to give a more responsive UI. However is there any downside to it that I may have missed?
I have attached an image to explain this. Please note that the image is just an example, the real model contains almost a hundred dimension tables and has millions of rows of data in the Fact table, and some of the dimension tables.
I will appreciate if anyone can help me understand the advantages and disadvantages of both the approaches (keeping in mind the volume of data the model is supposed to hold).
