ALERT! The community will be read-only on April 19, 8am Pacific as the migration begins. Read more for important details.
ALERT! The community will be read-only on April 19, 8am Pacific as the migration begins.Read more for important details.
Commodore Commodore
Commodore
198 views

Operation reporting

Good day,

 

I've recently installed operational reporting on our 9.3 environment.I'm new to operational reporting and i'd like to know the following.

 

1.In the operational reporting schema the table rpt_dim_requests would contain all the requests found in the kcrt_requests table?So the table rpt_dim_requests would be updated every night with all the changes and new requests created in the kcrt_requests table?

 

I'm unable to find many of the requests in the rpt_dim_requests table even though the kcrt_requests table contains them.

 

Regards,

Patrick.

0 Likes
3 Replies
Absent Member.. Absent Member..
Absent Member..

Hey Patrick,

 

A similar case was reported some time ago, the BOE ETL job was not copying all the information from PPM database to BOE database.

 

Please execute the following SQL in BO schema to bring all requests to RPT_DIM_REQUESTS

 

declare
l_file_name CONSTANT VARCHAR2(50) := 'load_common_data.sql';
l_module_name CONSTANT VARCHAR2(50) := 'main';
l_func_name CONSTANT VARCHAR2(50) := 'main';
reqdim_start_year_boundry DATE;
begin
     reqdim_start_year_boundry := RPT_UTIL.GET_START_BOUNDRY(RPT_CONSTANTS.PRT_PARAM_REQDIM_START_DATE);
     -- truncate the table first
    execute IMMEDIATE 'truncate table RPT_DIM_REQUESTS';
    -- invoke request dimension population script
    RPT_POPULATE_DIMENSION.RPT_POPULATE_DIM_REQUEST(reqdim_start_year_boundry);
    DBMS_OUTPUT.PUT_LINE('completed populating the Request Dimension table');
    rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name,$$plsql_line,  'completed populating the Request Dimension table');

    DBMS_OUTPUT.PUT_LINE('Gathering statistics for the Request Dimension table '|| '  ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
    rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name, $$plsql_line,  'Gathering statistics for the Request Dimension table');
    DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER,tabname=>'RPT_DIM_REQUESTS', force=>TRUE);
    DBMS_OUTPUT.PUT_LINE('Finshed gathering statistics for the Request Dimension table '|| '  ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
    rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name, $$plsql_line,  'Finished gathering statistics for the Request Dimension table');

    -- Req custom Params Dim Tables : truncate the table first
    -- Req Dimention be MUST be popualted before populating following Custom Parameters for Req Header and Req Details Custom parameters.
    execute IMMEDIATE 'truncate table RPT_DIM_REQ_HDR_CUSTOM_PARAMS';
    execute IMMEDIATE 'truncate table RPT_DIM_REQ_DTL_CUSTOM_PARAMS';

    RPT_POPULATE_DIMENSION.RPT_POPULATE_REQ_CUSTOM_PARAMS('H');
    rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name,$$plsql_line,  'Completed populating Req Header Custom Parameters Dimension table');

    DBMS_OUTPUT.PUT_LINE('Gathering statistics for the Req Header Custom Parameters Dimension table '|| '  ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
    rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name, $$plsql_line,  'Gathering statistics for the Req Header Custom Parameters Dimension table');
    DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER,tabname=>'RPT_DIM_REQ_HDR_CUSTOM_PARAMS', force=>TRUE);
    DBMS_OUTPUT.PUT_LINE('Finshed gathering statistics for the Req Header Custom Parameters Dimension table '|| '  ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
    rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name, $$plsql_line,  'Finished gathering statistics for the Req Header Custom Parameters Dimension table');

    RPT_POPULATE_DIMENSION.RPT_POPULATE_REQ_CUSTOM_PARAMS('D');
    rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name,$$plsql_line,  'Completed populating Req Details Custom Parameters Dimension table');

    DBMS_OUTPUT.PUT_LINE('Gathering statistics for the Req Details Custom Parameters Dimension table '|| '  ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
    rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name, $$plsql_line,  'Gathering statistics for the Req Details Custom Parameters Dimension table');
    DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER,tabname=>'RPT_DIM_REQ_DTL_CUSTOM_PARAMS', force=>TRUE);
    DBMS_OUTPUT.PUT_LINE('Finshed gathering statistics for the Req Details Custom Parameters Dimension table '|| '  ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
    rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name, $$plsql_line,  'Finished gathering statistics for the Req Details Custom Parameters Dimension table');
end;

 

 

Alonso

0 Likes
Commodore Commodore
Commodore

Hi Alonso, 

 

Thanks for the response.I've run  the sql by copying the SQL you've provided and running it directly in the reporting schema.The SQl runs as seen in the screenshot.

 

I still don't think this solves the problem.When i run select count(*) from kcrt_requests i find 38875 but when i run select count(*) from rpt_dim_requests k i find 11015 meaning lots of requests have not been copied over.

 

Where else do i need to check?

 

Regards,

Patrick.

0 Likes
Absent Member.. Absent Member..
Absent Member..

Hi Patrick,

 

Let's run the ETL again, to check if the data was refreshed.

 

Regards,

Alonso

 

 

 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.