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:

How to configure Analysis Services (SSAS) 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:
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

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:

• prefix {0} suffix → append custom prefix and/or suffix
• {0:yyyy-MM-dd} → format date (or timestamp) as 2017-05-25
• {0:MMM} → format month number (1-12) as a short month name (Jan, Feb etc)
• {0:MMMM} → format month number (1-12) as a full month name (January, February etc)
• {0:ddd} → format day-of-week number (0-6) as a short day-of-week name (Mon, Tue etc)
• {0:dddd} → format day-of-week number (0-6) as a full day-of-week name (Monday, Tuesday 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

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,.0#}k → show number in thousands with "k" suffix
• {0:0.#|k} → if number>1000 shorten it with "k" suffix
• {0:0,,.0#}M → show number in millions with "M" 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"].