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 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:

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 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 in the listed database associated with the given SSAS Server in Database combo box.

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.

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

In the data design view page, drag and drop the cube.
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.
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

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