search

GoogleCloudPlatform / cortex-data-foundation

Data Foundation - Google Cloud Cortex Framework
https://cloud.google.com/solutions/cortex
Apache License 2.0
161 stars 86 forks source link

Cortex 4.2 fails for SAP and CDC false #26

Closed chodankarcc closed 1 year ago

chodankarcc commented 1 year ago

Hello @Lsubatin , Cortex 4.2 build fails (CDC=false and SQL_FLAVOUR=S4) saying 'bstaus_sg not found inside StockMonthlySnapshots' (raw layer), I see table 'stock_monthly_snapshots' is getting created (without bstaus_sg column) at start of execution and later(after 1.5-2 hr) while creating StockMonthlySnapshots reporting view it fails. Just before this view creation, I see it has stock_monthly_snapshots DDL creation code with bstaus_sg column but it doesn't replace table. Please have a look at it and fix it.

Command: gcloud builds submit --project ########## \ --impersonate-service-account ############ \ --substitutions \ _PJID_SRC=project_A,_PJID_TGT=project_A,_DS_CDC=CORTEX_RAW_LANDING,_DS_RAW=CORTEX_RAW_LANDING,_DS_REPORTING=CTX_REPORTING,_DS_MODELS=CTX_ML_MODELS,_GCS_BUCKET=XXXX,_TGT_BUCKET=YYYYY,_SQL_FLAVOUR=S4,_TEST_DATA=true,_DEPLOY_CDC=false,_GEN_EXT=true,_DEPLOY_SAP=true,_DEPLOY_SFDC=false

*please note config.json is also changed. We have RAW actual SAP data replicated realtime from CDF SLT.

@Lsubatin After digging in more I see this table creation is happening at two places: Please have a look at it and fix:

Step sap-cdc: cortex/cortex4.2/cortex-data-foundation/src/SAP/SAP_CDC/src/external_dag/inventory_snapshots/stock_monthly_snapshots_initial.sql sap-reporting: cortex/cortex4.2/cortex-data-foundation/src/SAP/SAP_REPORTING/StockMonthlySnapshots.sql During sap-cdc I traced our BQ job history and I see jinja if loop of sql_flavour == 's4' didn't execute as expected. Below is query which got executed:

CREATE OR REPLACE TABLE XXXXX.YYYYY.stock_monthly_snapshots( mandt STRING, werks STRING, matnr STRING, charg STRING, lgort STRING, bukrs STRING, bwart STRING, insmk STRING, sobkz STRING, shkzg STRING, cal_year INTEGER, cal_month INTEGER, meins STRING, waers STRING, month_end_date DATE, total_monthly_movement_quantity NUMERIC, total_monthly_movement_amount NUMERIC, amount_monthly_cumulative NUMERIC, quantity_monthly_cumulative NUMERIC) PARTITION BY DATE_TRUNC(month_end_date, MONTH);

dotinfinity commented 1 year ago

HI @chodankarcc , I wasn't able to fully replicate what you are seeing based on our test data. However, to isolate the issue, can you try the following?

  1. Delete the following tables from your RAW/CDC dataset that was generated from the previous deployment, as they were likely generated incorrectly based on your description.:
    • monthly_inventory_aggregation
    • stock_characteristics_config
    • stock_monthly_snapshots
    • stock_weekly_snapshots
    • weekly_inventory_aggregation.
  2. Delete the reporting dataset CTX_REPORTING from your previous deployment (or deploy to a different Reporting dataset)
  3. Re-run the deployment with the same command you've used, except, set the value of _SQL_FLAVOUR to s4 (lowercase s).

I have a suspicision that this was caused by an incorrect parameter handling during the deployment process but would like to confirm. Thank you very much!

chodankarcc commented 1 year ago

Thank you @dotinfinity for your reply. I initially thought the same but when I looked at cloud build log most places I saw pipeline code is converting S4 to s4. But let me try rerunning process again with your suggestions. Thanks for detailed commands. Along with above tables, I see below extra tables were generated in RAW(i.e same as CDC) dataset for us, I will try to delete them and rerun framework.

currency_decimal slow_moving_threshold weather_weekly postcode prod_hierarchy_texts currency_conversion calendar_date_dim

chodankarcc commented 1 year ago

Team, @dotinfinity, @benschuler

After making suggested changes, I think it has crossed that error but build is failing later with below error:. I remember we didnt face such issues with Cortex 4.1. Can you suggest solution for this?

