Need advice on how to proceed next

[valleyman]

Hi.

I'll apologise in advance, as though I've been dabbling a bit in PBI in recent weeks and months, I'm still very much a novice and need some guidance on how to take the next step. I'll start at the beginning...

I am an owner of several care homes, and in these we use a computerised, care management system that records every interaction and observation of residents, 24/7. This information can be extracted via JSON and so I have written a delphi app that takes this data and dumps it into a SQL Server installation on a third party hosted Windows Server machine. I'm aware now that PBI could handle the raw JSON directly, however I did not know this at the time and anyway, I'm generally much better at SQL than PBI so I do not mind that I did it this way round.

The reason I have done this is because I wanted to create reports and dashboards which monitor and display KPIs and other information deemed relevant to me, brought into something like PBI through SQL Server connections. I need it to be automatically refreshed at defined periods (ideally), and for me to receive notifications and emails based on triggers, such as "number of residents falling in a 7 day period > 10", or "number of residents who on a weekly basis have drunk < 1500mls per day". 

I first downloaded PBI Desktop and created my reports, but there seemed to be no way to automatically refresh the data. I have tried PBI Server, but although I can define refresh periods, there seems to be no method of triggering alerts and no way of importing these reports created in Server into my main PBI account, to generate the alerts. We are a medium sized SME with a robust turnover, but I do not want to pay the sort of PBI Premium prices as I believe this is overkill. We could, in theory, just use the Excel spreadsheets we've been using up to this point, and can't justify the sorts of prices for that. 

Any advice and guidance would be very much appreciated, as I'm starting to feel dizzy with all the different things I'm looking at. 

Thanks

Ant

[valleyman]

Thanks to everyone who replied, they were all really useful and any of them could've been the 'answer'. I went with the one I referred back to the most, but all were good. Cheers. All sorted now. 

[v-lili6-msft]

hi  @valleyman 

For your datasource is SQL SERVER, you need a gateway for refresh dataset, please refer to this document:

https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem

https://docs.microsoft.com/en-us/power-bi/service-gateway-enterprise-manage-sql

Then configure scheduled refresh in power bi service, please refer to this document:

https://docs.microsoft.com/en-us/power-bi/refresh-scheduled-refresh

For alert in power bi service, you could refer to this document, and it only works on gauges, KPIs, and cards dashboard.

https://docs.microsoft.com/en-us/power-bi/service-set-data-alerts

Regards,

Lin

[DataVitalizer · Accepted solution]

Hi @valleyman ,

Data Refresh

The data refresh depends on the connection type you have used to connect your Data: https://docs.microsoft.com/en-us/power-bi/refresh-data

Set Alerts 

To create alerts you have to upload your report to power bi service then follow couple instructions availalbe here

Please let me know if that helps

[camargos88]

Hi @valleyman ,

You can check it on these links:

https://docs.microsoft.com/en-us/power-bi/refresh-data

https://docs.microsoft.com/en-us/power-bi/refresh-scheduled-refresh

https://docs.microsoft.com/en-us/power-bi/service-gateway-personal-mode

https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem

https://docs.microsoft.com/en-us/power-bi/service-set-data-alerts

Ricardo

[Greg_Deckler]

Generally you would just publish to the Power BI Service and you can setup automatic refresh from there. No need for Premium. Only thing is if you share the reports out with others they will need a Pro license, but you can get up to 400-500 people using Pro licenses before looking at Premium starts to make sense.