Activating Inactive Dimension Keys in the PS_F_BEGIN_BAL Table
The PS_F_BEGIN_BAL fact table is delivered with the following active dimension keys:
|
Active Dimension Keys |
Active Dimension Keys |
|---|---|
|
BU_SID |
OPER_UNIT_SID |
|
PYEAR_SID |
FUND_CODE_SID |
|
LEDGER_SID |
PRODUCT_SID |
|
ACCOUNT_SID |
PROJECT_SID |
|
ALT_ACCT_SID |
BUDGET_REF_SID |
|
DEPT_SID |
STAT_CODE_SID |
|
SUBLEDGER_SID |
SCENARIO_SID |
|
AFFILIATE_BU_SID |
CURRENCY_CD |
The following inactive columns can be activated as additional dimension keys:
|
Inactive Dimension Key Columns |
Inactive Dimension Key Columns |
|---|---|
|
PROGRAM_FDM_SID |
ADJUST_TYPE_SID |
|
CBU_SID |
CLASS_FIELD_SID |
|
AFF_OU_SID |
CHARTFIELD1_SID |
|
AFF_FUND_SID |
CHARTFIELD2_SID |
|
BOOK_CODE_SID |
CHARTFIELD3_SID |
To activate inactive dimension keys, you will use the PeopleSoft Application Designer and IBM WebSphere DataStage tools.
Configuring PS_F_BEGIN_BAL in Application Designer
To activate additional dimension keys in the PS_F_BEGIN_BAL table:
Open PeopleSoft Application Designer.
Select File, Open from the menu.
In the Open Definition dialog box, select Record for the Definition field, then enter F_BEGIN_BAL.
The Record Fields tab for the PS_F_BEGIN_BAL table appears. Note the inactive dimension keys.
In the Record Fields tab, right-click an inactive dimension key and select Record Field Properties.
The Record Field Properties window appears for the column.
In the Use tab of the Record Field Properties window select the Key and Search Key check boxes.
Click OK to continue.
Save your changes.
Select Build, Project from the menu.
The Build window appears.
In the Build window select the Create Indexes and Execute and Build Script check boxes.
Select Settings.
The Build Settings window appears.
In the Create tab of the Build Settings window, select the Recreate Index only if Modified check box.
Click OK to continue.
Click Build to complete your configuration of the PS_F_BEGIN_BAL table.
Repeat steps 4 through 13 to activate other inactive dimension keys in the PS_F_BEGIN_BAL table.
Configuring the J_Fact_PS_F_BEGIN_BAL Job in DataStage
To configure the new keys in the J_Fact_PS_F_BEGIN_BAL job:
In DataStage Designer client, navigate to the J_Fact_PS_F_BEGIN_BAL job in the project tree and open it for editing.
Image: J_Fact_PS_F_BEGIN_BAL job in the project tree
This example illustrates the fields and controls on the J_Fact_PS_F_BEGIN_BAL job in the project tree. You can find definitions for the fields and controls later on this page.
Open the source DRS stage (DRS_PS_F_LEDGER) and select the Outputs tab.
Select the Columns sub-tab and select Yes for the Key field for each column you have activated as a key.
Image: Specifying keys in the DRS_PS_F_LEDGER target stage
This example illustrates the fields and controls on the Specifying keys in the DRS_PS_F_LEDGER target stage. You can find definitions for the fields and controls later on this page.
Select the SQL and User-Defined sub-tabs and modify the user defined query to add any newly activated dimension keys.
Image: Modifying the SQL query in the DRS_PS_F_LEDGER source stage
This example illustrates the fields and controls on the Modifying the SQL query in the DRS_PS_F_LEDGER source stage. You can find definitions for the fields and controls later on this page.
Open the IPC stage (IPC_SRC1) and select the Outputs tab.
Select the Columns sub-tab and locate each column you have activated as a key in Application Designer.
Select the Key field check box for each column you have activated as a key.
Image: Specifying keys in the IPC_SRC1 stage
This example illustrates the fields and controls on the Specifying keys in the IPC_SRC1 stage. You can find definitions for the fields and controls later on this page.
Open the DRS target stage (DRS_PS_F_BEGIN_BAL) and select the Inputs tab.
Select the Columns sub-tab and select Yes for the Key field for each column you have activated as a key.
Image: Specifying keys in the DRS_PS_F_BEGIN_BAL target stage
This example illustrates the fields and controls on the Specifying keys in the DRS_PS_F_BEGIN_BAL target stage. You can find definitions for the fields and controls later on this page.
Note: Ensure that you add all the same keys that you added in Application Designer.
Select the SQL sub-tab and add the columns to the existing SQL statement.
Click OK to continue, then save your changes.
Open the HASH_BEG_END_AMT hashed file to configure the new keys in the lookup.
In the HASH_BEG_END_AMT hashed file, select the Outputs then the Columns sub-tab.
Select HASH_BEG_AMT_lkp for the Output Name field.
Select the Key field check box for each column you have activated as a key.
Image: Specifying keys in the HASH_BEG_END_AMT hashed file
This example illustrates the fields and controls on the Specifying keys in the HASH_BEG_END_AMT hashed file. You can find definitions for the fields and controls later on this page.
Select HASH_END_AMT_lkp for the Output Name field and repeat step 12.
Click OK to continue, then save your changes.
Open the Build_PS_F_BEGIN_BAL stage to map the newly activated keys in the hashed files to the source columns.
Image: Mapping keys in the Build_PS_F_BEGIN_BAL stage
This example illustrates the fields and controls on the Mapping keys in the Build_PS_F_BEGIN_BAL stage. You can find definitions for the fields and controls later on this page.
Click OK to continue, then save your changes.