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).
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.
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.
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.
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
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.
Figure 6, Source: SAP
Figure 6 discusses Information Models.
The models do not store data; they are definitions of how to access data.
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.
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.
Figure 9, Source: SAP
Figure 9 shows that the Analytical view is published so the client tool can access it.
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.
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…
- The HANA work can be reused; you do not have to re-build the same models
- Information models can be used in Analysis and Explorer
- HANA will use the right optimization engine for the job
- 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…
- Universe can be made for ad-hoc experience
- Business users can define filters that are pushed down to the database
- 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)
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.
Figure 13, Source: SAP
Single source data foundations perform better than multi-source foundations
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.
Figure 15, Source: SAP
Running a query against 2 million rows over VPN came back in milliseconds as shown in Figure 15.
Figure 16, Source: SAP
Figure 16 shows joins on HANA tables using the SAP COPA model and looks like any other universe.
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.
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
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.
- SAP Managed Tags:
3 Comments
