Creating a Report Model based on an Analysis Cube – Pros and Cons

May 15, 2008

Creating a Report Model based on an Analysis Cube – Pros and Cons

If you follow my blog, you probably know by now that I developed a report model based on the Analysis Services cube I already had. I even got around to updating the report model.

And then, my users started using it. And you know, whatever we do just seems so wonderful to us, until our users find all sorts of flaws with what we did… so yes, my users did open my eyes to quite a few problems that may occur when you build a report model based on a cube.

I even found myself contemplating doing the model all over again based on the relational database, but my boss decided otherwise. Still, he did ask me, "So Ella, if it has all these flaws, why did you do it in the first place?" The thing is, that though I saw all sort of information out there about how problematic it may be to build a report model based on a cube and all the preparation you need to do in the SSAS project, I never remember seeing a sort of warning about all the pros and cons of using a report model based on a cube. So I thought perhaps I would make a post out of it, especially now, that I also had the fortune of building a report model based on the relational DB in another project.

So here are the main points in my view:

1.   Maintenance – when you build a report model based on a relational database and you also have a cube, you will have to manage two projects. I think that this is really the main point. Because you’ll have 2 DS and 2 DSV to update and you’ll have to manage roles and security in 2 projects and so on and so forth… and I think that it should be quite a big consideration as to how much you want to put into maintenance of your projects. In fact, I think you’ll find it quite important that they stay just as low maintenance as possible. And creating a report model based on a relational DB may cause you more maintenance than with a report model generated from your Analysis Services cube.

As long as you don’t update the model you generated from the cube, any changes made to your SSAS project will show in your model, without you having to define them from scratch in the both projects, just regenerating your model after you defined the changes in your cube.

2.   MDX – Analysis Services enables you to define MDX scripts and their result is immediately mirrored in your report model. If the calculations you defined are important to you, you should consider the effect of not having them if you build a model without those manipulations on your data.

3.   Filtering on measures – one of the rare references I saw about problems in using a report model based on a cube was in this post, which I stumbled upon while looking for something else… as Bob Meyers points out there, "filtering on totals when using RB over AS will rarely result in acceptable performance" and believe me – it’s bold in the original post for a reason! :)…

At times, the query with the filter would run a very long time, at other times it wouldn’t give any rows back, even though I know they exist as I saw them without the filter on.

When I would use the filter on a measure that would have an MDX defined on it, it would give me the correct rows and in reasonable time, but without the column of the measure I was filtering on. I ran in the profiler the MDX query that the Report Builder defined for my report, and found in gave me my column with an "Error" for a value in each cell. I’m assuming that maintaining the value for the MDX calculation and filtering on it was too much to manage for report builder.

Even more so, I also have measures in certain measure group that are restricted for quite a few of my users. When a user who was blocked from one of those measures tried to use a filter on a measure in that measure group, she got an error. A user who wasn’t blocked was running the same report and got the all the rows correctly (only without the measure). I was left then to believe that the MDX query that the Report Builder built for my first user failed because the extra filter he had to add to the query (a filter required because of her security definitions) made the query timeout and fail.   

4.   Looking at different measure groups at once – a report model based on an SSAS project will only show you measures from one measure group each time. With a report model based on a relational DB, you can show measures from several fact tables at once.

This turned out to be very critical for me as I couldn’t look at my measures and compare, for instance, how my use of the budget stood in comparison to the budget itself at a certain year on the budget hierarchy. Something that seems so simple on my Panorama Nova View viewer.

The only exception to seeing measures from different measure groups in a report model based on a cube, is when your measure group is coming from a table that serves as a Fact table and a Dimension table. That means that in the “Dimension Usage” tab of the cube, you’ll see a relationship of “Fact” between the Dimension and that Fact. As there is a dual use of the table in the cube, you may see measures from different measure groups set beside one another.

5.   Defining a calculated member – the MDX run by report builder for a report model based on a cube is pretty simplistic, and so it does not allow you to define a calculated member based on a dimension attribute. You can only define calculated members based on your measures. This meant for instance, that if I had an attribute in my dimension and I wanted to define a new attribute in my model, based on the first letter of my attribute, I couldn’t do it in the report builder.

In a report model based on a relational DB, no such limitations exist.

Now, you could argue that all you have to do is to define your new attribute in the cube itself and then show it in your model. But then, you have to understand that you would need to add attributes to your cube according to each and every request of your users. That can bring you to a lot of attributes… and that’s instead of letting each and every user define it for himself for in his own reports.

