Create a cube in a semantic model

Create a cube with a fact table, dimensions, and measures to contain aggregated data from a semantic model’s physical connection. The fact table contains the data you want to aggregate in the cube. Dimensions describe the aggregated data so that it can be grouped for analysis. Measures quantify the data in the cube to facilitate operations for analyzing the data.

You can create a new cube by dragging a view or table onto a blank area of the canvas and then selecting to use the view or table as a fact table or dimension to create the new cube.

Note: When you drag a view or table onto the canvas, you also have the option to create a shared dimension that you can add to an existing cube. For details, see Create a shared dimension.

In this topic

• Add a fact table to a cube

• Add a dimension to a cube

• Add a measure to a cube

  • Add a simple measure to a cube

  • Add simple measures in bulk to a cube

  • Add a calculated measure to a cube

  • Add a measure created with SQL to a cube

• Add a degenerate dimension to a cube

• Use a shared dimension in a cube

Add a fact table to a cube

Add a fact table that contains the data from a semantic model’s physical connection that you want aggregated in a cube. You can either create a new cube while adding the fact table or add the fact table to an existing cube.

Notes:

• A cube can have only one fact table.

• Using an inline table as a fact table is supported only in Advanced mode. For details, see Advanced mode.

Complete the following steps to add a table or view as a fact table for a cube:

1. Log into the Pentaho User Console (PUC).

2. Open the Semantic Model Editor by taking one of the following actions:

   • If you are using the Modern Design of PUC, in the menu on the left side of the page, click Semantic Model Editor.

   • If you are using the Classic Design of PUC, click File > Semantic Model Editor.

   The Semantic Model Editor opens.

3. Open the semantic model to which you are adding a cube or editing a cube by completing the following sub steps:

   1. In the Semantic Models list, navigate to the model you want to open by searching or scrolling through the list.

   2. Click Open. The model opens in the canvas.

4. In the Data Source tab, navigate to the table or view that you want to use as a fact table.

5. (Optional) To preview data in the table or view, click the Preview icon next to the table or view. The Preview Data panel opens. You can take one or more of the following additional actions while previewing the data:

   • Hover over a column header to see metadata information for that column.

   • Click a column header to sort the table by the data in that column.

   • Click the Preview icon again to close the Preview Data panel.

     Note: You can preview one table or view at a time. By default, the maximum number of rows shown is 100. The administrator can change the maximum value by editing the row-limit property in the application.properties file, located in: \Pentaho\server\pentaho-server\pentaho-solutions\system\semantic-model-editor. The administrator must restart the Server for the new row value maximum to take effect.

6. Use one of the following options to add the table or view as a fact table:

   1. For an existing cube that does not already have a fact table, drag and hold the table or view over the cube until the Do you want to: dialog opens, and then drop the table or view onto the Use as Fact Table option.

   2. To create a new cube, drag and hold the table or view over a blank area of the canvas until the Do you want to: dialog opens, and then drop the table or view onto the Use as Fact Table option.

   The table or view is added to the cube as a fact table.

7. (Optional) If you created a new cube, change the name of the cube by clicking the name to make it editable and then enter a new, unique name for the cube.

   Note: The default name of a cube is “Cube” plus a number that represents the order in which the cube was created (example: Cube 3).

8. Click Save to save changes to the model.

Add a dimension to a cube

Add a dimension that describes aggregated data in a cube so that the data can be grouped for analysis. You can either create a new cube while adding the dimension or add the dimension to an existing cube.

Complete the following steps to add a table or view as a dimension of a cube:

1. Log into the Pentaho User Console (PUC).

2. Open the Semantic Model Editor by taking one of the following actions:

   • If you are using the Modern Design of PUC, in the menu on the left side of the page, click Semantic Model Editor.

   • If you are using the Classic Design of PUC, click File > Semantic Model Editor.

   The Semantic Model Editor opens.

3. Open the semantic model to which you are adding a cube or editing a cube by completing the following sub steps:

   1. In the Semantic Models list, navigate to the model you want to open by searching or scrolling through the list.

   2. Click Open. The model opens in the canvas.

4. In the Data Source tab, navigate to the table or view that you want to use as a dimension.

5. (Optional) To preview data in the table or view, click the Preview icon next to the table or view. The Preview Data panel opens. You can take one or more of the following additional actions while previewing the data:

   • Hover over a column header to see metadata information for that column.

   • Click a column header to sort the table by the data in that column.

   • Click the Preview icon again to close the Preview Data panel.

   Note: You can preview one table or view at a time. By default, the maximum number of rows shown is 100. The administrator can change the maximum value by editing the row-limit property in the application.properties file, located in: \Pentaho\server\pentaho-server\pentaho-solutions\system\semantic-model-editor. The administrator must restart the Server for the new row value maximum to take effect.

6. To add a table or view as a dimension, take one of the following actions:

   • For an existing cube, drag and hold the table or view over the cube until the Do you want to: dialog opens, and then drop the table or view onto the Use as Dimension option.

   • To create a new cube, drag and hold the table or view over a blank area of the canvas until the Do you want to: dialog opens, and then drop the table or view onto the Use as Dimension option.

   A dimension is created with one hierarchy that has one level, and the Dimension Editor window opens with the top position of the Dimension tree selected.

