15

Greetings beloved comrades,

I am building a series of power bi dashboards, and as they go into production I'd like to put them into TFS. However, due to the large datasets involved, some of these report definitions are quite large (1.6GB).

It doesn't seem like a good idea to force TFS to store all of the actual data, when only the definition really matters.

Is there a simple way to remove the data from a .pbix file or save only the definition?

Edit: Looks like Microsoft has rendered this question obsolete with the creation of PowerBI templates. April Update for PowerBI Nevertheless, the workaround in the answer could be used for other purposes.

Eric Hauenstein
  • 2,557
  • 6
  • 31
  • 41
  • Are you going to use Visual Studio Team Services? Do you mean you want to add the dataset into TFS? Are the datasets can be retrieved one they added into VSTS? As the data source that is connected to when retrieving data for VSTS is actually the public REST APIs. Once connected, data from the project is retrieved through those APIs and stored in a tabular model in Power BI. – Cece Dong - MSFT Jan 25 '16 at 10:46
  • 1
    No, we're using the on premise TFS 2012. No. `Are the datasets can be retrieved one they added into VSTS?` I'm not sure what you are asking here. I want to treat the PowerBI desktop file like an SSRS .rdl file so that it fits in our current version control schema, but I'm not sure how to do it. – Eric Hauenstein Jan 25 '16 at 13:39
  • Have a read of http://www.sqlchick.com/entries/2015/8/6/ways-to-utilize-power-bi-in-a-bimodal-bi-environment and http://community.powerbi.com/t5/Integrations-with-Files-and/Maximum-size-for-pbix-files/m-p/11292 Basically, if you're not using a live datasource, the datamodel is embedded in the pbix and there's no obvious way of truncating it, which is something I'd also like to know how to do. – Liesel Jan 31 '16 at 00:50

3 Answers3

8

I would add a "Parameters" Query (a table with a single row - created using Edit Queries / Edit Data) with a column called [Data Load], with a single row containing "Yes".

Then I would add a Filter step to the end of all the other Queries, referring to that "Parameters" Query. The Filter syntax would be:

Parameters{0}[Data Load] = "Yes"

That syntax is a bit obscure - it means:

Go to the Parameters Query, get the value from the 1st row, in the [Data Load] column, test if it equals "Yes".

When you want to empty all the data from the .pbix file, edit the Source step in the "Parameters" Query and change the [Data Load] value to "No", Apply and Refresh.

I've built a working example of this which you can download from my OneDrive and try out:

http://1drv.ms/1AzPAZp

It's the file: Power BI Demo - Dynamically filter all data.pbix

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • 2
    This is a nifty hack. I hope MS makes this a bit simpler in the future. – Eric Hauenstein Feb 02 '16 at 22:55
  • 2
    They have - it's called a Power BI Desktop Template: https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/ – Mike Honey Jun 23 '16 at 23:56
  • Thanks Andy Wilburn for pointing out that example file had gone missing. I've edited it to use the current Query Parameter functionality in the same way. This gives you table-by-table control of what data you remove, whereas a Template always removes all the data. – Mike Honey Jul 01 '19 at 03:59
  • Using a Query Parameter, the syntax is the Filter step is now #"Data Load" = "Yes". – Mike Honey Jul 01 '19 at 04:00
2

Convert the pbix files to a pbit file using "Save As..." option, and then version those pbit files in TFS, using Visual Studio, but controlling them on the server.

vaibhav gandikota
  • 21
  • 1
0

This approach is a bit interesting. When you commit a .pbix it uploads it to Premium, extracts the JSON metadata for the model, and then commits that back to DevOps next to the .pbix. That way you can see a diff over time of the model metadata including Power Query changes, measure changes, etc.

GregGalloway
  • 11,355
  • 3
  • 16
  • 47