Skip to main content

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

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

Procedure​

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