A post was split to a new topic: Issues with REST BAQ
I was playing around with Data Flows myself. I think this is a key move because it separates the ETL that was embedded into your PowerBI document, into a standalone function that creates governed simplified data that is accessible to all PowerBI users.
The problem I found is the PowerQuery in PowerBI online has significantly reduced functionality from the desktop version. For example, I couldn’t find any way (without writing your own M code) to simply tell it a column that comes over as Text is actually a number (because of the totally broken way Epicor has configured BAQ’s in both the V1 & V2 implementations). All data from the BAQ’s comes over as text and you have to use the Powerquery tools to convert into numbers, dates etc.
The only workaround I found was to do all the work on PowerBI desktop. Then go into the ‘advanced mode’ that shows the ‘M’ that gets created. Then literally copy and paste that into PowerBI Data Flows advanced editor.
Epicor still needs to fix the way they publish BAQ’s in order to not have to create a separate connection for each query which is slow and clunky for projects you create. You should be able to establish one connection (like you do with SQL Server) and then pick the ‘tables’ which should be each individual BAQ.
@Bart_Elia I agree with your statement about needing to move from SQL to Odata as a tools company. But Epicor needs to actually comply with the Odata standard.
As far as I know, and please correct me if I’m wrong, there is no way to connect to the BAQ Service, and then pick off the BAQ’s you want to use in Excel or any other odata consumer. You have to establish a separate login & connection for each and every BAQ you want to consume.
The BAQ’s should appear the same way, for example, the Sales Order Service does where you see all the ‘tables’ of data the sales order provides.
We were looking at options on BAQ but BAQ service is not the resource - the baq itself it the resource. That means the tables are specific to the individual BAQ as a service. If you read that as a cop out, I’ll take that abuse.
We need to support a baq hierarchy as a service to deliver what I think a lot of folks desire. We did this for Electronic Compliance for example - https://www.epiusers.help/t/ssrs-psa-on-filtering-subreports-performance-fixing/55699/12
If we treated BAQ itself as an end point, the meta on that would take out a few datacenters trying to generate the meta. The generators out there like Swagger are just not built for large data that would be visible under all the BAQs visible to a user. We’ve scanned more than a few generators and scoped what it would take to create an incremental loading mechanism for all the meta (open Customer and you’ll see the need as well).
It’s an open question and the PO over REST @bconner has looked at this a few times. There are some other activities underway they are ahead of this on the delivery train atm but it is an interesting problem we wish to tackle.
One option would be a check box on the BAQ setup such as ‘enable REST Access’. Then a company would only check this on the BAQ’s they need to provide external access to which is likely far fewer (less than 50) than total BAQ’s in the system.
When you generate the BAQ hierarchy you would do so for just those with REST Access enabled
You should look at the BAQ security added in … ?10.2.300? and the Access Scope feature added to 10.2.400.
Both were added to manage security on BAQ and REST services.
REST is the basis of everything going forward so definitely start thinking of how to apply it.
Where did you find it?
It’s been a while, I think it’s the url referenced by Jose: