Introduction to Multidimensional Tables

A multidimensional table, also known as a pivot table, cross-tabulation, or pivot table, refers to a type of table that allows the inclusion of one or more dimensions in both the row and column dimensions, displaying the relationships between dimensions. Users can easily analyze various scenario metrics and comparisons, aiming to assist in business analysis and decision-making.

Suppose we need to analyze sales data as shown in the table below:

When analyzing this type of tabular data, different data analysts or roles may raise relevant questions based on their business interests. For example, some may be interested in the sales revenue across different regions and would like to identify regions with lower sales performance. Others may want to understand the sales revenue comparison for different product categories in recent periods to facilitate product research and development.

In these questions, the business perspectives such as region, category, and time period (year) are dimensions, while “sales revenue” is the metric or measure.

Concepts of Multidimensional Tables

To understand the core concepts in multidimensional analysis in business intelligence (BI), consider the following:

Dimensions: These are the perspectives or angles used to classify and observe business data. Dimensions provide the context for analyzing data and are used to categorize and filter information. Examples of dimensions include region, category, and time.
Dimension Hierarchies: Dimension hierarchies represent different levels of detail within a dimension. They are predefined levels of a dimension based on varying levels of granularity. For example, a date dimension hierarchy might include levels such as year, month, and day. Similarly, a region dimension hierarchy might have levels such as country, province, and city. Each level within a dimension hierarchy is considered a dimension itself, with a hierarchical relationship among its members. Dimension hierarchies are often used for drilling down or rolling up data during analysis.
Dimension Members: These are the specific values or items within a dimension. They represent the distinct values that can be attributed to a dimension. For instance, within the month level of the date dimension, the dimension members could be January, February, March, and so on. Similarly, for the region dimension, members could include Northeast, North China, Central China, and so forth.
Indicators: Indicators are the data that describe the business situation or performance. They are quantitative values used to analyze and evaluate business metrics. Examples of measures include sales revenue, costs, profits, and other quantitative indicators.

How can we display multiple dimensions in a multidimensional analysis table? In the following image, there are four business dimensions: Region, Province, Year, and Quarter, and two indicators: Sales and Profit.

Regarding the sales data in the figure, the location is in cell [5, 5], that is, the data in column 5 and row 5: represents the sales profit value of Heilongjiang Province in the Northeast region in the Q2 quarter of 2016. That is to say, it corresponds to the row dimension value: [‘Northeast’, ‘Heilongjiang’], the column dimension: [‘2016’, ‘2016-Q2’], and the indicator: ‘Profit’. Next, we will introduce how to use VTable to implement this multi-dimensional table.

VTable implements multi-dimensional tables

Concept mapping to configuration items

The configuration of the pivot table above is as follows:

const option={  
  rows:['region','province'], //row dimensions  
  columns:['year','quarter'], //column dimensions  
  indicators:['sales','profit'], //indicators  
  records:[ //datasource  
    {  
      region:'东北',  
      province:'黑龙江',  
      year:'2016',  
      quarter:'2016-Q1',  
      sales:1243,  
      profit:546  
    },  
    ...  
  ]  
}

This configuration is the simplest configuration for multidimensional tables. As the functional requirements become more complex, various configurations can be added for each function point to meet the needs.

Data analysis related configuration:

dataConfig Type Define：

/**  
 * Data processing configuration  
 */  
export interface IDataConfig {  
  aggregationRules?: AggregationRules;  
  sortRules?: SortRules;   
  filterRules?: FilterRules;   
  totals?: Totals;   
  derivedFieldRules?: DerivedFieldRules;   
  ...  
}

DataConfig application examples：

Total or sub-total rules

demo：https://visactor.io/vtable/demo/data-analysis/pivot-analysis-total


dataConfig: {  
      totals: {  
        row: {  
          showGrandTotals: true,  
          showSubTotals: true,  
          subTotalsDimensions: ['province'],  
          grandTotalLabel: 'RowTotal',  
          subTotalLabel: 'sub-total'  
        },  
        column: {  
          showGrandTotals: true,  
          showSubTotals: true,  
          subTotalsDimensions: ['quarter'],  
          grandTotalLabel: 'ColumnTotal',  
          subTotalLabel: 'sub-total'  
        }  
      }  
    },

