unpivoting a cube in an RS report RRS feed

  • Question

  • Hi.  Many products seem limited to displaying olap measures across (horizontally) the pivot area but we'd like to display them vertically in one particular report that will be generated and saved by RS to pdf.   We'd probaby have at least one dimension going horizontally.  

    What are our options without adding too much complexity?   We'll fall back and use our star if this isnt possible without special self service bi products.

    We've heard PowerPivot is one option but in this particular project we are removing 80 excel 2007 spreadsheets (with interdependencies across 500,000 cells with at least 80,000 formulas) from our accounting system and replacing them with a sql solution.  So I'm a little resistant to reintroducing excel where yet again a lot of our metadata and business rules would be contained in excel.   And perhaps performance would return as an issue.  But if there is a good argument and the infrastructure on client and server machines doesnt become crazy with the intro of PowerPivot, I'm all ears.

    Wednesday, November 16, 2011 1:29 PM

All replies

  • I am not certain I understand what you want to do.  But here are a few comments, let me know if I missed:

    If it works for your users, you can lock down the structure and preclude users from adding business logic to the powerPivot workbook (to avoid regressing :-).

    If the work is done in SQL, I would imagine that PowerPivot (as a client) would not add much (from a delay perspective)?


    bob mick

    • Edited by bob mick Wednesday, November 16, 2011 4:29 PM
    Wednesday, November 16, 2011 2:53 PM
  • Thx Bob.  I'll read your link and respond here in a day or two.

    Here is what I mean with my question:

    Let's say I have two measures like Revenue and Expenses.  In most OLAP reporting products like mgt studio, excel (before power pivot) , RS it seems to us that you are restricted to what you see below format wise ...

                   Revenue       Expense

                   xxxxxxxx       xxxxxxxx

                   xxxxxxxx       xxxxxxxx

    ...and your only flexibility is in the dimension placement(s).    Dimensions could always be horizontal and/or vertical.  But notice that I'm restricted in a horizontal presentation of my measures.

    So what if I wantesd this format in my report without coding expensive mdx?...

                          Company1          Company2....

    Revenue         xxxxxxxxxxx       xxxxxxxxxxx

    Expense         xxxxxxxxxxx       xxxxxxxxxxx

    In the mean time I think you are saying that if we were interested in enlisting power pivot, we'd have to wait till the next release but at that time RS could see the vertical presentation from a power pivot data source.

    Can you explain what you mean by "

    If the work is done in SQL, I would imagine that PowerPivot (as a client) would not add much?".  Are you saying PP would only need to be installed on the server so the client machines wouldnt be impacted?

    Wednesday, November 16, 2011 4:35 PM
  • I think that the example on the link I provided does what you are needing.  In this screen:


    You can see that BudgetAmt and SalesAmt are measures (like your Revenue and Expenses) and are on rows.  You would drop Companies in the "Column Labels" box.

    Note that you can also put dimensions on rows to slice the measures.

    The capabilities desribed are in the SQL 2012 (Denali) release which is I think is scheduled for first half of 2012.  I do not yet know whats happening on the RS side.

    I appologize for my ambiguous statement.  I was trying to address you question about performance.  The answer really is dependent on the computation you would have to do in PowerPivot.  If you are doing most of that in SQL, it will be done once when the data is imported into PowerPivot and PowerPivot will perform well.


    bob mick
    Wednesday, November 16, 2011 4:56 PM
  • Thx Bob.  I have to leave this post open for the RS community to tell me what my options are today.  But I've marked your other post answered.
    Wednesday, November 16, 2011 5:16 PM
  • we've prototyped this thing off a cube data source and here is what I am concluding about SSRS 2008 and cubes...I dont know much about RS...

    -thanks to the Tablix control, displaying a dimension (in our case product) horizontally in a financial report doesnt require any sort of repetitive kind of coding where you need to know in advance what products will display where.  Your design area shows one tablix column for product and the rest is kind of automatic.

    -SSRS does not recognize the product dimension's Sort by property of our cube so our developer needed to script her own RS "sort by" expression.   I lost a $1 bet on that one thinking RS would recognize such a property.

    -rows are a slight challenge  and our developer did need to code labels and expressions that uses the report line name dimension in a conditional statement to filter what displays on any particular line of the report.

    -we have a ton of similar reports whose big difference is what products they report.  Our developer told me that a relational table can be used in place of a hard coded RS filter so I'm starting to get excited by the possibility that using a cube as a reporting source isnt such a bad thing even in RS 2008.   Unless the report row structures change, the solution is pretty much a low maintenance IT report.

    When I think how ugly an abstracted pivot off our star (the one that is used to build the cube) would be,  I dont think RS is terrible when it comes to using a cube as a data source.

    I'm a little worried about what we face when more measures are introduced to the same report.  Maybe this is where sub reports come in handy.

    Friday, December 2, 2011 10:26 PM