When adding relations between facts and dimensions, I’ve noticed that the fields on which the relations are built are the same. However, I don’t understand why those names don’t match when using the fact loader in Astera DW Builder.
For example, if I have a relationship between Customers and Invoices, the key field is named CustomerKey in both tables in my dimension model. However, this name is different (Customer_CustomerID) when using the fact loader object (refer to attached screenshot). Can someone explain to me why it is like that?
The reason for this name change is simple but a little tricky to understand. It requires a deep understanding of how fact loader works.
When we design a dimensional model, we create surrogate keys (system generated fields) in the dimensions which act as the primary keys. These keys then become foreign keys in the fact tables.
While loading the fact with data, the fact table receives the business keys (source system’s unique identifier, or in other words, the key that existed before the surrogate was introduced) as the input to the foreign keys. What this means is that the fact table actually performs lookups using the business keys in order to extract the relevant surrogate key.
The fact loader transformation hence converts the names of the keys to reference the business keys along with the tables they are coming from (source table).
In your case, the source table’s alias is Customer and the business key residing in that table is CustomerID. So, Aliasing_Business key is the format in which the name is displayed on the fact loader.
Furthermore, this name change is to assist the user when mapping fields onto the fact loader.
See the image below for an example:
