About Queries on Dimensions that are Unrelated to One or More Queried Measures

You can use the Unrelated Dimensions Handling feature (which is enabled by default) to specify the behavior of the AtScale engine when all of the following conditions apply:

  • A client queries a cube that has more than one fact dataset.
  • The data in each fact dataset are at a different level of granularity than the data in the other fact datasets.
  • The query references dimensions that are not related to the measures that it queries.

The three possible behaviors are:

  • Reject the query and return an error message
  • In the query results, display empty cells
  • In the query results, repeat the values for the measure at a level of aggregation that is determined from the shared dimensions in the query

A common use for this feature is querying to perform Per Member Per Month (PMPM) calculations. To illustrate with a simple example, suppose that a cube contains two fact tables: Claims and Membership.

Table 1. Structure of the Claims and Membership tables

ClaimsProcedure Code
ClaimsClaim Amount
MembershipMember Months

To find the cost of each procedure averaged per member per month, you want the total count of members to be the same for each procedure over the queried period of time.

Suppose the Claims table contained this data:

Table 2. The Claims table

MonthProcedure CodeClaim Amount

And the Membership table contained this data:

Table 3. The Membership table

MonthMember Months

You know that one of the uses that will be made of your cube is to query the tables to get the PMPM for each procedure code during these months,calculating the PMPM as (Claim Amount)/(Member Months). So, before you publish the cube, you edit the measure Member Months to make these two changes under the heading Unrelated Dimensions Handling in the Edit a Measure dialog:

  1. Select the check box Override Default Handling. The default is for the AtScale engine to reject the query and issue an error message.
  2. In the list box, select either the option Include empty cells in query results or the option Include repeated values in query results.

After saving the edits, you publish the cube. When the query is issued to find the PMPM for the procedure codes, the results look like this, even though the measure Member Months is unrelated to the dimensional attribute Procedure Code:

Table 4. Query results if you selected Include empty cells in query results

Procedure CodeClaim AmountMember MonthsPMPM
011010800 40
02103300 12.22
03109300 34.44

Table 5. Query results if you selected Include repeated values in query results

Procedure CodeClaim AmountMember MonthsPMPM

Known issues

When using a Scatter Chart in Power BI with filters applied, the results differ from the data available in Atscale.