OLAP

We can compare mdx queries using Microsoft SQL Server jdbc drivers.

Linked Server for Analysis Services must be set first on tested server.

Please download additional dll sqljdbc_auth.dll file and put in windows/system32 folder to use WIN-AUTH (integratedSecurity=true) or in folder at the same level where DBTestCompare-1.0-SNAPSHOT-jar-with-dependencies.jar is stored.

Datasource definition for connection to OLAP:

<datasource maxPollSize="6">
    <name>SQL_SERVER_WIN_AUTH</name>
    <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
    <!-- to use WIN-AUTH (integratedSecurity=true) you must put sqljdbc_auth.dll file in windows/system32
    folder. Download it from here: https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774 -->
    <url>jdbc:sqlserver://${SQL_SERVER};databaseName=master;integratedSecurity=true</url>
</datasource>

It can be any existing database on server e.g. databaseName=master, name of OLAP set in linked server is set in OPENQUERY e.g. CUBE.

Please remember that SQL server row size limit is 8060 bytes (eg. an 8K page) while using OPENQUERY.

SELECT *
            FROM OPENQUERY(CUBE,...

You can use fetch comparator to compare mdx queries, you can also compare mdx query with SQL using proper datasources.

<cmpSqlResultsTest>
    <compare mode="FETCH" fileOutputOn="true">
        <sql datasourceName="SQL_SERVER_WIN_AUTH">
            <![CDATA[
            SELECT *
            FROM OPENQUERY(CUBE, 'select {[Date].[CY 2005], [Date].[CY 2006] , [Date].[CY 2007]} on rows,
[Measures].[Internet Sales Amount] on columns from
[Adventure Works]');
            ]]>
        </sql>
        <sql datasourceName="SQL_SERVER_WIN_AUTH">
            <![CDATA[
            SELECT *
            FROM OPENQUERY(CUBE, 'select {[Date].[CY 2005], [Date].[CY 2006] , [Date].[CY 2007]} on rows,
[Measures].[Internet Sales Amount] on columns from
[Adventure Works]');
        ]]>
        </sql>
    </compare>
</cmpSqlResultsTest>