Harness the power of Rocket AS with Excel
Rocket AS provides a myriad of processing capabilities for data access, analysis, and presentation styles, as all users know. However, the widespread use of Excel as a business intelligence and/or analytics tool throughout any enterprise is simply a fact of life.
One of the reasons Excel is so pervasive is that users can address its data in any fashion. A single cell, a row, a column, or ranges are all readily available to “do the math.” There is no barrier such as having to figure out how to go backward in a table, etc.
An ongoing dilemma with Excel is the need to access and populate a sheet or series of sheets with information outside its scope. We see a wide range of add-ins, data access methods, and more to provide a solution.
An Excel-ent source for data and pre-processing
The ideal setting for any Excel user is the ability to use a back-end process (Analytics Server) that can preprocess information and hand it over to Excel in a rapid, easy-to-use, and efficient manner. We propose that the Rocket AS family might be the enterprise data access hub that Excel users have long sought. So what about the Rocket AS family?
In this special edition, we will cover the various ways to populate data in Excel by using Rocket AS, including solutions available from a Rocket partner (described later). Rocket has added new products to AS such as the Rocket Ascent Server and Rocket CorVu NG, which provide access to new data sources and types, run on multiple platforms, and use existing or new procedures.
The AS Command Client
This feature has been in place for some time, but is very useful for accessing data supported by the Analytics Server on IBM z/OS or IBM z/VM. It provides a windowed interface that lets the user (or application) specify a few simple parameters (connection method, application code, what type of data to create and return). The concept with the command client is that for any process employed on the back end (procedure, command, etc.) that results in an IN table being made available, the client will pass the results down to the workstation in the format specified.
In Figure 1 we show the setup to process a DB2 on z/OS table using the application code called STAT and return the data to the clipboard for a quick paste into Excel. Note that we have other file options such as HTML, DDE, CSV, and ODBC. Our results set isn’t very large, so we can use this quick method to return data.
Now we specify how the host processes the user’s request, as shown in Figure 2. You can bypass or expose the host processing. If it’s the first time you have used this method, you might want to see the back end ‘chatter’ as the server handles the request.
Now we simply go to Excel and use the ‘paste’ option to place the data wherever we want it within the sheet, as shown in Figure 3:
We can save this work dialogue to use again or for other applications. Or, we can simply close the command client after it’s done the job.
The Rocket Ascent Client with Excel
This client provides a means to access data from the IBM System z server or from any of the platforms supported by the Ascent Server (Linux, UNIX, AIX, and Windows). For illustrative purposes, we are using the client to access a table, held in a public library, that runs under Linux. Much as what we saw in the command client, the user takes an action to create a table available as an IN table to pass to the client. In Figure 4, we specify an XML file for output:
After the process is completed, we invoke Excel and use its option to get data from external sources. One of the options is XML, which we selected and, as shown in Figure 5, after we pointed Excel to the saved XML data set, it brought in the data effortlessly.
This option could prove extremely useful if you need to access data supported on new platforms such as Oracle or Microsoft SQL Server and more. It is also possible to import external data back into Ascent Client, such as an XML file we have that might be useful for processing. The process requires a ‘mask’ of the input file that you want to create to map the input data to a table definition. This is a very simple process. In Figure 5 we show the menu used to import the data:
Rocket CorVu NG with Excel
Rocket CorVu NG is a powerful desktop or web-based analytics tool that we recently added to the Analytics Server family. It provides a wealth of new analytic capabilities, as well as a glimpse into the future of the Analytics Server and its roadmap for end users.
CorVu NG is an Eclipse-based “power tool” for query, reporting, dashboards, and more. In Figure 6 we show the interface where the data sources are displayed and where we have already selected a table for processing. The table is displayed as a diagram where we can make changes, add more tables, and more. For the sake of this article we’ll just press the run button and see what we get.
The results set is shown in Figure 7:
So what do we do to get the data into Excel? We can navigate the menus in CorVu NG to see what option might apply, or we can press Ctrl+B and immediately populate the Excel sheet with whatever CorVu is accessing at the time, as shown in Figure 8:
Thus end users of Rocket Analytics Server have several options to use its powerful features and functions in conjunction with Excel.
The Excelerator from A Rocket Partner
The Excelerator is written in Excel VBA and uses the ActiveX control that is included with AS Client Connections Windows Edition. This application lets you:
- Populate Excel with any data that AS can read or produce, including ICE.
- Build DB queries (DB2 or SQL Server), execute them, and store them within the application or on the AS Server/SQL Server or both.
- Specify what DB2 system to attach, if other than the default and/or which SQL Server to connect to.
- Execute AS commands or procedures and even modify existing procedures before execution.
- Build a library of frequently used AS procedures, easy to find.
- Access extensive help all the way.
The following screen captures give you an idea of the look, feel, and function of the Excelerator.
Here you decide what database to attach/connect; in this example, My Other DB2 system.
You can define up to five connection profiles. Normally, AS attaches to a default database defined in a NAMES file. If you specify a different system and database in the Settings tab, Excelerator will drop the default and issue a new attach command.
You can search for tables and columns. You can pick columns or drag them into the editor pane. The from clause is built for you, including, as an option, join statements with table aliases.
You can ’swipe’ a table name in the editor pane and immediately get a list of its columns. If you know the name of the column, but not where to find it, type it in the filterbox and the tables containing a column with that name will be displayed.
You can copy an existing AS query into the editor, modify it, and execute it. There is a ’Count button’ if you want to check the size of the result, and an eMail button if you want to send the resulting Excel book to a colleague. You can save your query, and it will end up in the Archive and/or you can save it as a stand-alone AS query.
You can browse the shared libraries that you have access to, and select data tables, queries, ICE macros, or procedures. You can view the table structures or syntax, generate IN or RUN commands, or modify syntax. Additionally, you can log the execution and show the log, as well as save it as a text file on your PC.
Here you can easily find procedures and snippets that you use frequently. As with queries and ICE macros, you can add a category such as Sales or Finance, making it even easier to search.
Figure 13 shows some of the available settings:
You can add information about the source in the sheet: The SQL syntax, user, and date/time. Saving your work is simple, as shown in Figure 14:
If you are interested, please contact me. This is a rather elegant, but powerful Excel application that may prove to be well used within your enterprise.
A Unique Solution from A Rocket AS Consultant
This article presents an alternative way to use Analytics Server and Excel to create Excel books with multiple sheets, each one representing the content of an Analytics Server table with formatting of cells. It is based on the ability of Excel and Analytics Server to produce and interpret XML, and currently works only on Analytics Server native tables, although it could be expanded to DB2, etc. If the steps shown are not clear, please contact me and I’ll put you in touch with the developer of the procedure.
There are two parameter tables that need to be created and updated depending on the input data. The first table holds the name(s) of the server table(s) to populate the Excel book and the respective sheet names. The second table holds the attributes for columns or individual cells. These tables are used by the Excel XML Generator that creates the XML code. When the XML file is transferred to the PC or LAN, it can be opened directly by Excel. This example uses tables from the public library.
As shown in Figure 15, the Excel sheet displays the tables with the first line displaying the column names in bold and with the first row locked. A conditional format is used in column J, “Source of recruitment.” A time stamp is used to name the first sheet.
What is required is a procedure that updates the parameter tables with the desired table names, sheet names, and formats, and then runs the Excel XML Generator.
Current options for the Excel XML Generator support the generation of up to 10 sheets, with each one representing an Analytics Server input table. Formats can apply to all the rows in a column or a single cell (Table 1).
Table 1: Supported Excel XML Formats
|tribute||Alphanumeric columns||Numeric columns|
|Thousand delimiter red||Yes|
|Thousand delimiter bold||Yes|
|Date format YYYY-MM-DD||Yes|
The “White” option can be used to hide cells if values are repeated in a column that is used as a key column and only the first occurrence is to be displayed. The beauty of this option is that if you use AutoFilter in Excel, the values are still there, but not visible.
Create local copies of the parameter tables that are needed.
- dXLSTabs to be populated with the names of the AS-tables to be used.
- dXLSAttr to be populated with attributes for each AS-table, if any.
The table definitions should look like this:
Data Table dxlstabs – 3 Columns
|3||Key||1||NUMERIC KEY 1|
Data Table dxlsattr – 4 Columns
|1||AttrTab||2||NUMERIC KEY 1|
|2||AttrRow||3||NUMERIC KEY 2|
|3||AttrCol||3||NUMERIC KEY 3|
In our example, two server tables are used: ***DEMO/STAFF and ***DEMO/STAFFDEP. The procedure updates the column Tname on key 1 and 2 with the names of these tables. There also is a column SheetName where you can specify different text on the tabs in Excel, presenting the name of the sheet. If you leave this column empty, the name of the server table will be used. You can specify a maximum of 10 tables.
Update the attributes. There are three key columns in the attribute table:
- Key 1 corresponds to the table that is referenced in Tname(1).
- Key 2 refers to the row in the table; if 0, it refers to all rows.
- Key 3 refers to the column number in the table.
In our example, for all rows, column 13 will have the attribute STD. If the value in the column “Source of Hire” is “Outside Ref,” that row will be displayed as reversed yellow. The currently supported attributes are shown in Table 2:
Table 2: Currently supported attributes
|SOY||Alpha/Numeric||Solid yellow, reversed yellow|
|WHI||Alpha/Numeric||White text, in most cases invisible|
|STR||Numeric||Thousand delimiters and red text|
|BLT||Numeric||Thousand delimiters and bold text|
|DAT||Numeric||Date format YYYY-MM-DD|
When the procedure (shown below) is executed, the result is an external ASFILE called eXLSTab. This one should be transferred to a LAN or PC. There are numerous ways to do this, and I am sure you have one that you use regularly.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
/* A small sample of how to use the XML Generator /* Create copies of the parameter tables -------------- can inc in *XML/dXLSTabsout dXLSTabs mask in *XML/dXLSAttr out dXLSAttr mask /* ---------------------------------------------------- in ***DEMO/Staff inc dXLSTabs,dXLSAttr when @start /* Define what tables to get data from ---------------- let TName(1)='***demo/Staff' let TName(2)='***demo/Staffdep' /* Define the sheet names let SheetName(1)=concat('Staff as per ',@adate) let SheetName(2)='Departments' /* --------------------------------------------------- /* Table 1=Staff, row 0 = all rows, column 13 = Annual Salary let Attr(1,0,13)='STD' /* Thousand delimiter */ when /* Read the IN table Staff and set conditional colouring if Source of Hire='Outside Ref' /* Table 1=Staff, @rowno = current row, column 10= Source of Hire let Attr(1,@rowno,10)='SOY' /* Yellow Background */ run /* Now run the procedure that creates the XML code for Excel run *XML/pAS2XLS
We won’t go into depth on the procedure that creates the XML data, but the output looks like the following sample. Here is a small sample of what the procedure pAS2XLS generates:
<Cell><Data ss:Type="Number">1102</Data></Cell> <Cell><Data ss:Type="Number">20030210</Data></Cell> <Cell ss:StyleID="s23"><Data ss:Type="String">Inside Ref</Data></Cell> <Cell><Data ss:Type="Number">510</Data></Cell> <Cell ss:StyleID="s26"><Data ss:Type="String">Outside Ref</Data></Cell>
As you can see, there is a small difference between the two lines that reference StyleID resulting in StyleID being set to different values. The styles are defined previously in the code. If you would like to know more, we can put you in touch with the originator of this technique.
Rocket AS offers a suite of innovative and effective capabilities to incorporate all of AS’s ”firepower” with Excel. Given the pervasive use of Excel within an enterprise, it simply makes sense to complement it with server-based processing and data access. Rocket AS often can replace lengthy and tedious data transfers and unnecessary processing by performing much of the data access and calculations prior to Excel getting into the mix. It also opens the door to other applications that are client-side based, but could utilize the power of a server running interference.