Automating SAP HANA with Python

Basic Tutorial for using Python scripts and CLI to access HANA.

http://saphanatutorial.com/sap-hana-and-python/

with a gentle introduction to Python courtesy of MIT.

http://ocw.mit.edu/courses/electrical-engineering-and-computer-science/6-189-a-gentle-introduction-to-programming-using-python-january-iap-2008/

For those Microsoft-centric developers (like me) there is IronPython.
http://ironpython.net/
and IronPython Tools for Visual Studio.
http://ironpython.net/tools/

Some DB integration testing strategies and Parallel Processing of Tests
https://julien.danjou.info/blog/2014/db-integration-testing-strategies-python

Some approaches for data-driven tests.
http://sqa.stackexchange.com/questions/6678/what-are-some-good-approaches-to-separating-test-data-from-test-scripts

Parallel Testing with Python, Ruby, Node.js, R and SAP HANA

Julien Danjou, author of "The Hackers Guide To Python" gave me a wonderful idea.  What if your in-memory database platform could handle a number of high-load concurrent queries?  Perhaps you have SAP HANA and would like to perform some tests to ensure your analytic and calculation views are functioning as expected?

What if you could bombard your database with your entire suite of tests with a single parallel call?
https://julien.danjou.info/blog/2014/db-integration-testing-strategies-python

Maybe not the best way to become friends with your network administrator, this could showcase some of the awesome performance that is in SAP HANA.

Since the HANA ODBC drivers come with Python installed by default, leveraging Python for automation and unit testing only seems to make sense to me.

May as well host it in a Bottle.
http://bottlepy.org/docs/dev/

Sniff out your tests with Nose
http://nose.readthedocs.org/en/latest/
http://nose.readthedocs.org/en/latest/testing.html

Python Testing Taxonomy
https://wiki.python.org/moin/PythonTestingToolsTaxonomy

There is supposed to be support for SAP HANA in a flavour of SQLAlchemy - I couldn't find it.
http://www.sqlalchemy.org/

Getting started with HANA and Python.
http://saphanatutorial.com/sap-hana-and-python/
http://scn.sap.com/community/developer-center/hana/blog/2012/11/29/sqlalchemy-and-sap-hana

Comparing 2 CSV files
http://stackoverflow.com/questions/24556970/python-compare-two-csv-files-line-by-line

Exporting highly-formatted XLSX files with formulas and experimental macros
https://xlsxwriter.readthedocs.org/

Comparing those Excel files in Panda
https://xlsxwriter.readthedocs.org/

Parallel scenario testing
https://launchpad.net/testscenarios

Examples of HANA testing using LoadRunner
http://www.slideshare.net/SAPSolutionExtensions/testing-sap-hana-with-sap-loadrunner-by-hp

Examples of t-SQL tests that could be translated to SAP HANA, perhaps with the T-SQL to SQLScript translator
https://www.simple-talk.com/sql/t-sql-programming/sql-server-unit-testing-with-tsqlt/
http://www.codeproject.com/Articles/841250/Create-SQL-Server-Database-Unit-Tests
http://stackoverflow.com/questions/754527/best-way-to-test-sql-queries
https://msdn.microsoft.com/en-us/library/jj851212(v=vs.103).aspx
http://tsqlt.org/user-guide/tsqlt-tutorial/
 
IronPython & ODBC
http://www.ironpython.info/index.php?title=Databases_with_Odbc

pyodbc
http://www.easysoft.com/developer/languages/python/pyodbc.html

FitNesse & decision tables
http://fitnesse.org/FitNesse.UserGuide.TwoMinuteExample

ODBCTrace/SQLDBTrace
https://websmp130.sap-ag.de/sap/support/notes/1993254
http://service.sap.com/sap/support/notes/1993251

Save the results to Confluence Wiki
https://marketplace.atlassian.com/plugins/com.atlassian.labs.rest-api-browser
http://mattryall.net/blog/2008/06/confluence-python
https://ecosystem.atlassian.net/wiki/display/BLOG/XML-RPC+Page+Updater+Example

Blog your results
https://ecosystem.atlassian.net/wiki/display/BLOG/BlogginRPC+Plugin+Python+Scripts

If you don't want to go down the path of using Python for your unit tests, why not Ruby?
https://prograils.com/posts/getting-your-rails-app-running-on-the-sap-hana-cloud-platform
http://flavio.castelli.name/2010/05/28/rails_execute_single_test/

Or Node.js?
https://github.com/SAP/node-hdb

Test with Alpaca
http://scn.sap.com/community/developer-center/hana/blog/2014/09/11/alpaca--unit-test-over-hana

Or wait for HANA SP09 with Mockstar
http://scn.sap.com/community/developer-center/hana/blog/2014/12/09/sap-hana-sps-09-new-developer-features-hana-test-tools
http://mockstar.readthedocs.org/en/latest/

SAP HANA System Views & SQL Reference
https://help.sap.com/saphelp_hanaplatform/helpdata/en/b4/b0eec1968f41a099c828a4a6c8ca0f/content.htm?current_toc=/en/2e/1ef8b4f4554739959886e55d4c127b/plain.htm&show_children=true

Learn more with Shine
http://help.sap.com/hana/sap_hana_interactive_education_shine_en.pdf

Perhaps it makes sense to expose your HANA views as OData services and test those instead?

OData & Testing OData
http://scn.sap.com/people/lucas.sparvieri/blog
http://scn.sap.com/community/gateway/blog/2013/11/27/ecatt-based-test-automation-for-odata-services-available

http://scn.sap.com/community/developer-center/hana/blog/2012/12/21/hana-development-xs-odata-services

http://www.asp.net/web-api/overview/testing-and-debugging/unit-testing-with-aspnet-web-api
http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/odata-v4/create-an-odata-v4-client-app

Getting a little bit wackier with the possibility of using the HANA R integration to compare 2 data frames. 

http://scn.sap.com/community/developer-center/hana/blog/2012/05/21/when-sap-hana-met-r--first-kiss

Comparing 2 resultsets in R.
http://stackoverflow.com/questions/3171426/compare-two-data-frames-to-find-the-rows-in-data-frame-1-that-are-not-present-in

http://www.cookbook-r.com/Manipulating_data/Comparing_data_frames/
http://cran.r-project.org/web/packages/compare/compare.pdf
http://www.r-bloggers.com/identifying-records-in-data-frame-a-that-are-not-contained-in-data-frame-b-%E2%80%93-a-comparison/

http://www.johnmyleswhite.com/notebook/2010/08/17/unit-testing-in-r-the-bare-minimum/

Create an ODATA service with HANA and R

Interesting example of wrapping R up into an exposed ODATA layer out of SAP HANA.
http://scn.sap.com/community/developer-center/hana/blog/2013/10/08/creating-an-odata-service-using-r

How about using SQL in R?  Lubridate'ing?  Random Forests?
http://blog.yhathq.com/posts/10-R-packages-I-wish-I-knew-about-earlier.html

Exporting to Excel? Plus installing a bunch of other packages in a single shot?
https://gist.github.com/bearloga/10988512

Boasting about Oracle's In-Memory Database

Oracle is trying to halt some of the migrations from Oracle to SAP HANA with their new Oracle 12c In Memory technology.  Basically it allows you to "Pin" tables in memory in a columnstore cache.

Some speed boasts:

• Database queries and analytics running between 100 and 1,000 times faster than in the past.

• With in-memory technology, Oracle 12c database allows each CPU core to scan 2.5 billion rows per second.

• The time it takes for the 12c database to process 10 million invoice lines has been shrunk from 244 minutes to 4 seconds.

• The time it takes to run a financial analysis program is cut from about four hours to roughly 12 seconds

• A system for keeping track of a company’s transportation network featuring 16,000 drivers and 60 million shipment data records, is slashed to under a second from 16 minutes.

• A process that had previously taken 58 hours now needs only 13 minutes.

Welcome to a world where disks are a thing of the past.  Pretty soon I predict that physical disks will go the way of tape drives, and we'll all be running with 2-4 terabytes of RAM.

http://diginomica.com/2014/06/11/larry-ellison-database-fast-customers-broken/?utm_source=dlvr.it&utm_medium=twitter&utm_campaign=Feed%3A+diginomica2+%28diginomica%29

SAP HANA SPS 08 is out

Lots of new features and fixes for SPS 08 can be found in SCN or on Twitter.

SAP & the new SQL 2014 Cardinality Estimator

If you're running SAP on Microsoft and are lucky enough to be current on your SQL environment, you already know SQL 2014 was released yesterday and have probably been testing the CTP for months.  Right?

Anyhow, performance and compatibility are the two areas most likely to cause issues, or pleasant results.

Some more info on the new SQL 2014 Query Optimizer.
http://blogs.msdn.com/b/saponsqlserver/archive/2014/01/16/new-functionality-in-sql-server-2014-part-2-new-cardinality-estimation.aspx

If you're running, or upgrading to Oracle 12c, changes to their DB Optimizer with Adaptive Plans could affect your performance. 

http://scn.sap.com/community/oracle/blog/2014/02/19/oracle-db-optimizer-part-x--looking-under-the-hood-of-adaptive-query-optimization-adaptive-statistics--sql-plan-directives-oracle-12c

While testing Dynamic Sampling at my previous project, I noticed that whether dropping / recreating indexes, updating statistics, or using Dynamic Sampling, you always paid for your performance someplace.  As data gets larger, the built-in scheduled maintenance jobs can no longer cope with updating large partitioned tables in a 4-hour time window.  Custom solutions may need to be implemented.

Both SQL 2014 and Oracle 12c have enhancements to partitioning strategies.  Oracle can now delay the global index maintenance on an entire table when a partition is modified.  Truncation and exchange changes can be cascaded through referenced partitioned tables.  Interval partitioning is available. Partial indexing to speed up bulk loads.

For SQL 2014, online maintenance of index partitions and lock priorities seems to be one of the biggest feature improvements for partitioning.  Incremental creation of statistics on partitioned tables could also help with availability.

Getting started with Microsoft BI, Hadoop & SAP BI

Some links to get started with some of the various vendor technologies that integrate with Hadoop.

Azure HDInsight with Hadoop 2
http://www.windowsazure.com/en-us/solutions/big-data/

PowerQuery for Excel 2010/2013-- connect to Hadoop
http://www.microsoft.com/en-us/download/details.aspx?id=39379


Try out SAP HANA
http://www.saphana.com/community/try


Try out SAP Lumira or use free at home
http://www.saphana.com/community/learn/solutions/sap-lumira


Hadoop on a VM
http://edataanalyst.com/2013/08/hands-on-hadoop-tutorial-with-hortonworks-sandbox-vm-part-1-the-boss-edition/


Some articles around Oracle & Hadoop & tying it all together
http://blog.gbrueckl.at/2013/11/sap-hanas-big-data-scenario-with-power-bi/
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-integrating-hadoop-data-with-or-130063.pdf


Oracle vs. Couchbase - from Oracle
http://www.oracle.com/technetwork/products/nosqldb/documentation/nosql-vs-couchbase-1961718.pdf

Oracle vs. Couchbase -from the Comparison Wiki
http://vschart.com/compare/oracle-coherence/vs/couchbase

Next Hadoop (THUG) meetup in Toronto with Don Pinto from Couchbase.

http://www.meetup.com/TorontoHUG/events/169706762/