Real-time data in PBRS

[kostaszogo21]

Hello guys!
Newbie here so i would appreciate all the help i can get from you.
It's my first project in Power BI, so i am gonna  try and explain my problem with as many details as possible:

• we're using import mode (because we use ODBC connection and that's our only choice)
• the client wants almost real-time data (20-25 minutes before current time) so we have to schedule multiple refreshes during the day 
• we are using Power BI Report Server, so we cannot use incremental refresh.
• the data is huge (around 50M rows) and every scheduled refresh takes around 30-40, which means we cannot have near real-time data

Can you please think of any solution to my problem?

It would be really convenient if i could refresh just one table from my dataset when using scheduled refresh, but from what i've searched that's not possible.
We are also thinking of using SSAS, creating partitions in the tables (today's data and older data) and refresh older data in another tool and let PBRS' scheduled refresh run the refreshes in only the today's partition. But i don't know if that's even possible or how to do it to be honest.

Can anybody recommend a solution to myproblem?

Is there a way to have near real-time data in PBRS when using Import Mode and the data are huge?

I hope that my problem is really easy and someone from the community will save my project!
Thanks in advance guys,

Kostas

PS: No pbix file available, because of the company's policy

[d_gosbell]

---

@kostaszogo21 wrote:

We are also thinking of using SSAS, creating partitions in the tables (today's data and older data) and refresh older data in another tool and let PBRS' scheduled refresh run the refreshes in only the today's partition. But i don't know if that's even possible or how to do it to be honest.

---

So you are close with this idea, but the scheduler in PBIRS can only refresh data stored in the PBIX models and it only does a full refresh of all tables. But if you use SSAS you can create whatever partitioning scheme works for your needs and use some external scheduling tool like SQL Agent to trigger refresh jobs. We have a model with 200M rows in our main fact table and we refresh that every 10 minutes during the day since we only refresh the current day of data and the other 5+ years of history is not refreshed regularly. We also have a nightly job to merge the previous day's partition into the historic partition. It works well, there is just a bit more manual work involved to configure the partitions and processing jobs.

[kostaszogo21]

Thank you very much for the reply @d_gosbell.
Could you please guide me a bit more (maybe describe the steps needed in order to make it work)?
Is there any documentation or any article that you would recommend?
Also, cause i don't know if the company uses SQL Agent, can i trigger the refreshes using any other tool? I think i saw an article mentioning doing it with Powershell.
And one last question: If i let's say create two partitions (Partition 1:Current Day and Partition 2: All Previous Days), do i have to trigger two refresh jobs in SQL Agent (one for each partition) or would i refresh my current day's data in PBIRS using scheduled refresh? If i refresh both partitions in SQL Agent do i also need to create a scheduled refresh in PBIRS or do i not need that at all?
Sorry for asking so many questions but i'm not familiar with the subject at all.

I appreciate your help @d_gosbell ,

Kostas

[d_gosbell]

---

@kostaszogo21 wrote:

Thank you very much for the reply @d_gosbell.
Could you please guide me a bit more (maybe describe the steps needed in order to make it work)?
Is there any documentation or any article that you would recommend?

---

I can't think of any documentation off the top of my head, there are probably plenty of guides out there, but I'd just be googling for them.

If you only need the incremental refreshes during the day and you have some down time overnight the simplest approach is to

1. create 2 views in your source system, one which only shows data for today and one which shows data for everything earlier than today.
2. Then create 2 partitions in your table, one pointing to each of the views above
3. Then you setup a scheduled task to run every 20-25 minutes during the day that processes the daily partitions
4. Then setup a nightly job that does a full process of the whole model. This will clear out the current partition and incorporate the data for the prior day into the historic partition (so this job should run after midnight)

---

@kostaszogo21 wrote:

Also, cause i don't know if the company uses SQL Agent, can i trigger the refreshes using any other tool? I think i saw an article mentioning doing it with Powershell.

---

Yes you can also use Powershell or a custom c# program

---

@kostaszogo21 wrote:

And one last question: If i let's say create two partitions (Partition 1:Current Day and Partition 2: All Previous Days), do i have to trigger two refresh jobs in SQL Agent (one for each partition) or would i refresh my current day's data in PBIRS using scheduled refresh? If i refresh both partitions in SQL Agent do i also need to create a scheduled refresh in PBIRS or do i not need that at all?

---

Yes, you would need 2 agent jobs (or jobs in some other scheduling tool) as explained above. You cannot schedule SSAS refreshes using the PBIRS scheduled refresh feature so you don't need any of those setup if you are using SSAS.

[kostaszogo21]

Thanks again for all the help @d_gosbell .

When you say that

" You cannot schedule SSAS refreshes using the PBIRS scheduled refresh feature so you don't need any of those setup if you are using SSAS."

do you mean because i will be using DirectQuery to connect to Analysis Services (once i have created these partitions) ? Or is there another reason for that?
Sorry for asking again, but it's vague for me if once i have created these partitions i should use Import Mode or DirectQuery.

Thanks again,
Kostas 

[d_gosbell · Accepted solution]

---

@kostaszogo21 wrote:

do you mean because i will be using DirectQuery to connect to Analysis Services (once i have created these partitions) ? Or is there another reason for that?

---

Technically it uses a Live Connection when connected to SSAS, but conceptually that is similar to DirectQuery in that the Report Server does not host the data, it just sends queries out to SSAS. Therefore you have to schedule any refresh operations using the SSAS tools/ APIs.

---

@kostaszogo21 wrote:

Sorry for asking again, but it's vague for me if once i have created these partitions i should use Import Mode or DirectQuery.

---

You could use either, but if you only need to refresh every 20-25 minutes you could probably using import mode as that will give you the fastest possible query speed