Alert on a M2M Risk

[intapiuser]

Introduction

This article describes how to enable an alert* when there is a risk of Many-To-Many relationship (M2M) between two or more tables.

* This implementation involve the use of Pulse and it's supported in Sisense v6.5 and above. 

Motivation

Almost every Data Model designer that dealt with a bit more advanced modeling at some point ran into data\performance issue and after a long troubleshoot process he\her found out that the root cause came from M2M.

M2M can occur due to the following**:

1. Connecting 2 tables with a pure M2M relationship such as two Fact tables with a non-unique key.
2. A unique key became a non-unique due to changes or data issue within the source system.
3. A business need to have M2M between two entities.

From the described scenario you can assume that the M2M can be identified not only during the EC design but also during its ongoing use. Therefore, having a proactive alert for the EC designer can prevent from the user experiencing poor dashboard performances, wrong result sets and save many hours of troubleshooting, panicking users and general feeling of frustration.

** There can be other more complex cases of M2M which we won't describe here as the mechanism of the M2M alert is less relevant for them. Please refer to this article for a more detailed description of M2M relationship

Implementation

As an Data Model designer, during the design and after the final version of my schema is completed, I want to track on my connection and make sure that my assumptions of uniqueness on one side of the relationship (to get 1:M relationship) is being captured.
  

Step 1 - Build a M2M Detection Table

We'll create a table that will reflect the uniqueness of each table within the final schema that has to be unique according to its relevant primary or surrogate key. Basically we'll count the number of total rows within a table, the number of unique values of the table key and compare between them. if they are equal then the key is unique within the table, but if not there is a risk for M2M and so we'll raise a flag as you can see here:

We can learn from the output of the table that for our case we have only one table that is not unique (Cost).

Here's the query I used in order to create this table:

SELECT

'DimDates' AS Table,

'Date' AS Key,

count(t.[Date]) [Total Count],

DISTINCT_count(t.[Date]) [Distinct Count],

count(t.[Date])-DISTINCT_count(t.[Date]) AS [Duplicated Records],

ifint(count(t.[Date])-DISTINCT_count(t.[Date])>0,1,0) AS M2M_Flag

FROM DimDates t

 

UNION ALL

SELECT

'Customer' AS Table,

'CustomerID' AS Key,

count(t.CustomerID) [Total Count],

DISTINCT_count(t.CustomerID) [Distinct Count],

count(t.CustomerID)-DISTINCT_count(t.CustomerID) AS [Duplicated Records],

ifint(count(t.CustomerID)-DISTINCT_count(t.CustomerID)>0,1,0) AS M2M_Flag

FROM Customer t

 

UNION ALL

SELECT

'SalesPerson' AS Table,

'BusinessEntityID' AS Key,

count(t.BusinessEntityID) [Total Count],

DISTINCT_count(t.BusinessEntityID) [Distinct Count],

count(t.BusinessEntityID)-DISTINCT_count(t.BusinessEntityID) AS [Duplicated Records],

ifint(count(t.BusinessEntityID)-DISTINCT_count(t.BusinessEntityID)>0,1,0) AS M2M_Flag

FROM SalesPerson t

 

UNION ALL

SELECT

'Product' AS Table,

'ProductID' AS Key,

count(t.ProductID) [Total Count],

DISTINCT_count(t.ProductID) [Distinct Count],

count(t.ProductID)-DISTINCT_count(t.ProductID) AS [Duplicated Records],

ifint(count(t.ProductID)-DISTINCT_count(t.ProductID)>0,1,0) AS M2M_Flag

FROM Product t

 

UNION ALL

SELECT

'SalesOrderHeader' AS Table,

'SalesOrderID' AS Key,

count(t.SalesOrderID) [Total Count],

DISTINCT_count(t.SalesOrderID) [Distinct Count],

count(t.SalesOrderID)-DISTINCT_count(t.SalesOrderID) AS [Duplicated Records],

ifint(count(t.SalesOrderID)-DISTINCT_count(t.SalesOrderID)>0,1,0) AS M2M_Flag

FROM SalesOrderHeader t

 

--Example for uniqueness by surrogate key

UNION ALL

SELECT

'Cost' AS Table,

'Size + Weight' AS Key,

count(t2.Key) [Total Count],

DISTINCT_count(t2.Key) [Distinct Count],

count(t2.Key)-DISTINCT_count(t2.Key) AS [Duplicated Records],

ifint(count(t2.Key)-DISTINCT_count(t2.Key)>0,1,0) AS M2M_Flag

FROM

(

SELECT

t.Size + '|' + tostring(t.Weight) AS Key

FROM Cost t) t2

 

--Example for checking uniqueness for a table which not in the final schema but effects another table (as a source of a lookup custom field)

UNION ALL

SELECT

'Store' AS Table,

'BusinessEntityID' AS Key,

count(t.BusinessEntityID) [Total Count],

DISTINCT_count(t.BusinessEntityID) [Distinct Count],

count(t.BusinessEntityID)-DISTINCT_count(t.BusinessEntityID) AS [Duplicated Records],

ifint(count(t.BusinessEntityID)-DISTINCT_count(t.BusinessEntityID)>0,1,0) AS M2M_Flag

FROM Store t

Step 2 - Create relevant KPI within a widget

We'll create an indicator with the following formula:

if([Total M2M_Flag]>0,1,0)

This widget can be temporary as it only will serve us for alerting when the total of M2M flags is bigger than 0.

Step 3 - Add the KPI to Pulse

After Adding the widget to the Pulse, we'll mark the condition according the threshold 'Greater than' 0.

Feel free to adjust the notification mode and other preferences according your requirements.