We use a cloud based system to handle patient data. The vendors of that system log ship a copy of their SQL database to use and we restore it locally so we can do our reporting. Those local tables are locked down and set read-only. We don't have control over it.
Now comes the interesting part. We will create another company that will be under our umbrella. The cloud based vendor can't merge two companies on the same database. That means that we will get a second set of a copy of the database of that new company. Hopefully they will contain the same physical layout but based on my experience with the vendor that will not be the case.
So far we got DB #1 called "CompanyA" DB #2 called "CompanyB"
Their structure will be the "same". So we can easily do unions between objects of the two DBs.
For reporting purposes the vendor recommend us to use their slow custom made views. So whatever code we write with those views it won't break if they do an alter to the internal tables of their system. They don't index the views so creating an external view that UNIONS the same view under CompanyA and CompanyB isn't efficient.
Creating a staging process to pull only the data of the objects (table/view) that we use is another option but I don't like the idea of having duplicated data in our systems.
We can buy a system like the one Denodo for data virtualization. This system allows us to create special views (virtual tables) and point our reports or ETLs to those virtual tables. For example our ETL sees only one patient table but underneath that there is a combination of the data from CompanyA.Patients and CompanyB.Patients.
Our end goal is to have a process that is scalable in case our organization decides to keep adding companies into our portfolio and integrating them into our system isn't a challenge like we have now.
So here are my final questions: 1- Does SQL Server has a feature to create virtual tables on the latest releases? 2- Any other recommendation ?