7. Edit options for the dimension by completing the following sub steps:

   1. In the Dimension Name section, enter a unique Name within the cube.

   2. (Optional) If the dimension is not linked to a facts table in the cube, in the Connect to the Facts Table section, select the Fact Table Column that you want to link to the dimension.

      Note: If the cube does not have a facts table defined, you must first add a fact table to cube. For details, see Add a fact table to a cube.

   3. In the Dimension Type section, for the Type of dimension you want to use, select one of the following options:

      Option	Description
      StandardDimension	A dimension used for basic analysis. StandardDimension is the default value.
      TimeDimension	A dimension used for time-based analysis. A time dimension might have annotations like Year, Month, and Week.

8. (Optional) Expand the Optional Information section and edit one or more of the following options:

   Option	Description
   Visible	Value indicating whether the dimension element is visible in the Pentaho Analyzer design environment. Elements that are not visible cannot be accessed directly in Pentaho Analyzer to use in reports. However, hidden elements can still be used to build expressions and conditions that are internally evaluated by the Mondrian engine for reporting. The default value is true.
   Usage Prefix	String of text added to the beginning of the column name when building collapsed dimension aggregates to disambiguate the dimension usage. Usage prefixes enable the system to accurately recognize and associate columns during aggregate table matching.

9. (Optional) Expand the Describe Dimension section and edit one or more of the following options:

   Option	Description
   Caption	String of text that is displayed instead of the dimension's name. You can use captions to provide a user-friendly label for reports or for localization so that the dimension's name appears in the local language.
   Description	Description of the dimension.

10. In the Dimension tree, select the hierarchy that was created when you added the dimension.

    Note: You can add more than one hierarchy to a dimension.

11. Edit the options for the hierarchy by completing the following sub steps:

    1. In the Hierarchy Name section, enter a unique Name.

       Note: All hierarchies in a dimension must have a unique name. An empty name is considered unique and can be used only once for a hierarchy in the dimension.

    2. In the Mandatory Data section, enter information for the following options:

       Option	Description
       Source Table*	Table used as the source of data for the hierarchy.
       Alias	Unique text string used as an alias for the table in queries. You can assign an alias to a table used in multiple hierarchies to ensure that SQL queries work correctly. By default, the table name is used in queries.
       Hierarchy Key	Column from the hierarchy source table used to connect the hierarchy to the cube’s fact table, enabling accurate joins between dimension data and fact data during query execution.
       Has All*	Value indicating whether the hierarchy has an “all” member. The "all" member is the parent of all other hierarchy members, representing the total. The default value is true.

       *Required

    3. (Optional) Expand the Optional Information section and edit one or more of the following options:

       Option	Description
       Visible	Value indicating whether the hierarchy element is visible in the Pentaho Analyzer design environment. Elements that are not visible cannot be accessed directly in Pentaho Analyzer to use in reports. However, hidden elements can still be used to build expressions and conditions that are internally evaluated by the Mondrian engine for reporting. The default value is true.
       All Member Name	Name of the "all" member. The default value is "All Hierarchy Name" , for example: '"All Store" in case the hierarchy name was Store.
       All Member Caption	String of text that is displayed instead of the All Member's name. You can use captions to provide a user-friendly label for reports or for localization so that the name appears in the local language.
       All Level Name	Name of the "all" level member. The default value is "(All)".
       Default Member	String value representing the default member of the hierarchy. A valid multidimensional (MDX) identifier is expected, for example: [Time].[1997].[Q1].[1]
       Member Reader Class	String value of the member reader class, in case you want to apply some customized transformation to the original data. The class provided needs to implement the following interface: mondrian.rolap.MemberReader
       Origin	Unsigned Short value that determines the source of the hierarchy according to the following values: 1-identifies levels in a user defined hierarchy; 2-identifies levels in an attribute hierarchy; 4-identifies levels in attribute internal hierarchies, that are not enabled; 8-identifies levels in a key attribute hierarchy. The default value is 1 (user defined), except for Measures that it is 6 (attribute + attribute internal).
       Display Folder	String value to specify the folder in which to list the hierarchy for users in Pentaho Analyzer.
       Unique Key Level Name	Select one of the existent hierarchy levels. Used to indicate that the given level taken together with all higher levels in the hierarchy acts as a unique alternate key, ensuring that for any unique combination of those level values, there is exactly one combination of values for all levels below it.

    4. (Optional) Expand the Describe Hierarchy section and edit one or more of the following options:

       Option	Description
       Caption	String of text that is displayed instead of the hierarchy’s name. You can use captions to provide a user-friendly label for hierarchies or for localization so that the hierarchy’s name appears in the local language.
       Description	Description of the hierarchy.

12. In the Dimension tree, select the level of the hierarchy that was created when you added the dimension. For example, the default level is named “Level1”.

    Note: You can add more than one level to a hierarchy.

