Help and Support > Table of Contents > Using the star schema block

Using the star schema block- Part 1

The star schema block is used to lookup reference type data (a dimension table) and combine it with transactional or measure type data (a fact table) to provide analysis capability. For example, if you have a list of sales to customers, in which the product is identified only by a Product_ID field, by joining the product list you can lookup the various attributes of each product, and connect them to the order records.

Input data tables

join-1-product-reference-table (21K) join-1-sales-order-table (45K)

Connecting the two tables to the star schema block

The star schema block is connected as shown. The input stub on the left recieves the transactional or fact data- the data that generally has measures such as price, qty sold, etc. and the inputs at the top recieve the reference data- the data that contains codes or keys, and the various attributes for each object represented by the code. In this case, the products. It is possible to join many references onto a single join block. Only one transactional or fact table is joined to by the various references at one time.

star-schema-part-1-block-overview (76K) join-1-joined-result (85K)

The column names must be identical for the join to occur

The star schema block uses the column names to determine which columns you want to join together. This means that it is necessary to rename columns before connecting them to the join block if you wish to connect differently named fields. This renaming can be done either at the point where the data is imported using the data source area, or by putting a calculation block before the star schema block and simply renaming the column there.

Modifying the join key

When the join block is first connected, it joins on all available fields that exist in both the reference table and the input table. In some cases this may not be desirable- for example if all the tables have a common column named "Modified_date" these dates are not in fact related and should not be joined. To change which columns to use, simply click on the join block, and it is possible to modify the keys by pressing the "Edit Key" button corresponding to the reference in question.

join-1-edit-key (44K)