Power BI and SSAS Tabular conversion

One of the most compelling aspects of the Microsoft Power Pivot tool is the ability to take a workbook that has been developed as a quick prototype in Excel and promote it to a corporate SSAS Tabular model.

This way a business team is able to ‘self serve’ their requirements with Power Pivot and deploy to SharePoint or their Power BI Site.  The monitoring tools as part of these sites can pick up on important and heavily used workbooks and make them visible to IT as candidates for bringing into the corporate managed server tool set through SSAS.

You access this functionality in SSAS by starting a new project in SSDT and selecting Import from PowerPivot

Power Query

To enhance users ability to self serve their BI requirements Power BI also provides a tool called Power Query.  This tool acts as a data cleansing  and ETL tool allowing business users to gather data from any available source, process it and then integrate it as part of a Power Pivot model.  This tool is very flexible and easy to use, it is able to get data from nearly any website, Twitter, Facebook and, in the case shown below, Azure Blob Storage.

The Load to Data Model checkbox allows us to directly bring this data into Power Pivot and build rapid prototype analysis over this data.  This opens up a large number of business user cases to integrate on prem data with Cloud data and openly available web data from outside the organisation.  Really interesting analysis such as comparing spend on sales promotions to social media activity can then be performed by Excel business analyst from the desktop with just an Office licence.

Current Limitations

Unfortunately this Power BI story is currently missing the final pieces to close the loop, I’m sure these will come but you need to be careful with how you use them at the moment.  The current situation is that if you use Power Query load to data model then any upgrade of the model from Power Pivot to Tabular will be problematic.  Depending on the type of Power Query source you are usually able to import the metadata of the Power Pivot model but you will not be able to refresh and data through the Power Query connection manager defined.

Connection as seen in Tabular:

In my local environment trying to review or change the source of an SSAS table that was converted from a Power Query gives the error:

The ‘Microsoft.Mashup.OleDb.1’ provider is not registered on the local machine.

This means although I can bring in the metadata I’m unable to swap out the source query in the UI and so will need to delete and re-add the data manually – thus losing the benefit of the import from Power Pivot.

Possible workarounds

Workaround 1

It is possible to load Power Query results to a worksheet rather than directly into Power Pivot (the Data Model).  It is possible to bring some data in through Power Query and still have an updatable model by performing the following steps:

1. Build a Power Query and save results to worksheet
2. Save worksheet out as .csv
3. Build a new Workbook containing Power Pivot Data model
4. Import the .csv as a table from a text source
5. The .csv file source will continue to be refreshable in SSAS Tabular and it will be possible to edit the table source.

Workaround 2

Wait, I know Microsoft are looking to improve the integration of Power Query so we must hope that small issues like this will go away with future releases.

I would not really expect SSAS to be the right tool to embed Power Queries into in the corporate BI world so really what is missing is the piece of functionality to easily bring Power Queries into SSIS so they can be integrated into warehousing architectures.

In the mean time be very careful about mixing Power Query as part of the same architecture as SSAS Tabular models as they are not currently easy bedfellows.