Saturday, October 29, 2016

Excel PowerPivot.: How to execute SQL query inside PowerPivot

Power Pivot is one of the best faeture in excel. Though PowerPivot can be used to serve different purposes, however; this post only include: how you can run your SQL code into Powerpivot and manipulte the data into the excel?

Why?


If you use Excel Power Pivot as front end tool then by implementing this process you can avoid asking developer a new excel sheet each time the data is updated.

How?
Before you start you must need to have Powerpivot enable in your excel, if so then your excel Tabs are like below:
Fig 1: Different tabs in the excel

If you don't find the tab 'POWERPIVOT' in your excel then you need to work a little more to add that. Please follow the below link to make it work: 


Step 1 :Make connection with your data source 

Fig 2: Manage option under POWERPIVOT

After you click the manage window you will find a popup window which like below, from there you will have option to connect with data source.


Fig 3. Connect with the data source


You need to follow the wizard to complete the connection. 

Step 2: PowerPivot Design

After you establish connection with the data source, then click on Design tab as like below


                         Fig 4: Design Tab from powerpivot manager

Under the design window you wil find following Tabs like picture and you need to choose 'Table properties'


Fig 5: Design- table properties


And then you need to click Table properites where you will write/paste the SQL code to generate the data directely from the data source.


Fig 6: Add SQl to the table property window

After putting the SQL code in the above window,  Please click 'validate' button before you click the 'Save' button; it will validate your SQL code. Now press 'Save' button then SQL query will run and get the data from the database and populate the data in your excel.