Changing Shared Dimension

[Bygbobbo]

I would like to change the sharing status on some of my dimensions.

I would prefer to have a specific cube's dimensions only available to one specific cube.

Where do I change this status.

Thanks in advance,
Bygs

 

[JayKusch]

I do not believe you may change a shared dimension to a private on on the fly. You will probably need to recreate your shared dimension, that needs to be private, w/in the cube(s) you plan to use it in. I do not believe there is a toggle/switch/parameter you can set to change it from shared to private. Here is some general information you may want to read before you proceed.

Sharing Dimensions Across Cubes

You commonly build different cubes to represent different aspects of your business. These cubes can be based on different fact tables. When you create a new dimension in Analysis Services, you must define it as private or shared. Choosing to make a dimension private or shared depends on the structure of the fact tables in the warehouse and the business process you are trying to model.

Using Private Dimensions
Private dimensions can be used in only one cube. Use a private dimension when the characteristics by which you want to view and aggregate data exist in only one cube and relate only to a particular fact table.

You can also use private dimensions to simplify cube maintenance. Private dimensions are always processed along with the cube on which they are defined. They do not have to be processed separately. If there are changes in a shared dimension, all the cubes that use that shared dimension must be reprocessed. Creating private dimensions allows you to minimize the processing of multiple cubes. If the cubes are very large, this strategy can help reduce the overall processing time by processing only one cube rather than several. For example, a master customer dimension contains information about general customers of a business. You create a private customer dimension that contains only certain customers. You create a separate cube to analyze the sales to these customers only. This strategy allows you to process the cube containing the sales to special customers without having to process all the cubes that share the master customer dimension.

Sometimes, you want several cubes to use the same dimension structure but with some different dimension characteristics. You might define a dimension that contains custom member formulas. These formulas may only be valid for one cube. For example, a Budgets cube uses an accounts dimension that contains a custom member formula. This formula looks up information from the Sales cube. Even though you want to analyze the information in the Sales cube by members of the accounts dimension hierarchy, you cannot include the accounts dimension in the Sales cube because it results in a circular reference. In this case, you must build two separate dimensions that have the same hierarchical structure but different custom member formulas.

After a dimension is defined as private, it cannot be converted to being shared. Because of this, consider defining a dimension as shared if there is any possibility that it may be used in more than one cube.

Using Shared Dimensions
You can use shared dimensions when defining multiple cubes. Create shared dimensions when you want to ensure that dimension characteristics mean the same thing in each cube where the dimension is used. For example, you create a Product dimension that contains all of the products manufactured by a company. It represents the master list of products and defines a rollup hierarchy as well as many descriptive product properties. The information could be gathered from several sources and scrubbed to eliminate duplicates and inconsistencies. By using this dimension in different cubes that model related business processes (manufacturing, inventory, or sales), you enable several cubes to provide a consistent picture of your business across multiple processes. If you used a different product dimension for each cube, there could be inconsistencies in the interpretation of product descriptions and properties. In addition, shared dimensions make it possible to combine information from two related cubes in a virtual cube. In this case, the shared dimension acts similar to the join column when combining tables in a relational view.

Time dimensions are almost always shared dimensions. We frequently want to analyze a set of business processes by time. A time dimension usually contains a listing of dates for a given time period. Each date can have several attributes, such as day_of_week, day_of_month, is_holiday, or other attributes that relate to legal periods or marketing seasons. In addition to ensuring consistency, a shared time dimension saves you time because you do not have to re-create the dimension for each cube.

You can create a shared dimension from a single dimension table, multiple dimension tables, an OLAP data-mining model, or from the member properties of another dimension. The dimension table or the mining model you select should contain the column or columns you want to include in the shared dimension.

You should make a dimension shared unless there is some explicit reason to make it private. Using shared dimensions can also result in more efficient use of server memory. This is because the members are loaded only once and are not duplicated by those in private dimensions.



Thanks

J. Kusch