This is my personal top feature in SQL Server. Now the main goal of SSAS (SQL Server analysis service) is to do analysis, i.e. Convert data in to information. And SSAS achieves this by creating CUBES from data provided.
So the basic flow goes in 3 steps:-
1.First data is brought to central database (data ware house) using SSIS package. The design of the data ware house system is normally in snow flake or star schema, so that we can create CUBE’s effectively.
2.Later analysis services runs over the data ware house to create CUBES to give multi-dimensional view of the data for better analysis.
3.We can then run different clients like EXCEL, SSRS etc to display data to different sections of users.
Can you guess one big potential problem with the above 3 step approach?. Give a PAUSE and think over it for a minute before you read ahead.
The biggest issue is simple business users CAN NOT CONTRIBUTE TO CUBES. I mean if I am a business user who would like to take data in excel sheet, use my excel formula skills, derive conclusions and publish cubes, so how do I go about it?. My personal belief is the best business analysis can only be done by business end users who actually do business on the field. They are the best people who understand things and can create CUBES which are more useful and logical.
Also if you notice the previous steps its highly technical:-
•Can a simple business user create DB designs like snow flake / star schema
•Can he use the complicated SSAS user interface to publish cubes?
.
•Does he have the knowledge of using SQL Server analysis capability?
Note: - We will change our vocabulary so that we are compatible with Microsoft vocabulary. We will term simple business users as personal users hence forth.
Now personal users work most of the time with EXCEL and if we really want to give analysis power to them, it should be inside excel itself. That’s what power pivot does. Power pivot is plugin which sits inside EXCEL and gives analytical capabilities to simple personal users to do analysis with data they have in EXCEL.
Now EXCEL data is in tabular format with rows and columns. So if you want publish this kind of analyzed data from EXCEL you need to have SSAS installed in tabular mode.
1.
So now if you compare personal users with professional BI the workflow will be following:
•IMPORT
oProfessional BI personal will use SSIS, data flows, control flows etc.
oPersonal BI people can use import, copy past mechanism to get data in to EXCEL.
•ANALYZE
oProfessional BI person will uses SSAS , BI intelligence algorithm to do analysis. Once analysis is done they will publish in multi-dimension format.
oPersonal BI people will use power pivot and excel formulas to come to an analysis. Once analysis is done they will publish in tabular format.
•VIEW
oAt the end of the day both personal BI and SSAS will publish in a CUBE format. So you can view the data from CUBE using SSRS , EXCEL or any other mechanism.
So the personal BI user can use power pivot to do analysis. He can then save the same as an simple EXCEL file.
You can then select import from power pivot, go to power pivot EXCEL file and deploy the same in a tabular format.
Once deployed you should see the CUBE deployed in SSAS as shown in the below figure.
Because the CUBE is created from tabular format we cannot use MDX to query the CUBE. No worries, a new simple query language have been introduced called as DAX (Data analysis expression). You can see in the below figure how I have queried the “Sales 1” cube. DAX query starts with evaluate keyword, brackets and then the cube name.
This article will not go in to DAX as our main concentration is SQL Server 2012 new features.
Here are some SQL Server interview question around database design taken from the book SQL Server interview question by Shivprasadkoirala.
Do visit us for more SQL Server interview questions
