October 14, 2011 at 4:00 pm
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 ?
October 15, 2011 at 3:52 pm
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?
October 16, 2011 at 12:58 am
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 ?
October 16, 2011 at 2:03 am
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 ?
October 16, 2011 at 1:44 pm
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
October 16, 2011 at 8:24 pm
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.
October 17, 2011 at 2:50 am
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