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.

About reusing datasets

Consider that reusing Datasets between the Dimension canvas and the Cube 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 (QDS) 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 the 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 drag the dataset for the dimension on to the Cube Designer canvas and then create a measure on that fact dataset. You then can use the measure instead of a secondary metrical attribute.

Procedure

In the Cube Designer canvas, locate the toolbar on the right, and choose the (book-like) Library icon.
Scroll to find the dataset that contains the data for the dimension.
Drag the dataset on to the canvas.
In the Measures pane on the right, click the plus sign. The Create a Measure dialog opens.
Specify the properties of the measure, selecting the dimension's dataset in the Dataset field.
Click Save.

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 measures on the fact dataset, which lists sales transactions. An example of a measure 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 measure 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 measure to get the distinct count of customer IDs in this additional fact dataset.