Solved

migrating business logic from excel macros to CDF

  • 9 May 2023
  • 5 replies
  • 51 views

Badge +2

Hi, 

There are raw data files which are used to perform computations (yield tracking measures ) that uses tons of input data attributes to compute the metrics. Currently, VBA macros (excel) is used to perform the calculations and publishing the outputs. WE have to transfer the computation within CDF. Can we use cognite functions in this regard?. 

Is there a way to create python script that can perform these calculations within the CDF. If not, should we create some external cloud based construct like Azure functions or Lambda routines?

Please share some examples are snippets that can help to perform this project task.

icon

Best answer by roman.chesnokov 9 May 2023, 18:09

View original

5 replies

Badge +2
Illustration for implementing the Macros computation within CDF

 

Userlevel 3

Hey, thanks for reaching out, 

You can definitely use Cognite Functions for that. But please keep in mind the limitations.

It makes sense to ingest input data as RAW table/tables, and then you can use them from the CDF functions. For example, get it from CDF as a pandas data frame.

Here is the doc about creating a CDF function from a Python script. If you have any questions or need further clarification, please don't hesitate to let me know.

Badge +2

Thanks @roman.chesnokov for the inputs. I also see that the excel has lot of formulas in several columns in the workbook (with around 30+ sheets). I am wondering how to extract all of these individual computation in each of those columns and then use it as cognite-functions. 

Maybe, I am thinking if I should try setting up RAW tables and then use extended derived columns to make these computations as a part of transformations.

Userlevel 3

@eashwar11 the closest that you probably could get to replicate Excel in Python is pandas. But of course, you can also make your calculations using SQL in CDF Transformations, there you can use a 'with' clause for example to make temporary subqueries if you need them.

Badge +2

Thanks for the inputs. 

Reply