Dimensional Data Modeling - Descriptif Attribute (Dimensional Attribute)

About

A descriptif attribute is class attribute that describe a property or characteristic of a dimension.

They are used to label, filter and/or group on.

Articles Related

Example

Typical attributes for a product dimension :

• short description (10 to 15 characters)
• long description (30 to 50 characters)
• a brand name
• a category name
• packaging type
• size (It's a number but it behaves more like a textual descriptor, Size is a discrete and constant descriptor of a specific product)
• square footage in store dimension. One might be tempted to place it in the fact table. However, it's clearly a constant attribute of a store and is used as report filter.
• numerous other product characteristic.

Naming

• The best attributes are discrete (textual)
• They should consist of real words rather than cryptic abbreviations.

Identification

In a query or a report request, this descriptive attributes are identified by:

• the by words.
• and where words

For example, when a user states that he or she wants to see dollar sales by week by brand, week and brand must be available as dimension attributes.

Data Type

The data type is generally discrete but not always. You may find numeric descriptif attribute.

For example: the revenue of last year is a descriptif element even if it's a number because you may want to use it as a filter to retain customers with a certain amount of revenue.

Sql

They serve as the primary source for:

• filtering,
• grouping
• and reporting labels.

The descriptif attributes are used in the following SQL statement:

• SQL - GROUP BY (aggregate query)
• SQL - WHERE (filtering)
• SQL Function - Window Aggregate (Analytics function) - by clause of a window function

They are not used in any aggregate function.

Grain

The combination of all level of each descriptif attributes forms the grain of a relation (table, query,…).