<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Many To Many Relationship in SSAS

Published on
6,067 Points
2,867 Views
2 Endorsements
Last Modified:
Vikas Garg
I am a BI developer having 10 Years of experience with the BI technologies like SSIS, SSAS, Power BI ,SQL Server and SSRS since 2008.
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.

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

Image2.jpg
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.

Image3.jpgHere Fact_Transaction is selected in Fact.

Image4.jpgAnd Deposit Value is a Measuring Value and DimAccount and DimCustomer as Dimensions

Image5.jpg
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.

Image6.jpg
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.
Image7.jpg
When we click on the Empty tab between fact and dimension we have to select the relationship type as Many to Many

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

Image9.jpg
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
 
2
Comment
Author:Vikas Garg
0 Comments

Featured Post

The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month