What is Microsoft Analysis Service:
Analysis services are part of Microsoft SQL Server database management system. These services have OLAP and data mining capabilities. This is also known as SSAS( Sql Server Analysis Services).
Why do we need Analysis Services:
1. To get Unified and Integrated view of all your business data.
2. Data Mining, KPI (key Performance Indicator) and OLAP capabilities.
What is difference between Analysis Services and Reporting Services:
1. Reporting services are descriptive and Analysis services are correlative.
2. Reporting tracks while Analysis discovers relationship and dependencies.
Terminologies:
Cube : Basic unit of storage and analysis. It is a collection of data that has been aggregated to allow queries and return the data quickly.
Dimension: Cube has one or more dimension ; each based on one or more dimension tables. It represents a category for analyzing the business data.
Fact Table: Basic information you wish to summarize.
How it works:
We create Unified Dimension Model(UDM) from the relation tables or data source or files. We can use XMLA (XML for analysis ) to analyse the cube. We send MDX (Multidimensional Expression) and SQL queries to get the the data from analysis services.
Understanding Star and SnowFlake Schema:
Star Schema : relational database schema representing multidimensional data. It has dimension tables and fact table tables. Dimension table consists of one or more hierarchy e. g. for time we have year, half year , quarter etc. Fact table consists of few facts and foreign key references to dimension tables.
SnowFlake Schema : If we break the hierarchy in the dimension table to multiple table we get SnowFlake schema.
OLAP Types :
There are three types of OLAPs
1. MultiDimensional OLAP (MOLAP) : Data is stored in multidimensional cube and the storage in proprietary formats. It has excellent speed but it can handle only limited amount of data.
2. Relational OLAP (ROLAP) : Manipulating the data stored in the relational databases. It can handle any amount of data but it is slow because ROLAP make sql queries to the databases.
3. Hybrid OLAP (HOLAP) : MOLAP + ROLAP . For summary type information use MOLAP for Detailed information, we use ROLAP
I tried some hands on how to create a cube using SQL Server Business Development Studion http://technet.microsoft.com/en-us/library/cc280642.aspx
Next Step would be create a cube connecting it to tableau http://www.tableausoftware.com
I will post this in my next post.
