In this example, we will produce reports that show three analytic views of a single data set. Each one will show the historical performance of the top performing product groups. The first report shows the top selling product groups, the next shows the fastest growing product groups, and the third shows the product groups with the largest sales increase.  Three distinct analytic objectives, one simple query.

The entire nextanalytics script is shown at the bottom of this page 

Query the database

We start with a single SQL query to the Kognitio WX2 server which is both very fast and can read billions of records in a few seconds. Script lines 2 through 5 set up the SQL query. Note that we embedded the SQL in the script for the purposes of making this example easier to read. In a production environmemnt, a separate .SQL file could be referenced to keep the business logic distinct from the database query logic.

The results of the query appear as the first 'page' in the nextanalytics analysis. It shows the product groups, the sales week number, and a total sales column. 

Pivot the date dimension to the columns

With one single line of script (script line 9), the text column 'saleweek' is swapped to the column dimension. This creates a crosstab. Every unique value of 'saleweek' becomes a column in a new second page.

For those familiar with OLAP or BI tools which use a metadata repository, notice that there was no requirement to predefine the column values, no complicated SQL syntax, no procedure calls. Also note that there was no reference to any specific value of any week (i.e. no hard-coding of data values), making the report immediately useful for subsequent weeks and ideal for adhoc reporting.

To make the report more readable, we divide the values by 1000 and change the labels. The decision to do this in the nextanalytics script, independant of the SQL syntax, reduces the need for the business analyst/report author to work with the SQL language. They can use a 'simple' SQL query provided to them by a database administrator and work more independently.

Rank the values down the columns

In our final report, we want the top 10 product groups listed, so we sort the page, descending, on the last week (Line 16 below). Again, since we do not reference the actual week number anywhere in the script, this sort command will find the most recent week automatically.

The next step is to rank the values down each column. The nextanalytics engine ranks the values in each time period and assigns the value '1' to the largest number in each column, '2' to the next largest and so on. In the script, this is accomplished with the simple command on line 18; ChangeValuesToRank. A new page now shows when each product group appeared in the Top 10 in every period. Although there is not a lot of variation with this particular data, you will notice that the first few weeks do have product groups in slightly different orders.

Alert based on the ranking

Because nextanalytics maintains an in-memory state of each processed step, all previous pages are available for subsequent analytic operations. In this example, we'll display the original page, but use the page or rank numbers to drive a color scheme of alerts. This is done with a single script command (script line 25). 

nextanalytics supports alert levels from 1 to 10 and we can use rankings from the cells, a specific column, a specific row, or another page to drive the alert colors.  In the user interface (which is Open Source), we use a cascading style sheet with ten elements in it.  Remember: This part of the product is Open Source, so you can modify it to display the data any way that you want.

The resulting report now shows how each Product Group performed.  Note that the 'Frozen Meals' and 'Spirits' product groups have battled for second place over time, as evidenced by the alternating orange colors, while the rest have been consistently positioned.

Calculate the week-to-week growth

Let's return to the original query and crosstab from an earlier step. In the script, we simply select the page that is still in memory. This time, we want to calculate week-to-week growth of each product group. Line 31 returns to the original crosstab, but continues along a different path by calculating the week-to-week percentage growth over the whole page (line 32), which results in a new page which we sort and label as "finalgrowth".

Rank and highlight the top 10

Working with the Growth page, we can perform a similar analysis as with sales. First, we rank the growth numbers in each time period (down the columns) and then we use those rankings to highlight the top 10 in each week.

This report shows a lot more variation than the previous Sales report. For example, 'Catering Packs' are showing that they are one of the top growing product groups most often.

Repeat for net change 

So far, we've done analysis on sales and growth.  Let's repeat those analytics for Net Change, or the week-to-week difference in sales. Analyzing net change can be useful because it shows the absolute value of the change better than percentage growth, and the absolute value has a direct effect on the bottom line of profitability.

The first step is to return to the original crosstab page and compute the net change from cell to cell, left to right in line 49 of the script. The page then is sorted, named, ranked and alerted. Like with the other reports, the alerting shows which product groups had the largest net change stand out. In this case, Medical produced the largest effect on the corporate bottom line more frequently but there were several others as well.

This kind of chart also makes it easy to visualize the trends over time. If preferred, the data could be fed to charting application for a different kind of data visualization.

Summary and complete script listing

By leveraging the in-memory analytics capabilities of nextanalytics, three entirely different perspectives of the data were obtained from a single, simple SQL query. Using the nextanalytics ranking and alerting functions, with the abililty to easily compute growth and net change, patterns can be made readily visible even on a simple tablular report.

The separation of reporting analytics from the SQL query makes it much easier for the typical business analyst to produce insightful reports without having to learn advanced SQL syntax.

The combination of the analytical capabilities of nextanalytics with the power and speed of the Kognitio WX2 database makes all this possible.