0

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: enter image description here enter image description here enter image description here

And here is the Dimension Usage tab:

enter image description here

Abdulrahman Hasanato
  • 447
  • 3
  • 9
Steven2105
  • 540
  • 5
  • 20
  • Have you connected up the dimension relationships in the model? If you are getting the same results it suggests that you havn't set up these between the fact and dimensions. – MrHappyHead Jun 15 '23 at 07:00
  • @MrHappyHead I don't know, I have created the dimensions Case, Person and Time. Time was created by Visual Studio. I did not setup a relationship manually. Where can I connect them? – Steven2105 Jun 15 '23 at 07:28
  • You need to connect up the relationships between your fact table and dimensions in data source view. You need to ensure the dimensions are setup in the Dimension usage tab. – MrHappyHead Jun 17 '23 at 19:42
  • @MrHappyHead I have added two relationships, however I do not know whether that's correct. I don't know what to set in the Dimension Usage tab. I have edited my question to include some images of the relationships and the Dimension Usage tab. FYI: I have edited the images to include English names and excluded irrelevant attributes. – Steven2105 Jun 19 '23 at 09:31
  • Interesting that the second half of the article that you linked to talked about assessing your data model in the context of something that might require complex MDX to achieve it. For me, I'm always happier building some additional aggregation tables that translate much easier to a measure group, than trying to fiddle around with too much MDX (don't know if that means I'm lazy, or not?) ...... To be honest, what you're asking for an answer on here is something that could take a lot of effort to implement (which I don't have the time for) ...... – Craig Jun 22 '23 at 05:42
  • .... However, my suggestion would be that your best approach would be to build separate aggregate tables that reference just the relevant single Time entity, and use your rules (ie. starts before, ends after, etc.) to populate the counts. That way, you'll just have a one-to-one relationship between those aggregates and your Time dimension, rather than worrying about the "Start Date" and "End Date", and having to muck about with MDX. As I say, though, unfortunately I don't really have the time to come up with a complete answer for you – Craig Jun 22 '23 at 05:45

0 Answers0