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.