Introduction
This blog demonstrates how to generate fractal data using SQLScript and expose this data as an OData service to be visualised in a .NET client.
Fractals are an interesting source of "big data" to use to experiment with some SAP HANA features. With simple maths you can generate huge amounts of information containing intricate detail. In this blog I will show you how to generate fractal data with SQLScript and then consume that information in a bespoke C# client via an OData service. The fractal we will use is the famous Mandlebrot set, which is easy to code and has even had a song written about it! If you're not familar with fractals, you can think of them as images that produce ever more detail the more you zoom in. We use HANA as a "microscope" to zoom in to look at tiny details.
First I will demo how the finished development works, then I will step through the development process. If you want to try this out yourself, all the code is available on GitHub, for both the Windows client and the HANA backend.
Disclaimer: Sending huge result sets to a display client is not something HANA is designed for. In this blog what we're doing is essentially sending entire images in a very inefficient non-image format designed for small data sets. This blog is intended to demonstrate OData and its consumption in .NET, and is not a realistic technical scenario. Anyway, with that out of the way, lets start with a demo.
Demo
I run the Wndows client and type in the coordinate ranges of the part of the Mandlebrot set I want to look at. I start off by selecting the whole Mandlebrot set in the "Data Requested" box at the top left and I then press "Get Data". The client makes an OData call to HANA and retrieves the data asked for, then it renders the image:
That little part at the bottom highlighted with the yellow box looks interesting. I can type in new coordinates of that area and press "Get Data" to make another call to HANA to get more detail. I can optionally change the client's color scheme, and keep zooming in on interesting parts:
The last set of coordinates I enter brings me to here:
Now I'm getting to the limits of the pre-prepared data and I'd need to generate more points in HANA to look deeper. An improved colouring algorithm could smooth out the yellow pixelation you can see on the border of the black areas on the right. So that's the demo done. let's look at how we build this end to end.
Generating Fractal Data in HANA
To store the Mandlebrot set data we need a single table in HANA and some SQLScript routines to fill it. This is the .hdbtable file:
table.schemaName = "WORKSHOPA_K00";
table.tableType = COLUMNSTORE;
table.description = "Mandlebrot Set Points";
table.columns = [
{name = "Resolution"; sqlType = INTEGER; nullable = false; comment = "Resolution of X Y coords, equivalent of DPI"; },
{name = "XCoord"; sqlType = INTEGER; nullable = false; comment = "X Coord expressed in Resolution units"; },
{name = "YCoord"; sqlType = INTEGER; nullable = false; comment = "Y Coord expressed in Resolution units"; },
{name = "Escaped"; sqlType = INTEGER; nullable = true; comment = "Escaped result"; }
];
table.primaryKey.pkcolumns = ["Resolution", "XCoord", "YCoord"];The table key contains the x, y coordinate and an additional key to store a Resolution. Think of the Resolution field as representing a DPI "dots per inch" setting like you have on a printer, except in this case it is "dots per unit of Mandlebrot space". We calculate Mandlebrot set results at various Resolutions, and then request data be returned at a Resolution appropriate to how far we are zoomed in. The more you zoom in, the more you need to increase Resolution. The last field is Escaped which is the result of the Mandlebrot Set calculation for that particular point, and it is the value that is later turned into a colour by the Windows client.
Once the table is created, SQLScript routines are used to iterate through ranges of x, y coordinates for a Resolution, calculate the Escaped value, and write the resulting record to the table.
All the necessary definitions and routines you need for this part are on GitHub, using the files there you can follow these steps:
1. Create table using mandlebrotPoints.hdbtable
2. Create SQLScript procedures from fill_table_mandlebrotPoints.procedure and single_point.procedure.
3. Fill table with sample data by calling sql_fill_table.sql.
4. The fill can take a few minutes, you can monitor fill progress by calling sql_fill_table_progress_monitor.sql.
5. Activate OData service mandlebrotPointsTable.xsodata.
Windows Client
The client is written in C# and .NET 4 and is also available in GitHub. To get it running, open the solution, navigate to the project FractalViewerWindows and edit HanaServerDetails.resx with your server, login details and OData service name. Then build and execute. The projects contained in the solution are:
- FractalModel - Makes OData calls to HANA and parses the results using Json.net. The results are a series of x, y coordinates and an Escaped value.
- FractalViewerWindows - Draws results obtained from the FractalModel project. It is here that the colouring of the Escaped values is done. This project knows nothing about HANA.
- Newtonsoft.Json - Implements JSON support for .NET.
The client rendering is done in the woefully slow Windows Forms drawing, just because it was quick to write. Much better would be a client using DirectX to make rendering faster.
Consuming the OData Service
There are two main options for consuming OData services in .NET - you can use LINQ or you can parse the result sent by OData. For the LINQ approach, you point your C# project at the web service (create a Service Reference in Visual Studio) and it generates some helper classes for you. You use the generated classes to query the OData service using LINQ statements. However, this does mean you lose visiblity of what is really going on "under the hood". Since this is a learning exercise, I opted for the parsing-the-web-response approach as this gives you more control. The LINQ approach would have benefits when dealing with complex services as it handles result sets with associations much easier than you could by parsing.
Performance
OData has some overhead in terms of the data volume transferred. Transferring the initial whole Mandlebrot set shown in the first image above was about 22 MB(!) across the internet from HANA to the client, so each image does take a little time to appear. The actual result set we need is a series of x,y coordinate and an integer holding the count of iterations (that is later turned into a colour). This is no more than 24 bytes of information per result set row, depending how you store it. In my tests, Atom XML transfers 650 bytes per result set row and JSON transferred 300 bytes per result set row. Either way, that's a lot of overhead, but clearly JSON is more succinct.
This highlights why it is undesirable to send huge result sets in this manner using OData. I've read that SP06 supports the LOB data type suitable for video and images, and that the OData standard does support streaming of data (as opposed to "sending it all one go" as we do here), so perhaps other options will be available in future.
In terms of performance, regardless of the OData call, HANA responds in less that 0.5s with the start of the results of the OData service call. The delays after that is the time taken to transfer data over the network.
Other Notes
If you really, really, wanted to display fractals in real-time you'd be better off avoiding a client-server approach entirely and doing all the work locally, ideally on a modern GPU (which is very good at massive parallel calculations performed on a per-pixel basis). However, storing the pre-calculated results in a table in HANA does offer the ability to analyse the result set with calculation views. So instead of just displaying the results as we've done here, you could analyse the frequencies of colors or other properties of the data itself.
- SAP Managed Tags:
5 Comments
