Skip to main content

Adding a Secondary Metrical Attribute as a Metric in a Fact Dataset

Queries that use secondary metrical attributes together with metrics on fact datasets will return unexpected results. However, you can work around this restriction by creating metrics that are identical to the secondary metrical attributes that you would like to use.

About reusing datasets​

Consider that reusing datasets between the dimension Canvas and the model Canvas - or between multiple dimensions - can cause the engine to take unexpected join-paths at deployment time.

If a data architect wishes to reference the same table from multiple points in the same model, it is recommended to define separate query datasets (QDSs) that select from the desired table. The separate datasets should be used to back each dimension or fact model component. Doing so ensures the generation of run-time join-paths that are readily identified in Design Center.

Note that each QDS must be created from scratch; converting an existing dataset to a QDS does not satisfy the uniqueness requirement.

About this task​

You can create a metric on the fact dataset for the dimension, and then use the metric instead of a secondary metrical attribute.

Procedure​

  1. In the Repo Browser, locate the dataset that contains the data for the dimension.
  2. Click the datasets context menu and select Create metric. The Edit Metric panel opens.
  3. Specify the properties of the metric. Be sure to leave the dimension's dataset selected in the Dataset field.
  4. Click Apply.

Example​

Suppose that your Customer dimension contains the hierarchy Customer Statistics, and within that is the level Customer ID. You want a distinct count of the values of this level, which are the IDs for the full set of customers. You also want to allow analysts to show this distinct count along with certain metrics on the fact dataset, which lists sales transactions. An example of a metric on the fact dataset that an analyst could use this way is a distinct count of customer IDs in the sales transactions, giving an analyst the number of customers who have made purchases. By displaying this metric along side the total distinct count of customer IDs, the analyst could compare the number of customers who have made purchases recently or within various countries with your total number of customers.

Rather than create a secondary metrical attribute on the level Customer ID in the Customer Statistics hierarchy, you must follow the procedure above, dragging the dataset for the Customer dimension on to the canvas to create a fact dataset and then creating a metric to get the distinct count of customer IDs in this additional fact dataset.