The right solution for Intraday tracking in PowerBI

[or0982]

Hello,

Been a long term lurker but I've run into a fork in the road and could use some advice to weigh the pros and cons. I've come up with possible solutions but each have their negatives.

Background: At the company I work, we have the sales team track their sales throughout the day via a front end website that is managed by IT. The data is housed in an oracle DB and is where I extract the data. Since the server is managed by an outside vendor, we only have readonly access and creating aggregated views is not an option as it would incur cost. The data format is pretty stright forward, we have DATE|USERID|PRODUCT|QTY|REVENUE which we than tie to our a corporate hierarchy to add Location and 6 layers of leadership. I have a working PowerBI Report that refreshes every 30 minutes that is used by Leadership to view results but not entirely happy with the solution and exploring alternatives.

Current Solution: I created a vbs script that is tied to Windows Scheduler that will open an excel file that has all the data transformation needed via Power Query and is refreshed than saved as a CSV file on a network drive that is accessible via Power BI gateway every 5 minutes. (excel was original chosen because I have power pivot reports that were being used in the interim while we build out the PowerBI solution)

Pain Points

1. PowerBI only allows 30 minute incurment refreshes, leadership would like real time tracking
   1. I've thought about using Power Automate to refresh the model using CSV as the trigger when ever it's saved.
   2. Explored Livestream but ran into issues, but my experience with Livestream dats is limited
      1. Livestream can't be used in Reports, there are various measures and interactions I don't want to lose
      2. Reliant on Excel to push to the API, only way I know how currently with the current process
2. Dedicated local machine dependencies (Excel file that refreshes every 5 minutes)
   1. I would like to be able to go on vacation without worrying about my machine restarting or going down
      1. This can be solved by moving the PowerQuery steps into PowerBI model
      2. I head you can have PowerPivot pull from DataModel directly
3. User Experience
   1. Currently, users have no idea when new data has been refreshed but tend to hit refresh at the 30 minute increment marks for the new data to appear.
   2. Some of these Reports are hosted on Big Screens that are a pain to control to manually refresh the report when ever a new update is pushed.

Overall this is me nitpicking a process that is working and effective but I don't feel we have delivered a polished product yet. Nailing this down will open up additional reporting capabilities down the line and we can use this as the framework for that.

I appreciate any insight that community can provide and also hopeful this post will help others exploring interactive real time reporting in PowerBI. 

P.S. While I would like to stay in the ecosystem provided by Microsoft, I'm still open to alternative solutions if this is not the right one.

Thanks

[AlexisOlson]

The best solution would probably be a DirectQuery to the oracle DB where the data ultimately comes from. Read-only access is fine so long as you can access the database directly rather than having to export data. You'd probably have to get the vendor involved but they might prefer DirectQuery versus you exporting every 5 minutes.

Second best might be to automate the exporting such that it loads into a database you can DirectQuery rather than CSV files that need to be re-imported.

Having to rely on exports for anything approaching "live" data is likely going to be painful no matter what software combination you choose.