Maximo

Maximo

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

 View Only
Back to discussions
Expand all | Collapse all

SQL Window Functions: Rollup WO costs (including tasks)

  • 1.  SQL Window Functions: Rollup WO costs (including tasks)

    Posted Sun September 19, 2021 11:22 PM
    Edited by System Admin Wed March 22, 2023 11:49 AM
    MAM 7.6.1.2; Oracle 19c:

    I want to use SQL window functions/analytic functions to query for WO costs including task costs.
    #AssetandFacilitiesManagement
    #Maximo


  • 2.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted Sun September 19, 2021 11:32 PM
    Edited by System Admin Wed March 22, 2023 11:45 AM
    Someone suggested that the approach I took with window functions was incorrect. So I'll put this on hold for now until I can confirm.
    #AssetandFacilitiesManagement
    #Maximo


  • 3.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted Sun September 19, 2021 11:33 PM
    Edited by System Admin Wed March 22, 2023 11:54 AM

    #AssetandFacilitiesManagement
    #Maximo


  • 4.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted Mon September 20, 2021 06:53 PM
    Perhaps the costs are already rolled up on the parent work order?
    If you view the costs in Maximo, it displays the costs for the current work order, and for the work package.
    Select Actions -> View -> Costs.

    ------------------------------
    Scott Taylor
    Specialist Business Systems
    Port Waratah Coal Services
    ------------------------------

    Original Message


  • 5.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted Tue September 21, 2021 09:17 AM
    Edited by System Admin Wed March 22, 2023 11:52 AM
    Thanks for your reply.

    In my case, I'm hoping to access WO costs (including tasks, but not including child WOs) via:
    • Ad-hoc reports
    • Excel application export
    • WOTRACK Advanced Search and list view
    It's my understanding that the View Costs information isn't available as OOB WORKORDER fields (persistent). So I'm trying to find a good way to generate that info as persistent fields in a Maximo object.
    • ACTLABCOSTS_INCL_TASKS
    • ACTMATCOSTS_INCL_TASKS
    • ACTSERVCOSTS_INCL_TASKS
    • ACTTOOLCOSTS_INCL_TASKS
    • ACTTOTALOSTS_INCL_TASKS

    #Maximo
    #AssetandFacilitiesManagement


  • 6.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted Wed September 22, 2021 04:17 PM
    We had a similar need, but for labor hours, where users at one site wanted to display all hours recorded on the work order and tasks on the work order and/or in result sets. Using automation scripts we created an object launch point to sum the hours across the work group and populate that value in a custom field in the workorder object.

    from psdi.mbo import MboConstants;

    site = mbo.getString("SITEID");
    workgroup = mbo.getMboSet("WOGROUP");

    if (site == "siteid"):
    mbo.setValue("X_ACTLABCOSTWG",workgroup.sum("actlabhrs"),MboConstants.NOACCESSCHECK+MboConstants.NOVALIDATION);

    ------------------------------
    John Rice
    ------------------------------

    Original Message


  • 7.  RE: SQL Window Functions: Rollup WO costs (including tasks)
    Best Answer

    Posted Wed September 22, 2021 08:31 PM
    Edited by System Admin Wed March 22, 2023 11:49 AM


    This is what I came up with:


    Records for testing:



    Query:

    I used the SUM window function to get the total WOGROUP costs.
    And wrapped the SUM window query in an outer query that hides the task rows.

    select
    wonum,
    task_count,
    actlabcost_tasks_incl,
    actmatcost_tasks_incl,
    acttoolcost_tasks_incl,
    actservcost_tasks_incl,
    acttotalcost_tasks_incl,
    
    case 
        when acttotalcost_tasks_incl > 0 then 1 
        else 0 
    end as has_positive_actuals,
    case
        when acttotalcost_tasks_incl < 0 then 'NEGATIVE'
        when acttotalcost_tasks_incl = 0 then 'ZERO'
        when acttotalcost_tasks_incl > 0 then 'POSITIVE'
    end as actuals_category,
    
    estlabcost_tasks_incl,
    estmatcost_tasks_incl,
    esttoolcost_tasks_incl,
    estservcost_tasks_incl,
    esttotalcost_tasks_incl,
    siteid
