Ajilius uses a number of metadata column roles to govern how information is transformed through the data warehouse.

Roles are displayed for table columns on the Load, Stage, Dimension and Fact List screens.

These roles are assigned using the Column Role value during column editing.

Load Table Roles

BK Business Key Identifies the natural key or primary key columns for the table. The combination of BK columns uniquely identify each row in the table.
ED Effective Date When historical data is being loaded, flagging a date column as the Effective Date will make it available for use when choosing the appropriate row in a type-2 slowly changing dimension.
GUID Row GUID

A unique identifier may be assigned to each row that is loaded into the data warehouse using a GUID.

Create a new column before assigning this role.

HASH Row Hash

An SHA256 hash will be created over the non-BK columns from the source table at the time the row is loaded.

Create a new column before assigning this role

SRV Source Server

When the same table is being loaded from multiple databases, such as an SAAS vendor consolidating data from multiple clients, this metadata column may be used to identify the server from which each table was loaded.

The value used will be the SERVER property assigned to the data source.

Create a new column before assigning this role.

DB Source Database

When the same table is being loaded from multiple databases, such as an SAAS vendor consolidating data from multiple clients, this metadata column may be used to identify the database from which each table was loaded.

The value used will be the DATABASE property assigned to the data source.

Create a new column before assigning this role.

TBL Source Table

When data from several tables is being consolidated into one LOAD table, this metadata column may be used to identify the table from which each row was loaded.

The value used will be the SOURCE TABLE name.

Create a new column before assigning this role.

DT Load DateTime

Use this metadata role to timestamp each row as it is extracted by Ajilius.

Create a new column before assigning this role.

 

Stage Table Roles

BK Business Key Identifies the natural key or primary key columns for the table. The combination of BK columns uniquely identify each row in the table.
PAK Parent Key Identifies the column which holds the parent row key in a parent-child ragged hierarchy. This flag is required by the FLATTEN stage table operation.
IDT Insert DateTime

DateTime at which this row was inserted into the stage table.

Use this column with Persistent Staging tables to identify the datetime at which the row was first added.

UDT Update DateTime

DateTime at which this row was last updated in the stage table.

Use this column with Persistent Staging tables to identify the datetime at which the row was last updated.

This value will be set to NULL when the row is inserted.

UK Unpivot Key

Identifies the column which will form part of the row key after unpivoting a table. After unpivot, this column will hold the column name values.

Refer to the Unpivot Transform documentation for more details regarding this role.

UV Unpivot Value

Identifies the column which will hold unpivoted values after unpivoting a table.

Refer to the Unpivot Transform documentation for more details regarding this role.

UC Unpivot Column

Identifies the columns which are to be unpivoted. After unpivoting a table, these columns will be transposed to rows in the new stage table.

Refer to the Unpivot Transform documentation for more details regarding this role.

ED Effective Date

When historical data is being loaded, flagging a date column as the Effective Date will make it available for use when choosing the appropriate row in a type-2 slowly changing dimension.

This column role will most usually be carried forward from the Load table.

 

Dimension Table Roles

DK Dimension Key Identifies the surrogate key automatically created by Ajilius to uniquely identify a dimension row.
BK Business Key

Identifies the natural key or primary key columns for the table. The combination of BK columns uniquely identify each row in the table from a business perspective.

In the case of a type-2 slowly changing dimension (SCD2), there may be multiple rows for any given business key.

SCD0 Slowly Changing Dimension
Type 0

A column with the role SCD0 will never be over-written after creation.

Typical uses for an SCD0 column are calendars (which probably won’t change in our life times), and columns like “opening balance” for a bank account.

SCD1 Slowly Changing Dimension
Type 1

A column with the role SCD1 will be updated in place whenever the row is updated.

This is the most common type of column in a dimensional data warehouse.

SCD2 Slowly Changing Dimension
Type 2

A column with the role SCD2 will cause a new row to be created each time the column value changes.

This is the most powerful column role in a dimensional data warehouse, as it enables changes in data to be tracked over time, and for facts referencing this data to be queried in their historical context.

SCD3 Slowly Changing Dimension
Type 3
A column with the role SCD3 will automatically maintain an “old value” history whenever it is updated.
IDT Insert DateTime DateTime at which this row was inserted into the dimension table.
UDT Update DateTime

DateTime at which this row was last updated in the dimension.

This value will be NULL when the row is first inserted.

 

Fact Table Roles

BK Business Key

Identifies the natural key or primary key columns for the table. The combination of BK columns uniquely identify each row in the table.

Usually only used to identify rows in Accumulating Snapshot fact tables, as these are the only types of fact that will be updated.

FK Foreign / Dimension Key

Identifies the relationship to a dimension table.

Automatically created when dimensions are added to fact table metadata.

DD Degenerate Dimension A dimension value that is not linked to a dimension table. This is usually because there is no additional information to be held about the dimension in relation to this fact.
FADD Fully Additive Measure It is desirable that all measures in a fact table are fully additive. That means the measure can be summed across all dimensions in the star schema.
SADD Semi Additive Measure

A semi-additive measure may only be summed across some of the dimensions in a star schema.

The most common example is “balance” information which does not make sense when summed by time. For example, if I have an account balance of $100 today, and $110 tomorrow, their sum to $210 is a meaningless number.

NADD Non-Additive Measure

A non-additive measure can not be summed across any dimension.

Non-additive measures are typically ratios.

For this reason, ratios should typically be stored as the two values in the ratio, and the ratio calculated through the BI tool. This enables the base values to be summed, and the ratio calculated across any dimension.

IDT Insert DateTime DateTime at which the row was inserted to the fact table.
UDT Update DateTime

DateTime at which the row was last updated in the fact table.

Only used by Accumulating Snapshot fact tables.