Getting started with OLAP for ASP.NET MVC: Create a Basic Pivot Table
With the introduction of MVC OLAP control, 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.
View the samples: ASP.NET 4.0 | ASP.NET Core
In this blog series, we’ll be introducing various elements of MVC OLAP. Today we’ll be covering:
- Basic architecture elements
- Building a basic view with raw data
- Working with live data
Why OLAP for MVC?
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.
ComponentOne Studio OLAP for MVC allows your applications to provide the same functionality within your web applications. Add a PivotPanel control to a view page, give it some raw data by setting its dataSource property, and connect a PivotGrid and/or PivotChart controls to see the results.
ASP.NET MVC OLAP Architecture
The diagram below illustrates the MVC OLAP architecture:
ASP.NET MVC OLAP Architecture
PivotEngine object
The PivotEngine object is at the core of the OLAP control. It takes in the raw data (via its Bind 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. 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.
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”.
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.
DataEngine WebAPI
Sometimes the data that needs to be analysed is huge, it is not recommended to transfer the whole data to client and then perform the aggregation, it may slow down the application and may cause serialisation issues. In such scenario, the data should be aggregated on the server where there is no memory limitations. The DataEngine WebAPI caters to such scenarios, it provides a suite of APIs that allow the user to analyse data from multiple data sources, including SQL Server, other SQL based RDMS servers, NoSQL service, web service, structured files from file/network systems, and more. The aggregating data can be consumed by other controls or application. DataEngine Web API uses a column-oriented data model which is widely used in many open source and commercial analytical databases and libraries. Data Engine can handle up to hundreds of millions records in a fraction of a second. The aggregated data is fetched from the Web API, the client application is simple, and it only sends the corresponding query to the server with some format to fetch the data. For more information about Data Engine services, see Configuring Data Engine Services.
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 summarise the data and the UI for users to select the information they want, you can simply add two controls to the page: a PivotPanel and a PivotGrid.
Here’s the code:
@Html.C1().PivotPanel().Id(“panel”).Bind(Model)
@Html.C1().PivotGrid().Id("indexGrid").ItemsSourceId("Panel")
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 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.
Available in ASP.NET 4.0 and ASP.NET Core
OLAP for MVC is available in ASP.NET 4.0 and ASP.NET Core. In ASP.NET Core, you have choice of using the control HtmlHelper or the TagHelper. See the control in action:
Take a look at the documentation for more details.
GrapeCity