Benefits of using views in a BI solution

Using SQL Server views throughout a Business Intelligence (BI) solution can provide a tremendous amount of benefits.  Here is a list of such benefits, taken in large part from the excellent video SQLBI Methodology by Marco Russo and Alberto Ferrari:

Benefit of views

  • Can be modified by anyone, even outside of BIDS/SSDT
  • Can provide default values when needed
  • Simple computation can be carried out by views
  • Renaming fields leads to better understanding of the flow
  • Can present a star schema, even if the underlying structure is much more complex
  • Can be analyzed by third-party tools to get dependency tracking
  • Can be optimized without ever opening BIDS/SSDT
  • For security reasons, to limit the rows retrieved by joining with a security table

Benefit of views in SQL Server Integration Services (SSIS):

  • Simpler code inside SSIS packages
  • No need to open the package to understand what it is reading
  • Easily query the database for debugging purposes
  • Query optimizations can be carried out separately

Benefit of views in SQL Server Analysis Services (SSAS):

  • Renaming database columns to SSAS attributes
  • Clearly exposing all the transformations to DBA
  • Simplifying handling of fast variations
  • Full control on JOINs sent to SQL Server
  • Exposing a start schema, even if the underlying structure is not a simple star schema

More info:

Benefits of Creating SSAS Cube from Database Views

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in Business Intelligence, SQLServerPedia Syndication, SSAS, SSIS. Bookmark the permalink.

Comments are closed.