End-to-end SAS Integration for Dashboard design using SAS DI and Visual Analytics

[ravijangra2014]

Scenario: In this scenario, you will see the steps for end-to-end integration. Initially, the required data is fetched from SAP tables and being stored in the SAS tables created in the library. 

Further, we're running jobs and utilizing this data from tables and storing final data in the SAS target tables located in another library. 

Lastly, this target library is associated/linked with SAS Visual Analytics (SAS VA) server so that these tables can be accessed in the dashboards/reports. 
 

Perquisites: 

• You must be logged in to the SAS server and require the READ/WRITE accesses of SAS libraries. 
• Target library in SAS must be linked/associated with the SAS VA library. 
• READ/WRITE accesses to SAS VA to create dashboards and access the desired data from tables. 
• Knowledge of SAS Programming language is required in case if you write the custom code. 
   

Tools Used: 

• SAS DI: SAS Data Integration Studio, a tool like base SAS editor for coding and reporting purposes. You can call it a drag and drop tool which is equipped with many inbuilt functions, macros, transformation, loaders, etc. 
• SAS EG: SAS Enterprise Guide is a tool that you can use to write and run SAS code or you can say it is a Windows-based point and click graphical user interface which can also be used to create and execute SAS programs. The SAS Enterprise Guide is a data analysis tool that allows you to manage and join multiple data sets in order to facilitate combined analysis. 
• SAS VA: SAS Visual Analytics is an easy-to-use and web-based tool that provides a single application for reporting, data exploration, and analytics visualizations. 
• Database/3rd-party system(optional) 

STEP1: Registering SAP tables in the library in SAS DI. 

Navigate to the Inventory -> Library -> SAP associated library -> register table. 

Check for the below details and modify if required. 

Till here, the selected tables will be registered in the library which can be further opened/viewed their data in order to verify the registered tables in the SAS DI tool. 

Step2: Creating Jobs in SAS DI. 

Navigate to the Inventory -> Job -> right-click for New Job 

Choose the name of the job and your preferred location to store this. 

A job is an object of SAS DI where you can drag and drop the desired tables and transformations, configure, connect them, and set the order of execution (set this order in the Control Flow tab in Details in the job) of all transformation elements. 

Whenever you create a new job in SAS DI and add any elements i.e., tables, transformation elements, etc., a piece of code is automatically generated in the backend accordingly which can be checked but not modified under the Code section right to the Diagram section in the Job. Next, you can also check the logs and output after executing/running the job. 

We have used EXTRACT transformation which is an SQL Standard operation. In this case, the Extract operation is being performed on the MARA table registered in the previous step. 

Some configurations i.e., where, Group by, Order by, Mappings between Source and target table fields, etc. can be found in properties of the SQL transformations. 

User Written Code under Data in transformations is used to write a custom code using SAS Programming language. Most of the SAS developers would rather use the User Written code as it provides more flexibility and fast execution. 

In properties of User Written Code, custom code would be written in the place of comments shown in the below screenshot. In order to start writing and testing your SAS code, you can use the SAS Compiler also known as SAS EG (Enterprise Guide is shown in the next step). 

Below is the target table is shown which will store the data after executing the transformation. Each transformation has a work table by default which further can be replaced with a permanent table registered in the SAS libraries. 

 

Step3: Demo code in SAS EG tool. 

Below is the sample code that is shown in the above screenshot.

/* Assigning Libref SAPR3QA library */ 
LIBNAME SAPR3QA SASIOSR3  language=EN  Client=*****  trace=0  BatchMode=0  sysnr=00  ashost=*****  NETWEAVER  AUTHDOMAIN=*****;
 
/* Assigning Libref PCRTGT library */
LIBNAME PCRTGT BASE "/sasdata/PCR_Target_Data";
 
/* Assigning Libref PCRSTG library */
LIBNAME PCRSTG BASE "/sasdata/PCR_Staging_Data";
 
proc sql;
create table PCRSTG.blog1_table AS
SELECT MAIN_COM_REV_EFFDATE, LIFNR_DS, MATNR, WERKS, MAKTX, CREATED_BY, EBELN, NAME1_DS, TOTAL_COST_PER_UOM, APRL_STATUS
FROM SAPR3QA.ZMMOEPIR_RVD_HDR
WHERE MAIN_COM_REV_EFFDATE >= '01apr2019'd
;
QUIT; 

Step4: Creating Report/Dashboard in SAS Visual Analytics. 

Open SAS Visual Analytics - Explore and Visualize Data page -> New Report -> Add Data Source. 

Assuming that the target table/library of SAS DI is already linked to the SAS VA Repository so that all the tables in the target library in SAS DI are visible here in the SAS VA Data source. We need to load the required tables into the memory by right-clicking on the particular table on the left side of the below screenshot where detailed information is shown on the right side of the page. 

All the loaded and ready-to-use tables can be checked in the Available tab. 

  

On the left side of the page, different objects and data items like various tables, graphs, text/input items, categorized fields, measures, etc. are available to drag and drop into the workspace. 

Their properties can be configured on the right section on the page i.e., layout and visualizations, linking with other objects, filtering, roles, etc. 

This was all about the end-to-end integration between SAP and SAS DI and presenting data in the visualized form in SAS Visual Analytics. 

[JuanS_OCS]

Thanks @ravijangra2014 , perhaps you would like to create is as article in https://communities.sas.com/t5/SAS-Communities-Library/tkb-p/library ?

[ravijangra2014]

Thanks, yeah as an article for end-to-end integration might be a new one so I posted this as a New Topic.

[Kurt_Bremser]

Your topic is not a question, but a helpful article.

What @JuanS_OCS means: articles need to be posted in the Communities Library, not as a topic in another community.

In particular, the "Community Matters" forum is for issues concerning the working of the SAS Communities themselves.