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>