Can't use shared dimensions in cube...

[SQLBI]

Hi,

I'm putting together a data warehouse using SQL Server 2000 Analysis Services. I'm using a bottom up approach and building individual departmental data marts.

I have created a database to hold all of my organisation specific information and have created Shared Dimensions based on the tables in this DB.

I then have a Mart Specific DB which holds all of the information specific to that department, which is used for private dimensions and the mart's FACT table.

What i want to do is use the shared dimensions in each cube i create, however in the create new cube wizard, the shared dimensions box is empty. How can i make the dimensions based on another db visible to all cubes regardless of the source db?

Thanks in advance.


Leigh Moore
Business Systems Manager
Vets4Pets Veterinary Group

http://www.vets4pets.eu.com

 

[justins57]

Leigh,

When you create the cube you define the source DB for the cube. I may be wrong, but I think all of your dimension tables or views for this cube have to reside in this DB.

While you can create individual DBs for each cube, generally you would not create individual DBs for each cube. Instead you would create a Fact Table for each cube or Data Mart. The associated private and shared dimension data would reside in the same DB.

Justin

 

[SQLBI]

Hi Justin,

I do have both of my db's registered as data sources in Analysis Manager. The main reason for me trying it this way was to avoid having to append to any tables holding organisation specific data more than once. I will ultimately have 6-7 Marts that will all use dimensions such as regional structures and obviously time.

Looks like i'll have to get clever with procs and triggers to solve my problems.

Thanks again.


Leigh Moore
Business Systems Manager
Vets4Pets Veterinary Group

http://www.vets4pets.eu.com

 

[justins57]

Leigh,

I thought you said you had dimension tables in one DB and fact tables in another DB. The dimension tables and fact table have to reside in the same DB. I apologize for misundertanding your problem.

Justin