Model Additional Levels in a Hierarchy
You can add a level to an existing hierarchy by dragging a dataset column to the desired position in the Hierarchies panel. A hierarchy should go from the most granular level on the bottom to the most general level on the top.
Procedure
-
Open the dimension editor canvas.
-
Drag a dataset column to above or below an existing level in the Hierarchies panel. Look for a thin green line, and drop in the correct position in the hierarchy.
-
Complete the requested information in the Create a Level dialog.
Field Description Name The user-friendly caption name that users see in BI reporting and visualization tools. 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. 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: In AtScale 7.0, 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 about this
check box and requirements for modeling hierarchies, see Requirements
for Modeling Dimensional
Hierarchies."
"Value Name", "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 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')." "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", "If a null value is found for the level, AtScale substitutes
this value, enabling joins on dimension rows where the level contains
nulls. This value must match the data type of the value column in the
dimension. For example, if the data type of the value column is INTEGER,
the value to substitute for nulls should be an integer, such as 123. To
include the column in factless queries, add a UNION to those queries.
For example, suppose you want to query the Sales Reason dimension
without using a measure. The value column is salesreasonkey, for which
the data type is STRING. Two other columns in the query -
salesreasonname and salesreasongroup - might have null values. The label
for null values in salesreasonkey is no reason, while the label for null
values in salesreasongroup is no group. The query would look like this:
select salesreasonkey, salesreasonname, salesreasongroup from schema.dimsalesreason union select null, 'no reason', 'no group'
"
"Formatting", "Choose the format for the values. See Formats for Data
Values
for a description of the available formats." "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."
- Click Save.