Documentation

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:

Click the Data Sources button in the configuration panel to add a new data connection.
Click CREATE NEW to launch a new connection from the connection panel.
Select the Microsoft SQL Server Analysis Services connection in the connection panel.

NOTE: You can also create a data source from the home page by clicking on the Data Sources menu on the left menu panel and selecting 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 on the data source, the NEW DATA SOURCE configuration panel opens. Follow the steps to create a Microsoft SQL Server Analysis Services (SSAS) data source:

Enter a name and description (optional) for the data source.
Enter a valid SSAS server (Syncfusion supports both multi-dimensional tabular mode connection) or host name in the Server name text box.
Choose None, Windows Authentication, or Server Authentication in the Authentication Mechanism combo box.
Enter a valid SSAS server username and password, if you choose Server Authentication Mechanism.
Select the database you want to query from the listed databases associated with the given SSAS Server in the Database combo box.

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

Connect using custom attribute and dashboard parameter

We have added support for custom attributes and dashboard parameters in the data source connection. You can now connect to the data source using custom attributes or dashboard parameters.

Custom Attribute

Custom

Dashboard Parameter

Dashboard Parameter

Note: Refer to the Dashboard Parameter Documentation and Custom Attributes Documentation for more details.

Cube customization

IMPORTANT: The code view and join operation are not supported, and the buttons are always in a disabled state. Additionally, data preview is also not supported for the dragged cube.

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

The available list of Cubes is displayed in a treeview for the selected database, which are retrieved from the SSAS server.

Treeview schema

In the data design view page, drag and drop the cube.
Syncfusion only supports conversion from String to Date. You can convert the string to date format and then use this column in filter and widget operations.
Click Save to save the data source with a relevant name.

Connect to expression designer

The Expression Designer is used to create a calculated field or column based on the field(s) or column(s) in the 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

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 display share market data only for the year 2018, then you can utilize this filter.

Query filters

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

Filter DragDrop

The condition can be defined based on two options:

Custom
Parameters
Custom Attributes

Custom

In Custom, The Values will be listed.Select the values and Configure.

Custom SSAS

Parameters

Filter the records based on the dashboard parameter values. This allows for dynamically changing the parameter values in both view and preview modes.

Click on the Parameters option as shown below.

Parameters

Select the desired parameter lists from the dropdown menu. The first parameter will be selected by default.

Parameters

To create parameters, follow the instructions provided in configuring dashboard parameters

Custom Attributes

Similar to the Parameters, we can configure the filters with Custom Attributes

To create Custom Attributes, follow the instructions provided in configuring custom attribute

Connecting through MDX query

You can connect to the data source of specific data connections through a custom MDX Query. By default, the data design view opens in 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 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 the bottom of the Query Editor by clicking Update.

Preview data

Features Limitations:

When using the Microsoft SQL Server Analysis Services data source, not all the features provided in Bold BI work the same as with relational data sources, or they may not even be 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

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

Subselects have the following Limitations:
• 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 on the this documentation.

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 the authentication mechanism. You need to provide "true" for Windows Authentication and "false" for Basic Authentication. default value is false
Username optional	`string` A valid username is required if Integrated Security is not provided or is set to false.
Password optional	`string` A valid Password is required if Integrated Security is not provided or is set to 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: To edit a Data Source via API, all parameters are optional. Only provide the parameter that needs to be changed.

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)"
}]
}