How to handle multitenant data warehouse (each customer has a unique schema)?

Question

so I am trying to set up a data warehouse for a service where each customer has their own database with a unique schema. How do I go about setting up a warehouse so each customer has their own semantic layer / relational model set up automatically (since we (centrally) do not know what is in each database) So that each customer can easily report on their data? Is there any automatic process we can follow? Am I missing something?

Answer 1

It depends on whether you want a consolidated view of the data, or if each customer's data is to remain segregated.

If consolidation is the objective (and there are huge benefits for a multi-tenant SAAS vendor to have a consolidated overview of customer data) then Nithin B's suggestion is good.

If separate warehouses are required, then you'll need to think about how to optimise your costs. The two biggest components will be ETL/ELT, and database hosting.

The fastest way to ETL/ELT is data warehouse automation. You'll find a good list of vendors on our web site (http://ajilius.com/competitors). Look for a solution that will give you the flexibility to meet your deployment options (cloud and/or on-premise), as well as the geographic reach you'll need for accessing customer data.

Will you be hosting your own databases or in the cloud? How much data will each tenant require? A good starting point would be PostgreSQL or SQL Server (SMP), and Ajilius gives you the flexibility to instantly migrate to MPP platforms if your needs outgrow those platforms.

Answer 2

There are many ways to address this.

1. Land all the tables in a Landing area in different schemas.
2. Stage the data into appropriate staging tables for dim and fact loads.
3. Create a dim table to identify the Customer Area. For eg: Dim_Source
4. Load the data into the fact tables. Any specific customers can filter the data from the facts by using the Dim_Source values.
5. This design would help overall Enterprise reporting as well.

Hope that helps.

I would start with a Kimball BUS Matrix.

Cheers Nithin