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 SQL Server MVP and a independent consultant with the title of Business Intelligence/Data Warehouse/Master Data Management Architect and Developer, specializing in the Microsoft SQL Server BI stack.
This entry was posted in Business Intelligence, SQLServerPedia Syndication, SSAS, SSIS. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>