Edit a Level

You can always edit an existing level in a dimension to change its name or other properties. Double-click the level name to open the edit dialog.

Procedure

1. Open the dimension editor canvas.
2. Double-click the level name in the Hierarchies panel.
3. Edit the level properties as needed; for details, see the sections below.
4. Save your changes.

General

Name

The user-friendly caption name that users see in BI reporting and visualization tools. Consider the following specifics of the name:

• Should be unique among other levels in the same hierarchy.
• Can be the same as the name of a child secondary attribute.
• Can be the same as the name of another level in a different dimension.

Query Name

The SQL or MDX column name for this dimension attribute in the published AtScale cube. The query name is how the AtScale engine refers to this attribute internally. This should not be changed after the cube is published, as changing the query name may break existing data sources and reports that rely on this cube.

Time Unit

Available only for Time Dimensions. Choose the value you need, for example Years or Days.

Sources

Dataset

The source dataset that contains the columns that this level is based on.

Keys

The dataset column or columns that the level is based on. If the level has a compound key (a key of more than one column), click the + symbol to specify the other dataset columns that comprise the key.

• If the key consists of one column, the values in that column must be unique.
• If the key is a compound key, the columns together must provide unique values.

Note that when updating an existing level with relationships, you cannot add or remove key columns. You can still change the selected key columns of the level, but you cannot change the number of key columns. If a snowflake relationships is needed between two levels, one is automatically created and, consequently, neither level will allow adding or removing key columns. To change the number of key columns of an existing level with relationships, you have two options:

• Delete all of the relationships, update the key column definitions, and define new relationships that use the new key definitions.
• Delete the level. AtScale automatically deletes the relationships. You can then recreate the level with the desired key column definitions.

For more about selecting key columns and modeling hierarchies, see Requirements for Modeling Dimensional Hierarchies.

Unique

Specifies whether each row in the key columns contains a value that is unique within the dataset. Selecting this check box is equivalent to declaring the key to be a primary key. The AtScale engine uses this selection as input when joining rows from this dimension level to other datasets in the cube model.

• If the Key Column values are unique for each row, select this option. The join behavior would then consider the first matching row at query runtime.
• If the Key Column values are multi-valued, then leave unselected. The join behavior would then consider all matching rows at query runtime.

For more information about this check box and requirements for modeling hierarchies, see Requirements for Modeling Dimensional Hierarchies.

Value Column

The column whose values are used when a user selects this level in a visualization or report. For example, an ID number may be the key, but you want users to see a product name instead. For more information about selecting name columns and modeling hierarchies, see Requirements for Modeling Dimensional Hierarchies.

Sort Column

By default, sorts are on the column that you specify in the Value field. If you want to sort on a different column, select that column here. This applies to MDX queries only (i.e. queries received through the 'XMLA interface').

Data Handling

Exclude from System-Generated Dimension-Only Aggregates

Excludes this attribute from system generated dimension-only aggregates. Useful if the attribute contains a large number (e.g. millions) of distinct values that you don't want to aggregate.

Exclude from System-Generated Fact-Based Aggregates

Excludes this attribute from system generated fact-based aggregates. Useful if the attribute contains a large number (e.g. millions) of distinct values that you don't want to aggregate.

Custom Empty Member

Enable this property to ensure un-joinable values such as NULL that may reside in the Foreign Key columns of the fact dataset are included in your query results. The Custom Empty Member feature provides the convenience of handling NULL values so Data Architects are not forced to fix their fact tables. For maximum compatibility with BI Tools, you will have to add an empty member record to the dimension dataset. To learn more about Custom Empty Members, see Using Custom Empty Members for Levels and Attributes.

Formatting

Choose the format for the values. See Formats for Data Values for a description of the available formats

Use level as partition key

Specifies whether to partition system-defined aggregate tables by using this level as a partition key. Only system-defined aggregate tables that include the level will be partitioned.

You can specify only one partition key per cube for system-defined aggregate tables. If you select one level to use as a partition key and then select a different level to use as a partition key, the second selection replaces the first selection. The Design Center does not tell you when a second selection will replace the first selection. When a system-defined aggregate table uses a partition key, the key is listed in the entry for the table on the Aggregates page.

For more information about partitioning system-defined aggregate tables, see Partitioned System-Defined Aggregate Tables and Settings that Control Partitioning of System-Defined Aggregate Tables.

Dimensionally Modified Aggregates

Here you can enable or disable the creation of dimensionally modified aggregates for the current level. The following calculation types are available:

• Prev, Next, Lead, Lag
• Parallel Period
• Ancestor, Parent
• Descendants, Children
• Siblings
• Periods To Date
• Moving Window (aka Range Operator + Lag)

Consider the following when working with a Time Dimension:

• Periods To Date can be set only for this type of dimension.
• You can select calculation types only when the time unit (see above) is set to Days or longer.

For more information, see Dimensionally Modified Aggregates.

Visualization

Visible?

Specifies whether to make the level visible to BI client software. You might want to make a level invisible if you need it for join relationships, but don't want users to be able to select it in their queries.