Narrow vs Wide Fact tables
Hi
I am bust planning and designing a new itteration of out Warehouse and was wondering if anyone could provide some insights into these thoughts.
All of our data is related to a Session. This session contains a lot of data relating to the User and their preferences. There are a number of different things that a user can do during that session. Each one of there is a seperate area and so will have a sepreate Fact table and hence measure group.
The normal approach I would follow would be to do the wide Fact tables, where all information relating to that fact is placed in the fact table. This means that all info relvant from the Session needs to be added to that Fact. This can mean a pretty wide fact, and one that we know performs well with all dimensions.
The problem is that we are often adding information to the Session records, meaning that we have to propogate these addtional dimensions out to all Facts that need them.
So this has led me to investigate having narrower, specific fact tables, basically moving to a more relational than a dimensional star design. The up side to this is that when changes happen to any of the fact, but more specifically to the Session Fact, it only needs to be changed in one place. This will simplify development and decrease load times quite a bit as the volumes are quite large.
The main problem that the business is worried about is performance in the cubes. From my understanding (and I may be wrong) I will need to set up referenced dimensional links though the session fact to get to those dimensions.
I can imagine that there will be a performance impact, but would like to find out some more info about this and if anyone else has done something like this before I go down a testing path as due to time and staffing problems, I don't have much leeway to do a large scale test.
Thanks for any help
Michael
