Question regarding private vs. shared dimensions

[BKhanna]

I am trying to understand private and shared dimensions. If I have a single 'location' dimension table that is common between two star schemas in a database, can I use this table to create 2 private location dimensions for 2 cubes in a database or does the location dimension have to be a shared dimension? I would like to keep the dimensions private so that the 2 cubes don't have to be processed together, yet the maintainance would be less if there is one source table feeding these. See example below. The first cube would consist of Location, Organization and Time 1, the second would have Location, Earnings Code and Time 2.

Location
Organization Earnings Cd
\ / \ /
Fact table1 Fact table2
/ Time1 Time2


Thanks for your help!

 

[johnherman]

You can keep identical private dimensions separate or use a single shared dimension at your option.

As you mentioned, there will be two jobs to maintain the Location dimension if they are kept private for the two cubes, three jobs for three cubes, four for four, etc, but only one job, either incremental or full refresh for a shared dimension.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[BKhanna]

I guess my question is, can I have a single table feed two private dimensions? This way, the Location table would only have to be updated one time, and each time a cube is run, its location dimension would be updated from this table.

 

[johnherman]

Yes

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[MDXer]

Your best option is to Share dimension if the exact dimension structure is to be used in multiple cubes. Not only is administration simplified with the need to only update a single dimension but performance of the server increases

<<Mental Note>>I swear one day I will write a FAQ on dimensions
<</Mental Note>>

When analysis services starts, all metadata from the various dbs cubes and dimensions are read into memory. Then Dimensions Structures, members and member properties are read into memory. The more dimensions the more emory being occupied. If your dimension is 5mb then by building it as a private dimension for 2 cubes you will occupy 10mb.



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