looker-open-source/block-cortex-sap

Cortex - Order to Cash and Finance

Google Cloud Cortex Framework for SAP

What does this Looker Block do for me?

Gain faster insights into your Order to Cash, Finance, and Inventory data with these Dashboards and Explores based on the SAP Cortex Data Foundation. Leverage or customize this Looker model to:

• Identify trends and patterns in your data
• Spot potential problems early on
• Make better decisions faster

Included Dashboards by Subject Area

Order to Cash

• Orders Fulfillment - Monitor current delivery status, highlight late deliveries and compare pending deliveries with current stock.
• Order Snapshot - Monitor the health of the orders including product delivery efficiency.
• Order Details - See order details including status.
• Sales Performance - Review the sales performance of products, divisions, sales organizations and distribution channels.
• Billing and Pricing - Review price variations by customer and product.

Finance

• Accounts Receivable - Analyze total receivables, overdue receivables, days outstanding, and top companies with highest receivables.
• Accounts Payable - Find financial information such as accounts payable, accounts payable turnover, overdue payables, accounts payable aging, and cash discount utilization.
• Vendor Performance - Analyze vendor performance including delivery, lead time, price variance, purchase order status.
• Spend Analysis - Review Key Performance Indicators (KPIs) like total spend, active vendor count and cleared invoices. Breakdown spend by purchase organization, purchase group, vendor country, and material type.

Inventory

• Inventory Management - Review inventory value over time (in total and by material type). Highlight other important KPIs including:
  • Inventory Turn
  • Days of Supply
  • Obsolete Inventory Value
  • Slow Moving Inventory Value

Balance Sheet

⚠️ available beginning with Google Cloud Cortex Framework 5.3.

View the balance sheet as of a selected fiscal period, compared to the same period last year, the previous fiscal period, or a different fiscal period. And display 3-levels of the selected hierarchy for a user-specified chart of accounts and company. Two dashboards using different table styles are included.

• Balance Sheet Marketplace Report - This report uses an easy-to-read table style designed for finance reports. To use this dashboard, a Looker Admin must install the Report Table plug-in/visualization type from Looker Marketplace. Refer to Looker documentation for Using Looker Marketplace.

• Balance Sheet Subtotals Report - This report uses Looker's built-in table visualization with subtotals (no special installation required).

Required Data

Get the required BigQuery datasets for this block by following the installation instructions for Google Cloud Cortex Framework.

Installation Instructions

Manually install this LookML Model following one of the options below.

Option A: Marketplace Install via Git

Refer to the Looker documentation for Installing a Tool from Marketplace. Provide values for the required prompts as outlined in next section Required Parameters.

Option B: Manual Install via Fork of this Repository

• Fork this GitHub repository
• Create a blank LookML project with any name (e.g., cortex_sap_operational)
• Connect the new LookML project to the forked repository
• Update the values of constants in the manifest.lkml file as described in the next section Required Parameters
• Commit and deploy changes to production

With the Looker project based on your forked repository, you can customize the LookML to fit your unique business needs.

Required Parameters

> ⚠️ These required values are configured during the Marketplace Installation process, or if this Block was installed from a forked Git repository, you will update the values for these constants in the `manifest.lkml` file for the project.

• Connection: Value of the BigQuery CONNECTION_NAME allowing Looker to query the Cortex REPORTING dataset. Note, to use Balance Sheet reporting Persistent Derived Tables must be enabled.

• GCP Project ID: The GCP project where the SAP reporting dataset resides in BigQuery (i.e., GCP project ID). Identifying Project ID.

• Reporting Dataset: The deployed Cortex Data Foundation REPORTING dataset where the SAP views reside within the GCP BigQuery project.

• Client: The SAP Client number (mandt) to use for Reporting.

Required User Attributes

Dashboards require four Looker user attributes to work properly.

A Looker Admin should create the following user attributes and set their default values.

⚠️ Name each user attribute exactly as listed below:

Required User Attribute Name	Label	Data Type	User Access	Hide Value	Default Value
default_value_currency_required	SAP Default Currency to Display	String	Edit	No	USD or desired currency like EUR, CAD or JPY
client_id_rep	SAP Client Id (mandt) for Reporting	String	Edit	No	Enter your SAP Client ID or 100 if using the provided test data
sap_use_demo_data	SAP: Use Demo Data (Yes or No)	String	Edit	No	Enter Yes if using the provided test data. Otherwise, enter No
sap_sql_flavor	SAP: SQL Flavor (ECC or S4)	String	View	No	Enter ECC or S4 as required for your SAP system

Each dashboard user can personalize these values by following these instructions.

Other Considerations

• Persistent Derived Tables: Required for using Balance Sheet dasbhoards. And if using this block with production data, you may want to convert some derived tables to Persistent Derived Tables (PDTs) to improve query performance. Ensure your BigQuery Connection has enabled PDTs, then update any derived table syntax with the desired persistence strategy.

• Locale: The Looker user locale setting (as seen in account profile) maps to SAP language code for BalanceSheet, Materials_MD, Vendor Performance, and Inventory Metrics Overview views and determines material text language. See language_map for details.

• BI Engine Optimization: Some calculations perform better with BI Engine Optimization enabled in BigQuery.

• Liquid Templating Language: Some constants, views, explores and dashboard use liquid templating language. For more information, see Looker's Liquid Variable Reference documentation.

