Creating Business Layer (Universe) using Information Design Tool and optimize in SAP BO Web Intelligence

In this document, I will explain creating connection to HANA cube, publishing connection to SAP BO Repository, Data foundation, Business Layer and shortly, overview on SAP Web Intelligence 4.0.
The information design tool is an SAP BusinessObjects metadata design environment that enables a designer to extract, define, and manipulate metadata from relational and OLAP sources to create and deploy SAP BusinessObjects universes.

A universe is an organized collection of metadata objects that enable business users to analyze and report on corporate data in a non-technical language.

Universes enable the business users too freely and securely access, analyze, enrich and share information using familiar business terms.
Universes do not store data from HANA or add any performance overhead. Universes are just like any other client tool using SQL to access HANA and the latest data from HANA is sent to client tool on query refresh.

Procedure for creating Business layer (Universe)

1. Go to Start -> Programs -> SAP BusinessObjects 4 -> Sap BusinessObjects BI platform Client tools -> Information Design Tool

2. Create a New project. Select on File -> New ->  Project (Project can be shared for collaborative shared universe).

3. Name the project and specify the location of project.

And select on Finish.

4. Right click on newly created project and select New -> Relational Connection.

5. Name the connection and Click on Next.

6. As database driver, select “SAP -> SAP High-Performance Analytic Appliance (SAP HANA) 1.0 -> JDBC Drivers” Click Next.

7. Keep the Authentication Mode set to “Use specified username and password”
Enter your HANA username (SULABH_USER) and your password (xxxxxxx)
Enter the server connection ->  hostname:<port number>

Test the connection, if the connection test is successful, click “Next”

8. Click on Finish.

9. From the project folder, select the connection you just created. Right-click and select “Publish
Connection to a Repository

10. Log on to the BI 4 using the connection information provided by the instructor System: <hostname>:<port number>, Username: Administrator, Password: xxxxx, Authentication: Enterprise and Click on “Connect”

11. Select the repository folder where you want to publish the connection. Click on Next

12. Click on Yes and close

JDBC Connection is now defined on the BI 4.0 system and available to BI front end tools like SAP BusinessObjects Explorer.

13. Create a new data foundation within your project. Name the data foundation.

14. Select single source.

15. Select the secured connection you have previously created. It should have a file extension .cns and Click “Finish”

16. Expand the “_SYS_BIC”, and select “Sulabh/EFASHION_ANVIEW/OLAP”, double click on it. This analytic view will be added in the right panel. Save it.

This analytic view will be added in the right panel. Save it

17. Now, you should be able to see the data foundation we just created (i.e. SFLGHT_FOUN.dfx). Right click on the “SapFoxTest_HANA” project again to create the Business Layer.

18. Select the type of data source for the business layer as “Relational Data Foundation”. Click on Next.

19. Enter the name of business layer. Click on Next

20. Choose the data foundation we just created in the previous steps. Use the button to select the data foundation. Click on Finish. Now, the business layer is created

21. Now, the new business layer is created successfully. Before we publish it, we need to do one more thing. Expand the “Sulabh Efashion Anview OLAP”, Select the “Margin”. Right now, it shows as an attribute. We need to define an aggregation function for this field to make it to be a measure.

22. Right click on Margin, and choose “Turn into Measure with Aggregation Function/Sum” in the content menu.

Now, the icon of the Margin filed becomes the measure’s icon.

23. Right click on the business layer we just created and publish it to a repository by selecting “Publish/To a Repository…”

24. IDT shows the wizard and ask you to check the universe integrity. The “Check Integrity” button helps you checking the model. Click on “Next”. Now, the wizard asks for the location in the BOE server to store the business layer file. I saved HANA_SFLHIT.blx under folder SULABH_TEST_Unv in SAP BOBI 4.0 CMC. Click on Finish

25. The message dialog: “Universe published successfully” tells the universe is successfully published. Click on close.

Web Intelligence

