Introduction | Information Center

Creating and editing dimensions


You must enter information to define the dimensions and levels for the IBM Alphablox cube. Optionally, depending on your data structure, you might also need to specify joins, attributes, and other information.

Creating and editing dimensions
Creating and editing fact table joins
Creating and editing dimension joins
Creating and editing levels (and ordering of level members)
Creating and editing attributes
Creating and editing member ordering within a level
Creating persistent calculated members

Creating and editing dimensions

To create or edit a dimension:
  1. Log into the IBM Alphablox home page as a user who is a member of the administrators group.
  2. Click the Administration tab and then click the Cubes link.
  3. Select a IBM Alphablox cube from the list of cubes and click the Edit button. The IBM Alphablox Cube Administration dialog for the selected cube appears in a new web browser window.
  4. In the IBM Alphablox cube tree on the left, click the Dimensions label. In the right panel the Create Dimension button appears. To edit an existing dimension, click the dimension name and the existing dimension definition appears.
  5. Click the Create New Dimension button to create a new dimension or select a dimension from the Dimensions list to edit an existing dimension.
  6. In the Name text box, enter a name for the dimension. Allowable characters for dimension names are A-Z, a-z, 0-9, underscore (_), and space. The name specified here will appear in IBM Alphablox cubes. For a dimension listing product members ,you might enter Products to appear as the dimension name.
  7. (Optional) In the Description field, type a short description of the dimension. This description is a comment field only; it has no effect on the dimension definition.
  8. Specify the Dimension Type. By default, Regular is selected. If the new dimension is related displays time values, select Time. Specifying the dimension type as Time is required for MDX functions that use to work properly.
  9. (Optional) Type a default member in the Default Member field. The value you enter will be the member displayed by default. For example, in a Time dimension, it is common to have the current year as the default member to be displayed. In a Time dimension, to select the year 2005 as the default member, you might type [Time].[2005] as the value. Note: If no default member is specified and the dimension does not have an "All" level, the first visible member becomes the default member.
  10. From the Hierarchy Type list, choose the appropriate type of hierarchy being represented. The default value is balanced/standard. Hierarchy type options include balanced/standard, ragged/standard, unbalanced/recursive, and unbalanced/standard. When unbalanced/recursive is selected, the Data Membersoption allows you to select Showing or Hidden (the default value). For details about ragged and unbalanced hierarchies, see Support for ragged and unbalanced hierarchies.
  11. For the 'All' Level settings, indicate whether you want your dimension to display an "All" level by selecting the Has 'All' level option. By default, this option is selected. You can also specify the 'All' Level Member Name. For example, if you have a Product dimension, you may choose to set the default all level member name to All Products. If you do not type a value in the 'All' Level Member Name, the default member name will be displayed as All Product instead.Note: When 'All' Level Member Name is left blank, "All" is translated based on the server locale. You can override this by specifying the 'All' Level Member Name based on what you want to appear in the user interface.
  12. Click the OK button to save the dimension.

After you have created your new dimensions, you can begin defining the required fact table joins and dimension joins.

Creating and editing fact table joins

For star or snowflake schemas, you need to define a fact table join between the fact table and each table directly related to the fact table. If you have a snowflake schema, you must create a fact table join for every table that is directly joined to the fact table. In a snowflake schema, other related tables that are not directly related to the fact table also require dimension joins.

To create or edit a fact table join in a dimension:

  1. Click on the Fact Table Join node under the new dimension.
  2. To create a fact table join, click the Create New Join button. A join specification page appears. If a fact table join already exists, expand the Fact Table Join folder and click on the join.
  3. In the Expression field, type an expression that specifies the fact table join. You can also use the Expression Builder to assist you in entering an expression defining the join. Example:
    @col(qcc_fact.Week_Ending) = @col(qcc_time.Week_Ending)
  4. Click the Apply button to apply and save these settings without closing the dialog. Click the OK button to save the level definition.

Creating and editing dimension joins

A dimension join is a join between related tables in a dimension that are not directly related to the fact table. Dimension joins are used only with snowflake schemas.

