Using Many-to-Many Relationships in Multidimensional SQL Server Analysis Services

By:   |   Comments (7)   |   Related: > Analysis Services Development


In the health care world, claims are identified by many attributes. A frequently requested attribute is Diagnosis. These codes are used to identify types of claims and categorize claims for reporting and analytics. But once users request slicing by more than just the Primary Diagnosis, the one to many option is no longer valid. What can be done in Dimensional Modeling and Multidimensional Analysis Services to handle this request?


Many-to-Many Data Mart

Analysis Services multidimensional cubes have the enterprise level features to support slicing and dicing for large volumes of claims using a many-to-many relationship. The Kimball Group suggests different paths for Data Marts to take when modeling Many-to-Many relationships. The method we will look at involves relating a group of Diagnosis to each line item in a claim.

The data comes in 2 separate tables – ClaimDiag and ClaimRevenue. They each have multiple rows related to one unique claim number. Here is a look at a Data Diagram (simplistic).

OLTP Claims Diagram

The Claim Revenue table is going to be the main source of the Fact table in this OLAP design and Cost is used as the measure along with counts. The Claim Diagnosis table will be related in a Many-to-Many fashion. This means that one Diagnosis Code for a claim can be related to each Line Item (Revenue) in a claim. Here is our star schema without the Diagnosis:

Data Mart Claims Diagram

Adding Many-To-Many Dimension and Fact Tables to Data Mart

We will add 3 additional tables to the design: DimDiagnosis, DimDiagGroup and FactDiagGroup. The surrogate key from DimDiagGroup will be added to the FactMedicalClaims table. The following diagram shows that the Line Items in Medical Claims has an indirect relationship to DimDiagnosis through the DimDiagGroup (Dimension) and FactDiagGroup (Bridge Fact) tables.

OLTP Claims Diagram

The DimDiagGroup table contains different combinations of Diagnosis Codes found in the Medical Claims. This table does not have to have all possible combinations, but needs to have the ones related to the known claims.

Diagnosis Groups

Here is an example of some data from the Diagnosis Grouping table. The combination of Diagnosis Codes are representing the possible combinations on one or more claims. Each line item will be related to this grouping.


Group Names Group Count
1 0100,0200,0300 3
2 0130 1
3 0400,0500,0060,0080 4

Dimension Relationships

Setting up this relationship in Analysis Services requires 2 new Dimensions - Diagnosis and DiagnosisGroup. The DiagnosisGroup will not be visible in the cube. The cube without the Many-To-Many looks like the following:

SSAS Design

The Data Source View (DSV) will now include the 3 new tables. The 2 new dimension tables will have dimension objects created in the cube.

SSAS Data Source View

Next, we create a Measure and Measure Group for the FactDiagGroup as a count, but Visible is false. This Measure Group is needed for the eventual many-to-many relationship.

SSAS New Diagnosis Dimensions

There is an error identified that there is no relationship between the new measure and any dimension. We will need to add the Diagnosis Dimension to the Cube.

SSAS New Diagnosis Dimensions

This will give the Diagnosis dimension a relationship with the Fact Diag Group measure group through the Diagnosis surrogate key. The Cube understands the join because a relationship was setup in the Data Source View (DSV) between these tables.

SSAS Dimension Relationship

Before we can setup the many-to-many relationship between Medical Claims and Diagnosis, we need to add the Diagnosis Group dimension to the Dimension Usage area of the cube and make Visible = False for the Diagnosis Group dimension.

SSAS Dimension Relationship

To add a Dimension Usage between the Medical Claims measure group and Diagnosis dimension, we need to setup a many-to-many.

SSAS Dimension Relationship

And now will can slice the Cost by Diagnosis Codes. The Costs (and Counts) of a claim is now associated with a Diagnosis (or really more than one diagnosis).

SSAS Dimension Relationship
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Thomas LeBlanc Thomas LeBlanc is a Sr DBA and MCITP 05/08 DBA & 08 BI has spoken at the PASS Summit 2011/12, SQL Rally & many SQL Saturday’s.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, September 26, 2016 - 11:08:27 AM - Thomas LeBlanc Back To Top (43419)



This article does not go into that level of detail. In the actual solution, there are 2 DisgnosisKey columns in the fact table: PrimaryDiagKey and DiagKey. The Diagnosis Dimension table is related to the fact table twice. one for primary and one for all.


Works nicely as long as the end user is educated on the difference.




Saturday, September 24, 2016 - 2:07:52 AM - Yuriy Back To Top (43405)

Hi Thomas!

In the given model how do you differentiate which diagnosis is primary and which one is not? 

Looks like that information is lost. How would one add it to the model and SSAS cube?






Monday, May 16, 2016 - 9:19:15 AM - Thomas LeBlanc Back To Top (41490)

 Hi Reuben,


Yes, you are correct. The total for the claim is associated with each Diagnosis because the dollar amount is NOT per diagnosis, but per claim line item.


Usually analyst are not concerned with dollar amounts per dianosis, they know that is not how things are billed. But, they do need to do analysis on diagnosis for patients. So, their queries of this data by diagnosis is not for monetary analysis but patient wellness or non-wellness.



Monday, May 16, 2016 - 3:53:04 AM - Reuben Anderson Back To Top (41489)

Hi Thomas,

Thanks for the tip, it's a good real world, non Adventure-Works example. 

Am I correct in understanding from your model above that any analysis will show the full cost of the claim being attached to any/all of the diagnoses selected? So there's a risk of a user double-counting costs? 


Thursday, November 19, 2015 - 6:31:15 AM - rutvij Back To Top (39102)



Facing issue with SSAS cube dimension.


I am already having one dimension name Entity present in cube and want to add new dimension customer to cube.


customer view is having and column of type int which is referring to entity id.

Even I established a relationship between Entity dim with Customer dim from foreign key to primary key


Then I deployed and processed cube on my local

When I browse cube using SSMS cube browser


it should display customer which are associated to that particular entity as shown below





Customer 1001


Customer 1002


Customer 1003


But its displaying record like





Customer 1001


Customer 1002


Customer 1003


Customer 1001


Customer 1002


Customer 1003


I will be great help if you can help me on the below issue.

Thanks in advance

Monday, September 8, 2014 - 12:07:35 PM - Thomas LeBlanc Back To Top (34441)

The idea behind this tip was for BI developers that have already created cubes and are having problems relating multiple dimension attribute values to a measure (or 2).

There are plenty of introduction to SSAS on - start at



Friday, September 5, 2014 - 9:55:57 PM - girishkumar Back To Top (34402)

I didn't understand what u posted.. even a bit..please keep it simple.Please make understand easy for  new guest visits the page instead of professionals.

get free sql tips
agree to terms