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

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

    Query designer

  3. 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

  4. 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 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

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

Data Transformation

Editing a Data Connection

Dashboard Designer Walkthrough

Microsoft SQL Server Analysis Services Integration

Previous
Next