Couldn’t the Rabbi circumscribe the DB?
1 Like
Oh I’m certified alright.
2 Likes
A lot of valid points in here. You’ll have to weigh the pros and cons.
Anyway, I can help solve 1 of your problems.
Restricting Tables in the BAQ Designer (Example)
Note this code is not complete, as you would need to add in the security checks,
and also, need to do the basically the same thing on the BO Table List etc…
But anyway, two approaches. Allow & Deny
Allow:
//Method Directive Post-Processing on Ice.BO.BAQDesigner.GetTableList
//Allow List
List<string> allowedList = new List<string>()
{
"Erp.ABCCode",
"Ice.UD01",
"Erp.Customer"
};
List<FullTableListRow> onlyAllowedTables = result.FullTableList.IntersectBy(allowedList, x => x.FullTableName).ToList();
result.FullTableList.Clear();
result.FullTableList.AddRange(onlyAllowedTables);
Deny:
//Method Directive Post-Processing on Ice.BO.BAQDesigner.GetTableList
//Deny List
List<string> denyList = new List<string>()
{
"Erp.PartTran",
"Ice.Menu",
"Erp.Customer"
};
List<FullTableListRow> onlyAllowedTables = result.FullTableList.ExceptBy(denyList, x => x.FullTableName).ToList();
result.FullTableList.Clear();
result.FullTableList.AddRange(onlyAllowedTables);
According to some organization that I belong to (called epiusers.help), I received a badge that called Certified Epicor Consultant… So, i guess I am certified!
2 Likes
I’m reading this for the first time 3 days after it was posted.
Mark, I gotta say this is probably the BEST (or WORST, depending on your perspective) one you’ve made yet.
Along this same line, when I was in college, I owned a '73 Vega and then a '71 Pinto (I survived). In both cases, I could do a lot of the maintenance and repairs myself.
Now I own a '15 Crosstrek and a '15 Forester. I can inflate the tires and probably change the oil if I had to, but beyond that I turn to professionals.
It’s just too expensive to allow mistakes to be made and recover from them.
With appropriate safety limits here, the risk here is minimal.
I’m a non IT user. I’m pretty sure I write more dashboards than most of our IT staff. I use BAQ’s all the time for ad-hoc queries and use BAQ’s for product configurator (yes, moving to CPQ this year.) Our IT staff can write circles around me, but because I know our operations side, it is often much easier and faster for me to write things. I’m not a security manager in LIVE and don’t have a problem. We are slowly adding another BAQ user. She manages our part setup. BAQ’s and DMT together make for a powerful tool to keep that data accurate. I would suggest that this is a business tool not only an IT tool. If you have business managers that are comfortable with that side of things and take responsibility for not doing (too many) stupid things, they can do a lot for company with access and training.
2 Likes
Hi Doug, My first question is will you be at INSIGHTS 2023? If so, hope to discuss this more then. If not, you said your area is operations. Are you a manager? How is security set for you by the IT team, not only to create the BAQs, but to only see tables (data) for your area? EPICOR says user needs to be Security Manager to create BAQs. I have this quoted in a recent case I opened with them. Are you an SQL programmer or database person? Do you link tables? BAQs are read-only with the exception of Updateable BAQs, but I don’t think you are writing those. DMT, on the other hand, is very dangerous since it literally changes live data. I am going to look at some “bolt-ons” like GROW, EDD, or EDA when at INSIGHTS since having timely data enhances decision making. Again, it all comes down to setting the security to minimize the risks of keeping data as need-to-know and potential of server / systems issues due to improper table linking.
That is incorrect. You will need those to do updateable BAQs.
Edit: I spoke too soon. It will not let you save. (It will, but it won’t actually save.)
Hi Tony,
I will be at insights. We have a huge group going. 4 IT, 1 finance, 2 operations people. I’m currently the director of operations. Though I just hired my replacement and will move to dealing with special projects.
In our test environment I do have security manager access. But not in LIVE. Mainly because IT go tired of me asking them to open things up. I’m not a SQL guy. We use product configurator very heavily. When we started I tried to explain the nuances of product and production process to IT folks. It was much easier for me to learn any code needed, than it was for IT to learn our process and product. Since BAQ lookup were a think I needed access to BAQ’s. I started with 1 table in test, which gets most people more than half of what they need. IT would import them into LIVE once reviewed. (That was in E9 so it was much easier to cause problems.) Eventually, I added more tables, subqueries, updatable BAQ’s. Eventually, I added dashboards. Again, I started with test access and IT imported them. Then I needed DMT to updated forecasting. (BAQ’s and DMT together are a great tool.) It is still best policy to build in test and import to live, but I have access in both. In LIVE I cannot add a dashboard to a menu but do everything else. Typically at this point I will build it in TEST and put it on a menu. I will review it with IT. Then I will import it into LIVE and deploy. IT will then put in on a menu.
We have a person whom we are just starting with BAQ. Right now she has access in TEST. She is manipulating BAQ’s that someone else writes. She doe part maintenance, so we have things like run out parts that have 0 inventory so they can become inactive. She might change site or some other criteria. We will then move her to LIVE with the same access and agreement on how to use it. Then she will do 1 table queries on her own in TEST. Then LIVE etc.
I hope that helps. I look forward to meeting you at Insights.
Regards,
Doug Harvey
Director of West Coast Operations
818-505-6290 x427
800-360-5056 x427
818-505-6293 (fax)
818-262-5718 (mobile)
RoseBrand.com
Rose Brand East, 4 Emerson Lane, Secaucus, NJ 07094
Rose Brand West, 11440 Sheldon Street, Sun Valley CA 91352
CA Lic. #: 1024256
*** For Invoice and Credit Card Receipt Copies – Lockstep ***
Hi Doug, Thanks for the contact info. Hope to connect at INSIGHTS. My IT dept is currently me. HelpDesk person starting Tuesday. It will be just me at INSIGHTS. Is your TEST environment on a different server than LIVE? Does if have limited data? Are you on-prem or cloud? Curious to know how IT limited access to tables for the BAQs. Did you have to set Security Manager access in TEST for new BAQ user? Agree on IT deploying to menus. Good back and forth. Thanks Tony (cell) 631-796-3679.
You look at this post?
@tsantaniello
From my experiance with several clients, some of who provided non-IT users access to create BAQ’s, I would not recommend it. I have two that we just recently were able to pull their access. Some of the issues you may run into are:
-Crimpling Performance (untrackable often times)
-Incorrect Data used for business decisions
-Interpreting Data incorrectly for business decisions
-Increased IT support for BAQ users
Most assume databases are just fancy spreadsheets and since they can maniupate complext spreadsheets then they can work with the database directly. However, what is missing is an understanding of the data\business model. Data alone does not provide value, it’s the context of the data. Not to mention the poor naming of fields and abandoned fields.
Great. It looks like you are NYC area. Our IT dept. is all in Secaucus NJ. They know the security stuff better than me and I think would be happy to answer your questions. Contact me directly at and I can connect you.
Our TEST is a duplicate of our LIVE environment. IT copies over every several months or as needed. It is a separate server on citrix in Azure cloud. That’s well past my expertise of the setup. Again, I would defer to our IT team if you have specific questions.
Security Manager does not seem to be needed. I only have security manager in TEST because I test and have my hands in lots of different things.
(818) 262-5718 cell
Regards,
Doug Harvey
Director of West Coast Operations
818-505-6290 x427
800-360-5056 x427
818-505-6293 (fax)
818-262-5718 (mobile)
RoseBrand.com
Rose Brand East, 4 Emerson Lane, Secaucus, NJ 07094
Rose Brand West, 11440 Sheldon Street, Sun Valley CA 91352
CA Lic. #: 1024256
*** For Invoice and Credit Card Receipt Copies – Lockstep ***
Thanks everyone for the spirited replies. To quote a previous reply, “a lot of valid points here.” Pros and cons aside, we are going to take a serious look at EPICOR Data Analytics. It will provide a myriad of data in a cloud environment (no risk to on-prem server) for many functions of the business. I expect it to be more compartmentalized regarding who should see what based on the data (i.e. AR, AP, Inventory, Sales, etc.).
1 Like
a well trained non-IT user is always an asset… also remember that even the best IT user can make horrible mistakes. I have made some doozy mistakes in BAQs and BPMs in the past… this typically happens when you are testing the edges of what can be done with the software.
4 Likes
Our best power users have the ability to create BAQs under supervision and one of them even went so far as to create pivot tables and dashboards. But only IT will deploy to a menu after reviewing the BAQ and dashboard. uBAQs are too dangerous as they are basically BPMs.
As far as pulling bad data, power users quickly learn where the right place to get the data is after beating their heads against the wall.
I remember when we went live in '17 we had a couple cases of runaway queries that would dog the SQL server, eating all the memory but that hasn’t happened in quite some time, maybe there are some improvements but it seems these days the query will time out without noticably taxing the server while it’s working.
Bottom line if you have a material manager, data entry engineer, ops person, account manager and accountant who are power users, it will take a lot of load from IT to work on larger projects.
Potentially dumb solution also, maybe use REST to restrict the tables and let them pull data direct into excel via power query editor or MS Query? Data pulls to excel are great for generating fast DMT templates and you can even script DMT to do the upload via macro button.
2 Likes