Build SQL Analysis Services-Connected Web Apps in Servoy

Servoy is a rapid application development and deployment platform. When paired with the CData JDBC Driver for SQL Analysis Services, users can build SQL Analysis Services-connected apps that work with live SQL Analysis Services data. This article describes how to connect to SQL Analysis Services from Servoy and build a simple web app to display and search SQL Analysis Services data.

With built-in optimized data processing, the CData JDBC Driver offers unmatched performance for interacting with live SQL Analysis Services data. When you issue complex SQL queries to SQL Analysis Services, the driver pushes supported SQL operations, like filters and aggregations, directly to SQL Analysis Services and utilizes the embedded SQL engine to process unsupported operations client-side (often SQL functions and JOIN operations). Its built-in dynamic metadata querying lets you work with SQL Analysis Services data using native data types.

Connect to SQL Analysis Services in Servoy Developer

To build SQL Analysis Services-connected apps, you need to first create a data provider in Servoy Developer using the CData JDBC Driver for SQL Analysis Services.

1. Install the JDBC Driver.
2. Copy the JDBC Driver JAR file. (cdata.jdbc.ssas.jar) to the /application_server/drivers/ directory in the installation directory for Servoy.
3. Open Servoy Developer.
4. In the Solution Explorer, right-click Database Server (under Resources) and choose "Connect to existing database" -> "empty."
   1. Name the server.
   2. Click to show the advanced server settings.

      • Set the URL, for example: jdbc:ssas:User=myuseraccount;Password=mypassword;URL=http://localhost/OLAP/msmdpump.dll;

        Built-In Connection String Designer

        For assistance in constructing the JDBC URL, use the connection string designer built into the SQL Analysis Services JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

        java -jar cdata.jdbc.ssas.jar

        Fill in the connection properties and copy the connection string to the clipboard.

        To connect, provide authentication and set the Url property to a valid SQL Server Analysis Services endpoint. You can connect to SQL Server Analysis Services instances hosted over HTTP with XMLA access. See the Microsoft documentation to configure HTTP access to SQL Server Analysis Services.

        To secure connections and authenticate, set the corresponding connection properties, below. The data provider supports the major authentication schemes, including HTTP and Windows, as well as SSL/TLS.

        • HTTP Authentication

          Set AuthScheme to "Basic" or "Digest" and set User and Password. Specify other authentication values in CustomHeaders.

        • Windows (NTLM)

          Set the Windows User and Password and set AuthScheme to "NTLM".

        • Kerberos and Kerberos Delegation

          To authenticate with Kerberos, set AuthScheme to NEGOTIATE. To use Kerberos delegation, set AuthScheme to KERBEROSDELEGATION. If needed, provide the User, Password, and KerberosSPN. By default, the data provider attempts to communicate with the SPN at the specified Url.

        • SSL/TLS:

          By default, the data provider attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats.

        You can then access any cube as a relational table: When you connect the data provider retrieves SSAS metadata and dynamically updates the table schemas. Instead of retrieving metadata every connection, you can set the CacheLocation property to automatically cache to a simple file-based store.

        See the Getting Started section of the CData documentation, under Retrieving Analysis Services Data, to execute SQL-92 queries to the cubes.

      • Select the Driver class you just copied, for example, cdata.jdbc.ssas.SSASDriver

Build a SQL Analysis Services-Connected Web App

Once you have configured the connection to SQL Analysis Services in the Servoy Developer resources, you are ready to build apps with access to live SQL Analysis Services data.

Create a New Solution

1. In the Server Explorer, right-click "All solutions" and select "Create new solution."
2. Name the solution.
3. Select the checkbox to include the "search" module.
4. Click "Finish."

Create a New Form

Right-click "Forms" and select "Create new form."

1. Name the form.
2. Select a Datasource.
3. Set the type (e.g., Simple) and click "Finish."

Add a Data Grid to the Form

1. Drag a Data Grid component (from Servoy NG-Grids) onto the form.

2. Drag a column component onto the Data Grid and set the "dataprovider" property for each column component to a column from the SQL Analysis Services "table" (e.g., Fiscal_Year from the Adventure_Works table).

   Continue adding columns as desired.

Add Searching to the App

Note that the "svySearch" extension is required to add search functionality (included by default when you create a new solution). If you did not add the extension when you created the solution or you are modifying an existing solution, you can add the search module by right-clicking Modules (in the solution) and selecting "Add Module." Select "svySearch" and click "OK."

1. Drag a Text Field component onto the Form.
2. Right-click the Form and select "Open in Script Editor."
3. Create a new variable (JavaScript) to hold the search value:

   var searchText = '';
   

4. Back on the Form, in the Text Field properties:
   1. Set the "dataprovider" property to the Form variable you just created.
   2. Double-click to add a method for the onAction event.
   3. Click to create the method in "Form," name the method (e.g., onEnter), and click "Create private."
   4. Click "OK & Show."
5. Add the following JavaScript to the JavaScript file to use the Servoy framework to implement searching bound data based on the text in the Text Field:

   var search = scopes.svySearch.createSimpleSearch(foundset).setSearchText(searchText);
   search.setSearchAllColumns();
   search.loadRecords(foundset);
   

Save and Launch the App

Save the form and JavaScript file, then click Run -> Launch NGClient to start the web app.

Download a free, 30-day trial of the CData JDBC Driver for SQL Analysis Services and start building SQL Analysis Services-connected apps with Servoy. Reach out to our Support Team if you have any questions.