Using Constraint Translation for Fact Table Query Acceleration

Note

Constraint translation is a Public Preview feature.

Constraint translation enables the AtScale engine to translate dimension filter constraints in queries to fact table partition column constraints. When the data warehouse processes the query, it can use the additional partition column constraints to accelerate it.

Overview

To enable constraint translation, you identify levels in your model as constraint translation levels, then define relationships between those levels and your fact table partition key columns. When the engine processes a query containing a filter on a hierarchy with a constraint translation level, the engine translates the filter constraints to additional fact table constraints and appends them to the end of the original query.

note

Constraint translation is only applied to subqueries that select from dataset tables. It is not applied if the query selects from an aggregate table.

The data warehouse can then use those additional constraints to leverage its partition/cluster block pruning capabilities, enabling it to run a faster query on the fact table.

Define a constraint translation

Note

Currently, you can only define constraint translations via SML.

To add a constraint translation to a model:

1. Specify which dimension levels in your model are constraint translation levels. In the dimension SML file, add the constraint_translation_rank property to the level attribute. For example:

   level_attributes:
       - unique_name: Quarter
           label: Quarter
           dataset: DateCustom
           key_columns:
               - quarter
           name_column: quarter_name
           sort_column: quarter
           time_unit: quarter
           constraint_translation_rank: 1

   The rank determines the order in which the fact table constraints are appended to the outbound query. This should match the order of the physical partition/clustering configuration of the fact table.

2. Define the relationship between the constraint translation level and the partition key column. In the model's SML file, add the constraint_translation property to the relationship connecting the dataset that contains the partition key column to the dimension that contains the constraint translation level. For example:

   relationships:
       - unique_name: Internet_Sales_Date_Custom
         from:
           dataset: Internet Sales
           join_columns:
               - orderdatekey
         to:
           dimension: Date Custom
           level: Date
         constraint_translation:
           level: Quarter
           from_columns: 
               - part_year_quarter

Additional information

For more information on the constraint_translation_rank and constraint_translation properties, see Dimension and Model in the AtScale SML Object Documentation on GitHub.