Skip to Content
Author's profile photo Simha Ramagiri Srinara

Ad-hoc Analysis Comparison with MYSQL & SAP HANA ONE – Continued

In this section of the blog,will talk about the “Ad-hoc Analysis using SAP Hana one”.SAP Hana one is,in memory platform hosted in the Cloud.SAP Hana one offers powerful combination of transactional and analytical processing,Subscription fees are also very low,it starts from $0.99 an hour plus AWS hosting fees

Let’s begin!! Prerequisits are:-

  • Export the mysql data to Hana
  • Install SAP Hana studio.

Exporting the MySQL Database to Hana using ETL

1. Use Talend to Sync Data from MySQL to HANA (Part I)

2. Use Talend to Sync Data from MySQL to HANA (Part II)

3. Sync Data from My SQL to HANA DB with Pentaho (Part I)

4. Sync Data from My SQL to HANA DB with Pentaho (Part II)

I am demonstrating two methods that can be used to do the ad-hoc analysis using sap Hana. As mentioned in previous blog, ad-hoc many times or mostly done by the users, they may not be having high technical exposure.

ü Method1:- Executing the existing MySQL queries in sap Hana studio,SQLConsole

SAP Hana is support ANSI standard SQL syntax, already written queries from any other database can be executed seamlessly. Let me execute, couple of queries for demonstration purpose. I am able to execute the same query executed on MySQL without changing the query on SAP Hana. Also we get the advantage of Performance capability of SAP Hana.Constantly I am getting the 4-5 times better performance in the execution time of the queries.

  • Getting the name of the store with the highest Margin using SAP Hana


  • Method2:-SAP Hana modeling

Modeling refers to an activity of refining or slicing data in database tables by creating views to depict a business Scenario. The views can be used for reporting and decision making. These views are also known as information views. You can model entities in SAP HANA using the Modeler perspective, which includes graphical data modeling tools that allow you to create and edit data models (content models) and stored procedures. As this is done using GUI, technical expertise to use is very minimal. Once the modelling is done without any one help, anybody can view the data and make more analysis.

In SAP Hana studio, I have created 4 attribute views, 2 Analytic views, 1 calculation view.Hana_modeling_1.png

Attribute View:-









Analytical View:-

ANALYTICAL_VIEW_PROMOTION Data foundation with product_promotion




Calculation view:-

CALC_VIEW_SALES_PROMOTION –Joined both Sales and Promotion Analytical views


Let’s do some analysis using the Analytical View Sales:-

  • Right click on ANALYTICAL_VIEW_SALESà”Click on Data Preview” tab.
  • Add Attribute “Shop_Name” to Label Axis.
  • Margin,amount_sold,Quantity_sold to Value Axis
  • Result Window will show the result in Graph,table,Grid and Html format


Let’s filter based on CITY


For ad-hoc analysis,Drillup,DrillDown,Filters,Aggreation,Sorting etc are the major workflow,all these can be attained by just few clicks in the Studio.


Change is the only constant in the present business scenario. Every detail about the analysis cannot be captured with the formal reporting. Ad-hoc analytics provides insights to the questions that we get based on the day to day needs. In order for doing ad-hoc analytics high proficiency in database and high resource usage to execute those complex queries are constraints. Advantages of using SAP Hana one for Ad-hoc analytics is:-

  • Execution time of the  Queries reduces by 4-5 times in SAP Hana one, compared with MySQL
  • Imperative and Declarative logic in SQLscript have greater capability when compared to MySQL
  • Most importantly, using the GUI of the Hana modeler, even the Beginner can do better analysis using SAP Hana.

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.