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

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.