One thing that Microsoft has always done well is get its wide variety of services and applications to work together seamlessly (well, relatively seamlessly at any rate). We have previously looked at Power BI and noted that Power BI exploits the same in-database Analysis Services engine that powers the Excel Power Pivot data model. Many Excel users routinely use Excel’s data model as a source for pivot tables. It is also possible to use Excel to create pivot tables and pivot charts from data maintained in a Power BI data model. This can be extremely valuable if an analyst becomes curious about a trend observed in a Power BI visualization and would like to probe further using pivot table reporting.
If you already have a Power BI report open, there will be a button at the right-hand edge of the browser window that reveals “Analyze in Excel” on its context menu.
Alternatively, you can right-click on the report of interest in the Navigator pane to bring up a context menu with “Analyze in Excel”. Ultimately, we will want to download the odc connection file that will be generated for us, but if we do not have the appropriate Ole Db provider, Excel will not recognize the information in the odc file.
Clicking on the “Analyze in Excel” menu choice will bring up an invitation to download the latest Analysis Services OleDb provider.
Excel 2010 SP1 or later is supported. Running the downloaded msi file is trivial and requires no decisions at all.
After installation, we can download the odc file, which will have the same name as our Power BI report, but with the “odc” extension. This file contains all the necessary information for Excel to connect to the Power BI server and download the required data. You can install this file in any directory you like, but you might like to install it in the “My Data Sources” folder in Documents folder, which is where Excel looks first for odc connection files.
Once we have the connection file, we are ready to create a pivot table, but there is a catch.
Power BI is smart enough to create a sum on numeric values if no measures have been defined. However, using the Analysis Services Ole Db provider, we can only use measures that have been explicitly defined in Power BI. In this case, DAX is not just for custom measures, we must even define simple sums on the columns of interest.
Designing measures in DAX is a fascinating topic, but not our interest here, so we will create nothing more than a simple sum. In Power BI desktop, you can click on the “New Measure” button, but it my opinion it is a bit better to right-click on the table name in the Fields list and choose New Measure. This will ensure that the measure is created where you want it and prevent inadvertently defining the measure in some other table.
Now that we have defined a measure, we are ready to create pivot tables in Excel. There is nothing new here. After clicking Insert Pivot Table, you will choose Use an external data source. If you have not saved the downloaded odc connection file in the default location, you will need to browse for it the first time. After that, there will be a shortcut in the default folder.
Once we have connected, we see the measure we created at the top of the field list, exactly where we would expect it if we were connecting to a classic Analysis Services multidimensional database.
The potential for using Excel pivot tables to drill-through a Power BI data model adds greater value to maintaining server-based Power BI reports.