I am a newbie when it comes to data warehouse projects and would like to seek the advice of the community here.
I need to create a data warehouse from which both historical and current information can be extracted in the most efficient/inexpensive way possible. In this particular example, we are dealing with web site users and preferences.
We have a Users table, an attribute table called Preferences (with a name of preference and ID) and then a connection fact table called User_Preferences. Rather than storing all history and preferences changes/deletions/additions in the User_Preferences table (which could be 100s of millions of rows), would it make more sense to have 2 tables, one for current preferences and one with all preference history (using an isCurrent flag)? In the ETL process, we would load all historical data from the prod db into one dw table and then insert only those records with an isCurrent=1 in new dw table into another dw table only storing current preferences.
From a business standpoint, the majority of queries would be run on the current data, as customers only care about a user's current preferences. A much smaller number of queries would need to return information about the full history of a user's preferences for the internal interests of the business.
Thanks for any help you can provide!
Yes, it makes sense. I would use a CurrentPreference fact table, as you have it described, and also a TransactionalPreference that report all the changing in preference. From this table you could easily get the history of a user.
A Transactional F.T. (dimension are Time, Transaction, User, Preference) has all the information but is quite difficult to query for past situation (what are the preferences of Texans last year in januar?) so could be useful also a Snapshot Preference, a fact table that contains situation at a point in time (every month, or every day, it depends on your users wish).