This was a requirement for me around 2 years ago, and I always thought worth storing the experience in case it came around again. Essentially the client wanted to analyse by 5 different dates. Imagine 'Delivery Date', 'Order Date' etc etc and you'll get the idea...
Possibly doesn't sound that bad but the killer requirement was to have a picker on the SSRS reports where you chose the date you wanted for that particular analysis. Without this you'd potentially end up with 5 separate calendar widgets on your report. Maybe sounds innocent enough but did prove very difficult. After a bit of work-shopping and brainstorming the solution we finally came up with was to:
Possibly doesn't sound that bad but the killer requirement was to have a picker on the SSRS reports where you chose the date you wanted for that particular analysis. Without this you'd potentially end up with 5 separate calendar widgets on your report. Maybe sounds innocent enough but did prove very difficult. After a bit of work-shopping and brainstorming the solution we finally came up with was to:
- Create 5 separate views of the Fact data, identical except for 2 columns
- Have a column called 'Date Type' that has values 1-5, a different value in each view
- Have a generic date in the views called 'Analysis Date'
- This date was mapped to a different source date depending on the value of 'Date Type'
We also created a simple little dimension with 2 columns like..
Date Type Code || Date Type
1 Delivery Date
2 Order Date
etc...
The trick then was to create 5 partitions in the cube and load the Fact data 5 times. Depending what value of 'Date Type' the user chooses then they are analyzing with a different date. Its pretty clever when its working and the users were a happy bunch.They were knocking up Excel books with pickers and we could use them in SSRS as well.
One gotcha of course is that they MUST choose a value of 'Date Type' or all the measure values will be a factor of 5 to big!! We got round this by placing a default value in the cube dimension - choosing the most popular 'Date Type'. This isn't the only drawback though, as you are also loading the data 5 times and depending on the cube size this could cause you problems.
So it did work for us but eventually we were able to solve the problem alot of more efficiently using Dynamic MDX. I'll cover that in a separate blog, I can see this technique here being used again though as the users did like the way the pickers worked. Dynamic MDX has its own overheads and this way you can put the MDX directly in the reports.