from
(
select
    wogroup as wonum,
    istask,
    count(taskid) over (partition by wogroup) as task_count,

    sum(actlabcost ) over (partition by wogroup) as  actlabcost_tasks_incl,
    sum(actmatcost ) over (partition by wogroup) as  actmatcost_tasks_incl,
    sum(acttoolcost) over (partition by wogroup) as acttoolcost_tasks_incl,
    sum(actservcost) over (partition by wogroup) as actservcost_tasks_incl,
    sum(actlabcost + actmatcost + acttoolcost + actservcost) over (partition by wogroup) as acttotalcost_tasks_incl,

    sum(estlabcost ) over (partition by wogroup) as  estlabcost_tasks_incl,
    sum(estmatcost ) over (partition by wogroup) as  estmatcost_tasks_incl,
    sum(esttoolcost) over (partition by wogroup) as esttoolcost_tasks_incl,
    sum(estservcost) over (partition by wogroup) as estservcost_tasks_incl,
    sum(estlabcost + estmatcost + esttoolcost + estservcost) over (partition by wogroup) as esttotalcost_tasks_incl,
    siteid
from
    workorder
where
    siteid = 'SERVICES'
)
where
    istask = 0



    Result:


    The task costs are rolled up to the WO rows. And the task rows are hidden.


    Summary:

    The benefit of this approach is that it performs well because it only does a single full table scan. It avoids the need for a self-join (via GROUP BY and JOIN). More info here: Group by x, get other fields too (comparing options and performance).
    It's also easy to read.

    (There is another option that's probably just as good. It's a simplified GROUP BY option that takes advantage of the selective aggregates technique.)


    Notes:

    1. The cost columns are not nullable. So we don't need to worry about converting nulls to zeros (to avoid doing math on nulls; 1+null=null).
    2. Yes, I'm aware that I should include SITE and WOCLASS in the WHERE clause.
    3. I found this course on PluralSight to be very helpful for learning about window functions: Advanced SQL Queries in Oracle and SQL Server. That course might be a bit dated, but it was still useful.
      • I was always nervous about using window functions/analytic functions. I didn't understand until recently that window functions are applied last to a resultset. Window functions only add additional information to the resultset as an extra column. They don't add or remove rows to the resultset, which is what I was afraid of.
    4. Here's a related post where I took a different approach (self-join via GROUP BY/JOIN): Select WO actuals and include task actuals?

    #AssetandFacilitiesManagement
    #Maximo


  • 8.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted Thu September 23, 2021 10:17 PM
    Thanks John. Good to know.
    Original Message


  • 9.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted Fri October 22, 2021 10:35 AM
    Related:
    Group BY query utilizes indexes, but window function query doesn't
    Original Message


  • 10.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted Mon October 25, 2021 11:59 AM

    User1971,

    If the real goal here is to roll-up this data to make searching and reporting easier, it rarely needs to be real-time and a huge priority would be finding the solution that has minimal impact to overall  system performance while people are actively trying to work with records.

    I would suggest a much simpler approach. The following sql would give you a new roll-up for any work orders with changes in the past 2 days. (The change date is updated at either the work order or task level even when adding estimates or actuals.)

    This would give you a much smaller set of data with changes that you could push into your extra fields on the work order table, or you could create a new table just for these costs and populate it with this query once a day (after the initial population.)

    select
  ORGID, SITEID, WOGROUP, 
  SUM(ESTLABCOST)  as ESTLABCOST_TI,
  SUM(ESTMATCOST)  as ESTMATCOST_TI,
  SUM(ESTSERVCOST) as ESTSERVCOST_TI,
  SUM(ESTTOOLCOST) as ESTTOOLCOST_TI,
  SUM(ACTLABCOST)  as ACTLABCOST_TI,
  SUM(ACTMATCOST)  as ACTMATCOST_TI,
  SUM(ACTSERVCOST) as ACTSERVCOST_TI,
  SUM(ACTTOOLCOST) as ACTTOOLCOST_TI
