Oct 18

Oct 18 Converting a Power BI Dataset .bim to a .pbix with pbi-tools

GETTING A .PBIX FROM METADATA

…so you can download .pbix datasets previously inaccessible by other means.

Special thanks to Mathias Thierbach for helping me experiment with this when I had a need, myself.

Note: This details an approach involving decompiling a .pbix, modifying its contents, then recompiling it. This is currently not supported by Microsoft. Take a back-up of any dataset handled with this approach in case you made a mistake and disrupt your file; this approach does work, but is executed at your own risk.

Can we convert model metadata to a PBIX using the tools available, today?

NEED A PBIX FROM THE SERVICE?

WHAT ABOUT A PBIX FROM A .BIM FILE?

Once a dataset is deployed into Power BI Service, you aren’t guaranteed to be able to download it back as a .pbix or .pbit. There are various reasons why this might be the case:

A feature was enabled that limits download
The dataset was modified via XMLA endpoint
(i.e. Tabular Editor, ALM Toolkit)
The dataset was created in the Power BI Service
(i.e. Deployment Pipelines)
You were never using Power BI Desktop; you deployed the .bim file to Power BI Premium and manage the metadata using Tabular Editor.

Why might you need the .pbix? Well, “it depends”, but often this is because the original file is not available, or maybe someone wants to continue working with that dataset .pbix in Power BI Desktop. Take the below example:

You come to a self-service Power BI environment that is quite… wild west, let’s call it. Some advanced users with Premium-Per User (PPU) trials have created or upgraded workspaces, where they were managing datasets with external XMLA tools. Their trials have now lapsed, and they need to continue working with the datasets, but can’t download the .pbix. They can’t get a PPU license and are now blocked. The question now is - can we somehow get their dataset from the service?

Well, we can… with some limitations:

The .bim file from an AAS dataset is not using the Power BI format. For example, the Model.DataSources Property does not exist in the Power BI metadata.
If the AAS .bim and Power BI dataset .bim were the same, migration would be simple 😉
When downloading the .bim metadata, the PolicyRange partitions will be in the TMSL. Things like custom partitions of tables, or incremental refresh, will be problematic since they are handled in the Power BI Service and not in desktop; only some of the tabular modelling features are supported in Power BI Desktop.

WHAT YOU NEED

To do this, you need some ingredients:

If you don’t already have it, a way to get the model metadata (.bim file); i.e. with XMLA endpoint on Premium Capacity or PPU.
Tabular Editor 2 or Tabular Editor 3
pbi-tools
Either:
(A) A dataset .pbix file copy, where you will replace the dataset with the metadata from above, or
(B) Alternatively, you can use the “Blank” .pbix template provided by Mathias Thierbach of pbi-tools, available from his Git Repo, and just add the metadata to it.

GETTING THE METADATA WITH TABULAR EDITOR

Presuming we ourselves have a Premium Capacity or (for the above story) a Premium-Per User license, we can easily connect to the dataset through the XMLA endpoint from Tabular Editor and view the metadata. Tabular Editor is an external tool for managing Tabular models, which dramatically improves productivity and the quality of the models you make.

From Tabular Editor, we can save the metadata locally, either as a .bim file (single .json) or deserialized folder structure (a format optimized for source control). So how do we go from this metadata to the .pbix?

USING PBI-TOOLS TO EXTRACT AN EXISTING PBIX

If you’ve elected to use a ‘Blank’ .pbix from Mathias’ git repo, you can skip this step and instead directly deposit the renamed database.json.

Yes I used my minecraft dataset as the “starting point”…

 
      pbi-tools
     
 extract 
  
    "Path\To\File.pbix" 
  
    -extractFolder 
  
    "Path\To\ExtractedPbix" 
  
    -modelSerialization 
  
  Raw

pbi-tools is a command-line DevOps tool that facilitates automation and version control of .pbix files - including “thin” reports. With pbi-tools, you can decompile a .pbix file into its contents. For datasets - .pbix files that contain in-memory data and model logic - this produces a similar metadata file; “database.json”.

Connecting the dots, we see that the database.json is essentially the same as the .bim file, when decompiling using the -modelserialization Raw parameter. Basically, this means that we can decompile any dataset pbix, replacing the database.json with the .bim we get from Tabular Editor, above.

REPLACING THE MODEL/DATABASE.JSON WITH THE .BIM FILE

Again, if you’ve elected to use a ‘Blank’ .pbix from Mathias’ git repo, you can skip this step and instead directly deposit the renamed database.json.

All you need to do this is re-name the .bim file to database.json, and then copy this file into the new “PbixProj” folder that you created with pbi-tools; the decompiled dataset. Overwrite the old database.json.

COMPILE THE PBIXPROJ FOLDER INTO A .PBIT FILE IN PBI-TOOLS

 
      pbi-tools
     
 compile 
  
    "Path\To\PbixProjFolder" 
  
    -format PBIT
    -outPath 
  
    "Path\To\Compiled\DownloadedModel.pbit" 
  
    -overwrite 
  
  True

Once you replace the database.json, you go back to pbi-tools and re-compile the “PbixProj” folder; the result of the decompile. You have to specify that you want to create a .pbit. This will build a .pbit file from the deserialized model.

OPEN THE .PBIT & RE-HYDRATE THE MODEL (RE-LOAD THE DATA)

This is the only issue I have noticed so far; it happens for queries in the “other queries” query group, or sometimes with Dataflows for some reason.

When you open the .pbit, you will be prompted to fill in any parameters (they will have the same values as in the metadata), and then either load or edit the queries. If you have a large dataset, it’s recommended that you go “edit” and then apply a parameterized, early filter on the data to limit the volume being worked with in Power BI Desktop. Close & load, then save the .pbix.

You’ve now built a .pbix from the model metadata .bim file. Hooray!

ANOTHER REMINDER

This isn’t an officially supported feature, and a disclaimer - I’m not recommending it as a go-to approach. I tried it myself and found it helped me, so I chose to quickly write it out.

But… if you accidentally break your .pbix this way, you won’t be able to get help from Microsoft. That said, this can save a lot of time & headache if you need to recover the .pbix from model metadata and have no remaining options, other than painstakingly copying the model objects one-by-one into a new, empty .pbix.

TO CONCLUDE

There are many limitations to downloading a .pbix file from a dataset, today. However, if you are willing to venture into some unexplored territory, external tools will enable you to re-build the .pbix from model metadata, alone, and it just takes a few minutes.

- Marcus Wegener has a blog post also about this topic here:
  🔗 Link
- Mathias Thierbach also presented this approach with Artur Konig on Tool Talk:
  🔗 Link
- Rui Romano also spoke about this (video in Portuguese; see 42:34):
  🔗 Link