Post reply

Data model changes for Multi Tenant datawarehouse

  • July 17, 2012 at 5:19 pm

    #278039

    I would appreciate the recommendation of experienced people in the below scenario. Thanks in advance!

    We have a relatively small datawarehouse DB with 2 Facts tables and around 10 Dimension tables. We now need to accommodate the data from Multiple Tenants in our next release. Each Tenant can have multiple Sites. We have decided that we will be going with "Shared DB, Shared Schema" approach.

    I assume we need to have 2 new Dimensions added for "Tenant" and "Site" with "TenantId" and "SiteId" respectively, as the PKs. If you agree, below are the questions I have. Thanks again.

    1. I would assume we need to add "TenantId" and "SiteId" to the Facts tables. Do they need to be part of PK of Facts tables?

    2. Do we need a Reference table linking Tenant to the Site with a "ReferenceId"? If so, can we use "ReferenceId" instead of "TenantId" and "SiteId" in the Facts tables?

    3. If we add "ReferenceId" to the Fact tables, then we will need to add the Join to the "Tenant" and "Site" tables in all our queries. Could that impact performance.

    Thanks again!

  • July 19, 2012 at 10:32 am

    #1514991

    I'm sorry I'm not in agreement with the approach.

    Provided each tenant actually owns its own data warehouse I wouldn't put all of them together in a single database - I would have a database per tenant.

    This is for a whole bunch of reasons like... one tenant asks you to restore/recover its data warehouse as it was yesterday at 3:35pm, how are you going to do it if everything is a single database?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply