KB16547: Partition mapping table in Warehouse Catalog displays only one partition irrespective of the number of partition base tables when the partition base tables are from different user schemas and have the same table name

SYMPTOM:
The Warehouse Catalog only displays one partition for a partition mapping table even though there are multiple partitions. The partitions have the same name but reside in different schemas.
 
Consider the scenario in which the partition base tables in the warehouse are named the same and are contained in different users' schemas. For example:
 

• User1 - login ID is ZHA7JUN26U1 and schema contains a table called INVENTORY.
• User2 - login ID is ZHA7JUN26U2 schema contains a table called INVENTORY.

• ZHA7JUN26U1 is the user login defined in the database instance for the project.
• ZHA7JUN26U1 has other tables (which are not called INVENTORY) which are necessary for the project.
• The requirement is to create a warehouse partition table whose base tables are the above mentioned INVENTORY tables contained in different user's schemas.

CAUSE:
The above described behavior is because of a defect in the way the Warehouse Catalog read is designed to work. The Warehouse Catalog does not remove the base tables from the left pane and automatically add them to the project as partitions and instead of displaying the number of partitions as 2, it displays the number of partitions as 1.
 
Even though the partition mapping table does not recognize all the base tables, the metadata registers the correct base tables and can retrieve correct data from the appropriate tables.
 
The Inventory table in this example contains End on Hand and Beginning on Hand inventory data. If a report is created with the attributes - Quarter and Month and with metric - EoH, the report SQL shows that data is retrieved from the appropriate base tables - ZHA7JUN26U1.INVENTORY and ZHA7JUN26U2.INVENTORY.
 

 
ACTION:
Follow the steps below to resolve this issue:
 

• Create the Partition Mapping Table. Suppose that the INVENTORY table in each user's schema contains inventory data for each quarter. The partition mapping table is created in User1's schema and should be defined as follows:
   
   
  

  KFKProducerError.log DSSErrors.log FailedSentOutMessages (folder) (Intelligence Server machine) 2. Statistics and metadata object updates process and inserted into warehousePlatform Analytics Consumerplatform-analytics-consumer.log platform-analytics-consumer.err platform-analytics-health-check.out3. Platform Analytics cube refreshed with updated dataIntelligence ServerDSSErrors.logQUARTER_IDPBTNAME20051ZHA7JUN26U1.INVENTORY20052ZHA7JUN26U2.INVENTORY	KFKProducerError.log DSSErrors.log FailedSentOutMessages (folder) (Intelligence Server machine) 2. Statistics and metadata object updates process and inserted into warehousePlatform Analytics Consumerplatform-analytics-consumer.log platform-analytics-consumer.err platform-analytics-health-check.out3. Platform Analytics cube refreshed with updated dataIntelligence ServerDSSErrors.logQUARTER_IDPBTNAME20051ZHA7JUN26U1.INVENTORY20052ZHA7JUN26U2.INVENTORY
  KFKProducerError.log DSSErrors.log FailedSentOutMessages (folder) (Intelligence Server machine) 2. Statistics and metadata object updates process and inserted into warehousePlatform Analytics Consumerplatform-analytics-consumer.log platform-analytics-consumer.err platform-analytics-health-check.out3. Platform Analytics cube refreshed with updated dataIntelligence ServerDSSErrors.logQUARTER_IDPBTNAME20051ZHA7JUN26U1.INVENTORY20052ZHA7JUN26U2.INVENTORY	KFKProducerError.log DSSErrors.log FailedSentOutMessages (folder) (Intelligence Server machine) 2. Statistics and metadata object updates process and inserted into warehousePlatform Analytics Consumerplatform-analytics-consumer.log platform-analytics-consumer.err platform-analytics-health-check.out3. Platform Analytics cube refreshed with updated dataIntelligence ServerDSSErrors.logQUARTER_IDPBTNAME20051ZHA7JUN26U1.INVENTORY20052ZHA7JUN26U2.INVENTORY
  KFKProducerError.log DSSErrors.log FailedSentOutMessages (folder) (Intelligence Server machine) 2. Statistics and metadata object updates process and inserted into warehousePlatform Analytics Consumerplatform-analytics-consumer.log platform-analytics-consumer.err platform-analytics-health-check.out3. Platform Analytics cube refreshed with updated dataIntelligence ServerDSSErrors.logQUARTER_IDPBTNAME20051ZHA7JUN26U1.INVENTORY20052ZHA7JUN26U2.INVENTORY	KFKProducerError.log DSSErrors.log FailedSentOutMessages (folder) (Intelligence Server machine) 2. Statistics and metadata object updates process and inserted into warehousePlatform Analytics Consumerplatform-analytics-consumer.log platform-analytics-consumer.err platform-analytics-health-check.out3. Platform Analytics cube refreshed with updated dataIntelligence ServerDSSErrors.logQUARTER_IDPBTNAME20051ZHA7JUN26U1.INVENTORY20052ZHA7JUN26U2.INVENTORY

  
  The partition mapping table can also be created in any user's schema, but the warehouse catalog Read settings have to be modified accordingly.
   
  
• Modify the Warehouse Catalog Read settings. The Warehouse Catalog 'Read Settings' should be modified by accessing the 'Settings' link available under Tools > Options > Warehouse Catalog > Read Settings:
  

•  
  For the schema described in the background section, the warehouse catalog read settings should be modified to the following:
   
  The Table SQL (top pane) should be modified to the following:
  SELECT DISTINCT OWNER NAME_SPACE, OWNER '.' TABLE_NAME TAB_NAME
  FROM ALL_TAB_COLUMNS
  WHERE OWNER IN (' ZHA7JUN26U2', ' ZHA7JUN26U1') and table_name like 'INVEN%'
  union all
  SELECT DISTINCT OWNER NAME_SPACE, TABLE_NAME TAB_NAME
  FROM ALL_TAB_COLUMNS
  WHERE OWNER IN ('ZHA7JUN26U2', 'ZHA7JUN26U1') and table_name NOT like 'INVEN%'
  
• The columns SQL (bottom pane) should be modified to the following:
  SELECT OWNER NAME_SPACE, TABLE_NAME TAB_NAME, COLUMN_NAME COL_NAME, DATA_TYPE DATA_TYPE, DATA_LENGTH DATA_LEN, DATA_PRECISION DATA_PREC, DATA_SCALE DATA_SCALE
  FROM ALL_TAB_COLUMNS
  WHERE TABLE_NAME in (#TABLE_LIST#)OR OWNER '.' TABLE_NAME in (#TABLE_LIST#) ORDER BY 1, 2
  
• Note: The SQL required will vary depending on the schema and has to be tailored for a specific schema and warehouse table setup. The above SQL was written for Oracle 10i WH.
   
  
• After replacing the default SQL with the modified SQL, select OK and again select OK in the warehouse catalog options window.
• With the modified SQL, the base tables and the Partition Mapping table are correctly read into the Warehouse Catalog, as shown below:
  

• Add the Partition Mapping Table to the project: Add the PMT to the project and the partition mapping table is seen in the project as PMT_INVENTORY(1) instead of PMT_INVENTORY(2) as it should, because the PMT table contains two partitions, as shown below: