none
Aggregations over many-to-many dimensions RRS feed

  • Question

  • I have just discovered that aggregations I have set up that go across a many-to-many dimension are not being used by the processing engine in SSAS 2008. My setup is as follows:

    MEASURE ---> Dimension1 <--- Bridge(many-to-many) ---> Dimension2

    I would like to setup an aggregation on an attribute of Dimension2 in my MEASURE's partition. So far I have been unable to figure out how to do this. I have seen some guidance of how to do this with SSAS 2005 with the following setup:

    MEASURE <--- Bridge(many-to-many) ---> Dimension1

    However, this is not the many-to-many setup I have.

    Does anyone know of a way to do what I'm trying to do, or at least have a link to some documentation?

    Friday, November 4, 2011 2:57 AM

Answers

  • Referring to the paper below, the many-to-many Dimension2 attribute should not be included in primary measure group (MEASURE) aggregations - rather it could be included in intermediate measure group (Bridge) aggregations:

    Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques

    ·         Designing data measure group aggregations for M2M queries

    To design an aggregation in the data measure group to support a M2M query, you must include in the aggregation the granularity attribute of all dimensions that join with the intermediate measure group. However, do not include the attribute from the M2M dimension; the join on this attribute in the data measure group and in the intermediate measure group occurs at query time as part of the run-time join.

    ...

    ·         Designing intermediate measure group aggregations for M2M queries

    To design an aggregation in the intermediate measure group, you must include in the aggregation the granularity attribute of the dimensions in the intermediate measure group that relates the measure group to the data measure group along with the attribute in the dimension that you wish to aggregate.   


    - Deepak
    Saturday, November 5, 2011 2:32 AM
    Moderator
  • a) I think in your scenario, refrence relationship should work fine, I will try ti simulate it below b) Also another approach can be if you are allowed to create a user hierachy then you can combine Dimension1 and dimension 2 as a single dimension which would come out of a single view which is a combination of Dim1 and DIM2 and bridge table, though this would turn to be a huge dimension as i assume seeing the table structures . A user hierachy would be created Scenario for a) above Dimension1 <--- Bridge(many-to-many) ---> Dimension2 Dimension1: vwProduct ( ProdID, ProductName), as Dim1 is in related to measurs so ProdId would be there in cube Dimension2: vwMarket(MarketId, Market Name) Bridge is a mapping table between Market and Product so it is like vwBridge(ProdId, MarketId) Now as a market can have many products and a product can be there in many markets so its a many to many relationship. Modify the vwProduct to take a join with the vwBridge and change it to return the colsumns as (ProdID, ProductName,MarketId) in the Dimension usage tab create a referenced realtionship on market(Dimension2) via the Product(Dimension1) on MarketId column The refernced column would be MarketId in both the cases, I hope this should resolve the problem, let me know Abhinav
    Abhinav
    Tuesday, November 8, 2011 11:19 AM

All replies

  • Referring to the paper below, the many-to-many Dimension2 attribute should not be included in primary measure group (MEASURE) aggregations - rather it could be included in intermediate measure group (Bridge) aggregations:

    Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques

    ·         Designing data measure group aggregations for M2M queries

    To design an aggregation in the data measure group to support a M2M query, you must include in the aggregation the granularity attribute of all dimensions that join with the intermediate measure group. However, do not include the attribute from the M2M dimension; the join on this attribute in the data measure group and in the intermediate measure group occurs at query time as part of the run-time join.

    ...

    ·         Designing intermediate measure group aggregations for M2M queries

    To design an aggregation in the intermediate measure group, you must include in the aggregation the granularity attribute of the dimensions in the intermediate measure group that relates the measure group to the data measure group along with the attribute in the dimension that you wish to aggregate.   


    - Deepak
    Saturday, November 5, 2011 2:32 AM
    Moderator
  • a) I think in your scenario, refrence relationship should work fine, I will try ti simulate it below b) Also another approach can be if you are allowed to create a user hierachy then you can combine Dimension1 and dimension 2 as a single dimension which would come out of a single view which is a combination of Dim1 and DIM2 and bridge table, though this would turn to be a huge dimension as i assume seeing the table structures . A user hierachy would be created Scenario for a) above Dimension1 <--- Bridge(many-to-many) ---> Dimension2 Dimension1: vwProduct ( ProdID, ProductName), as Dim1 is in related to measurs so ProdId would be there in cube Dimension2: vwMarket(MarketId, Market Name) Bridge is a mapping table between Market and Product so it is like vwBridge(ProdId, MarketId) Now as a market can have many products and a product can be there in many markets so its a many to many relationship. Modify the vwProduct to take a join with the vwBridge and change it to return the colsumns as (ProdID, ProductName,MarketId) in the Dimension usage tab create a referenced realtionship on market(Dimension2) via the Product(Dimension1) on MarketId column The refernced column would be MarketId in both the cases, I hope this should resolve the problem, let me know Abhinav
    Abhinav
    Tuesday, November 8, 2011 11:19 AM
  • I'm not sure I'm following, Abhinov.  I'm having trouble parsing out your reply...

    From what I can gather, you are saying my situation is:

    Sale ---> DimProduct <--- Bridge ---> DimMarket

    and you are suggesting that I turn this into something like:

    Sale ---> DimProduct --> DimMarket

    So this transforms DimProduct to have both ProductId, MarketId as well as ProductName. This implies that ProductId is duplicated many times in DimProduct.

    Is this correct so far?

    • Edited by Mikeg22 Tuesday, November 8, 2011 4:36 PM
    Tuesday, November 8, 2011 4:00 PM