While SSAS allows you to build a cube straight of the fact and dimension tables, however there are benefits in creating the cube from database views that sits on top of the underlying fact and dimension tables. Here’s why.
1. Allows use of reference tables or MDM for code descriptions
It’s a good modeling practice to store code and description in reference tables or in Master Data Management (MDM) systems especially if they are name-value pairs E.g. occupation code and description, employment status and description, country code and description, post code and suburb, diagnosis code and description. In such data warehouse systems, the dimension tables store only the code values and the description required for the cube is obtained using a database view on the dimension table that joins to the reference table. In this way changes to the code description flows through to the cube when it’s processed next time.
2. New attributes can be derived from existing ones
Having a database view on top of dimension tables helps to create new attributes from already materialized base attributes. For e.g. some prefer fiscal year to be displayed as FY2013 while others prefer 2012-13. Similarly new flags can be created which simplifies cube browsing. For e.g. based on the location the house can be classified as high risk or low risk to natural calamities. It must be noted while SSAS Data Source View (DSV) also allows to create named calculations, unlike database views DSVs are not re-usable. DSVs are however a second best alternative if you don’t own the database model and unable to make DDL changes (for e.g. a third party application).
3. Hide attributes to enhance cube browsing experience
Attributes such as specialized sort keys could be created in views on top of dimension tables. These attributes may not add business value as such but will definitely enhance the cube browsing experience and can be hidden in the dimension.
4. Exclude Unused Dimension Members
Not every dimension member is associated with a fact record. These unused dimension members are an eye sore and could be excluded using database views which will simplify the cube browsing experience thereby encouraging self-service BI.
5. New measures can be derived from existing ones
It’s not just the dimension tables that benefit from having database views. Fact tables also benefit from having database views. New measures with simple calculation can be derived from existing measures by creating database view on Fact tables without the need to materialize the new measure and make subsequent ETL changes. E.g. new.measure 4= (existing.measure1 + existing.measure 2)/ existing.measure 3. The new measure is a pre-aggregated measure and performs faster compared to an equivalent calculated measure in cube.
6. Security Mechanism
By creating database views, power users can be granted access to the de-normalized views instead of the underlying fact and dimension tables.
7. Semantic Layer
Ideally the table and column names would be the names that business users are familiar with. In instances where this is not the case, database views creates an opportunity to build this semantic layer.
When creating a SSAS DSV from database views, you have to re-establish the reference keys even though the underlying tables may already have foreign keys defined. This is an additional one-off step that must be done when using database views.