Single or Multi Tenant Data Warehouse

Hi,

We supply software systems to individual care providers. Care providers can range from large national companies with multiple facilities, to small companies with a single facility. We are building a data warehouse & business intelligence solution to help the care providers get better access to the data captured in our systems.

One thing that I'm struggling with is which is the more suitable approach:

• Multiple single tenanted data warehouses, i.e. a separate data warehouse per care provider; or
  
• One multi-tenanted data warehouse, i.e. one data warehouse that contains all care providers, with appropriate authentication and permissions to prevent one care provider viewing another's data (disaster!)
  

I don't even know where to start researching this concept.

If it helps, the structure of the warehouse is going to be the same across care providers (there will be differences in levels of hierarchy here & there, but generally the same processes and measurements are taking place). My guts tell me that one multi tenanted solution is easier to maintain and avoids silo'ing, however my CTO fears that security might be compromised and it might be easier to upgrade individual care providers separately over time.

Any advice hugely appreciated :-)

Brad

A typical solution is to implement individual client/customer databases with appropriate views.  Clients would access via the views.

If you are dealing with retail numbers (customer volume), you need to look at content or row based security at the DBMS level.

But, there is another way to look at it.

You will need to address legal and liability issues a co-mingled database presents. On the other hand it would be the best source for health analytics.

It is all going to depend on your contracts and liability issues and the organizations ability to support its security policies. It is a simple technical problem.

Hi, thanks for your comments.

We are dealing with patients in the UK, our software captures clinical and medication information on a patient level. In the UK the regulation is the Data Protection Act (1998). Our clients (the care providers) are the 'data controllers' and we are the 'data processors', i.e. we process patient data on their behalf for the purposes of them providing care to patients. Our clients, as data controllers, are responsible for informing the patients directly how their data is processed and gaining their consent.

As far as I am aware, there is no legal requirement in the UK to physically store personal information in separate locations. The issue is around end user access. If you take the banking industry as an example, I can access my bank details online, but I can't view the details of someone else who uses the same bank. In our scenario, we are the bank and the care providers are the individual customers.

So as long as we maintain a robust set of processes for user management, permissions and authentication etc., to manage and monitor and reduce the risk of cross contamination of client data, I don't think there are any legal issues to worry about. Correct me if I'm wrong, please!

The question posted here is more about the technical solution to hosting data warehousing for multiple clients.

I've never worked in this environment but I've talked to several folks who have and they all say to use a common database for all tenants. They all say the management of data structures becomes too onerous if everyone is in their own database.

Having one database per tenant is a more contemporary approach.

From a data perspective I agree that one database for combined tenants is better, especially if you want to provide tenant:tenant comparisons, but the trend is currently towards one database per tenant.

The arguments are that it gives you better scalability, better manageability and better security. The downside is that it causes (perhaps) more administration.

The scalability argument is that each tenant can grow to limits that would break a combined database. A contrived example, if I had a petabyte limit on my platform, and had ten tenants each with 100Tb of data, the eleventh prospective tenant would cause me a headache in a single tenanted database. If separate databases are used per tenant, it is just a hardware problem to move them around.

The manageability argument is that I have more flexibility in the service levels that I provide to each tenant. In turn, this potentially enables me to monetise each tenant differently. One tenant wants SSD performance and is prepared to pay for it? Fine. One tenant values capacity over performance? Move their data to different disks.

The security argument is less valid in my eyes, but it purports that separately managed security per database makes it very difficult for a mistake to be made that would grant inappropriate access to the data of another tenant. Possible, but maybe not highly probably in a well managed environment.

That downside point of administration suggests that it requires more DBA and Operator time to manage one database per tenant. This is true, but less important as workloads move to the cloud. For example, today I've been reading about Azure SQL Data Warehouse, and thinking about scenarios exactly like yours. Most of my operational overhead will be shifted to Azure. I'll still have the schema and upgrade issues to manage, which will require more time, but I could make huge savings on hardware, software and related expenses.

I think the opportunities of database-per-tenant, both technical and monetary, outweigh the issues it may create.