Creating Dimensions for Essbase Cubes

This chapter provides an overview of Essbase Cubes dimensions and discusses how to:

• Set up cube dimension builder.

• Create Essbase dimensions.

Understanding Dimensions of Essbase Cubes

After creating an Essbase cube connection, you can create Essbase dimensions. Dimensions represent different aspects that are related to a multidimensional database. Typical dimensions are:

• A time dimension, which is used to map transactional information over a time period.

• An account dimension, which is used to map various affected accounts.

• A country dimension, which is used to map geographic transactional areas.

Groupings of dimensions make up a cube outline.

Setting Up Cube Dimension Builder

Access the Cube Dimension Builder Setup page (CUB_GUISETUP) by selecting PeopleTools, Cube Builder, Essbase Cube Builder, Cube Dimension Builder Setup.

Use the Cube Dimension Builder Setup page to set values for the number of members to display at a time for dynamic queries, and to set the maximum number of rows to retrieve for static queries.

Creating Essbase Dimensions

This section discusses how to:

• Define an Essbase dimension.

• Insert a tree structure into the dimension.

• Insert a query into the dimension.

• Define tine dimensions.

• Add generation names.

Pages Used to Create Essbase Dimensions

Note. Only users with these permission lists are able to create Essbase dimensions: AEPNLS, ALLPAGES, CPPT1080, PTPT1200, and SMPPNLS.

Defining an Essbase Dimension

Access the Dimension page by selecting PeopleTools, Cube Builder, Essbase Cube Builder, Dimensions.

This is an example of the Dimension page when you first access it to create a dimension:

When you first access the Dimension page to create a dimension, this page displays dimensional properties and a blank tree-like structure with a root node that has the same name as the dimension; most of the action icons at the bottom are disabled. Later on, depending on the Dimension Type value, the tree object members may or may not have all of the available properties that are shown in the following example. Action icons appear available as they become relevant when you build the dimensional structure.

This is an example of the Dimension page with some properties and active icons:

Dimension Type	The system displays the type of the dimension. Available options are: Accounts Country Date-time None (or Standard) Time
Object Owner ID	Select the owner of the dimension. The owner ID helps to determine the product, feature, or application team that last made changes to this dimension.
Storage	Select from the following storage options: Default Dynamic Calc Dynamic Calc and Store Never Share Label Only
<Tree-like structure section>	Displays dimension data graphically to show a tree hierarchy.
	The Insert Member Peer icon is available when you highlight a tree member. Click this icon to add a new member that is a peer of the currently highlighted member.
	The Add Member Child icon is available when you highlight a tree member if the current member can have children. Click this icon to add a new member that is a child of the currently highlighted member.
	Click the Assign From Query icon to run a public query. The results of running the query will be placed directly under the currently highlighted member. You can select which query to run, and you need to supply any prompt values that the query requires. You need to supply the column of the query to use to populate the child members, which will be created when the query is run. You also can select the query to be run in dynamic mode. The query must return at least one row of data or it is considered invalid and is not used.
	Click the Assign From Tree icon to copy a tree structure into the dimension. Select the starting node of the tree; the system copies the selected node and all of its children. The currently highlighted node becomes the selected tree top node, and the member name of the current node is changed to reflect this selection. As with queries, you can select to have the tree copy be dynamic.
	Click the Delete Member icon to delete dimension (children) members.
	Click the Delete All Children icon to delete all query or tree children.
	Click the Reset Values icon to clear or refresh member override values, if any, that you have entered for dynamic trees and queries. Note. This icon is used for dynamic queries and trees when the concept of member overrides and templates is defined. This clearance is important if you have changed a template and want to reset all previously saved values.
	Click the Time Dimension Wizard icon to access the Time Dimension Wizard page, where you can automatically populate the members of a time dimension. Note. The Time Dimension Wizard icon is available only for the time dimensions and not for other dimension types.
