Bold BI supports connecting Microsoft SQL Server Analysis Services (SSAS) database using the SQL MDX Query (C# API).
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.
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.
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.
The available Cubes list is shown in treeview for the selected database that are retrieved from the SSAS server.
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.
Expression designer is used to create a calculated field or column based on the field(s) or column(s) in selected cube(s).
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 |
Category | Functions | Syntax, Descriptions & Examples | Numbers | ABS |
|
---|---|---|
Numbers | ACOS |
|
Numbers | ASIN |
|
Numbers | ATAN |
|
Numbers | COS |
|
Numbers | DEGREES |
|
Numbers | EXP |
|
Numbers | LOG |
|
Numbers | PI |
|
Numbers | POWER |
|
Numbers | RADIANS |
|
Numbers | ROUND |
|
Numbers | SIGN |
|
Numbers | SIN |
|
Numbers | SQRT |
|
Numbers | TAN |
|
Aggregation | AVG |
|
Aggregation | COUNT |
|
Aggregation | COUNTD |
|
Aggregation | MAX |
|
Aggregation | MIN |
|
Aggregation | STDEV |
|
Aggregation | SUM |
|
Aggregation | VAR |
|
Conditional | IF |
|
Conditional | IFNULL |
|
Conditional | ISNOTNULL |
|
Conditional | ISNULL |
|
Logical | AND |
|
Logical | NOT |
|
Logical | OR |
|
Date | DATEADD |
|
Date | DATESUB |
|
Date | NOW |
|
Date | TODAY |
|
String | LEN |
|
String | CHAR |
|
String | LEFT |
|
String | LTRIM |
|
String | RIGHT |
|
String | RTRIM |
|
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 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.
You can configure filter by dragging the required columns to right side canvas.
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.
This will open the query editor window as follows.
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.
You can also view the data for your query at bottom of the Query Editor by clicking Update
.
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.
Type while creating the data source needs to be ssas.
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 | Details |
---|---|
Name required | `string` Name of the Expression |
Expression required | `string`
Expression
|
NOTE: For editing Data Source via API. All the parameters are optional. The parameter which needs to be changed can be provided.
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": [
{
"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)"
}]
}
]
"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": [
{
"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)"
}]
}
]