Solved

asset transformation

  • 18 January 2024
  • 9 replies
  • 100 views

Userlevel 1
Badge

I am doing asset transformation 

asset
Source Target
key  
categoryId  
sourceDb source
parentExternalId parentExternalId
updatedDate  
createdDate  
externalId externalId
isCriticalLine  
description description
tag  
areaId  
isActive  

I don't have all the columns for mapping to target schema

These are the rest target columns what to do if the mapping column is not there? and where should i add the rest additional columns from source?
 

parentId
metadata
name
dataSetId
labels
icon

Best answer by Andrian Gasper 18 January 2024, 11:43

View original

9 replies

Userlevel 1
Badge +2

Hi. 
The only required field for target schema is “name”. Which you can set it as “tag” for example, or “externalId” as well, if it is a good asset identifier (i.e. asset title). 

Other fields in target schema are not mandatory. However, I would recommend you to create a Dataset first and assign the assets to that dataSetId. See this documentation for why use datasets.

Other source fields I would include as “metadata” ({“areaId”:value,”isActive”:value, “updatedDate”:value     
“createdDate”:value}.
As for “categoryId”, if you can find names for each category, I would create labels, (see documentation) so you can filter, group, list, etc. them. If not, you could also include them as metadata.

Does that help? :) 

Userlevel 1
Badge

Hey Andrian Gasper,
Thank you for the detailed explanation! Your guidance is incredibly helpful.

I have another doubt

I have these files i added the assets file to asset transformation where should I add the rest files in which resource type?
like asset2children,timeseries2assets,workitem2assets these files
 


 

Userlevel 1
Badge +2

Without knowing the contents of the files my suggestion would be the following:

  • You could add “workorders” as “events” in CDF. Then you can use “workorders2assets” to connect them to assets via the “assetIds” field in “events”. Alternatively you can use “relationships” (see documentation).
  • For “asset2children” you can use it to create the asset hierarchy (see structure an asset hierarchy). If it doesn’t fit, then you can use “relationships” as well. 
  • For “timeseries” and “timeseries2assets”, similar to “events”, you can connect them to “assets” via “assetId” field in “timeseries”. (timeseries). 
  • Use similar approach for “workitems2assets” based on the data in the file. Try map “workitems” to a CDF resource (my guess would be “events”), then use the data from “workitems2assets” to map them to assets via the “assetId(s)” field or “relationships”. 
Userlevel 1
Badge

@Andrian Gasper Thank You so much Your guidance is helpful. I will try if I face any issues I will get back to you.

Userlevel 1
Badge

@Andrian Gasper Hi

I have done the transformation for assets as per your input but i am getting this error

Request with id 608afd10-0683-9791-9d1-29938ac to https://az-eastus-1.cognitedata.com/api/v1/projects/a-cf-sandbox/assets failed with status 400: Reference to unknown parent with externalId WMT:23-1ST STAGE COMP LUBE OIL SYS-PH.

Data in CSV
 

key categoryId sourceDb parentExternalId updatedDate createdDate externalId isCriticalLine description tag areaId isActive
WMT:23-1ST STAGE COMP LUBE OIL SYS-PH 1157 workmate WMT:23-XX-9106 11-12-2013 14:35 13-12-2012 13:02 WMT:23-1ST STAGE COMP LUBE OIL SYS-PH FALSE 1ST STAGE COMP LUBE OIL SYS ON PH 23-1ST STAGE COMP LUBE OIL SYS-PH 1004 TRUE
Userlevel 1
Badge +2

Hi @Sumit Bondlewad 
This error indicated that the parent asset externalId you are referencing to does not exist.
To solve you can:

  1. Either make sure that the parent asset exists in CDF with such externalId
  2. Or in your SQL, include a if/else clause, to check if the parent asset exists before assigning the parent externalId. 
Userlevel 1
Badge

Hi @Andrian Gasper  ​​​​
Sorry to disturb you but I need your small help

I am still confused I have these above CSV and I stored all CSV in a staging area in CDF but in transformation, I am not getting which file will go in which resource type like time series, Datapoints, assets, assets hierarchy, events Can you tell me which file I have to store in the resource type that would be helpful.

 

Userlevel 1
Badge +2

Hi Sumit.

So:
assets → Create Update Assets
asset2children → Create Update Asset hierarchy
workorder → Create Events
workorder2asset → Update Events (connect them to assets) 
workorder2items → not sure, maybe Relationships
workitems → Create Events
workitem2asset → Update Events (connect them to assets)  
timeseries2asset → Update Timeseries (connect them to assets)
timeseries → Create Timeseries
dataPoints → Create Data Points (connect them to timeseries)

Userlevel 1
Badge

Hi @Andrian Gasper 
I wanted to take a moment to express my gratitude for your valuable input and assistance. Your time and expertise are greatly appreciated. Thank you for your thoughtful contributions.

Reply