About Factless Queries

A factless query is a query that references one or more dimensions and does not include any metrics. Factless queries are sometimes referred to as dimension-only queries.

An example of a single-dimension factless query is Customer Name, queried from a model called Internet Sales.

SELECT `Internet Sales`.`Customer Name` AS `customer_name`
FROM `sales insights`.`internet sales` `Internet Sales`

A multi-dimension factless query is a query that references more than one dimension, with a relationship amongst the dimensions to a common fact table. If the dimensions are not directly connected in the model, then the system may join them through a fact table to satisfy the query. For multi-fact models, multiple join queries may be unioned together.

The following is an example of a multi-dimension-only query where all dimensions are derived from the same fact table, in this case factinternetsales from a model named Internet Sales.

SELECT `Internet Sales`.`Style` AS `style`,
`Internet Sales`.`Product Line` AS `product_line`
FROM `sales insights`.`internet sales` `Internet Sales`

Incidental Filters: Engine Settings for Filtering on Factless Queries

The AtScale engine's default handling of factless queries is to not apply filters upon them. However, this behavior is configurable and controlled by the query.factless.ignoreIncidentalFilter option (default=TRUE). When query.factless.ignoreIncidentalFilter=TRUE and the AtScale engine is faced with an Incidental Filter query - a factless query that may filter dimensional values by other not strictly related dimensional values - the filter is ignored by the AtScale engine.

An example of an Incidental Filter query is as follows:

SELECT `Internet Sales`.`Style` AS `style`
FROM `sales insights`.`internet sales` `Internet Sales`
WHERE (`Internet Sales`.`Product Line` = 'T ')

If ignoreIncidentalFilter=TRUE, would have the same outbound query as:

SELECT `Internet Sales`.`Style` AS `style`
FROM `sales insights`.`internet sales` `Internet Sales`

Setting query.factless.ignoreIncidentalFilter to FALSE enables users to filter on factless queries. This scenario mirrors the common use case of enabling the "Show Relevant Values" option for a Tableau workbook - in which Tableau generates queries that only select dimensional values restricted to those that join to fact data.

In addition to filtering upon factless queries, AtScale also supports demand-defined aggregation of factless, incidental filter queries when query.factless.ignoreIncidentalFilter=false. System aggregation of factless queries may occur if:

There are statistics available and the system handles user-generated dimension-only queries.
The compression factor is met. The compression factor is a ratio of the fact dataset(s) used to join the data, to the cardinality of the hypothetical instance.
The aggregate data does not already exist.
An aggregate exists, but its definition should be superseded by another definition.
All Aggregate Locality settings for the datasets associated with a hypothetical aggregate definition are satisfied.

The aggregates will only contain records for dimensional combinations that have fact records in the joining fact table.

Note: To enable this functionality in multi-fact models, the user must also enable the engine setting query.factless.useIncidentalFacts.

Procedure

In Design Center, open the AtScale menu and select Settings.
In the Settings panel, click Engine.
Search for the query.factless.ignoreIncidentalFilter or query.factless.useIncidentalFacts settings and enable/disable them as needed.
Click Save Engine Settings.