Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2014-09-05   |   Comments (7)   |   Related Tips: > Analysis Services Development

Problem

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?

Solution

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.

DiagGroup_SK

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


Last Updated: 2014-09-05


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

Yuriy

 

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.

 

Thomas 

 


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

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?

 

Regards,

-Yuriy

 

 


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

 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.

Thomas

 


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

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

Hi 

 

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

 

Entity

Customer

Vodafone

Customer 1001

 

Customer 1002

Airtel

Customer 1003

 

But its displaying record like

 

Entity

Customer

Vodafone

Customer 1001

 

Customer 1002

 

Customer 1003

Airtel

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 08, 2014 - 12:07:35 PM - Thomas LeBlanc Back To Top

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 MSSqlTips.com - start at http://www.mssqltips.com/sqlservertutorial/2000/sql-server-analysis-services-ssas/

Thanks,

Thomas


Friday, September 05, 2014 - 9:55:57 PM - girishkumar Back To Top

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.


Learn more about SQL Server tools