How to join time intervals with measure groups within a cube in SSAS?

Question

I would like to create a intersection between a dimension that has following attributes:

Customer ID | Customer Level | Date From | Date To

1234 | Level 1 | 01.01.2018 | 31.12.2018

1234 | Level 2 | 01.01.2019 | 31.05.2019

And a measure group such as follows:

Purchased Product | Purchase Date

Cheap beer | 01.06.2018

Cheap beer | 01.04.2019

Actually I got stuck with defining the relationship that would fit to my purpose. I thought about redefining the dimension table so that instead of the time intervals for the first row I would have such table:

Customer ID | Customer Level | Tier Date

1234 | Level 1 | 01.01.2018

1234 | Level 1 | 02.01.2018

…

1234 | Level 1 | 31.12.2018

But if I have table with 600k customers and I would like to define a time relationship 'till the end of the world' then the size of the table would be too huge.

Finally I would like to have the same customer (ID = 1234) being assigned to Level 1 & Jun-18 as well as Level 2 & Apr-19. I would like to ask you help me how to build a dimension table (or confirm that mine is correct) and how to define a time relationship within Visual Studio so that customer level from the purchase date is taken into account.

Answer 1

Well, I see two possible approaches to your task.

1. Split your Customer master data into intervals when Customer Level assignment does not change, assign Customer ID for that interval and refer to it at Transactional data. Like in your example

   Customer ID | Customer Level | Date From | Date To
   12341 | Level 1 | 01.01.2018 | 31.12.2018
   12342 | Level 2 | 01.01.2019 | 31.05.2019

   Pros - easy to implement at SSAS side, relatively small size of data at SSAS.
   Cons - all ID logic has to be implemented at ETL level. Structure of analysis attributes is fixed.

2. Remove Customer Level from Customer Dimension and make a separate Customer Level Dim. You will get the same Customer Table and a Customer level table with

   Customer Level ID | Customer Level name
   1 | Level 1
   2 | Level 2

   Then - create a M2M relationship between Customer Dim, Customer Level Dim and Date Dim. To do that - add a Measure Group with the following structure

   Customer ID | Customer Level ID | Date ID
   1234 | 1 | 01.01.2018

   On this Measure Group - name it Customer Level Date Bridge - define Count measure. Then, on the Dimension Usage tab of the Cube designer in SSDT - you can set Customer Level Dim for Purchase Measure Group (assuming it has direct relations with Date and Customer Dimensions) as M2M dimension with a bridge Customer Level Date Bridge. More details on this on MS Docs.

   Pros - M2M approach is very flexible. As name says, you can have more that 1-1 direct assignment between Customer and its level. For example, you can divide customers into groups with some intersections.
   Cons - it requires building a bridge table and filling it with a record for every date within relationship interval. It can have implications on ETL, cube size and cube processing.