External types as TimeseriesDataPoint

#1

Hi,

a customer has an external type that looks like this:

entity type CCHActivas mixes External, NoSystemCols schema name 'c3_owner.cch_activas' {
id: ~ schema name 'cd_cups_ext'
cd_cups_ext : PsSipsAnual schema name 'cd_cups_ext'
fecha : Integer schema name 'fecha'
activa1_h1 : Integer schema name 'activa1_h1'
activa1_h2 : Integer schema name 'activa1_h2'
activa1_h3 : Integer schema name 'activa1_h3'
...
}

where fecha represents the date and activa1_h1,…, activa1_h24 represent the value of the time series at hours 1, 2, …, 24. Also note that cd_cups_ext is a reference field to PsSipsAnual, an external type in the same database. The counterpart of this reference field on PsSipsAnual looks like this:

cch: [CCHActivas](cd_cups_ext)

I wonder if you can help me find the most efficient way to build a CCH_PsSipsAnual simple metric, i.e., a metric that returns an hourly timeSeries from the information in CCHActivas.

So far, I have come up with the following mechanisms but all of them have issues:

Method 1

1.- Create an intermediate non-entity type, let’s call it LoadProfile, that mixes TimeseriesDataPoint<PsSipsAnual> and
2.- implement its fetch(spec) method so it fetches from CCHActivas and returns well-formatted TimeseriesDataPoints.
3.- The mixes TimeseriesHeader<LoadProfile> clause would need to be added to PsSipsAnual type.

I have been able to make it work on the LoadProfile part, i.e., I can see the right results when I do: c3Grid(LoadProfile.fetch({filter:"intersects(parent,['ES0021000000000006WA'])"})). Please, note that the result returns items with a parent field. However, I don’t find any items in the data field when I do c3Grid(PsSipsAnual.fetch({include:"id,{data:[]}",limit:10,filter:"intersects(id,['ES0021000000000006WA'])"})).

Do you know why? Do I need to implement the fetchObjStream(spec) method? If so, please, do you have an example of how to populate and return a Stream from within a js server script?

Method 2

1.- Create a calc field in PsSipsAnual type:
loadProfile: [LoadProfile] calc 'asTimeseriesDataPoints(cch)

2.- Remix ExpressionEngineFunction with a new method asTimeseriesDataPoints() as follows:
asTimeseriesDataPoints: function(b:[CCHActivas]):[LoadProfile]

Unfortunately, this doesn’t work as it is not allowed to remix platform types like ExpressionEngineFunction

Method 3

1.- Create and implement a new method within CCHActivas.c3type as follows:
asTimeseriesDataPoints: function(b : [CCHActivas]) : [LoadProfile] js server

2.- Create a SimpleMetric that calls this method:

var loadProfile = SimpleMetric.make({
  "id" : "LoadProfile_PsSipsAnual",
  "name" : "LoadProfile",
  "srcType" : "PsSipsAnual",
  "tsDecl" : {"data":"CCHActivas.asTimeseriesDataPoints(cch)",
    "dataVersion" : "1",
    "treatment" : "RATE",
    "start" : "start",
    "end" : "end",
    "value" : "value"
  }
}); 

But it fails with
"Metric 'LoadProfile' with id 'LoadProfile_PsSipsAnual' has tsDecl.data 'CCHActivas.asTimeseriesDataPoints(cch)' which can't be resolved in header type 'PsSipsAnual'"

How can I call a method that modifies the data from within the metric execution?

#2

I think you can define e metric using tsdecl on PsSipsAnual and use the attribute ”path” to go from PsSipsAnual to CCHActivas data

Something like
path=cch

#3

Thank you Marco.

Your approach looks similar to method 3 above where you suggest to use "data": "cch" instead of "data":"CCHActivas.asTimeseriesDataPoints(cch)". This works, but it returns a set of CCHActivas with one column per hour, which cannot be used to build a time series. That is why I need to apply the .asTimeseriesDatapoints() transform.

#4

Is there a reason that you don’t want to load the CCHActivas data into the platform?

The “C3 Way” here would be to load CCHActivas into a Timeseries or TimedData. Then use a standard simple metric on that type.

