Creating Cubes and Dimensions

Creating Cubes and Dimensions Overview:

  • Creating a Dimension,
  • Creating a Cube,
  • Editing a Cube,
  • Editing a Dimension,
  • Deleting a Cube or Dimension,
  • Opening and Closing a Cube,
  • Viewing Metadata for Cubes and Dimensions,
  • Cubes and Dimensions,

Overview:  The Target Designer provides an interface to enable you to create and edit cubes and dimensions. Multidimensional metadata refers to the logical organization of data used for analysis in online analytical processing (OLAP) applications. This logical organization is generally specialized for the most efficient data representation and access by end users of the OLAP application. The following sections provide an overview of the concepts relevant to the multi-dimensional features of PowerCenter.

Understanding Multi-Dimensional Metadata

The multi-dimensional model is a key aspect of data warehouse design. A well-designed dimensional model can help you organize large amounts of data. The dimensional model was originally created for the retail industry, where analysts view business data by simple dimensions, such as products and geographies. This dimensional model consists of a large central fact table and smaller dimension tables. The fact table contains the measurable facts, such as total sales and units sold, and disjoint dimensions represent the attributes pertaining to various business segments of the industry. The central fact table is the only table in the schema with multiple joins connecting it to the dimension tables. The dimension tables in turn each have a single join connecting them to the central fact table.

There are different types of multi-dimensional models depending on the degree of redundancy in the logical schema. More redundancy can improve the efficiency of data access but represents a less normalized view of the logical schema. The most common type of a multi-dimensional schema is called a star schema. A star schema is a normalized multi-dimensional model where each of its disjoint dimensions is represented in a single table.

Another type of a normalized multi-dimensional model is a snowflake schema. A snowflake schema is logically similar to a star-schema except that at least one dimension is represented in two or more hierarchically-related tables. The star schema can become a snowflake schema if the product dimension is represented by means of multiple tables. For example, you could add one dimension table for the main product attributes, one for the brand attributes, and one for a specific brand attributes.

Non-normalized multi-dimensional models have duplicate attributes in tables that are associated with a dimension. You can quickly retrieve various attributes of a dimension without having to perform multiple joins between tables in the dimension.

 Key Elements of Multi-Dimensional Metadata

The key elements of multi-dimensional metadata:

Term Definition

Aggregate Pre-stored summary of data or grouping of detailed data which satisfies a specific business rule. Example rules: sum, min, count, or combinations of them.

Level A specific property of a dimension. Examples: size, type, and color.

Cube A set of related factual measures, aggregates, and dimensions for a specific dimensional analysis problem. Example: regional product sales.

Dimension A set of level properties that describe a specific aspect of a business, used for analyzing the factual measures of one or more cubes which use that dimension. Examples: geography, time, customer, and product.

Drilling Drilling is the term used for navigating through a cube. This navigation is usually performed to access a summary level of information or to provide more detailed properties of a dimension in a hierarchy.

Fact A fact is a time variant measurement of quantitative data in a cube; for example, units sold, sales dollars, or total profit.

Hierarchy Hierarchy concept refers to the level of granularity represented by the data in a particular dimension of a cube. For example, state, county, district, and city represent different granularity in the hierarchy of the geography dimension.

Measure Means for representing quantitative data in facts or aggregates. Example measures are total sales or units sold per year.

Normalization A process used for reducing redundancies and removing anomalies in related dimension tables in various hierarchies.

Redundancy Term used for referring to duplication of data among related tables for the sake of improving the speed of query processing.

Star Schema A normalized multi-dimensional model in which each disjoint dimension is represented by a single table.

Snow Flake Schema A normalized multi-dimensional model in which at least one dimension is represented by two or more hierarchically related tables.

Creating a Dimension

Before you can create a cube, you need to create dimensions. Complete the following steps to create a dimension:

1. Enter a dimension description.

2. Add levels to the dimension.

3. Add hierarchies to the dimension.

4. Add level instances to the hierarchies.

Step 1. Create a Dimension

To create a dimension:

1. In the Target Designer, click Targets > Create/Edit Dimension.

The Dimension Editor displays.

2. Click Add Dimension.

3. Enter the following information:

  •  Name. Dimension names must be unique in a folder.
  • Description. Enter a description for the dimension. This description appears in the Repository Manager.
  • Database type. The database type of a dimension must match the database type of the cube.

Note: You cannot change the database type after you create the dimension.

4. Click OK.

Step 2. Add Levels to the Dimension

After you create the dimension, add the needed levels. Levels hold the properties necessary to create target tables.

To add a level to a dimension:

1. In the Dimension Editor, select Levels and click Add Level.

2. Enter a name and description for the level.

The Level name must be unique within the dimension.

3. Click Level Properties.

4. Click the Import from Source Fields button.

The name of a level property must be unique within the dimension.

5. Select a source table from which you want to copy columns to the level.

The columns display in the Source Fields section.

6. Select the columns you want to add to the level.

7. Click the Copy Columns button to add source columns to the level.

