BUILDING THE FACT AND DIMENSION TABLE IN DATA MODELER

Building the Fact Table and Dimension Table in Data Modeler

  1. In the Data Modeler, click Lock to Edit button.

lock to edit button

  1. The Information dialog box displays confirming that you have successfully locked the model for editing.

information dbox

  1. Click OK. The model is locked by the current user for editing.

locked by current user

  1. In the Database accordion, select the W_AP_AGING_SUPPLIER_A table, click the Table Actions icon table actions icon, and select Add To Model.

 table actions then add to modeler

  1. The Add W_AP_AGING_SUPPLIER_A to SampleApp dialog box is displayed.

add table to sampleapp displayed

  1. Select the Add as Fact and Dimension Tables option, deselect the Let Data Modeler Recommend option, and click OK.

deselect let data modeler recommend

  1. The Add W_AP_AGING_SUPPLIER_A to SampleApp wizard is displayed.

add table to sampleapp wizard displayed new

8.   Create a new dimension table by selecting the following columns from the W_AP_AGING_SUPPLIER_A table and dragging them under NEW_DIMENSION :

  • LOC_CURR_CODE
  • OPERATING_UNIT_ORG_WID
  • TENANT_ID
  • W_INSERT_DT
  • W_UPDATE_DT

columns under new dimension

  1. Click the name of the new dimension table, and change it to Location and Date.

location and date

10.   Click Add to add a new dimension table .

A new dimension table is added below the Location and Date dimension table.

add to add a new dimension table

  1. Create a new dimension table by selecting the following columns from W_AP_AGING_SUPPLIER_A table and dragging them under NEW_DIMENSION :

 

  • TOT_DAYS_ODUE
  • PAYABLES_ORG_WID
  • OUTSTAND_XACT_CNT
  • LEDGER_WID

added columns in new dimension2

  1. Click the name of the new dimension table, and change it to Payables.

payables

  1. You can also add more dimension tables, by clicking on the Add button, if required.

 

  1. Create a fact table by completing the following steps:

a) Select the following columns from the W_AP_AGING_SUUPLER_A table and drag them under NEW_FACT.

  • COMPANY_ORG_WID
  • DATASOURCE_NUM_ID
  • MCAL_CAL_WID

b) In the Location and Date dimension table, select the LOC_CURR_CODE column as a primary key. This adds the LOC_CURR_CODE column to the fact table and creates a join between the Location and dimension table and the fact table.

c) In the Payables dimension table, select the OUTSTAND_XACT_CNT column as a primary key. This adds the OUTSTAND_XACT_CNT column to the fact table and creates a join between the Payables table and the fact table.

dimension table added to fact table

  1. Click the name of the new fact table, and change it to Facts.

facts table

  1. Click Next.

The wizard displays three Database views, a Fact table, two dimension tables and two joins.

wizard listing

  1. Review the listed objects, and click Create.

The three Database Views, a Fact Table, two Dimension Tables and two Joins are created in the model.

model created

  1. Click Done.

The SampleApp data model is displayed with the names of the fact table, dimension tables, and joins.

sampleapp shown new

Leave A Reply

Your email address will not be published. Required fields are marked *