it much easier to clear the view from the SQL code of BAQ Phrase the way i recommend then run it straight a way in MSSM Epicor database, and will be ready for all SQL based applications
@asmar Oops, sorry, I didnāt see the version you working with. I believe REST is available since 10.1.500/600, so the suggestion may not apply to you, however, I believe you should still consider that once you or your customer upgrades to the latest version, they will have that feature available.
@dhewi Yes, I have seen PowerBI receive some Timeout errors when calling BAQs via the REST service too, but I believe that was mainly because the BAQs used were bringing back too much data, too many columns back to the client. Have you get those performance issues with smaller BAQs as well? My tip is to filter and reduce as much as possible the data those BAQs need to bring back to client tool.
3 Likes
To answer the original question, LINQ generated SQL is different from high performance written SQL and I havenāt ever seen anything to convert the two.
@dhewi Look at using Dataflows to load the BAQ data to your SSAS instance for PowerBI. You can now mix SSAS summaries with direct queries now by using a Composite Model. When a user drills down to report details PowerBI will run the Direct Query REST call to return just that detail and it doesnāt have to be loaded into the model.
1 Like
Well, the problem with Power BI is that itās most useful when thereās a massive amount of data, and thatās where REST seems to falter, as you say.
I remain hopeful we can make good use of REST for other projects we have on our agenda, and I think itās a great thing to have available. Iāve just been disappointed in it for this particular purpose and have gone back to direct SQL Server query. I was hoping there might be something Iād missed!
@John_Mitchell - thanks. I think thatās something weāll get to in due time.
@asmar you can either do that, or if you have full freedom on the SQL Server, you could create a separate database where all the custom SQL objects reside, and make direct usage of the Epicor db on your code, example:
USE [YOUR_DB]
GO
SELECT * FROM [EpicorDemo].Erp.GLJrnDtl
That way you donāt create custom objects on the Epicor DB.
Your custom Apps create direct connections to this custom DB instead of Epicor DB.
It sounds like youāre on a similar journey to us, which is interesting. From what weāve found, BAQ to server view is an unhelpful detour, but itās very interesting to see what others have to say and Iāll also be keen to see how it develops.
In our experience, if something is best done outside of Epicor, BAQs become a bad fit. We havenāt found a good substitute for knowing what you want out of the data and crafting a SQL query to get exactly that. As @carlosqt advises, we keep all that in a separate DB on the same instance, and the more complicated constructions rebuild themselves overnight - some summarised data can then be accessed in seconds instead of minutes that way. That DB then holds the data in the form best suited to the non-Epicor app that needs it, and the permissions etc are totally separate. In some cases we even use the same data in External BAQs back into Epicor.
I agree with your suggestion and method.
Probably the best approach for Epicor ERP customers On-Premise.
For Epicor Cloud ERP customers, they need to rely on BAQ + REST, or another complicate replication/sync data between Cloud and On-Premise.
1 Like
So, even though this is a little old, I will add my 2 cents worth for future searchers. Not sure how much rewriting you are having to do, or if it is because we are on 10.2.300, but thus far I have not had any issues cutting and pasting from the BAQ Query Phrase window to SQL SSMS Query window with just a few edits needed. I have thus far used this method to convert 15 BAQs into SQL Stored Procedures to be used by .NET programs and Windows tasks for sending out daily, weekly, and monthly spreadsheets. usually the edits are the same from one query to the next. Mainly, you will have to add joins for the UD extended tables and then change the references in the SELECT statements, but for the most part, SQL SSMS shows you the parts it canāt interpret, you fix those, and youāre ready to rock and roll.
You donāt need to add extra joins for the UD tables.
Simply change the Erp. prefix to dbo.
1 Like
Even easier, thanks!
or remove the Erp. prefix will work as well (if the table has any UD fields)
1 Like