Navigation: Flag Descriptions >

Key Field Used in Expression

 

 

 

 

The key field "xxx" was used in an expression. This is considered bad practice.

 

A key field was used in an expression, for example:

 

Count(CustomerId)  // Count of Customers

 

Max(Aggr(Sum(Linesales), OrderId)  // Largest Order

 

Using a key field in an Expression or as a Dimension is considered to be a bad practice for a number of reasons.

 

As a Key Field appears in multiple tables, potentially with different values and densities, expression results can be ambiguous.  Results may be impacted by changes to the load script that modify the data model structure.
 

Using a key field may result in less than optimal performance. Using a non-key field will frequently perform better.
 

It is generally a good idea to AutoNumber keys. If the key field is used in an expression or Dimension, it may prevent the use of AutoNumber.
 

The additional considerations of using a key field, e.g. adding DISTINCT to a Count() function, make the expression more complex and require a higher level of knowledge from the expression Author.

 

 

Guide

 

Consider creating a new field that matched the purpose and matches the business model. 

 


Consider the following data model:
 

 

 

 

To calculate a count of Customers, don't use:

 

Count(DISTINCT CustomerID)

 

Instead create a new field in the customers table:

 

1 as CustomerCounter

 

Then count using the expression:

 

Count(CustomerCounter)

 


Consider the following data model:

 

 

 

To find the largest order by LineSales amount, don't use:

 

Max(Aggr(Sum(LineSales), SalesOrderID))

 

Instead duplicate the SalesOrderID as a new field in theSalesOrderHeader table:

 

SalesOrderID as SalesOrderRecord

 

Then calculate using the expression:

 

Max(Aggr(Sum(LineSales), SalesOrderRecord))

 

 

 

 

 

 

QSDA Pro version 3.1.2