Query loading order

[FilipK]

Dear all,

assume you've got 2 tables to load from a single SQL database with PowerBI.

Each table takes 10 minutes to do so.

Via keys the tables are related to each other. While one table is loaded there is still ongoing input traffic into my database.

That means as soon as the 2nd table starts loading there might be entries that cannot be related to the first loaded table.

In PBI this leads to null values, when connecting both tables after loading all. 

How can that been prevented?

I tried simultaneous load option, but this didn't help. I also don't want to work with timers as it was proposed somewhere.

When deactivating parallel load, I experienced that the order of the queries in the query editor influences the load option. So the idea was to start with the one that has higher input frequency, afterwards load the other one and than filter the rows that are not related. The problem with that approach is that after uploading to the PBI service the order has varied and I don't know why.

[parry2k]

@FilipK yes, why not have that heavy lifting done at the backend.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[FilipK]

Perhaps I don't understand your point in total. If needed I can do in backend. No problem. But I'd like to avoid working with constant times, since the time for refresh is not constant (depending on SQL performance at that time and other database workloads). 

[v-kelly-msft · Accepted solution]

Hi @FilipK ，

Try to shape data at the query editor before you load them:

You could create a new table keeping the transformed data.Then choose uncheck "Enable loaded" for the oringinal table.

For the reference,pls see below:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[parry2k]

@FilipK why not you create a view in the SQL Server and take care of all this at the backend and then use that view in the Power BI.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[FilipK]

@parry2k , acutally I'm accessing the views. But the views are executed with each query. So output of view 1 holds data from time T, while output of view 2 holds data from T+10. Do you mean, that I shall take that delay already into account and filter the view accordingly?