Data modeling transformations

Hello, 

I do not fully understand what you are trying to achieve. 

With your query above, you are listing the cdf timeseries you have in your project. 

Can you clarify what you would like to do? Do you want to link the timeseries to your assets?  

Hi,

So with my query i am only getting metadata of my timeseries and no values of the actual tag. i have a bucnh of timeseries data that i want to transform into my data model ok, but how do i query name, description and value of the data point during transformation? if i loo at my transformation sql editor there is no value to query only externalid, type, name, description and machinetype? 

I would not recommend to use transformations to read or manipulate datapoints. 

If you need to read datapoints for 1 specific timeseries, you can use the below query. It needs be done for 1 specific timeseries externalID. That will retrieve value and timestamp for a specific timeseries. 

 select
  timestamp,value
from
  `_cdf`.`datapoints` where externalId="pi:160627";

I am however, still uncertain of the use case of doing so :) 

ok, maybe i have misunderstood how it works with cdf data models but this is what i did.

i have connect mqtt broker to cdf and extracted all topics into one dataset which holds all topics/timeseries data points. now i want to create a datamodel and knowledge graph out of this data and create asset hiararcy, models of machines, lines etc so i was trying to populate my data model with all of this timeseries data that i have. is this the right approach? is there any indepth data modeling course that i can take? 

Thank you very much for supporting me:)

ok thanks for clarifying. 

Your time series object will act as a container for your datapoints, you would not need to worry about your datapoints (value, timestamp) as they will be linked to other resources through the time series. 

You can populate your model with your timeseries (the metadata part: space, external_id, name, description) and the datapoints will follow as they are linked to your timeseries instance id (externalid + space). But I believe you are already done that if you have created the timeseries through the MQTT broker? 

The next step would be to create an asset hierarchy https://docs.cognite.com/cdf/dm/dm_guides/dm_cdm_build_asset_hierarchy and then to link the timeseries to the assets (which you would do in a transformation indeed)

ok great i didnt knew that data points will be linked too but i am a bit confused now.

i have built my custom data model and different containers, node, relations etc and to me this is building asset hierarchy since in my broker i have isa 95 structure so how does asset hierarchy affect my data model?

should you always start with cognite core data model?

previously i have linked my timeseries to my asset hierarchy (in other project) but now i have a data model where i linked timeseries to it and to different nodes so why cant i have hierarchy in my model? 

As you can see i maybe havent grasped the concept yet haha:)

ok great, so if you have your asset hierarchy and your timeseries, the only thing you would need to do now is to link them together, ie contextualize the timeseries with the assets, you can do that in transformations through direct relations

yeah i know and then i will have dataset with assets and timeseries and i get that. so then i should populate that contextualized dataset with my data model right? i am trying to understand the connections or context between data models and contextualized datasets if you understand what i mean?

dataset is not really a concept in data modeling, it is mostly used for the (legacy) classic asset hierarchy. Datasets are only used in data modeling to store among others extraction pipelines, transformations, functions

Hi Gaetan, 

So i have came along a litlle further now with your help but i douth that mine timeseries values have been transformed into my data model like you said. i did the transformation from dataset into my data model and tried to query for values just to test and  i get null.  This mine sql query:

SELECT
    CAST(SUBSTRING_INDEX(`externalId`, '/', -1) AS STRING) AS externalId,
    CAST(`externalId` AS STRING) AS name,
    CAST(`description` AS STRING) AS description
FROM
    `_cdf`.`timeseries`
WHERE
    `externalId` LIKE 'AcmeManufacturing/PlantA/ProductionFloor/Line1/MachineA/%'
       AND `dataSetId` = 6070405924869548;

data model: 

type Machine {

  name: String!

  description: String

  machineType: String

  Infeed: TimeSeries

  Outfeed: TimeSeries

  Waste: TimeSeries

  State: TimeSeries

  line: Line @relation(type: {space : "ADN_SPACE", externalId : "Line.machines"}, direction: INWARDS)

}

so when i try to test i get no reuslts so probably i only transformed metadata and no actual timeseries data.

query GetMachineTimeseriesData {
  listMachine {
    items {
      Infeed {
        getLatestDataPoint {
          items {
            value
            timestamp
          }
        }
      }
    }
  }
}

I would recommend using Search to view the data, the SQL query you wrote is only valid for (legacy) CDF time series so will not return anything. 

Do you see your time series in Search? (you might need to edit the location/data model selection)

so what is the differenc between legacy cdf timeseries and non legacy?

yes, i can see all mine data points using seach but why cant i get those using graphql query?

Legacy time series are time series created and updated/maintained using the Time Series API. They are typically identified using an (optional, but recommended) external ID.

Non-legacy are time series created initially in data modeling as an instance of a derived CogniteTimeSeries Core Data Model type.

These can be “seen” and read (partially) using the “legacy” Time Series API by using the instance ID - instanceId -  (which maps to the space ID and mandatory External ID of the CogniteTimeSeries instance in the data modeling service.

We’re working on tooling to support migrating a specific Time Series from the legacy Time Series API to become a CogniteTimeSeries instance in Data Modeling.

For either model, the actual data point CRUD and querying is done using the Time Series API endpoints for data points.

Hi,

this is great and thank you for the explanation:)

the way i understood i have non legacy timeseries that i have linked to my data model but with the above sql query i have only transformed meta data and not data points which is why i cannot get any values when i use graphql explore. So using below sql query how can transform externalid, name and value into my data model: 

SELECT
    CAST(SUBSTRING_INDEX(`externalId`, '/', -1) AS STRING) AS externalId,
    CAST(`externalId` AS STRING) AS name,
    CAST(`description` AS STRING) AS description
FROM
    `_cdf`.`timeseries`
WHERE
    `externalId` LIKE 'AcmeManufacturing/PlantA/ProductionFloor/Line1/MachineA/%'
       AND `dataSetId` = 6070405924869548;

in my dataset where i have stored all my topics as timeseries i also have asset hierachy of the actual plant so i should be able to link timeseries data and my asset hierachy to my data model instances/containers which are factory-line1-machine1-mes data-erp data etc..