d031182 / modeling-in-sap-data-warehouse-cloud

Modeling Best Practices in SAP Data Warehouse Cloud
0 stars 2 forks source link

Feedback and Comments #1

Open d031182 opened 3 years ago

d031182 commented 3 years ago

Hi Modeling Dudes, I hope the tutorial is useful for you and your work. Please feel free to add a comment in this section, and share what you like and what could be improved to make the tutorial even better ;) I will try to answer as much as time allows, Cheers, San

ERibe3 commented 3 years ago

Hello San!

Thanks for publishing the tutorial, it has been very useful.

However, I would like to request some assistance with an issue that I am facing while trying to perform the steps described in the Create Calculation Node with Currency Conversion part.

I have reached the seventh step where I inserted the following expression:

CONVERT_CURRENCY(
    "AMOUNT" => "NETAMOUNT", 
    "SOURCE_UNIT" => "CURRENCY", 
    "TARGET_UNIT" => 'EUR', 
    "CONVERSION_TYPE" => 'M', 
    "REFERENCE_DATE" => CURRENT_DATE, 
    "CLIENT" => '002', "SCHEMA" => 'GIT_HUB_EXERCISES', 
    "ERROR_HANDLING" => 'set_to_null', 
    "STEPS" => 'shift,convert,round', 
    "PRECISIONS_TABLE" => 'V_TCURX', 
    "CONFIGURATION_TABLE" => 'V_TCURV', 
    "PREFACTORS_TABLE" => 'V_TCURF', 
    "RATES_TABLE" => 'V_TCURR',
    "NOTATION_TABLE" => 'V_TCURN')

Once I have inserted it and tried to preview the data, the following error occurred:

Error | invalid name of function or procedure: CONVERT_CURRENCY: line 16 col 3 (at pos 783), Code: 328, SQL State: HY000 -- | --

I keed getting this error besides having to perform all the previous steps in listed in the training, even tried to recreate the view but the issue still is occurring. Would you have any idea of what is causing this and how to fix it? It looks like that the CONVERT_CURRENCY is not get read or recognized.

Also here is what is displayed in the the SQL preview, if it is useful:

SELECT
  "CSV_INTERNALORDERS"."INTERNALORDERID" AS "INTERNALORDERID",
  "CSV_INTERNALORDERS"."CREATEDAT" AS "CREATE_DATE",
  "CSV_INTERNALORDERS"."CREATEDBY" AS "CREATEDBY",
  "CSV_INTERNALORDERS"."EMPLOYEEID" AS "EMPLOYEEID",
  "CSV_INTERNALORDERS"."COSTCENTER" AS "COSTCENTER",
  "CSV_INTERNALORDERS"."COMPANYCODE" AS "COMPANYCODE",
  "CSV_INTERNALORDERS"."PRODUCTID" AS "PRODUCTID",
  "CSV_INTERNALORDERS"."PRODUCTTYPE" AS "PRODUCTTYPE",
  "CSV_INTERNALORDERS"."QUANTITY" AS "QUANTITY",
  "CSV_INTERNALORDERS"."CURRENCY" AS "CURRENCY",
  "CSV_INTERNALORDERS"."GROSSAMOUNT" AS "GROSSAMOUNT",
  "CSV_INTERNALORDERS"."NETAMOUNT" AS "NETAMOUNT",
  "CSV_INTERNALORDERS"."TAXAMOUNT" AS "TAXAMOUNT",
  "CSV_INTERNALORDERS"."COMPANYCODE_COSTCENTER" AS "COMPANYCODE_COSTCENTER",
  'EUR' AS "TARGET_CURRENCY",
  CONVERT_CURRENCY("AMOUNT" => "CSV_INTERNALORDERS"."NETAMOUNT", "SOURCE_UNIT" => "CSV_INTERNALORDERS"."CURRENCY", "TARGET_UNIT" => 'EUR', "CONVERSION_TYPE" => 'M', "REFERENCE_DATE" => CURRENT_DATE, "CLIENT" => '002', "SCHEMA" => 'GIT_HUB_EXERCISES', "ERROR_HANDLING" => 'set_to_null', "STEPS" => 'shift,convert,round', "PRECISIONS_TABLE" => 'V_TCURX', "CONFIGURATION_TABLE" => 'V_TCURV', "PREFACTORS_TABLE" => 'V_TCURF', "RATES_TABLE" => 'V_TCURR', "NOTATION_TABLE" => 'V_TCURN') AS "NETAMOUNT_TG"
