Top 20 exciting features of SQL Server 2012 – Part 2

Introduction

Feature number 6 (Evolution):- User defined roles

Feature number 7 (Evolution):- Windows server core support

Feature number 8 (Revolution):- Tabular Model (SSAS)

Feature number 9 (Revolution):- Power view 

Feature number 10 (Revolution):- DQS Data quality services

Introduction

In part 1 SQL Server 2012 new features we talked about five features Column store indexes,Sequenceobjects,Pagination,Contained database and Error handling. In case you have missed it you can read about the same from http://www.codeproject.com/Articles/526621/Top-20-exciting-features-of-SQL-Server-2012-Part-1

In this article we will talk about the next 5 interesting SQL Server 2012 features:-

• User defined roles.
• Windows Core support.
• Tabular model.
• Power and DQS. 

Feature number 6 (Evolution):- User defined roles

In SQL Server 2008 R2 we had the ability to create roles at database level. So you create customized roles at the database level and then assign them to users. But at the server level or instance level we did not have options of creating server roles. So if you right click on the “Server roles” you will not find any options for adding new server roles. 

Now that’s a serious limitation. Let’s say you have two sets of database user one programmers and the other DBA’s. The programmers should be able to fire insert, update and delete queries while DBA’s should be able to create database, backup and do maintenance related activities. But DBA’s should not be able to fire insert, update and delete queries. But now because you have fixed roles the DBA’s get more access so they can even fire insert, update and delete queries. In simple words we need flexible roles.

In SQL Server 2012 you can create your own role and define customized permission for the role at a more granular level. 

You can see in the below image how you can select permission at a finer level and create customized roles which can be later assigned to a user.

 

Feature number 7 (Evolution):- Windows server core support

This is a small evolution but an important one. Windows server core is one of the flavors of Windows operating system. It is a GUI less version of windows operating system. When you boot with windows core you would be surprised to get a simple DOS command line as shown in the figure as compared to start program files and crowded desktop short cuts. Because only necessary services are enabled, we have less memory consumption, simplified management as many features are not enabled and great stability. When we talk about SQL Server we would love to run it over an operating system with minimal feature enabled. So this is the most welcome feature and on production server using windows core is definitely the way to go.

 

Feature number 8 (Revolution):- Tabular Model (SSAS)

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 from a excel sheet, use my excel formula skills, derive conclusions and publish cubes, so how do I go about it?. My personal belief is that 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.

 

So now if you compare personal users with professional BI the workflow will be following:-

• IMPORT 

1. Professional BI personal will use SSIS, data flows, control flows etc.
2. Personal BI people can use import, copy past mechanism to get data in to EXCEL.

• ANALYZE

1. Professional BI person will uses SSAS , BI intelligence algorithm to do analysis. Once analysis is done they will publish in multi-dimension format.
2. Personal 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

At 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.

 

 

To publish the same to tabular you can click on Build – Deploy tabular project name.

 

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.

 

Feature number 9 (Revolution):- Power view

Every second project I have worked in my life always wanted a system where in end users can go and create their own custom reports. Even though we have a facility in SSRS for adhoc reporting it has huge limitations like you need to install something on the client, works only with windows operating system and internet explorer etc.

Power view is created for simple end user who would like to drag and drop and create their own report using ad-hoc ways. It’s a simple Silverlight plugin which gets downloaded and you get a screen something as shown below. End users can now drag and drop the fields from right hand side, create a report and publish it. Please note end users can not add fields that have to be added from SSRS or Power pivot.

This feature would have been my top feature but due a serious limitation it is not. “Power view only works with SharePoint”….I am sure you are feeling hurt like me. Hope Microsoft makes this independent of share point.

 

If we visualize properly you can understand what the end GOAL of Microsoft is to empower simple business users so that can do BI themselves. So a personal BI user cannot get data in EXCEL, do analysis by using Power pivot and finally create reports using the ad-hoc reporting tool power view.

Feature number 10 (Revolution):- DQS Data quality services

This feature really touched by heart. When we talk about business intelligence it’s all about DATA, DATA and DATA. One of the big problems with data is that it can come in crude and unpolished formats. For instance if someone has entered “IND” and you would like to change it to “India” so that data is in a proper format.DQS helps you build a knowledge base for your data and you can then use this knowledge base to do data cleaning. You can locate DQS as shown in the below image.

 

Once you open DQS you will find three sections as shown below Knowledge base, Data quality projects and Administration.

 

Knowledge base will help you define your validation rules. For instance you can see in the below figure how we are creating a validation called as “CustomerCode” and this validation checks if the data length is equal to 10. 

You can also define correction rules like as shown one below. If you find data as “IND” change it to “India”.

 

Once you have defined you knowledge, next step is to run this knowledge base over a data. So create a DQS project and apply the knowledge base which you had created as shown in the below figure.

 

You can then define where the data can come from and also you can map which columns can have which validations. For instance you can see in the below screen for country and customer we have mapped different domains. Domains are nothing but validation rules.

 

Once done you can start the process and you would see a progress screen as shown below of corrected values and suggested values depending.

 

Finally you can export the cleaned data to SQL Server, Excel or CSV.

 

Next 5 features I will discuss about Always on, Trouble shooting in SSIS, Varying columns, SSIS CDC support and SSIS parameter support.

For further reading do watch the below interview preparation videos and step by step video series.

• SQL Server Interview Questions & Answers
• Learn SQL Server in 1 hour
• Learn SQL Server Step by Step
• Learn MSBI Step by Step
• MSBI Interview Questions with Answers
• MSBI Installation Step by Step