Sort rules

demo：https://visactor.io/vtable/demo/data-analysis/pivot-analysis-sort-dimension

dataConfig: {  
    sortRules: [  
      {  
        sortField: 'Category',  
        sortBy: ['Office Supplies', 'Technology','Furniture']  
      },  
      {  
        sortField: 'Sub-Category',  
        sortBy: ['Chairs', 'Tables','Labels', 'Art', 'Paper', 'Appliances']  
      }  
    ],  
  },

Filter rules

demo：https://visactor.io/vtable/demo/data-analysis/pivot-analysis-filter

dataConfig: {  
    filterRules: [  
      {  
        filterFunc: (record) =>record['Sub-Category'] !== 'Chairs'  
      }  
    ]  
  },

Aggregation rules

demo：https://visactor.io/vtable/demo/data-analysis/pivot-analysis-aggregation

dataConfig: {  
    aggregationRules: [  
        //As the basis for aggregate calculations, if the sales amount is not configured,   
        //the cell content will be displayed by default according to the aggregate sum calculation.  
        {  
          indicatorKey: 'TotalSales', //Indicator name  
          field: 'Sales', //The field  which the indicator is based  
          aggregationType: VTable.TYPES.AggregationType.SUM, //Calculation type  
          formatFun: sumNumberFormat  
        },  
        {  
          indicatorKey: 'OrderCount', //Indicator name  
          field: 'Sales', //The field  which the indicator is based  
          aggregationType: VTable.TYPES.AggregationType.COUNT, //Calculation type  
          formatFun: countNumberFormat  
        },  
        {  
          indicatorKey: 'AverageOrderSales', //Indicator name  
          field: 'Sales', //The field  which the indicator is based  
          aggregationType: VTable.TYPES.AggregationType.AVG, //Calculation type  
          formatFun: sumNumberFormat  
        }  
      ]  
  },

Derived field rules

demo：https://visactor.io/vtable/demo/data-analysis/pivot-analysis-derivedField

derivedFieldRules: [  
      {  
        fieldName: 'Year',  
        derivedFunc: VTable.DataStatistics.dateFormat('Order Date', '%y', true),  
      },  
      {  
        fieldName: 'Month',  
        derivedFunc: VTable.DataStatistics.dateFormat('Order Date', '%n', true),  
      }  
    ]

data analysis process

Dependent configuration: dimensions, indicators and dataConfig.

The process of traversing data:

Traverse the records once, parse the row header dimension value to display the header cell, distribute all data in the records to the corresponding row and column path set, and calculate the aggregate value of the body part indicator cell.

Data dimension tree

According to the above traversed structure, a dimension tree will be generated, from which the value of the cell and the original data entry of the value can be found.

After analysis and calculation of record grouping and aggregation, the corresponding relationship between the cell data in the table and the records data source is finally presented:

Custom dimension tree

Although multidimensional tables with analytical capabilities can automatically analyze the dimension values of each dimension to form a tree structure of row headers, and can sort according to dataConfig.sortRules, scenarios with complex business logic still expect to be able to customize row header dimensions. values and their order. Then these business requirement scenarios can be realized through rowTree and columnTree.

Configuration details for custom trees:

