Creating Essbase Dimensions

Using the Blank Member Option

PeopleSoft Cube Builder uses queries and trees to define the structure of a dimension. You can define a dimension query that returns data rows used to generate the members of a dimension as well as its hierarchical structure, or insert a tree structure into a dimension.

To populate the data in a cube, you need to define a data source query that must return one column for each dimension and one column for the measures (facts). If you are building a data source query for a cube that contains the amounts dimensioned by account, region, product, and date, then the output of the query should have five columns.

Image: Data source query of a cube

This example shows the data source query of a cube that contains the amounts dimensioned by account, region, product, and date.

The dimension columns (Account, Region, Product, and Date) contain the dimension member names, and the measure column (Amount) contains the value of the measure. Thus, each result data row defines the data cell to be populated and the value of the measure column. For example, the amount (measure) of 100 is the results of the data cell where account equals E102, region equals 401, product equals EP603, and date equals 2009-01-01.

If the data is not defined for every dimension in the result data cells, then some data queries return rows with blank or empty dimension members.

Image: Data source query for a cube with blank members

This example shows the data queries that return rows with blank or empty dimension members.

If blank members are returned and the blank member name is not specified, then the data load processing of Cube Builder ignores the rows that contain blank members. Thus, some measures are not added to the rollup total, and the rollup total is not accurate

To accurately add all measures to the rollup total when a member has no name, you use the Blank Member link in the Dimension page to access the Dimension Options page, where you can specify a unique name for the blank member in the dimension. The name that you specify for a dimension member is used as a placeholder for any blank member that Cube Builder finds in the resulting query data rows.

Using the Dimension Options Page

Use the Dimension Options page (CUB_DIM_OPTIONS) to specify a name for the blank member.

• If the Blank Member field is specified, when you create dimension or load data, Cube Builder creates a dimension member for the blank member using the value in the Blank Member field to hold the blank member.

• If the Blank Member field is blank, Cube Builder ignores the blank member when you create dimension or load data.

Image: Dimension Options page

This example illustrates the fields and controls on the Dimension Options page.

Image: Dimension for the blank member from a static tree

This example shows the dimension for the blank member (No Product) from a static tree when the Blank Member field is specified as No Product.

If the dimension is not populated from a static or dynamic tree structure, you can manually add the blank member name to the dimension and then specify the Blank Member field value using the same name to indicate that the dimension member using the specified name is the blank member.

To manually add a blank member name to a dimension:

1. In the Dimension Structure section of the Dimension page, add a dimension member.

   Image: Dimension member (NO_ACCT) in the Dimension Structure section

   This example shows the newly added dimension member (NO_ACCT) in the Dimension Structure section.

   

2. Select the newly added dimension member (NO_ACCT) and click the Blank Member link.

   The Dimension Options page appears.

3. Enter a name (NO_ACCT) in the Blank Member field.

4. When Cube Builder creates the dimension structure:

   • If the dimension member (NO_ACCT) has the same name that is specified in the Blank Member field on the dimension, then the dimension member is used as a place holder for the blank member.

     Image: Outline displays the blank member

     This example shows the outline that displays the blank member (NO_ACCT) under the CUBT_OUTLINE dimension.

     

   • If blank member is not defined for the dimension, then when you insert a tree structure with a blank member (leaf) into the dimension, the blank member is ignored and no blank dimension is created.

     Image: Outline displays the dimension structure without the blank dimension created

     This example shows the dimension structure without the blank dimension created.

     

5. When you run the data to create or populate the cube, Cube Builder checks whether a blank member is defined for the dimension.

   • If the dimension has blank members and the blank member name is defined, then the measure value is populated to the cell for the blank member of the dimension.

   • If no blank member is found in the dimension or no blank member name is defined, then the measure value is ignored.

   Image: Results from the data source query with a blank member

   This example shows the results from the data source query with a blank member in the Account dimension.

   

   In the previous example, when member name NO_ACCT is selected as the blank member, the data row that shows Account equals <blank> and Product equals EP603 will be populated to a cell with Account equals NO_ACCT and Product equals EP603.

   Image: Sample cube

   This example shows the sample cube.

   

Note that:

• If a dimension is already created from a static tree or query, entering a name for the blank member after inserting a tree or query to the dimension will not automatically add the blank member. In this case, you must manually add a member with the same name to indicate the member is a placeholder for blank member.

• Using the blank member in dimension is optional. If the blank member name is not defined and if data rows contain blank members in the dimension during data load, then those blank members are excluded from the dimension on the cube.

