Using views in a datawarehouse

Question

I recently inherited a warehouse which uses views to summarise data, my question is this: Are views good practise, or the best approach? I was intending to use cubes to aggregate multi dimensional queries.

Sorry if this is asking a basic question, I'm not experienced with warehouse and analyis services

Thanks

Answer 1

Analysis Services and Views have the fundamental difference that they will be used by different reporting or analytic tools.

If you have SQL-based reports (e.g. through Reporting Services or Crystal Reports) the views may be useful for these. Views can also be materialised (these are called indexed views on SQL Server). In this case they are persisted to the disk, and can be used to reduce I/O needed to do a query against the view. A query against a non-materialized view will still hit the underlying tables.

Often, views are used for security or simplicity purposes (i.e. to encapsulate business logic or computations in something that is simple to query). For security, they can restrict access to sensitive data by filtering (restricting the rows available) or masking off sensitive fields from the underlying table.

Analysis Services uses different query and reporting tools, and does pre-compute and store aggregate data. The interface to the server is different to SQL Server, so reporting or query tools for a cube (e.g. ProClarity) are different to the tools for reporting off a database (although some systems do have the ability to query from either).

Answer 2

Cubes are a much better approach to summarize data and perform multidimensional analysis on it.

The problem with views is twofold: bad performance (all those joins and group bys), and inability to dice and slice the data by the user.

In my projects I use "dumb" views as a another layer between the datawarehouse and the cubes (ie, my dimensions and measure groups are based on views), because It allows me a greater degree of flexibility.