Microsoft Analysis Services

Next week onwards, I have to work on Project which required knowledge of Microsoft Analysis Services. so I thought of finding about Analysis Services:

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.

ShortCuts

VIM ( Vi Improved)

This is one of the most useful editor comes in Unix . It can be used as editor for coding in perl , c , c++ even some people use it for Java. GVim ( Graphical Vim ) is also available.

Open a file using VIM and how to edit it

vim filename
vim -g filename ( g switch is to open the GVim )

You can also adjust the size of the window ( height and width) in which Vim opens the file.
vim -g -geom 84x67 filename
now this opens the file in a window of size 84x67 .

There is .vimrc file configuration file for vim either set the geometry option in the .vimrc file or
create a n alias say v for the command and use as
v filename

Moving Around : h ==> left
l ==> right
j ==> down
k ==> up


To edit the file go into the insert mode . To enter in the insert mode press i . Now u can add to the file .

Cut , Copy & Paste

v ==> enter into visual mode
select the text u want to copy or cut
yy ==> copy
x ==> cut
p ==> paste

Saving the file

:w ==> save
:wq ==>save and quit
:q! ==> quit without saving


Programming Specific ShortCuts

open new tab ==> :tabnew
go to next tab ==> gt
go to previous tab ==> gT
go to begining of the line ==> ^
go to end of the line ==> $
go to end of the file ==> shift + g
go to start of the file ==> gg
undo ==> u
erase current line ==> dd
paste after the current line ==> p
paste before the current line ==> P
find ==> /exprr
find from end ==> ?regex
next occurence of the find item ==> n
previous occurence of the application ==> N
autocomplete ==> ctrl + p/n
go to variable definition==> gd
go to matching parenthesis ==> %
reindent the whole file ==> ggVG=