we've been using SQL Server for quite a while now in our company to host all application data. We use a typical set-up with data being loaded from various data sources into a Staging Area, which afterwards feeds different datamarts for their specific purposes (with tailored data stored there).
However, from my point of view, this generates data "silos", which duplicates so much data and makes it hard to keep all somewhat synchronous. Furthermore, as the number of solutions is growing constantly, the number of "silo" datamarts is growing proportionally, as the requirements are always slightly different and we have a clear instruction to use separate datamarts for each solution.
Apps use direct connections to the datamarts then to consume and manipulate the data (as needed).
I was thinking a lot about this recently and there are some other initiatives ongoing at the moment to modernize our backend, so I did some research and my idea is to propose the following:
- keep the Staging Area as-is (plain import of data from different sources into one datamart). There is one schema for each source and corresponding SSIS packages to load this data. This all should stay.
- create a schema for each solution within the Staging Area. Access rights can be managed on this level then to ensure app consumers can't access the "import schemas" or others, but only the ones that belong to the apps they have access to. As soon as point 5 (API) is implemented, authorization should take place there before connecting to the datamart.
- replace "silo" datamarts and the regarding ETL jobs (SSIS packages) that currently load the datamarts with materialized (aka "indexed") views in the newly defined schemas
- move any tables that are used with CRUD operations from the former app datamart into the corresponding new schema in the "Staging Area" datamart (needs to be renamed, as it now covers the entire data warehouse in one datamart)
- implement a simple API that features GET requests for the defined views and POSTs for required CRUD operations of the tables from point 4
Of course each of these steps has a great impact on the solutions themselves and where they consume data from. Also, of course many hours of development would be needed. But this is not the point for the time being, it's more a general question if this makes sense or how you would set this up on SQL Server.
I just have the feeling that we build so much around the data, which also makes it hard in other areas to improve / speed-up backend processes.
Many thanks in advance and all the best Benny