December 20, 2010 at 11:05 am
I have cube of survey data, which contains several dimensions (Time, Question, and Organization) and one Fact table (FactResponse). The FactResponse table has these relationships :
Time dimension on the survey date
Question dimension on the question key
Organization dimension on the unit key
We add questions to the survey over time and retire old questions, so the Question dimension has an Active flag, Start Date and End Date fields to indicate the changes.
On the reports the user can pick a date range to see the survey results for a unit in the Org. Currently the report built in SSRS 2008 uses the date parameters against the survey date in the FactResponse table and only displays Active questions. We need to change this so that the report not only filters on the survey date, but also shows questions that were active during the selected time period. This way the report will show the survey and the results as it way during the selected time period.
I'm just not sure how to do this. Should I create a relationship between the question Start Date and End Date with my Time dimension? Once that is done, how do I use that in my SSRS query? The question Start Date would need to be prior to the selected time period and the question End Date would need to be after the selected time period. But I didn't see any optin for greater than or less than in the graphical query design interface. Complicating the matter is that the user can select multiple time periods.
Any advice would be appreciated.
Thanks,
MWise
December 22, 2010 at 10:32 am
Hi,
What you should be doing in this case, is to change your Question dimension to a type 2 SCD. By doing that you will be able to track the changes (active/not active) those dimension members, and the key in the fact table should then reflect the appropriate dimension member for that particular fact.
Just another thing to consider...if your fact table contains survey data, doesn't it mean that the question had to be active during that time? If that assumption is correct, then should you be filtering on the active indicator for questions?
Either way, I still think a type 2 SCD is the best way to go. That will allow you to report on how long questions were active, and how many are active vs. not active at any given point in time.
Hope this helps.
Martin.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply