Many to Many by Design (Relationship Table)

[intapiuser]

 Analytical Need

There are cases where we need to define an association between two entities. This association is comprised of the entities and not by each of them separately. 

For example, students & courses. We need to have a list of each student, a list of all courses and a list of which student is listed at which course.

 Modeling Challenge

A table in our cube will hold a connection between the two identifiers from 2 different tables. Each identifier is not unique in this table but the combination is unique.

For example, We have a fact table in which every ticket has a start date (and no end date) and we want to count how many open tickets we have per day.

We don't have a daily data of how many open tickets, we only have the open date.

 Solution

Example: We have the following two tickets:

Logic:

Step 1 : New field for Resolved Date

Create a new field as temporary Resolved date, that will assign today's date in case the Resolved Date is Null. This field will be populated daily based on today's date. Let's say today's date is 5-18-2016. The updated Tickets table will looks like this :

SQL for the new custom field:

CASE WHEN isnull([Resolved Date]) 
THEN createdate(getyear(now()),getmonth(now()),getday(now())) 
ELSE [Resolved Date] end

Remember to: 

• Name the field 'New Resolved Date'
• Set the field to be of type Date-Time
• Set the original Resolved Date field as invisible.

Step 2: Relationship table

Creating a relationship table connecting the Date Dimension and the Tickets tables.
The relationship table will hold a record per each date in between the Start & Resolved dates of each ticket.

SQL for the new Relationship table:

SELECT DISTINCT d.Date, t.[Start Date], t.[New Resolved Date] 
FROM [Tickets] t INNER JOIN DimDate d 
ON d.Date >= t.[Start Date] AND d.Date <=t.[New Resolved Date]

Step 3: Connections between the tables

Chart 1. Connection table Schema

Dashboard Result:

Chart 2. Dashboard : # of tickets per day

Dashboard

Ecdata