Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
garrek99
Helper I
Helper I

Removing data from .pbix file?

Does anyone know how to dump the data from a report file (while leaving everything else intact)?

 

We are backing up our report files daily and each file is like 300+MB.

 

Is there a way of dumping the data and retaining the queries and report configurations?

Basically stripping away the data is what want to be able to do.

 

Out reports get published online and the Enterprise gateway takes care of the data and the scheduled refreshes. We don't need to back up the data everyday and are wasting valuable disk space.

 

Thank you

1 ACCEPTED SOLUTION

I've just been trying this myself and observed different behaviour depending on how you save the .pbit

 

File > Save As > .pbit = File size roughly equal to the original report

File > Export > Power BI Template = Significantly reduced file size

View solution in original post

17 REPLIES 17
ankitpatira
Community Champion
Community Champion

@garrek99 there is a way to dump data but that won't actually reduce your pbix file size. within query editor you have option of executing R script on your tables. so as a last step you can have R script that outputs your data into a csv but that will just take a dump out of your data. if you're interested in doing that then R script syntax will be,

 

Capture.PNG

 

The thing is that our data is on the cloud and we see no point in having a copy of it in a csv file.

 

R is something we'll explore soon when we start playing with involved analytics.

 

TY

@garrek99

 

Just to confirm, which file size did you observe? My template file (.pbit) will be much smaller than the .pbix file.

Removing data from .pbix file_1.jpg

 

A Power BI Report Template contains the definition of the Report (pages, visuals, etc.), Data Model definition (schema, relationships, measures, etc.) and Queries definition (collection of queries, including Query Parameters, etc.). In other words, a Power BI Report template includes pretty much everything that a Report file includes, with the exception of the data itself.

 

Best Regards,

Herbert

I've just been trying this myself and observed different behaviour depending on how you save the .pbit

 

File > Save As > .pbit = File size roughly equal to the original report

File > Export > Power BI Template = Significantly reduced file size

You are totally right. Exporting as PBIT did the trick.

 

It's unfortunate that I never received your reply when you made it. (Came across it by accident today and after testing can confirm that it works.)

 

Thank you very much.

The templates are reduce to the same size for both "Save As" and for Export - it looks like Microsoft may have fixed the issue with Save As Template since this discussion took place over a year ago.  I started with a 41 MB file (one million rows), and the PBIT templates save and export as 24 KB.

That's because the template removed the loaded data. As it's name suggest, it is just a template



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Agreed. I was pointing out that the OP may have experienced the same where Save As template doesn't reduce the file size, but Export as template does.

I even tried creating a .pbit from scratch and as soon as I save it the file size jumps.

 

Are you doing the DirectQuery method or Import method? Could that be the difference?

DirectQuery is not an option for us because it limits the capabilities too much.

@garrek99

 

I'm also doing the Import mode. What about the solution provided by MattAllington?

 

Best Regards,

Herbert

Matt's option is very involved and with a small team of people with different tech skills will be hard to implement.

 

We have to use Import method so are still looking for a solution. Thank you.

 

Hi Herbert. This is exactly what we want to accomplish but alas it's not working out. See image below.

1.png

When I open the file it asks for the custom query parameters that we've set up and also right away goes into Data Refresh mode which indicates that we are on the right track. Unfortunately the file size remains huge.

 

tjd
Impactful Individual
Impactful Individual

A report without data is no longer a report, it's just a shell.  In the BI venacular, that would be a template (.pbit).  However, it tells you nothing without the corresponding data.  So, I see no value in keeping daily backups of it because it won't tell you anything without the corresponding data for that date.  You'd be better off printing a .pdf of the report (but now it's static) if you're interested in going back and seeing what the report said on a given date and are concerned about saving storage space.

Hi

 

The value in the file without the data is the code that went into creating all the measures & columns in the different tables, the query manipulations and the considerable configuration that goes into achieving the best visuals. 

We don't care about the data at the time the file is saved because the Dashboards are viewed online.

 

We want to be able to develop on the desktop with the data present, publish it and only then save the file after stripping the unnecessary data. And you are correct, they will be more like shells rather than reports and that's exactly what we want.

 

Do you see what we are trying to accomplish?

 

I work for a large org with an initial team of like 6 people trying to get PowerBI instituted as the Enterprise reporting tool. Down the line the team will have more like 2 people but right now we are struggling with sharing files and organizing them. There are dozens of reports that need to be produced. To be able to work together we are storing these reports on shared drives and just waiting for 300+MB files to download and then saving them back up on that drive is a pain.

 

I will do some research on what a .pbit is used for. Do you think it will fit the bill for us?

 

TY

here is what I would do

 

create a dummy source for your data (eg an identical  query with just 10 rows of data (for example)

repoint the workbook to the dummy, and refresh

save

 

if you have lots of sources, you can probably create a master source for "real" and "dummy", and then build an "active source". Point all queries to the active one, and then toggle the active one between real and dummy. 

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
tjd
Impactful Individual
Impactful Individual

If so, then you want to save your file as a .pbit.  Go to File|Save As| and select PowerBI Template (.pbit) as the file type.  It will strip out your data but save all your report pages and customizations and greatly reduce the file size.  Put a date-time stamp on the file name and you're ready to go!

That sounded so good, until it didn't work. 😉

 

My file is still 332MB.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.