Fastest Solution for Data Refresh

[Anonymous]

Hi all,

I guess similar questions may have been asked before, but I'm getting lots of different ideas and am totally lost. So let me ask this again with my use case below.

• Current account: PPU
• Data mode: import mode with data endpoints, not connecting to the database directly
• Auto refresh interval: every 30 minutes

Everything has been okay, but some report users requested shorter refresh intervals, like single digit minutes if not real time. 

I've read all documents I can find but still cannot formulate a final solution.

My biggest challenge: I'm not allowed to connect Power BI server to the on-prem database.

Does that mean the import mode is my only option?

I thought about using one of the data sources that supports DirectQuery, such as Azure SQL Database. But all data will still be stored in the on-prem database, which means I'll need to find a way to constantly sync my data to Azure. 

My difficulties with this option are:

1. It sounds like a double query and I don't know how much faster that can be;
2. I don't know how to do this.

I think I might be missing something here. Can anyoen suggest a better solution?

At this time, I'm willing to try anything other than connecting to the on-prem database directly. 

Appreciate your help!

[Burningsuit]

Hi @Anonymous 

My approach in this situation would be to ask "Why do some users need real-time or near real-time data updates?"  What will be their action on this data, will they act immediately they see it, or does it really matter if the data is up to 30 minutes old ? In my experience only commodity trading, currency dealing or machine operating need real-time data, and they are often served by solutions other than Power BI! Do your users want this just because they can get it ? 

You are restricted from connecting directly to the SQL database, and that is good practice, as any Direct real-time connection will slow the Database down appreciably. Direct access on Power BI generates at least one SQL Select per visualisation and is constantly updating them as users click and page through a report, so that can really slow down the SQL server, especially if the Report is shared with several people. If the SQL server is doing other important line-of-business stuff it will not be good to slow it down just to get some reporting out of it.

Every solution you propose, apart from import mode, will give a bigger hit on your SQL database than it currently has. You need to find out if those users really really need real-time updates then, if they must, have them support you in an approach to your IT department to get Direct Access and a faster SQL server.

Hope this helps

Stuart

[Anonymous]

Thanks @Burningsuit 

Trust me I screamed the same question silently when I got the request!

But sadly the request is from my boss's boss. He doesn't really know tech, so he always asks for things that don't make much sense. 

In this case, the actual end users of the reports are actually okay with refreshing every 30 minutes. But he goes to the floor once and decides that he wants something faster.

So the answer to the question is, no they don't really really need real-time updates, but it's all about someone wanting to feel like the boss. 

I figured I might have to sync the data to Azure SQL database, but how fast can I do that?

[collinq]

HI @Anonymous ,

This can get quite complex quite quickly.  You have two big concerns as I see it - the refresh ability and the licensing to let that refresh happen.  And, each option has its own set of limitations, options and considerations.  My first guess is that you would want a live connection.

Here is an article explaining the different type of connections:

DirectQuery, Live Connection or Import Data? Tough Decision! - RADACAD

If you are looking for streaming data, you can read this here:

Real-time streaming in Power BI - Power BI | Microsoft Docs

You will need licenses that are appropriate and this link shows some guidance for that:

Streaming dataflows (preview) - Power BI | Microsoft Docs

[Anonymous]

Thanks @collinq 

I've honestly read all of those docs. 

Since I don't have a direct connection to the original database, what is the fastest way to sync data to a source that can be used for either DirectQuery or Live Connection? 

I mean that sync will be the bottleneck for the updates. Reports can only get updated as fast as the sync goes.

[collinq]

Hey @Anonymous ,

I think that Live Connection should be faster since it is just hitting the existing schema.

[Anonymous]

I actually had some success with the Power BI API, but only in Powershell.

My next step would be to build a cron job with Python and call the refresh API every so often. But the Azure authentication is really complex and not well documented. I'm extremely confused with client id, service principal, app secrets and how/what to use in my script. Everything I can find online now is either somewhat based on an older version of Azure and doesn't apply much, or they talk about different routes that stop halfway.

Can you recommend a tutorial that can walk me through end to end?

Thank you so much!