Adding dimensional data

In general, dimensional data is used to provide descriptive information about a fact by using the code of the dimension entity that is stored in the facts to join on the dimension table to retrieve the descriptive information.

The position fact that we loaded in the previous section has four explicit foreign keys, which we have aptly named with the _CODE suffix: the account code, the security code, the exchange code, and the currency code.

These four codes are the references, or foreign keys, to the four dimensions that we can directly connect to this fact.

There is also one extra implicit dimension, the bank dimension, which is implied in the names of the models.

Creating clear data models for the refined and data mart layers

To be able to finalize the dimensions and the fact design, we need to have a clear picture of the data model that we want to use in our reports (the data mart layer), which is often a star schema or, rarely, a wide table...