To create or edit a dimension join in a dimension:

  1. After creating a new dimension using the Cube Administration dialog, click on the Dimension Joins node under Joins folder of the new dimension.
  2. To create a new dimension join, click the Create New Join button. A dimension join page displays. To edit an existing dimension join, expand the Dimension Joins folder and select the join you want to edit.
  3. In the Expression text box, type an expression that specifies the dimension join. You can also use the Expression Builder to assist you in entering an expression that defines the dimension join.

    For the QCC-ACS example cube, if you had a related Product Family table (as you would in a snowflake schema) the product family name would come from the Product Family table, not the Products table as it does now. Since the Product Family table is related to the Products table, and not the fact table, you would use a dimension join like the following example:
    @col(QCC_PRODUCTS.FAMILYID) = @col(QCC_PRODUCTFAMILIES.FAMILYID)
  4. Click the Apply button to apply and save these settings without closing the Cube Administration dialog. Click the OK button to save your changes and close the Cube Administration window.

Creating and editing levels

For each dimension, you can specify levels in a dimensional hierarchy. At least one level is required for each dimension. Levels are used to indicate a position within a hierarchy. For example, in a Time dimension, you could have levels for Year, Quarter, Month, and Week.

The 'All' level is a level at the top of the hierarchy with a single member. This member in the 'All' level is know as the 'All' level member and represents the aggregation of all members below it as it is modeled by the level objects in a dimension. For most dimensions, it makes sense to have an 'All' level, but for the Scenario dimension, and sometimes a time dimension, you will not want to display an 'All' level.

For each level in a dimension, you must have a level key defined. A level key consists of one or more level key expressions. Taken together, the level key expressions uniquely identify each member in the level. For example, the level key for a city level might consist of three level key expressions <Country Name, State Name, City Name>, or it could consist of a single expression (for example, <city_id>). The order of the level key expressions might influence the member ordering in the level if member ordering expressions have not been defined. Note that best practices dictate that level key expressions should not be completely nullable (at least one of the level key expressions should be not null for each member of the level).

When you have an unbalanced or ragged hierarchy and use standard deployment, then your dimension table might have null values. Suppose you have a geography table which includes the following columns:
Country Region State City City_ID
USA West California San Jose 1
USA East <null> Washington, D.C. 2
Canada Quebec <null> Quebec 3
Canada Quebec <null> Montreal 4
Canada British Columbia <null> Vancouver 5

When you model a dimension like this, you need to create four level and specify a unique level key for each level. An initial reaction might be to create the following four levels:

Looking at the data represented above, there are two countries, four regions, and five cities. But, how many states are there? Because of the null values, at first glance it seems that there is only one state (California). The IBM Alphablox Cube Server, however, will create internal "dummy" States for the null values. If you drill into Quebec, for example, you will see the Quebec and Montreal. To the end user, there is only one state, California. Because of this internal representation, there would be four members in the State level (California, <null>, <null>, and <null>). This would result in the cube not starting because there are three members (the three <null> values) with the same key at the State level.

To fix this, you need to change the level key for the State level to something like this:

Then the level keys for the four State members (one regular member plus three dummy members) would be:

Since these level keys are unique, the cube will start properly.

The important point is that unique keys are required for level expression values that evaluate to <null>, even though the <null> members are not visible when you query a cube at runtime.

Note: Level expressions (member expressions, attribute expressions, and ordering expressions) resulting in a string data type with maximum length greater than 255 bytes are not supported by the Alphablox Cube Server.

To create or edit levels:

  1. To create a new level, click the Levels node under that dimension and click the Create New Level button. To edit an existing level, open the Levels folder and select the level you want to edit.
  2. For a new level, type a name in the Name field. The default name that appears is "New Level." Allowable characters for IBM Alphablox cube names are A-Z, a-z, 0-9, underscore (_), and space.
  3. In the Type menu, select the level type. By default, the type is REGULAR. The optional types include: REGULAR, TIME, UNKNOWN, TIME_YEARS, TIME_HALF_YEARS, TIME_QUARTERS, TIME_MONTHS, TIME_WEEKS, TIME_DAYS, TIME_HOURS, TIME_MINUTES, TIME_SECONDS, or TIME_UNDEFINED. If you are using a Time dimension, choose the appropriate time-related level type (for example, TIME_QUARTERS). If an appropriate time-related level type is not available, then use the TIME_UNDEFINED level type. The TIME level type should only be used with levels in a regular dimension. Time-related MDX functions depend on the correct use of the time-related level types to work properly. If you are not using the TIME_* levels within a dimension, then you can use a mix of REGULAR, TIME, and UNKNOWN level types.
  4. In the Expression field, enter an expression that specifies the level. You can also use the Expression Builder to assist you in entering an expression defining the level. An expression may have an associated performance penalty that you need to consider. In the following example, a SQL expression using a date function is used to create three new levels called Year, Month, and Week Ending:
    YEAR(week_ending_date)
    MONTHNAME(week_ending_date)
    week_ending_date
  5. Before applying or saving these changes, you must create at least one or more level keys that uniquely identify the level (see description above).
    1. Under the new level (displayed as "New Level" before changes are applied or saved) in the navigation tree, click on the Level Keys folder and then click the Create New Level Key button.
    2. Manually type in a level key expression or create one using the Expression builder.
    3. Click the Apply button to save your new level key. Your new level key will appear in the navigation tree under the Level Keys folder. Continue creating additional level keys, if needed.
  6. Click the Apply button to apply and save these settings without closing the Cube Administration dialog or click the OK button to save your changes and close the Cube Administration window.