• (Optional) Unhide additional dimensions and measures: Many dimensions and measures are hidden for simplicity. If you find anything valuable missing, update the field's hidden parameter value No in the relevant views.

Additional Resources

To learn more about LookML and how to develop visit:

• Looker Best Practices
• Looker/Google Cloud Training

block-cortex-sap's Issues

Datasets and Accounts Receivable Dashboard

Hello, we have two questions:

1- The required customizations are three datasets called "OTC_DATASET", "FINANCE_DATASET" and "FUNCTIONS_DATASET". However, when installing Google Cloud Cortex Framework (https://github.com/GoogleCloudPlatform/cortex-data-foundation) these datasets do not exist. We were able to view the dashboards using the reporting dataset from Google Cloud Cortex Framework on the "OTC_DATASET", "FINANCE_DATASET" and "FUNCTIONS_DATASET" parameters (i.e using the same dataset on all the parameters), but is this the correct configuration?

2 - On the [SAP OTC AR] 06: Accounts Receivable dashboard, one of the visualizations is encountering a query error. Below is the query:

-- raw sql results do not include filled-in values for 'data_intelligence_ar.Current_Date'
SELECT
(DATE(current_date() , 'Indian/Christmas')) AS data_intelligence_ar_current_date_1
FROM cortex_test_reporting.AccountingDocumentsReceivables
AS data_intelligence_ar
WHERE (data_intelligence_ar.Client_MANDT = "050" )
GROUP BY
1
ORDER BY
1 DESC
LIMIT 500

And below is the error:
Query execution failed: - No matching signature for function DATE for argument types: DATE, STRING. Supported signatures: DATE(TIMESTAMP, [STRING]); DATE(DATETIME); DATE(INT64, INT64, INT64); DATE(DATE); DATE(STRING) at [2:6]

Is this a problem related to the query or some configuration is missing?

Looker dashboards not available on Marketplace, not working from Github

Hi Team,

We have had downloaded 'Cortex Data Foundation for SAP - Operational Dashboards' from Looker Marketplace and it was working fine. Today while we updated to Cortex 4.0, we found that 'Cortex Data Foundation for SAP - Operational Dashboards' is no more on Looker Marketplace to install.
While trying to update from Github facing the issue

Could not install listing: Unable to load marketplace project. Invalid target uri: https://github.com/looker-open-source/block-cortex-sap.git. Original Message: Error resolving prototype. Problem cloning remote dependency: Initial checkout from ref: '2ee4e8f' failed. Make sure your ref is a valid commit sha.. Check your manifest.

Could you please help with the issue.

Demand Sensing Data is not Visible in Looker Dashboard

HI

I have deployed Demand sensing test data from SAP and i am not able to see data in Looker Dashboard.
Please find the below Input details which i deployed :

Forecast Horizon (weeks) :13
Content Window (weeks): 52
Model Training Budget (hours): 1
Deploy Test data : Yes
Mandt Client : 900

Could someone help me why i am unable to see data in Looker Dashboard for Demand sensing.


I have deployed Demand sensing and i have data set. Please find the attachement and i cannot see any data in Looker

Add refinements

Would any of the contributors kindly add a 'refinement' file properly included on the model file?

This would be an enormous progress to this block.

LookML error: "Model Not Found" when opening the dashboards

Hello! I set up the Cortex SAP Block in our Looker Project. I have added the correct values in manifest and added the user attributes need for this block. But when I open the dashboard, all the charts is getting LookML Error. I'm not sure what's causing the error tho.

Thanks!

Add AUD to OTC Currency Dropdown

Update Data_Intelligence_OTC.view.lkml to change Currency_Required to the below.

parameter: Currency_Required{
    type: string
    allowed_value: {
      label: "AUD"
      value: "AUD"
    }
    allowed_value: {
      label: "USD"
      value: "USD"
    }
    allowed_value: {
      label: "EUR"
      value: "EUR"
    }
    allowed_value: {
      label: "CAD"
      value: "CAD"
    }
    allowed_value: {
      label: "JPY"
      value: "JPY"
    }
  }

Not able to access the looker dashboard plugins from Looker

I am not able to see any cortex related plugins in Looker marketplace. Is there an issue or any other way to download the looker dashboards in our instance.

Cortex Operational Dashboards issues

Team, We have had downloaded 'Cortex Data Foundation for SAP - Operational Dashboards' from Looker Marketplace and it was working fine. Today when we were unable to update BQ dataset name. We found that 'Cortex Data Foundation for SAP - Operational Dashboards' is no more on Looker Marketplace to install. Can you please check on this?

Deployed Demand Sensing Dataset but unable to see data in Looker

HI

I have deployed Demand sensing test data from SAP and i am not able to see data in Looker Dashboard.
Please find the below Input details which i deployed :

Forecast Horizon (weeks) :13
Content Window (weeks): 52
Model Training Budget (hours): 1
Deploy Test data : Yes
Mandt Client : 900

Could someone help me why i am unable to see data in Looker Dashboard for Demand sensing.

Issue with Looker Trial and PDT set up

Hi Team,

I have set up a Looker Trial version from Google Console with 30 days validity. I have created the instance and set up the cortex foundation. First issue : The Marketplace is not enabled in the trial version. However I have downloaded from Git and uploaded it . The dashboards work, with one issue of not being able to create the PDT.

I get the error , "Can't construct persistent derived table language_map, connection cortex could not be registered". Any lead would be helpful.