SAP BO Webi report development on HANA views
SAP BO Webi report development on HANA views:
This document explains step by step creation of Hana views, Universe in IDT and development of Webi report on top of the Hana views universes. This document will give understanding of the complete development/creation of end to end scenario from Hana views to report.
In this scenario we are considering Purchase application flow and analysis of report for purchase users assuming that Purchase related data extracted thru SLT from ECC system.
Applications & Versions used for this scenario:
SAP Hana Studio 2.0.7
Information Design Tool 4.1
SAP BO Webi 4.1
This Document explains below major topics:
- Creation of Attribute View
- Creation of Analytical View
- Creation of calculation View
- Creating and Publishing universe using IDT (Information Design Tool)
- Creation of Webi Report
Extracted Purchase order header, Item and account assignment tables and also Material, Vendor, Company code and G/L account master data tables in to HANA from ECC using SLT.
As a best practice need to follow the proper naming conventions for creating any views. (ex: Attribute view –starting with AT followed by table name)
Step 1. Creation of Attribute View:
Step 1.1. Select the respective package –> Right click –-> New à Select Attribute View
Step 1.2. Select the Mara table from the schema and drop it in Data Foundation in view and double click on the selected fields for output display.
Save and activate the view. Attribute view AT_MARA created on Material Master data table – Mara:
Similar way creates other attribute views and by selecting the respective master data tables from the schema, and enable the fields which we want to see in output in each view for below.
Vendor Master – LFA1 – Attribute View
Material Master – MARA – Attribute View
Company Code master – T001 – Attribute View
G/L Account master – SKB1 – Attribute view
Once all attribute views are created it displays as below:
Step 2. Creation of Analytical View:
Below Purchase order tables are from ECC thru SLT.
EKKO : Purchase Document Header
EKPO : Purchase order Item
EKKN : Purchase Data (Account Assignment)
Step 2.1. Right click on your package — Select New – Analytical View:
Enter the details
Step 2.2. Drag the EKKO table in to data foundation and AT_LFA1 and AT_T001 attribute views in to star join.
Join the appropriate fields of EKKO table with both attribute views based on the requirements.
EKKO.company_code = AT_T001.Company_Code
EKKO.vendor = AT_LFA1.Vendor
Step 2.3. In Semantics keep the Key fields, correct the Attributes and measures if required.
Save and activate the view.
Similarly create the other two Analytical views, all three analytical views will displays as below:
Step 3. Creation of Calculation View:
3.1. Right click on your package — Select New – Calculation View:
Drag two Aggregation nodes and select AN_EKKO and AN_EKPO analytical views in each node respectively.
Select required output fields in each Analytical view
3.2. Connect the two aggregation nodes to Join Node and connect the related common fields.
Drag the Projection node and select required fields.
3.3 Select the key fields and adjust the dimensions and measures.
Save and activate the view.
Right click on the view and select Data Preview, we can see the data output of calculation view CL_Purchase
Now Calculation view is ready need to create universe to expose this data thru webi report.
Step 4: Creating universe using IDT (Information Design Tool)
- 4.1. Logon to IDT, In Repository Resources under the respective system expand right on Connections Node.
Select ‘Insert Relational Connection’
Enter the connection name:
Click on Next, and then select the database middle ware drivers under SAP node
Click on Next, and then enter the User name, password details of Hana system and click on Test connection button.
Then click on Finish, Now connection established to Hana system.
Expand the Connections node there we can find the new connection ‘Purchase_Flow’.
- 4.2. Create project ‘Purchase_Flow’ by clicking on File à Newà Project and enter the project name.
Click on Finish. New project ‘Purchase_Flow’ created in Local Projects node.
4.3. Now select Purchase_Flow connection, right click and click on ‘Create Relational Connection Shortcut’.
It will popup Select a Local Project window; select our ‘Purchase_Flow project.
Relational Connection connected to project as shown below:
Select Project à right click à select New Click on ‘Data Foundation’
Enter the name of Data Foundation
Select Multi Source Enabled as IDT will support.
Check for the user authentication details..
Click on Next and select the connection which was created earlier.
Click on Next, displays the connection parameters.
Click on Finish.
Open the Purchase_Flow_DF, expand the connection node, select _SYS_BIC schema node where all Hana views columnar table will store. Select the CL_Purchase Calculation view table.
4.4. Now create Business layer. Select Purchase_Flow project Right click à New à click on Business Layer.
It displays the below pop up screen
Select Relational Data Foundation and click on Next.
Enter the Name and click on Next
Select the respective data foundation and click on Finish. There is check box if system will create classes and objects, if you want to create on your own need to un check this.
Purchase_Flow_BL business layer created as below, review dimensions and measures change if required.
Select change the data type if necessary
Save the Business Layer. We can check the query output by clicking on Queries tab.
5. Click on Insert Query, it will add displays the classes and objects of Business layer
Drag and drop required fields to click on refresh to check the data in first instance.
Close the query.
4.6. To publish as universe Right click on Purchase_Flow_BL business layer à Publish à To a Repository
Click on Next
Click on Next
Click on Finish, Universe published successfully.
Step 5: Creation of Webi Report :
Open BI Launch pad by entering the login credentials
Expand Application and select Web Intelligence
Click on New and select ‘Universe’ as data source.
Select our Universe from the list
Purcahse_Flow_BL universe will open in Query Panel as below:
Drag and drop required dimensions and measures in Result objects and if you want to filter any filed also.
Query filtered based on Vendor, Click on Run Query, pop up display to select Vendor to see the data.
Once required Vendors are selected click on Ok, report will display.
Change the Report Name as Purchase Data Flow Report
You can format the report with Refresh Date, page no, logo and chart as per requirement.
Great post Riyez! Thanks for sharing!
Wav... Fentastic documentation Riyaz!!!!
It is actually very detail and very much useful for beginner. Every step by step process including the pictorial presentation is superb..
All the best...
V.nice document Riyez.
Excellent Riyez!! This is an Fantastic document explains complete development/creation of end to end scenario from Hana views to report. Very Useful to the Beginners . Explanation with screen shots will makes this document more effective for the viewers. Thanks for sharing such a nice document.
Questions if you don't mind, I hope that can be answered quickly, is this best practice?30 reports created on HANA Views, each report has been built directly on a single HANA View per report
There is a universe but this is not being used for reports (?)
Complex calculations and measures are held at report level, each report has any number of these and they are manually replicated across reports
Report variances have been created, new report for each version even though only location is different
My understanding is that this is not best practice, it is anything but, however need input from someone who knows more.
Which of these methods is most recommended or has more advantages?
I find this post very interesting. I am just stuck on one thing-I am not able to create a new session in IDT. Can anyone help me on this?
I am providing the systemname:port but still its saying specify correct host and port 🙁
Any timely help will be highly appreciated.