Star schema and Snowflake schema in SSAS 2014

Below table will show the difference between the Star Schema and Snowflake Schema.

Star Schema: Each and every dimension present in the Data Source View (DSV) is directly linked or related to Fact or measures table.

Snowflake Schema: Some dimensions present in the Data Source View (DSV) are linked directly to the fact table and some dimensions are indirectly linked to fact tables (with the help of middle dimensions). For instance in [Adventure Works DW 2014], [Dim Product sub category] is indirectly related to [fact Internet Sales] with the help of [Dim Products]. 

STAR Schema	Snowflake Schema
Centrally located fact table surrounded by DE normalized dimension table	Centrally located fact table surrounded by the normalized dimension table
In Star Schema, All dimensions will be linked directly with fact table	In Snowflake Schema, some dimensions are linked directly to the fact table and some dimensions are indirectly linked to fact tables (with the help of middle dimensions)
It is easy to understand the design	It is difficult to understand
Increase the query performance because we can extract the data with less number of joins	We have to join more tables to extract data so more joins
We mostly see the STAR Schemas in small data base or we say small companies	Most of the Big Companies or any big database will always belongs to Snowflake Schema.
By DE normalizing the database we can convert the Snowflake Schema to star schema	By normalizing the database means dividing the table’s data further using primary and foreign keys we can convert the star schema to Snowflake Schema easily.

STAR FLAKE: A hybrid structure that contains a mixture of star schema (DE normalized data) and snowflake schema (normalized data).

STAR SCHEMA in SSAS EXAMPLE

We can see from the below figure [Dim Production], [Dim Customer], [Dim Product], [Dim Date], [Dim Sales Territory] tables are directly attached to [Fact Internet Sales]. So, this is the perfect example for the star schema in SSAS

SNOW FLAKE SCHEMA in SSAS EXAMPLE

We can see from the below figure [Dim Currency], [Dim Customer], [Dim Date], [Dim Product] tables are directly attached to [Fact Internet Sales] but [Dim Product Sub Category] and [Dim Product Category] are indirectly connected.

Thank you for Visiting Our Blog

Create OLAP Cube in SSAS

In this article we are going to show you, How to create OLAP cube in SQL Server Analysis Services (SSAS) using cube Wizard.

Cube is a combination of Measures (Derived, Calculated Measures) and Dimensions, calculations, perspectives, actions, partitions, key performance indicators (KPIs) and translations.

Create OLAP Cube in SQL Server Analysis Services

Within the Solutions Explorer, Right click on the Cube folder will open the context menu. Select New Cube option as shown in below screenshot.

It will open the Cube wizard with the welcome page. If you don’t want to see this welcome page again check the Don’t show this Page again option below.

Click next button

Select Creation Method

Within the cube wizard this page will give you 3 options to choose

• Use Existing Tables: If you select this option, then cube will use the existing tables in the Data Source View.
• Create an Empty Cube: This option will create an empty cube with no Measures and Empty Dimensions. After completing the cube creation we have to add them one by one.
• Generate Tables in the Data Source: It will generate tables in the Data Source directly.

It is always better to choose the option (Create an Empty Cube), that’s what I did in my experience and I hope this is the best approach for any developer. Click on Next Button

Select the Data Source View

This page is used to select the Data Source View for our Empty cube. Here we can select the data source if we already created or else we can select none and later we can create and use it. Again its developer’s choice but it is always good practice to create the data source and data source views at the beginning.

Here we are selecting the Data Source View we created in the previous article. Please referSSAS Data Source View article to understand How to create the Data Source View in SSIS

Click Next button and rename the cube as per your requirement.

Click finish button to complete the cube creation.

Lets look at our Solution Explorer to check for newly created Cube.

Creating Cube Using Existing Tables

Here we are selecting the Existing Tables option to show you, How this gone work.

Click on the Next button

Select Measure Group Tables

Select the already created (existing) Data Source View. Here also we are selecting the Adventure Works DW2014, which we created in our previous article.

Now select the Measure Tables (Measure Groups or Fact tables) from the list of available tables in the data Source View.

Click next button.

Cube wizard will take you to next page to select the individual Measures from Measure Groups.

Here we selected some random Measures and click on Next button.

Select Existing Dimension

This page allows us to select the required dimensions for cube creation from the list of available dimensions

Click on next button and provide the cube name as per your organization requirements.

Click finish button and see whether we created the cube successfully or not

Thank you for visiting my Blog