const option = {  
    rowTree: [{  
        dimensionKey: 'region',  
        value: '中南',  
        children: [  
            {  
                dimensionKey: 'province',  
                value: '广东',  
            },  
            {  
                dimensionKey: 'province',  
                value: '广西',  
            }  
        ]  
    },  
    {  
        dimensionKey: 'region',  
        value: '华东',  
        children: [  
            {  
                dimensionKey: 'province',  
                value: '上海',  
            },  
            {  
                dimensionKey: 'province',  
                value: '山东',  
            }  
        ]  
    }],  
    columnTree: [{  
        dimensionKey: 'year',  
        value: '2016',  
        children: [  
            {  
                dimensionKey: 'quarter',  
                value: '2016-Q1',  
                children: [  
                    {  
                        indicatorKey: 'sales',  
                        value: 'sales'  
                    },  
                    {  
                        indicatorKey: 'profit',  
                        value: 'profit'  
                    }  
                ]  
            },  
            {  
                dimensionKey: 'quarter',  
                value: '2016-Q2',  
                children: [  
                    {  
                        indicatorKey: 'sales',  
                        value: 'sales'  
                    },  
                    {  
                        indicatorKey: 'profit',  
                        value: 'profit'  
                    }  
                ]  
            }  
        ]  
    }],  
    indicators: ['sales', 'profit'],  
    //enableDataAnalysis:true,  
    corner: {  
        titleOnDimension: 'none'  
    },  
    records: [  
        {  
            region: '中南',  
            province: '广东',  
            year: '2016',  
            quarter: '2016-Q1',  
            sales: 1243,  
            profit: 546  
        },  
        {  
            region: '中南',  
            province: '广东',  
            year: '2016',  
            quarter: '2016-Q2',  
            sales: 2243,  
            profit: 169  
        }, {  
            region: '中南',  
            province: '广西',  
            year: '2016',  
            quarter: '2016-Q1',  
            sales: 3043,  
            profit: 1546  
        },  
        {  
            region: '中南',  
            province: '广西',  
            year: '2016',  
            quarter: '2016-Q2',  
            sales: 1463,  
            profit: 609  
        },  
        {  
            region: '华东',  
            province: '上海',  
            year: '2016',  
            quarter: '2016-Q1',  
            sales: 4003,  
            profit: 1045  
        },  
        {  
            region: '华东',  
            province: '上海',  
            year: '2016',  
            quarter: '2016-Q2',  
            sales: 5243,  
            profit: 3169  
        }, {  
            region: '华东',  
            province: '山东',  
            year: '2016',  
            quarter: '2016-Q1',  
            sales: 4543,  
            profit: 3456  
        },  
        {  
            region: '华东',  
            province: '山东',  
            year: '2016',  
            quarter: '2016-Q2',  
            sales: 6563,  
            profit: 3409  
        }  
    ]  
};

The final effect is as follows:

VTable website demo：https://visactor.io/vtable/demo/table-type/pivot-table

The complexity of the custom tree lies in the formation of the row, column and dimension trees. You can choose to use it according to the business scenario. If you have complex sorting, aggregation or paging rules, you can choose to use a custom method.

Note that choosing a custom tree configuration method will not have data aggregation capabilities, that is, one of the matched data entries will be used as the cell indicator value.

Some typical application scenarios

Requirements: Different indicators set different formats

Configuration method: Set different cellTypes for indicators.

Demo*：*https://codesandbox.io/s/vtable-pivot-table-indicators-pxphqx

2. Requirements: Set different font sizes for different dimensions. Set the font size for the region dimension value of the row dimension to 20, and set the font size for the province dimension value to 16.

Configuration method: This can be achieved by configuring the style in the headerStyle of the corresponding dimension.

3. Requirement: Corner header configuration displays row dimension name.

Configuration method: By default, the corner header will display the dimension name in columns of the column dimension. If you want to display the dimension name of the row dimension in the corner header, configure titleOnDimension to row.

4. Requirements: Trend analysis table — showing sales in different time periods, year-on-year comparisons and trend charts

Configuration method: Set the cellType of one of the indicators to ‘sparkline’. You can use icon to configure the chart trend.

Demo*：*https://visactor.io/vtable/demo/business/trend

5. Requirement: Integrate charts into tables to express data more vividly

6. Requirements: PivotChart, comprehensive view of data distribution and trends by dimension and indicator

VTable can seamlessly integrate VChart and render the chart type of VChart as the element type of the cell, which greatly improves the visual expression of the table. At the same time, it also provides a new solution to solve the performance problem of single-page multi-chart rendering.