Linking date dimension to date range in analysis services 2005
Posted on 2008-10-03
I have a table describing accounts of a company. It looks like this:
ID Account_id Active Record_start_date Record_end_date
1 1 false 01/01/2007 10/01/2007
2 1 true 11/01/2007 31/12/2007
3 1 false 01/01/2008 null
4 2 false 01/01/2007 31/01/2007
5 2 true 01/02/2007 31/09/2007
6 2 false 01/10/2007 null
Is it possible to connect this to a date dimension, so that i can get number of accounts, and number of active accounts
for a given date/date-period ?
I have been solving this by making a fact table containing one record per account per day, stating weather or not it was active or not,
and then doing a making a distinctcount measure on account_id, but this takes a lot of time to make, and also a lot of time to process,
so i thought that perhaps a more clever solution existed. I have searched the web for a few days now, without finding the solution to this,
so i thought i would try you guys.
Thanks in advance.
/ David Bojsen