Good afternoon everyone! I know this is an Epicor support forum, but you are all so helpful and I am not getting anywhere with Microsoft support/community.
We are thinking about using PowerBI as a way to pull live data from various sources to display on information monitors throughout our machine shop. We don’t currenlty use PowerBI, or pay for any subscription services.
In my research I have setup an ODATA connection with REST to an Epicor BAQ. This works great. My connection details get saved in the PowerBI file (I assume) and the datasource updates everyday, just like I expected.
Now we want to pull data from a networked excel file. It is just a simple excel file with a macro in it. The macro is just for user-level stuff, and has nothing to do with the data or the connection. The Excel file is on our company network, in a folder that most people have access to.
When I setup a PowerBI report (visualization) that utilizes this Excel file as a data source, it seems to work great. The data coms in just fine and I can see the visualization updates and shows just want I wanted. However, I can’t refresh the data in the report without opening PowerBI and refreshing from there. Ideally the data source should update a few times a day, so that the report has accurate data. When I refresh the data source from PowerBI in the browser, I get the error:
Scheduled refresh is disabled because at least one data source is missing credentials.
To start the refresh again, go to this datasets settings page and enter credentials for all data sources.
Then reactivate scheduled refresh.
I didn’t ever have to enter credentials for the Excel file, so I am not sure where it is getting the missing credentials from.
Has anyone else had experience with using PowerBI, and pulling data from a shared Excel File on the network into a Power BI report?
Power BI requires credentials to access the Excel file on your network. This is usually done using a server with access to your network share as a gateway. There is a service to install on that server, and then Power BI needs to be provided with Windows credentials on that domain to access the network share and upload the data to Power BI. When you go to your dataset’s settings (e.g. to set up a refresh schedule) it’ll show you this section. I have an example of my own, where I’m joining such an Excel file on a network share with data I’m pulling from Epicor. My dataset’s settings look like this:
And if you follow that link to “Manage Connections and Gateways” (assuming your IT dept got one set up), this will be where you set up the connections that will show up in the “Maps to:” dropdown on the previous page. In my case, the [*****] Units Forecast has settings like this:
Now, this is just me personally, but while BI tools can be used for real-time data, I feel like there’s a lot of moving parts just to get a real-time view - as you’re currently experiencing. If the data can be a day old, then most BI solutions: PowerBI, EDA, probably Grow.com, etc. are great - especially with immutable data. For real-time, BAQs feeding a graphing solution may be a better bet. I think @jgiese.wci is doing cool stuff with Grafana, which is built to ingest data in real-time.
I am my IT department. HA! I was worried that I would need the gateway. I don’t have any setup now.
I guess my next step is to figure out how to setup a gateway. I think I should do this at the server level, and not on my desktop PC, otherwise I will be the only one with access. Right?
I might have to pull in our MSP to get the gateway setup.
Totally agree! For now, I am just pulling yesterday’s Epicor data into PowerBI through that ODATA REST API. Eventually we might migrate to a Grafana-like approach. Thanks!!
Hey Nate, you can install the gateway anywhere on the network. Only benefit of doing it on the server is for better uptime, since your workstation is more likely to restart for updates etc.
