XMLA OLAP server connector for web pivot tables

SeekTable use OLAP server as a data source via XMLA-over-HTTP(S) endpoint:

• SQL Server Analysis Services (via IIS msmdpump)
• icCube OLAP server (v.8.4.14+)

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 SQL Server 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. 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("MDX_unique_name", value)	if value is present, adds a member-name slicer to <MdxSelect> object for the specified dimension's MDX selector. To generate member-key slicer you may specify .& suffix in the MDX selector. 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("MDX_unique_name", fromVal, toVal)	adds a member-name range slicer to <MdxSelect> object for the specified dimension's MDX selector. To generate member-key slicer you may specify .& suffix in the MDX selector. Either fromVal or toVal may be null (if parameter is not defined). Function returns <MdxSelect> so you can call it in a chain. Note: DateTime values are converted to "YYYYMMDD" (suitable for SSAS date-key slicer). You may use Format function to format dates differently.
   <MdxSelect>.CustomSlicer("MDX_unique_name", formatStr, value)	if value is present, formats a custom MDX slicer. For example: CustomSlicer("[Customers].[Geography].[Country]", "&[{0}]", param_country) Note that formatStr is formatted with .NET String.Format method and you can use necessary number/date format modifiers.

4. Keep Infer dimensions and measures by columns checked to determine dimensions and measures automatically (by DISCOVER request). You can modify proposed cube members 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.

icCube Connection String

For icCube XMLA Endpoint Settings are configured in the same way as for SSAS, the only difference is a Connection String value:

DataSource=http://localhost:8282/icCube/xmla?seekTable;Initial Catalog=Sales (Demo);

Please note that icCube version should be at least 8.4.14 or newer.

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"].