Activating Inactive Dimension Keys in the PS_F_KK_BALANCES Table
The PS_F_KK_BALANCES fact table is delivered with the following active dimension keys:
|
Active Dimension Keys |
Active Dimension Keys |
|---|---|
|
BU_SID |
FUND_CODE_SID |
|
KK_BUDGET_SID |
DEPT_SID |
|
BPERIOD_SID |
PROGRAM_FDM_SID |
|
CURRENCY_CD |
CLASS_FIELD_SID |
|
STAT_CODE_SID |
BUDGET_REF_SID |
|
PPERIOD_SID |
PRODUCT_SID |
|
ACCOUNT_SID |
|
|
OPER_UNIT_SID |
The following inactive columns can be activated as additional dimension keys:
|
Inactive Dimension Key Columns |
Inactive Dimension Key Columns |
|---|---|
|
CHARTFIELD1_SID |
PRJ_BU_SID |
|
CHARTFIELD2_SID |
PRJ_SID |
|
CHARTFIELD3_SID |
AC_SID |
|
AFFILIATE_BU_SID |
PROJ_RSRC_TYPE_SID |
|
AFF_FUND_SID |
|
|
AFF_OU_SID |
To activate inactive dimension keys, you will use the PeopleSoft Application Designer and IBM WebSphere DataStage tools.
Configuring PS_F_KK_BALANCES in Application Designer
To activate additional dimension keys in the PS_F_KK_BALANCES 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_KK_BALANCES.
The Record Fields tab for the PS_F_KK_BALANCES 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.
Note: For DB2 or OS390 databases, deactivate any unused dimension keys to maintain the 255 character index limit.
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_KK_BALANCES table.
Repeat steps 4 through 13 to activate other inactive dimension keys in the PS_F_KK_BALANCES table.
Configuring the J_Fact_PS_F_KK_BALANCES ETL Job in DataStage
To configure the new keys in the J_Fact_PS_F_KK_BALANCES job:
In DataStage Designer client, navigate to the J_Fact_PS_F_KK_BALANCES job in the project tree and open it for editing.
Image: J_Fact_PS_F_KK_BALANCES job in the project tree
This example illustrates the fields and controls on the J_Fact_PS_F_KK_BALANCES job in the project tree. You can find definitions for the fields and controls later on this page.
Open the DRS source stage DRS_PS_F_KK_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_KK_LEDGER source stage
This example illustrates the fields and controls on the Specifying keys in the DRS_PS_F_KK_LEDGER source 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 and User-Defined sub-tabs and modify the user defined query to use any newly activated dimension keys in the PS_F_KK_LEDGER table.
Image: Modifying the SQL query in the DRS_PS_F_KK_LEDGER source stage
This example illustrates the fields and controls on the Modifying the SQL query in the DRS_PS_F_KK_LEDGER source stage. You can find definitions for the fields and controls later on this page.
Note: Do not use LEDGER_SID and KK_TRANS_TYPE_SID in the join criteria.
Click OK to continue, then save your changes.
Open the DRS target stage DRS_PS_F_KK_BALANCES 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.
Click OK to continue, then save your changes.
Open the Aggregator stage Calculate_Bal to define sort order and grouping parameters.
Select the Inputs tab, then Columns sub-tab.
Use the Sort Order field to select Ascending, Descending, or Ignore for each key column; the value you choose should match the order defined in the SQL ORDER BY clause in the source DRS stage (DRS_PS_F_KK_LEDGER).
Image: Specifying sort order in the Calculate_Bal Aggregator stage
This example illustrates the fields and controls on the Specifying sort order in the Calculate_Bal Aggregator stage. You can find definitions for the fields and controls later on this page.
Select the Outputs tab, then Columns sub-tab.
Select the Group check box for each key column you want grouped.
Image: Specifying key columen grouping in the Calculate_Bal Aggregator stage
This example illustrates the fields and controls on the Specifying key columen grouping in the Calculate_Bal Aggregator stage. You can find definitions for the fields and controls later on this page.
Click OK to continue, then save your changes.