Blog Post

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating