Skip to Content

This ASUG webcast was provided by SAP’s Pierpaolo Vezzosi.  Pierpaolo is responsible for the semantic layer technology and BI solution on top of HANA.  The purpose of this webcast was to show how to access the SAP Hana with the BusinessObjects semantic layer (universe).

image 

Figure 1, Source: SAP

 

Figure 1 shows when you work with SAP Hana you have three main layers.  The Data Analysis and Visualization part is where BI fits.

image 

Figure 2, Source: SAP

 

Figure 2 shows the usual BI tools.  According to Pierpaolo, the positioning of the suite doesn’t change because SAP is running on SAP Hana.  Figure 2 shows the BI solutions are certified on SAP Hana.

image 

Figure 3, Source: SAP

 

Pierpaolo says the capabilities bring value to the customer, such as the columnar data storage allowing for data compression, and with in-memory  storage allowing for fast retrieval.

image 

Figure 4, Source: SAP

 

Figure 4 shows that no data caching is needed, and SQL extensions support

You can move from whatever data source to SAP Hana without disrupting the “user experience”.

 

SAP Hana Basics for Universe Designers

image 

Figure 5, Source: SAP

 

Pierpaolo wanted to explain how to use the Hana if you are a Universe Designer.  HANA is a relational database as shown in Figure 5. Row storage tables are optimized for writing while column storage tables are optimized for reading.  Usually BI runs against column storage as it is faster.

image 

Figure 6, Source: SAP

 

Figure 6 discusses Information Models.

The models do not store data; they are definitions of how to access data.

image 

Figure 7, Source: SAP

 

Figure 7 leaves out the attribute view.

Analytical views are cubes and contains the definition.  Analytical views are single fact table cubes. Calculation views can contain multiple fact tables. SQL script is a container for multiple languages.  CE functions have specific API that manage the HANA calculation engine.  L and R languages are for predictive analysis.

image  

Figure 8, Source: SAP

As Figure 8 shows the Analytical View, where facts are stored in a data foundation, and the dimensions which are joined to the fact table.    If you are familiar with the Information Design Tool, you should see a similar look and feel with Figure 8 but they are targeted for different users.

image 

Figure 9, Source: SAP

 

Figure 9 shows that the Analytical view is published so the client tool can access it.

image 

Figure 10, Source: SAP

 

Figure 10 shows the SAP Hana has table and information models and BI can access both of them.  Universe on tables does not look any different.  Analysis uses BICS technology to connect to HANA.  BusinessObjects Explorer uses an Extended SQL to access the information.

image  

Figure 11, Source: SAP

Figure 11 shows a mapping between SAP Hana and the Universe

Best Practices:

Should I build my universe on information models or on tables? (source: SAP)

There is no fixed answer and it may change with time and suggests that you go to the best practices section on SCN.

When building a universe on an information model…

  1. The HANA work can be reused; you do not have to re-build the same models
  2. Information models can be used in Analysis and Explorer
  3. HANA will use the right optimization engine for the job
  4. Complex calculations with L and R

The constraints with Information Models include programmatic calculation views are fully executed even if not all the metadata is used

When building a universe on Hana tables…

  1. Universe can be made for ad-hoc experience
  2. Business users can define filters that are pushed down to the database
  3. Joins between tables are faster than joins on models

The constraints – there is no bridge between the Hana information models and the Universe.  The best HANA engine may not be used either.

 

How to make sure my universe on HANA has a good performance? (Source: SAP)

image 

Figure 12, Source: SAP

 

The array-fetch size is 10, as shown in Figure 12.  10 was good when you didn’t have a lot of memory. This is not good for HANA, Pierpaolo says, and he changed it to 1000.  The array-fetch size is the number of lines retrieved from the database each time you connect.  Pierpaolo says with this setting change you should see 10 times better performance.

image 

Figure 13, Source: SAP

 

Single source data foundations perform better than multi-source foundations

image 

Figure 14, Source: SAP

 

This demo was done on IDT Feature Pack 3.  Pierpaolo says to only use one data foundation and one information model, one universe. 

JOIN_BY_SQL parameter of the Data Foundation should be set to yes.  JOIN_BY_SQL says when there are multiple SQL’s generated by the query that query synchronization will take place in HANA and not the client tool.

 

Measures defined in IDT need to be refined:

1 turn into measures

2 set aggregation

 

Pierpaolo said you can reorder the objects and then define a navigation path for drilling for the Business Layer.

image 

Figure 15, Source: SAP

Running a query against 2 million rows over VPN came back in milliseconds as shown in Figure 15.

image 

Figure 16, Source: SAP

 

Figure 16 shows joins on HANA tables using the SAP COPA model and looks like any other universe.

image 

Figure 17, Source: SAP

 

HANA is great at handling large amounts of data, but not to transfer them to your client tool, as Figure 17 shows.

 

Future Directions

For the next part of the presentation, the safe harbor statement applies, and things are subject to change.

image 

Figure 18, Source: SAP

 

As part of the planned future direction SAP is to provide dimensional  access to HANA information models.  BusinessObjects Analysis will be the first. Workflows will be possible to pass from one model to another. Merge modeling environment to one tool and reduce the number of “personas” needed for a project.

 

Resources, provided by Pierpaolo:

SDN: Best practices for Universes on SAP HANA

– for the best practices, Pierpaolo says this is updated frequently so always check it periodically

 

Official Product Tutorials – SAP BusinessObjects Information Design Tool

 

Semantic Layer

 

Question & Answer (a subset)

Q: Is HANA ANSI SQL compliant?

A: Yes

Q: How different is building the universe on the columnar store?

A: There is no difference as you see the logical view

Q: Is this Information Design Tool in BusinessObjects?

A: It is part of BI 4

  

I thank Pierpaolo for his energy and enthusiasm for IDT/Hana and giving ASUG this webcast.

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Sudhanshu Surma
    Great stuff Guys!
    Pictures do speak more than a thousand words!…thanks Tammy for the snapshots of some really excellent content webcast by Pierpaolo.
    Thank you also for following each picture with a short snippet of the key info delivered / take-away.
    Certainly one of the best blogs on HANA:-) No blog I browsed ‘yet’ says so clearly that HANA DB is actually Relational (not Columnar like SybaseIQ), though BI on HANA primarily uses its Column stores. Also, didn’t see any blog ‘yet’ addressing Universes on InfoModels vs. Tables, performing queries on HANA etc.
    Keep it up guys…look forward to more from your side:-)

    (0) 

Leave a Reply