This section describes how to change the Product dimension. The seeded product dimension is PRODUCT. You can change any product dimensions other than the seeded dimension, as follows:
Topics:
· Overview
· Steps to Point to a Different Product Dimension in ALMBI
ALM Product is a logical idea in the Presentation or Logical layer. In the physical layer, it can hold any one of the product dimension members that are available in ALM.
(This can be DIM_COMMON_COA, DIM_PRODUCT, DIM_GL_ACCOUNT, or any other user-defined Product dimensions).
As part of the released RPD (ALMBI 6.1 version), the ALM Product container was mapped to the DIM_PRODUCT table in the physical layer (containing PRODUCT_ID). In an implementation, however, users can point to any other product dimension table.
The section Steps to Point to a Different Product Dimension in ALMBI describes the steps to change the Product Dimension in the following three cases:
· Case 1: Changing the Product Dimension from DIM_PRODUCT to DIM_COMMON_COA.
· Case 2: Changing the Product Dimension from DIM_COMMON_COA to DIM_GL_ACCOUNT.
· Case 3: Changing the Product Dimension from DIM_PRODUCT to the user-defined dimension. For example, DIM_TM_COA_ID.
ALM Product is a logical idea in the Presentation or Logical layer and in the physical layer, it can hold any one of the product dimension members that are available in ALM. It can be from DIM_COMMON_COA, DIM_PRODUCT, DIM_GL_ACCOUNT, or any other user-defined dimension.
This section details the steps that must be performed to enable these Changes in the following places:
· RPD layer: change the references in the physical layer.
· Database layer: change data in the FSI_BI_SETUP_TABLE.
Changes in RPD layer: Stop the BI Server and open the RPD file in offline mode. Expand the ALMBI folder in the physical layer of the repository.
Take a backup of ALMBI RPD before doing any changes.
When the product dimension needs to be changed from DIM_PRODUCT to DIM_COMMON_COA, the following changes are required in the OBIEE Repository:
1. Repository physical layer will have DIM_PRODUCT.
2. Rename the table name from DIM_PRODUCT to DIM_COMMON_COA.
3. Rename every column name of the DIM_PRODUCT with PRODUCT to COMMON_COA.
4. Rename every column name of the DIM_PRODUCT with PRODUCT to COMMON_COA.
5. Double click Dim Product - Assumptions (View).
6. Go to General tab. Replace below query:
Select a.parent_id,a.child_id,a.child_depth_num,a.parent_depth_num,b.leaf_only_flag,c.product_name,a.hierarchy_id,a.display_order_num,c.language from dim_products_hier a
left outer join dim_products_b b on
a.child_id=b.product_id left outer join
dim_products_tl c
on b.product_id=c.product_id
and c.language='US'
with
select a.parent_id,a.child_id,a.child_depth_num,a.parent_depth_num,b.leaf_only_flag,c.common_coa_name,a.hierarchy_id,a.display_order_num,c.language from dim_common_coa_hier a
left outer join dim_common_coa_b b on
a.child_id=b.common_coa_id left outer join
dim_common_coa_tl c
on b.common_coa_id=c.common_coa_id
and c.language='US'
7. Click OK
8. Save the Repository file.
9. Start the BI Server.
The following changes are required in the Database layer. These can be applied in these two ways:
§ Batch Execution
§ Manual SQL Execution
The database component used to change the product dimension or any other user-defined product dimension is FN_ALM_BI_SET_USER_DEF_DIM - Oracle database function.
Table used by the Change of Product Dimension Process is FSI_BI_SETUP_TABLE. This table stores the source Dimension table name, source Column name, member column name, and Surrogate Key Column Name with the Join required flag. The flag JOIN_REQUIRED = 'Y' represents the active dimension to be considered for ALMBI Transformation.
Users must create a Batch Process for changing the product dimension. This process is explained in Executing the Change of Product Dimension.
The procedure takes Source Dimension Table Name, Source Column Name, Member Column Name, and Skey Column Name as additional parameters. Source Dimension Table Name is mandatory whereas Source Column Name, Member Column Name, and Skey Column Name are optional if a record with Source Dimension table Name already exists in FSI_BI_SETUP_TABLE. This process can also be run using the Simplified Batch window.
You can execute the function from the Operations framework module of OFSAAI.
Define a new Batch and an underlying Task definition from the Batch Maintenance window of OFSAAI. For more information on defining a new Batch, see the How to Define a Batch section.
To define a new task for a Batch definition:
1. Select the check box adjacent to the newly created Batch Name in the Batch Maintenance window.
2. Click Add + button from the Task Details grid. The Task Definition window is displayed.
3. Enter the Task ID and Description.
4. Select the Transform Data component from the Transform Data drop-down list.
5. Select the following from the Dynamic Parameters list:
§ Datastore Type: Select the appropriate datastore type from the list.
§ Datastore Name: Select the appropriate datastore name from the list.
§ IP address: Select the IP address from the list.
§ Rule Name: SelectFN_ALM_BI_SET_USER_DEF_DIM from the drop-down list.
(This is a seeded Data Transformation procedure installed as part of the ALMBI application. If you don't see this procedure in the list, contact Oracle support).
§ Parameter List: These are comma-separated values of Source Dimension Table Name, Source Column Name, Member Column Name, and Skey Column Name.
For example:'DIM_COMMON_COA', 'N_COMMON_COA_ID', 'COMMON_COA_ID', and 'N_COMMON_COA_SKEY'.
6. Click Save. The Task definition is saved for the selected Batch.
7. Execute the Batch. You can execute a Batch definition from the Batch Execution window of the OFSAAI Operations module.
NOTE |
This process can also be run using the Simplified Batch user interface. In the optional parameters field within the Simplified Batch window, specify the parameters mentioned above. For more details, see the Simplified Batch Execution window. |
The status of execution can be monitored using the Batch Monitor window of the OFSAAI Operations module.
The status messages in Batch Monitor are:
· N: Not Started
· O: On Going
· F: Failure
· S: Success
The Event Log window in Batch Monitor provides logs for execution with the top row being the most recent. If there is any error during execution, it will get listed here. Even if you see Successful as the status in Batch Monitor it is advisable to go through the Event Log and re-check if there are any errors.
Alternatively, the execution log can be accessed on the application server in the following directory $FIC_DB_HOME/log/date. The file name will have the Batch Execution ID.
The database-level operations log can be accessed by querying the FSI_MESSAGE_LOG table. The Batch Run ID column can be filtered for identifying the relevant log. (This is the same log you see in the Event Log window.)
Check the .profile file in the installation home if you are unable to navigate to the above-mentioned locations.
The details are as follows:
· Function Name: FN_ALM_BI_SET_USER_DEF_DIM
· Parameters: BATCH_RUN_ID, MIS_DATE, DIMTABLENAME, SOURCECOLNAME, and MEMBERCOLNAME
· Sample Parameter Values: 'Batch1' , '20130310' , ' DIM_COMMON_COA', 'N_COMMON_COA_ID', 'COMMON_COA_ID', and'N_COMMON_COA_SKEY'
· Manual Change of Product Dimension
a. Execute the following query and it will return three rows as seen in the following screen shot.
update fsi_bi_setup_table set JOIN_REQUIRED='Y' where
target_table_name='FCT_TABLE' and
SOURCE_DIM_TABLE_NAME='DIM_COMMON_COA';
update fsi_bi_setup_table set JOIN_REQUIRED='N' where
target_table_name='FCT_TABLE' and SOURCE_DIM_TABLE_NAME='DIM_PRODUCT';
By default, the JOIN_REQUIRED column will have 'Y' for the row where SOURCE_DIM_TABLE_NAME='DIM_PRODUCT'.
b. Execute the following statements to change the value of the JOIN_REQUIRED column for DIM_COMMON_COA.
c. Commit the transaction.
It appears as seen in the following figure.
The ALMBI Transformation package should also be updated in ALMBI for a user-defined product dimension.
Perform the following actions to update the ALMBI Transformation package (PKG_ALM_BI_TRANSFORMATIONS) to handle a user-defined dimension.
NOTE |
The examples used here use the following: DIM_ALM_COA table with dimension member column as ALM_COA_ID You should substitute this with the correct user-defined dimension table and column name as required. |
1. Inside the PKG Declaration section, include the new user-defined dimension reference in the variable 'v_basic_select'.
For example, existing variable declaration is:
v_basic_select VARCHAR2(500) := 'SELECT PROCESS_ID,PRODUCT,
CURRENCY,START_DATE_INDX,SCENARIO,RESULT_TYPE,ORG_UNIT,COMMON_COA,BUCKET
S,LEGT
YPE,';
For a new user defined dimension reference ('ALM_COA_ID'), make the following
update:
v_basic_select VARCHAR2(500) := ' SELECT PROCESS_ID,PRODUCT,
CURRENCY,START_DATE_INDX,SCENARIO,RESULT_TYPE,ORG_UNIT,COMMON_COA,BUCKET
S,LEGT
YPE, ALM_COA_ID,';
2. Inside the PKG Body section, extend the existing IF condition with a new ELSEIF.
ELSIF V_BI_SETUP_TABLE.MEMBER_COL_NAME = 'ALM_COA_ID' THEN
V_JOIN_CLAUSE := V_BISETUP_JOIN_COLUMN ||' = CONS_RES_DTL.ALM_COA_ID AND ' ||
V_BI_SETUP_TABLE.DIM_TABLE_NAME ||'.F_LATEST_RECORD_INDICATOR = ''Y'' ';
3. Once the above changes are complete, recompile, and execute the package.
When the new product dimension is DIM_GL_ACCOUNT, the following changes are required in the OBIEE Repository:
1. Rename the existing DIM_GL_ACCOUNT table name to DIM_GL_ACCOUNT_OLD.
2. Rename the DIM_COMMON_COA to DIM_GL_ACCOUNT in the same way it has been stated above for DIM_PRODUCT.
3. Rename every column of DIM_COMMON_COA in the following way:
4. Double click on the alias table Dim General Ledger in the physical layer.
5. Source Table is displayed as DIM_GL_ACCOUNT_OLD.
6. Click Select next to the Source table name.
7. Select the physical table DIM_GL_ACCOUNT and click OK, and then click OK again.
8. Select Dim Product - Assumptions (View) and double click.
9. Go to General tab. Replace below query:
select a.parent_id,a.child_id,a.child_depth_num,a.parent_depth_num,b.leaf_only_flag,c.common_coa_name,a.hierarchy_id,a.display_order_num,c.language from dim_common_coa_hier a
left outer join dim_common_coa_b b on
a.child_id=b.common_coa_id left outer join
dim_common_coa_tl c
on b.common_coa_id=c.common_coa_id
and c.language='US'
with
select a.parent_id,a.child_id,a.child_depth_num,a.parent_depth_num,b.leaf_only_flag,c.gl_account_name,a.hierarchy_id,a.display_order_num,c.language from dim_general_ledger_hier a
left outer join dim_general_ledger_b b on
a.child_id=b.gl_account_id left outer join
dim_general_ledger_tl c
on b.gl_account_id=c.gl_account_id
and c.language='US'
10. Click OK.
11. Save the Repository file.
12. Start the BI Server.
Changes required in the Database layer:
Changes to the Database layer can be achieved in two ways:
· Batch Execution
Follow the steps mentioned in section Case 1 of 'Change of Product Dimension through Batch Execution'.
NOTE |
The following parameter list needs to be provided when changing from DIM_COMMON_COA to DIM_GL_ACCOUNT: 'DIM_GL_ACCOUNT','N_GL_ACCOUNT_ID', 'GL_ACCOUNT_ID', and 'N_GL_ACCOUNT_SKEY'. |
· Manual Execution
a. Execute the following query and it will return three rows as seen in the following figure.
By default, the JOIN_REQUIRED column will have 'Y' for the row where SOURCE_DIM_TABLE_NAME='DIM_COMMON_COA'.
b. Execute the following statements to change the value of JOIN_REQUIRED column for DIM_GL_ACCOUNT.
update fsi_bi_setup_table set JOIN_REQUIRED='N' where target_table_name='FCT_TABLE' and
SOURCE_DIM_TABLE_NAME='DIM_COMMON_COA';
update fsi_bi_setup_table set JOIN_REQUIRED='Y' where
target_table_name='FCT_TABLE' and
SOURCE_DIM_TABLE_NAME='DIM_GL_ACCOUNT';
c. Commit the transaction.
When the product dimension needs to be changed from DIM_PRODUCT to User-defined dimension (for example, DIM_TM_COA_ID), the following changes are required in the OBIEE Repository:
1. Repository physical layer will have DIM_PRODUCT.
2. Rename the table name from DIM_PRODUCT to DIM_TM_COA_ID.
3. Rename every column name of the DIM_PRODUCT from 'PRODUCT' to 'TM_COA'.
4. Select Dim Product - Assumptions (View) and double click.
5. Go to the General tab.
6. Replace below query:
select a.parent_id,a.child_id,a.child_depth_num,a.parent_depth_num,b.leaf_only_flag,c.product_name,a.hierarchy_id,a.display_order_num,c.language from dim_products_hier a
left outer join dim_products_b b on
a.child_id=b.product_id left outer join
dim_products_tl c
on b.product_id=c.product_id
and c.language='US'
with
select a.parent_id,a.child_id,a.child_depth_num,a.parent_depth_num,b.leaf_only_flag,c.tm_coa_name,a.hierarchy_id,a.display_order_num,c.language from dim_tm_coa_hier a
left outer join dim_tm_coa_b b on
a.child_id=b.tm_coa_id left outer join
dim_tm_coa_tl c
on b.tm_coa_id=c.tm_coa_id
and c.language='US'
7. Click OK.
8. Select DIM_PRODUCTS_TL.
9. Rename the tableDIM_PRODUCTS_TL toDIM_TM_COA_TL.
10. Expand DIM_TM_COA_TL.
11. Rename column PRODUCT_ID to TM_COA_ID.
12. Rename column PRODUCT_NAME to TM_COA_NAME.
13. Save the Repository file.
14. Start the BI Server.
The following changes are required in the Database layer. These can be applied in following two ways:
· Batch Execution
· Manual SQL Execution
The database component used to change the product dimension or any other user-defined product dimension is FN_ALM_BI_SET_USER_DEF_DIM - Oracle database function.
Table used by the Change of Product Dimension Process is FSI_BI_SETUP_TABLE. This table stores the source Dimension table name, source Column name, member column name, and Surrogate Key Column Name with the Join required flag. The flag JOIN_REQUIRED = 'Y' represents the active dimension to be considered for ALMBI Transformation as shown below:
Users must create a Batch Process for changing the product dimension. This process is explained in section Executing the Change of Product Dimension. The procedure takes Source Dimension Table Name, Source Column Name, and Member Column Name as additional parameters. Source Dimension Table Name is mandatory whereas Source Column Name and Member Column Name are optional if a record with Source Dimension table Name already exists in FSI_BI_SETUP_TABLE. This process can also be run using the Simplified Batch window.
You can execute the function from the Operations framework module of OFSAAI. Define a new Batch and an underlying Task definition from the Batch Maintenance window of OFSAAI. For more information on defining a new Batch, see the How to Define a Batch section.
To define a new task for a Batch definition:
1. Select the check box adjacent to the newly created Batch Name in the Batch Maintenance window.
2. Click Add + button from the Task Details grid. The Task Definition window is displayed.
3. Enter the Task ID and Description.
4. Select the Transform Data component from the Transform Data drop-down list.
5. Select the following from the Dynamic Parameters list:
§ Datastore Type: Select the appropriate datastore type from the list.
§ Datastore Name: Select the appropriate datastore name from the list.
§ IP address: Select the IP address from the list.
§ Rule Name: Select FN_ALM_BI_SET_USER_DEF_DIM from the drop-down list.
(This is a seeded Data Transformation procedure installed as part of the ALM BI application. If you don't see this procedure in the list, contact Oracle support).
Parameter List: These are comma-separated values of Source Dimension Table Name, Source Column Name, Member Column Name, and Skey Column Name.
For example: 'DIM_TM_COA_ID','N_TM_COA_ID','TM_COA_ID','N_TM_COA_SKEY'.
6. Click Save.
The Task definition is saved for the selected Batch.
7. Execute the Batch.
You can execute a Batch definition from the Batch Execution window of the OFSAAI Operations module.
NOTE |
This process can also be run using the Simplified Batch user interface. In the optional parameters field within the Simplified Batch window, specify the parameters mentioned above. For more details, see the Simplified Batch Execution |
The status of execution can be monitored using the Batch Monitor window of the OFSAAI Operations module.
The status messages in Batch Monitor are:
· N: Not Started
· O: On Going
· F: Failure
· S: Success
The Event Log window in Batch Monitor provides logs for execution with the top row being the most recent. If there is any error during execution, it will get listed here. Even if you see Successful as the status in Batch Monitor it is advisable to go through the Event Log and re-check if there are any errors.
Alternatively, the execution log can be accessed on the application server in the following directory $FIC_DB_HOME/log/date. The file name will have the Batch Execution ID.
The database-level operations log can be accessed by querying the FSI_MESSAGE_LOG table. The Batch Run ID column can be filtered for identifying the relevant log. (This is the same log you see in the Event Log window.)
Check the .profile file in the installation home if you are unable to navigate to the above-mentioned locations.
Change of Product Dimension can also be directly executed on the database through SQLPLUS.
Details are:
· Function Name: FN_ALM_BI_SET_USER_DEF_DIM
· Parameters: BATCH_RUN_ID, MIS_DATE, DIMTABLENAME,
· SOURCECOLNAME, MEMBERCOLNAME, and SKEYCOLNAME.
Sample Parameter Values: 'Batch1' , '20130310' , ' DIM_TM_COA_ID', 'N_TM_COA_ID', 'TM_COA_ID', and 'N_TM_COA_SKEY'
Execute the following queries
update fsi_bi_setup_table set JOIN_REQUIRED='N' where target_table_name='FCT_TABLE' and SOURCE_DIM_TABLE_NAME='DIM_PRODUCT'; INSERT INTO FSI_BI_SETUP_TABLE (target_table_name,target_col_name,member_col_name,source_dim_table_name ,source_column_name,skey_column_name,join_required) VALUES ('FCT_TABLE','N_PRODUCT_SKEY','TM_COA_ID','DIM_TM_COA_ID','N_TM_COA_ID', 'N_TM_COA_SKEY','Y');