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.
Attribute View:-
AV_ARTICLE -- Joined ARTICLE_LOOKUP & ARTICLE_LOOKUP_CRITERIA tables
AV_CALENDAR_YEAR -- Used table CALENDAR_YEAR_LOOKUP
AV_OUTLET_LOOKUP -- Table OUTLET_LOOKUP
AV_PROMOTIONS -- Table PROMOTION_LOOKUP
Analytical View:-
ANALYTICAL_VIEW_PROMOTION - Data foundation with product_promotion
ANALYTICAL_VIEW_SALES - Data foundation with SHOP_FACTS
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.
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.
- SAP Managed Tags:
