SQL Server Analysis Server

Now we reach the core of the BI solution. Now the Application we build is making a progress. why all this cheer, because we reach this component SSAS. In this module we will analyze the system from different levels. We can make several decisions. We can see the Life analysis of the application. Now I stop being talkative, and begin building a cube practically.

take a look on the SSAS:
















before we proceed, there is a question

• What is the Cube?
• Why we need it?
• How to build it?
• Troubleshoots...

What is the Cube?

the cube is Fast Analysis of Shared Multidimensional Information, allowing for complex analytical and ad-hoc queries with a rapid execution time.


Why I need it?

it is now explained in the what..

How to build it?

the main concept of the cube, that the cube consists of two main things:

1. the Fact Table
2. the Dimensions

the fact table consists of 2 things: relations to the dimensions, and aggregated values related to this relations which called Measures.

the dimension tables consists of 2 things also: hierarchy, and attributes.
the hierarchy is the logical hierarchy of the data within this dimension. Like Product under Product Categeory under Product Line. and the attributes will be like: the class and the type and the size, ...etc

YOU HAVE TO BUILD YOUR OWN FACTS AND DIMENSIONS ACCORDING TO YOUR BUSINESS.

There is 2 main preferred schema's for building the cube:

the Star Schema like the figure below











which all the dimensions are denormalized and all have one relation to the fact table.

the second one is the the snow flake Schema:







which some tables have relations with each other.

why we may use the 2 schema's:

If your main concern the time and no concern for the size of the data, use start schema.
If the data is very very large when you denormalize it, and no way to minimize it, Use the Snowflake schema.

Now you have a Cube!

Next time we continue exploring the cube studio, and troubleshoots.