Even more so, if you have updated the report model you defined on a cube, any changes made in the cube, you will have to redo in your model. For more detail, please see paragraph number 1 and the linked post.

6.   Response time – OLAP gives you predefined aggregations. As a result of that, you can promise yourself a quick response time for (most) of your queries in a report model based on an Analysis Services cube. To get the same quick response time in a report model that’s based on a relational DB you need to define indexes on your tables.

So yes, defining a report model on an Analysis Services cube does have its perks, especially when you have a lot of tables in your DSV and you’ve used translation and MDX. But at times, the lessened usability of a report model based on an SSAS cube in comparison to one based on a relational DB, can make you reconsider. I highly recommend that you consider wisely before choosing to develop your Report Model one way instead of the other.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

11 comments

  1. RickyMay 16, 2008 ב 0:03

    Hello Ella

    Nice post, very informative. I was wondering, if the model isn’t updated, after generation, and the cube is updated and hence the report model reflects these changes, will reports break?

    Kind regards

    Ricky

    Reply
  2. Ella MaschiachMay 16, 2008 ב 11:04

    Hi Ricky,

    Well as far as I know, if you changed something in your cube, (as in added something to it), and you haven’t updated your report model, then you can regenerate the model on the report server and old reports will still run. If you have taken out something from your model, or even hide an attribute that was in use, then old reports may not run.  

    I admit I haven’t stumbled upon a paper or post stating what it is exactly that will stop old reports from running.

    All the best,

    Ella

    Reply
  3. RickyMay 16, 2008 ב 11:14

    Hey Ella

    Thanks for you reply. I suppose I don’t wish to support two different data sources, so that’s why we are contemplating basing all reporting from the cubes and then using Report Models for ad-hoc reporting and Report Designer reports for complex reporting.

    Will keep you posted with any developments we make.

    Kind regards

    Ricky

    Reply
  4. Ella MaschiachMay 16, 2008 ב 12:42

    Hi Ricky,

    Well I hope the post helped you also consider how using a report model based on a cube can affect your model’s usability in relation to one based on a relational DB.

    I would very much appreciate it if you could come back and tell us all about your own experience.  

    All the best,

    Ella

    Reply
  5. Miky SchreiberMay 25, 2008 ב 22:25

    Hi Ella,

    I think you should try Chris Webb’s Intelligencia (http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1797.entry). I believe it can solve many of your problems ’cause it’s sending queries to the cube and show the results in SSRS reports. That way, you can enjoy both world – Low maintenance (you only have one DB model), fast query response and all your data in one place.
    By the way, don’t take too hard what your boss told you. Only from the experience we’re getting better.

    Miky.

    Reply
  6. Ella MaschiachMay 26, 2008 ב 17:00

    Hi Miky,

    Thank you for your suggestion, I had read about Intelligencia in Chris Webb’s blog.
    Unfortunately enough, I don’t think it’s so simple to add a new application to an organization, as it is a matter of budgeting, looking at the compatibility of the new application with existing applications, implications on infrastructure, time needed to learn the new tool and so on and so forth. All of this means that moving from one tool to another is not very simple. But I will move on your suggestion.

    Thank you again for your kind words and suggestion,
    Ella

    Reply
  7. Chris WebbMay 30, 2008 ב 12:19

    If you were interested in using Intelligencia, I would be more than happy to help you get started 🙂 It’s not a replacement for Report Models though, more of a tool to improve the productivity of developers writing MDX reports in Reporting Services. You can download an eval here:
    http://www.it-workplace.co.uk/IQ.aspx

    Reply
  8. Ella MaschiachMay 30, 2008 ב 17:59

    Hi Chris,

    Very nice seeing you here!
    I’ll download the evaluation package at work and will go over it with the one person that really matters in the office – my boss! 🙂

    Reply
  9. swapnil yadavJune 27, 2011 ב 7:17

    Hi Ella,
    Could you please explain Report Builder 3.0 pros and cons over previous versions ? What are the other alternates available for the same if Report Builder doesn’t fit for any specific requirement ?

    Reply
  10. Ella MaschiachJuly 2, 2011 ב 11:09

    Hi Swapnil,

    I have made a post on that topic of Report Builder 3.0 vs. 1.0. more than anything else, I guess that Report Builder 3.0 (or 2.0 for that matter) wasn’t made to replace Report Builder 1.0. RB 3.0 requires a user that knows how to play with tables and can write T-SQL. In RB 1.0 you give your user a semantic layer he can play with. They both seem to target an entirely different audience. On differences n usability – please check the blog post.

    All the best,
    Ella

    Reply