real time data sql server

[mwaters4]

I am not sure if this is the correct place for this post or not. If I need to move it, please let me know.

We run service repair shops and have TVs setup that show the status of the repairs (Target Hours required for the repair to the Actual Hours on the job so far, also might show some additional information like the tech on the job the input Task status for the repair, the repair type, etc.).  This data needs to refresh about every minute or so.

We also have TVs setup in the Parts department office showing the sales status for the hour, day, week, month, etc.

The hourly and daily report refreshes maybe every minute or few minutes.

Branch managers might be pulling up daily dashboards for varying departments that also need to be updated frequently.

I am at a loss for what I need from a licensing perspective and what technology I need to use to make this happen.

I really need some assistance.  It is very confusing what I actually need (nothing new for Microsoft there, unfortunately).

Some have said I need Power BI Premium, some have said I need Power BI Embedded, and some have said I just need Power BI Pro (this was told to me at a Microsoft Technology Center in St Louis, so I would hope it has credibility).  

Power BI Premium is crazy expensive for an organization my size (260 employees with maybe 50-75 that I could see using Power BI, so I really hope that is not a requirement.  If it is, I am baffled why Microsoft doesnt have something less expensive for small businesses that cant afford $60k/year.

Can you guys, PLEASE point me the in correct direction to accomplish this? 

Ive googled till im blue in the face and watched tons of youtube videos and still dont have a good grasp on what I need.

Thanks in advance!

Michael

[v-yulgu-msft]

Hi @mwaters4,

I think you may need Pro license and setup real time streaming to update data automatically and frequently. Here is a similar thread for your reference: How To Display on a TV?

Best regards,

Yuliana Gu

[henriquesilveir]

@mwaters4 hello, you could use the PRO license to do this. You must enter for each report and put it in full screen in each TV, but the method of connection you need use Direct Query.

take a look about direct query in this article: https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery

[mwaters4]

thanks for the response... 

Are you suggesting that I use Power BI Desktop to open the report and then make it full screen on each TV locally and it will somehow auto refresh?

Im sorry... I am not following what you are suggesting.  Can you please clarify?

thanks

Michael

[henriquesilveir]

@mwaters4 you open in full scren into PBI Service, similar like the suggestion of @v-yulgu-msft .

But the method of connection must be 'Live Query / direct query'

[mwaters4]

So best I can tell that method will only let me refresh every 15 minutes via the service.

I need this to refresh more frequently (every 1 minute or so).

So how can I make that happen?

thanks,

Michael

[GilbertQ]

Hi there,

If you want it more frequently then you would need to use a streaming dataset where you push the data from your SQL Server into the streaming dataset.

The above will allow the dashboards to be updated in real-time, within a second or two of you pushing the data.

For reporting purposes, this could then be done via DirectQuery or you could have an import model, due to the fact that this is not based on a dashboard being refreshed every minute.

[mwaters4]

So I guess im back to where I started... 

Do you have to have a premium license in order to have streaming datasets?

Someone at a Microsoft Technology Center told me that I just had to have a Pro License to make it work, but I still have not been able to find an answer of exactly how to do that.  Do you know if I have to have a Premium license to make that work?  Anyone know of another way to make this work?  Ive seen some comments about possibly some kind of embedded report being able to do this but nothing that explains how.

Also... when I set something up with Direct Query in the service and have the dataset settings to refresh every 15 minutes, should the browser power bi table auto refresh also every 15 minutes or would I have to have a 3rd part plugin to force a browser refresh?

This is so confusing and seems like a type of scenario that would be very prevalent in the business world.  What am I missing? 

thanks for the help!

Michael

[GilbertQ]

Hi @mwaters4

You only need a Power BI Pro license to share streaming datasets with other users. It works in the service automatically.

If you require data in a report to be refreshed via DirectQuery, then you would need to use a plug-in such as Revolver in Google Chrome to re-load the page, which would then run a query to the source database and update the report.

[mwaters4]

Thanks for the response... ill continue to look at Streaming dataset.

As far as the Direct Query method and using the Power BI Service...

I created a report using Direct Query and published to the service.

I then ran the report and "pin to live tile" which put it on a dashboard as well.

A) What is the difference in putting it on a dashboard as a live tile vs just having as a report?  I dont understand the difference?

B) I downloaded Revolver and Super Auto Refresh in Chrome to test this out.  It does appear to refresh the numbers at the intervals I would like when I do a browser refresh, but if I put either the report or the dashboard in full screen, when the Revolver or Super Auto Refresh does a browser refresh/reload, it kicks it back out of full screen mode, which is not what we need.  Do you know how to keep that in full screen mode upon refresh?

C)  Another problem I have encountered is paging. We have a table of data that would be on the TV.  Using the current BI platform that is being put to pasture, it has a way to "auto page" so if there are 20 records returned for a query and the table only shows 10, it will show the first page, wait 10 seconds, then page to the second page, wait 10 seconds, then go back to the first page... reloading the query each time.  Do you know of a way to have this paging functionality for a table in Power BI?

Thanks in advance for your help!

Michael

[GilbertQ]

The dashboard with a streaming dataset is designed to be automatically updated without having to do anything (like refreshing the page)

It should not kick it out of Full Screen mode, have you tried Full screen mode via the Browser or Power BI?

Currently I do not know of a way to auto scroll a table, I would think that might be possible using a URL filter?

[mwaters4]

Re: Streaming Dataset - I see where I can set that dataset up in Power BI Service, but not sure how I can connect to a SQL Server database using the methods they provide for a connection. Any advice there?

Re: Full Screen Mode - I was using Power BI full screen mode so that it takes all the extra menus/bars, etc. off of it for a true full screen look for the TV.  It will refresh if I just use the browser full screen mode, but was hoping to get every bit of real estate since we are putting on TVs high on walls and need people to be able to read the text.

Thanks for any input you can provide... hopefully, I can find something about the auto paging items... that is another real need and I know other systems that can do it, so im unsure why Power BI cannot.... 

Thanks

Michael

[ranbeermakin]

@mwaters4 for TV mode can you append ?chromeless=1 at the end of Power BI report URL?

For setting up a database with direct query you need a gateway installed on your intranet if your SQL box is on intranet. If your SQL is on Azure SQL then you can do direct connection without the gateway.

I have blogged about this here: https://bigintsolutions.com/2019/03/29/refresh-power-bi-report-every-min-and-show-on-a-tv/

One of our community fans have also recorded a YouTube video on this: https://www.youtube.com/watch?v=pacBkMqMjDQ

If you need any other assistance, why don't you send us a private message and we can spend few hours to check your scenario and recommend/implement it.

-Ranbeer

[mwaters4]

thanks for your post... sorry... I must have overlooked it!

I will review and get back with you if I cant figure it out.

Thanks very much!

Michael