Web Intelligence is used to generate interactive reports for data analysis, information discovery and Ad hoc reporting. Web Intelligence performs Querying, Reporting, Analyzing and Sharing information.

1. Go to Start -> Programs -> SAP BusinessObjects 4 -> Sap BusinessObjects BI platform Client tools -> double click on Web Intelligence Rich Client

2. Select on data source -> Universe (i.e. HANA_SFLIGHT)

3. Enter your User Identification to logon to SAP Business Objects Web Intelligence
System: xxxxx(host):xxxx(port)
User name: Administrator
Password:  xxxxx
Authencation: Enterprise

4. The editor opens to Query Panel, You can view data source i.e. HANA_SFLIGHT universe. Select your objects to display in report. (In this scenario I want to analysis Family Name, Category, City, Fiscal period, QTR. Margin, Amount Sold, Quantity Sold, and Sales Revenue from HANA_SFLGHT universe). So I added Attribute and Measures to result objects. Now , I run the query, the report looks like below:

Sulabh Bartaula 
SAP HANA certified consultant 
+358 40 4163 717 
sulabh.bartaula@sapfox.com

Creating Calculation View

In this document, I tried to explain “how to create Calculation view in SAP HANA studio”.

Calculation views are used to create your own data foundation using database tables, attribute views, and analytic views to address a complex business requirement. When running a calculation view it is executed by the Calculation Engine. It should however be noted that it may not be as fast as an Analytical View.

Calculation view is a column view that is visible to reporting tools. When the view is accessed, a function is implicitly executed. There are two types of calculation views i.e. Graphical view (Composite views, re-uses Analytical and Attribute views) and SQL/SQL Script/Custom Functions.

Procedure for creating Calculation view:

1. Navigate to the Calculation View under your Sulabh package. Right click package, select New and choose Calculation View to create a new Calculation View. Name your calculation view, Description, Specify view type Graphical and select your schema. Click on Next.

2. Since we will be reading from existing Analytical Views i.e. you do not have to select any tables from the catalog folder -> Expand Content folder -> Select the previously created Analytical Views -> Click finish button.

3. As a result the Graphical Calculation View editor will open.

4. From the Tools Palette select 2 Projection graphical nodes one for each Analytical View. Double Click on each Projection node to rename.

5. Add a Union Graphical node and rename the node to Union. With the mouse pointer hover over each graphical node and draw a connection line between the nodes as shown on the below representing a data flow graph

6. Add all the fields of AN_ECC_PLANNING to the Output of the Pro_AP node. Select the Pro_AP node, within the details view select all the fields -> Right Click -> Add to Output

7. Add a new Calculated Column called KPLIKZ. In the diagram select the Pro_AP node. Within the Output right click on Calculated Columns -> New

8.  Enter KPLIKZ for the name of the field. Select INTEGER and enter 0 in the Expression Editor as the Planned Indicator value. Click OK.

9. Ensure the Calculated Column KPLIKZ is visible in the Output of Pro_AP.

10. Proceed to work on the Pro_PP node. As before add the field KPLIKZ but this time set the Expression value to 1 as an indication for planned data.

11. Add all the fields of AN_ECC_29012013 to the Output of the Pro_PP node. Select the Projection A node, within the details view select all the fields -> Right Click -> Add to Output

12. Select on UNION_AP_PP and use automatic by name

13. Click on the Output Node in the main diagram. Within the Details view pane select each field -> Right Click -> ‘Add the field as Attribute (Material_Number, Material_Description, Region, LAND1, city, Landx, PERIO, VKORG, KPLIKZ’) or as Measure (Gross_Revenue, Sales_Deduction, Production_Variance, Other_Expenses, Net_Revenue, CM1, and CM2).

14. Save validate, activated and data-preview of calculation view

Sulabh Bartaula 
SAP HANA certified consultant 
+358 40 4163 717 
sulabh.bartaula@sapfox.com