<

Many To Many Relationship in SSAS

Published on
5,723 Points
2,523 Views
2 Endorsements
Last Modified:
Vikas Garg
Myself vikas garg , I am a BI developer and working with the BI technology like SSIS, SSAS 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month