HomeData warehouseComplex reporting off a SSAS cube

Comments

Complex reporting off a SSAS cube — 10 Comments

  1. James,
    Could explain beter your perspective that argues that is not possible using “Finantial Cube as Source”?
    Almost my cubes are for finantial departments and always used CUBE as source without any problem. Let me know into deep detail what was the issues you faced. It’s possible? Thank you!
    Amazing blog you have here!
    Pedro

    • Hi Pedro. Glad you like my blog. The cube should always be used over the data warehouse for reports if possible, but my point is going against the cube forces you into a hierarchical structure for the reports, and when trying to do a report that does not fit into a hierarchical structure, like a P & L, you just can’t make it work in SSRS. So, you are forced to go against the data warehouse instead or use Excel. Or say you want to show on the report a total of account 100 and account 200 and display that total as the last line in the report. You don’t have that kind of freedom when using the hierarchical structure of the cube.

  2. Another solution would be to use cube value fomulas in Excel to point at the cube. Then you can build the report structure that is required as well as include addtional calculations that might not be in the cube using Excel formulas. This has the added benefit of allowing an end user to create and update the report without much help from IT. Pivot table filters and slicers can also be leveraged to provide a rich selection and filtering experience.

    • Great point Drew, thanks. Of course this means that you are using Excel as the front-end instead of SSRS, but with so many users familiar with Excel and with Excel Services, it’s an excellent option.

    • Hi Pedro. Using your example, say I wanted to take “Selling Expenses” and “Computer Expenses” and add them together and put them in a line at the bottom of the report called “One-time Expenses”. How would you do that? Or say I wanted “Cost of Raw Materials” to be indented more and bolded, and I wanted to take PROFIT/LOSS and multiply it by 2 and add a line with that total as “EXPECTED PROFIT/LOSS 2012”?

  3. Hi James, you’re right – it’s ugly & very manual process to make SSRS return rows different than the natural hierarchies that are set up. I needed to do this once, for financial reports, and it wasn’t pretty. (Filtered groups to control presentation on each row; calculations to detect which level & do indentation & bolding based on level #; etc. Very tedious.) However, the hierarchies worked as desired within an Excel environment – so we put the “burden” on the fully formatted reports that wouldn’t change very often.

    I agree with you that the relational DW was more well-suited for the format desired. That would have been my solution. However, my client drew a line in the sand: all reporting is from the cube only – they were concerned about the possibility of getting two different answers if we created some reports relationally & some multidimensionally (i.e., the cube had a lot of time intelligence & business calculations defined that wouldn’t have been accessible to the relational DW queries).

    • Great input Melissa. And I have found that what is even harder than using hierarchies in SSRS is using them in PerformancePoint. Maybe one day Microsoft will make it easier. Thanks so much!

Leave a Reply to James Serra Cancel reply

Your email address will not be published. Required fields are marked *

HTML tags allowed in your comment: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>