Cube with multiple partitions fails to process - dimension error

[dean12]

Using MSSQL2000 and Analysis Services.

This is my first attempt at setting up a cube with multiple partitions. There are three partions, each points off to a SQL2000 database. All three databases are physically identical in table structure, keys, etc.

What differs is the data.

I have setup all my dimensions as shared dimensions and I can successfully process them; however, I suspect that they are getting their values from only the first partition. This is because when I try to process the cube, the first partition processes fine but it fails on the second partition saying there is a key value that cannot be found in one of the dimensions. In this case, the value it is referring to is a department ID that exists in database 2 but not in database 1.

Should I expect that the dimensions will contain the appropriate values from all three partitions?

 

[MDXer]

You would be correct. The fact table is the only item processed from your partition. This is not to say that your dimension and fact tables can't be on seperate servers. The issue you are experiencing is that while you have a shared dimension they are not built from a single source. for your scenario to work you need to choose one of your systems to be the "master" server that contains all dimension members in a shared dimension structure that exist on the various servers.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[dean12]

I kinda figured this partition thing was gonna be too easy.

 

[MDXer]

Actually once you have things laid out right it is easy. The difficulty is getting the underlying data right.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[dean12]

Yea, but there's always a hitch. Two databases. Each has a list of customers identified by customer #.

No way to merge two separate customer lists where there is overlap of the customer # and customer 1 in one database is a different individual from customer 1 in the other database.

Only thing I can see to do is build a master customer list with some sort of datbase identifier tacked on as part of the key and for the two fact tables, add a database ID there as well so I can match dbID and cust# to the fact records.

Wouldn't you guess that's about the only solution?

 

[MDXer]

How large are the dbs and what resources do you have available? To me the obvious answer is a DW that consolidates in to a single DB which would allow you to implement a star schema utilizing surogate keys to map dimension members to fact records.

That said I understand that a DW is not always an option so what I would look at doing would be to implement a 3rd database which would contain your tables for you shared dimensions. You could then use a quasi method of implementing SKs a possible approach would be to create this DB which had all your patrons from all your systems. Build a process that would auto synch this table with your servers that contain the source data. add to the patron record an identity column which would serve as your SK as well as a column that would be your system designator.

Link this server and db to your various servers. Then create a view that that joins out to this linked patron db based upon cust# and sales system and place the SK (identity column) from tha patron DB as the patron key. THen build your patron dimension from the patron DB. There is some over head to this approach but it may be an alternative to not being abled to go a DW or DM route.

to illustrate:

Patron Dimension Table

PatronKey     CustNmbr   Fname    Lname    Source
---------     --------   -----    -----    ------
1             2345       Joe      Smith    USASales
2             2345       Jane     Brown    Canada

USA Sales Fact view

Select
d.PatronKey
DateKey
LocationKey
Product
Quatity
Cost
From Sales S
   JOIN LinkedServer.PatronDB.PatronDim d ON d.CustNmbr = S.CustNmbr
Where d.Source = 'USASales'


Canada Sales view
Select
d.PatronKey
DateKey
LocationKey
Product
Quatity
Cost
From Sales S
   JOIN LinkedServer.PatronDB.PatronDim d ON d.CustNmbr = S.CustNmbr
Where d.Source = 'Canada'

you would then point your patritions and fact table to the appropriate views. I would even go to the point of generating your sales table in the patronDB and leaving it empty and pointing you cube to this empty table and have one partition for each of your actual sales tables.



Shoot Me! Shoot Me NOW!!!
- Daffy Duck