I have a SQL Server 2016 OLAP Cube in the multidimensional mode and a corresponding database containing the tables Cases and Person. The Case table has the columns StartDate and EndDate and the Person table has the columns FirstName, LastName, and BirthDate. I'm using Visual Studio with the template Multidimensional Project to modify the cube.
I want the cube to have multiple Time attributes: Year, Quarter, Month, Week, and Date. These attributes should be placed in the left row, and their corresponding count of Cases should be displayed, where, for example, the Year is between the StartDate and the EndDate. Specifically, when I select Year as the displaying value, it should show all cases that had some occurrence in that year. Valid examples listed for the Year 2020 would be cases that started before 2020 and ended in 2020, cases that started in 2020 and ended in 2020, cases that started in 2020 and ended in one of the following years, and cases that started before 2020 and ended after 2020.
How it should display for Year:
| Year | Case Count |
|---|---|
| 2010 | 300 |
| 2011 | 200 |
| 2012 | 500 |
How it might display for Quarter:
| Quarter | Case Count |
|---|---|
| 2010 - 1 | 150 |
| 2010 - 2 | 50 |
| 2010 - 3 | 75 |
| 2010 - 4 | 25 |
It would also be nice to create a hierarchy of Time, like Year -> Quarter -> Month, which is compatible with the case count.
The display doesn't have to be exactly as described; it could also work as a column. I only want a statistical view.
I have already spent one week trying to get it working but without success. The case count is always the same for each date.
I already created the project in Visual Studio and defined the dimensions and created the cube, it only needs modification to work with my scenario.
I have found a website that could have a possible solution, however, I need the script to work with my dimension:
AGGREGATE(
{NULL:LINKMEMBER([Date].[Calendar].CURRENTMEMBER
,[Start Date].[Calendar])}
* {LINKMEMBER([Date].[Calendar].CURRENTMEMBER
, [End Date].[Calendar]):NULL}
, [Measures].[Project Count])
https://www.purplefrogsystems.com/2013/04/mdx-between-start-date-and-end-date/
Thank you in advance.
Edit:
According to MrHappyHead's answer, I have created some relationships:
And here is the Dimension Usage tab:
