Link to home
Start Free TrialLog in
Avatar of DavidBojsen
DavidBojsen

asked on

Linking date dimension to date range in analysis services 2005

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
ASKER CERTIFIED SOLUTION
Avatar of srnar
srnar

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DavidBojsen
DavidBojsen

ASKER

Hello srnar

Thanks for your comment.  Actually this is what i am already doing, as i tried to explain in the question.
I was just hoping that someone had a better solution.

Best Regards
David