• Each dimension can have only one blank member name, so only one member is attached to the blank member name.

• Essbase Cube Builder applies the duplicate member rules when it uses the blank member name.

• The blank member name must be unique to the dimension.

Creating a Dimension from a Query Without the Blank Member Name Defined

To create a dimension from a query without the blank member name defined:

1. Select PeopleTools > Cube Builder > Dimensions to add a new dimension.

2. In the Dimension page, select the root node in the Dimension Structure section and click the Add Member Child icon.

3. Enter the name for the child member.

4. Select the member child and click the Assign From Query icon to populate the dimension from a dimension query.

5. In the Find Query page, select the Dynamic option to create a dimension from a dynamic query.

   Alternatively, clear the Dynamic option to create a dimension from a static query.

6. Search for and select a query, and click the OK button.

7. In the Select Column page, select the column that contains the member name and its alias.

8. Click the OK button to populate the dimension with members from the query.

   Note: The blank members from the dimension query are not listed in the dimension structure because the blank member name is not specified in the dimension.

Creating a Dimension from a Query With the Blank Member Name Defined

To create a dimension from a query with the blank member name defined:

1. Select PeopleTools > Cube Builder > Dimensions to add a new dimension.

2. In the Dimension page, select the root node in the Dimension Structure section, and click the Add Member Child icon.

3. Click the Blank Member link to access the Dimension Options page.

4. Enter a name for the blank member and click the OK button.

5. In the Dimension page, select the member child and click the Assign From Query icon.

6. In the Find Query page, select the Dynamic option to create a dimension from a dynamic query.

   Alternatively, clear the Dynamic option to create a dimension from a static query.

7. Search for and select the dimension query.

8. In the Select Column page, select the column that contains the member name and its alias.

9. Click the OK button to populate the dimension with members from the query.

   Note: The blank member from the dimension query is added to the dimension structure using the blank member name that you defined in the dimension.

Modifying the Blank Member Usage in an Existing Dimension with the Members Populated from a Static Query

If you add a blank member name to an existing dimension or remove a blank member name from an existing dimension that was created from a static query, the dimension query structure is not changed. To modify the blank member usage in a dimension that was created from a static query, you can use one of the following methods:

• Reload the dimension members from dimension query by deleting the member structure and then adding it again.

• Manually add the blank member to the dimension structure.

  The name should be specified in the Blank Member option of the dimension.

• Manually remove the blank member from the dimension.

Removing a Blank Member from an Existing Dimension with the Members Populated from a Dynamic Query

To remove a blank member from an existing dimension with the members populated from a dynamic query:

1. Select PeopleTools > Cube Builder > Dimensions.

2. Open an existing dimension with the members from a dynamic query.

3. Click the Blank Member link in the Dimension page.

4. Clear the Blank Member field in the Dimension Options page, click the OK button, and save the dimension.

5. Reopen the dimension to see the changes.

   The blank member is removed from the dimension structure.

Adding a Blank Member to an Existing Dimension with the Members Populated from a Dynamic Query

To add a blank member to an existing dimension with the members populated from a dynamic query:

1. Select PeopleTools > Cube Builder > Dimensions.

2. Open an existing dimension with the members from a dynamic query.

3. Click the Blank Member link in the Dimension page.

4. Enter a blank member name in the Blank Member field.

5. Click the OK button and save the dimension.

6. Reopen the dimension to see the changes.

   The blank member is listed in the dimension structure.

Creating a Dimension Using a Tree and Without the Blank Member Name Defined

To create a dimension using a tree and without the blank member name defined:

1. Select PeopleTools > Cube Builder > Dimensions to add a new dimension.

2. In the Dimension page, select the root node in the Dimension Structure section and click the Add Member Child icon.

3. Enter a name for the member child and click the OK button.

   The Dimension page reappears.

4. Click the Assign From Tree icon to populate the dimension from a tree.

5. In the Find Tree page, select the Dynamic option to create a dimension from a dynamic tree.

   Alternatively, clear the Dynamic option to create a dimension from a static tree.

6. Select a tree and the top nodes that will be used to populate the dimension members.

7. Click the OK button and save the dimension.

   Note: The Dimension Structure section in the Dimension page does not list the blank members or leaves from the tree because the blank member name is not defined (or no blank member name is specified) in the dimension.

Creating a Dimension Using a Tree With the Blank Member Name Defined

To create a dimension using a tree with the blank member name defined:

