Mixing XSLT , Access , SAP and .NET

Its fun when the technology that you hear about actually make it easier to make something relevant.

In this case, I am extracting planning information from SAP. Aggregating and analysing the data in Microsoft Access. Providing web access to this using ASP.NET. The next piece was to make the data accessible in XML and Excel.

Here XSLT steps in. I use the native XML support in NET to convert the Access data into an XML string. The XML is then transformed by an XSLT spreadsheet into Excel 2000 HTML spreadsheet format. (Incidently Excel 2002 has a native XML format, but it isn't supported by Excel 2000.)

The next step is to graph this data, since graphs is what makes planning easy.
The first idea was to use VML since it has native support in IE. However, VML is not an approved draft (even though Microsoft will continue supporting it) SVG is the next generation Vector Markup Language. So the graphs must be SVG.

To view SVG, you'll need the Adobe SVG Viewer plugin.

Now, the fun part. The XSLT cookbook by Sal Mangano is brilliant. It already has an XSLT template to generate SVG graphs. After breaking my head trying some trivial stuff in XSLT, I've turned to the guru. He is amazing. The XSLT techniques that he uses are mind blowing and open your eyes. You feel humble after comparing your feeble attempts to his elegant solutions.

But another question that arises is whether there is a real user requirement for graphing the data. I'm not so sure about it. There is a strong request to automate sending the updated percentages back into SAP "automatically". To this end, I've written a program which can update the percentages in planning configuration points. It is a RFC enabled function module to update the points. Now I've to write the NET code to provide an update mechanism.

Day to day activities have no coherant purpose. I think I've got to get some coherance into these activities.


Signing off.

Connecting to a mainframe

IBM provides Java connectors to connect to a mainframe.
The CCF connector is one example. It is a J2EE resource provider.
Check out the IBM redpages www.redpages.ibm.com to get a PDF on using the CCF connector.

Putting the KMAT option series on the web

After looking at SIS and APO, it is clear that they will not be sufficient to meet my requirements.
So I had to go back to the Access database (which processes an extract from SAP).

1. Reorganize the database to use a star schema and behave like a DW.
a. Pre-aggregate at three different levels.
b. Seperate the database load application and the DW database. (seperate MDBs).
c. Have a single Access form to query all data.
2. Provide Web-page access to this data.
a. Used ASP.NET to query the data from Access. [It took a bit of digging to figure out how to pass parameter values to Access. Especially when the query uses form variables. Also figured the other way around - i.e how to use query parameters in Access Forms :- this involves creating your own recordset and binding it to the form at runtime.]
I've borrowed the concept of using JSP "session" bean to store parameters. This provides an elegant method of sharing parameter selections across pages. Page Viewstate is a bit restrictive.
b. Add the company trimmings
c. Write an XSLT transform that converts the data into MSExcel 2000 "HTML" format. When you associate this with Excel's Mime type, Excel will open the file as a standard spreadsheet.
d. Provide raw XML access also. [Must override the Render function, if you don't want the HTML in the page to get rendered]

3. Next, time for some graphics.
a. VML is easy and integrated in IE. The syntax is not too difficult either. Microsoft provides a VMLGenerator editor to play around with VML.
b. Found a tool to generate graphs in VML or SVG (www.grapl.com).
[This led to a interesting diversion into the languages APL and J. May take some time to study the language J].
c. SVG is the W3C approved vector language, but you need to download a plugin for it. For some reason it does not work on my system (Adobe SVG Viewer 3.0).
d. Need an XSL template to convert the XML into a set of graph data.
[ Rather challenging, since I've forgotten XSLT. The site www.exslt.org provides a lot of handy subroutines/functions.]
[ The site http://purl.org provides a place to put up permanent URLs. Registered there as dmahajan.]

So for now, I've just got as far as transforming the data into a graph series. Next step is to transform it into VML lines.