Many-to-Many Part 2: Multiple Groupings of a Dimension Member in SSAS

By Evan Schmidt

This article describes how to enable a dimension member to be classified multiple times or given multiple tags. This data structure can be built by linking many-to-many relationships together.

 

The scenario, described by the data below, show a dimension where one attribute (e.g. “Bob” a sales person) is described by multiple members of the “Expertise” dimension (e.g. his 3 areas of expertise).

 

For example a sales person might have multiple areas of expertise:

Sales Person	Expertise
Bob	Art History
Bob	Modern Art
Bob	Local Art
Nancy	Modern Art

 

Now “Expertise” becomes a new dimension and the table above is the “Sales Person Expertise Bridge” table.

The dimension usage will look like this:

Fact: Dimension:	Trans.	Trans. Sales Person Bridge	Sales Person Expertise Bridge
Transaction	Regular usage	Regular usage	None
Sales Person	M-M: Trans. Sales Person Bridge	Regular usage	Regular Usage
Expertise	M-M: Trans. Sales Person Bridge	M-M: Sales Person Expertise Bridge	Regular Usage

 

The report will show the total revenue driven by people who have a given area of expertise:

Expertise	Distinct Qty	Distinct Rev
Art History	3	25,000
Local Art	3	25,000
Modern Art	4	75,000
Total	8	85,000

 

At run time the server looks at the dimension usage of the "Expertise" dimension in the "Transaction" fact table and finds the dimension usage is sourced from the "Transactions Sales Person Bridge". So then server then looks at the dimension usage for the "Expertise" dimension in the "Transactions Sales Person Bridge" and finds the dimension usage is sourced from the "Sales Person Expertise Bridge". Then the serer finds the Regular Usage between "Expertise" and the "Sales Person Expertise Bridge" and so it can generate the data by joining the fact tables based on the common dimensions.

Notice how Trent’s sale is missing. This is because Trent dos not have an area of expertise.

It might be perfectly acceptable for the report to appear this way. If this missing transaction is not acceptable there are several ways to deal with it. The next article will discuss my preferred method.

 

Many-to-Many Part 1: Distinct Sum in SSAS

By Evan Schmidt

This article describes how to use a many-to-many relationship to create a distinct, or de-duplicated, revenue calculation.

 

With this method revenue will never be counted twice in a total or sub-total. This may be required when a single transaction can be described by multiple descriptions of the same type. For example in the data below you will find the sale of a $10k Print was influenced by both Nancy and Bob.  It may not be correct to allocate the revenue 50% to Bob and 50% to Nancy so the solution is to give both full credit while never double counting in totals and sub-totals.

 

The transaction is the measure, or fact, record.  Here is the “Transaction” table which lists four sales:

Transaction ID	Product Type	Date	Qty	Rev
A	Original	6/5/11	1	15,000
B	Print	6/6/11	2	10,000
C	Texture	6/7/11	1	50,000
D	Print	6/7/11	4	10,000

 

Here is the “Transaction Sales Person Bridge” table which records the sales person who influenced each sale:

Transaction ID	Sales Person
A	Bob
B	Bob
B	Nancy
C	Nancy
D	Trent

 

The report user wants to see a report which shows the revenue driven by each Sales Person. Both Bob and Nancy are given full credit for the sale but the sale is not double counted in the grand total.

Sales Person	Distinct Qty	Distinct Rev
Bob	3	25,000
Nancy	3	60,000
Trent	4	10,000
Total	8	85,000

 

 

This can be implemented by using a many-to-many relationship in SSAS. The dimension usage will look like this:

Fact: Dimension:	Trans.	Trans. Sales Person Bridge
Transaction	Regular usage	Regular usage
Sales Person	M-M: Trans. Sales Person Bridge	Regular usage

 

 

Upon query execution the SSAS engine will respond based on the dimension usage. When the Transaction Fact is sliced by the Sales Person dimension the slice should be based on the many-to-many relationship with the Transaction Sales Person Bridge table. First the SSAS engine finds all Transaction IDs in the bridge table with the given member from the Sales person dimension. Then the SSAS engine finds all records in the transaction fact table with those Transaction IDs and returns the revenue.

 

How to create a comma delimited list using SQL Server

By Evan Schmidt

This mini-blog describes a quick method to create a comma separated field within a SQL query using SQL Server.

 

In this example there are two teams with multiple sponsors for each team.

TeamID	TeamName
1	Ducks
2	Bears

 

TeamID	SponsorName
1	Fuller Co.
1	Martin & Chase
2	Bo Thomas Inc

 

You would like the query to return a delimited list that contains the sponsors for each team.

TeamID	TeamName	SponsorList
1	Ducks	Fuller Co.; Martin & Chase
2	Bears	Bo Thomas Inc

 

 

The following query will takes the first two tables as inputs and creates the third table. Please note that the “FOR XML PATH” statement will cause xml restricted characters to be replaced.

 

SELECT

L.TeamID

, L.TeamName

—replace xml friendly ‘&’ with ‘&’

, REPLACE(

   —limit the final output to 100 characters

   CONVERT(NVARCHAR(100),

          —the first 3 characters of this new field are removed

          SUBSTRING(

                (

                —the xml output is created which creates the delimited list

SELECT ( ‘; ‘ + TMP.SponsorName) –requires no column name

                FROM (

                       —sponsor list

                       SELECT 1 as TeamID, ‘Fuller Co.’ as SponsorName

                        UNION ALL

                       SELECT 1 as TeamID, ‘Martin & Chase’ as SponsorName

                       UNION ALL

                       SELECT 2 as TeamID, ‘Bo Thomas Inc’ as SponsorName

                ) TMP

                WHERE TeamID = L.TeamID

                FOR XML PATH(”) —required empty xml path

 

          ), 3, 100)

   )

,‘&’,‘&’) AS SponsorList

  

FROM (

                —team list

                SELECT 1 as TeamID, ‘Ducks’ as TeamName

                UNION ALL

                SELECT 2 as TeamID, ‘Bears’ as TeamName

) L