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

Question

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!

Answer 1

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.

Answer 2

Security and simplicity.

You can use a report model against a relational or a dimensional database to give a user an easier to use view of the data.

You may also wish to secure your database by only exposing a subset of the columns available.

You could change the underlying data source/schema of the model, but keep the model intact, thus ensuring a seamless experience for end users.

Answer 3

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.