How to display data in an sql server olap cube by a given time dimension with a special filtering

Question

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:

201030020112002012500How it might display for Quarter:

2010 - 11502010 - 2502010 - 3752010 - 425It 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 have created the project in Visual Studio and defined the dimensions and created the cube, it only needs the 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.

Answer 1

The solution you found is based on a scenario where you have two distinct date dimensions, one for start date and one for end date. But the complexity of your case resides in the fact that you need to aggregate your measures based on whether or not a certain date falls within the start and end dates of each case, no matter the dimension chosen (Year, Quarter, Month, Week, Date). In order to implement this, you would need to follow a number of steps:

Step 1: Set up the date dimension

You need to set up a shared date dimension which has Year, Quarter, Month, Week, and Date levels. This dimension is not connected to your fact table directly but used in the calculations.

Step 2: Create a factless fact table

To handle the fact that a single case can span multiple dates, you need a bridge table, often referred to as a "factless fact table". For each case in your Case table, this table will contain a row for each date from the StartDate to the EndDate. The granularity of this table would be on the day level. Here's an example of what this table would look like:

| CaseID | Date |

|--------|------------|

| 1 | 2020-01-01 |

| 1 | 2020-01-02 |

| 1 | 2020-01-03 |

| ... | ... |

| 2 | 2020-02-01 |

| 2 | 2020-02-02 |

| ... | ... |

Step 3: Create a new measure

With the factless fact table, you can create a new measure in your cube for 'CaseCount'. This measure will count the number of distinct CaseIDs in the factless fact table.

Step 4: Utilize the new measure

You can now use this measure with the shared date dimension. Depending on the level you are viewing in your date dimension (Year, Quarter, Month, Week, Date), SQL Server Analysis Services (SSAS) will automatically aggregate the CaseCount accordingly. For example, if you're looking at the Year level, it will sum the CaseCount for all dates within each year.

However, generating and maintaining the factless fact table could be a challenging task, particularly when you have a large amount of data. Alternatively, you might want to consider building a custom MDX expression, or even migrating your cube to a tabular model, where DAX provides more straightforward and efficient methods for solving this type of problem.

Answer 2

SQL Server Analysis Services