here is my case:
Given there are two tables [tableA] and [tableB]. [tableA] is automatically filled with data from an external system. [TableB] contains let’s say only two columns,
- [columnA] = PK from [TableA] and
- an empty column [columnB]
What I'm looking for is a way to provide an interface to an end user to insert values into [tableB] for each value of [columnA].
So, normally this would be done through a dedicated application, but since money is tight, I'm looking for a solution in the Microsoft 365 landscape.
Requirements:
- Active Directory integration for access control
- Direct DB access for end use is not allowed
Solutions I came up with:
- First, I thought about 'classic' Excel files. Creating an SSIS package that loads the data from [tableB] to an Excel file and puts the file on a fileshare. But honestly, I don't like data manipulation in Excel since things can get messy if the end user messes around with datatype or deletes rows etc.
- Another solution would be writeback to SSAS, but since we have only tabular enabled, and I want the data to be in [tableB], this is also not an option.
- Also, I thought about using PowerAutomate (aka Flow) and Sharepoint. My solution would include a flow that loads the data from [tableB] and puts it into a sharepoint list. Once an edit was made, an update or insert would be made through another flow.
- PowerAutomate/PowerApps. I know this is maybe the most elegant solution, but it is still not quite clear if we are allowed to use PowerApps in our enterprise.
- Will Master Data Services do the job?
What tools do you use or which solution do you have in place?
Thank you for your help!
