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