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.
Benny Austin 
On a related note – http://www.kimballgroup.com/2013/08/05/design-tip-158-making-sense-of-the-semantic-layer/
Benny,
I’d be obliged if you would take a look at
http://social.technet.microsoft.com/Forums/en-US/035a03e5-013b-428d-acd9-6d3df72647ff/many-small-reference-tables-should-they-be-dimensions-or-attributes
It poses the kind of questions you address here. FYI, I’m using views to look up the reference tables, substitue values for names, and then send that forward into SSAS and them building attribute hierarchies on the ‘name’ part of the name/value pairs. This issue I have with this is what happens when the text changes?
Cheers, Donna
Hi Donna,
As a design pattern I would recommend just two tables to handle name value pairs – a reference code type table and a reference code table.
The reference code type table stores the list of code sets represented in the data warehouse like gender, employment status, industry code, country code, marital status and so on. In its simplest form this table would have a code type id and code type description. Establish a standard for code type id which will be useful to automate if a new code type has to be created
On the other hand, the reference code table would store all possible codes for each code type and their description. For example male and female for gender code, list of countries for country code and so on. This table should have at a minimum these columns – code type id, code id and code description. You could prefix your code id with the type id to create a pattern for the codes being used in the datawarehouse. For example if gender is represented by code type id 001 in reference code type table, male could be represented by code id 001-M and female could be represented by code id 001-F in reference code table. This table should be refreshed by ETL to account for new code values and updates to code descriptions.
Different source systems may have different ways to represent the same code values. For e.g. HR system might supply gender codes as M and F while sales system could supply gender codes as 01 and 02. In this case you would need a mapping table or MDM system to arrive at the data warehouse reference code which ultimately gets populated to reference code table
In your dimension table use the code id as dimension attribute. Each attribute that is a name value pair is effectively a code type represented by its code id (remember code id is prefixed by its type id). In your example client dimension would have gender id, marital status id, country id and so on – all in one table. In this way you will be able to group related attributes into one dimension instead of having several dimensions with only description as attribute. For instance gender is an attribute of both Client and Staff dimension and is not a dimension by itself.
Create a view for the dim table by looking up the reference code table to get the description as many times as the attributes. Retain the code id for each attribute which will be serve as key column in SSAS cube and the description obtained from reference code table will serve as name column. In this way any change to the code description automatically flows to the cube when processed.
This design pattern has worked well for me in several projects. Hope this helps you as well.
Can you provide an example? It’s not clear. Thanks.