Creating Cubes and Dimensions Overview:

Creating Cubes and Dimensions Overview:

The Warehouse Designer provides an interface to let you create and edit cubes and dimensions. Multi-dimensional metadata refers to the logical organization of data used for analysis in 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 that are relevant to the multi-dimensional features of PowerMart and PowerCenter.

What is 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 (or redundant data) in tables that are associated with a dimension. This allows you to quickly retrieve various attributes of a dimension without having to perform multiple joins between tables in the dimension.

Key Elements of Multi-Dimensional Metadata:

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	The 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	The 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	The 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.
SnowFlake 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 each of the following steps to create a dimension:

• Enter a dimension description.
• Add levels to the dimension.
• Add hierarchies to the dimension.
• Add level instances to the hierarchies.

Step 1. Create a Dimension

• In the Warehouse Designer, choose Targets-Create/Edit Dimension. The Dimension Editor displays.
• Select Add Dimension.
• Enter the following information:
• Name. Dimension names must be unique in a folder.
• Description.
• Database type. The database type of a dimension must match the database type of the cube. Note: You cannot change the database type once you create the dimension.
• Click OK.

Step 2. Add Levels to the Dimension:

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

• In the Dimension Editor select Levels and click Add Level.
• Enter a name and description for the level. The Level name must be unique within the dimension.
• Click Level Properties.
• Click the Import from Source Fields button. The name of a level property must be unique within the dimension.
• Select a source table from which you want to copy columns to the level. The columns display in the Source Fields section.
• Select the columns you want to add to the level.
• Click the Copy Columns button to add source columns to the level.
• Click the Add Columns button to add a new column to the level.
• Click OK after you add all the columns. The Dimension Editor displays the new level.

Step 3. Add Hierarchies to the Dimension:

• In the Dimension Editor, select Hierarchies.
• Click Add Hierarchy.
• 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 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:

• From the Dimension Editor, drill down to view the levels in the dimension.
• Drag the level you want to define as the root level in the hierarchy. The root level is the level of finest granularity.
• Enter a target table name and description of the target table.
• Click OK. A window displays a listing of all the objects affected by the new level.
• Click OK. The new level displays under the hierarchy.

Creating a Cube:

After you create dimensions, you can create a cube. To create a cube:

• From the Warehouse Designer, choose Targets-Create Cube.
• Enter the following information:
• Cube name. The cube name must be unique in a folder.
• Cube type: Normalized or Non-normalized. Normalized dimensions must have a normalized cube. Likewise, 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.
• Click Next.
• Specify the dimensions and hierarchies to include in the cube.
• Click Next.
• 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.
• Add a name for the fact table.
• Click Finish. The Designer adds the cube and fact tables to the workspace. Next, you need to generate the SQL for the tables.

Editing a Cube:

You can edit a cube in the Warehouse Designer. You cannot edit a fact table or dimension table directly. If you want to change a column in a fact table or dimension table, you need to edit the cube or dimension. To edit a cube:

• Choose Targets-Edit Cube.
• You can modify any settings in the dimension except the following:
• Database type
• Dimension type (normalized or non-normalized)
• Click Close.

Editing a Dimension:

You can edit dimensions in the Warehouse Designer. You cannot, however, change the database type once you create a dimension. If you edit a dimension, the Designer marks all mappings with the dimension invalid. To edit a dimension:

• Choose Targets-Create/Edit Dimension.
• You can modify any settings in the dimension except the following:
• Database type
• Dimension type (normalized or non-normalized)
• 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 Warehouse Designer workspace. When you delete a cube, all fact tables associated with the cube are deleted. When you delete a dimension, all dimension tables are deleted, as well as all references to the dimension in any cubes. To delete a cube or dimension:

• In the Designer, open the repository.
• In the Navigator, select the cube or dimension you want to delete.
• Press Delete. A message prompts you to verify if you want to delete the cube or dimension.
• Click OK to delete the cube or dimension.

Opening and Closing a Cube:

You can open a cube in the Warehouse Designer. To open a cube:

• Open a repository and open a folder.
• Open the Warehouse Designer.
• Select the cube and drag it into the workspace.
• A message prompts you to clear the workspace.
• 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 Warehouse Designer workspace:

• Choose Targets-Close Cube. The Designer closes the cube, saving the layout of all the tables.

Tips for Creating 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 Warehouse 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 and drop 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.