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.
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.
Below is a table describing each of the fields in an Entity Column record.
| Field Name | Description | Example Value |
|---|---|---|
| ID | The unique generated ID for this entity (autogenerated). | 1 |
| Model ID | The 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 ID | The entity this entity column is associated with. Must be an existing entity in the Entities table. | dim_product |
| Name | The name for this entity column. | productId |
| Description | A short description of this entity column and what it is for. | the ID of the product |
| Type ID | The type for this entity column. Must be one of several types explained below. | surrogate_key |
| Ordinal | The order in which this column should be placed relative to others. | 1 |
| Nullable | A toggle that signifies if this field is nullable or must always have a value. | TRUE |
| Missing Value | A field used to define buildout behavior when missing values are found in this field. Used to create a ghost record. | -1 |
| Source Column | This column can be left blank, it is depreciated | [depreciated] |
| Active | A toggle to activate/inactivate this entity column | TRUE |
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.
| ID | Name | Definition | Example Value |
|---|---|---|---|
| 1 | surrogate_key | A 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. |
| 2 | business_key | A 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. |
| 3 | foreign_key | A 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. |
| 4 | metadata | Data 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. |
| 5 | attribute | A 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. |
| 6 | metric | A 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. |
Updated 3 months ago