locked
Cube design RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I have one SQL database which contains data for 2 clients i.e. my fact table and dimension tables contains data for 2 clients and I have client identifier column in all dimension and fact tables. Now I want to create 2 separate SSAS databases for both the clients. for example I have 2 clients named AAA and BBB so I want to create one SSAS database which will contain data for cleint AAA and want to create another SSAS database names BBB which contains data for client BBB.
    Now on relational database side, I am thinking of creating a relational schema for each client and then planning to create tables under that schema. For example, I have client "AAA" and "BBB", So I will create one relational schema "AAA" and then  fact table and dimension tables under that schema for client "AAA" so it should look like AAA.FactTable1, AAA.DimTable1,AAA.DimTable2...and so on for Client. Similarly for Client "BBB", I am planning to create separate schema with name say "BBB" and then fact table and dimension tables under that schema for client BBB so it would look like BBB.FactTable1,BBB.DimTable1, BBB.DimTable2....so on (table names for both the schemas are same).
    So tables under AAA schema would contain data for client AAA and tables under BBB schema would contain data for client BBB. and so the names of my fact tables and dimension tables in cube remains same.
    Can I do like this and is yes then.. What are the pros and cons of this.
    Wednesday, August 24, 2011 10:36 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    If you decide to have 10 database for 10 clients. It will be like 10 databases + 10 Cubes. Which is lot of maintenance. In my point of view , it is easy to change the DSV files than having 10 different databases for 10 clients.

    • Marked as answer by Challen Fu Thursday, September 1, 2011 10:33 AM
    Wednesday, August 24, 2011 2:31 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    In addition to Sorna's recommendation, also consider implementing rowsecurity using database views, and you can end up with a single code base for the cube. All that is needed is to create different windows accounts that will do the processing of client cubes.

    HTH,

    Hrvoje Piasevoli

    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli
    • Marked as answer by Challen Fu Thursday, September 1, 2011 10:33 AM
    Wednesday, August 24, 2011 4:54 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Instead of creating schemas for two different clients , you can keep the data in one single schema and while creating the SSAS cube in the DSV you can filter out the required data.You can achieve this by creating views for two different clients like Vw_DimProductAAA and Vw_DimProductBBB. Inside the view you can filter the data for the required client and use this view in the DSVs of the SSAS. By doing this it is easy to maintain as you will be having a single source only. Also You can consider partitioning the data based on the Client data for improving performance.In this approach no need to do ETL for two clients seperatly. If you have two schemas you may end up doing ETL seperatly. Hope this helps.

    • Proposed as answer by Thameem Wednesday, August 24, 2011 11:37 AM
    Wednesday, August 24, 2011 11:10 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    I will also go with Sorna's suggestion. Instead of having separate schema it better to create views and handle it there.
    Thameem
    Wednesday, August 24, 2011 11:37 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks for your suggestions, I thought of this solution before but if I want to create cube for suppose 10 clients then in that case every time I will need to modify the DSV and I don't want to do that. I want my cube structure should be same and want to do the changes on database side.
    Wednesday, August 24, 2011 1:16 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    If you decide to have 10 database for 10 clients. It will be like 10 databases + 10 Cubes. Which is lot of maintenance. In my point of view , it is easy to change the DSV files than having 10 different databases for 10 clients.

    • Marked as answer by Challen Fu Thursday, September 1, 2011 10:33 AM
    Wednesday, August 24, 2011 2:31 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    In addition to Sorna's recommendation, also consider implementing rowsecurity using database views, and you can end up with a single code base for the cube. All that is needed is to create different windows accounts that will do the processing of client cubes.

    HTH,

    Hrvoje Piasevoli

    MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli
    • Marked as answer by Challen Fu Thursday, September 1, 2011 10:33 AM
    Wednesday, August 24, 2011 4:54 PM