Hi,
I am about a year into an implementation and have used BAQ’s for various things but originally I come from various other ERP’s where if you want custom data you just write a select statement (in most cases directly in the ERP itself). The BAQ’s just seem like a limited and buggy abstraction of SQL. By trying to make it more accessible they have made it less so in my opinion. Nearly every ERP I have ever used handles custom data better. You can get to a result quicker and with less hoops to jump through. I seems to me that BAQ’s were designed for an audience which fundamentally doesn’t exist and now every customer has to live with it because it is so ingrained in the product.
Take by way of example SAP B1.
If I want custom data I simply open up a query window and write a select statement, click run and I have the data. Click save give it a name and its done. When ever I run the query any field SAP recognizes as an object in itself I can use to drill into to get to the master data. It takes less than a minute.
Parameter based queries just as easy simple include a parameter and SAP asks for it when you run the query.
Don’t get me wrong SAP B1 implementation of custom queries is basic and I mean basic but fundamentally that basic route seems so much better than the completely around the houses approach that Epicor takes with BAQ’s. It seems as well that every time the product changes they have to redesign all the BAQ stuff that goes along with it and yet the traditional SQL statement hasn’t altered at all.
So what does everyone else think? which would you rather have “sql queries” or “baq’s”
BAQs are one of the most powerful tools available in Epicor
They are solid and provide a layer of autonomy for users that can’t write SQL which most systems don’t allow.
As far as being limited I’d be hard pressed to find much you can’t do with a BAQ my co worker @Banderson has delved far and wide into BAQs and again while there are some limitations they are minor.
They provide no code access to all your data while accounting for user security and access. Personally I think they are the killer feature in Epicor along with functions.
I am not sure what you mean by having to redesign all BAQ stuff because I haven’t had to touch BAQs on an upgrade in forever.
I are you by chance using the web based BaQ Designer?
I’ve never had any of my BAQ’s break on updates. I do wish it was possible for BAQ’s to be two-way - that you could write SQL and it would make a diagram or you could use the designer. But I understand that would be extremely difficult for a relatively minor feature improvement.
As someone who only has knowledge of SQL from what I have learned on this forum, the BAQs in Epicor are my only tool to develop the Dashboards management wants to see. It was easy to learn and I am now pretty dang quick at finding the data I need. IF I knew SQL I could see using the Select statement would be faster, but as Jose pointed out it provides us “Low-Coders” a tool to get the job done.
BAQs are one of the most powerful tools available in Epicor
I think I would agree with that.
They are solid and provide a layer of autonomy for users that can’t write SQL which most systems don’t allow
Actually I think select statements are very simple SELECT fields FROM tables WHERE criteria. I have taught people select in minutes and I have known IT professionals give up on BAQ’s in frustration. I would argue that its easier to teach someone SQL than it is to teach BAQ’s and most would find it far easier to remember. But that is just my opinion.
As for limitations they are a subset of SQL no one could possibly argue otherwise.
I am not sure what you mean by having to redesign all BAQ stuff because I haven’t had to touch BAQs on an upgrade in forever.
Just one off the cuff current example: If you include a parameter in an BAQ and then run BAQ export in kinetic it will not ask for the parameter and it will return all of the results. If you run the same BAQ in classic it will ask you for the parameter and it will filter the results. Same function, same BAQ, different version, different result
Both the BAQ designer in classic and in the web are actually marvels as far as software design in concerned. I don’t doubt for a moment the skill involved, I simply question the necessity given that SQL just works.
Ohhh. I’m still letting Kinetic ripen. I’m a picky eater.
I haven’t used other ERP systems so I can only speak to what I’ve experienced. Most of my frustrations with BAQ’s have come from not understanding the underlying data (or poorly stored data… looking at you, ~ separated lists…) or from not knowing what kind of transformations were needed (aka I didn’t know how to write the SQL either)
Are you saying SAP lets you write raw unrestricted SQL right through the UI? That seems really dangerous
Yeah the Kinetic UX still needs a lot of TLC particularly when it comes to the technical tools.
As a Software Engineer who primarily loves to write code and used to think like you at the beginning of my interaction with BAQs I have come to rely and love BAQs once I got used to them and how to quickly whip something up I can now write a BAQ much faster than I would a complex SQL query requiring a CTE joins and subqueries. It’s a tool like any other and if I REALLY want to go straight to the DB I can write LINQ in Functions and or even directly in the BAQ Designer by using UBAQs GetList BPM hook.
Overall I don’t miss direct SQL access and I would be terrified to allow a regular user to write even simple select statements… Can you imagine the mess they would make with a bad cross join between PartTran and TranGLC poor poor server.
Don’t you think its quite telling that when we try to describe data queries we naturally drop to SQL and then try to work out how to implement that SQL in BAQ’s. This forum is full of examples like that.
I think I might start calling BAQ’s (B)ad (A)t (Q)ueries, lol
Yes and Yes , you can write update queries , delete queries etc. so you do have to be really careful who has access to what . but it 9 years of using it I never had a single issue.
That’s because SQL is a universal query language so its easier to use it to describe things and it is what BAQs use in the back end, you just can’t type it into a box. I think is just a problem with comfort, once you understand the tool and how to use it it can become second nature just like SQL might.
Its funny these same things you are saying I said myself back when I started messing with BAQs in Vantage 8, the tools have come a long way and so have I in this aspect I guess lol
mmm…I think I get where you are coming from but were we not all regular users when we started. Not knowing how joins work can get you into just as much trouble with BAQ’s as with Select statements and I would argue that it can make it even more obscure as to know why.
Your comments about software engineering are quite interesting. I too develop software and its seem to me that Epicor is moving in the direction of web technologies and interoperability and I wonder how BAQ’s as a technology fit into that future. I suspect in the long term it will become extinct possibly before Epicor’s time or possibly with it. Just doesn’t seem like a technology that has any possible benefit outside of the system that has been built around it. SQL however is already the bedrock on which almost all ERP’s are built. If you know SQL you can get data out of any ERP but if all you know is BAQ’s…
select * from dbo.Partbin
where partnum = mypartnum
sure, it’s easier to write the direct sql.
But as soon as I start joining other tables, the visualization that baq’s give me to be able to see what’s going on make BAQ’s way more powerful for me to be able to see what’s going on. If I had to parse a complicated SQL query and understand what it’s doing it would take me a LONG time to be able to just see it as simply as I can see it in a BAQ.
Everyone get’s frustrated with new things, especially when they have an old way that they can do it. That’s why people quit jobs because things are changing. I always find it funny that people can’t handle a new change (like a new ERP system) and so they quit and go to a TOTALLY NEW JOB… That’s more change by orders of magnitude.
But it’s easier to go somewhere where you don’t know how “It’s always been done” so you aren’t stuck in your old ways.
Programming languages are the same. You get used to one, and it’s a pain to change to something else.
That’s because SQL is a universal query language so its easier to use it to describe things and it is what BAQs use in the back end
you said it, you cannot take it back
I don’t doubt for a moment that intelligent people like yourself can become adept and even expert in BAQ’s. I am just playing devils advocate and wondering why we have to? We have SQL and no one but Epicor wants (or feels they need) for BAQ’s.
I suspect that Epicor a long time ago went down a road for the best of reasons but those reasons faded away and the world changed and now they cannot get back up that road and that is the primary reason why BAQ’s exist today.
The big benefit in my mind is that schema changes are handled for you during upgrades. If you write your own views, sps, etc in sql server, then everything breaks when you upgrade.
So there’s a few things that the BAQ does for you that you don’t see and if one had to do it in SQL would be a nightmare. For example:
BAQs respect Field Security. Epicor implemented field security at the application level making it easier to manage by security groups. No need for Altering the database schema.
BAQs respect Company and Site security. An SQL author would have to remember to correctly return different results based on the user running the query.
BAQs respect Territory Security. An SQL script would have to limit records based on the territory that the user has access to.
BAQs hide User Defined field details from the author. An SQL programmer has to know to use a view or manually make the linkage.
BAQs hide the details of the PatchFld table. While Epicor doesn’t make any schema changes at the patch level, sometimes they need to add a field. The PatchFld table is a hack to add a field without changing the schema and a BAQ will automagically handle this for you.
I have to claim ignorance here but I wonder about caching too. I’m not sure if BAQs account for data not yet flushed to the database. However, this is a big difference with some web applications and historical database applications many of us are used to. The database is not necessarily the source of truth.
Even in SAP HANA, where the database is entirely in memory, it’s quite possible that you won’t find the most recent data in the database but have to use SAP methods (or “BAQ”) to get the correct information. I haven’t looked into it to know for sure.
Systems that follow a CQRS model, like Stackoverflow, where the updates and reads are separated cannot directly query a database directly since the most common information is cached.
SQL just works if you know how to work it.
**EDIT: SQL requires intimate knowledge of the database design and most users don’t know it and most developers don’t know they don’t know it.
But as soon as I start joining other tables, the visualization that baq’s give me to be able to see what’s going on make BAQ’s way more powerful for me to be able to see what’s going on. If I had to parse a complicated SQL query and understand what it’s doing it would take me a LONG time to be able to just see it as simply as I can see it in a BAQ.
ok not sure what you are using for looking at SQL, there are plenty of tools that allow you to see diagrammatically what an SQL query does and trust me it looks a lot better. also many IT guys I know that work with BAQ’s look at the “estimated SQL” to figure out what is wrong with the BAQ as it’s quicker that way.
Programming languages are the same. You get used to one, and it’s a pain to change to something else.
Not sure about that, I know about 20+ programming languages and enjoyed developing in all of them. Sure its a challenge but what is life without challenge. My point is that BAQ’s are not a language. They have no purpose or usefulness outside of Epicor. However anybody on this forum who has gained any knowledge of SQL from working with BAQ’s will almost undoubtedly benefit from that knowledge in their career. Just think of how much value you would be to your next company if you had spent as much time perfecting your SQL skills as you did your BAQ skills. SQL skill is just so much more valuable than BAQ in the job markets.
