Are you passing the API-KEY in the Request Header? I would use Postman first to get your data then move back to Excel.
To use API Key
add header: x-api-key: <keyvalue>
You can generate the API keys in Epicor.
1 Like
You can also pass the API Key in the Query String:
https://EpicorServer/ERP102400/api/v2/odata/EPIC06/Erp.BO.SalesOrderSvc
?api-key=atI7eSN1f1Uuao8gsgtDeWzRv4LiC9OotpUbzFTAPN7mk
As to a few examples
https://[EpicorServer]/[ERPInstance]/api/v2/odata/[Company]/BaqSvc/[BaqName]/Da
ta?[baqParam1]=x&[baqParam2]=y&api-key=[Value]
https://EpicorServer/ERP102400/api/v2/odata/EPIC06/BaqSvc/COM-90daysSORel?api
-key=atI7eSN1f1Uuao8gsgtDeWzRv4LiC9OotpUbzFTAPN7mk
https://EpicorServer/ERP102400/api/v2/odata/EPIC06/Erp.Bo.CustomerSvc/Customers
?$select=EstDate&$filter=EstDate eq datetime'2016-02-22T00:00:00'&api-key=atI7e
SN1f1Uuao8gsgtDeWzRv4LiC9OotpUbzFTAPN7mk
1 Like
Yes, I’m passing the api key in the query string.
I’m betting if you try (your example)
https://EpicorServer/ERP102400/api/v2/odata/EPIC06/BaqSvc/COM-90daysSORel?api-key=atI7eSN1f1Uuao8gsgtDeWzRv4LiC9OotpUbzFTAPN7mk
in chrome it will work. But if you try it in Excel it will not.
Any chance you can test?
Whats your exact Error? If you have a self-signed Cert you must add it to the Trusted Root Folder - Excel is very sensitive when it comes to a valid cert and https…
When you visit it via chrome is your SSL Indicator Green or red?
Ok so turns out this is either a ‘bug’ or ‘works as designed’.
You CANNNOT use API-Keys with Excel 2016 and above and the v2 driver.
You must go into your system configuration file and disable the API key requirement…
Except Epicor will NOT let you modify this file if the client is hosted by Epicor, which is the main reason I need to do this in the first place. If they were on prem, I could just connect to their SQL Server and we would be done.
This same Excel incompatibility is also affecting the cData driver I’m using to pull data into a data warehouse tool
One wonders if this was done just to force people onto Epicor’s own EDA solution which they allow an install of an agent that extracts your SQL Data and sends it to the EDA data center.
3 Likes
Nice Find! Interesting, I dont understand why it wouldn’t support it, it supports other params. Unless OData does something different with its own params like $select= and so on
Your guess is as good as mine but I’ve been pulling my hair out on how to get a decent feed of Epicor hosted customer data into our data warehouse solution and it seems, as of now, it’s literally impossible
Man that sucks. I hope they can change this in the future.
I was always told by Epicor that hosted customers are allowed to replicate their DB for reporting?
I’m not sure what cData uses for their driver but you can build your own PowerQuery Data Connectors. It is currently limited to PowerBI but there is a UserVoice to be able to use them in Excel
Also, I don’t know if XL Connect works with hosted accounts, but if so, their RDL’s have to accessible. You might be able build an RDL using the XLConnect datasource and then pull data using the SSRS OData feeds.
Thanks. Unfortunately my own connector wouldn’t work for my purpose and would be way past my development pay grade anyway.
The XLConnect idea is interesting, but that would mean all customers would need to own XLConnect, which they don’t. And if I can get this working, my end solution would replace XLConnect’s functionality as well as it offers alot more.
Right now my problem is I can’t offer it to anyone hosted by Epicor. They are so darn restrictive with their SaaS hosting that I cringe whenever a new prospect comes our way that is hosted by them because of all the things we can’t do or hoops we have to jump through vs. on-prem or privately hosted customers where we don’t have handcuffs on.
Hey all,
I think we’ve already projected a lot of this info to you via a support case (which i hope has been passed on by now 
) but just in case here’s a direct discussion on it.
So here’s the deal with excel and power query clients + our API Keys. Long story short we needed to add api keys which don’t themselves give you any rights to do anything, but still require a user login so that we can restrict and monitor access by application (say mobile crm) what can be done as well as by user. There is another way to build api keys where the key itself is all you need to log in and make calls and we have that as a maybe on the future roadmap.
The Microsoft folks behind the query component used in excel (and other things) made an assumption that API keys are always authorization keys (no login) and throw an error if you couple up a login + an api key because they think two different login methods at the same time isn’t valid. In our services that’s not correct. We found that out relatively late in our v2 release testing process.
We can’t drop the login + key behavior which we’re going to need for applications that log in with different user accounts and act with those user’s rights, nor can we drop required keys so instead we opted to (for now) direct folks back to api v1. While application integrations are our primary use case for REST we do care about other applications that know how to connect & consume so we do have a few options that may enable this in the future, but have not yet done one. In the mean time the answer is still - use v1 though we understand many of the newer odata consuming apps assume odata v4 which leaves you in a bit of a catch22.
The options just fyi are
-
Add security-imbued api keys. This is what that excel connector is expecting and was on our backlog but we have some security concerns around it so have not yet pulled the trigger. Basically these are erp service accounts bound to an api key + required restrictions on what apis they can call. Easy for api <-> api integration but not appropriate for apps which is what our keys are tuned for now.
-
Add an alternate way to pass the key that wont trigger this bad assumption specifically for these connectors such as accepting the key in the url in some other way.
-
Stop trying to use our existing REST as an ETL source and create our own first class connectors. I will say we’re very aware that our rest services are really not built with bulk ETL or ongoing streaming ETL in mind so even if you make it work it’s still not a great solution in that it’s quite inefficient, but today it’s the only option in SaaS - that won’t be true forever.
Can I ask - what’s your actual data warehouse platform that you’re trying to get data into?
6 Likes
BTW All I thought I’d go ahead and give that CData Odata ODBC Driver a try with our rest services v2 - works great and I’d think it should be a reasonable solution. You just have to know how to fill out the settings to align with our apis:
Here’s an example for the COM-CustJobs baq. Fill out
- The root url for the baq or business object (don’t include /data, etc BaqSvc/BaqName/ is the root here)
- use basic authentication with user/pass
- Under custom headers add your api key X-API-KEY:YourApiKeyHere - this also works if you place the querystring in the custom url parameters like api-key=YourApiKeyHere
Then you can query the one table “Data” using sql. so here in excel i’m just letting it do select *
So - that’s i think a relatively reasonable way to hook up things that want SQL sources with the apis. nonwithstanding the odata source type in power query fighting our querystring additions.
11 Likes
ps. Had not noticed this comment until just now but as the product owner for data platform including REST, EDA, EDD, etc - definitely not. We’d never do this - your data belongs to you and that’s a core principal of our data platform. To paraphrase Tim O’Reilly - we create more value than we capture by having an open data platform and that’s better for everyone.
Especially in the data warehousing space it’s our expectation that a set of customers want an easy to get rolling with solution that’s not overwhelming for mere mortals (EDA), and a set of customers that want to go best of breed or already have a warehouse which they want to expand and retain rather than starting over.
5 Likes
Oh heck yes on this sentiment. I missed that as well (I was off site last week so catching up on all life atm). One of the major aspects of the REST effort is making Epicor more open and allowing you access to your data - ofc so you don’t want to leave. Why would you bother when you have all the breadth and depth of functionality and can get at your data in any way you want and need?
3 Likes
Thanks Brian a lot of awesome info here!! 
1 Like
@bconner Thanks for all the great information!
I have only heard amazing things about EDA and I believe that it is absolutely the solution for 80-90% of the Epicor customers. There will be a very small group who will build their own solution though.
I think an easy feature would be to add modern endpoints to BAQ Exports. You could start with Amazon S3 buckets and Azure Data Lakes (Gen 2). It would be awesome to add One Drive eventually though.
Last, if you are serious about building a data platform, don’t give up! I would love to see Epicor in the Open Data Initiative or maybe even one day use their GitHub repo! Just remember, things change very very slowly, and then all at once!
2 Likes
FWIW we’re very aware of the CDM and the rest of the azure analytics/data stack data lake included. I just came back from build with a whole new set of new services to play around with in fact. You can see us starting to use select things like IoT Hub in products now.
Anyway better ways to ETL to arbitrary other cloud tools esp for analytics and reporting type use cases are coming eventually, but that’s about all i can say at this moment.
Getting epicor to place stuff regularly onto an official github though… ha well i tried but we’re a big boat to turn.
5 Likes
@bconner Awesome! I assume that you already did a lot of this CDM work for Azure Search with your Epicor Virtual Assistant product?
Someone tell the XL Connect Team to stop lobbying against this… sarcasm
Thank you for all the info, and keep up the good work.