Many To Many Relationship in SSAS

HI,

Here I am going to show you a case which is widely used in many scenarios of Data Warehousing and Cube processing in OLAP which is Many to Many relationship which means that two dimensions share relationship and combination of them gives measure a distinct value.

To understand the Case we will take a simple example of Banking System.
In Bank we have Accounts and Customers between which we can have many to many relationship means that an account can include multiple person if it is a joint account the same way Single Customer can have multiple accounts in the same bank out of which one can be Individual and other can be Joint.

So if Bank has to calculate Customers Transactions through out the year then multiple relationships come into the picture.

To understand this let's take few tables.

1. Account Master
2. Customer Master
3. Transactions
4. Junction between Account and Customer.

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer_accounts_junction](
    [cust_id] [int] NOT NULL,
    [acct_id] [int] NOT NULL,
 CONSTRAINT [cust_acct_pk] PRIMARY KEY CLUSTERED
(
    [cust_id] ASC,
    [acct_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimAccounts](
    [acct_id] [int] NOT NULL,
    [acct_name] [varchar](50) NULL,
    [acct_type] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
    [acct_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DimCustomers](
    [cust_id] [int] NOT NULL,
    [cust_name] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
    [cust_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Fact_Transactions](
    [trans_id] [int] NOT NULL,
    [trans_acct] [int] NULL,
    [deposit] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [trans_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Customer_accounts_junction]  WITH CHECK ADD  CONSTRAINT [FK_Acct] FOREIGN KEY([acct_id])
REFERENCES [dbo].[DimAccounts] ([acct_id])
GO
ALTER TABLE [dbo].[Customer_accounts_junction] CHECK CONSTRAINT [FK_Acct]
GO
ALTER TABLE [dbo].[Customer_accounts_junction]  WITH CHECK ADD  CONSTRAINT [FK_Cust] FOREIGN KEY([cust_id])
REFERENCES [dbo].[DimCustomers] ([cust_id])
GO
ALTER TABLE [dbo].[Customer_accounts_junction] CHECK CONSTRAINT [FK_Cust]
GO
ALTER TABLE [dbo].[Fact_Transactions]  WITH CHECK ADD  CONSTRAINT [FK_acct_id] FOREIGN KEY([trans_acct])
REFERENCES [dbo].[DimAccounts] ([acct_id])
GO
ALTER TABLE [dbo].[Fact_Transactions] CHECK CONSTRAINT [FK_acct_id]
GO

Here we got Two dimension tables which are Accounts and Customers one Transaction Table and a Junction for Customer and Account Table.

Now we will open SSDT and see how Many to Many relationship can be implemented along with the above tables.

Remember that the Customer_accounts_junction Table plays the Junction role in Many to Many relationship.

I have created DataSource M2MDB and then created DataSource View and included all the four Tables.


Here since we have Foreign Key and Primary key relationship it is auto mapped but if FK PK is not defined at Database site it can be done in mapping over here which will create logical keys for Cube Processing.

Now let's move towards Cube creation.

Here Fact_Transaction is selected in Fact.

And Deposit Value is a Measuring Value and DimAccount and DimCustomer as Dimensions


But it shows that still the Junction Table is missing which we will add in the Measure Group since it is a Many to Many relational table that has be added as Fact less fact.


We can see that adding the Junction table in New measure group will add the midfact table as junction.
Now we have to go to dimension usage tab to set up for Many to Many relationship.

When we click on the Empty tab between fact and dimension we have to select the relationship type as Many to Many


By doing so the Above Scenario will appear and the Junction table now mapped as Many to Many Relation.


Now Process the Cube and that's it.

One can get the deposit Values per Customer (individually) for Tax deduction and also can come to know the Deposit amount per account as well.

This was just an example of Many To Many relationship other scenarios can be One Employee having Multiple Employments and One Employment having multiple Employees.

Hope this will be helpful for you to Implement Many to Many Relationship in SSAS.

Thanks