Skip to main content
Version: I2023.2.1

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

TableColumns
ClaimsMonth
ClaimsProcedure Code
ClaimsClaim Amount
MembershipMonth
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
January03105000
January02102000
January01103000
January02101500
February01102500
February01102700
February02101600
March01102600
March03104300

And the Membership table contained this data:

Table 3. The Membership table

MonthMember Months
January100
February80
March90

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
01101080040
0210330012.22
0310930034.44

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

Procedure CodeClaim AmountMember MonthsPMPM
01101080027040
0210330027012.22
0310930027034.44

Known issues

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

ATSCALE-8586