Each CCHActivas should transformed into 24 individual timeseries data points with timestamps. fecha+0h, fecha+1h, fecha+2h etc.

#5

The customer would prefer not to replicate this data unless it is absolutely necessary.

#6

Delete it in the other system :slight_smile:

1 Like
#7

But seriously, the right thing to do is to get that type Transformed into a timed data somehow. I’m not sure it actually needs to be LOADED but it needs to be transformed. I’m not sure if its possible to make a type that is a transformation of an external type without loading it.

1 Like
#8

Another approach is to implement an actionDecl metric. Something like:

function actionDeclMetricImpl(obj, tsEvalSpec, metric) {

  var customMeasurements = [];
  var startDate = tsEvalSpec.start;
  var endDate = tsEvalSpec.end;
  var interval = tsEvalSpec.grain;
  var startDate;
  var startDateNum = 0;
  var endDateNum = 0;
  var min = 60;
  var value = 0;
  
  var asTimeseriesSpec = AsTimeseriesSpec.make({
    treatment: "SUM",
    startPath: "start",
    valuePath: "value",
    overlapHandling: "MAX"
  });

  var filter = "servicePoint.id == '" + obj.id + "'";
  var fetchSpec = FetchSpec.make({filter:filter});
  var mpoints = ExternalMeasurement.fetch(fetchSpec);

  if (mpoints.count > 0) {

    var fields = mpoints.objs[0].fields();

    mpoints.objs.forEach(function (row) {
      
      // construct startDate variable from an existing field
      startDate = new Date.parse(row.readingDateRaw.toString(), "yyyyMMdd");
      
      fields.forEach(function (field) {

        if (field.name.startsWith("activa")) {
          startDate = startDate.addMinutes(min);
          value = row[field.name];

          var meas = CustomMeasurement.make({
              start: startDate.clone(),
              value: value,
              servicePoint: {id: obj.id}
          });

          customMeasurements.push(meas);
        }

      });
    });
 
    var objs = ObjArry.make({
      value : customMeasurements
    });

   return SimpleMetric.asTimeseries(objs, tsEvalSpec, asTimeseriesSpec);
 } else {

    var objs = ObjArry.make({
      value : customMeasurements
    });

   return SimpleMetric.asTimeseries(null, tsEvalSpec, asTimeseriesSpec);

 }
}
1 Like
#9

Your simplest option is to create it as a TSDecl metric. I’m not 100% sure if the below approach will work, but worth the try:

If this link leads you to the data points:

entity type PsSipsAnual {
  cch: [CCHActivas](cd_cups_ext)
}

you can have your data point be mixing in TimeseriesDataPoints like:

entity type CCHActivas mixes  TimeseriesDataPoints, External, NoSystemCols ... {
  start: ~ calc "dateTime(fecha)"

  intervalMillis: ~ calc "3600000"

  parent: ~ calc "cd_cups_ext",
 
 @ts
  values: [int] calc "[activa1_h1, activa1_h2, activa1_h3 .....]"
}

then you can have the metric defined on that type as:

SimpleMetric.make({
  "id" : "LoadProfile_PsSipsAnual",
  "name" : "LoadProfile",
  "srcType" : "PsSipsAnual",
  "tsDecl" : {
    "data":"cch",
    "treatment" : "RATE",
    "start" : "start",
    "value" : "values"
  }
}); 
1 Like
#10

@rohit.sureka I tested this approach. When I do

c3Grid(PsSipsAnual.fetch({include:"id,{cch:[fecha,start,intervalMillis, {values:[]}]}",limit:10,filter:"intersects(id,['ES0021000000000006WA'])"}))

I can see the fecha, start and values fields. intervalMillis field is not available. The resulting metric displays only the value of the first hour every day.

It seems to be almost there, only intervalMillis field is missing.

#11

@santiago.lopez can you start by fetching directly the data point type ?

#12

Approach 1 (it works)

Metric definition

{
  "id" : "LoadProfile_PsSipsAnual",
  "name" : "LoadProfile",
  "srcType" : "PsSipsAnual",
  "actionDecl" : {
    "action":"cchAsTS"
  }
}

PsSipsAnual.c3typ definition