Step #3: INFO:root:Generated DAG SQL file : /workspace/generated_reporting_dag_sql_files/SAP_reporting_PROJECTID_CTX_REPORTING_april_6_AccountsPayable.sql Step #3: INFO:root:Generated dag python file: /workspace/generated_reporting_dag_py_files/SAP_reporting_PROJECTID_CTX_REPORTING_april_6_AccountsPayable.py Step #3: INFO:root:Populating table 'PROJECTID.CTX_REPORTING_april_6.AccountsPayable' with test data... Step #3: ERROR: Failed to load test data. Error = 400 Query error: Scalar subquery produced more than one element at [2:1]

BQ job query details (initial two lines): TRUNCATE TABLE PROJECTID.CTX_REPORTING_april_6.AccountsPayable; INSERT INTO PROJECTID.CTX_REPORTING_april_6.AccountsPayable (

dotinfinity commented 1 year ago

Hi @chodankarcc , this error is related to the new experimental reporting optimization (Materializer) feature we've rolled out for 4.2. The goal is to provide capability to materialize certain heavy views so they are more efficient when accessed. In the current version we only materialize a small number of views (see here for list).

Based on BQ documentation, Error 400 is described as follows: This error returns when the value of statistics.query.billingTier for an on-demand Job exceeds 100. This occurs when on-demand queries use too much CPU relative to the amount of data scanned

I'm not sure about the details about your Google Cloud / BigQuery / Billing set up, but here's a temporary workaround to try: can you update materializer settings file and replace all 'type: tabletotype: view`? See documentation for details.

chodankarcc commented 1 year ago

@dotinfinity I believe actual error is "Scalar subquery produced more than one element"

I feel its more related to BQ query. Please find attached actual query.

https://cloud.google.com/bigquery/docs/reference/standard-sql/subqueries#scalar_subquery_concepts

Query: TRUNCATE TABLE PROJECTID.CTX_REPORTING_april_6.AccountsPayable; INSERT INTO PROJECTID.CTX_REPORTING_april_6.AccountsPayable ( Client_MANDT, CompanyCode_BUKRS, CompanyText_BUTXT, AccountNumberOfVendorOrCreditor_LIFNR, NAME1, AmountInLocalCurrency_DMBTR, AccountingDocumentNumber_BELNR, NumberOfLineItemWithinAccountingDocument_BUZEI, DocumentNumberOfTheClearingDocument_AUGBL, TermsOfPaymentKey_ZTERM, AccountType_KOART, ReasonCodeForPayments_RSTGR, PaymentBlockKey_ZLSPR, ClearingDate_AUGDT, PostingDateInTheDocument_BUDAT, FiscalYear_GJAHR, FiscalPeriod_MONAT, DocFiscPeriod, KeyFiscPeriod, NetDueDate, InvStatus_RBSTAT, PostingDate_BUDAT, PurchasingDocumentNumber_EBELN, CurrencyKey_WAERS, SupplyingCountry_LANDL, AccountingDocumenttype_BLART, InvoiceDocumenttype_BLART, MovementType__inventoryManagement___BWART, POOrderHistory_AmountInLocalCurrency_DMBTR, POOrderHistory_AmountInTargetCurrency_DMBTR, YearOfPostingDateInTheDocument_BUDAT, MonthOfPostingDateInTheDocument_BUDAT, WeekOfPostingDateInTheDocument_BUDAT, QuarterOfPostingDateInTheDocument_BUDAT, AmountInTargetCurrency_DMBTR, ExchangeRate_UKURS, TargetCurrency_TCURR, OverdueAmountInSourceCurrency, OverdueAmountInTargetCurrency, OutstandingButNotOverdueInSourceCurrency, OutstandingButNotOverdueInTargetCurrency, OverdueOnPastDateInSourceCurrency, OverdueOnPastDateInTargetCurrency, PartialPaymentsInSourceCurrency, PartialPaymentsInTargetCurrency, LatePaymentsInSourceCurrency, LatePaymentsInTargetCurrency, UpcomingPaymentsInSourceCurrency, UpcomingPaymentsInTargetCurrency, PotentialPenaltyInSourceCurrency, PotentialPenaltyInTargetCurrency, PurchaseInSourceCurrency, PurchaseInTargetCurrency, IsParkedInvoice, IsBlockedInvoice, CashDiscountReceivedInSourceCurrency, CashDiscountReceivedInTargetCurrency, TargetCashDiscountInSourceCurrency, TargetCashDiscountInTargetCurrency, AmountOfOpenDebitItemsInSourceCurrency, AmountOfOpenDebitItemsInTargetCurrency, AmountOfReturnInSourceCurrency, AmountOfReturnInTargetCurrency )

-- Copyright 2022 Google LLC

--

-- Licensed under the Apache License, Version 2.0 (the "License");

-- you may not use this file except in compliance with the License.

-- You may obtain a copy of the License at

--

-- https://www.apache.org/licenses/LICENSE-2.0

--

-- Unless required by applicable law or agreed to in writing, software

-- distributed under the License is distributed on an "AS IS" BASIS,

-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

-- See the License for the specific language governing permissions and

-- limitations under the License.

( WITH CurrencyConversion AS ( SELECT Client_MANDT, FromCurrency_FCURR, ToCurrency_TCURR, ConvDate, ExchangeRate_UKURS FROM PROJECTID.CTX_REPORTING_april_6.CurrencyConversion WHERE ToCurrency_TCURR IN ('USD') --##CORTEX-CUSTOMER Modify the exchange rate type based on your requirement AND ExchangeRateType_KURST = 'M' ),

AccountingInvoices AS ( SELECT AccountingDocuments.Client_MANDT, AccountingDocuments.CompanyCode_BUKRS, AccountingDocuments.AccountingDocumentNumber_BELNR, AccountingDocuments.FiscalYear_GJAHR, AccountingDocuments.Documenttype_BLART AS AccountingDocumenttype_BLART, InvoiceDocuments.Documenttype_BLART AS InvoiceDocumenttype_BLART, AccountingDocuments.DocumentDateInDocument_BLDAT, AccountingDocuments.PostingDateInTheDocument_BUDAT, InvoiceDocuments.PostingDate_BUDAT, AccountingDocuments.FiscalPeriod_MONAT, AccountingDocuments.PurchasingDocumentNumber_EBELN, AccountingDocuments.NumberOfLineItemWithinAccountingDocument_BUZEI, AccountingDocuments.ClearingDate_AUGDT, COALESCE(AccountingDocuments.NetPaymentAmount_NEBTR, 0) AS NetPaymentAmount_NEBTR, COALESCE(AccountingDocuments.AmountInLocalCurrency_DMBTR, 0) AS AmountInLocalCurrency_DMBTR, AccountingDocuments.AccountType_KOART, AccountingDocuments.TransactionKey_KTOSL, AccountingDocuments.PostingKey_BSCHL, AccountingDocuments.CashDiscountDays1_ZBD1T, AccountingDocuments.BaselineDateForDueDateCalculation_ZFBDT, COALESCE(AccountingDocuments.AmountEligibleForCashDiscountInDocumentCurrency_SKFBT, 0) AS AmountEligibleForCashDiscountInDocumentCurrency_SKFBT, AccountingDocuments.AccountNumberOfVendorOrCreditor_LIFNR, AccountingDocuments.PaymentBlockKey_ZLSPR, AccountingDocuments.SpecialGlIndicator_UMSKZ, AccountingDocuments.ItemNumberOfPurchasingDocument_EBELP, AccountingDocuments.FollowOnDocumentType_REBZT, AccountingDocuments.DocumentNumberOfTheClearingDocument_AUGBL, AccountingDocuments.TermsOfPaymentKey_ZTERM, AccountingDocuments.ReasonCodeForPayments_RSTGR, AccountingDocuments.CashDiscountPercentage1_ZBD1P, AccountingDocuments.NetPaymentTermsPeriod_ZBD3T, AccountingDocuments.CashDiscountDays2_ZBD2T, AccountingDocuments.DebitcreditIndicator_SHKZG, AccountingDocuments.InvoiceToWhichTheTransactionBelongs_REBZG, AccountingDocuments.CurrencyKey_WAERS, AccountingDocuments.SupplyingCountry_LANDL, AccountingDocuments.ObjectKey_AWKEY, NULL AS InvStatus_RBSTAT, AccountingDocuments.YearOfPostingDateInTheDocument_BUDAT, AccountingDocuments.MonthOfPostingDateInTheDocument_BUDAT, AccountingDocuments.WeekOfPostingDateInTheDocument_BUDAT, AccountingDocuments.QuarterOfPostingDateInTheDocument_BUDAT FROM PROJECTID.CTX_REPORTING_april_6.AccountingDocuments AS AccountingDocuments LEFT OUTER JOIN PROJECTID.CTX_REPORTING_april_6.InvoiceDocuments_Flow AS InvoiceDocuments ON AccountingDocuments.Client_MANDT = InvoiceDocuments.Client_MANDT AND AccountingDocuments.CompanyCode_BUKRS = InvoiceDocuments.CompanyCode_BUKRS AND AccountingDocuments.ObjectKey_AWKEY = CONCAT(InvoiceDocuments.InvoiceDocNum_BELNR, InvoiceDocuments.FiscalYear_GJAHR) AND AccountingDocuments.FiscalYear_GJAHR = InvoiceDocuments.FiscalYear_GJAHR AND LTRIM(AccountingDocuments.NumberOfLineItemWithinAccountingDocument_BUZEI, '0') = LTRIM(InvoiceDocuments.InvoiceDocLineNum_BUZEI, '0') WHERE AccountingDocuments.AccountType_KOART = 'K' ## CORTEX-CUSTOMER: Please add relevant Account Type. Value 'K' represents 'Vendor' OR AccountingDocuments.PurchasingDocumentNumber_EBELN IS NOT NULL OR AccountingDocuments.Documenttype_BLART IN ('KZ', 'ZP') ## CORTEX-CUSTOMER: Please add relevant Document Type. Value 'KZ' represents 'Vendor Payment' and 'ZP' represents 'Payment Posting' OR AccountingDocuments.TransactionKey_KTOSL = 'SKE' ## CORTEX-CUSTOMER: Please add relevant Transaction Key. Value 'SKE' represents 'Cash Discount Received' OR AccountingDocuments.PostingKey_BSCHL = '31' ## CORTEX-CUSTOMER: Please add relevant Posting Key. Value '31' represents 'Vendor Invoice'

UNION ALL

SELECT InvoiceDocuments.Client_MANDT, InvoiceDocuments.CompanyCode_BUKRS, InvoiceDocuments.InvoiceDocNum_BELNR AS AccountingDocumentNumber_BELNR, InvoiceDocuments.FiscalYear_GJAHR, CAST(NULL AS STRING) AS AccountingDocumenttype_BLART, CAST(NULL AS STRING) AS InvoiceDocumenttype_BLART, InvoiceDocuments.DocumentDate_BLDAT AS DocumentDateInDocument_BLDAT, InvoiceDocuments.PostingDate_BUDAT AS PostingDateInTheDocument_BUDAT, InvoiceDocuments.PostingDate_BUDAT, CAST(NULL AS STRING) AS FiscalPeriod_MONAT, CAST(NULL AS STRING) AS PurchasingDocumentNumber_EBELN, CAST(NULL AS STRING) AS NumberOfLineItemWithinAccountingDocument_BUZEI, CAST(NULL AS DATE) AS ClearingDate_AUGDT, NULL AS NetPaymentAmount_NEBTR, InvoiceDocuments.GrossInvAmnt_RMWWR AS AmountInLocalCurrency_DMBTR, CAST(NULL AS STRING) AS AccountType_KOART, CAST(NULL AS STRING) AS TransactionKey_KTOSL, CAST(NULL AS STRING) AS PostingKey_BSCHL, CAST(NULL AS NUMERIC) AS CashDiscountDays1_ZBD1T, CAST(NULL AS DATE) AS BaselineDateForDueDateCalculation_ZFBDT, CAST(NULL AS NUMERIC) AS AmountEligibleForCashDiscountInDocumentCurrency_SKFBT, InvoiceDocuments.InvoicingParty_LIFNR AS AccountNumberOfVendorOrCreditor_LIFNR, CAST(NULL AS STRING) AS PaymentBlockKey_ZLSPR, CAST(NULL AS STRING) AS SpecialGlIndicator_UMSKZ, CAST(NULL AS STRING) AS ItemNumberOfPurchasingDocument_EBELP, CAST(NULL AS STRING) AS FollowOnDocumentType_REBZT, CAST(NULL AS STRING) AS DocumentNumberOfTheClearingDocument_AUGBL, CAST(NULL AS STRING) AS TermsOfPaymentKey_ZTERM, CAST(NULL AS STRING) AS ReasonCodeForPayments_RSTGR, CAST(NULL AS NUMERIC) AS CashDiscountPercentage1_ZBD1P, CAST(NULL AS NUMERIC) AS NetPaymentTermsPeriod_ZBD3T, CAST(NULL AS NUMERIC) AS CashDiscountDays2_ZBD2T, CAST(NULL AS STRING) AS DebitcreditIndicator_SHKZG, CAST(NULL AS STRING) AS InvoiceToWhichTheTransactionBelongs_REBZG, Currency_WAERS AS CurrencyKey_WAERS, CAST(NULL AS STRING) AS SupplyingCountry_LANDL, CAST(NULL AS STRING) AS ObjectKey_AWKEY, InvoiceDocuments.InvStatus_RBSTAT, InvoiceDocuments.YearOfPostingDate_BUDAT, InvoiceDocuments.MonthOfPostingDate_BUDAT, InvoiceDocuments.WeekOfPostingDate_BUDAT, InvoiceDocuments.QuarterOfPostingDate_BUDAT, FROM PROJECTID.CTX_REPORTING_april_6.InvoiceDocuments_Flow AS InvoiceDocuments WHERE

CORTEX-CUSTOMER: Please add relevant Invoice Status. Value 'A' represents that the document is Parked and not posted

InvoiceDocuments.Invstatus_RBSTAT = 'A'

QUALIFY RANK() OVER ( PARTITION BY InvoiceDocuments.Client_MANDT, InvoiceDocuments.CompanyCode_BUKRS, InvoiceDocuments.InvoiceDocNum_BELNR ORDER BY InvoiceDocuments.InvoiceDocLineNum_BUZEI) = 1 ),

AccountingInvoicesKPI AS ( SELECT AccountingInvoices.*, CompaniesMD.CompanyText_BUTXT,

CASE `PROJECTID.CTX_REPORTING_april_6.Fiscal_Period`(
  AccountingInvoices.Client_MANDT,
  CompaniesMD.FiscalyearVariant_PERIV,
  AccountingInvoices.PostingDateInTheDocument_BUDAT
)
  WHEN 'CASE1' THEN
    `PROJECTID.CTX_REPORTING_april_6.Fiscal_Case1`(
      AccountingInvoices.Client_MANDT,
      CompaniesMD.FiscalyearVariant_PERIV,
      AccountingInvoices.PostingDateInTheDocument_BUDAT
    )
  WHEN 'CASE2' THEN
    `PROJECTID.CTX_REPORTING_april_6.Fiscal_Case2`(
      AccountingInvoices.Client_MANDT,
      CompaniesMD.FiscalyearVariant_PERIV,
      AccountingInvoices.PostingDateInTheDocument_BUDAT
    )
  WHEN 'CASE3' THEN
    `PROJECTID.CTX_REPORTING_april_6.Fiscal_Case3`(
      AccountingInvoices.Client_MANDT,
      CompaniesMD.FiscalyearVariant_PERIV,
      AccountingInvoices.PostingDateInTheDocument_BUDAT
    )
END AS DocFiscPeriod,

CASE `PROJECTID.CTX_REPORTING_april_6.Fiscal_Period`(
  AccountingInvoices.Client_MANDT,
  CompaniesMD.FiscalyearVariant_PERIV,
  DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)
)
  WHEN 'CASE1' THEN
    `PROJECTID.CTX_REPORTING_april_6.Fiscal_Case1`(
      AccountingInvoices.Client_MANDT,
      CompaniesMD.FiscalyearVariant_PERIV,
      DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)
    )
  WHEN 'CASE2' THEN
    `PROJECTID.CTX_REPORTING_april_6.Fiscal_Case2`(
      AccountingInvoices.Client_MANDT,
      CompaniesMD.FiscalyearVariant_PERIV,
      DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)
    )
  WHEN 'CASE3' THEN
    `PROJECTID.CTX_REPORTING_april_6.Fiscal_Case3`(
      AccountingInvoices.Client_MANDT,
      CompaniesMD.FiscalyearVariant_PERIV,
      DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)
    )
END AS KeyFiscPeriod,

DATE_ADD(
  IF(
    ## CORTEX-CUSTOMER: Please add relevant Account Type. Value 'K' represents 'Vendor'
    AccountingInvoices.AccountType_KOART = 'K' AND AccountingInvoices.BaselineDateForDueDateCalculation_ZFBDT IS NULL,
    AccountingInvoices.DocumentDateInDocument_BLDAT,
    AccountingInvoices.BaselineDateForDueDateCalculation_ZFBDT
  ),
  INTERVAL CAST(
    CASE
      ## CORTEX-CUSTOMER: Please add relevant Account Type. Value 'K' represents 'Vendor'
      WHEN AccountingInvoices.AccountType_KOART = 'K' AND AccountingInvoices.NetPaymentTermsPeriod_ZBD3T IS NOT NULL
        THEN AccountingInvoices.NetPaymentTermsPeriod_ZBD3T
      ## CORTEX-CUSTOMER: Please add relevant Account Type. Value 'K' represents 'Vendor'
      WHEN AccountingInvoices.AccountType_KOART = 'K' AND AccountingInvoices.CashDiscountDays2_ZBD2T IS NOT NULL
        THEN AccountingInvoices.CashDiscountDays2_ZBD2T
      ## CORTEX-CUSTOMER: Please add relevant Account Type. Value 'K' represents 'Vendor'
      WHEN AccountingInvoices.AccountType_KOART = 'K' AND AccountingInvoices.CashDiscountDays1_ZBD1T IS NOT NULL
        THEN AccountingInvoices.CashDiscountDays1_ZBD1T
      WHEN AccountingInvoices.CashDiscountDays1_ZBD1T IS NULL
        THEN 0
      ## CORTEX-CUSTOMER: Please add relevant Account Type. Value 'K' represents 'Vendor'
      ## CORTEX-CUSTOMER: Please add relevant Debit Credit Indicator. Value 'H' represents 'Credit' ('S' represents 'Debit'))
      WHEN AccountingInvoices.AccountType_KOART = 'K' AND AccountingInvoices.DebitcreditIndicator_SHKZG = 'H'
        AND AccountingInvoices.InvoiceToWhichTheTransactionBelongs_REBZG IS NULL
        THEN 0  ## CORTEX-CUSTOMER: Please add relevant Debit Credit Indicator
      ELSE 0
    END
    AS INT64
  ) DAY
) AS NetDueDate