Member Name	Enter the name or identifier of the member.
Label Only	This option is available only for child members, which are not at level 0. Select to enable the system to group members together (with a dummy parent member) and enable easier access to the report. Note. If you define a parent member as label only, the system does not calculate parent member from its child and reserves no storage space for that parent member. Use the default calculations for parent members to save time and storage space. When you view the report in Microsoft Excel, the label-only member shows its first child value and does not occupy any storage space.
Expense Report	Expense item applies to account dimensions only. Essbase has certain built-in formulas that can take advantage of the knowledge that an item is an expense. Select this check box to pass this knowledge on to Essbase. Valid values are Blank (set) and non-Blank (do not set).
Shared Member	Select to share values between members.
Never Share	Select to not allow members to be shared implicitly. Note. Members that are set as Never Share can only be explicitly shared. To explicitly share a member, create the shared member with the same name and set it as shared.
Consolidation	This property enables you to define the mathematical operator used for rolling up members. Most often, you expect that data is added (using the + operator) when rolled up. However, you might occasionally need to specify other operators. The available operators are: % (percent sign): Divide the total of previous member calculations by this member and multiply by 100. * (asterisk): Multiply. + (plus sign): Add. – (minus sign): Subtract. / (forward slash): Divide ~ (tilde): Do not consolidate. [Blank]: Do not consolidate.
Time Balance	Time balance affects how the parent time value is calculated. Select from these values: 0: none 1: first 2: last 3: average
Alias	Add a custom alias for the member.
Storage	Data storage enables Essbase to recognize what type of storage to allocate for the member. Select from these values: 0 (or blank): store data. 1: never share. 2: label only. 3: shared member. 4: dynamic calculation and store. 5: dynamic calculation, no store.
Formula	Click to access the Member Formula page, where you can enter formula information for a dimension member.
Dynamic Source	If you select a dynamic query or dynamic tree member of the dimensional structure, the attributes on the right also display the dynamic object key values that were used in creating the entries: For a query, this section displays the query name that was used and if a particular template was used. For a tree, this section displays the tree name and the key values of the tree.

Adding a Child or a Peer Member

Access the Child Member Add page (on the Dimension page, highlight a tree member and click the Add Member Child icon).

Access the Peer Member Add page (on the Dimension page, highlight a tree member and click the Insert Member Peer icon).

Member	Enter the name of the new member that will be a child or a peer of the currently highlighted member on the Dimension page.
OK	Click to add the child or a peer member and return to the Dimension page.

Inserting a Member Formula

Access the Member Formula page (on the Dimension page, click the Formula link).

Formula

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 (select PeopleTools, Cube Builder, Essbase Cube Builder, Dimensions) and add a dimension with a name of PRODUCT.

This is an example of the Dimension page when you first create a 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 tree-like 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.

Note. To create dimension, often you add member children and peers manually and then supply the various attributes for each member. Parent members (those with children) may have attributes that leaf members (those without children) do not have, or vice versa.

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. This example shows a manually entered child member called M1 and the properties that are available to be set for that particular M1 member:

Note. M1 is highlighted and its corresponding attributes appear to the right of the tree. You can change any of these attribute values. After adding more members, you can view attributes of each member by selecting each new node. Selecting a node highlights it and displays the attributes for that node to the right of the dimension tree.

This example illustrates the attributes of the member VAT_FREE, which shows that VAT_FREE member was built using a dynamic tree called 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:

Inserting a Tree Structure into the Dimension

When inserting a tree structure into a dimension using the Find Tree page, you need to define whether that tree reference is dynamic or static. Note the following differences between dynamic and static (nondynamic) trees:

• Dynamic trees do not store dynamic referenced members in the dimension. They store only a reference to the tree definition.

  When the dimension is sent to Essbase, the tree is reopened, read, and sent to Essbase when the cube needs to be populated.

  If you select the Dynamic option on the Find Tree page, the Template ID field appears, enabling you to select a template.

  You use templates to determine what each member of the dynamic tree should have as its defined properties. You create templates in their own component (PeopleTools, Cube Builder, Essbase Cube Builder, Dynamic Dimension Templates). You are not required to use templates because the system uses a default template automatically if you do not provide a value in this field. Only members that do not match template values are actually stored as part of the dimension.

  For example, if a tree has 50 members, the dimensional definition will not save 50 rows in the dimensional definition. Instead, it will store only the single row that references the tree name. From that point on, if you decide to add an alias for the 45^th member, then the system stores only the 45^th member and the tree reference member. Basically, the system stores only members for which you have changed the default values in the dimensional structure. For all other members, the assumption is that the member values match what is in the template for that dynamic reference or the default template that is used automatically. This technique saves storage space in the database.

• For static (nondynamic) trees, static references read the contents of the tree and store them within the dimension. All subsequent changes made to the tree are not reflected in the dimension. After the contents of the tree are read, the system does not save even the tree reference; it is as if you had manually entered all tree node and leaf values in the dimension.

Selecting a Tree

Access the Find Tree page (on the Dimension page, select a tree name and click the Assign From Tree icon).

Dynamic	Select to define this tree reference as dynamic. Deselect this option to define this tree reference as static.
Template ID	If the Dynamic option is selected (for dynamic trees), the Template ID field appears, enabling you to select a template. If the Dynamic option is deselected (for static queries), after the contents of the query are read not even the query reference is saved. See Creating Dynamic Dimension Templates.
Available Trees	Displays a list of trees that are defined in the system after you click the Search button.

