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

2
votes

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!

4
reporting-servicesssasolapbusiness-intelligenceolap-cube
The whole point of a Dimensional Database (relational DB as used as a source for cubes etc) is that it SHOULD be understandable by the business users. This can then be refined further using DSV to control security and rename things in a more 'friendly' manner if necessary. [By Dimensional DB I mean a star schema RDBMS.] - adolf garlic

4 Answers

1
votes

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.

1
votes

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.

1
votes

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.

0
votes

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.