1 Power View enhancements with SQL Server 2012 SP1 and Excel 2013 - Part2

This post is continuation to my earlier post on Power View enhancements came up with SQL Server 2012 SP1. In this post, I am going to introduce the much anticipated features like KPI's, Hierarchies, and drill down/ups in the power view reports. In this post also, I am not going to give much explanation as you can understand better from images.

1 Power View enhancements with SQL Server 2012 SP1 and Excel 2013 - Part1

Update: I have written part-2 of this artcle which you can read from this link.
Few days back, SQL Server 2012 ServicePack1 was released. It came up with many new features. In this post I'll concentrate on the new features it has brought to the power view (a web based reporting tool which can be launched from SharePoint / it's now also there in Excel 2013) visualizations. I'll take you through them with the screenshots of the reports I have created. I am not going to give much explanation as you can understand from the images.

0 SQL Server connection error: A connection was successfully established but then an error occured during login process

Using SQL Server Management studio (SSMS), I have created a new login with user name and password using SQL Server authentication. When I have tried to connect to SQL Server with the newly created login, I get the following error.
Error: A connection was successfully established with the server, but then an error occurred during the login process. (provider: shared memory Provider, error: 0 - No process is on the other end of the pipe).

0 Getting FACEBOOK friend suggestions using T-SQL

Suppose a business has a database containing the users and their friend's details. As in facebook, we may want to identify the friends of our users and send the notifications about product details to them.This post shows how to get the friends of our users up to the required level using T-SQL.

0 Deep into indexes 2 - How indexes can degrade performance

As we saw in my earlier post, indexes are organized as B-Trees and they make the accessing or retrieving the data faster and easier. But the insert part is trickier which I am going to explain in this post.

0 Deep into SQL Server indexes 1- B-Tree or Balanced Tree

Indexes organize the data in the table in a particular order to make accessing the data easier and faster. As we know, they are crucial in performance tuning exercises. However we need to be careful while creating and using indexes as they can degrade the performance also. I am going to write a series of posts on my understanding of indexes.

0 DAX Studio - A designer to query tabular models

With SQL Server 2012, we (BI developers) have a choice to choose between multi-dimensional and tabular models. Also we have a choice to choose the languages, MDX or DAX. We can query multi-dimensional and tabular models using either DAX or MDX.

0 Events and Streams - Stream Insight

Before reading this post you should read my earlier post on Stream Insight architecture and basics. As I said earlier stream insight processes streams of events coming from multiple data sources. To work with Stream Insight it is important to know about streams, events and how they are structured.  In this post I am going to write on streams, events and their structures.

0 Complex Event Processing with Microsoft STREAM INSIGHT

Sometimes we need to work with the data as it comes (in real time). Few examples I can outline are,
  • sensor-based observation, where have to react as soon as we know something unusual/wrong happens
  • Monitoring and capitalizing on current market conditions with very short windows of opportunities.

0 New to Power Pivot for Excel ?- 5 easy steps to start with

Recently, I have got a mail from my friend asking for posts or resources to learn Power Pivot . So I have decided to dedicate this post to provide the best possible links/resources (to my knowledge) to learn power pivot quickly and work with it on your own. This post should guide anyone who are new to Power Pivot as I have learned this way.

4 Custom sorting the slicers in Power Pivot for Excel 2010

Thanks to pscorca from msdn forums for steering me to write this post. He asked this question in the msdn forums which was a bit interesting so I decided to work on it.  The actual problem here is,  inside excel there are options to sort alphabetically (A to Z / Z to A) and sort as per source order. There is no option to custom sort directly in Excel so I decided to solve the issue from the source side as there is an option to sort as per source order. Lets see how we can use this option to sort the slicer in a custom way as per our requirements. 

0 How to get the SQL Server Instances available in my server?

As humanbeings, we sometimes tend to forget small things such as the instance names of SQL Server (or) you have been assigned a new SQL Server machine to work with but you don’t know instance names. Whenever you try to connect to SQL Server you get the below error.

0 sys.dm_db_partition_stats DMV- Row count of all tables in a database with schema names

Dynamic Mangement Vews and Functions (DMVs and DMFs) are very much helpful for the developers to get the internal and instance specific data. There are almost 140+ of those which can be advantageous while working  with SQL server. You can get all the DMV's by writing this query.
select * from sys.system_views where name like 'dm%'

1 PowerView vs Other reporting tools

With the release of SQL Server 2012, Microsoft introduces an excellent user-friendly reporting tool called "PowerView". PowerView is more striking compared to other reporting tools in the following aspects; Visualization, Performance, Interactivity, Intuitiveness, Presentation. In this post, We will delve into each of those aspects.

0 Types of concurrency control in SQL Server

