Getting Started with Wijmo's HTML5 OLAP Module: Create Basic Pivot Tables

March 15 2016

With the introduction of Wijmo OLAP module, you'll be able to implement high-speed HTML5 pivot charts and tables into your web-based applications. The built-in Excel-like UI and powerful data engine will allow you and your end users to analyze and aggregate raw data easily and meaningfully.

Full Wijmo OLAP series:

Getting Started with Wijmo's HTML5 OLAP Module: Create Basic Pivot Tables

Answer Recurring Questions with Pre-Defined Views

Filtering Data in a PivotGrid

Exporting a PivotGrid to Excel

Customizing the UI and PivotGrid

In this blog series, we'll be introducing various elements of Wijmo OLAP. Today we'll be covering:

Basic architecture elements
Building a basic view with raw data
Working with live data

Why Wijmo OLAP?

When you have a lot of data, it can be difficult to analyze all of the information by looking at it as a plain list of items. Pivot tables can help by summarizing data and allowing you to manipulate it in different ways. Excel has “Pivot Tables” that make this task easy. You select the raw data, click “Insert Pivot Table”, pick a destination, and get a panel where you can drag fields to summarize data in different ways, and instantly see the results. This is one of Excel’s most powerful and popular features. Wijmo OLAP allows your applications to provide the same functionality within your HTML5 applications. Add a PivotPanel control to a page, give it some raw data by setting its dataSource property, and connect a PivotGrid and/or PivotChart controls to see the results.

Wijmo OLAP Architecture

The diagram below illustrates the Wijmo OLAParchitecture: Wijmo OLAP Architecture

PivotEngine object

The PivotEngine object is at the core of the Wijmo OLAP module. It takes in the raw data (via its itemsSource property) and summarizes it according to parameters defined in code (including a list of the fields that should be included in the summary and their properties). The PivotEngine object is not a control and has no visual representation.

PivotPanel control

The PivotPanel control provides the drag-drop UI used to configure the PivotEngine object. Users can drag fields from the main field list to four “view” lists that represent row, columns, values, and filter. The fields in the “rows” and “columns” lists are sometimes referred to as “dimensions”. Fields in the “values” list are sometimes referred to as “measures”. For convenience, the PivotPanel control creates an internal PivotEngine object that it uses by default so you don’t have to create the engine explicitly. It exposes the engine via its engine property.

PivotGrid control

The PivotGrid control displays the summarized data (view) as a grid, with collapsible row and column groups and a custom context menu that allows users to drill-down into the data represented by each cell, or to configure the view. The PivotGrid control extends the FlexGrid, so it inherits important features such as custom cells and the ability to export the grid to XLSX files.

PivotChart control

Finally, the PivotChart control displays the summarized data (view) as a chart. It provides hierarchical axes that help convey the view structure, and multiple chart types including column, bar, area, line, scatter, and pie. The PivotChart control contains two controls: a FlexChart and a FlexPie. The inner controls are exposed via the flexChart and flexPie properties.

Starting with Raw Data

Imagine you're building a dashboard designed to answer questions such as:

How many product units have we sold?
Which countries bought the most units?

Imagine that the data is available from your server in typical database format: Basic database structure Answering questions like these require routines to scan through all the data and build summaries. Each product and country appears on multiple rows, and we'd need to total all of their different orders individually. This is relatively easy to do, but it does require you to write some code. And of course, as soon as you finish your app, users ask other questions:

What are the average units sold or downloaded for each product?
What is the quarterly sales trend per product?
Anything else you can dream up…

Answering questions with a PivotPanel and a PivotGrid

Now, instead of writing the code needed to summarize the data and the UI for users to select the information they want, you can simply add two controls to the page: a Wijmo PivotPanel and a PivotGrid. Here's the code:



// create PivotPanel and PivotGrid controls  
var pivotPanel = new wijmo.olap.PivotPanel('#pivotPanel' {  
itemsSource: myRawData  
});  
var pivotGrid = new wijmo.olap.PivotGrid('#pivotGrid', {  
itemsSource: pivotPanel  
});

Your users would use Excel-style drag-and-drop to build any views they wanted. Here are a few examples based on the questions we listed earlier:

Question: How many product units have we sold?

Answer: Drag the “Product” field into the “Rows” panel and the “Sales” field into the “Values” panel: Product units sold Since the data was randomly generated, we don't see any clear trends here. The sales for all products are very similar in this case.

Question: Which countries bought the most copies?

Answer: Drag the “Country” field into the “Rows” panel and the “Sales” field into the “Values” panel, then click the grid header to sort the results in descending order: Units grouped by country Notice how easy it is to sort the data: simply click the grid header as you would with a regular flat data grid.

Question: What are the average units sold or downloaded for each country?

Answer: Drag the “Country” field into the “Rows” panel and the “Sales” and “Downloads” fields into the “Values” panel, then right click the “Sales” and “Downloads” fields and set its “Summary” property to “Average”: Average units downloaded or sold per country As in Excel, you don’t have to drag the fiends into the view lists. You can simply use the checkboxes next to the field names to add or remove them from views with a single click. Notice how the PivotPanel displays the type of summary being used next to each field (in this case, “Avg”).

Question: What is the quarterly sales trend per product?

Answer: Drag the “Product” field into the “Columns”, the “Date” field into the “Rows” panel, and the “Sales” field into the “Values” panel. Then right-click the “Date” fields and set its “Format” property to “Year Quarter (yyyy "Q"q)”, and set the “Sales” field’s “Show As” property to “% Difference from previous row”: Quarterly sales trend per product Notice how the format used to display the “Date” field affects the grouping and summarization process. In this case, the data is summarized by quarter. Since the data was randomly generated, this example shows no clear trends.

Using Live Data

The views created by Wijmo OLAP controls are dynamic. The PivotPanel’s itemsSource property is a CollectionView that can be filtered, sorted, modified or refreshed at any time. Whenever a change happens, the views are automatically re-generated. This makes it easy to create dynamic dashboards. All you have to do is load the latest data into the CollectionView being used as an itemsSource and all PivotGrid and PivotChart controls will be automatically updated.