Joining excels to tables in published schemas
Hello.. In my use case, business users want to join their local data (excel, csv etc) to published schemas on enterprise data (bookings, billings etc), for adhoc analysis. The tables in the published schema have some row-level security filter logic in the SQL based on SSO. I would like IT to retain control of published schemas, but allow business power users to upload their excel and join to tables in already published schemas.
- Is this possible without creating an additional schema containing the enterprise table and the user uploaded excel file? The power users will not understand the SQL behind the schema table, and I would like to avoid them creating a new schema with their excel each time they want to join a new excel to existing enterprise data.
- If creating a new schema each time is the only way, then it will be a security hole if users can apply their own data security filters. Is there a better place to put the row-level data security SQL filter in that case?
Hi Sanjay Akhare -- one approach a number of our customers take in situations such as this is to:
a) create a new schema owned just by that user with just the tables defined by the excel files. From this new user-owned schema, joins can be made to the published schema tables.
In some cases, however, this presents some security challenges. Often companies don't want to share the published schemas at the physical level with all users. In cases such as this, we recommend
b) creating a new schema that is identical to or a subset of the published schema using nothing but alias tables. Alias tables allow the same tables to be loaded into Incorta once but to be leveraged in different schemas in different ways (aka. different joins). Note that the record level security can't be redefined at the alias table level, it will be enforced at the base table and brought into the alias table.
In short, these new schemas defined solely by alias tables unlock a degree of freedom and ad hoc analysis that doesn't require loading the same data from source multiple times.
Let us know if you think it will meet your need. If it doesn't, let us know here and we will brainstorm with you further.
Sanjay, just a pointer... Anytime when you allow a user to upload data file and allow them to load data into schema tables, you need to provide Schema Manager role for the user. But this role will also enable the user to create new schema and tables. Also if you don't monitor the volume of data uploads, it will impact the available memory for other schema data when you have memory limitation.