8. Click the Add Columns button to add a new column to the level.

9. Click OK after you add all the columns.

The Dimension Editor displays the new level.

Step 3. Add Hierarchies to the Dimension

To add a hierarchy to a dimension:

1. In the Dimension Editor, select Hierarchies.

2. Click Add Hierarchy.

3. Enter a hierarchy name, description, and select Normalized or Non-normalized. Normalized cubes restrict redundant data. Non-normalized cubes allow for redundant data, which increases speed for retrieving data.

Step 4. Add Levels to the Hierarchy

After you create a hierarchy, you add levels to it. You can have only one root level in a hierarchy. To add a level to a hierarchy:

1. From the Dimension Editor, drill down to view the levels in the dimension.

2. Drag the level you want to define as the root level in the hierarchy.

The root level is the level of finest granularity.

3. Enter a target table name and description of the target table.

4. Click OK.

A window displays a listing of all the objects affected by the new level.

5. Click OK.

The new level displays under the hierarchy.

Creating a Cube

To create a cube:

1. From the Target Designer, click Targets > Create Cube.

2. Enter the following information:

Cube name. The cube name must be unique in a folder.

* Cube type. Choose Normalized or Non-normalized. Normalized dimensions must have a normalized cube.

Similarly, non-normalized dimensions must have a non-normalized cube.

  • Database type. The database type for the cube must match the database type for the dimensions in the cube.

3. Click Next.

4. Specify the dimensions and hierarchies to include in the cube.

5. Click Next.

6. Add measures to the cube.

You can copy columns from source tables or add new columns.

Measure names must be unique within a fact. Level names must be unique within each cube.

7. Add a name for the fact table.

8. Click Finish.

The Designer adds the cube and fact tables to the workspace.

Editing a Cube:

You can edit a cube in the Target Designer. You cannot edit a fact table or dimension table directly. To edit a column in a fact table or dimension table, you need to edit the cube or dimension.

To edit a cube:

1. Click Targets > Edit Cube.

2. You can modify any settings in the dimension except the following:

*  Database type

* Dimension type (normalized or non-normalized)

3. Click Close.

Editing a Dimension:

You can edit dimensions in the Target Designer. You cannot, however, change the database type after you create a dimension.

When you edit a dimension, the Designer marks all mappings with the dimension invalid.

To edit a dimension:

1. Click Targets > Create/Edit Dimension.

2. Optionally, modify any setting in the dimension except database type or dimension type.

3. Click Close.

Deleting a Cube or Dimension

You can delete a cube or dimension from the Navigator in the Designer. Unlike target tables, you cannot delete cubes and dimensions in the Target Designer. When you delete a cube, you delete all fact tables associated with the cube. When you delete a dimension, you delete all dimension tables and references to the dimension.

To delete a cube or dimension:

1. In the Designer, open the repository.

2. In the Navigator, select the cube or dimension you want to delete.

3. Click Delete.

A message prompts you to verify if you want to delete the cube or dimension.

4. Click OK to delete the cube or dimension.

Opening and Closing a Cube

You can open a cube in the Target Designer.

To open a cube:

1. Open a repository and open a folder.

2. Open the Target Designer.

3. Select the cube and drag it into the workspace.

A message prompts you to clear the workspace.

4. Click OK to open the cube.

The Designer clears the workspace and displays all fact and dimension tables associated with the cube. To close a cube in the Target Designer,

click Targets >

Close Cube.

The Designer closes the cube, saving the layout of all the tables.

Viewing Metadata for Cubes and Dimensions

You can view the metadata for cubes and dimensions in the Repository Manager.

To view cube or dimension metadata:

1. In the Repository Manager, open a folder.

2. Drill down to the cube or dimension you want to analyze. The Repository Manager displays the metadata for each object.

Cubes and Dimensions. Consider the following tips when working with cubes and dimensions:

  •  If you want to copy a cube, you need to copy the folder that stores the cube.
  • To view the levels of a cube or dimension, you can either edit the cube or dimension or use the Navigator in the

Repository Manager.

  • You cannot revert to previous versions of cubes or dimensions.
  •  You can delete a cube or dimension from the Navigator.
  • You can delete a dimension using Targets > Create/Edit Dimension.
  • You cannot delete cubes and dimensions from the Target Designer workspace.
  • If you want to change a column in a fact table or dimension table, you need to edit the cube or dimension. You cannot edit a fact table or dimension table directly.
  • If you delete a level, the Designer deletes the associated level instances in hierarchies. The Designer also removes the level instance from any associated cubes.
  • A primary key is generated for each fact and dimension table. The format is GK_TABLE_NAME.
  • A foreign key is added to the appropriate fact table when you associate a dimension level instance to the fact table.
  • You can drag a cube to the workspace and then edit the cube and cardinality by double-clicking the links.
  • You cannot delete or create a link between fact and dimension tables in the workspace. You can only delete and create links in the Cube and Dimension Editors. You can create a graph in the hierarchy.

Let me know if you have any further, comments welcome here.