Carrying Higher Keys Downhill

Birst allows higher level data relationships to be carried down to lower data levels, as you might see in a master-detail type of relationship. For example, let's say you have an Order Details source which does not contain keys to levels in the Employee and Supplier hierarchies. However, higher level sources (Order and Product) do. This illustrates that grain is meant to indicate the level of the data, not the key relationships. By understanding that all of the levels present on the Orders source are also present on the Order Details source, Birst can conclude that for each Order record, there are a unique set of records on the Order Details table – in other words, Order Details is a more detailed version of the same data. That means that any metric that exists on the Order Details source can be analyzed against any attribute or measure that exists at the Orders level. For example, there is a customer linkage to the Orders source, not to the Order Details source. But, you can analyze quantity sold (on the Order Details source) by customer name (on the Customer hierarchy which is linked to the Orders source via CustomerID). Birst knows that since it can analyze metrics on the Orders table against customer data, it can also analyze Order Details metrics against that same information.

The image below is a depiction of how grain configurations on the Orders and Order Details sources instruct Birst to carry the higher level grains from the Orders source down to the more granular Order Details source.

The automated process of setting up the logical model in Automatic mode applies heuristics based on an analysis of the data relationships and identifies the additional grains to be carried down to more detailed grains and will set these additional grains up on the detail source for the user. For Advanced spaces, it is the responsibility of the space designer to understand the data relationships and they can choose whether or not to add these additional grains to lower levels.

The following image shows the requirement for all the higher level grain setups to be set up on a lower level source in order for the keys to be successfully carried down.

The system carries the Employee and Customer keys down to the Order Details level through a join between the staging source and the already loaded higher level fact table. Birst automatically recognizes these dependencies when such a configuration exists and loads the higher level grain table first into the target schema.

The next image is a representation of how this load process might occur when an extra grain is intended to be carried down. Birst will use the available higher level grain keys to do the appropriate join to the higher level fact table in order to bring down the missing keys if possible.