locked
Creating MDX sets from Excel 2010 RRS feed

  • Question

  • Hi 

     

    I hope I don't get shot because this is seen as an Excel question but not sure where this one goes.

     

    But I am creating MDX sets from Excel 2010 against SSAS 2008. 

     

    Firstly does any one have any references to any decent resource on this functionality? It is really sparse out there with no specific examples.

     

    For example:

    I have a requirement to exclude some products from a set. The following works fine.

     

     EXCEPT([Product].[uid].AllMembers, {[Product].[uid].&[0001],[Product].[uid].&[0002] } )

     

    However is there a way of summing the excluded products and returning the sum as ..... "Total Excluded Products" ?

     

    Thanks

     

    Bif

     

     

    Wednesday, January 26, 2011 3:20 PM

Answers

  • Best solution I know of (assuming you don't want to have to change your cube definition and just want to do this inside Excel) would be to create a calculated dimension member called "Total Excluded Products" using OLAP PivotTable Extensions. I've documented how to add calculated members to dimensions using that tool here:
    http://olappivottableextend.codeplex.com/wikipage?title=Calculations%20Help&referringTitle=Home

    The formula would be something like this:

    Name: [Product].[uid].[All].[Total Excluded Products]
    Calculation: Aggregate( {[Product].[uid].&[0001],[Product].[uid].&[0002] } )

    Once you've got that calculation added and in your PivotTable, then try building a set that includes it. I haven't tried this exact sequence, per se, so let us know if this works. (I'm not 100% sure that Excel 2010 will let you put calculated dimension members into a set, but I'd love to hear results if you try this.)

    (Be aware that OLAP PivotTable Extensions won't display the set in it's UI correctly, currently. Just ignore that and don't use OLAP PivotTable extensions to edit the set. Just use it to edit the calculated dimension member mentioned above.)


    http://artisconsulting.com/Blogs/GregGalloway
    Wednesday, January 26, 2011 3:51 PM
  • Another possibility is using Excel cube functions to create a set with the excluded members.  This will require converting the pivot table to formulas, so it may not work in all scenarios but is still worth mentioning.

    Using Adventure works as an example, and creating a pivot table for all Countries in the Geography dimension by [Reseller Sales Amount], after converting to formulas you get the following formula for dimension members  (here showing only Canada, but it applies for the other members as well) -

    =CUBEMEMBER("Adventure Works DW 2008","[Geography].[Country].&[Canada]")

    and the [Reseller Sales Amount] value next to it would be -

    =CUBEVALUE("Adventure Works DW 2008",$A3,B$1)

    in which cell A3 in this case points to the cell displaying the [Canada] member, and cell B1 points to the cell that references the [Reseller Sales Amount] Member (=CUBEMEMBER("Adventure Works DW 2008","[Measures].[Reseller Sales Amount]") which appeared automatically when converting to formulas)

    At this point you can just type below the last member the value "Other", and use the following formula to get the aggregate for excluded members (which are, in my example, Australia and France)

    =CUBEVALUE(
     "Adventure Works DW 2008",
     "[Measures].[Reseller Sales Amount]",
      CUBESET(
      "Adventure Works DW 2008",
      "{[Geography].[Country].[All Geographies].[Australia],[Geography].[Country].[All Geographies].[France]}"
      )
     )

     




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Thursday, January 27, 2011 2:28 AM

All replies

  • Best solution I know of (assuming you don't want to have to change your cube definition and just want to do this inside Excel) would be to create a calculated dimension member called "Total Excluded Products" using OLAP PivotTable Extensions. I've documented how to add calculated members to dimensions using that tool here:
    http://olappivottableextend.codeplex.com/wikipage?title=Calculations%20Help&referringTitle=Home

    The formula would be something like this:

    Name: [Product].[uid].[All].[Total Excluded Products]
    Calculation: Aggregate( {[Product].[uid].&[0001],[Product].[uid].&[0002] } )

    Once you've got that calculation added and in your PivotTable, then try building a set that includes it. I haven't tried this exact sequence, per se, so let us know if this works. (I'm not 100% sure that Excel 2010 will let you put calculated dimension members into a set, but I'd love to hear results if you try this.)

    (Be aware that OLAP PivotTable Extensions won't display the set in it's UI correctly, currently. Just ignore that and don't use OLAP PivotTable extensions to edit the set. Just use it to edit the calculated dimension member mentioned above.)


    http://artisconsulting.com/Blogs/GregGalloway
    Wednesday, January 26, 2011 3:51 PM
  • Wednesday, January 26, 2011 3:55 PM
  • Another possibility is using Excel cube functions to create a set with the excluded members.  This will require converting the pivot table to formulas, so it may not work in all scenarios but is still worth mentioning.

    Using Adventure works as an example, and creating a pivot table for all Countries in the Geography dimension by [Reseller Sales Amount], after converting to formulas you get the following formula for dimension members  (here showing only Canada, but it applies for the other members as well) -

    =CUBEMEMBER("Adventure Works DW 2008","[Geography].[Country].&[Canada]")

    and the [Reseller Sales Amount] value next to it would be -

    =CUBEVALUE("Adventure Works DW 2008",$A3,B$1)

    in which cell A3 in this case points to the cell displaying the [Canada] member, and cell B1 points to the cell that references the [Reseller Sales Amount] Member (=CUBEMEMBER("Adventure Works DW 2008","[Measures].[Reseller Sales Amount]") which appeared automatically when converting to formulas)

    At this point you can just type below the last member the value "Other", and use the following formula to get the aggregate for excluded members (which are, in my example, Australia and France)

    =CUBEVALUE(
     "Adventure Works DW 2008",
     "[Measures].[Reseller Sales Amount]",
      CUBESET(
      "Adventure Works DW 2008",
      "{[Geography].[Country].[All Geographies].[Australia],[Geography].[Country].[All Geographies].[France]}"
      )
     )

     




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Thursday, January 27, 2011 2:28 AM
  • Thanks all. Javier, I used you solution because I wanted to solve this without altering the cube. 
    Friday, January 28, 2011 9:16 AM