1. Select PeopleTools > Cube Builder > Dimensions to add a new dimension.

2. In the Dimension page, click the Blank Member link.

3. Enter a blank member name in the Dimension Options page and click the OK button.

4. In the Dimension page, select the root node and click the Add Member Child icon.

5. Enter a name for the member child and click the OK button.

6. In the Dimension page, click the Assign From Tree icon to populate the dimension from a tree.

7. In the Find Tree page, select the Dynamic option to create a dimension from a dynamic tree.

   Alternatively, clear the Dynamic option to create a dimension from a static tree.

8. Select a tree and top nodes that are used to populate the dimension members.

9. Click the OK button to populate the dimension from the tree structure.

   Note: The blank member from the tree is added to the dimension using the blank member name that you entered in the Dimension Options page.

Adding a Child or a Peer Member

Use the Child Member Add page (CUB_DIMENSION_SEC) on initial member creation to enter the initial dimension name and consolidation operator, and to add a new member that is a child of the currently highlighted tree member.

Image: Child Member Add page

This example illustrates the field and controls on the Child Member Add page.

Use the Peer Member Add page to add a new member that is a peer of the currently highlighted tree member.

Image: Peer Member Add page

This example illustrates the field and controls on the Peer Member Add page. Definitions for the field and controls appear following the example.

Inserting a Member Formula

Use the Member Formula page (CUB_FORMULA_SEC) to enter formula information for a dimension member.

Image: Member Formula page

This example illustrates the field and controls on the Member Formula page. Definition for the field appears following the example. Use the Formula text box to enter a formula that will be used to calculate aggregates for the specific member.

Example: Defining Essbase Dimensions

Suppose that you need to create a product dimension based on a table that contains setID-controlled products.

To create a product dimension, access the Dimensions page:

1. Select PeopleTools > Cube Builder > Dimensions.

2. Add a dimension with a name of PRODUCT.

Image: Example of the Dimension page when you first create the PRODUCT dimension

This is an example of the Dimension page when you first create the PRODUCT dimension.

At this point, the Dimension page:

• Displays dimensional properties such as dimension type, storage option, and so on.

  Later on, depending on the dimension type, some of the tree object members may or may not have all of the available properties discussed in this book.

• Has an empty Dimension Structure section with a root node that has the same name as the dimension, PRODUCT.

• Has all icons at the bottom of the page appearing not active.

  Icons become active as they become relevant when you build the dimensional structure. For example, when you highlight a tree member, the relevant action icons become available.

  All actions in the icon area are applicable to whatever tree member is highlighted. For example, for dynamic trees and queries, you cannot delete any children.

To add a child member, highlight the PRODUCT node and click the Add Member Child icon. The Child Member Add page appears.

Enter the name for the child member and click the OK button. The Dimension page reappears.

Image: Example of the Dimension page with M1 member and its properties

This example shows a manually entered child member called M1 and the properties that are available to be set for that particular M1 member.

Image: Example of the attributes of the member VAT_FREE

This example illustrates the attributes of the member VAT_FREE, which shows that VAT_FREE member was built using a dynamic tree called QE_CUBT_PRODUCTS. All of the tree key field values appear in the Dynamic Source section of the attributes. The top portion shows the general relevant attributes for the current member node.

Selecting a Tree

Use the Find Tree page (CUB_TREESEARCH_SEC) to search for and select a tree that is defined in the system.

Image: Find Tree page

This example illustrates the fields and controls on the Find Tree page. Definitions for the fields and controls appear following the example.

Selecting the Top Node

Use the Select Top Node page (CUB_TREENODE_SEC) to select an existing node of a highlighted tree.

Image: Select Top Node page

This example illustrates the fields and controls on the Select Top Node page. Definitions for the fields and controls appear following the example.

Example: Inserting a Tree Structure into the Dimension

The Dimension page has two nodes, VAT Ready Products and US Tax Rate Products, that were added manually under the PRODUCT node.

Image: Example of the Dimension page with two nodes added under the PRODUCT root node

This example illustrates the Dimension page that has two nodes, VAT Ready Products and US Tax Rate Products.

When you highlight the PRODUCT root node, the Add Member Child button below the tree becomes active. Subsequently, below the VAT Ready Products member, a child member was added with the name T1. When the T1 child member is highlighted, the Assign From Tree icon became active; click it to connect with a tree structure at that point in the dimension tree.

Click the Assign From Tree icon to access the Find Tree page, where you can select a tree.

