10.2.4 Case 3

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 table DIM_PRODUCTS_TL to DIM_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.