Setting up Many to Many in SSAS 2008 Cube

This article will cover creating a Many to Many relationship in analysis services. It is assumed you already know how to create dimension and measure groups in Analysis services and have a basic understanding of analysis services. I will be writing more basic articles on analysis services to help the ones that are new. Check my other articles and blogs for this.

In some situations the fact table in your data warehouse will have a many to many relationship with a dimension. This situation calls for the use of a bridge table and setting up a many to many relationship between the fact and dimension with the bridge. I have created a miniature data warehouse example to simplify the issue and show the steps on how to set this up quickly. First let's discuss why we would have a many to many relationship.

Let's say our fact table is a table of sales, and each sale can have multiple sales reps. If several sales reps worked on the sale then they all get credit for the order. They all may get a percentage of the sale also. I will show you how to handle this later.

First let's take a look at the tables. I have trimmed these down to just the needed columns to simplify the example.

Here is the Fact Sales Table. Notice there are only 5 sales on the table. But each sale can have several sales reps associated with it. Notice there is no SalesRepSk. This is because no one sales rep ID would cover all the sales reps. The order id will tie to the bridge table to tell us who the sales reps were on each sale.

Here is the Sales Rep table. Again stripped down for simplicity.

Here is the Sales Rep Bridge table. Notice this table has an SK that ties it to the sales rep and the order id which ties it to the fact table. Notice how some orders repeat. This is because they have multiple sales reps. For example Order ID 10 has the reps 1, 2, and 3.

This is what the table will look like in Analysis Services in a cube. Notice the direction of the foreign keys from the sales rep bridge. Notice the fact sales does not tie directly to the sale rep dimension table. I have a date dimension added also. This is just a basic date dimension with a regular relationship to the fact sales. I will explain the SalePercent on the bridge in a moment.

Once you have the tables and regular dimensions set up you will need to add the fact table as a dimension. Right click on the dimension folder in the solution explorer and select new dimension. Select the fact table as the table to use.

Then you will add it to the cube as a dimension. In the bottom left right click on the cube in the dimension window and select Add Cube Dimension. Select Fact Sales as the dimension. The only attribute I am going to show is the order ID. This is also known as a degenerate dimension.

Next we are going to add the sales rep bridge as a new measure group. Right click on the cube in the measure window and select new measure group. Select the sales rep bridge as the table. You can hide all of the attributes. We do not need to see these in the cube.

Now we will set up the many to many relationship between the tables. Click on the dimension usage tab. Notice the field next to Sales rep under fact sales is blank. Click on the button next to it and select many to many as the relationship type. Select Sales Rep Bridge as the intermediate measure group. Afterwards you will see a many to many symbol in the dimension relationship field.

Now process and deploy the cube. Go to the browser tab and you will now be able to see the orders with the multiple sales reps.

But wait there's more. What if each sales rep has a different percentage of the sale. Maybe a different commission percentage. The only place it would make since to place the percentage would be on the bridge table. Because the number can be different for each order and for each sales rep. Notice I have added this column to the sales rep bridge table. Each order the percentage will add up to 100%.

Now you can add this measure to the sale rep bridge measure group.

Go back to the browser and reconnect. Drag down the percentage from the measures. Now you can see the sales rep percentage for each sales rep for each sale. In this example you can see Devin has 25% of order 10 and 100% of order 12. Some sales may have a single rep and other multiple.

This is a complex situation. I hope I simplified it for you. Let me know if you have any questions.