Is anyone using Views in their on-prem Kinetic databases? Are these now frowned upon? What do you do to service requirements of erp data for other systems in the business?
We use them but in a replicated dB.
Technically I donât think they are supported by Epicor. Because during an upgrade they could be wiped out. But, as long as you have a backup of them and know you may need to recreate them, they wouldnât hurt anything.
3 Likes
As @chadd mentions, you can but others before us had their upgrades blow up. The solution was to create them in a different schema or different databases altogether.
It would be great if views could be created within the framework and (re)built on every regen.
3 Likes
@Mark_Wonsil You have the memory of an elephant my friend. 
@Daniel_White as @Mark_Wonsil pointed out used them for a long time. Although at the time when I was more naive it did add extra steps to keeping live and pilot in sync. Nowdays not so much.
3 Likes
Use external baqs with a different DB / schema as mentioned adding your own can cause upgrade nightmares
2 Likes
Imagine this on SaaS! An empty parallel database or schema with db_datareader privs to production data. Local create/alter/drop permissions for views and functions. Excluding CREATE PROCEDURE of course so it doesnât turn into a storage unit. Indexing views should be disallowed for the same reason.
At this point BAQ is replaced with something more capable and secure, and arguably more aligned with the intent of BAQ. Also makes granting something in the SHOWPLAN ballpark reasonable so we can optimize. Possibly query store so we can identify optimization exceptions. And if a customer still abuses resources, now thereâs a point to apply constraints.
Nah. Shifting BAQ to a parallel schema or database can decouple these customer assets from dependency on the software to exist, and Epicorâs perceived responsibility to maintain.
Move UD tables as well? 
As long as weâre dreaming, why not a UD schema where weâd create/alter/drop tables targeted to their purpose? Just create the columns we need, name them sensibly, and apply relevant indexes.
SQL insert/update/delete seems like too much to even dream about. Surely someone would decide that select * into UD.TragedyOfTheCommons from ERP.LaborDtl, ERP.LaborDtl is a fine thing to do and ruin it for everyone.
Oh, I like that idea.
If it wasnât such a performance nightmare, the extra database could be hosted in the customerâs own tenant. Or maybe Epicor charges separately for that side database based on cpu/storage consumed. 
We used to do views and External BAQâs back in the E9 days all the time because we couldnât do subqueries.
With the advent of subqueries and now even nested BAQâs I donât see a need for it anymore. Why go with a view when you can just make a native BAQ?
What is the view solving for you that you canât do in a regular BAQ?
Views allow you to canonize your commonly structured data reusably. Write tedious SQL once as a view you can simply join like any table. Or invest the time in scraping every bit of performance out of lousy data and packaging that work in a single reusable artifact that the optimization engine will reliably take advantage of.
And then there are functions. For the unfamiliar - think of functions (table valued anyway) as views that can take parameters. Like what new SQL authors use stored procedures for, except functions can only read, they canât write or modify your database or alter security or etc.
âSubqueryâ is just weird nomenclature for normal SQL. Because of BAQ reasons, nested BAQâs canât be equivalent to views and functions, and performance is clumsy and unreliable.
2 Likes
It is allowing access to data to people outside of the IT department so they can create their own âToolsâ that use EPICOR data. Not something that we in IT condone.
We use them on our prod database. Had 1 issue back in the Vantage days when the ERP upgrade failed as the view had a field that got removed (this was from v8 to v8.03). Other than that no issues in 10+ years.
Edit: We also wrap the views back into BAQâs by adding the Epicor database and an external database so we can also use them in Kinetic for dashboards and consistent reporting.
Our main reason is to provide unified data for use across companies without having to duplicate/maintain this data in every single company within Epicor.
Cross company BAQ?
Not all of our users have access to all the companies, but the data is needed at a certain granular level because of lookups when the overseas companies are not in office.
Yeah so subverting Epicorâs security is a valid reason to use a view instead of a baq 
4 Likes
That came up in one of my meetings 
Using a database view also doesnât consume an Epicor license 
3 Likes
Using [the database you paid money for and run on your own hardware with data you put into it using a schema that cannot be copyrighted] also doesnât consume an Epicor license [as it shouldnt].
Fixed that for you 
2 Likes