FROM AccountingInvoices INNER JOIN PROJECTID.CTX_REPORTING_april_6.CompaniesMD AS CompaniesMD ON AccountingInvoices.Client_MANDT = CompaniesMD.Client_MANDT AND AccountingInvoices.CompanyCode_BUKRS = CompaniesMD.CompanyCode_BUKRS )

SELECT AccountingInvoicesKPI.Client_MANDT, AccountingInvoicesKPI.CompanyCode_BUKRS, AccountingInvoicesKPI.CompanyText_BUTXT, AccountingInvoicesKPI.AccountNumberOfVendorOrCreditor_LIFNR, VendorsMD.NAME1, AccountingInvoicesKPI.AmountInLocalCurrency_DMBTR, AccountingInvoicesKPI.AccountingDocumentNumber_BELNR, AccountingInvoicesKPI.NumberOfLineItemWithinAccountingDocument_BUZEI, AccountingInvoicesKPI.DocumentNumberOfTheClearingDocument_AUGBL, AccountingInvoicesKPI.TermsOfPaymentKey_ZTERM, AccountingInvoicesKPI.AccountType_KOART, AccountingInvoicesKPI.ReasonCodeForPayments_RSTGR, AccountingInvoicesKPI.PaymentBlockKey_ZLSPR, AccountingInvoicesKPI.ClearingDate_AUGDT, AccountingInvoicesKPI.PostingDateInTheDocument_BUDAT, AccountingInvoicesKPI.FiscalYear_GJAHR, AccountingInvoicesKPI.FiscalPeriod_MONAT, AccountingInvoicesKPI.DocFiscPeriod, AccountingInvoicesKPI.KeyFiscPeriod, AccountingInvoicesKPI.NetDueDate, AccountingInvoicesKPI.InvStatus_RBSTAT, AccountingInvoicesKPI.PostingDate_BUDAT, AccountingInvoicesKPI.PurchasingDocumentNumber_EBELN, AccountingInvoicesKPI.CurrencyKey_WAERS, AccountingInvoicesKPI.SupplyingCountry_LANDL, AccountingInvoicesKPI.AccountingDocumenttype_BLART, AccountingInvoicesKPI.InvoiceDocumenttype_BLART, POOrderHistory.MovementType__inventoryManagement___BWART, POOrderHistory.AmountInLocalCurrency_DMBTR AS POOrderHistory_AmountInLocalCurrency_DMBTR, POOrderHistory.AmountInLocalCurrency_DMBTR CurrencyConversion.ExchangeRate_UKURS AS POOrderHistory_AmountInTargetCurrency_DMBTR, AccountingInvoicesKPI.YearOfPostingDateInTheDocument_BUDAT, AccountingInvoicesKPI.MonthOfPostingDateInTheDocument_BUDAT, AccountingInvoicesKPI.WeekOfPostingDateInTheDocument_BUDAT, AccountingInvoicesKPI.QuarterOfPostingDateInTheDocument_BUDAT, AccountingInvoicesKPI.AmountInLocalCurrency_DMBTR CurrencyConversion.ExchangeRate_UKURS AS AmountInTargetCurrency_DMBTR, CurrencyConversion.ExchangeRate_UKURS, CurrencyConversion.ToCurrency_TCURR AS TargetCurrency_TCURR,