Image: Example of the Find Tree page

This example illustrates the fields and controls on the Find Tree page.

You need to define whether this tree reference is dynamic or static, select a tree name, and click the OK button. The Select Top Node page appears with the Available Tree Nodes section listing all the nodes from the selected tree.

Image: Example of the Select Top Node page

This example illustrates the fields and controls on the Select Top Node page.

You need to select a starting point in the tree definition and click the OK button to return to the Dimension page. Note that the tree structure is read and the contents of the tree are placed in the current node of the dimension.

Because VAT_FREE was selected as the starting node, that member is placed at the starting point and all of the children of that node are read, after which all of them become members of the dimension.

Image: Example of the Dimension page with contents of the tree added in the VAT_FREE node

This example shows the PRODUCT dimension after the contents of the tree definition are copied.

In this example, because you selected a dynamic tree, a tree icon is present in the dimension member that references the tree definition. The three items that were read are not actually stored as part of the dimension, but they are read every time you view the dimension. That is, after you save this dimension to the database, if the tree is modified to have more nodes or leaves, then the next time you view the dimension it will display the contents of the tree with the modifications. Note that this process does not happen with static tree members.

For a static tree member, no reference is made to the tree that you used during the initial creation of the members and no tree icon appears to indicate that those members were originally created from a tree definition.

Selecting a Query

Use the Find Query page (CUB_QRYSEARCH_SEC) to select a query that is defined in the system.

Image: Find Query page

This example illustrates the fields and controls on the Find Query page.

Selecting a Column

Use the Select column page (CUB_QRYCOLUMN_SEC) to select an existing column of a highlighted query. The columns displayed are from the select statement of the query.

Image: Select column page

This example illustrates the fields and controls on the Select column page.

The following examples demonstrate the use of a wide query and rollup levels that define the hierarchical structure of a dimension:

• Use the Select Column page to set the rollup levels:

  Image: Setting the rollup levels

  This example illustrates the setting of the rollup levels.

  

• After you select columns and click the OK button in the Select Column page, the first level under the selected member is populated using the column that was designated as Rollup Level 1.

  Image: Example of rollup level 1

  This is an example of rollup level 1.

  

• The second level is populated using the column designated as Rollup Level 2.

  Image: Example of rollup level 2

  This is an example of rollup level 2.

  

Example: Inserting a Query into the Dimension

Image: Example of the Dimension page with Account Query member

This example illustrates a child member of the root node called Account Query that was added manually.

Highlight the Account Query member and click the Assign From Query icon, the Find Query page appears.

Image: Find Query page

This example illustrates the fields and controls on the Find Query page.

You can search a partial name query. Note that the system displays only public queries to which the current user has authorization. In this example, you select the QE_CUBT_ACCOUNTS query and click the OK button. You also can assign a defined template. In this example, the results of the query yield leaf members, so use the query dynamically.

If the selected query has a prompt criteria defined, then a prompt page appears enabling you to enter values to satisfy the prompt requirement for the query.

The last step is to select a query column to fill the dimension structure using the Select column page. In this scenario, you select the A.SETID and A.QE_CUBT_ACCOUNT columns as the desired fields. You also select the A.DESCR option as alias value for the A.QE_CUBT_ACCOUNT column

Image: Example of the Select column page

Example of the Select column page, A.SETID and A.QE_CUBT_ACCOUNT as desired fields, DESCR column as aliases.

After selecting the desired values, click the OK button to run the query. The results appear as part of the dimensional structure.

Image: Example of the Dimension page with results displayed as part of the dimensional structure

This example illustrates the Dimension page with results displayed as part of the dimensional structure.

Time Dimension Members

Time Dimension members are created as manual members (not dynamic) and they are in PeopleSoft Date-Time format, which is compatible with Essbase format. However, you should create the data query sources in a standard PeopleSoft Date-Time format as well; otherwise, the data-load process will fail because it will not recognize matching members.

During the data-load process, Essbase verifies that the passed cells match the members that are already in the dimension. For example, if the time members for the dates of a year were created in the dimension with format YYYY-MM-DD and the quarters in the data source query are set as mm/dd/yy, then Essbase returns an error message to inform you that the members were not encountered in the time dimension.

On the Time Dimension Wizard page, after you select the time dimension and click the Create Time Dimension button, the Time Dimension members appear on the Dimension page.

Image: Dimension page, Dimension Structure section

This example illustrates the fields and controls on the Dimension page, Dimension Structure section.