Documentation
Demos
Support
Blog
Download Free Trial
Search results

Connecting to Microsoft SQL Server Analysis Services(SSAS) data source

Bold BI supports connecting Microsoft SQL Server Analysis Services (SSAS) database using the SQL MDX Query (C# API).

Choose a Microsoft SQL Server Analysis Services (SSAS) data source

To configure the Microsoft SQL Server Analysis Services (SSAS) data source, follow these steps:

  1. Click the Data Sources button in the configuration panel to add a new data connection.

    Data source icon

  2. Click CREATE NEW to launch a new connection from the connection panel.

  3. Select the Microsoft SQL Server Analysis Services connection in the connection panel.

    Choose data source

NOTE: You can also create a data source from the home page by clicking the Data Sources menu from left menu panel and Create Data Source from the data sources page.

Choose data source server

Connect to Microsoft SQL Server Analysis Services (SSAS)

Create a Microsoft SQL Server Analysis Services (SSAS) data source

After clicking the data source, the NEW DATA SOURCE configuration panel opens. Follow the given steps to create a Microsoft SQL Server Analysis Services (SSAS) data source:

  1. Enter a name and description (optional) for the data source.

  2. Enter a valid SSAS server (Syncfusion supports both multi-dimensional tabular mode connection) or host name in the Server name text box.

  3. Choose None, Windows Authentication, or Server Authentication in Authentication Mechanism combo box.

  4. Enter a valid SSAS server username and password, if you choose Server Authentication Mechanism.

  5. Select the database you want to query in the listed database associated with the given SSAS Server in Database combo box.

    SSAS Connection

In future, you can edit the connection information using the Edit Connection option.

Cube customization

IMPORTANT: Code view and join operation is not supported and the buttons are always in disable state. And data preview is also not supported for the dragged cube.

  1. Click Connect to connect the SSAS server with configured details.

The available Cubes list is shown in treeview for the selected database that are retrieved from the SSAS server.

Treeview schema

  1. In the data design view page, drag and drop the cube.

    Query designer

  2. Syncfusion only supports String to Date conversion. You can convert the string to date format and then use this column in the filter and widget operations.

    Column customization

  3. Click Save to save the data source with a relevant name.

Connect to expression designer

Expression designer is used to create a calculated field or column based on the field(s) or column(s) in selected cube(s).

Expression Designer

Supported expression list

Function Type Function Name
Numbers ABS, ACOS, ASIN, ATAN, COS, DEGREES, EXP, LOG, PI, POWER, RADIAN, ROUND, SIGN, SIN, SQRT, TAN
Aggregation AVG, COUNT, COUNTD, MAX, MIN, STDEV, SUM, VAR
Logical AND, OR, NOT
Conditional IF, IFNULL, ISNULL, ISNOTNULL
Date NOW, DATESUB, DATEADD, TODAY
String CHAR, LEN, LEFT, RIGHT, LTRIM, RTRIM

Examples for SSAS supported expressions:

Category Functions Syntax, Descriptions & Examples
Numbers ABS
    Syntax:
    ABS(numeric_expression)

    Description:
    Returns the absolute value of a given expression.

    Example:
    ABS([Average Rate])
Numbers ACOS
    Syntax:
    ACOS(numeric_expression)

    Description:
    Returns the inverse cosine (also known as arccosine) of the given numeric expression.

    Example:
    ACOS(0.25)
Numbers ASIN
    Syntax:
    ASIN(numeric_expression)

    Description:
    Returns the inverse sine (also known as arcsine) of the given numeric expression.

    Example:
    ASIN(0.25)
Numbers ATAN
    Syntax:
    ATAN(numeric_expression)

    Description:
    Returns the inverse tangent (also known as arctangent) of the given numeric expression.

    Example:
    ATAN(0.25)
Numbers COS
    Syntax:
    COS(numeric_expression)

    Description:
    Returns the cosine of the angle specified in radians in the given expression.

    Example:
    COS(0.25)
Numbers DEGREES
    Syntax:
    DEGREES(numeric_expression)

    Description:
    Returns the angle in degrees for the one specified in radians in the given numeric expression.

    Example:
    DEGREES(1.5708)
Numbers EXP
    Syntax:
    EXP(numeric_expression)

    Description:
    Returns the exponential value of the given expression.

    Example:
    EXP([Growth in Customer Base])
Numbers LOG
    Syntax:
    LOG(numeric_expression)

    Description:
    Returns the logarithm of the given expression to the specified base.

    Example:
    LOG(DEGREES(PI()))
Numbers PI
    Syntax:
    PI()

    Description:
    Returns the constant value of PI.

    Example:
    EXP(PI())
Numbers POWER
    Syntax:
    POWER(expression1, expression2)

    Description:
    Returns the value of the given expression (expression1) to the specified power (expression2).

    Example:
    POWER(EXP(1), SIN(90))
Numbers RADIANS
    Syntax:
    RADIANS(numeric_expression)

    Description:
    Returns the angle in radians for the one specified in degrees in the given numeric expression.

    Example:
    RADIANS(90)
Numbers ROUND
    Syntax:
    ROUND(numeric_expression)

    Description:
    Returns a rounded value.

    Example:
    ROUND([Amount])
Numbers SIGN
    Syntax:
    SIGN(numeric_expression)

    Description:
    Returns a value representing the positive (+1), zero (0), or negative (-1) sign of the given numeric expression.

    Example:
    SIGN([Max Quantity])
Numbers SIN
    Syntax:
    SIN(numeric_expression)

    Description:
    Returns the sine of the angle specified in radians in the given expression.

    Example:
    SIN(0.25)
Numbers SQRT
    Syntax:
    SQRT(numeric_expression)

    Description:
    Returns the square root of the given numeric expression.

    Example:
    SQRT([Order Count])
Numbers TAN
    Syntax:
    TAN(numeric_expression)

    Description:
    Returns the tangent of the given numeric expression.

    Example:
    TAN(0.25)
Aggregation AVG
    Syntax:
    AVG(numeric_expression)

    Description:
    Returns the average of the values in the given expression.

    Example:
    AVG([Customer Count])
Aggregation COUNT
    Syntax:
    COUNT(numeric_expression)

    Description:
    Returns the number of items in the given expression.

    Example:
    COUNT([Account type])
Aggregation COUNTD
    Syntax:
    COUNTD(expression)

    Description:
    Returns the distinct number of items in the given expression.

    Example:
    COUNTD([Amount])
Aggregation MAX
    Syntax:
    MAX(numeric_expression)

    Description:
    Returns the maximum value in the given expression.

    Example:
    MAX([Amount])
Aggregation MIN
    Syntax:
    MIN(numeric_expression)

    Description:
    Returns the minimum value in the given expression.

    Example:
    MIN([Amount])
Aggregation STDEV
    Syntax:
    STDEV(numeric_expression)

    Description:
    Returns the standard deviation of values in the given expression.

    Example:
    STDEV([Average Rate])
Aggregation SUM
    Syntax:
    SUM(numeric_expression)

    Description:
    Returns the sum of values in the given expression.

    Example:
    SUM([Amount])
Aggregation VAR
    Syntax:
    VAR(numeric_expression)

    Description:
    Returns the variance of values in the given expression.

    Example:
    VAR([Average Rate])
Conditional IF
    Syntax:
    IF(expression, true_part, false_part)

    Description:
    Returns either true part or false part, depending on the evaluation of the expression.

    Example:
    IF([Average Rate]>50,[Amount],0)
Conditional IFNULL
    Syntax:
    IFNULL(expression1, expression2)

    Description:
    Returns expression1 if the expression1 evaluates to be not null.

    Example:
    IFNULL([Account Type],'Account not specified')
Conditional ISNOTNULL
    Syntax:
    ISNOTNULL(expression)

    Description:
    Returns true if the given expression evaluates to be not null.

    Example:
    ISNOTNULL([Account Type])
Conditional ISNULL
    Syntax:
    ISNULL(expression)

    Description:
    Returns true if the given expression evaluates to null.

    Example:
    ISNULL([Account Type])
Logical AND
    Syntax:
    (expression1) AND (expression2)

    Description:
    Returns true if both the expressions evaluate to true.

    Example:
    IF([Category]='Accessories' AND [Average Rate]='50', [Amount], 0)
Logical NOT
    Syntax:
    NOT(expression)

    Description:
    Returns the reversed logical value of the expression being evaluated.

    Example:
    IF(NOT [subCategory]='Helmets', [Amount]-1,[Amount])
Logical OR
    Syntax:
    (expression1) OR (expression2)

    Description:
    Returns true if any of the expressions evaluate to true.

    Example:
    IF([subCategory]='Helmets' OR [Average Rate]>1, [Amount]-1, [Amount])
Date DATEADD
    Syntax:
    DATEADD(datepart,numeric_expression, date_expression)

    Description:
    Adds a time/date interval to the specified date.

    Example:
    DATEADD(year,1,[Start Date])
Date DATESUB
    Syntax:
    DATESUB(numeric_expression, date_expression)

    Description:
    Returns the date subtracted from the specified date.

    Example:
    DATESUB(7,[Start Date])
Date NOW
    Syntax:
    NOW()

    Description:
    Returns the current date and time.

    Example:
    NOW()
Date TODAY
    Syntax:
    TODAY()

    Description:
    Returns the current date.

    Example:
    TODAY()
String LEN
    Syntax:
    LEN(string_expression)

    Description:
    Returns the number of characters in the given string expression.

    Example:
    LEN([Accounts])
String CHAR
    Syntax:
    CHAR(integer_expression)

    Description:
    Converts the given integer ASCII code into a character.

    Example:
    CHAR(70)
String LEFT
    Syntax:
    LEFT(string_expression, numeric_expression)

    Description:
    Returns the specified number of characters from the start of the given string expression.

    Example:
    LEFT([Departments],6)
String LTRIM
    Syntax:
    LTRIM(string_expression)

    Description:
    Returns the string value with any leading blanks from string expression.

    Example:
    LTRIM(‘Remove trailing spaces.’)
String RIGHT
    Syntax:
    RIGHT(string_expression, numeric_expression)

    Description:
    Returns the specified number of characters from the end of the given string expression.

    Example:
    RIGHT([Departments],6)
String RTRIM
    Syntax:
    RTRIM(string_expression)

    Description:
    Returns the string value with any trailing blanks removed from a string expression.

    Example:
    RTRIM(‘Remove trailing spaces.’)

Note: Please make sure to configure the string dimension field in the widget along with the created expression field to get the desired results.

Query filters or Initial filters

Query filters can be used to filter data for the created data source. For example, if you want to create a data source to show share market data only for year 2018, then you can use this filter.

Query filters

You can configure filter by dragging the required columns to right side canvas.

Filter DragDrop

Connecting through MDX query

You can connect to the data source of certain data connections through custom MDX Query. By default, the data design view opens with the design view. Switch to the code view by enabling the toggle option in the tools pane in the data design view as shown in the following image.

Code view toggle

This will open the query editor window as follows.

Query editor window

In the Query Editor Window, you can enter the MDX Query which helps to access table schema information. After entering your query, click the Run button in the tools pane.

MDX query

You can also view the data for your query at bottom of the Query Editor by clicking Update.

Preview data

Features Limitations:

When you use the Microsoft SQL Server Analysis Services data source, not all the features provided in the Bold BI work the same as with relational data sources or are even available.

The following table lists the differences.

Feature Support Availability
Extract Mode connection Not applicable
Aggregating Value Columns Based on Fields Not applicable
Data Sampling Not applicable
Dynamic connection string Not applicable
Shared Tables Not applicable
Joining Tables Not applicable
User Filter Not applicable
Preview Data source Not applicable
Expressions

Please find supported expression items here.

Below Expression items are not available in the Expression Module.

Numbers: COT, CEIL, FLOOR

Aggregation: AVGD, TOTAL, SUMD

Conditional: COALESCE

Date: CURRENT YEAR, CURRENT MONTH, DATEDIFF, DATENAME, DATEPART, DAY, DAYDIFF, HOUR, MINUTE, MONTH, PREVIOUSMONTH, PREVIOUS YEAR, YEAR, YESTERDAY

String: CONCAT, CONTAINS, ENDSWITH, LOWER, MAX, MIN, REPLACE, STARTSWITH, SUBSTR, TRIM, UPPER

Row: RUNNING COUNT, RUNNING AVG, RUNNING MAX, RUNNING MIN, RUNNING SUM
Sorting Field based sorting support not available
Customizing Tooltip Support available for Measure fields
Formatting Columns Renaming column support not available and date, string to integer, integer to string, date type conversion support not available.
Code view support Support available for tables Internal Queries

NOTE: We have provided the code view mode support in SQL Server Analysis Services using subselects.

Subselects have the following Limitation:
• Usage of “WITH” and “DAX” queries is restricted and will be provided in the future through extract mode.
• The WHERE clause does not filter the subspace.
• The WHERE clause changes the default member in the sub-cube only.
• The NON-EMPTY clause is not allowed in an axis clause; use a filter (MDX) function expression instead.
• The HAVING clause is not allowed in an axis clause. Use a filter (MDX) function expression instead.
For reference, click this link.

Connecting Bold BI to Microsoft SQL Server Analysis Services(SSAS) via REST API

Prerequisites

Type while creating the data source needs to be ssas.

Rest API - v4.0

Parameters for creating Data Source

Parameters Details
Servername

required 		`string`

Server name or Host name of the connection
IntegratedSecurity

optional 		`string`

This is used to specify Authentication mechanism. Need to provide true for Windows Authentication, false for Basic Authentication.

default value is false
Username

optional 		`string`

A valid username. Required if Integrated Security is not provided or is provided as false.
Password

optional 		`string`

A valid Password. Required if Integrated Security is not provided or is provided as false.
Database

required 		`string`

database which needs to be connected
Schemaname

required for table mode 		`string`

Enter a valid Schemaname. Need to provide this as Cubes.
Tablename

required for table mode 		`string`

Enter a valid Tablename
Query

required for code view mode 		`string`

Enter a valid Query
Impersonate

optional 		`boolean`

Enable or disable user impersonation. By default, it is false.
AdvancedSettings

optional 		`string`

Additional optional connection parameters can be provided. By default, it is empty.
CommandTimeout

optional 		`string`

Enter a valid Timeout for connection. By default, it is 300
Expressions

optional

`Array of Objects`

Parameters for adding expressions when creating Data Source

Parameters Details
Name

required

`string`

Name of the Expression
Expression



required

`string`

Expression


Parameters for editing Data Source

NOTE: For editing Data Source via API. All the parameters are optional. The parameter which needs to be changed can be provided.

Parameters for modifying expressions when editing Data Source

Parameters Details
Name

required

`string`

Name of the Expression
Expression



required

`string`

Expression


Action



optional

`string`

add/delete/edit

By default it is add.

NewName

optional

`string`

For renaming the expression. This is applicable only if the Action is edit

Connection Sample for Table Mode

For creating connection:

"Connection": [
{
"Servername": "string",
"IntegratedSecurity": "false",
"Username": "string",
"Password": "string",
"Database": "string",
"Schemaname": "Cubes",
"Tablename": "string",
"Impersonate": "false",
"AdvancedSettings": "string",
"CommandTimeout": "300",
"Expressions" : [{
"Name": "Expression1",
"Expression" : "SUM(numeric expression)"
    },
    {
"Name": "Expression2",
"Expression" :  "UPPER(string expression)"
}]
}
]

For editing connection:

"Connection": [
{
"Servername": "string",
"IntegratedSecurity": "false",
"Username": "string",
"Password": "string",
"Database": "string",
"Schemaname": "Cubes",
"Tablename": "string",
"Impersonate": "false",
"AdvancedSettings": "string",
"CommandTimeout": "300",
"Expressions" : [{
"Name": "Expression1",
"Expression" : "SUM(numeric expression)",
"NewName" : "Sum",
"Action": "edit"
    },
    {
"Name": "Expression2",
"Expression" :  "UPPER(string expression)"
"Action": "delete"
}]
}
]

Connection Sample for Code View Mode

"Connection": [
{
"Servername": "string",
"IntegratedSecurity": "false",
"Username": "string",
"Password": "string",
"Database": "string",
"Query": "string",
"Impersonate": "false",
"AdvancedSettings": "string",
"CommandTimeout": "300",
"Expressions" : [{
"Name": "Expression1",
"Expression" : "SUM(numeric expression)"
    },
    {
"Name": "Expression2",
"Expression" :  "UPPER(string expression)"
}]
}
]

Data Transformation

Editing a Data Connection

Dashboard Designer Walkthrough

Microsoft SQL Server Analysis Services Integration

Previous
Next