/ Overdue Amount / IF(

CORTEX-CUSTOMER: Please add relevant Account Type. Value 'K' represents 'Vendor'

AccountingInvoicesKPI.AccountType_KOART = 'K' AND CURRENT_DATE() > AccountingInvoicesKPI.NetDueDate,
AccountingInvoicesKPI.AmountInLocalCurrency_DMBTR,
0

) AS OverdueAmountInSourceCurrency,

IF(

CORTEX-CUSTOMER: Please add relevant Account Type. Value 'K' represents 'Vendor'

AccountingInvoicesKPI.AccountType_KOART = 'K' AND CURRENT_DATE() > AccountingInvoicesKPI.NetDueDate,
AccountingInvoicesKPI.AmountInLocalCurrency_DMBTR * CurrencyConversion.ExchangeRate_UKURS,
0

) AS OverdueAmountInTargetCurrency,

/ Outstanding But Not Overdue / IF(

CORTEX-CUSTOMER: Please add relevant Account Type. Value 'K' represents 'Vendor'

AccountingInvoicesKPI.AccountType_KOART = 'K' AND CURRENT_DATE() <= AccountingInvoicesKPI.NetDueDate,
AccountingInvoicesKPI.AmountInLocalCurrency_DMBTR,
0

) AS OutstandingButNotOverdueInSourceCurrency,

