Child Fact to Parent Dimension Join Example

In this example of a data store to data store join for a Managed Data Mashup, a fact in a Child space joins to a dimension from a parent space. See Managed Data Mashups.

Important: In Birst 5 ADR spaces, the query language relies on surrogate keys to join a dimension to fact table. If the Parent Space for a Managed Data Mashup (MDM) is an Always On space, the surrogate keys can change when the data is being processed preventing the Child space fact table from joining with the Parent space dimension table due to the mismatch of the surrogate keys. A similar case applies for an Always On Child space.

Birst does not recommend a use case for MDM when both the Parent and Child spaces are Always On spaces as the surrogate keys are not guaranteed to be consistent when the spaces are being processed.

Note: Modeler Networking does not rely on surrogate keys to join Parent Child tables.

Example Scenario

A global corporation has centralized corporate data that needs to blend with country-specific data. Each local office uploads their own fact data to their own spaces (Child spaces) and joins those to the shared model from the centralized space.

A report author wants to look at a customer from the shared dimension and analyze in the same report the sales value global measure. In addition,

the author wants to see the number of complaints for a given period that is available from the local data.

The parent space provides the Global Sales fact and Stores dimension.

The Child space provides the Current Inventory fact.

Example Steps

  1. The Infor Team recommends that you treat imported dimensions as local dimensions. In Admin - Define Sources - Manage Sources - Columns, target the Current Inventory to the appropriate hierarchy from the parent.

  2. Then in the Grain tab set the grains, picking the dimensional level from the parent.

  3. Click Save.
  4. Go to Process Data - Process New Data and click Process Now to re-process the Child data for the updated hierarchies and grains.
  5. Create the report including:
  • The sum of the revenue, from the parent fact
  • The retail store name, which is a joined dimension from the parent
  • The sum of the current inventory, from the Child fact

We recommend graining the Parent Fact table locally to the Child Dimension and instead manually join the imported Parent fact table using the Data Flow page. This method eliminates the need for creating surrogate keys which allows for the joins to occur based on the column names which will not get changed during data processing. This should prevent a mismatch of keys for Always On spaces when employing this method. Note: This is a strategy that may not always be available because the key column from the parent dimension must also exist in your Child fact to carry out this procedure.

Using Complex Joins Instead of Grains to Join Parent and Child Tables

Start by creating a compound key for the local Child dimension table so a join can be made to the Parent Fact table. In the following example, the Orders Fact table can be joined with the Parent Customers Dimension table by using the CustomerID. To make this key available to join, the Child Fact table will need to have its key structure modified to employ both the OrderID (current key) and CustomerID (proposed added key) to create a compound key for the Child Orders Fact table.

Step 1: Create the Compound Key for your local Child dimension table. You can select the Orders table from the Data Flow page, right click and select Set Primary Key option to open the dialogue box.

Alternatively, you can also create a compound key from the Define Sources > Hierarchies page as follows:

Step 2: Since the Child warehouse is now using a compound key, you will need to re-process your Child space if you previously processed the Child space warehouse for a single level key.

Step 3: It is recommend that you also make the OrderID column a key in your Parent dimension table. It is okay to specify compound keys for your fact table if possible. Import your Parent Dimension package (Orders)

Step 4: Create the manual "complex join" by navigating to the Data Flow page and right-click on the local Orders fact table (pink icon) and select Join to a related source to join with the imported Customers dimension via inner join on CustomerID.

You should now be able to report on the Child Fact and Parent Dimensions.

Tip: If Birst does not show or allow a join, it may be because the data sources are on different database instances. Contact your Birst representative.

Data sources residing on different database cannot join as follows: