How to use MDX query result in dashboard through Data Integration Platform (DIP)?

Syncfusion Dashboard Designer does not support executing the Multi-Dimensional Expressions (MDX) query against a Microsoft SQL Server Analysis Services (SSAS) cube. This functionality can be achieved in the Syncfusion Dashboard platform via Syncfusion Data Integration Platform (DIP server).

The Syncfusion DIP server provides an intuitive visual interface for integrating and transforming data from various sources into analytics-ready data. The transformed data can be easily used in the Syncfusion Dashboard Platform to design the dashboard.

To use the resultant data of the MDX query in dashboard, follow the given steps:

1)Move the resultant data of corresponding MDX query from an OLAP Cube (SSAS) to target SQL server using the custom processor through the Syncfusion Data Integration Platform.

2)Use the published data source via DIP server in the Dashboard Designer.

Moving the resultant data of corresponding MDX query from an OLAP Cube (SSAS) to target SQL server using the custom processor

The following are the list of processors required to setup the DIP.

Dataflow overview

Step 1: Drag and drop the GenerateFlowFile processor into the work area. Double-click this processor and choose the SETTINGS tab in the Configure Processor dialog, and then rename the processor name in the Name field. Now, choose the SCHEDULING tab and change the Run Schedule field as per your need. For example: 1 day, 1 hr, 5 mins, and 30 secs.

In properties, add new properties using the Add icon.

The above screenshot shows the newly added properties to the ExecuteStreamCommand processor. The properties and their descriptions are:

• AdventureWorks:  OLAP Cube name.
• ColumnsCount:  Number of columns to be returned.
• dataSource:  OLAP Cube data source IP.
• Query: MDX query (Here, users need to give the MDX query).

Example Query: SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount] } ON COLUMNS, NON EMPTY { ([Date].[Month of Year].[Month of Year] * [Geography].[Country].[Country] ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING

Step 2: Drag the ExecuteStreamCommand processor to work area and connect the GenerateFlowFile processor to ExecuteStreamCommand processor with Success relationship. Double-click this processor and choose PROPERTIES tab in the Configure Processor dialog. In properties tab, enter the value for command arguments, command path, and ignore STDIN properties as follows.

In Command Arguments, enter the value as ${Query};${dataSource};${AdventureWorks};${ColumnCount}

In Command Path, enter the value as C:\Syncfusion\DataIntegration\3.1.0.80\SDK\NIFI\OLAPSample.exe (download here)

Step 3: Drag the SplitText processor to work area and connect the ExecuteStreamCount processor to SplitText processor with Output Stream relationship. Double-click this processor and choose PROPERTIES tab in the Configure Processor dialog. Enter 1 as a value for the Line Split Count property as shown in the following screenshot.

It splits each incoming FlowFile line into separate FlowFiles.

Step 4: Drag the ExtractText processor to work area and connect the SplitText processor to ExtractText processor with Splits relationship. Double-click this processor and choose PROPERTIES tab in the Configure Processor dialog. Add a new property as olapData using the Add icon as follows.

The newly added property will get the FlowFile data and update the value to the olapData attribute.

Step 5: Drag the ReplaceText processor to the work area and connect the ExtractText processor to ReplaceText processor with Matched relationship. Double-click this processor and choose PROPERTIES tab in the Configure Processor dialog, and then enter the SQL query in the Replacement Value property.

Refer to the query used in the above screenshot.

insert into sample(MonthofYear, Country, InternetSalesAmount, InternetTaxAmount) values('${olapData:getDelimitedField(1,',')}','${olapData:getDelimitedField(3,',')}',${olapData:getDelimitedField(5,',')},${olapData:getDelimitedField(6,',')})

Step 6: Drag the PutSQL processor to work area and connect the UpdateAttribute processor to PutSQL processor with Success relationship. Double-click this processor and choose PROPERTIES tab in the Configure Processor dialog, and then enter the values for JDBC Connection pool property as follows.

Step 7: Drag the PublishDataSource processor to work area and connect the PutSQL processor to PublishDataSource processor with Success relationship. Choose PROPERTIES tab in the Configure Processor dialog, and then enter the values for required properties as follows.

Step 8: Now, start the workflow. The OLAP data will be moved into the target SQL server.

Using the published data source via DIP server in the Dashboard Designer

After publishing the data source using the DIP server, you can use that data source in the Syncfusion Dashboard Designer using the Server Explorer option.

Follow the given steps to connect the data source from target SQL Server:

1. Expand the Server Explorer tab in left corner of the Dashboard Designer.

2. Right-click the DIP Servers and select Add Server option to open the DIP Server login window.

3. Provide DIP Server details in the window and click Get Data.

4. Expand the published workflow and click Create Data Source option by right-clicking the target server.

The data source is added in the data design view as follows.

Now, user can continue to design their dashboard with the available data source.

Reference:

Refer to the following files from the given downloadable link.

1. OLAPSample.exe
2. Data Integration Platform Workflow: OLAP_cube_sample.xml
3.  Video Illustration

http://www.syncfusion.com/downloads/support/directtrac/general/ze/SSAS_query_view1634487253