Syncfusion OLAP engine query

syncfusion-olap-engine.md

Syncfusion has a great JavaScript pivot table. Under the covers is support for OLAP (Analysis Services or Mondrian) not just relational or JSON or CSV. This support is implemented in a class called OlapEngine. This engine can be used separately to access data from an AS cube when there is a need to create some kind of custom report. The engine module can be imported:

import { OlapEngine } from '@syncfusion/ej2-pivotview';

Note this use of import assumes there is a script map from '@syncfusion/ej2-pivotview' to a web accessible place that implement OlapEngine.

The engine can then be used to access data:

let olapEngine = new OlapEngine();
olapEngine.renderEngine( PivotUtil.getClonedDataSourceSettings( dataSourceSettings ) );

console.log( olapEngine.pivotValues );

The key to retrieving data are the data source settings. This is a JSON object. These settings define the rows, columns, values and filters to retrieve.

When used outside a PivotView, its essential some of the data source settings are initialised. When used with a pivot view, the pivot view will set them but outside a pivot view these must be initialised explicity. 4 top level properties fall into this category:

drilledMembers: [],
authentication: {},
valueSortSettings: [],
allowMemberFilter: true

In addition, slicer queries can be generated with specific filters. When these are used the filters MUST include a type of 'Include' which is not necessary when the data source settings will be used with a pivot table. For example:

filterSettings: [
    {
        name: '[Date].[Fiscal]', 
        items: [
                '[Date].[Fiscal].[Fiscal Quarter].&[2002]&[4]',
                '[Date].[Fiscal].[Fiscal Year].&[2005]'
        ],
        type: "Include",
        levelCount: 3
    }
]