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

Hana_Query_1.png

  • 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:-

AV_ARTICLE  — Joined ARTICLE_LOOKUP & ARTICLE_LOOKUP_CRITERIA tables

Hana_modeling_2.png

AV_CALENDAR_YEAR  — Used table CALENDAR_YEAR_LOOKUP

Hana_modeling_3.png

AV_OUTLET_LOOKUP  — Table OUTLET_LOOKUP

Hana_modeling_4.png

AV_PROMOTIONS Table PROMOTION_LOOKUP

Hana_modeling_5.png

Analytical View:-

ANALYTICAL_VIEW_PROMOTION Data foundation with product_promotion

Hana_modeling_6.png

ANALYTICAL_VIEW_SALES – Data foundation with SHOP_FACTS

Hana_modeling_7.png

Calculation view:-

CALC_VIEW_SALES_PROMOTION –Joined both Sales and Promotion Analytical views

Hana_modeling_CV.png

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

Hana_modeling_Analysis1.png

Let’s filter based on CITY

Hana_modeling_Analysis2.png

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.

Summary:-

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.
To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply