Configuring Connections: Microsoft SQL Server Analysis Services (SSAS)

Versions: 8.0+

When you create a QuerySurge Connection, the Add Connection Wizard will guide you through the process. Different types of QuerySurge connections require different types of information.

For an SSAS Connection, you will need the following information (check with a DBA or other knowledgeable resource in your organization):

• Database login credentials (ID and Password)
• The XML/A Web Service URL for your SSAS service
• The SSAS Catalog Name
• The SSAS Cube Name
• The Max String Width for your data (the largest size that string data types can have in your data set).

Launch the Connection Wizard

Log into QuerySurge as an Admin user.

1. Log into QuerySurge as an Admin user.
2. To configure a Connection, select Configuration > Connection in the Administrative View tree (at the left).
3. Click on the Add button at the bottom left of the page to launch the Connection Wizard. Click Next.
   
   
    
4. Provide a name for your connection. Select SSAS (Olap4j) as the Data Source. Click Next.
   
   Note: The open source Olap4j JDBC driver is bundled with QuerySurge, so you can install this driver from the QuerySurge Installer. See www.olap4j.org for information.
   
   
   
   
5. Once you have selected your Data Source, the Wizard will tell you what information you are likely to need in order to create your Connection. Once you have collected this information, you are ready to click Next.
   
   
   
   
6. Provide the connection information to your database. This includes the URL for the XML/A Web Service, the Catalog name, the Cube name and credentials if required. Click Next.
   
   Required fields for your Connection are marked by an *.
   
   Note: Your SSAS service must be exposed via an XML/A Web Service. See for example: http://msdn.microsoft.com/en-us/library/gg492140.aspx (SQL Server 2014)
   
   
   
   
7. Check the Max String Width. This value is dependent on your data, and is the largest size that string data types can have in your data set. The default is 500 B. Modify the value if necessary.
   
   
   
   
8. Enter an optional MDX Test Query for the test connection. A test query should bring back a row or two rows reliably, and is used simply to show connectivity between the Agent and SSAS. A simple query that should work in most circumstances is:
   

   WITH MEMBER Test AS 1 SELECT Test ON COLUMNS FROM [Your-Cube-Name]

9. Click on Test Connection.
   
   Note: You must have an Agent running with the driver for this Connection deployed in order to test the Connection.
   
   
   
   
10. Save the Connection.
    
    
    
    
11. Congratulations! You’ve created a QuerySurge Connection. Make sure that you have deployed the driver for this Connection to all your QuerySurge Agents. (See Adding a JDBC Driver to your QuerySurge Agent for instructions.)
    
    Note: On Windows, your SSAS Connection may be sensitive to the login that the Agent runs under. By default, the Agent is installed as a service that runs under the "Local System" user. This user may not have the necessary rights to connect with SSAS. You can set up your Agent(s) to run under a different user by following the instructions in this article.

Important Usage Note: While MDX queries can be elaborate and may return elaborately structured resultsets, QuerySurge is designed to work in a "tabular" world. So, QuerySurge expects all queries including MDX queries to return 2D resultsets that conceptually look like regular "tables". If you are dealing with data that is more complex, then the solution is usually to write multiple QueryPairs to cover your test requirements.

Note: You can also connect to SSAS without an XML/A Web Service running via a Linked Server. Details can be found in this article.