To change the ordering of a level member:

  1. Click on the Levels label in the dimension. The Set Level Order option appears.
  2. Select members that you want to move and then use the Move Up or Move Down buttons to adjust them to the order that you want.
  3. Click the Apply button to apply and save these changes without closing the dialog. Click the OK button to save your changes and close the dialog.

Creating and editing attributes

Attributes are properties that can be defined for a level and can be used to provide additional information about the members of that level. For example, a member named Product might have attributes for size, color, cost, or other related product information. The MDX Properties function, supported by the IBM Alphablox Cube Server, can be used to display attributes in IBM Alphablox applications.

To create or edit an attribute:

  1. To create a new attribute, click the Attributes node that appears under your dimension, then click the Create New Attribute button. An attribute definition dialog appears. To edit an existing attribute, click on the attribute node that you want to edit.
  2. In the Expression field, type an expression that specifies your attribute. You can also use the Expression Builder to assist you in entering an expression defining the attribute. Example:
    @col(FAMILY.FAMILYID)
  3. Click the Apply button to apply and save these settings without closing the Cube Administration window. Click the OK button to save your changes and close the Cube Administration window.

Creating and modifying member ordering within a level

The default ordering of members is by member names. If the default ordering is not what you want, you can use Member Ordering to modify the ordering of members displayed in a level. For example, months returned are ordered alphabetically by default and you will want to change the member ordering so that the months appear in their chronological order.

For example, if months that were defined in a time dimension like this:

MONTHNAME(week_ending_date)

and displayed alphabetically, you can create a member ordering expression like this:

MONTH(@COL(week_ending_date)

which results in the months being ordered chronologically, as expected.

To modify the member ordering in a level:

  1. Click on the Member Orderings label under the level that you want to modify.
  2. Click the Create New Member Ordering button.
  3. Use the Expression builder to create an expression, or manually enter an expression, for the member ordering that you want to use.
  4. Click the Apply button to apply and save these settings without closing the Cube Administration window. Click the OK button to save your changes and close the Cube Administration window.

Creating persistent calculated members

Persistent calculated members are defined as part of the cube, during cube definition. The advantage of persistent calculated members is that they are available for any query. In IBM Alphablox Cube Server, the persistent calculated members belong to the specified dimension and act similar to regular members, having a parent and fitting into the dimension hierarchy. To create a persistent calculated member, you must specify the full name of the calculated member, including the dimension and the place in the hierarchy where the calculated member fits, and you must specify the expression that respresents the calculated member. Optionally, you can also specify the solve order.

  1. Log into the IBM Alphablox home page as a user of the administrators group.
  2. Click the Administration tab and click the Cubes link.
  3. Select a IBM Alphablox cube from the list of cubes and click the Edit button. The IBM Alphablox Cube Administration window displays.
  4. In the navigation tree for the selected cube, locate and click on the Calculated Members label.
  5. Click the Create New Calculated Member button.
  6. In the Member Name field, enter a valid member name, which includes the dimension name and member name ([DimensionName].[MemberName]. For example, to add a new calculated measure to represent an inventory backlog, the calculated member name might be [Measures].[CalculatedCost].
  7. In the MDX Expression field, type a valid MDX expression that specifies the new member value. For example, for a new [Measures].[CalculatedCost] calculated member, a valid MDX expression might look like this:
    [Measures].[Sales Amount]-[Measures].[Profit Amount]
  8. Select an integer for the Solve Order value. The solve order value must be an integer value (positive, zero, or negative) for the order in which the specified calculated member should be evaluated. The solve order values are evaluated relative to each other, with negative values solved before zero or positive values.

Related topics: