Add or Edit a Measure within a Dimension (Experimental)

Measures are only allowed on the fact datasets of a cube. However, you can add a secondary metrical attribute to a dimension, which behaves like a measure in a very limited context of the cube.

note

This feature is experimental. An AtScale Organization Administrator must enable the feature by going to Admin > Organization Settings. Then, click Features. Scroll down the list of features to Secondary Metrical Attributes and click Override & Enable.

About this task

In some cases, you may have dimension data that you want to aggregate. For example, a product dimension may have a list price column, that you want to aggregate to determine the average list price for all of the products you carry.

Note that this would not help you determine the average list price of all products sold. This is because a secondary metrical attribute can only be calculated for the attributes local to the dimension where it resides. Queries that reference an unrelated dimension generate errors like this:

Query is not possible: measures (xxx, yyy) are not defined over the product of these dimensions: (AAA, BBB, CCC)

Restrictions

• Queries that use secondary metrical attributes together with measures on fact datasets will return unexpected results.

  To work around this restriction, add dimension datasets in which secondary metrical attributes are defined to the canvas as fact datasets; then; model the secondary metrical attributes as measures in those fact datasets. For steps, see Adding a Secondary Metrical Attribute as a Measure in a Fact Table.

• If you want to write a query that uses a secondary metrical attribute and groups the results by a hierarchy level other than the level that the attribute is defined on, you must define the attribute on the lowest level of the hierarchy.

• If you define a secondary metrical attribute on a level higher than the lowest level, queries that use the attribute can reference only that hierarchy.

  However, you can overcome this restriction if each hierarchy that you want to reference in the same dimension has a level that uses the same key column. For example, suppose you have these hierarchies in a single dimension:

  Hierarchy A	Hierarchy B	Hierarchy C
  Level 1	Level 1	Level 1
  Level 2 (secondary metrical attribute)	Level 2	Level 2
  Level 3	Level 3	Level 3

  If Level 1 in each of these hierarchies uses the key column KeyColA, then queries that use the secondary metrical attribute can also reference levels in hierarchies B and C.

• If you use a secondary metrical attribute in a calculated measure formula, it can be used only with other secondary metrical attributes that come from the same dimension.

  For example, to create an average of product list price, first create two secondary metrical attributes: one for the sum and one for the non-distinct row count. Then, add a calculated measure on the main model canvas that refers to these measures by their query name, as in this example: ([Measures].[product_list_price] / [Measures].[product_nd_count])

Procedure

1. Open the dimension editor canvas.

2. Drag a dataset column directly on top of an existing level in the Hierarchies panel. The Create Secondary Attribute dialog appears. The following table describes the fields on this dialog.

   Field	Description
   Name	The user-friendly name that users will see when they connect to the cube from a client tool. Since this will show as a cube measure, the name should reflect the dimension it comes from, since it can only be used with attributes of that dimension.
   Query Name	The alias that the AtScale query engine will use for the target column. This can be the same as the Target Column name, or you can enter a more readable name if you want - just don't change the Query Name once the cube has been published.
   Visible?	If selected, the measure will be visible in the cube. You may want to make a measure not visible if you intend to use it in a calculation, but not as a measure on its own.
   Description	This is a description of the data that this measure contains. Some BI tools can show this description, some can't. It's a good practice to enter descriptions to help other people get familiar with the data in the cube.
   Attribute Type	Select Metrical. The option Dimensional defines a normal secondary attribute. The option Level Alias defines an attribute as an alias of a level. See Model Secondary Dimensional Attributes.
   Attribute Source	The source dataset that contains the column that the measure is based on. In the case of a metrical secondary attribute, this is the dimension dataset name.
   Aggregation Type	This is the aggregate calculation to apply to the data. With the exception of Distinct Count, Distinct Sum, and Percentile, all of these calculations produce additive measures, which can be managed by AtScale's aggregate management system.
   Value Column	The dataset column that the measure is based on.
   Formatting	The format of the data in the column that the measure is based on.
   Custom Value for Unmatched Fact Records	An optional string that you want to appear in place of NULL values whenever a fact record has no match for this attribute.
   Unrelated Dimensions Handling	For more information about these controls, see About Queries on Dimensions that are Unrelated to One or More Queried Measures. These controls do not appear unless the Ignore Unrelated Dimensions feature is enabled, which it is by default.
   Folder	(optional) The name of the folder in which to display this attribute in BI client software, such as Microsoft Excel and Tableau. If your cube has a lot of attributes, folders are a good way to organize them.

3. After specifying information about the new secondary metrical attribute, click Save.

Results

The new measure does not appear in the Measures panel or the Preview panel of the cube, but you will see it as a measure in your client applications.

• Adding a Secondary Metrical Attribute as a Measure in a Fact Dataset Queries that use secondary metrical attributes together with measures on fact datasets will return unexpected results. However, you can work around this restriction by creating measures that are identical to the secondary metrical attributes that you would like to use.