Many to many dimension - MDX help needed

Question

I’m pretty new to the many-to-many dimensions but I have a scenario to solve, which raised a couple of questions that I can’t solve myself… So your help would be highly appreciated!

The scenario is:

1. There is a parent-child Categories dimension which has a recursive Categories hierarchy with NonLeafDataVisible set
2. There is a regular Products dimension, that slices the fact table
3. There is a bridge many-to-many ProductCategory table which defines the relation between the two. Important to note is that a product can belong to any level of the categories hierarchy – i.e. a particular category can have both – directly assigned products and sub-categories.
4. There is a fact Transactions table that holds a FK to the Product that has been sold, as well as a FK to its category. The FK is needed, because

I have all this modeled in BIDS, the dimension usage is set between each of the dimensions and the facts, the many-to-many relation between the Categories and the Transactions table is in place is in place. In other words everything seems kind of OK..

I now need to write an MDX which I would use to create a report that shows something like that:

Lev1 Lev2 Lev3 Prod  Count

-A    
   -AA               6
      -AA            2
              P6     1
              P5     1
      -AAA           2
              P1     1
              P2     1
      -AAB           2
              P3     1
              P4     1
   +BB

The following MDX almost returns what I need:

SELECT 
[Measures].[SALES Count] ON COLUMNS, 
NONEMPTYCROSSJOIN(
      DESCENDANTS([Category].[PARENTCATEGORY].[Level 01].MEMBERS),
      [Product].[Prod KEY].[Prod KEY].MEMBERS,
      [Measures].[Measures].[Bridge Distinct Count],
      [Measures].[SALES Count],
      2) ON ROWS
FROM [Sales]

The problem that I have is that for each of the non-leaf categories, the cross join returns a valid intersection with each of the products that’s been sold for it + all subcategories. Hence the result set contains way too much redundant data and besides I can’t find a way to filter out the redundancies in the SSRS report itself.

Any idea on how to rewrite the MDX so that it only returns the result set above?

Another problem is that if I create a role-playing Category dimension which I set to slice directly the transactions data, then the numbers that I get when browsing the cube are completely off… It seems as SSAS is doing something during processing (but it’s not the SQL statements it shoots to the OLTP, as those remain exactly the same) that causes the problem, but I’ve no idea what. Any ideas?

Cheers, Alex

Answer 1

I think I found a solution to the problem, using the following query:

WITH 
  MEMBER [Measures].[Visible] AS 
    IsLeaf([DIM Eco Res Category].[PARENTCATEGORY].CurrentMember) 
  MEMBER [Measures].[CurrentProd] AS 
    IIF
    (
      [Measures].[Visible]
     ,[DIM Eco Res Product].[Prod KEY].CurrentMember.Name
     ,""
    ) 
SELECT 
  {
    [Measures].[Visible]
   ,[Measures].[CurrentProd]
   ,[Measures].[FACT PRODSALES Count]
  } ON COLUMNS
 ,NonEmptyCrossJoin
  (
    Descendants
    (
      [DIM Eco Res Product].[Prod KEY].[(All)],
     ,Leaves
    )
   ,Descendants([DIM Eco Res Category].[PARENTCATEGORY].[(All)])
   ,[Measures].[FACT PRODSALES Count]
   ,2
  )
  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ,PARENT_UNIQUE_NAME
   ,LEVEL_NUMBER
   ON ROWS
FROM [Sales];

In the report then I use the [Measures].[CurrentProd] as a source for the product column and that seems to work fine so far.