About Queries on Dimensions that are Unrelated to One or More Queried Metrics
You can use the Unrelated Dimensions Handling functionality to specify the behavior of the AtScale engine when all of the following conditions apply:
- A client queries a model 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 metrics 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 metric 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 model contains two fact tables: Claims and Membership.
Table 1. Structure of the Claims and Membership tables
| Table | Columns |
|---|---|
| Claims | Month |
| Claims | Procedure Code |
| Claims | Claim Amount |
| Membership | Month |
| Membership | Member 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
| Month | Procedure Code | Claim Amount |
|---|---|---|
| January | 0310 | 5000 |
| January | 0210 | 2000 |
| January | 0110 | 3000 |
| January | 0210 | 1500 |
| February | 0110 | 2500 |
| February | 0110 | 2700 |
| February | 0210 | 1600 |
| March | 0110 | 2600 |
| March | 0310 | 4300 |
And the Membership table contained this data:
Table 3. The Membership table
| Month | Member Months |
|---|---|
| January | 100 |
| February | 80 |
| March | 90 |
You know that one of the uses that will be made of your model 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 deploy the model, you edit the metric Member Months to make these two changes:
-
Enable the Override Default Handling option. The default behavior is for the AtScale engine to reject the query and issue an error message.
-
In the dropdown list, select one of the following options:
- Display an error message and return nothing
- Include empty cells in query results
- Include repeated values in query results.
After saving the edits, you deploy the model. When the query is issued to find the PMPM for the procedure codes, the results look like this, even though the metric Member Months is unrelated to the dimensional attribute Procedure Code:
Table 4. Query results if you selected Include empty cells in query results
| Procedure Code | Claim Amount | Member Months | PMPM |
|---|---|---|---|
| 0110 | 10800 | 40 | |
| 0210 | 3300 | 12.22 | |
| 0310 | 9300 | 34.44 |
Table 5. Query results if you selected Include repeated values in query results
| Procedure Code | Claim Amount | Member Months | PMPM |
|---|---|---|---|
| 0110 | 10800 | 270 | 40 |
| 0210 | 3300 | 270 | 12.22 |
| 0310 | 9300 | 270 | 34.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