Power BI Desktop: Create your first report

Power BI Desktop is a client side report authoring tool which is intuitive and powerful for some stunning Power BI visuals. It allows you to quickly mash up the data from a multitude of sources, shape it and create amazing reports. Below is a step by step procedure of creating your first report in Power BI desktop.

Creating your first report in Power BI desktop:
Install Power BI desktop:  Power BI desktop is a free software. You can install it from here.

Once you install Power BI desktop, launch it and you would see something like this. You can see Report pane (authoring reports), data pane (get data and shape it) and relationships pane (create relationships between datasets) as marked below. The top ribbon will give you options while working on these panes. On the right-hand side you can see visualizations that you can use (you can import more custom visuals which I will explain in separate post), filters you choose to apply on reports and Fields from your data model.
         

'Get Data' option in the top ribbon allows you to collect data from various sources. You could see the sources from where you can get the data, Power BI desktop supports almost all the sources that you can think of.

For examples in this post I am using Excel as source with World Population dataset downloaded from here.
Once you load the data from Excel, you can see the data in the data pane as below. But first we have to shape/transform the data. We would like to un-pivot the data and create a year column so that we can use it in various analysis of how population has grown over years etc.
To do all these transformations we have a powerful tool in Power BI suite - "Power Query". To transform using click on "Edit Queries" option.



It opens a pop up window like below and you can see lot of transformations that can be applied on your data. In my case, un-pivoting is very easy. Just select the columns you want to unpivot, in my case last 4 columns as shown below and click on 'Unpivot columns' in the top ribbon.


..and here is the output I wanted, just renamed columns to 'Year' and 'Population' from pre-populated names attribute & value.


Just Save it and close the window. Now you should see the data in your 'Data pane' transformed to the unpivoted format  and ..... your data is ready to visualize.

Visualizing the data:

Go back to the Report pane, the first icon on the left hand side as highlighted below. Now you should see your dataset in the Fields section (far right). Just drag the columns into the pane and you should see a table with your data.



You can change the visualization just by selecting the table/chart and clicking on another visualization in visualizations section. I have changed the chart to a line chart and only selected few countries for analyzing. Observe the Filters pane and others such as Axis, Legend etc. from the below screen.



Now your first sample report created in Power BI desktop is ready. You can create as many visuals as you want in this pane just by dragging the required columns again and creating other visuals and aligning them properly as below.



This is how you can create a simple report using Power BI desktop. I will cover in-depth features in the coming posts. Stay tuned!

Power BI - Tennis Grandslam analysis using Power BI

During one of our analytics summits' visualization competition, we were given a fictitious grand-slam dataset to model, analyze & visualize. Below is the analysis I have done using only Excel 2013 built-in components -  Power Pivot and Power View which got me great rewards and recognition :)

I have divided my analysis into few levels - Tournament level, Individual game level and Player level. And finally provided few insights into how and why people are winning. Below are the screenshots. Remember, all this is done inside Excel environment :) (no macros, no complex coding, nothing).
 
1. Tournament Analysis: This screen will allow us to analyze at tournament level. See the four tournament images on the right hand side as slicers (T.Image section). You can select one and view the details of that tournament.
Showing you the winner details, breakpoints details, serve won percentages and competitiveness based on the average sets played per game in the tournament.



2. Individual game analysis: This will give you a detailed game level analysis. See the game slicer on the right hand side. Generated a game code using some formulas, (AU_M_ROUND 1_G-1 means Australian Open, Male, Round 1, Game 1). And then you can see the player details and who won and compare their performance as well in all aspects of the game.



3. Player Analysis: This is a player analysis across grand slam tournaments. See the four images flowing at the bottom, these are used as slicers, You click on one of them to analyze players performance in that tournament. Look at the detailed analysis at round level and the winning status.




4. How to win?
This screen gives some insights based on the data available.
For ex, the top ribbon says 91% of matches are won by those who won more break points, 62% by those who aced more etc.
And the how these aspects are changing across tournaments and top players performances.


5. Top Players:
This screen gives you the top players information in several aspects of the game. 


I will explain in detail on how to create these in coming posts. Do not forget that all this is done inside excel workbook:)

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.

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.

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

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.

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.

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.

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.


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.