Issue with switching to a large dataset

[naninamu]

Hello - was hoping someone could advise me of the best way to go about this! 

I have developed a report based on a Test data set.

When I send this to someone else in the company to use, they then repoint it to point at the actual Prod data - this is many millions of rows.

We're finding a few issues:

1) Before I send the PBIX to them, I am setting it up to point to their connections as they don't know how to do this. Due to the volume of data that would need to be imported, I am telling PBI to not run the Pending Changes

2) Due to this, when they attempt to publish, it is still for some reason looking to connect to my Test Data set

So I could either run the pending changes on my machine - which would take I think hours, or send them the file with the Test Connections still in place and then change everything on their end - not ideal either as I may have to send this to multiple depts. 

So how do I update the file to point to their large data source, send it to them and have them publish it and begin ingesting data without attempting to reconnect to the old Test Data source? 

Thanks in advance, Andrew

[naninamu]

Thank you. Another approach I was thinking about was to restrict the number of rows ingested from the datasource (Keep Rows), switch the data source and apply changes, then send the model to them and get them to publish after removing the Keep Rows condition. My questions was - when using Keep Rows, does PowerBI still need to first read in all the rows before restricting what we see, or does Query Folding mean it only actually ever reads in the number of rows that you specify? Thanks!!

[tharunkumarRTK · Accepted solution]

@naninamu 

Query folding depends on your datasource. If Keep Top rows step is folded then it wont read all the rows before restricting it to top rows. Even if it does not support you have work around you can use parameters to implement the restriction, instead of performing Keep top rows transformation.

Check out this thread for more information 
https://community.fabric.microsoft.com/t5/Power-Query/How-to-Limit-rows-from-Oracle-source/m-p/43288...

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[naninamu]

Thank you for your advice. It is much appreciated. Just one question - what if we do not want them to see our Test Data - ie we would prefer them not to connect to our Test Data set?

[tharunkumarRTK]

@naninamu 

In that case, you can publish the semantic mondel on your own. If you do not have permissions to publish it to the actual workspace then you can publish to a test workspace or a Personal workspace (My Workspace) and change the parameter values and perform the data refresh and then share the report with them.

Or if you want you can download the report and share it with them as a attachment 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[tharunkumarRTK]

@naninamu 

Create m parameters for your datasource. While you are sending them to your peers, fill these parameters with Test datasource connection. Ask your peers to publish them to power bi service with the same details. Once the dataset (semantic model) is published, you can open the dataset settings page and under the parameters section you will be able to find all the M parameters you would have created, modify them and enter the Prod datasource details. Then perform the data refresh. Then every one would be able to see the report with full set of data.

This is the common practice that everyone follows when they are handling large datasets. 

Make sure you choose the M parameter datatype as Text. If you choose 'Any' then you will not be able to change its value from service. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!