cancel
Showing results for
Search instead for
Did you mean:

# Many to Many by Design (Relationship Table)

Community Team Member

## 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

Version history
Last update:
‎02-23-2024 10:48 AM
Updated by:
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email [email protected]

Share this page: