In SQL Server Business Intelligence, why would I create a report model from an OLAP cube?

By : ngm

In Business Intelligence Developer Studio, I'm wondering why one would want to create a report model from an OLAP cube.

As far as I understand it, OLAP cubes and report models are both business-oriented views of underlying structures (usually relational databases) that may not mean much to a business user. The cube is a multidimensional view in terms of dimensions and measures, and the report model is... well I'm not sure entirely -- is it a more business-oriented, but still essentially relational view?

Anyway, in Report Builder I can connect directly to both an OLAP cube or a report model. So I don't see why, if I have an OLAP cube which already provides a business-oriented view of the data suitable for end-users, why I would then convert that to a report model and use that in Report Builder instead.

I think I'm obviously missing some fundamental difference between report models and cubes -- any help appreciated!

By : ngm

In SQL Server 2005 you still had to create a report model over a cube to use Report Builder. RB 2.0 will directly open a cube, although not all available features of SSAS are necessarily supported by RB. This blog entry by Teo Lachev discusses it in more detail.

Generally I would agree that there isn't much point to creating a model based on a cube. I suppose you could use it to hide some aspects of the cube and then you could use role based security to expose different models to users. I don't normally let users outside of BI build reports in SSRS, though, so that wouldn't be a compelling reason for me.

Report models are good if you don't have an OLAP cube. It is a good way to hide the complexity of table joins and fields while providing users with a way to get to the data. It also is a chance to provide friendlier names for business users for fields than how the columns are stored in the database.

Another advantage of an OLAP cube is the calculation engine.

You can have complex calculated measures in your cube that are hard to make and/or time consuming in a relational database, but are well suited to Analysis Services.

You can then expose those calculations to your business users with Report Builder, hiding the inherent complexity.

This video can help you solving your question :)
By: admin