11/26/2014

Latest Power Query Connects to Analysis Services

Power Query for Microsoft Excel now supports connection to SQL Server Analysis Services cubes.

You can download the latest release here:
http://www.microsoft.com/en-us/download/details.aspx?id=39379

Upon loading Microsoft Excel, it will prompt you to close programs while it installs the new features.

Once loaded, you can see the Power Query tab across the top.  I'm using Excel 2013, so it was already there, just updated to a newer version.


We'd like to get some external data from our SSAS Multidimensional cube, so we click on the button "From Database Get External Data":

Then we select "From SQL Server Analysis Services".


We locate the name of our Analysis Services Server.

Next we're prompted to Use Your Windows credentials to access Analysis Services, click SAVE:


On the right hand side of Excel screen, you'll the Navigation pane containing our SSAS Multidimensional Cube:

We selected Measures and a Dimension and clicked the Load button, it starts chugging away, to pull the data into our Excel Workbook solution:

After a brief moment, whallah, we have data from our cube:


Now you can edit the query by clicking the Edit Query button, on the right hand side, you'll the list of steps used to pull in the data:


We then clicked the Settings button to the right of the Added Items and a window pops up:


Added another field called Orders Count and the field got added to the list of data:


By clicking the down arrow on any of the fields, a window pops up to sort the data, or remove specific entries:


Click the Close and Load button:


Excel re-loads the data containing the new field:

And the field now displays in our Workbook:


No MDX required!


We select the "Load to Data Model"... Click Load... we get a warning box:

Click continue, it loads data into a Model:

Then hovered over the Workbook Queries, right clicked, and said "Send to Data Catalog"


After supplying credentials, a screen pops up:

And the Sharing screen:


Notice the box in Yellow, '"This query uses a local data source and may not be available to others".

I publish and log into PowerBI.  Navigate to My Queries and sure enough, there it is...


I click the ellipses...

See the Analytics...


I don't have a Data Management Gateway established at this time, so that's as far as I'm going to take it at this point.

Hope you enjoyed and learned a bit.

Happy coding~!