Date Calculations (WTD/MTD/YTD) in SAC using SAP Data Warehouse Cloud Live Connection

[sukanya_krishnan]

SAP DWC has inbuilt data integration editor - "Data Builder" that helps to blend data from sources and produce Analytical Datasets. This can be consumed by SAP Analytics Cloud. We can create graphical and SQL views in the data builder which can be further embedded and nested in other consumable views. If DWC and SAC are in same tenant, they are automatically linked and the components in specific DWC space can be consumed in SAC stories.

Use case: Requirement is to fetch WTD,MTD and YTD values based on the input date selection. Let's consider the "Sales"  graphical view explained in the "Data Warehouse Cloud - Tutorial series" . Please refer the tutorial series here

https://www.youtube.com/playlist?list=PL48QrZZyMuAbDlcwtVxFL-KFSveNyAJ3q to understand the steps to create view in DWC

• We have Sales view developed based on Sales Order Header/Line Item and supporting master data tables like Product; Customer; Sales Person; City

• We need restricted data set based on the order date selection in Sales Order Header. For example -
  
  
  
  • If input date selected in SAC story is 2/21/2020 - MTD revenue value should consider sales orders from  2/1/2020  - 2/21/2020
  
  
  • WTD being 2/16/2020 - 2/21/2020
  
  
  • YTD considering 1/1/2020 till 2/21/2020
  
  
  
  


• Below is snapshot of Sales Order Header table data

Solution:

• Create SQL view as below, restricting the Sales Order Header based on the input date and date range parameter selections. We have Date table in DWC that stores the WTD/MTD/YTD values for each calendar date - for both current and previous years.

• Leverage above SQL view in the final consumption view in place of "Sales_Order_Header" table. Map input parameters accordingly. Semantic usage in final view should be selected as "Analytical Dataset" and "Expose For Consumption" should be turned on.

• Leverage final consumption view in SAC story  - Create DWC connection ; Fetch data from DWC connection by selecting the right DWC space

• Input Parameters in DWC can be accessed from story filters in SAC. Based on the input date and range selection, corresponding data would be fetched. In the below example, story is refreshed for input date - 3/31/2020 and range YTD

• Changing the date range to MTD fetches data as shown below

• Same view can be leveraged in Analytics Designer by choosing DWC Analytical Dataset as source.

• By default date input can be set to current date and MTD as default range selection

var current_date = new Date(Date.now());

Table_1.getDataSource().setVariableValue("DT_RNGE",RadioButtonGroup_1.getSelectedKey());

Table_1.getDataSource().setVariableValue("IP_DT",current_date.toLocaleDateString());

Table_1.getDataSource().openPromptDialog();

• Selecting date as 3/31/2020 with MTD as range parameter, below is the output

• Switching to YTD in radio button - data is fetched from 1/1/2020 till 3/31/2020


• Code snippet on radio button click


• Table_1.getDataSource().setVariableValue("DT_RNGE",RadioButtonGroup_1.getSelectedKey());

We have now created a story and analytical application, by using DWC as source live connection and DWC view having Input Parameters to fetch week to date ,  month to date and year to date values. This can be enhanced further to fetch the Previous Year WTD/MTD/YTD values as well based on single date selection.