This documentation page assumes that you already have a SeekTable account. Create your free account by signing up.

Web pivot tables for XMLA OLAP

SeekTable can be connected to OLAP cube (like SQL Server Analysis Services) via XMLA-over-HTTP(S) endpoint. MDX queries are composed and executed on-the-fly; you can specify your own MDX SELECT template and define custom named sets using WITH keyword. XMLA connector features/limitations:

  • cube hierarchies are not loaded: reports are rendered fast even if cube has a huge number of members
  • report parameters are supported, but only 'equals' comparison is possible for dimension keys. The same goes for pivot filter translation: only full-match filtering criteria can be converted to MDX slicer.
  • all sub-totals and grand total are calculated on OLAP cube side. You may get empty cells or inadequate numbers for some combinations of dimensions; this depends on your AS cube configuration.
  • drill-down (click on the value cell) may lead to empty table if pivot table filter is translated to MDX slicers and one of the dimensions doesn't support slicing by the key like [Country].&[Canada]. To avoid this you may change AS cube configuration or exclude this dimension from the translation to MDX slicers.
Looking for a web pivot table component?
Check out PivotData microservice: SeekTable's reporting engine which can be seamlessly integrated into any web application for pivot tables generation by OLAP cubes with a simple web API.

How to configure Analysis Services as a data source

  1. Click on "Connect to Database" item at "Cubes" view (or open this link if you're logged in).
  2. Select "XMLA Client" in Data Source Type selector: XMLA OLAP connection settings
  3. Please fill out all required fields:
    Cube Name
    short title that describes this data source
    Connection String
    determines connection properties for the ADOMD. For example:
    Data Source=http://localhost/olap/msmdpump.dll;Initial Catalog=Adventure Works DW Standard Edition;Connect Timeout=30;
    Note: only HTTP or HTTPS URL may be used as a Data Source. For SSAS HTTP access can be configured with IIS + msmdpump.dll. Important: only Anonymous or Basic authentication are supported by SeekTable XMLA OLAP connector.
    MDX Select Query
    a template for MDX SELECT. Placeholders for the tokens (dynamically-generated MDX parts) are specified with @token_name syntax. For example:
    SELECT @AXES FROM @CUBE
    where @AXES is a special pre-defined token, and @CUBE is defined with an expression (see below).
    MDX Query Tokens
    You can define custom tokens for MDX template that are calculated in a run-time with expressions. In this way you can handle user-entered report parameters and include MDX parts conditionally, for example @CUBE may be defined as:
    SelectFromCube("Adventure Works").Where("[Geography].[Geography].[Country]", Parameter["country"] )
    Expressions syntax is the same as for calculated fields but evaluation context is a different:
    Function Description
    Parameter["parameter_name"] returns value of the report parameter with name="parameter_name". If parameter name contains only letter/digits/'_' it can be referenced simply as parameter_name.
    DIMENSIONS list of dimension names used in the MDX query. For example, you may check if concrete dimension is used with DIMENSIONS.Contains("[Geography].[Geography].[Country]").
    SelectFromCube("olap_cube_name") helper function that composes sub-cube SELECT with slicers and returns special <MdxSelect> object. Usage of this function is required if you want to translate pivot table filter into MDX slicers or apply report parameters to filter by concrete dimension members.
    <MdxSelect>.Where("dimension", value) adds a slicer to <MdxSelect> object for the specified dimension. If value is null (parameter is not defined) slicer is not applied. Function returns <MdxSelect> so you can call it in a chain like Where().Where().
    <MdxSelect>.Range("dimension", fromVal, toVal) adds a range slicer to <MdxSelect> object for the specified dimension. Either fromVal or toVal may be null (if parameter is not defined). Function returns <MdxSelect> so you can call it in a chain.
  4. Keep Infer dimensions and measures by columns checked to determine dimensions and measures automatically. You can modify suggested configuration later.
  5. Click on "Save" button.

If everything is fine you should see a new cube dashboard with the list of available dimensions and measures.

In case of connection error you'll see an orange box with an error message; you may click on "Edit Configuration" and apply necessary changes to solve the issue.

Dimensions setup

XMLA OLAP dimensions setup
Type
Field: dimension value is resolved from OLAP cube dimension attribute.
Expression: dimension is defined as calculated field with custom formula that uses another dimensions as arguments (formula and arguments should be specified in "Parameters"). This kind of calculation is performed on SeekTable side.
Name
Unique dimension identifier. For Type=Field this is a dimension attribute specifier like:
[Product].[Category]
Last element can be a dimension property to load like [MEMBER_CAPTION], [MEMBER_NAME], [MEMBER_VALUE], [MEMBER_UNIQUE_NAME]; if property is not specified MEMBER_CAPTION is loaded by default.
Label
User-friendly dimension title (optional).
Format
Custom format template (syntax is for .NET String.Format, only zero-index placeholder {0} can be used). Examples:
  • {0:yyyy-MM-dd} → format date (or timestamp) as 2017-05-25
  • {0:MMM} → format month number (1-12) as short month name (Jan, Feb etc)
  • {0:MMMM} → format month number (1-12) as full month name (January, February etc)
Parameters
Type=Field: you can specify a custom MDX expression to define a set for this dimension. For example, if .ALLMEMBERS doesn't include (All) (which is needed for totals) you may include it explicitely by specifying {[Product].[Category].members, [Product].[Category].[All Products]}.

Type=Expression: you can specify formula expression (1-st parameter) and dimension names for the arguments (2-nd, 3-rd etc parameter).

Measures setup

XMLA OLAP measures setup
Type
Cube Measure: OLAP cube measure.
Expression: measure is defined with an expression-based formula field. This kind of calculation is performed on SeekTable side.
Parameters
For Type=Cube Measure: first parameter is an OLAP cube measure specifier like [Measures].[Sales Amount]. For Type=Expression: first parameter is a formula expression, and next parameters are names of measures used as arguments in the expression.
Name
Explicit unique measure identifier. In case of Type=Cube Measure you can leave it blank to generate the name automatically.
Label
User-friendly measure caption (optional).
Format
Custom format template (syntax is for .NET String.Format, only zero-index placeholder {0} can be used). Examples:
  • {0:$#.##} → format number as $10.25 (or empty if no value)
  • {0:0.#|k} → if number>1000 shorten it with "k" suffix
  • {0:0.#|M} → if number>1000000 shorten it with "M" suffix
  • {0:0.#|kMB} → shorten large number with appropriate "k"/"M"/"B" suffix

Report parameters setup

Report parameter allows you to specify some filtering condition in MDX query by user-entered value. Also you can use parameters to affect MDX query generation.

Name
Unique (for this cube) parameter identifier.
Label
User-friendly parameter caption for UI (optional).
Data Type
String: text-based value.
Int32: 32-bit integer (max value is 2,147,483,647).
Int64: 64-bit integer (max value is 9,223,372,036,854,775,807).
Decimal: Fixed-point number with max 28 significant digits. Decimal point is '.' character.
DateTime: datetime or date value (in this case datetime value has 0:00:00 time). Date value should be specified as string in YYYY-MM-DD format.
Boolean: accepts only 'True' or 'False' value.
Multivalue
If checked parameter can accept several values (as array, in UI user can enter them as comma-separated string). Multivalue parameter can be used only with IN condition.
Default Value
Defines default value of this parameter. Empty means 'not defined'.
Expression
Custom expression to evaluate final parameter value. Expression syntax is the same as in calculated cube members; you can access user-entered values with Parameter["param_name"].
If you're not sure how to configure XMLA OLAP as a data source feel free to contact us and ask for assistance.