Using views as a source

"Is it ok to use a SQL Server database view from the source system as the source for your DWH, or is it better avoid views as a source, using only tables?".

I had this discussion recently with a BI consultant. His point of view was: use the raw tables underlying the views as the source and duplicate the definition of the views from the source system in the DWH layer if the view is important for the business.

This triggered me, as I see views as another piece of data that I can use, just like any table. Why should it be a bad idea to load data from the views on the source system?

I think it all depends on the situation.

Data views as an interface

Views can be very handy to expose data to other systems. Views can be kept consistent while the raw tables can change as the source application changes over time. If the views that you want to use are created and used for this purpose, then by all means: use them in your DWH.

Example: a view that denormalizes source tables to create a customer view.

Views as reports

It is a different case when the view is used for reporting purposes.
If the view is created to support a report, it might mean that the business wants to see this data in a report in the BI solution. For example: a view that shows customers, products and sales.

In this case it might make more sense to create a similar view in your DWH and use the views/tables for customer, products and sales to extract the data from.

 

Principal BI consultant at Rubicon

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next ArticleIssues (& solutions) with Power BI Analysis Services Connector