CODEWITHSUNDEEP
sqlsql-serverssasolap
stevebot
stevebot

Reputation: 24005

Is it more efficient to build a cube off of tables or views?

I began to ponder this question after I noticed a system that I had been asked to work on had a cube based entirely off of views. I noticed that those views all joined in other tables/views and the views also had logic coded into them (if, case statements aswell as convert statements, concatation etc.). This all seemed horrible to me, but it made me wonder if one should ever base their datasource for a cube off of views?

To me tables make more sense. It prevents expensive joins in the datasource, and also isn't prone to errors that could occur due to conversions performed by a view. However, I still see many people using views as datasources for cubes. Is there a best practice here? Am I perhaps missing some advantages that views give when used as a datasource?

Upvotes: 2

Views: 3074

Answers (3)

Mike Honey
Mike Honey

Reputation: 15027

Views are an important layer to insulate SSAS from the tables - not from column changes so much as from unwanted rows. Without a view SSAS will always process every row in the table. A classic case of this is a dimension table with SCD type 2 rows, where the cube only needs the "current" row. Other common cases are for limiting test data or feeding SSAS partitions.

These views are best kept simple i.e. no complex joins, CTEs, calculated columns etc. Those requirements are best tackled in the ETL layer - they are painful to test/debug/support in the SSAS layer.

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300579

No, it's not faster or slower.

It's usual practice to build a cube off views rather than tables.

It gives a certain amount of insulation against changes to the underlying tables. It allows the design to group information differently to the underlying tables. It allows denormalisation to occur. Just a few reasons.

For example, you can easily change a column name in a view that is there solely to service the cube. Plus, you can name your view columns so they model the domain of the cube rather than the name given in a table.

Upvotes: 1

Nick.Mc
Nick.Mc

Reputation: 19194

If you were to build your cube off tables rather than views, how would you get your data into the tables? The most logical way is to load the tables from the views. Regardless you need some business logic layer to populate your tables whether it's a view or an ETL process.

What that means is that you 'snapshot' your data into your tables at a given time, which might mean yuor data is out of date when you build the cube.

It's really a performance question. It might be beneficial creating tables (or indexed views) as a cube source under the following circumstances:

  1. Your source data doesn't change much (i.e. it's fine to have a daily batch load)
  2. You need to frequently drill through from your cube data to your relational (table or view) data and your views are introducing a performance issue

If you are only using your views to feed cubes (and not detail drill throughs) then you are probably better off with views as you only incur one performance hit when building the cube from the view, rather than incurring one performance hit first building tables from the view and then another (admittedly less) building cubes out of the tables.

Upvotes: 1

Related Questions