Real time Analysis From a SQL Database

[DebbieE]

I am talking to someone who wants to do some real time Analysis from a SQL database

they decided that they would do direct query straight from the transactional database and this would be the read time Data set

Im very against this, specifically because you shouldnt be using your OLTP as the basis for Analysis. they will probably cause issues for the people needing to use it as OLTP, expecially if they get a lot of users.

Ive had lots of good stuff come through about why you shouldnt use the OLTP for Analysis with Direct Query but I need to 

A. Give them a better Option

B. Explain whay its a better option

Has anyone got any ideas on best practice for this? 

[Icey]

Hi @DebbieE ,

I haven't done much research on this. 

Just to provide a reference, consider using streaming datasets, like using PowerShell to push data to a Power BI Push Dataset from a SQL Server data source. For specific operations, you can refer to this blog: https://www.blue-granite.com/blog/power-bi-streaming-datasets-for-more-than-iot.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DebbieE]

Im absolutely lost as soon as the article gets to the bit about Powershell.

I have no idea where to do this bit. It gets very high level at this point and I would need more step by step guidance on what Im trying to do. 

are there any articles on line with more detailed explanations on this?

[Icey]

Hi @DebbieE ,

Hope these documents and videos could help you:

Create a Power BI streaming dataset for real-time dashboards;

Streaming Data from SQL Server to Power BI using PowerShell;

Stream Dataset for local SQL Server Database Query.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DebbieE]

Fantastic,

Guy in a cube is always great, Its a nice starter. The middle one is really helpful but doesnt explain the actual connecing up on the power shell script into the Real time data sets.

Ive not read the third one yet but Im getting closer. Ill post when I have had chance to do more. Thank you so much

[DebbieE]

Fantastic

I will have a look at this and see if I can get something working. However one of this isues, which I dont think is covered in this article is that I dont want them getting data from a transactional system

What is the best way to deal with this before its addded to a push data set. I imagine my users are still going to say, why bother doing all that when we can just direct query the transactional system