Scenario:
We have a Subscriber Table contains Column "Subscriber_Note" Containing Info related Subscriber.

Let say this column has "Name" with "DOB" and "Purchase date" in format (Name:Harry DOB:10081987 PurchaseDate:11012015 ExpirationDate: 11012016 PurchaseDate:11012014 ExpirationDate: 11012015)

And our BA provider us free text rules in excel to fetch data from this column and migrate it to destination table.

Say we need to migrate this data into table "T1" which has column "purchase_date" and BA provided the Free text Rule " First purchasedate should be filled in column".

Method Implemented So Far:
These rules provided in excel were change in native VB Code and were process further.

Requirement:
Client want to have some interface to Convert/Use these Formula Given in Excel Sheet in Native SQL/Native interface that can be use further for integration (Need to no exclude lagging in this coding of the logic).
They have Heard that there's a privilege to store formula in METADATA and process it further with tool like Pentaho.


Analysis so Far:
Search something called "Pentaho Metadata Editor" which seems to me basis for designing metadata for Cube and to be used further for reporting purpose and which provides some calculations(Seems to me like SSAS in MSBI).

Question:
  • Is "Pentaho Metadata Editor" the one related to my requirement and if so how is PDI related to PME or how these rules stored in PME can be used for integration data into table?
  • Is there any layer/interface above PDI that will take this free text formula and process it in its native language.