Selecting the Top Node

Access the Select Top Node page (on the Dimension page, select a tree name and click the Assign From Tree icon; on the Find Tree page, select trees and click the OK button).

Available Tree Nodes	This section displays a list of all the nodes from the selected tree.
Select	Select one tree node as a starting point in the tree definition to use when copying the structure into the current dimension. Note. If the actual top node of the tree is selected, then the entire tree will be copied.
OK	After selecting the starting node, click the OK button to return to the Dimension page. The tree structure is read, and the contents of the tree are placed in the current node of the dimension.

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, as shown in this example:

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. This example shows 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.

This is an example of 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. 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.

Inserting a Query into the Dimension

As with trees, queries can also be used to populate a dimension. Queries also use templates. While assigning members from query definitions, you need to supply prompt values, if any, that are required by the query.

For dynamic queries, as with dynamic trees, you do not actually store query results in the dimension definition, except for those that do not match any assigned template for the query (or the default template if no actual template was assigned). At the time the dimension is sent to the Essbase database, the query will be rerun to make sure it reflects the current result rows from the query.

For static queries, take the query results and store all of those rows within the dimension.

Note. When you create dimensions, you may have issues with queries that return a large number of rows. When a query returns thousands of rows, it may cause PIA to crash or it may cause the dimension builder to have long waiting times to display the results in the dimensional tree.

If you have issues with large results query, use dynamic queries. By using dynamic queries, you ensure that only a certain number of rows appear at one time within the dimensional structure.

Selecting a Query

Access the Find Query page (on the Dimension page, highlight a tree member and click the Assign From Query icon).

Dynamic	Select to define this query reference as dynamic. Deselect this option to define this query reference as static.
Template ID	If the Dynamic option is selected (for dynamic queries), then the Template ID field appears, enabling you to select a template. Optionally, assign a defined template to the query. If the Dynamic option is deselected (for static queries) after the contents of the query are read, not even the query reference is saved. See Creating Dynamic Dimension Templates.
Available Queries	Displays a list of queries that are defined in the system after you click the Search button. Note. Only public queries to which the current user has authorization appear.

Selecting a Column

Access the Select column page (on the Dimension page, highlight a tree member and click the Assign From Query icon; on the Find Query page, select queries and click the OK button).

Available Query Columns	Select columns from a list of columns that are defined in the system.
OK	After selecting the columns, click the OK button to return to the Dimension page.

Example: Inserting a Query into the Dimension

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:

You can search a partial name query. Note that the system displays only public queries to which the current user has authorization. You need to select a 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. In this case, query CUBT_ACCOUNTS has a prompt on the SetID field, and you must supply this value to run the query:

After entering the required values and clicking the OK button, the last step is to select a query column to fill the dimension structure using the Select column page:

In this scenario, you need to select A.CUBT_ACCOUNT as the desired field. After selecting the desired field, click the OK button to run the query. The results appear as part of the dimensional structure, as shown by this example:

Note. The results of running this query should yield unique values for the leaf members. All leaf values under the same parent must always be unique for the dimension to be valid.

A query icon is in the member that references the dynamic query. If the query was not run as dynamic, then a normal parent icon exists and—from that point on—no indication exists that the members were added as part of running a query. As with static trees, it is as if you had manually typed in all the members.

Defining Time Dimensions

Access the Time Dimension Wizard page (on the Dimension page, click the Time Dimension Wizard icon).

Use the Time Dimension Wizard page to automatically populate the members of a time dimension.

Note. The Time Dimension Wizard icon is available only for time dimensions and not for other dimension types.

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, as shown in this example:

Note. To prevent date-time format conflicts, Essbase eliminates previously created members before it automatically displays new members.

You also can add new members to the time dimension posterior to the automatic population of members either by PeopleSoft trees, PeopleSoft queries, or manually. However, the members must be in the same PeopleSoft date-time format to be consistent with the automatically populated members.

Adding Generation Names

Access the Generation Names page (on the Dimension page, select the Generation Names tab).

Generation

Displays the generation of the dimension. The top parent of a dimension is called generation 1, each child of generation 1 is called generation 2, each child of generation 2 is called generation 3, and so on. For example, as shown on the Generation Names page, the PRODUCT dimension has two generation names: WORLD and CONTINENT. These names are not relevant to the graphical user interface (GUI), but they can be used on the Essbase side when Essbase references an entire generation in a member formula. Note. In Essbase terminology, a generation refers to a level within a dimension. A root branch of the tree is generation 1. Generation numbers increase as you count from the root toward the leaf member. Level also refers to a branch within a dimension; however, levels reserve the numerical ordering used for generations.

Generation Name

Enter the generation name for each generation.