Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only

  • 1.  Precompute data for BIRT reports?

    Posted Wed November 18, 2020 05:02 PM
    Edited by System Admin Wed March 22, 2023 11:45 AM
    For BIRT reports that are properly designed, but are still slow, do we have options for precomputing the data/queries for faster reporting?
    (and for reducing the load on the system/network)

    Examples:

    1. Create a  materialized view.
    2. Setup a nightly process that loads the data into a static copy-table.
      • Possibly using OOB Maximo functionality -- create a table in Maximo and delete/replace the records via an escalation.
    3. Run the report on a schedule during off-peak hours and email it to users as a PDF (or email a hyperlink to a network drive, etc.).
    4. Create/populate persistent fields in the database table instead of calculating fields in the BIRT query.
    5. Other
    Anyone have any ideas or experience with this sort of thing?

    Thanks.
    #AssetandFacilitiesManagement
    #Maximo


  • 2.  RE: Precompute data for BIRT reports?

    Posted Thu November 19, 2020 08:39 AM

    C is the out of the box method. On a given report you can mark it as "Schedule Only" and can even configure when it's allowed to run. All of this is done in Report Administration on the Performance tab. 

    I would avoid D. It depends a bit on what you're calculating and whether or not it's on the object directly or depends on data in child objects, but if it's not on the object directly (actual materials on a WO for example), it can be a pain to ensure the data that has been calculated is correct and the last thing you want is someone running a report to get the wrong data. 

    B could work, but has some similar problems to D. What happens if your process doesn't run successfully and shows the wrong data from a previous day or is missing data? 

    A is an Oracle specific thing and I don't know much about it. 



    ------------------------------
    Steven Shull
    Director of Development
    Projetech Inc
    Cincinnati OH
    ------------------------------

    Original Message


  • 3.  RE: Precompute data for BIRT reports?

    Posted Sun February 14, 2021 01:24 PM
    Edited by System Admin Wed March 22, 2023 11:50 AM
    I have alternative idea here: Report based on materialized view: Deliberately make the report fail if MV is out of date?


    This WHERE clause makes it so the report will be blank if the materialized view is out-of-date.
    In other words, we'd rather have no data in the report, than wrong data.

    select
    *
from
    wo_mv
where
    exists (select 1
            from all_mviews
            where mview_name = 'WO_MV'
                  and trunc(last_refresh_date) = trunc(sysdate) 
           )

    We'd notice that the report is empty and investigate what's wrong with the materialized view. That would eliminate the risk of the report being out of date, but still let us keep the benefits of the MV.

    #AssetandFacilitiesManagement
    #Maximo


  • 4.  RE: Precompute data for BIRT reports?

    Posted Thu November 19, 2020 09:35 AM
    Oftentimes BIRT reports are slow because the queries are inefficient.  Of course it does not help that Maximo is a Non-Normalized database in some cases.  In others it does.  As for creating any kind of views (materialized or otherwise) that is certainly and option.  I have had clients over the years, (going all the way back to SQR days!) create stored procedures, views, extra tables, etc. just to improve the efficient of the report.  In general if you can safely, efficiently and economically do the "heavy lifting" of the report in your DB then do so.  This is one of the reasons why IBM provides the ability to have your BIRT Report Only Server (BROS).  

    If you have serious performance consideration surrounding your reports and you have a) optimized your queries, b) set up your BROS, c) scheduled reports for off-hours processing, then you have done everything correctly so far.  If the next step is to take the business logic of the report, and create a stored procedure and load extra tables for that purpose, then redesign the report to just query the table created/ managed by the stored procedure, I would recommend doing that. I have had many clients do that over the years;  but..... I would only do that if the expense of the report is so heavy on performance that you are forced to do that as a last step.

    ------------------------------
    Bradley K. Downing , MBA
    IBM Certified Adv. Deployment Prof. Maximo v7.6.1
    IBM
    Bakersfield CA
    ------------------------------

    Original Message


  • 5.  RE: Precompute data for BIRT reports?

    Posted Thu July 15, 2021 02:47 PM
    Edited by System Admin Wed March 22, 2023 11:51 AM

    Function-based indexes might also be an option:

    RFE: Add support for Function Based Indexes (FBI) to Maximo DB Configuration
    https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=44977

    It sounds like IBM rejected the RFE to support FBIs. But I don't think that would prevent us from creating FBIs outside of Maximo.
    @Steven Shull or @Mark Robbins might know more about that.

    Steps (from RFE):
    1. standard indexes need to be created in DB Cfg
    2. standard index is dropped
    3. FBI index is created
    4. Maximo is restarted.


    #AssetandFacilitiesManagement
    #Maximo


  • 6.  RE: Precompute data for BIRT reports?

    Posted Thu July 15, 2021 07:05 PM
    There are several points that need to be considered.
    As usual I agree with Steven's views.
    I partially agree with Bradley's comment about using the database to do the heavy lifting.

    It is important to understand the value of a separate reporting database.
    A seperate reporting database can deliver a number of benefits including:
    - No impact on production database when a report retrieves 7 years of workorder data - Yep I have seen that
    - Ability to use PL/SQL to generate summary tables etc. IBM Support have previously advised against manipulating data at the SQL level because it can cause other problems - I have seen Workorder saves fail because of deadlocks created by PL/SQL code running in the database
    - Ability to manually create/manage indexes without risking Maximo drop the indexes when it rebuilds the tables - As my RFE says this is a major pain.
    It isn't a particularly hard job to create an additional data source pointing at the reporting database. some thought is required if the replication is not immediate.

    Individual SQL statements need to be optimised and this step is often overlooked.
    I talk about the importance of that in the "Database performance & DBA tips mini-series" in my blog (which I know you read)

    Re your query about detecting if the materialised view is out of date.

    select 1
    from all_mviews
    where mview_name = 'WO_MV'
    and trunc(last_refresh_date) = trunc(sysdate)

    Your query is not taking advantage of the power of BIRT.
    An alternative approach would be:
    Have two datasets
    dataset 1 - compare the last_refresh_date against sysdate. Fetch method sets a "mvDataIsOutOfDate" flag to indicate if the MV is up to date.
    dataset 2 - execute the query to retrieve the data.
    controls used to show data from dataset2 - these have an expression that controls the visibility e.g if mvDataIsOutOfDate = false
    If the mvDataIsOutOfDate flag is true then the crontrols will be hidden and the query in dataset2 will not be executed

    Re my RFE about adding support for Function Based Indexes above - Periodically I talk to Dev and I will see what the latest on this was.
    I know we were working on a solution that would allow us to automatically recreate FBI indexes after they had been dropped.


    ------------------------------
    Mark Robbins
    Support Lead/Technical Design Authority / IBM Champion 2017 & 2018 & 2019 & 2020 & 2021
    Vetasi Limited
    https://www.linkedin.com/pulse/maximo-support-advice-from-non-ibm-engineer-article-mark-robbins/
    ------------------------------

    Original Message