4

We are just starting to get into business intelligence at our company. We have some architects who are designing our data warehouse and also our data marts. They are using star schema for both designs.

The architects have stated that the design they are using for the marts is the same, that we just need "another copy" of it for whichever customer wishes to query their mart and that the data may differ per mart.

We only had one mart to start with. This was one AIX LPAR, one DB2 database instance, and one database, with one schema.

Now we have requests for three more data marts. What is the best practice? Should these be different schemas within the same database? Should each mart be a separate database within the same instance? Should we have separate databases, separate instances?

What is the best practice here from a physical deployment and maintenance perspective? Or what guidelines are used to define when it should be which option?

Improve this question
4
  • 1
    What do they mean "The data may differ per mart". If they're saying they will have different data marts reporting different values for the same metric it means they're being slack about conforming the data. Commented Jul 2, 2012 at 18:30
  • Will all these be hosted on your servers, or will a data mart be delivered to a customer for use on a customer's server? Commented Jul 26, 2012 at 1:01
  • @ConcernedOfTunbridgeWells - I talked with the architect and discovered that they are designing a set of dimensions and fact tables and these can be combined into specific marts. So we might have an "auto insurance mart" and two customers may each have a copy of that schema (dimensions and fact) but differing data based on their needs. Commented Jul 26, 2012 at 12:44
  • @Catcall - right now these will be hosted on our servers. Where I work we have not "charged back" the business for any IT services. Our department has taken on the cost in our own budget. That being said, things are changing here culturally and I could see department/customer servers with their own marts that we could "charge back" to them in the near future. But for now, all the marts are on our own servers. Commented Jul 26, 2012 at 12:46

1 Answer 1

Reset to default
3

It sounds like you're looking at something similar to, if not identical to, a multi-tenant architecture.

In a multi-tenant architecture, all the tenants (clients, customers) share something. They might share only the server and the dbms, with each tenant getting a private database. Or they might share the database, with each tenant getting a private schema. Or they might share tables, where each row in each table carries a tenant identifier.

So there's a pretty broad spectrum of choices. Each one of these can probably be considered a best practice for some applications, and a worst practice for others.

Your architects seem to be thinking "one database per customer," also known as "shared nothing." (That actually means sharing only the dbms, and usually the server.)

What is the best practice here from a physical deployment and maintenance perspective? Or what guidelines are used to define when it should be which option?

There isn't really a single best practice, because of the spectrum of possibilities. You want to make deployment and maintenance as easy as possible, but you also have to consider what the tenants are sharing.

Separate, private databases make some kinds of maintenance easy, and it makes other kinds of maintenance hard. For example, when tenants share nothing but the server and dbms,

  • adding custom columns, tables, and view for a single tenant is easy, but
  • adding an identical column or table for all tenants is relatively hard.

When tenants share tables

  • adding custom columns, tables, and views for a single tenant is hard, but
  • adding an identical column or table for all tenants is easy.

There are other issues. Disaster recovery is a lot easier with "shared nothing" than with shared tables (also called "shared everything"). With "shared nothing", you can just restore a database. With "shared everything", you have to restore just a few rows in every table. Cost of hardware can be an issue when you scale up. Data isolation and security is easier with "shared nothing", harder with "shared everything".

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.