End to End Scenario of SAP -BW on HANA-Web Intelligence Reporting...!!!

Here is a document which will give you step by step guidance on how to load data from SAP ECC to SAP BW on HANA system and then perform reporting based on the BW BeX queries in Web Intelligence Reporting Tool

This document is basically for beginners in Business Objects , who are working in SAP Business Warehouse and have to create reports on Web I

The basic purpose of this document is to convert the Bex Reports into Web I Reports for more enhanced features and good look and feel of the reports.

 

I. Architecture Diagram

 

I. Step-By-Step Development Process

A. ECC-BW ON HANA REPLICATION (LO Extraction)

1. Login

 

                 Steps  to extract data from ECC to BW on HANA system through LO Extraction

1. To login Start à SAP LOGON àSelect the ECC Systemà Enter the relevant username and password.

NOTE:    To get the data from SAP ECC to SAP BW system , we perform Extraction Methodology

              There are  various types of Extraction methods as specified below:

• Application Specific --BW Content FI, HR, CO, SAP CRM, LO Cockpit
• Customer-Generated Extractors -LIS, FI-SL, CO-PA
• Cross Application (Generic Extractors)  - DB View, InfoSet, Function Module

              In this document we will be using application specific method  ,to extract the Sales data by using LO Extration Method.

 

1. Go to /NRSA6 ---> SAP-R/3 ---> SD

               Check if the needed data sources are activated

               Here we will be replicating datasource for Delivery Item Data 2LIS_12_VICTM

              

                 If the datasource is not visible  in /NRSA6 (datasource is not active)

  Go to /NRSA5 and activate the required data source.

Go to --> /NRSA3  --> Extraction

               To check whether the extraction is done properly

              

                Before performing extraction make sure to delete and fill the setup tables,

                Please  go through the below link for more details of Lo Extraction Steps

                Lo Extraction step by step guide

 

 

                  For Replicating the data from ECC to BW system

1. Go to SAP LOGON ----> BW LOGIN -----> ENTER CREDENTIALS

Once the datasources are activated in ECC system , Go to BW on HANA system and type RSDS

               Provide the Data Source name that needs to be replicated and the ECC system name from where it is been replicated.

               Here 2LIS_12_VCITM is data source and ECW_00_800 is source system.

              

1. Click DATASOURCE ----> REPLICATE DATASOURCE

Once the replication is done successfully, a message pops up at left hand corner  status bar as shown in the below screen

A. BW ON HANA DATA MODELLING

1. Data Flow

               Once the replication is performed successfully

               Go to /NRSA1 (Data Modeling Screen)

              

             

 

                It displays Data Warehousing Workbench screen

1. INFOPROVIDER ---> INFOCUBE ---> Create dataflow for load from newly replicated Data source

1. INFOPROVIDER ---> Right click INFOCUBE ---> MANAGE

1. INFOCUBE CONTENT ---à Check for data in INFOCUBE is relevant

A. REPORTING ON BEX

 

               To Create BeX Query

               Type /NRRMX

              

               An Excel sheet will get displayed

               Go on the Add-Ins Tab

              

                 NOTE : BEx Analyzer is an analytical reporting and design tool

1. BeX ANAYLZER ---> TOOLS ---> CREATE NEW QUERY

                BEx Designer will get opened

1. Click QUERY ---> NEW

NOTE : BEx Designer is used to analyze the data set of the BI system by defining queries for Info provider.

 

              Select the Info Area where the Info Provider is prepared on which the Query has to be build.

              In this case, SALES OVERVIEW is Info cube. Click OPEN

             

               In this document we are preparing a BeX Query on Sales Data.

              Drag and Drop the required dimensions and key figures from left panel to the middle panel of rows and columns respectively.

             

              NOTE: AVG_SUB is a calculated key figure as ( (Subtotal 1+Subtotal 2)/2)

              

              Once the columns and rows are selected.

              Go to properties tab on right side panel and select the Query_Name.

              Select the Extended tab and check all the check boxes under this tab

             

              NOTE : This will create a .mdx file , which will be then used to call in the BO reporting tools. Multidimensional Expressions (MDX) is a query language for                                    OLAP databases.

 

               Once the Query is created and saved

1. Click BeX ANALYZER ---> OPEN QUERY

1. Select QUERY_NAME ---> OPEN

                 In this case, Material sales overview query is query used.

                 The Query output will get displayed.

A. REPORTING ON WEBI

 

                 We call the same BeX Query in Web Intelligence Tool. This tool allows analysis on BEx query.

1. Go to START ---> All Programs-->SAP Business Intelligence--> SAP Business Objects BI Platform 4 Client Tools --> WEB INTELLIGENCE RICH CLIENT

1. Go to Web Intelligence --> Login as

1. Provide with relevant User ID and Password --> Log On

Click on

1. Select BEX --> OK

1. Click BW7.4 --> INFOAREA

               BW7.4 is the already existing connection wherein we connect to BW system.

                Info area are the same which were created in BW System.

             

              

                NOTE: BW 7.4 connection is already done, if the connection is not visible, please contact the administration team

              

1. Select the appropriate Query --> OK

               Here we have selected Material sales overview query

             

              

               Once the Query is selected, a Query panel window is popped up, where the required dimensions and the measures (key figures) can be selected from right                      side panel to the Result Objects window.

               Drag and drop required dimensions or measures to Result object. We can also apply filters by dragging to Query Filters.

              

 

                 Before executing the query, we can preview the data content in the Data Preview tab by clicking on refresh button.

              

 

               Once the Data is properly previewed, click on Run Query to generate a Report

              

SAP HANA for Basis Administrators - A cook book available in PDF for FREE... Request and get your copy...

SAP HANA for Basis Administrators - A cook book available in PDF for FREE... Request and get your copy...

- Basha Shaik

Reorg / Drop / Automating Clenanup of Tables/Views/Table Types of a Schema in SAP HANA.

There was a use case to clear a specified schema in a Test HANA system. And the schema was having good number of tables, table types and views. And writing DROP statement manually was a lengthy task.  In order to automate this task, I thought of writing a stored procedure in HANA. Stored procedure should also have displayed a message on deletion or completion of task. Below are details about stored procedure and usage of it.

1.1 Summary -

  

    Document illustrates a SQL Script stored Procedure, implemented in SAP HANA (SP09), to delete various objects (Tables/Views/Types) of a Schema. Objects Technical names are Case sensitive.

1.2 Description –

Stored procedure has Input Parameters Schema and Object Type (TABLE/TYPE/VIEW). Values of Object Type should be given in upper case.  Stored procedure has Output Parameter which displays a list of objects deleted.

1.3 Procedure SQL Script code –

/* *********************************************************************** **

** PROCEDURE: TEST_DEL_TAB_WRAPPER                                         **

** Author : Basha Shaik, Client: Defense,  May 12, 2015 11:28                  **

** DESCRIPTION:                                                            **

** Procedure has input parameters SCHEMA and OBJECT TYPE                   **

** Procedure reads tables SYS.OBJECTS and SYS.TABLES                       **

** Dynamic SQL statement performs deletion                                 **

** *********************************************************************** */

/********* Begin Procedure Script ************/

BEGIN

declare lv_count integer := 0;

declare lv_init integer := 1;

declare lv_tabnm1 nvarchar(100);

declare lv_schema1 nvarchar(50);

declare lv_obj_type nvarchar(50);

declare lv_full_tabnm varchar(100);

CREATE LOCAL TEMPORARY TABLE #OP_TEXT_1 ( TEXT_OP NVARCHAR(100));