Whenever many people are accessing the same database at the sametime, the DBMS should take care of all the transactions in a way that no transaction could adversely affects other transactions. This is called concurrency control. Normally this would be done using locking and isolation levels. Locking means, Whenever a user accessing a resource that would be locked until he completes accessing so that others won't make changes to that resource. I am planning to write some articles on locking and isolatio levels in depth later. For now I am concentrating on types of concurrency controls on databases.There are two types of concurrency controls; pessimistic and optimistic.

0 How Excel works with Tabular models?

We know how to interact with the analysis services using Excel 2010. Since Excel uses multi-dimensional interface it works with multi-dimensional models quite comfortably. But what if it needs to interact with tabular models. Even if we connect to the tabular models do they understand MDX queries which are sent by Excel 2010? Open Excel 2010 and go to "Data" tab in the top ribbon and select "From analysis services". The only change when it comes to tabular model is you should provide the tabular instance name of SQL Server 2012 as the server name. Then you would get to see all the tabular models you have deployed to that instance. In the 3rd screen shot below, the field list displays all the tables I have used in the model and measures, hierarchies, KPIs etc which is the same representation when you connect to multi-dimensional models too. Go on and create charts/tables as you do with cubes. Then whats the diffrence?

0 Hybrid nature of Business Intelligence Semantic Model

In my earlier post we have seen how end users can interact with the models in a common way irrespective of the type of model (tabular/multi-dimensional) because of the hybrid nature of BISM. Here I am going to show you the hybrid nature of BISM with a great example. I am going to interact with the same tabular model with Excel and PowerView and show you the hybrid nature of it.Interacting with tabular model in Excel:

0 What is Business Intelligence Semantic Model?

SQL Server Analysis Services from Microsoft is one of the best BI platforms in the market. But there are some issues with this. One of them is it only supports multi-dimensional modeling which is not easy for the developers to design, more over it uses MDX (Multi-Dimensional expressions) to query the model which is very tough to learn. To overcome this, with SQL Server 2012 Microsoft introduces tabular models (relational) which are easy and flexible for the developers to understand and develop. Tabular models use DAX (Dynamic Analysis Expressions) which is easy compared to MDX.

1 SQL Server 2012 evaluation edition released...Download it now

The much awaited SQL Server 2012 was released yesterday. For more information check out this official blog post. You can download the evaluation edition of SQL Server 2012 here. It is coming with a ton of prominent and exciting features. As I have been working on BI part, this post gives a good initial look at new features in BI part.

1 MDX PART3- DESCENDANTS(), self_before_after

Before reading this post you should read my earlier posts in this MDX series (you can get earlier posts by clicking on MDX label). In this post we are going to look at an important function in MDX--- DESCENDANTS (). As demonstrated in my previous post, using MEMBER & CHILDREN we can't drill-through all the levels in a hierarchy. To accomplish this, DESCENDANTS function is required. This function allows one to go to any level in depth.


Before reading this you should read my earlier post on MDX basics. In this post we are going to look at some more useful and important functions in MDX like CHILDREN, DESCENDANTS etc. In the earlier post we have used MEMBERS function. It returns the members of specified dimension or a dimension level.


SQL Server Analysis Services (SSAS) provides an easy way to analyze the data you get through transactions. SSAS organize and stores the data in a multi-dimensional structure/cube for rapid response to user queries. To query those multi-dimensional structures SSAS provides a full-fledged language with highly functional syntax called Multi-Dimensional eXpressions (MDX). I am planning to write a series of articles to showcase the capabilities of MDX.

3 SQL Server2012-Security in BISM tabular model

This post shows you the security features of BISM tabular model in SQL Server 2012. Tabular models in SQL Server 2012 support two types of security; Role based security, Row level Security.
Role based security (Data base level security):
Role based security works the same way as it works in traditional SSAS (UDM). Using this you can allow several members to access your model from client tools. To create roles in tabular model go to Model->Roles.

0 MultiDimensional modeling-Simplifying a Data Source View with named queries

While developing a cube in UDM we have to create a data source view which contains the metadata of the data we use in the cube for analyzing. A data source view (DSV) is a logical view of the data in the data source. Any changes made to a DSV will not reflect in underlying data source. Model only see the DSV but not underlying data source. We can create new calculated columns, Named queries, logical primary queries with in this DSV.

0 Security in SharePoint 2010

In this post I'll give you an over view of security in sharepoint 2010. This doesn't give you complete deatils on security but some important and more useful security details. This post covers security for site collections, sites in a site collection and adding security from central administration etc.
Security for a site collection:
Open a site collection and go to “Site Settings -> Site Permissions”, we can see all the SharePoint Groups and SharePoint users. This list also provides the permission level of the groups and users.

Business Intelligense News