Defining SSAS Data Warehouse

SSAS Cubes rely on relational data structure. Essentially, the SSAS Cubes is a collection of tables and relationships in DBMS. However, nomenclature is different in the OLAP world. We call tables not just entity tables but fact tables and dimension tables.

• Fact Table is a relational database table that houses a measure.
• Dimensions Table is a relational database table that houses a lookup data.
• Combination of Fact and Dimensions can be stored in one table and this table is also called Fact Table.

There are two different data warehouse schemas exist in the OLAP world.

1.      Star schema

2.     Snowflake schema

Star Schema

SSAS Star Schema is designed with one table in the center or SSAS Fact Table and lookup tables or SSAS Dimension Tables linked to this central table.

SSAS Star schemas are easier to visualize and it is faster to retrieve data from the SSAS start schema. However, there is a need to flatten data or de-normalize and it takes more space on the disk. There are products that are configured to use only start schemas and not the others. So it may be a natural choice to use just this type.

Snowflake schema

SSAS Snowflake Schema is any, other than start, schema design. However, it is usually normalized. Data hierarchies are defined with the help of multiple related tables with the help of primary and foreign key relationships. SSAS Snowflake schemas contain multiple fact and dimensional tables. The SSAS Snowflake schemas have few advantages. They require less storage space due to highly normalized structure of its scheme. However, they are slower for data processing due to multiple joins which take most of the computational power. They are also harder to update.