Creating PowerView Reports From Analysis Services Cubes In Excel Using PowerPivot

If you have ever tried to create a PowerView report within Excel that is populated directly from an Analysis Services Cube, then you may have come across a small roadblock where PowerView refuses to work.  All is not lost though, you can trick Power View into thinking that it is querying a real table by using PowerPivot to craft the query first.

The only downside to this is that we don’t have to use PowerQuery as much any more…

Why This Is Useful…

Here is the problem…

How To Do It…

Open Up Excel, and click on the Manage button within the Data Model group of the POWERPIVOT ribbon bar.

When the PowerPivot Manager is displayed, click on the Get External Data menu button and select the From Analysis Services for PowerPivot menu item.

When the Table Import Wizard dialog is displayed, specify the Server Name, and also the Database Name and then click the Next button.

Rather than write the MDX code by hand, click on the Design button when the Table Import Wizard is displayed.

When the designer is displayed, you can just drag and drop the fields over into the canvas and create your query visually.  When you have completed your query, just click on the OK button.

When you return back to the initial page on the Table Import Wizard, just clock on the Finished button to complete the import.

If everything goes well, then PowerPivot will import all the records for you, and you can press the Close button.

Now within the PowerPivot Manager you will see your query directly from the Analysis Services Cubes, and you can close the form.

How It Works…

Now, create a PowerPivot canvas by clicking on the PowerView button within the Reports group of the Insert ribbon bar.

When the PowerView canvas is displayed, you can start adding your dimensions and measures directly from the queried cube.

With a little bit more work you end up with a great looking dashboard.