SSAS - Combine data from mutiple databases to one cube

  • I would like to have my SSAS cube extract data from mutiple databases (on different sites) with completely same schema. I also have one "central" database, all site databases are linked to. I would also like to have additional dimension (or just attribute) telling users from which database/site measures are taken from.

    For example, very simplified, I have several tables on each site/database:

    Table1/Fact table

    ===========

    TaskID

    Date

    Hour (measure)

    Table2/Dimension table

    ===============

    TaskID

    ProjectID

    TaskDescription

    Also I have a table on "central" database:

    Table 3/ProjectTable

    =============

    ProjectID

    ProjectDescription

    Table2 from each site is linked to central Table3 based on ProjectID key.

    Question is: what is best approach to create one cube, merging data from all databases ?

  • Best approach is to combine them into a sql dw first, then load from there into the cube. If you can't do that, then linked severs combined with views could simulate that. Will you have problems with duplicate keys on task id from different databases?

  • SpringTownDBA (10/15/2011)


    Best approach is to combine them into a sql dw first, then load from there into the cube. If you can't do that, then linked severs combined with views could simulate that. Will you have problems with duplicate keys on task id from different databases?

    Thanks for reply...

    Yes, each site DB can have same TaskIDs, so I supposse I should have DW created something like this:

    Fact Table

    ========

    SELECT 'DB1'+TaskID AS Key, Date, Hour FROM srv1.db.dbo.Timesheet

    UNION

    SELECT 'DB2'+TaskID AS Key, Date, Hour FROM srv2.db.dbo.Timesheet

    UNION

    SELECT 'DB3'+TaskID AS Key, Date, Hour FROM srv3.db.dbo.Timesheet

    etc...

    WBS Dimension Table

    ===============

    SELECT 'DB1'+t.TaskID AS Key, t.TaskDescription, p.ProjectDescription FROM srv1.db.dbo.Task t LEFT OUTER JOIN srv0.db.dbo.Project p ON t.ProjectID=p.ProjectID

    UNION

    SELECT 'DB2'+t.TaskID AS Key, t.TaskDescription, p.ProjectDescription FROM srv2.db.dbo.Task t LEFT OUTER JOIN srv0.db.dbo.Project p ON t.ProjectID=p.ProjectID

    UNION

    SELECT 'DB3'+t.TaskID AS Key, t.TaskDescription, p.ProjectDescription FROM srv3.db.dbo.Task t LEFT OUTER JOIN srv0.db.dbo.Project p ON t.ProjectID=p.ProjectID

    etc...

    How to get site (DB1,DB2,DB3) attribute ? Or it will be better to have DB1,DB2,DB3 as new Dimension ?

  • One more question: If I have completely same local cubes at each site, is it possible, to join those local cubes into one "supercube" ? If yes, how ?

  • you could try and use a linked dimension.

    though i would recomened doing the intregration pre-cube in the ETL, and just building one cube that contains all the data

  • Without looking at your model, i'm going to guess and say go with a site dimension vs an attrib of task dimension.

    Also, depending on the number of fact rows, you might keep your key as an integer instead of a string.

    I'd imagine it would be easier to go this route using views and linked severs vs trying to glue together multiple cubes on different machines.

  • One more question: If I have completely same local cubes at each site, is it possible, to join those local cubes into one "supercube" ? If yes, how ?

    Agree with steveb. to create one cube. Don't think in terms of a "supercube." A cube is centrally located. It almost sounds like you want to create several "sub-cubes." Try to avoid that. The cube should be the central respository of "the truth." If security is a concern, you can set permissions by site attribute.

    Use a new field for the fact table primary key. The business key can be the site/id for the site database combination to prevent duplication.

Viewing 7 posts - 1 through 6 (of 6 total)

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