Build the model using sample data

[kjani]

I want to build a Power BI report and need to connect to two databases. The connections are very slow and it's difficult to play with the data. I would like to get a sample of the data, build the report and then connect to live databases. What's the best way to accomplish this? Is there a way to import a sanpshot of data from SQL Server database? I am not in IT, so need help how do I build the report first using small set of data and then scale. Thanks in advance.

[amitchandak · Accepted solution]

@kjani ,

Avoid by directional join, prefer star schema 

you need to check with sql profile

https://radacad.com/directquery-connection-in-power-bi-how-does-it-work-limitations-and-advantages

Query reduction

https://www.youtube.com/watch?v=4kVw0eaz5Ws

Try composite model

https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fas...

[mahoneypat]

Here is a tip that may help.  Make your queries, and in one of the first steps add a Keep Rows step and keep 100,1000, or whatever you think is representative.  You can build out your model, make measures/visuals, etc.  When you are done, just delete the Keep Rows step to load all the data.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat