Model - Entity Column

Navigate to the Advanced Options pane on the left side of your screen. On the top of Advanced Options, navigate to the Model - Entity Column tab.

📘

What are entity columns?

Entity columns allow Empower to track and maintain detailed metadata about entities.

The Entity Column table, which shows all existing entity columns. You can also create new ones.

The Entity Column table, which shows all existing entity columns. You can also create new ones.

Entity columns should be a 1:1 mapping with the fields of all gold-layer dims and facts. All entity columns must be linked to an existing Entity (defined in Entity).

Filtering

You can filter Entity Column by both Entity and Model for easy navigation.

Use these filters to quickly scope your view to a specific set of assets.

Creating an Entity Column

Create a new Entity Column record by clicking "New Record". This will bring up the Entity Column creation modal.

Creating a new record in the Entity Column tab.

Creating a new record in the Entity Column tab.

Below is a table describing each of the fields in an Entity Column record.

Field NameDescriptionExample Value
IDThe unique generated ID for this entity (autogenerated).1
Model IDThe model this entity column is associated with. Should be the same model the attached entity is associated with. Must be an existing model in the Models table.financial-model-division-a
Entity IDThe entity this entity column is associated with. Must be an existing entity in the Entities table.dim_product
NameThe name for this entity column.productId
DescriptionA short description of this entity column and what it is for.the ID of the product
Type IDThe type for this entity column. Must be one of several types explained below.surrogate_key
OrdinalThe order in which this column should be placed relative to others.1
NullableA toggle that signifies if this field is nullable or must always have a value.TRUE
Missing ValueA field used to define buildout behavior when missing values are found in this field. Used to create a ghost record.-1
Source ColumnThis column can be left blank, it is depreciated[depreciated]
ActiveA toggle to activate/inactivate this entity columnTRUE

After filling out fields, hitting the create button will result in a new entry in this table.

🚧

Ordinal Column

The ordinal setting is only taken into account during the first model build. Any subsequent build will assume the order within the existing table. In other words, if Ordinal settings are changed after the first time the model is successfully built, these changes will not be respected.

📘

Missing Values and Ghost Records

Empower uses ghost records to gracefully handle missing values in entity columns. In a Kimball data warehouse model, a ghost record (also known as a "dummy record" or "default record") is used to handle scenarios where there are missing or unknown values in a dimension table.

Ghost records are particularly useful in scenarios where:

  • Data comes from multiple sources, and some sources might have incomplete or inconsistent data.
  • The data quality is not guaranteed, and there might be missing or unknown dimension values.
  • The business requires every transaction to be accounted for, even if some attributes are unknown.

By implementing ghost records, you can create a more resilient and user-friendly data warehouse that can handle real-world data imperfections gracefully.

👍

Activate your Entity Column!

Make sure to toggle the Entity Column ACTIVE in order to start using it. Inactive Entity Columns will not be built by the Empower system.

Type ID

Selecting the correct Type ID for your column is as simple as selecting from a drop down list. Please refer to the following table to understand how assignments should be made.

IDNameDefinitionExample Value
1surrogate_keyA unique identifier for each row in a table, often generated automatically by the system, which has no business meaning.A primary key that is a system-generated number, such as an auto-incrementing integer in MySQL or a sequence in PostgreSQL.
2business_keyA natural key which has a real-world meaning and relevance to the business domain.For a Customer table, the customer's email address or social security number can be a business key.
3foreign_keyA key used to establish a relationship between two tables by referencing the primary key in another table.In an Order table, a foreign key might be Customer_ID, which links to the primary key of the Customer table.
4metadataData that provides information about other data, such as descriptions, data types, and relationships between entities.A description of a database table, such as the column names, data types, and constraints, is an example of metadata.
5attributeA characteristic or property of an entity within a table, represented as a column.In a Product table, 'product_name', 'price', and 'stock_quantity' are examples of attributes.
6metricA quantifiable measurement, often used for analytical purposes to track performance and trends.In a Sales table, total revenue, average order value, and the number of items sold can be considered metrics.