3 Replies Latest reply on Jun 9, 2020 3:56 PM by Ben Davies

    Is there a table that details how derived system statistics are calculated (ex. SUM, AVG, A/A, A/P)?

    Michael Kramer
      Share This:

      I'm looking to validate the way derived systems were created in my environment vs the thresholds in their respective models. While this can be done through manual effort by comparing the derived system data vs the data from the individual systems I need to be able to do this multiple systems.

        • 1. Re: Is there a table that details how derived system statistics are calculated (ex. SUM, AVG, A/A, A/P)?
          Andrea Gallo

          Hello Michael,

          When you create a Derived System, you are actually creating a new hierarchy rule that will derive metrics for the target system.

          Details are stored in different tables, and you need to create a SQL query to achieve your goal.

           

          You can use the following as a good starting point (first section is for System, second section - after UNION ALL - is for Business Driver)

           

          /*SYSTEM*/

          select 'SYS' as type, s.sysid as entid, s.name, aggregation

          from (

          select hierruleid, value as sysid

          from hier_rule_props prop

          where prop.name like '%targetid.equals%') sys

          join (

          select hierruleid, aggr.description as aggregation

          from hier_rule_props prop

          join child_aggr_type aggr on aggr.childaggrtypeid = prop.value

          where prop.name like '%target.childaggrtypeid%') aggr using (hierruleid)

          join pv_system s on sys.sysid = s.sysid

          /*WORKLOAD*/

          union all

          select 'WKLD' as type, w.wkldid, w.name, aggregation

          from (

          select hierruleid, value as sysid

          from hier_rule_props prop

          where prop.name like '%targetid.equals%') sys

          join (

          select hierruleid, aggr.description as aggregation

          from hier_rule_props prop

          join child_aggr_type aggr on aggr.childaggrtypeid = prop.value

          where prop.name like '%target.childaggrtypeid%') aggr using (hierruleid)

          join pv_workload w on sys.sysid = w.wkldid;

           

          You need to run this query with full visibility on all the tables in the schema.

           

          Thanks!

          Best Regards

          Andrea

          • 3. Re: Is there a table that details how derived system statistics are calculated (ex. SUM, AVG, A/A, A/P)?
            Ben Davies

            Queries like this are useful to know, but difficult to find a good place for.   I personally make a "TSCO Facts" view on the presentation service for just this sort of query.  Note the time filter of 'TSCO time' which is 1970-01-01 to 9999-12-31

             

            As the query is not going against published database view tables, it is not supported (meaning it may break if (when) the underlying data tables change), but these are definitely worth the effort to make into a TSCO View.