if :ip_obj_type = 'TABLE' then

       p_sys_objects =

       select distinct T1.SCHEMA_NAME, T1.OBJECT_NAME, T1.OBJECT_TYPE from

       (select distinct SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE

       from SYS.OBJECTS

       where

       SCHEMA_NAME = :ip_schema and OBJECT_TYPE = 'TABLE'

       order by SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE) T1

       inner join

       (select distinct SCHEMA_NAME, TABLE_NAME, TABLE_TYPE, IS_USER_DEFINED_TYPE

       from SYS.TABLES

       where

       SCHEMA_NAME = :ip_schema and IS_USER_DEFINED_TYPE = 'FALSE'

       order by SCHEMA_NAME, TABLE_NAME, TABLE_TYPE, IS_USER_DEFINED_TYPE) T2

       on T1.SCHEMA_NAME = T2.SCHEMA_NAME

       and T1.OBJECT_NAME = T2.TABLE_NAME;

elseif :ip_obj_type = 'TYPE' then

       p_sys_objects =

       select distinct T1.SCHEMA_NAME, T1.OBJECT_NAME, 'TYPE' as OBJECT_TYPE from

       (select distinct SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE

       from SYS.OBJECTS

       where

       SCHEMA_NAME = :ip_schema and OBJECT_TYPE = 'TABLE'

       order by SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE) T1

       inner join

       (select distinct SCHEMA_NAME, TABLE_NAME, TABLE_TYPE, IS_USER_DEFINED_TYPE

       from SYS.TABLES

       where

       SCHEMA_NAME = :ip_schema and IS_USER_DEFINED_TYPE = 'TRUE'

       order by SCHEMA_NAME, TABLE_NAME, TABLE_TYPE, IS_USER_DEFINED_TYPE) T2

       on T1.SCHEMA_NAME = T2.SCHEMA_NAME

       and T1.OBJECT_NAME = T2.TABLE_NAME;

elseif :ip_obj_type = 'VIEW' then

       p_sys_objects =

       select distinct SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE

       from SYS.OBJECTS

       where

       SCHEMA_NAME = :ip_schema and OBJECT_TYPE = 'VIEW'

       order by SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE;

end if;

select count(*) into lv_count from :p_sys_objects;

op_param = select distinct SCHEMA_NAME, OBJECT_NAME,

ROW_NUMBER() OVER  (partition by SCHEMA_NAME, OBJECT_TYPE order by OBJECT_NAME) asrow_num,

:lv_count as TOT_COUNT, OBJECT_TYPE from :p_sys_objects;

if :lv_count > 0 then

while (:lv_init <= :lv_count)

do

       select distinct SCHEMA_NAME into lv_schema1 from :op_param

       where

       row_num = :lv_init;

   

       select distinct OBJECT_NAME into lv_tabnm1 from :op_param

       where

       row_num = :lv_init;

   

       select distinct OBJECT_TYPE into lv_obj_type from :op_param

       where

       row_num = :lv_init;

   

       if (:lv_schema1 is not null and :lv_tabnm1 is not null) then

              insert into #op_text_1 select 'Deleted: '|| :lv_obj_type || ' '||'"'||:lv_schema1||'"'||'.'||'"'||:lv_tabnm1||'"' as TEXT_OP from dummy;

              select 'DROP '|| :lv_obj_type || ' '||'"'||:lv_schema1||'"'||'.'||'"'||:lv_tabnm1||'"' into lv_full_tabnm from dummy;

              EXEC :lv_full_tabnm;

   

       end if;

   

       lv_init := :lv_init + 1;

end while;

end if;

if :lv_count = 0 then

       insert into #op_text_1 select 'No Entries Found' as TEXT_OP from dummy;

end if;

OP_TEXT = select TEXT_OP from #OP_TEXT_1;

drop table #OP_TEXT_1;

END;

  /********* End Procedure Script ************/

1.4 Test Results –

A. Schema TEST has tables and views as below –

  TABLE1, TABLE2, Table3 & VIEW1

B. Execute procedures

1.4 Result –

Tables and Views deleted from Schema.