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:
- Log into the IBM Alphablox home page as a user who is a member of the administrators group.
- Click the Administration tab and then click the Cubes link.
- 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.
- 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.
- Click the Create New Dimension button to create a new dimension
or select a dimension from the Dimensions list to edit an existing
dimension.
- 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.
- (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.
- 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.
- (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.
- 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.
- 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.
- 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:
- Click
on the Fact Table Join node under the new dimension.
- 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.
- 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)
- 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:
- After creating a new dimension using the Cube Administration dialog, click
on the Dimension Joins node under Joins folder of the
new dimension.
- 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.
- 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)
- 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:
- Country: level key =
Country
; level expression
= Country
- Region: level key =
Country, Region
; level expression
= Region
- State: level key =
State
; level expression = State
- City: level key =
City_ID
; level expression = City
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:
- State: level key =
Country, Region, State
; level
expression = State
Then the level keys for the four State members (one regular member
plus three dummy members) would be:
<USA, West, California>
<USA, East, null>
<Canada, Quebec, null>
<Canada, British Columbia, null>
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:
- 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.
- 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.
- 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.
- 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
- Before applying or saving these changes, you must create at least one
or more level keys that uniquely identify the level (see description above).
- 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.
- Manually type in a level key expression or create one using the Expression
builder.
- 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.
- 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:
- Click on the Levels label in the dimension.
The Set Level Order option appears.
- 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.
- 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:
- 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.
- 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)
- 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:
- Click on the Member Orderings label under the level that you want to modify.
- Click the Create New Member Ordering button.
- Use the Expression builder to create an expression, or
manually enter an expression, for the member ordering that you want to use.
- 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.
- Log into the IBM Alphablox home page as a user of the administrators group.
- Click the Administration tab and click the Cubes link.
- Select a IBM Alphablox cube from the list of cubes and click the Edit button.
The IBM Alphablox Cube Administration window displays.
- In the navigation tree for the selected cube, locate and click
on the Calculated Members label.
- Click the Create New Calculated Member button.
- 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].
- 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]
- 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: