Hovitaga OpenSQL Editor
Brief description
Hovitaga OpenSQL Editor is a powerful tool for SAP consultants, ABAP developers and basis administrators that helps to work with the database of an SAP system.
- It provides an intuitive way to build ad-hoc reports and statistics with OpenSQL commands.
- The results of the queries can be easily visualized using charts and diagrams, and can be exported to MS Excel for further analysis.
- Importing data from external sources like MS Access or MS Excel can be done with a few clicks.
- Testing and code maintenance efforts can be greatly reduced, development speed and code quality can be increased, since all OpenSQL commands can be tested, evaluated and analyzed with Hovitaga OpenSQL Editor.
Screenshots
A simple SELECT command
|
Totals and subtotals
|
The Field Selection Wizard
|
Linked Query Assistant - Foreign key relations
|
Linked Query Assistant - Checktables
|
A 'Select for all entries...' command
|
My Repository
|
Displaying table relations
|
An UPDATE command
|
A DELETE command
|
Results in tree view
|
A bar chart
|
A pie chart
|
Command properties
|
Dynamic value ranges usage
|
Adding a new record
|
Modifying a database record
|
Architecture
User Interface
We have developed our OpenSQL Editor primarily for the traditional SAP Gui, but it can be used with the use of WebGUI (SAP Gui for HTML) if the ITS (Internet Transaction Server) is configured properly. Some functions cannot be used from WebGUI (creating charts for example).
Security and authorizations
Our OpenSQL Editor uses the standard SAP autohrizations for accessing database tables and also uses custom authorization objects to restrict features available for users. We provide ready-to-use roles to reduce implementation efforts.
The Query Engine
A robust OpenSQL parser is the base of our query engine that is capable of executing any valid OpenSQL statement depending on the authorizations set for the user. All syntax variants are supported except for a few ones that are not relevant ("SELECT FOR UPDATE" is not supported for example, since we have our own locking mechanism).
The Data Modification Engine
If a query fulfills certain conditions (uses one table and includes all key fields), the result set of queries is editable. Simply use the ALV grid that displays the query results to edit the result set. Changes can be saved to the database with a single click of a button. Mass update and delete commands are also supported, which is a convenient way do manipulate large sets of data.
The Data Modification Engine can be transported separately to each SAP system, so it is possible to set up a read-only version in the productive system while having a fully functional version in the development and test systems.
Locking concept
A strict locking concept ensures that the database remains consistent even in case of concurrent accesses to the same database record. The locking mechanism not only works between multiple instances of Hovitaga OpenSQL Editor but it protects records processed with standard SAP transactions also.
Import and export of data
The result set of any query can be exported to several formats including MS Excel, HTML or flat files (tab separated or CSV). Any data can be imported from the clipboard into the ALV grid in change mode, all records can be verified (data format, foreign key and checktable relations etc) before saving them to the database. Field mapping is quick and easy with the use of the Field Selection Wizard and/or by simply using draging and dropping the columns.
Basic features
Execution of OpenSQL commands – Any number of select, update and delete OpenSQL commands can be edited at once. In case of executing a select command, the result is displayed in an ALV Grid or in an ALV Tree as requested. The hierarchy levels and sort conditions of the hierarchical tree can be changed at any time without re-executing the select command. Accessing pooled and clustered tables is supported.
Robustness – All syntax variations are accepted, without the use of local variables. Group functions, subqueries (nested to any level), inner and outer joins, related select constructs (“Select for all entries in…” command) are all supported.
Related database queries – Any result set of a select command can be the input of another select query. In this case, the res u lt of the “outer” select command is used in the “for all entries in” clause of the “inner” select command. The result of a “select for all entries” command may be also used as an input of another one, so these commands may be nested to any levels.
Format the data extracted – Common mathematical calculations can be added to the result set without coding, and without accessing the database. Totals, subtotals, averages, minimum and maximum values can be calculated for any numerical column of the result set. Sorting and filtering the result is also possible without typing and stressing the database.
Use the data extracted – The result of a select command can be printed, exported to a spreadsheet and many other formats, and can be used as the basis for creating charts.
Ease of use
Dynamic value ranges – An easy and quick way of filtering the affected records of a command, eliminates the need of manually typing in the filter conditions.
Linked Query Assistant – A feature that helps to develop commands with inner and outer joins. The Linked Query Assistant reads the data dictionary to explore the relations between database tables. With a few clicks, a join or a subquery may be implemented automatically, without typing anything.
Field Selection Wizard – An efficient feature that provides a way to select the fields used in a command without manual typing. It reads the table definition from the Data Dictionary and offers the fields with their descriptions in a list for selection.
Commands can be organized into folders within the Repository – Command properties may be set for each command (shor t and long description, link to Business Object or Application Component, search terms..). Commands can be searched by any of these properties.
Shortcuts to relevant transactions – Once a command is parsed and the accessed tables are recognized, many user friendly tools are available with a push of a button, like displaying the Table/View (call SE11), table indexes, technical settings, Where-Used List and a Network Graphics tool to discover relationships between tables.
Application Hierarchy Explorer – The application hierarchy of the underlying SAP system can be browsed to find data dictionary objects related to a specific software module. All the development packages are displayed as subnodes of the relevant application components. Data dictionary objects are listed for every package and can be displayed with the appropriate transaction with
Advanced features
The result set of a select command is editable – If a select command fulfills certain conditions (selects from one table, includes all key fields, contains no group functions), the result ALV Grid can be switched into edit mode, and all the records may be changed or deleted, new ones can be added. A locking mechanism ensures that one record in the database cannot be modified if it is already locked. This locking concept is not working only in Hovitaga OpenSQL Editor, but checks all locks that were placed on the database record by other programs. When the editing of the result set is finished, all the changes can be saved to the database with a push of a single button. An automatic input help/input check may be switched on and off at any time.
Universal data import – Data can be imported to any table from practically any source (MS Excel, MS Access, flat files etc.) with the trivial use of the clipboard. Data can be reviewed and adjusted before saving to the database. The mapping of fields is simple and easygoing using the Field Selection Wizard or simply moving the columns of the result set using drag and drop.
Loop-at workbench - due to the limitations of the OpenSQL language, some modifications of the database are impossible with a simple update command, and a separate report must be created. The Loop-at Workbench offers a perfect alternative and eliminates the creation of “disposable” reports that slowly but steadily fill the SAP database. With the SAP standard developer authority check and explicit authorization, the users can execute ABAP code on the result of any select command, so all the limitations of OpenSQL are gone. Any custom logic can be implemented, and all the modifications can be saved to the database with a single click. No hassle with the data declaration, displaying the results on the screen, locking the records, no more painfu
l hours in the debugger.
Data backup & restore – Any result set of a select command can be the basis for creating a transparent table in the data dictio nary. The structure of the table will be the same as the structure of the result set, and the records of the result set are inserted into the newly created table. Restoring the records can be done easily and quickly. The same set of data extracted at different times can be easily compared and analyzed.
Direct benefits
Software development time reduced – Writing programs that access the database in any form becomes much easier since all commands accessing database can be developed, tested, and analyzed with many user-friendly features.
Software testing time reduced – Test data can be created quickly in any table without using the relevant transactions which can be very time-consuming. Since any data can be modified, special test scenarios can be set up rapidly. Any problem in a productive system can be replicated in a test system without having to wait for a complete database copy.
Quick ad-hoc reports, charts – Any requirement of an ad-hoc report can be fulfilled in much shorter time. Many development steps are done automatically, developers don’t have to hassle with programming the list or tree output, the selection screen, data declaration and creation of charts and diagrams.
Boost business analytics – Most business users have the skill of using MS Excel for analytics, they just simply need the data from the SAP system extracted. This is just a matter of a few clicks with Hovitaga OpenSQL Editor and your business users can instantly start analyzing up-to-date business data.
Disposable reports eliminated – There is no need to create a new report in the Repository each time a report or chart is required.
Maintenance efforts reduced – Defects can be found earlier with the positive effects on testing described above. Moreover, existing programs and data models can be comprehended quicker and easier with inspecting the OpenSQL commands and table relations in our OpenSQL Editor instead of the ABAP Debugger.
Import of data to SAP systems simplified – For sources with a modest amount of records (typically stored in Excel sheets and Access databases) Hovitaga OpenSQL Editor offers a strikingly simple and intuitive way to import that into an SAP system.
Indirect benefits
Improved code quality – An OpenSQL command can be thoroughly tested with minimal effort opposed to the typical awkward way of executing the given report and using the ABAP Debugger to inspect the result of the relevant commands.
Job satisfaction – Providing a tool that eliminates most of the monotonic and tiring work from your employee’s day to day activities will have a good effect on team morale and general job satisfaction.
Less manual typing – Constructing commands that access the database can be speeded up significantly with the use of several user-friendly features.
Download a free version
You can download a free version of Hovitaga OpenSQL Editor here. There are versions for 4.6C, 6.20 and 7.00 basis releases.
Simply copy and paste the contents of the file you download into a new program (with SE38) in the Z namespace.
Activate and run. The program will install itself (screens, messages, texts etc.) and will be fully functional.
Related Articles
Hovitaga Products
Hovitaga Report Generator - SAP reporting made easy