13. Edit the options for the level by completing the following sub steps:

    1. In the Level Name section, enter a unique Name.

       Note: All levels in a hierarchy must have a unique name. An empty name is considered unique and can be used only once for a level in the hierarchy.

    2. In the Level Source section, which presents the Source Table previously chosen for the hierarchy, select a value for the following option:

    Option	Description
    Column	Column of the source table that you want to use for the level. If a column is not selected, a KeyExpression must be defined in the Advanced mode instead or an error occurs when you try to save the model. For details, see Advanced mode.

14. In the Relevant Information section, edit one or more of the following options:

    Option	Description
    Name Column	Column of the source table that contains the user identifier of the level., i.e., the value the user sees in each row of the reports representing each member of the level. Note: Instead of selecting a specific column, you can define a NameExpression in the Level element of a SQL expression to use as the user identifier. For details, see Advanced mode.
    Ordinal Column	Column of the source table that contains the member ordinals for the level. Note: If the Ordinal Column is not specified, the key column is used for ordering. Instead of selecting a specific column, you can define an OrdinalExpression in the Level element of a SQL expression to use for ordering. For details, see Advanced mode.

15. (Optional) Expand the Optional Information section and edit one or more of the following options:

    Option	Description
    Visible	Value indicating whether the level element is visible in the Pentaho Analyzer design environment. Elements that are not visible cannot be accessed directly in Pentaho Analyzer to use in reports. However, hidden elements can still be used to build expressions and conditions that are internally evaluated by the Mondrian engine for reporting. The default value is true.
    Approximate Row Count	Estimated number of members in the level. Setting an approximate row count can improve performance of running reports that use the cube.
    Null Parent Value	Value that identifies null parents in a parent-child hierarchy. Typical values are NULL and 0.
    Key Column Type	The type of data in the key column for the level. Types of data include STRING, NUMERIC, INTEGER, BOOLEAN, DATE, TIME, and TIMESTAMP. When generating SQL statements, Mondrian encloses values for String columns in quotation marks but leaves values for Integer and Numeric columns un-quoted. Date, Time, and Timestamp values are quoted according to the SQL dialect. For an SQL-compliant dialect, the values appear prefixed by their type name, for example, DATE '2006-06-01'.
    Internal Type	The Java type that Mondrian uses to store the level's key column. Types include INT, LONG, OBJECT, and STRING. The Internal Type value also determines the JDBC method that Mondrian calls to retrieve the column. For example, if the Java type is INT, Mondrian calls ResultSet.getInt(int). Usually, the Internal Type attribute is not needed, because Mondrian chooses a type based on the type of database column.
    Unique Members	Value that indicates whether members are unique across all parents in the level. For example, zip codes are unique across all states. Members of the first level are always unique. The default value is false.
    Level Type	Value that indicates whether the level is a regular or a time-related level. The level type is important for time-related functions such as YTD (year-to-date). The available values are conditioned by the Dimension Type. When the Dimension Type is StandardDimension, then the only possible value for the Level Type is Regular. When the Dimension Type is TimeDimension , then you can choose as Level Type one of: TimeUndefined, TimeYears, TimeHalfYears, TimeQuarters, TimeMonths, TimeWeeks, TimeDays, TimeHours, TimeMinutes, TimeSeconds.
    Hide Member If	Value that indicates when a member of the level is hidden. Never: Member is never hidden. IfBlankName: Member is hidden if its name is null or empty. IfParentsName: Member is hidden if its name matches the parent’s name. The default value is Never.
    Formatter Class	Important: This attribute is deprecated and might be removed in a future release. To ensure compatibility, instead of using this attribute, use a nested MemberFormatter inside the Level element in Advanced mode. For details, see Advanced mode. Formatter class name for the member labels displayed. The class must implement the mondrian.olap.MemberFormatter interface. A formatter class is a user-defined Java class for customizing how values are displayed so that you can format data beyond default settings, such as applying custom date formats, currency symbols, or localized labels.
    Caption Column	The name of the column in the source table that holds the caption for members. Captions are a string of text that is displayed instead of the member's name. Note: Instead of selecting a specific column, you can define a CaptionExpression in the Level element of a SQL expression to use as the caption. For details, see Advanced mode.
    Parent Column	The name of the column in the source table that references the parent member in a parent-child hierarchy. Note: Instead of selecting a specific column, you can define a ParentExpression in the Level element of a SQL expression to use as the parent. For details, see Advanced mode.

16. (Optional) Expand the Describe Level section and edit one or more of the following options:

17. Option	Description
    Caption	String of text that is displayed instead of the level's name. You can use captions to provide a user-friendly label for reports or for localization so that the level's name appears in the local language.
    Description	Description of the level.

18. Click Apply. The dimension is created and added as a new node of the cube in the semantic model.

19. (Optional) If you created a new cube, change the name of the cube by clicking the name to make it editable and then enter a new, unique name for the cube.

    Note: The default name of a cube is “Cube” plus a number that represents the order in which the cube was created (example: Cube 3).

20. Click Save to save changes to the model.

Add a measure to a cube

Add a measure to a cube so that you can quantify data in the cube and facilitate operations for analyzing the data, such as slicing, dicing, and drilling down.

Note: The cube must have a fact table before you can add a measure to it.

Complete one of the following procedures for the type of measure you want to add:

• Add a simple measure to a cube

  Add a simple measure to use values pulled directly from a column in the cube’s fact table as a measure.

• Add simple measures in bulk

  When a cube’s fact table has many fact columns, and you need to create one or more simple measures for each column, you can create simple measures in bulk by defining criteria to select columns and apply an aggregation function to those columns.

• Add a calculated measure to a cube

  Add a calculated measure when you want to calculate values for the data you are measuring in a cube by using a multidimensional (MDX) expression.

• Add a measure created with SQL to a cube

  Add a measure created with SQL to a cube when you want to calculate the values for the data you are measuring in a cube by using an SQL expression that references data from multiple tables or views in the cube.

Add a simple measure to a cube

Add a simple measure to use values pulled directly from a column in the cube’s fact table as a measure.

Complete the following steps to add a simple measure to a cube:

1. Log into the Pentaho User Console (PUC).

2. Open the Semantic Model Editor by taking one of the following actions:

   • If you are using the Modern Design of PUC, in the menu on the left side of the page, click Semantic Model Editor.

   • If you are using the Classic Design of PUC, click File > Semantic Model Editor.

   The Semantic Model Editor opens.

3. Open the semantic model that contains the cube to which you want to add a measure by completing the following sub steps:

   1. In the Semantic Models list, navigate to the model you want to open by searching or scrolling through the list.

   2. Click Open. The model opens in the canvas.

4. Select the cube you want to edit, and then click the Add a Measure icon. The Add a Measure dialog opens.

   Note: If the Add a Measure icon is not visible, click the More Actions icon, and then select Add a Measure.

5. Select Add Simple Measure, and then click Confirm. The Add a Simple Measure dialog opens.

6. In the Mandatory Information section, enter the required information for the following options:

   Option	Description
   Measure Name	A unique name for the simple measure.
   Source Column	Column that is the source of the measure's values. If not specified, you must enter a measure expression for the name of an existent column in the Facts table of the cube.
   Default Aggregation	Default method used in reports for grouping and calculating data for the measure.