entity type PsSipsAnual mixes External, NoSystemCols, MetricEvaluatable, MetricFunctionLibrary schema name 'c3_owner.t_ed_f_ps_sips_anual' {
...
asTSDataPoints: function(b : [CCHActivas]) : [customTSDataPoint] js server
cchAsTS: function(obj: Obj, spec: TSEvalSpec, metric: Metric): Timeseries js server
}

CustomTSDataPoint.c3typ

type CustomTSDataPoint mixes TimeseriesDataPoint<PsSipsAnual>{
  value: 		double
  fetch: function(spec : FetchSpec) : FetchResult<CustomTSDataPoint> js server
}

PsSipsAnual.js

function cchAsTS(obj, tsEvalSpec, metric){

    var customTSDataPoints=[];
    var asTimeseriesSpec = AsTimeseriesSpec.make({
        treatment: "SUM",
        startPath: "start",
        valuePath: "value",
        overlapHandling: "MAX"
      });

    var filter = "cd_cups_ext.id== '"+ obj.id + "'";
    var fetchSpec = FetchSpec.make({filter:filter, limit: -1});
    var b=CCHActivas.fetch(fetchSpec) ;
    if (b.count===0){
        return SimpleMetric.asTimeseries(null,tsEvalSpec, asTimeseriesSpec);
    }

    customTSDataPoints=asTSDataPoints(b.objs);
    var objs = ObjArry.make({
        value: customTSDataPoints
    });

    return SimpleMetric.asTimeseries(objs,tsEvalSpec, asTimeseriesSpec);
}

Approach 2 (it doesn’t work, why?)

However, for better performance, I am also trying an alternative to Rohit’s approach above as follows:

LoadProfileAlt.json

{
  "id" : "LoadProfileAlt_PsSipsAnual",
  "name" : "LoadProfileAlt",
  "srcType" : "PsSipsAnual",
  "actionDecl" : {
    "action":"cchAsTSAlt",
    "include":"id,{cch:[start, values, intervalMillis, parent, outlierCode, comments, isEstimated, dataVersion, statusCode]}"
  }
}

CCHActivas.c3typ

entity type CCHActivas mixes TimeseriesDataPoints, External, NoSystemCols schema name 'c3_owner.cch_activas' {
...
@ts
values: [int] calc "[activa1_h1, activa1_h2, activa1_h3, activa1_h4, activa1_h5, activa1_h6, activa1_h7, activa1_h8, activa1_h9, activa1_h10, activa1_h11, activa1_h12, activa1_h13, activa1_h14, activa1_h15, activa1_h16, activa1_h17, activa1_h18, activa1_h19, activa1_h20, activa1_h21, activa1_h22, activa1_h23, activa1_h24]"
start: ~ calc "dateTime(concat('20',substring(string(fecha),2,9)),'yyyyMMdd')"
intervalMillis: ~ calc "3600*1000"
parent: ~ calc "cd_cups_ext"
outlierCode: ~ calc "0"
comments: ~ calc "clave_cal"
isEstimated: ~ calc "false"
dataVersion: ~ calc "1"
statusCode: ~ calc "''"
}

PsSipsAnual.js

function cchAsTSAlt(obj, tsEvalSpec, metric){

    var asTimeseriesSpec = AsTimeseriesSpec.make({
        treatment: "SUM",
        startPath: "start",
        valuePath: "value",
        overlapHandling: "MAX"
      });
    return Timeseries.fromObjs(ObjArry.fromArry(obj.cch),tsEvalSpec, asTimeseriesSpec);
}

PsSipsAnual.c3typ

...
cchAsTSAlt: function(obj: Obj, spec: TSEvalSpec, metric: Metric): Timeseries js server
...

There are no errors when I execute EvalMetrics but the result is an all-missing timeseries. The same happens when I use Timeseries.fromObjStream(). What am I doing wrong?

#13

@santiago.lopez where is the field value on PsSipsAnual ? I see that the following: valuePath: "value". Must be a typo.

Also, I verified that fromObjs & tsDecl does not have support for dealing with [int] today. The only way to use TimeseriesDataPoints today is by normalizing the series (which I believe you do not want to do`)

The other way to do this would be to have 24 fields on the type one for each hour and having separate metrics for each of them and then eventually stitching together the results of those. (ugly but a workaround)