FROM "CSV_INTERNALORDERS"

Please let me know if you can help.

Thanks in Advance.

d031182 commented 3 years ago

Hi, Thanks for approaching.

  1. Did you also do the exercise 1, and preparing the TCUR tables with wranging? modeling-in-sap-data-warehouse-cloud/exercises/ex1/upload-tcur at main · d031182/modeling-in-sap-data-warehouse-cloud (github.com)https://github.com/d031182/modeling-in-sap-data-warehouse-cloud/tree/main/exercises/ex1/upload-tcur

2.) In the previous steps you have to create the wrapper views. Have you make sure that they are exposed for consumption? (There is a flag for doing so) modeling-in-sap-data-warehouse-cloud/exercises/ex3/currency-wrapper-view at main · d031182/modeling-in-sap-data-warehouse-cloud (github.com)https://github.com/d031182/modeling-in-sap-data-warehouse-cloud/tree/main/exercises/ex3/currency-wrapper-view

3.) There is also currently a limitation, that the Currency Tables (TCUR*) have to be on the same space where the view resides.

Best regards, San

From: ERibe3 @.> Sent: Thursday, 25 November 2021 00:28 To: d031182/modeling-in-sap-data-warehouse-cloud @.> Cc: Tran, San @.>; Author @.> Subject: Re: [d031182/modeling-in-sap-data-warehouse-cloud] Feedback and Comments (#1)

Hello San!

Thanks for publishing the tutorial, it has been very useful.

However, I would like to request some assistance with an issue that I am facing while trying to perform the steps described in the Create Calculation Node with Currency Conversionhttps://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fd031182%2Fmodeling-in-sap-data-warehouse-cloud%2Ftree%2Fmain%2Fexercises%2Fex3%2Finternal-orders-currency-conversion&data=04%7C01%7Csan.tran%40sap.com%7C019d46c10f0b445eca6108d9afa21d5a%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C637733933145880004%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=LYLsgi7GOETGnGKGLV8tRdvILYg%2FfTqo4pXrfa9VBUE%3D&reserved=0 part.

I have reached the seventh step where I inserted the following expression:

CONVERT_CURRENCY(

"AMOUNT" => "NETAMOUNT",

"SOURCE_UNIT" => "CURRENCY",

"TARGET_UNIT" => 'EUR',

"CONVERSION_TYPE" => 'M',

"REFERENCE_DATE" => CURRENT_DATE,

"CLIENT" => '002', "SCHEMA" => 'GIT_HUB_EXERCISES',

"ERROR_HANDLING" => 'set_to_null',

"STEPS" => 'shift,convert,round',

"PRECISIONS_TABLE" => 'V_TCURX',

"CONFIGURATION_TABLE" => 'V_TCURV',

"PREFACTORS_TABLE" => 'V_TCURF',

"RATES_TABLE" => 'V_TCURR',

"NOTATION_TABLE" => 'V_TCURN')

Once I have inserted it and tried to preview the data, the following error occurred: Error invalid name of function or procedure: CONVERT_CURRENCY: line 16 col 3 (at pos 783), Code: 328, SQL State: HY000

I keed getting this error besides having to perform all the previous steps in listed in the training, even tried to recreate the view but the issue still is occurring. Would you have any idea of what is causing this and how to fix it? It looks like that the CONVERT_CURRENCY is not get read or recognized.

Also here is what is displayed in the the SQL preview, if it is useful:

SELECT

"CSV_INTERNALORDERS"."INTERNALORDERID" AS "INTERNALORDERID",

"CSV_INTERNALORDERS"."CREATEDAT" AS "CREATE_DATE",

"CSV_INTERNALORDERS"."CREATEDBY" AS "CREATEDBY",

"CSV_INTERNALORDERS"."EMPLOYEEID" AS "EMPLOYEEID",

"CSV_INTERNALORDERS"."COSTCENTER" AS "COSTCENTER",

"CSV_INTERNALORDERS"."COMPANYCODE" AS "COMPANYCODE",

"CSV_INTERNALORDERS"."PRODUCTID" AS "PRODUCTID",

"CSV_INTERNALORDERS"."PRODUCTTYPE" AS "PRODUCTTYPE",

"CSV_INTERNALORDERS"."QUANTITY" AS "QUANTITY",

"CSV_INTERNALORDERS"."CURRENCY" AS "CURRENCY",

"CSV_INTERNALORDERS"."GROSSAMOUNT" AS "GROSSAMOUNT",

"CSV_INTERNALORDERS"."NETAMOUNT" AS "NETAMOUNT",

"CSV_INTERNALORDERS"."TAXAMOUNT" AS "TAXAMOUNT",

"CSV_INTERNALORDERS"."COMPANYCODE_COSTCENTER" AS "COMPANYCODE_COSTCENTER",

'EUR' AS "TARGET_CURRENCY",

CONVERT_CURRENCY("AMOUNT" => "CSV_INTERNALORDERS"."NETAMOUNT", "SOURCE_UNIT" => "CSV_INTERNALORDERS"."CURRENCY", "TARGET_UNIT" => 'EUR', "CONVERSION_TYPE" => 'M', "REFERENCE_DATE" => CURRENT_DATE, "CLIENT" => '002', "SCHEMA" => 'GIT_HUB_EXERCISES', "ERROR_HANDLING" => 'set_to_null', "STEPS" => 'shift,convert,round', "PRECISIONS_TABLE" => 'V_TCURX', "CONFIGURATION_TABLE" => 'V_TCURV', "PREFACTORS_TABLE" => 'V_TCURF', "RATES_TABLE" => 'V_TCURR', "NOTATION_TABLE" => 'V_TCURN') AS "NETAMOUNT_TG"

FROM "CSV_INTERNALORDERS"

Please let me know if you can help.

Thanks in Advance.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fd031182%2Fmodeling-in-sap-data-warehouse-cloud%2Fissues%2F1%23issuecomment-978451665&data=04%7C01%7Csan.tran%40sap.com%7C019d46c10f0b445eca6108d9afa21d5a%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C637733933145889958%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=4%2F2d6omvdXsSd5N1OFmtQ8Dmjvedr6lmMIey%2F8PpT1Y%3D&reserved=0, or unsubscribehttps://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAQ64KYJXKSCT7ZJCEZ66LN3UNVYJRANCNFSM5FLEW2CA&data=04%7C01%7Csan.tran%40sap.com%7C019d46c10f0b445eca6108d9afa21d5a%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C637733933145889958%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=7TaZwlO28k800W9CCN4OgVgDqOWAUUdaUXHXGMqpguY%3D&reserved=0. Triage notifications on the go with GitHub Mobile for iOShttps://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fapps.apple.com%2Fapp%2Fapple-store%2Fid1477376905%3Fct%3Dnotification-email%26mt%3D8%26pt%3D524675&data=04%7C01%7Csan.tran%40sap.com%7C019d46c10f0b445eca6108d9afa21d5a%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C637733933145899914%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=oeuouhx7FtUxNo6D2YDLZwh%2BQWg1wZPglGzeJzMcVe4%3D&reserved=0 or Androidhttps://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fplay.google.com%2Fstore%2Fapps%2Fdetails%3Fid%3Dcom.github.android%26referrer%3Dutm_campaign%253Dnotification-email%2526utm_medium%253Demail%2526utm_source%253Dgithub&data=04%7C01%7Csan.tran%40sap.com%7C019d46c10f0b445eca6108d9afa21d5a%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C637733933145909870%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=tdSRvYIDd9%2Bh52uz6mw8IYQDKVGfHD8pTAkPX4kA9fY%3D&reserved=0.