Ad hoc is a Latin phrase meaning "for this”, enhancing little further, it is created or done for little purpose as necessary. Ad hoc analysis is a business intelligence process designed to answer a single, specific business question. The product of ad hoc analysis is typically a statistical model, analytic report, or other type of data summary. Ad hoc analysis may be used to create a report that does not already exist. Sometimes ad-hoc analysis is done on the reports to drill deeper and to make more statistical comparisons to get better insights. Some of the procedure are, interactive querying, ranking, year-over-year analysis etc. Generally Ad-hoc analysis is done by the people, who are non-technical. Queries that executes during ad-hoc analysis are extremely complex, as data is retrieved from multiple tables and data sources. Using an ad hoc query may also have a heavy resource impact depending on the number of variables needed to be answered.
With this preamble, I want to proceed to show the Ad-hoc analysis done using MySQL and SAP Hana one. My main intention is to show, the ease of use of building ad-hoc analysis methodology in sap Hana. Process followed is:-
- Identified the efashion and club as Retail data set
- Exported or created the efashion and club Schema and updated the database with the data, in MySQL database.
- Using the ETL tool, MySQL database is exported to Hana Database.
- Designed and build the queries that are necessary to execute on MySQL database.
- Executed the same queries on the Hana database instance.
- To unleash the true power of Hana, created Attribute Views, Analytical views, Calculation views, procedures and Decision tables.
Explanation of the Database [EFASHION]:-
- The efashion database is also shipped with this release. This MS Access 2000 database tracks 211 products (663 product color variations), sold over 13 stores (12 US, 1 in Canada), over 3 years.
- The database contains:
- A central fact table with 89,000 rows of sales information on a weekly y basis.
- A second fact table containing promotions.
- Two aggregate tables which were set up with aggregate navigation.
Picture below show the schema of the Efashion Database.
Mysql is a very popular open source database.Most of the customers,planning to build the low cost solutions primarily use this database.As cost is the primary constarint,many times we need to do our analysis of data without the help of any BI tools.Direct & Quick option is to build queries on Mysql database.Let me show few scenarios build below:-
- Generate a Report showing the Revenue,Margin,etc based on Year and CITY
- Consolidate the Data based on the Shop Name,Here we want to see the Revnue based on the Shops
- Year on Year comparision Query for Revenue, Amount Sold, Quantity Sold & Margin
- Query used to filter based on the Quarter for the year 2004
- Year on year comparison based on the city contains character ‘Aus’
- Getting the name of the store with the highest Margin
By now,we have got the fair idea of the requirements that comes from the day to day business(example took are relatively simple).In most of the cases decision makers are non-technial users.Constraints are: Fair database knowledge is required for the user or need to rely heavily on the expert.Also, Query processing time,as most of the queries are complex SQL queries with Joins,Groupby,Orderby,etc
In my next section of blog,will how we can use SAP hana one to explore the possiblity of doing the ad-hoc analysis.
- SAP Managed Tags:
1 Comment