IF(

CORTEX-CUSTOMER: Please add relevant Account Type. Value 'K' represents 'Vendor'

AccountingInvoicesKPI.AccountType_KOART = 'K' AND CURRENT_DATE() <= AccountingInvoicesKPI.NetDueDate,
AccountingInvoicesKPI.AmountInLocalCurrency_DMBTR * CurrencyConversion.ExchangeRate_UKURS,
0

) AS OutstandingButNotOverdueInTargetCurrency,

/ Overdue On Past Date / IF(

CORTEX-CUSTOMER: Please add relevant Account Type. Value 'K' represents 'Vendor'

AccountingInvoicesKPI.AccountType_KOART = 'K'
AND AccountingInvoicesKPI.PostingDate_BUDAT < CURRENT_DATE()
AND AccountingInvoicesKPI.NetDueDate < CURRENT_DATE()
AND AccountingInvoicesKPI.ClearingDate_AUGDT IS NULL,
AccountingInvoicesKPI.AmountInLocalCurrency_DMBTR,
0

)

FROM AccountingInvoicesKPI LEFT OUTER JOIN ( / Vendors may contain multiple addresses that may produce multiple VendorsMD records, pick the name agaist latest entry / SELECT Client_MANDT, AccountNumberOfVendorOrCreditor_LIFNR, NAME1 FROM PROJECTID.CTX_REPORTING_april_6.VendorsMD WHERE ValidToDate_DATE_TO = '9999-12-31' ) AS VendorsMD ON AccountingInvoicesKPI.Client_MANDT = VendorsMD.Client_MANDT AND AccountingInvoicesKPI.AccountNumberOfVendorOrCreditor_LIFNR = VendorsMD.AccountNumberOfVendorOrCreditor_LIFNR LEFT OUTER JOIN PROJECTID.CTX_REPORTING_april_6.VendorConfig AS VendorConfig

CORTEX-CUSTOMER Vendor Name in the config follows the format 'ZVENDOR{VendorId}'. Please change the logic if the name follows a different format.

ON VendorsMD.AccountNumberOfVendorOrCreditor_LIFNR = ARRAY_REVERSE(SPLIT(VendorConfig.NameOfVariantVariableNAME, ''))[SAFE_OFFSET(0)] LEFT JOIN PROJECTID.CTX_REPORTING_april_6.PurchaseDocumentsHistory AS POOrderHistory ON AccountingInvoicesKPI.Client_MANDT = POOrderHistory.Client_MANDT AND AccountingInvoicesKPI.PurchasingDocumentNumber_EBELN = POOrderHistory.PurchasingDocumentNumber_EBELN AND AccountingInvoicesKPI.ItemNumberOfPurchasingDocument_EBELP = POOrderHistory.ItemNumberOfPurchasingDocument_EBELP AND AccountingInvoicesKPI.FiscalYear_GJAHR = POOrderHistory.MaterialDocumentYear_GJAHR AND AccountingInvoicesKPI.ObjectKey_AWKEY = CONCAT(POOrderHistory.NumberOfMaterialDocument_BELNR, POOrderHistory.MaterialDocumentYear_GJAHR) LEFT JOIN CurrencyConversion ON AccountingInvoicesKPI.Client_MANDT = CurrencyConversion.Client_MANDT AND AccountingInvoicesKPI.CurrencyKey_WAERS = CurrencyConversion.FromCurrency_FCURR AND AccountingInvoicesKPI.PostingDateInTheDocument_BUDAT = CurrencyConversion.ConvDate) ;

dotinfinity commented 1 year ago

Hi @chodankarcc , I copied your query and replaced the datasets with my own S4 test deployments, and it executed just fine. I did notice that your target project to be PROJECTID in the query you've copied, which is very weird (and incorrect). Can you double check if you've set all projects correctly in your deployment parameters? See here for details.

Also, it may help if you can post the full error message here if it's available.

chodankarcc commented 1 year ago

I had purposely given PROJECTID in github comment here, in order not to expose project in public forum.

I ran that query again and I am getting same error like below in BQ

Query error: Scalar subquery produced more than one element at [2:1]

Is there a way if we can connect over quick meeting?

chodankarcc commented 1 year ago

@dotinfinity @Lsubatin Can you please suggest some workaround for this issue in Cortex 4.2? I can not go back to Cortex 4.1 as it will introduce same recordstamp issue again as mentioned in https://github.com/GoogleCloudPlatform/cortex-data-foundation/issues/25

Lsubatin commented 1 year ago

Hi @chodankarcc , without the full error message or the ability to reproduce the error, we can't help troubleshoot further.

Issue #25 technically works as documented, templates should be adapted for your specific deployment in all versions, and the one touch query has been there since 4.0, so keeping 4.1 as your stable branch should have the same or similar modifications. This is why I recommended tagging customizations too, so you can visibly keep what you need for your specific setup.

For this issue, we'd need the command you used for build, the config.json and the materializer settings to try and reproduce it, as I'm not getting that error either. The full error would help and I'm assuming you're deploying from all latest tagged commits. If you are uncertain, please pull recursively with rebase again. Since I'm also not getting the error with your query, it may help to understand what the sources are (e.g., has AccountsPayable been migrated form a function into a table? is currency conversion migrated too?). Conversely, as mentioned in the documentation, set turbo mode to false so you can track deployment errors individually.

chodankarcc commented 1 year ago

@Lsubatin As we were facing issue with Cortex 4.2, we used previous version Cortex 4.1 and made changes to SAP/SAP_REPORTING/dependencies_s4.txt and SAP/SAP_REPORTING/s4/OrderToCash.sql in order to remove 'OneTouchOrder' reference mentioned in https://github.com/GoogleCloudPlatform/cortex-data-foundation/issues/25 to avoid recordstamp issue. This build ran successfully. I am going to go ahead and run Looker dashboards on it.

Please find command which I used for both Cortex 4.1 (successful) and Cortex 4.2 (failed) run:

gcloud builds submit --project PROJECTID \ --impersonate-service-account SERVICEACCOUNT \ --substitutions \ _PJID_SRC=PROJECTID,_PJID_TGT=PROJECTID ,_DS_CDC=CORTEX_RAW_LANDING,_DS_RAW=CORTEX_RAW_LANDING,_DS_REPORTING=CTX_REPORTING_april_6_cortex41,_DS_MODELS=CTX_ML_MODELS,_GCS_BUCKET=LOGS_BUCKET,_TGT_BUCKET=DAGS_BUCKET,_SQL_FLAVOUR=s4,_TEST_DATA=true,_DEPLOY_CDC=false,_GEN_EXT=true,_DEPLOY_SAP=true,_DEPLOY_SFDC=false

PROJECTID - our GCP project id SERVICEACCOUNT - SA used LOGS_BUCKET - Logs bucket DAGS_BUCKET - DAGs bucket We used different dataset for _DS_REPORTING with Cortex 4.1 and Cortex 4.2 runs.

Also I had to fix PricingConditions view for handling column 'Checkbox_KDATU' parse issue for 00000000 string. I see that fix was there in my old deployed view using cortex.

Lsubatin commented 1 year ago

Hi @chodankarcc , your contact in the sales team will reach out directly to help you gather the rest of the requested information.

Lsubatin commented 1 year ago

I understand this has been addressed with the CE team, please re open if that is not the case.