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
| 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 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:
- Select the check box Override Default Handling. The default is for the AtScale engine to reject the query and issue an error message.
- 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 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