7. (Optional) Expand the Optional Information section and edit one or more of the following options:

   Option	Description
   Format	Option that determines how the value for the measure is displayed (example: 0.00 or #, ###).
   Visible	Value indicating whether the measure element is visible in the Pentaho Analyzer design environment. Elements that are not visible cannot be accessed directly in Pentaho Analyzer to use in reports. However, hidden elements can still be used to build expressions and conditions that are internally evaluated by the Mondrian engine for reporting. The default value is true.
   Data Type	The type of data used for the measure. Types include String, Numeric, and Integer.

8. (Optional) Expand the Describe Measure section and edit one or more of the following options:

   Option	Description
   Caption	String of text that is displayed instead of the measure's name. You can use captions to provide a user-friendly label for reports or for localization so that the measure's name appears in the local language.
   Description	A description of the measure.

9. Click Create. The measure is created and added to the Measures node inside the cube.

10. Click Save to save changes to the model.

Add simple measures in bulk to a cube

When a cube’s fact table has many fact columns, and you need to create one or more simple measures for each column, you can create simple measures in bulk by defining criteria to select columns and apply an aggregation function to those columns.

Complete the following steps to add simple measures to a cube, in bulk:

1. Log into the Pentaho User Console (PUC).

2. Open the Semantic Model Editor by taking one of the following actions:

   • If you are using the Modern Design of PUC, in the menu on the left side of the page, click Semantic Model Editor.

   • If you are using the Classic Design of PUC, click File > Semantic Model Editor.

   The Semantic Model Editor opens.

3. Open the semantic model that contains the cube to which you want to add measures to in bulk by completing the following sub steps:

   1. In the Semantic Models list, navigate to the model you want to open by searching or scrolling through the list.

   2. Click Open. The model opens in the canvas.

4. Select the cube you want to edit, and then click the Add a Measure icon. The Add a Measure dialog opens.

   Note: If the Add a Measure icon is not visible, click the More Actions icon, and then select Add a Measure.

5. Select Add Simple Measure, and then click Confirm. The Add Measure dialog opens.

6. Select Bulk Measures. The Add Measure window opens with the Condition section displayed.

7. (Optional) To ensure that measure names are unique inside each cube, in the Name the Measures subsection, turn on the Use Custom Name toggle to display the Measure Name fields and then enter a prefix or suffix or both.

   Notes:

   • By default, measure names are assigned the same name as the related column name.

   • If measure names are repeated during bulk creation, the system displays a warning and appends a number to each measure name so that it is unique. The appended number represents the order in which the measure was created (example: Measure (1)).

   • When using physical column names to name measures, underscores are replaced with spaces and each word in the column name is capitalized (example: birth_date is changed to Birth Date).

8. In the Select the Criteria subsection, select the Subject that the system should use as criteria when selecting columns during bulk measure creation. Subjects include:

   • All Columns: Considers all columns of the table.

   • Column Key: Selects among the table’s key columns (defined physically as Primary or Foreign Keys in the database) .

   • Column Type: Matches columns by the column type.

   • Column Name: Matches columns by using physical names (contains, starts with, ends with).

9. (Optional) If you selected a Subject that displays an additional option to configure in the Select the Criteria subsection, enter the information for that option:

   Subject	Option	Description
   Column Key	Key Type*	The type of key that the system looks for when selecting columns during bulk measure creation. Key types include the following options: All Primary Key Foreign Key
   Column Type	Column Type*	The type of column that the system looks for when selecting columns during bulk measure creation. Types available to choose from are determined by the information in the cube’s fact table.
   Column Name	Filter*	Filter option and text that the system uses to filter the columns by their name during bulk measure creation. Filter includes the following options: Contains Ends With Starts With

   * Required field

10. In the Define Aggregation subsection, edit or change the following options:

    Option	Description
    Aggregation	Method used in reports for grouping and calculating data for each one of the new measures that will get created. Aggregation options include: Average Count Distinct Count Maximum Minimum Sum (default value)
    Excluding Columns	List with the columns that you want the system to exclude from the bulk measure creation process. This list is populated with the fact table columns that matched the criteria previously defined for the bulk measure creation. Leave empty in case you don't want the system to exclude any.

11. Click Create. The measures are created and added to the Measures node inside the cube.

12. Click Save to save changes to the model.

Add a calculated measure to a cube

Add a calculated view when you want to calculate values for the data you are measuring in a cube by using a multidimensional (MDX) expression.

Complete the following steps to add a calculated measure:

1. Log into the Pentaho User Console (PUC).

2. Open the Semantic Model Editor by taking one of the following actions:

   • If you are using the Modern Design of PUC, in the menu on the left side of the page, click Semantic Model Editor.

   • If you are using the Classic Design of PUC, click File > Semantic Model Editor.

   The Semantic Model Editor opens.

3. In the Semantic Models list, navigate to the model that contains the cube to which you want to add a measure. You can find the model by searching or scrolling through the list.

4. Select the model and click Open. The model opens in the canvas.

5. Locate the cube you want to edit, and then click the Add a Measure icon. The Add a Measure dialog opens.

   Notes:

   • If the Add a Measure icon is not visible, click the More Actions icon, and then select Add a Measure.

   • To locate an element in a large semantic model, you can click the Model Structure tab and then navigate to the element by searching or scrolling through the list.

6. Select Add Calculated Measure, and then click Confirm. The Add Calculated Measure dialog opens.

7. In the Mandatory Information section, enter the required information for the following options:

   Option	Description
   Measure Name	Name for the measure that is unique for all measures in the cube.
   Formula	MDX formula used to calculate the value for the measure.

8. (Optional) Expand the Optional Information section and edit one or more of the following options:

   Option	Description
   Format	Option that determines how the value for the measure is displayed (example: 0.00 or #, ###).
   Visible	Value indicating whether the measure element is visible in the Pentaho Analyzer design environment. Elements that are not visible cannot be accessed directly in Pentaho Analyzer to use in reports. However, hidden elements can still be used to build expressions and conditions that are internally evaluated by the Mondrian engine for reporting. The default value is true.
   Parent	Fully-qualified MDX identifier of the parent member. If not specified, the member will be at the lowest level in the hierarchy.

9. (Optional) Expand the Describe Measure section and edit one or more of the following options:

   Option	Description
   Caption	String of text that is displayed instead of the calculated measure's name. You can use captions to provide a user-friendly label for reports or for localization so that the measure's name appears in the local language.
   Description	A description of the measure.

10. Click Create. The calculated measure is created and added to the Measures node inside the cube.

11. Click Save to save changes to the model.

Add a measure created with SQL to a cube

Add a measure created with SQL to a cube when you want to calculate the values for the data you are measuring in a cube by using an SQL expression that references data from multiple tables or views in the cube.

Complete the following steps to add a measure created with SQL:

1. Log into the Pentaho User Console (PUC).

2. Open the Semantic Model Editor by taking one of the following actions:

   • If you are using the Modern Design of PUC, in the menu on the left side of the page, click Semantic Model Editor.

   • If you are using the Classic Design of PUC, click File > Semantic Model Editor.

   The Semantic Model Editor opens.

3. In the Semantic Models list, navigate to the model that has the cube to which you want to add a measure. You can find the model by searching or scrolling through the list.

4. Select the model and click Open. The model opens in the canvas.

5. Locate the cube you want to edit, and then click the Add a Measure icon. The Add a Measure dialog opens.

   Notes:

   • If the Add a Measure icon is not visible, click the More Actions icon, and then select Add a Measure.

   • To locate an element in a large semantic model, you can click the Model Structure tab and then navigate to the element by searching or scrolling through the list.

6. Select Add with SQL, and then click Confirm. The Add with SQL dialog opens.

7. In the Mandatory Information section, enter the following, required information:

   Option	Description
   Measure Name	Name for the measure that is unique in the cube for all measures.
   Default Aggregation	Default aggregation to use for the measure in reports.
   SQL Expression	SQL expression used to calculate the measure's value. Note: Autocomplete options are available for generic SQL functions, keywords, and table columns within the cube's scope.

8. (Optional) Expand the Optional Information section and edit one or more of the following options:

   Option	Description
   Format	Option that determines how the value for the measure is displayed (example: 0.00 or #, ###).
   Visible	Value indicating whether the measure element is visible in the Pentaho Analyzer design environment. Elements that are not visible cannot be accessed directly in Pentaho Analyzer to use in reports. However, hidden elements can still be used to build expressions and conditions that are internally evaluated by the Mondrian engine for reporting. The default value is true.
   Dialect	SQL dialect that corresponds to the SQL expression that you wrote for the measure. In Advanced mode, you can write multiple expressions for the same measure by using a different dialect for each expression. For details, see Advanced mode.
   Data Type	The type of data resulting from the SQL expression used for the measure.

9. (Optional) Expand the Describe Measure section and edit one or more of the following options:

   Option	Description
   Caption	String of text that is displayed instead of the measure's name. You can use captions to provide a user-friendly label for reports or for localization so that the measure's name appears in the local language.
   Description	A description of the measure.

10. Click Create. The measure is created with SQL and added to the Measures node inside the cube.

11. Click Save to save changes to the model.

Add a degenerate dimension to a cube

Add a degenerate dimension when you want to use only the information in a cube’s fact table to describe the aggregated data in the cube. The degenerate dimension describes aggregated data in the cube so that the data can be grouped together for analysis.

Note: Before you can add a degenerate dimension to a cube, the cube must have a fact table.

Complete the following steps to add a table or view as a degenerate dimension of a cube:

1. Log into the Pentaho User Console (PUC).

2. Open the Semantic Model Editor by taking one of the following actions:

   • If you are using the Modern Design of PUC, in the menu on the left side of the page, click Semantic Model Editor.

   • If you are using the Classic Design of PUC, click File > Semantic Model Editor.

   The Semantic Model Editor opens.

3. In the Semantic Models list, navigate to the model that contains the cube to which you want to add a degenerate dimension. You can find the model by searching or scrolling through the list.

4. Select the model and click Open. The model opens in the canvas.

5. Locate the cube you want to edit, and then click the Add a Degenerate Dimension icon. The Degenerate Dimension Editor window opens with the top position of the Dimension tree selected.

   Notes:

   • If the Add a Degenerate Dimension icon is not visible, click the More Actions icon and then select Add a Degenerate Dimension.

   • To locate an element in a large semantic model, you can click the Model Structure tab and then navigate to the element by searching or scrolling through the list.

6. Edit options for the dimension by completing the following sub steps:

   1. In the Dimension Name section, enter the Name of the degenerate dimension.

   2. In the Dimension Type section, select the type of dimension you want to use from the list.

      Option	Description
      StandardDimension	A dimension used for basic analysis. StandardDimension is the default value.
      TimeDimension	A dimension used for time-based analysis. A time dimension might have annotations like Year, Month, and Week.

   3. (Optional) Expand the Optional Information section and edit one or more of the following options:

      Option	Description
      Visible	Value indicating whether the degenerate dimension element is visible in the Pentaho Analyzer design environment. Elements that are not visible cannot be accessed directly in Pentaho Analyzer to use in reports. However, hidden elements can still be used to build expressions and conditions that are internally evaluated by the Mondrian engine for reporting. The default value is true.
      Usage Prefix	String of text added to the beginning of the column name when building collapsed dimension aggregates to indicate how the degenerate dimension is used. Usage prefixes enable the system to accurately recognize and associate columns during aggregate table matching.

   4. (Optional) Expand the Describe Dimension section and edit one or more of the following options:

      Option	Description
      Caption	String of text that is displayed instead of the cube's name. You can use captions to provide a user-friendly label for reports or for localization so that the cube's name appears in the local language.
      Description	Description of the cube.

   5. In the Dimension tree, select the hierarchy you want to use for the degenerate dimension.

      Note: A degenerate dimension can have one or more hierarchies.

7. Edit the options for the hierarchy by completing the following sub steps:

   1. In the Hierarchy Name section, enter a unique Name.

      Note: All hierarchies in a degenerate dimension must have a unique name. An empty name is considered unique and can be used only once for a hierarchy in the degenerate dimension.

   2. In the Mandatory Data section, enter information for the following options:

      Option	Description
      Source Table	Table used as the source of data for the hierarchy.
      Alias	Unique text string used as an alias for the table in queries. Consider assigning an alias to a table used in multiple hierarchies to ensure SQL queries for the table work correctly. By default, the table name is used in queries.
      Hierarchy Key	Unique text string used to connect the table or view produced by the hierarchy to the cube’s fact table.
      Has All*	Value indicating whether the hierarchy has an “all” member. The "all" member is the parent of all other hierarchy members, representing the total. It is the default member used for calculations when the hierarchy is not on an axis or slicer. The default value is true.

      * Required field

   3. (Optional) Expand the Optional Information section and edit one or more of the following options:

      Option	Description
      Visible	Value indicating whether the hierarchy element is visible in the Pentaho Analyzer design environment. Elements that are not visible cannot be accessed directly in Pentaho Analyzer to use in reports. However, hidden elements can still be used to build expressions and conditions that are internally evaluated by the Mondrian engine for reporting. The default value is true.
      All Member Name	Name of the “all” member if the hierarchy has an “all” member. Default value is 'All hierarchyName', for example, 'All Store'
      All Member Caption	A string of text that is displayed instead of the "all” member’s name. You can use captions to provide a user-friendly label for reports or for localization so that the "all” member’s name appears in the local language.
      All Level Name	Name of the “all” level. The “all” level is the topmost level in a hierarchy and contains only the “all” member, which represents the total aggregation of all members in the hierarchy below it.
      Default Member	Default member of the hierarchy.
      Member Reader Class	Name of the custom reader class. The custom reader class must implement the mondrian.rolap.MemberReader interface.  A custom reader class is a user-defined Java class that extends Mondrian’s default behavior for reading members of a dimension so that you can customize how dimension members are retrieved and displayed in OLAP queries.
      Origin	A bit mask that determines the source of the hierarchy: MD_ORIGIN_USER_DEFINED identifies levels in a user defined hierarchy (0x0000001). MD_ORIGIN_ATTRIBUTE identifies levels in an attribute hierarchy (0x0000002). MD_ORIGIN_INTERNAL identifies levels in attribute hierarchies that are not enabled (0x0000004). MD_ORIGIN_KEY_ATTRIBUTE identifies levels in a key attribute hierarchy (0x0000008). The default value is MD_ORIGIN_USER_DEFINED. For Measure the default value is MD_ORIGIN_ATTRIBUTE & MD_ORIGIN_INTERNAL (0x0000006).
      Display Folder	String value to specify the folder in which to list the hierarchy for users in Pentaho Analyzer.
      Unique Key Level Name	Name of the level within the hierarchy. The Unique Key Level Name is used to indicate that the given level taken together with all higher levels in the hierarchy acts as a unique alternate key, ensuring that for any unique combination of those level values there is exactly one combination of values for all levels below it.

   4. (Optional) Expand the Describe Hierarchy section and edit one or more of the following options:

      Option	Description
      Caption	String of text that is displayed instead of the hierarchy's name. You can use captions to provide a user-friendly label for reports or for localization so that the hierarchy's name appears in the local language.
      Description	Description of the hierarchy.

   5. In the Dimension tree, select the level of the hierarchy that you want to edit. For example, the default level is named “Level1”.

      Note: A degenerate dimension hierarchy can have one or more levels.

8. Edit the options for the level by completing the following sub steps:

   1. In the Level Name section, enter a unique Name.

      Note: All levels in a hierarchy must have a unique name. An empty name is considered unique and can be used only once for a level in the degenerate dimension.

   2. In the Level Source section, edit one or more of the following options:

      Option	Description
      Source Table	Table used as the source of data for the hierarchy.
      Column	Column of the table that contains the data used by the level. If a column is not selected, a KeyExpression must be defined in the Advanced mode instead or an error occurs when you try to save the model. For details, see Advanced mode.

   3. In the Relevant Information section, edit one or more of the following options:

      Option	Description
      Name Column	Column of the table that contains the user identifier for the level. Note: Instead of selecting a specific column, you can define a NameExpression in the Level element of a SQL expression to use as the user identifier. For details, see Advanced mode.
      Ordinal Column	Column of the table containing the member ordinals for the list level. Note: If the Ordinal Column is not specified, the key column is used for ordering. Instead of selecting a specific column, you can define an OrdinalExpression in the Level element of a SQL expression to use for ordering. For details, see Advanced mode

   4. (Optional) Expand the Optional Information section and edit one or more of the following options:

      Option	Description
      Visible	Value indicating whether the level element is visible in the Pentaho Analyzer design environment. Elements that are not visible cannot be accessed directly in Pentaho Analyzer to use in reports. However, hidden elements can still be used to build expressions and conditions that are internally evaluated by the Mondrian engine for reporting. The default value is true.
      Approximate Row Count	Estimated number of members in the level. Setting an approximate row count can improve performance.
      Null Parent Value	Value that identifies null parents in a parent-child hierarchy. Typical values are NULL and 0.
      Key Column Type	The type of data in the key column for the level. Types of data include STRING, NUMERIC, INTEGER, BOOLEAN, DATE, TIME, and TIMESTAMP. When generating SQL statements, Mondrian encloses values for String columns in quotation marks but leaves values for Integer and Numeric columns un-quoted. Date, Time, and Timestamp values are quoted according to the SQL dialect. For a SQL-compliant dialect, the values appear prefixed by their type name, for example, DATE '2006-06-01'.
      Internal Type	The Java type that Mondrian uses to store the level's key column. Types include INT, LONG, OBJECT, and STRING. The Internal Type value also determines the JDBC method that Mondrian calls to retrieve the column. For example, if the Java type is INT, Mondrian calls ResultSet.getInt(int). Usually, the Internal Type attribute is not needed, because Mondrian chooses a type based on the type of database column.
      Unique Members	Value that indicates whether members are unique across all parents in the level. For example, zip codes are unique across all states. Members of the first level are always unique. The default value is false.
      Level Type	Value that indicates whether the level is a regular or a time-related level. The level type is important for time-related functions such as YTD (year-to-date). The default value is Regular.
      Hide Member If	Value that indicates when a member of the level is hidden. Never: Member is never hidden. IfBlankName: Member is hidden if its name is null or empty. IfParentsName: Member is hidden if its name matches the parent’s name. The default value is Never.
      Formatter Class	Formatter class name for the member labels displayed. The class must implement the mondrian.olap.MemberFormatter interface. A formatter class is a user-defined Java class for customizing how values are displayed so that you can format data beyond default settings, such as applying custom date formats, currency symbols, or localized labels.
      Caption Column	The name of the column that holds the caption for members. Captions are a string of text that is displayed instead of the member's name. Note: Instead of selecting a specific column, you can define a CaptionExpression in the Level element of a SQL expression to use as the caption. For details, see Advanced mode.
      Parent Column	The name of the column in the source table that references the parent member in a parent-child hierarchy. Note: Instead of selecting a specific column, you can define a ParentExpression in the Level element of a SQL expression to use as the parent. For details, see Advanced mode.
      Describe Level	Description of the level.

   5. (Optional) Expand the Describe Level section and edit one or more of the following fields:

      Option	Description
      Caption	String of text that is displayed instead of the level's name. You can use captions to provide a user-friendly label for reports or for localization so that the level's name appears in the local language.
      Description	Description of the level.

9. Click Apply. The degenerate dimension is created and added the Degenerate Dimensions node in the cube.

10. Click Save to save changes to the model.

Use a shared dimension in a cube

Use a shared dimension in a cube when you want the aggregated data to be consistent with other cubes in the same semantic model. For example, you might use a shared time dimension for all cubes that are used in time-based analysis. The shared dimension must be created before it can be used in a cube. See Create a shared dimension.

Complete the following steps to use a shared dimension in a cube:

1. Log into the Pentaho User Console (PUC).

2. Open the Semantic Model Editor by taking one of the following actions:

   • If you are using the Modern Design of PUC, in the menu on the left side of the page, click Semantic Model Editor.

   • If you are using the Classic Design of PUC, click File > Semantic Model Editor.

   The Semantic Model Editor opens.

3. Open the semantic model to which you are using a shared dimension by completing the following sub steps:

   1. In the Semantic Models list, navigate to the model you want to open by searching or scrolling through the list.

   2. Click Open. The model opens in the canvas.

4. In the canvas, locate the shared dimension you want to use and the cube in which you want to use the shared dimension.

   Note: To locate an element in a large semantic model, you can click the Model Structure tab and then navigate to the element by searching or scrolling through the list.

5. Drag and hold the shared dimension over the cube until the Do you want to: dialog opens and then drop the shared dimension onto the Use as Dimension option. The Dimension Usage Editor opens.

6. In the Dimension Name section, enter a Name that is unique in the context of the cube. The name can be the same name used for the shared dimension if it is unique in the context of the cube.

7. In the Mandatory Data section, enter information for the following options:

   Option	Description
   Source*	Table used as the source of data for the dimension. This is not editable in the Dimension Usage as it is defined in the Shared Dimension.
   Fact Table Column*	Fact table column used to connect the cube's fact table to the usage of the shared dimension in that cube.

   *Required field

8. (Optional) Expand the Optional Information section and edit one or more of the following options:

   Option	Description
   Level to Join	Level of the hierarchy in the shared dimension to connect to the cube's fact table. When not specified, joins to the lowest level of the dimension.
   Visible	Value indicating whether the hierarchy element is visible in the Pentaho Analyzer design environment. Elements that are not visible cannot be accessed directly in Pentaho Analyzer to use in reports. However, hidden elements can still be used to build expressions and conditions that are internally evaluated by the Mondrian engine for reporting. The default value is true.
   Usage Prefix	String of text added to the beginning of the column name when building collapsed dimension aggregates to indicate how the dimension is used. Usage prefixes enable the system to accurately recognize and associate columns during aggregate table matching.

9. (Optional) Expand the Describe Hierarchy section and edit one or more of the following options:

   Option	Description
   Caption	String of text that is displayed instead of the hierarchy’s name. You can use captions to provide a user-friendly label for hierarchies or for localization so that the hierarchy’s name appears in the local language.
   Description	Description of the hierarchy.

10. Click Apply. The dimension is used as a Shared Dimension node in the cube.

11. Click Save to save changes to the model.