Microsoft Excel - Power Pivot

Microsoft has provided excellent feature in MS-Excel which enables the BI developer/Analyst to dig into the corporate data with different view to help in decision making. Microsoft introduced use of power pivot in MS-Excel 2010 and I would like to give some of the basic feature in this post.

Excel can connect different database to pull the records and for this demo we can connect excel with the sample database AdventureworksDW2008 provided by Microsoft. I do not want to connect the tables directly so instead we can use the below query.

select OrderQuantity,SalesAmount,Englishproductname ,c.englishmonthname,c.FiscalYear
from dbo.FactInternetSales a
inner join DimProduct b on a.ProductKey = b.ProductKey
inner join DimDate c on a.OrderDateKey = c.DateKey

Open Excel and connect to SQL by selecting From SQL Server option from From Other Sources of data menu as showned in the figure 0.1

 
 
 
 
 
 
 
 
 
 
 
Fig 0.1

 
 
 
 
 
 
 
 
 
 
 
 
 
 
Fig 0.2
 
Select the database and the view created to fetch the data from the table as showed in Fig 0.3

 
 
 
 
 
 
 
 
 
 
 
 
 

 

Fig 0.3

Once the Excel is connected to the view created it looks like below figure

 

 

 

 

 

 

 

 

 

 

 

 

Fig 0.4

 

Now we can easily create pivot table or chart by selecting the fetched data and use the option given in the insert menu.

 

Fig 0.5

We can create the Pivot table either on the same sheet or different sheet and here I am choosing new sheet for easy analysis.

Look at the figure fig 0.6, we can see report layout at the right side of Excel which displays the aggregated details depend up on the field selection from the options used in the option window. We can hide or unhide the options window by right click over the layout window.

Fig 0.6

Now drag and drop the fields displayed into the   options available.

Fig 0.7

Value field always contains the numeric fields and we can use column and raw fields to analyze the measure value for the different dimension. Microsoft provides lots of option to ease the analysis which we can take right clicking on the pivot table. Keep watching my next posts to find out more information.

 
 
 
 
 
 
 
 
 
 
 
Fig 0.8
 
We can convert this into graphical representation by selecting pivot graph to have visual look.