from
  WORKORDER
where 
  EXISTS ( SELECT 1 FROM WORKORDER W2 WHERE W2.CHANGEDATE > SYSDATE - 2 AND W2.WOGROUP = WORKORDER.WOGROUP )
group  by
  ORGID, SITEID, WOGROUP


    ------------------------------
    CHRISTOPHER WESTFALL
    ------------------------------

    Original Message


  • 11.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted Tue October 26, 2021 09:24 AM
    @Christopher Westfall
    Thanks. Good points.

    Additionally, I wonder if asynchronous attribute formulas might also be an option worth considering?


    In a ​different post, @Steven Shull talks about how asynchronous automation scripts work: Run asynchronous tasks from automation scripts (new in 7.6.1.2?)

    "This allows you to ensure these background tasks aren't running on the UI JVM. If these processes are resource intensive in WebSphere, it can negatively impact the experience for your other users who were on that JVM/server."

    I know asynchronous automation scripts are not the same thing as asynchronous attribute formulas. But I wonder if that explanation still applies -- and is useful for explaining the benefit of running jobs asynchronously.

    More info here: 


    Also, a while back, @Steve Hauptman and I had brainstormed about this requirement in general. Here are some assorted notes:

    • Performance
    • Update conflict issues (update contention). We need a way to control the event that triggers the formula so it is not constantly firing.
    • How to know which WO to process?
    • Those issues are the nature of your requirement and implementation, not specific to formulas. So, automation scripts and even Java would have issues if they were ran each time task costs were updated. So, we might need to use an alternative - like escalations to kickoff the process, maybe nightly. Of course there would still be the issue of "which WOs changed?".
    • There is also the option of leveraging my view and maybe just use a basic SetValue action in escalation.

    I don't think we found a solution that we really liked...yet.
    ​​​
    Original Message


  • 12.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted Wed October 27, 2021 01:54 AM
    @Christopher Westfall

    Another option would be to use materialized views in Oracle.​ They're pretty easy to set up when they only reference data within the same database. It gets harder/riskier when they involve data in other databases (via dblinks).

    The materialized view could be set up to update on a schedule -- or even update when the underlying tables are updated (live updates).

    Related info here: Precompute data for BIRT reports?
    Original Message


  • 13.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted Wed October 27, 2021 08:19 AM

    User1971,

    I haven't played with formulas much but you can pull related data so it might be possible to do that to populate the value directly using a formula.

    I do like using material views sometimes, but wouldn't use them for your use case due to the following reasons:

    • Refreshing a view refreshes all the data in the view and is a huge consumption of resources. For your use case, the underlying data is changing constantly, so you can't use that as a trigger. Your only real scheduling option would be to schedule the refresh it at a set interval (e.g. - once a night), but still that's a huge waste of system resources and it creates up to a 24 hour lag in value.
    • You could reduce the amount of data in the view (e.g. - last 12 months), but then that makes the use of the view extremely limited


    ------------------------------
    CHRISTOPHER WESTFALL
    ------------------------------

    Original Message


  • 14.  RE: SQL Window Functions: Rollup WO costs (including tasks)

    Posted Wed October 27, 2021 08:49 AM
    You don't need to refresh all the data with a materialized view if you don't want to:

    Materialized View Logs:

    "Since a complete refresh involves truncating the materialized view segment and re-populating it using the related query, it can be quite time consuming and involve a considerable amount of network traffic when performed against a remote table [they're talking about an external db via a dblink here - not applicable in our case]. To reduce the replication costs, materialized view logs can be created to capture all changes to the base table since the last refresh. This information allows a fast refresh, which only needs to apply the changes rather than a complete refresh of the materialized view."
    Original Message