I spent this morning trying to understand and compile how to use Power BI with Epicor BAQs. Power BI is new to me; I know Power Query but not this interface.
Thus far I have ascertained the following points - and tell me if any of it is out of date or wrong, etc.
Power BI is all about connecting to data and visualizing. Even live data.
Smart people here say don’t do it with live data, though.
Let’s say you’re going to do it anyway. How do you do it?
a. ODBC? NOOOOOO! (I knew that already.)
b. Some app you install called Power BI Gateway
c. REST v1? Can only use Desktop version; supposedly works, but not secure. [I have not tried it myself]
d. REST v2? Maybe - this is where my questions come in.
Again, I am gathering (and would love to be wrong) that the only way to use REST is through PBI Desktop (not online)?! So that stinks.
Also, the connection type is Web, not Odata. Got it.
I am using basic authorization (what else is there for REST, except maybe Azure AD?).
I wanted to put the API key as a header, but it looks like you can’t with basic auth.
So I saw a hack where you put the API key in the URL (I used the “parts” just for fun). And this seems to work.
EDA also doesn’t use live-data. They all upload the data on a schedule to their own data cubes. Epicor did acquire www.grow.com I am not sure how that works. I would try that.
It’s still basic auth, just manually passed through the http headers instead of having power bi add it for you.
power bi would do the encoding for you, but you can also do it manually. The encoding is just user:password in base64 ( https://www.base64decode.org/ can do it )
Take this as the compliment it’s intended to be – that’s next-level nerd to me. I felt clever putting the API key in the URL (not very secure, of course).
Makes you realize the big push for different, more secure auth methods huh! Decoding a base64 encoded string containing a username and password is trivial.
EDIT: Then you could use an on-premise data gateway to have Power BI Online auto-refresh published reports that use the endpoint.
Have you actually accomplished this in coordination with REST v2 and the HTTP headers you mentioned?
I can get the report to pull in the data, but it doesn’t seem to allow connection to an on-premise gateway and the credentials fail as configured in the http headers.
Yes we have this working with the workarounds mentioned above. May be an issue with your gateway installation/configuration. It looks like you have that data source in your screen shot configured as a cloud connection, not a gateway connection. If you go into that “Maps to:” drop down menu, you may have the option to “Add to Gateway”.
