AF
Ash Framework•5w ago
smelly

Calculation Heavy Resource with Historian Retrieval

Hello, was needing some help on a difficult problem. I have a calculation heavy resource (100+ calculations) that we would like to grab data out of our 3rd party historian sensor tables and perform calculations on as well as filter the results of certain calculations for metrics purposes. For example we would want to get the temperature over the course of a run and perform the average over the whole run. One caveat is that the sqlth_data tables are created weekly and have dynamic names for the tables that are stored in the partition tables and due to the historian data from tables require complex time-based filtering and dynamic table names its breaking my brain. I can make this work with calculate but i was hoping to make this work via expr is there something i am missing or could do that would help out my approach? 
  def expression(_opts, _context) do
    expr(
      avg(
        equipment.tag_versions.tag_paths.tags.historical_data,
        field: :floatvalue,
        query: [
          filter: expr(
            tag_paths.tag_template.name == "Temperature" and
            t_stamp >= ^ref(:start_time_resolved) and
            t_stamp <= ^ref(:end_time_resolved) and
            dataintegrity == 192
          )
        ]
      )
    )
  end
end
  def expression(_opts, _context) do
    expr(
      avg(
        equipment.tag_versions.tag_paths.tags.historical_data,
        field: :floatvalue,
        query: [
          filter: expr(
            tag_paths.tag_template.name == "Temperature" and
            t_stamp >= ^ref(:start_time_resolved) and
            t_stamp <= ^ref(:end_time_resolved) and
            dataintegrity == 192
          )
        ]
      )
    )
  end
end
No description
4 Replies
ZachDaniel
ZachDaniel•5w ago
🤔 That can be a bit tough. raw ecto code is always an escape hatch. Another option though is to set the table name with relationship context 
has_many ... do
  context %{data_layer: %{table: "table name"}}
end
has_many ... do
  context %{data_layer: %{table: "table name"}}
end
smelly
smellyOP•5w ago
Then try to set the context in the load on the action? Since it has many if there is data that ranges across partitions it would be handled? Then does a preparartion help this out at all to load the data before hand to be used in the calcualtion expr()? 
partition_table = get_partition_for_time_range(start_date, end_date)
  
Run
  |> Ash.Query.filter(expr(run_id == "some_id"))
  |> Ash.Query.filter(expr(
    exists(equipment.tag_versions.tag_paths,
      tag_template.name == "Temperature"
    )
  ))
  |> Ash.Query.load([
    equipment: [
      tag_versions: [
        tag_paths: TagPath
        |> Ash.Query.filter(expr(tag_template.name == "Temperature"))
        |> Ash.Query.load([
          :tag_template,
          historiantags: Tag
          |> Ash.Query.load([
            historical_data: HistoricalData
            |> Ash.Query.set_context(%{data_layer: %{table: partition_table}})
            |> Ash.Query.filter(expr(
              t_stamp >= ^start_date and
              t_stamp <= ^end_date
            ))
            |> Ash.Query.limit(50)
            |> Ash.Query.sort(:t_stamp)
          ])
        ])
      ]
    ]
  ])
  |> Ash.Query.limit(1)
  |> Ash.read!()
partition_table = get_partition_for_time_range(start_date, end_date)
  
Run
  |> Ash.Query.filter(expr(run_id == "some_id"))
  |> Ash.Query.filter(expr(
    exists(equipment.tag_versions.tag_paths,
      tag_template.name == "Temperature"
    )
  ))
  |> Ash.Query.load([
    equipment: [
      tag_versions: [
        tag_paths: TagPath
        |> Ash.Query.filter(expr(tag_template.name == "Temperature"))
        |> Ash.Query.load([
          :tag_template,
          historiantags: Tag
          |> Ash.Query.load([
            historical_data: HistoricalData
            |> Ash.Query.set_context(%{data_layer: %{table: partition_table}})
            |> Ash.Query.filter(expr(
              t_stamp >= ^start_date and
              t_stamp <= ^end_date
            ))
            |> Ash.Query.limit(50)
            |> Ash.Query.sort(:t_stamp)
          ])
        ])
      ]
    ]
  ])
  |> Ash.Query.limit(1)
  |> Ash.read!()
ZachDaniel
ZachDaniel•5w ago
I think its unlikely that this strategy would work well for you TBH Well... you could potentially use as in the load statement I forget if it works for relationships TBH I think it wouldn't work you'd need to use the builtin calculation module and Ash.Query.calculate but the complexity feels quite high here for what you want, which is effectively a reporting concern, something Ash doesn't really cross over on right now
smelly
smellyOP•5w ago
For sure, that was the feeling i was getting as well after trying many approaches. Thanks for all the feedback!!

Did you find this page helpful?