How to compare the result-sets returned by an SQL query and an MDX query with NBi

If you’re designing an SSAS cube and you’re putting into action some test automation, you’ll probably want to compare the results of two queries. The first one on a source system or on your sql data warehouse and the second one on your cube.

It’s a common and simple task for NBi. This framework is designed to ensure that this kind of test doesn’t become quickly too complex. The implementation of this test shouldn’t take more than a few seconds. As for other tests designed for this framework, you’ll need to define two objects: a system-under-test (the result-set of the query on the cube) and an assertion (it’s equal to the result-set of the query on the data warehouse). The basic structure of the test will be:

<test name="Compare Cube (MDX) with datawarehouse (SQL)">
<system-under-test>
         <execution>
             <query connectionString="@AsAdventureWorks2012">
                 <!-- Here my MDX Query -->
             </query>
         </execution>
    </system-under-test>
    <assert>
         <equalTo>
             <query connectionString="@SqlAdventureWorks2012">
                 <!-- Here my SQL Query -->
             </query>
         </equalTo>
     </assert>
</test>

The two queries can be directly embedded into the test file or written in an external file. Both options have their own advantages. If you’re placing your query into the test it will be helpful for test edition to have only one file to open. On the opposite, a query written in an external file, could be open directly in SQL Server Management Studio, this file could also be referenced more than once by your test-suite. The choice is not obvious and is depending of what you’ll exactly do with this query, but a general guidance could be to use an embedded query if this query is not too large and is only used once in your test-suite.

To illustrate our sample, we’ll check that the “count of customers” is indeed correctly implemented in the SSAS cube of Adventure Works. For this we’ll use the “Date” dimension as a dicer. The corresponding MDX query will be:

select
    [Measures].[Internet Order Count] on 0,
    non empty([Date].[Calendar Year].children) on 1
from
    [Adventure Works]

MdxResult
Execution of this MDX query will return the preceding result-set: Four rows of two cells. The first cell of a row is the year and the second the count of customers.

In the corresponding SQL data warehouse, we’ll need to write the following query.

select
	'CY ' + cast(year([OrderDate]) as varchar(50)) as CivilYear
	, count([CustomerID]) as CountOfCustomer
from
	[Sales].[SalesOrderHeader]
where
	[OnlineOrderFlag]=1
group by
	'CY ' + cast(year([OrderDate]) as varchar(50))

SqlResult
The corresponding result-set is displayed at the left. Note that for this result-set, there is no guarantee of the ordering of the rows. In this case, 2006 is at the top and 2007 at the end. This is not a problem for NBi, the framework doesn’t take into account the ordering of the rows to compare two result-sets.

If we put together the two queries and the test definition, we’ve the following test:

<test name="">
    <system-under-test>
         <execution>
             <query connectionString="@AsAdventureWorks2012">
                 select
					[Measures].[Internet Order Count] on 0,
					non empty([Date].[Calendar Year].children) on 1
				 from
					[Adventure Works]
             </query>
         </execution>
    </system-under-test>
     <assert>
         <equalTo>
             <query connectionString="@SqlAdventureWorks2012">
                 select
					'CY ' + cast(year([OrderDate]) as varchar(50)) as CivilYear
					, count([CustomerID]) as CountOfCustomer
				 from 
				 	[Sales].[SalesOrderHeader]
				 where
				 	[OnlineOrderFlag]=1
				 group by 
					'CY ' + cast(year([OrderDate]) as varchar(50))
             </query>
         </equalTo>
     </assert>
</test>

Positive ResultThe execution of this test in the test runner will return a positive result (green). As already said before, the fact that the rows are not ordered the same way on the two result-sets is not an issue for NBi. The framework doesn’t rely on rows ordering when executing the comparison but on keys matching. By default, NBi will consider the first cells of the row as part of the key. On our sample, NBi will look for the row “CY 2005” (first row of the MDX query) into the second result-set. When this row is found in the second-result-set, NBi will compare its value (content of last cell of the row / 1013) to the expected value (1013). NBi will continue like this for each row. In this case NBi will take the conclusion that the result-sets are equal and validate the test.

If we slightly change our SQL to a “distinct count” in place of a “count”,

select
	'CY ' + cast(year([OrderDate]) as varchar(50)) as CivilYear
	, count(distinct [CustomerID]) as CountOfCustomer
from
	[Sales].[SalesOrderHeader]
where
	[OnlineOrderFlag]=1
group by
	'CY ' + cast(year([OrderDate]) as varchar(50))

Reload-Testsit will impact the result-set and the test should not be green any more. To ensure, this you can run the test after adaptation of your query in your nbits file. Don’t forget to reload the test (CTRL+R) in NUnit to ensure your latest version of your test-suite is taken into account.
Negative Result
Then execute the test and you’ll see a red bar, synonym of failure.

delta-resultsetsThe exception described just under is “Execution of the query doesn’t match the expected result”. If you copy paste the full message displayed in the textbox, you’ll receive additional valuable information about the difference between the two result-sets. At the top of the message NBi will display the first 10 (max.) rows of the two result-sets (sections highlighted in yellow). Just under, NBi will display the rows missing, unexpected or with delta in the values (highlighted in green). In the sample, two rows are considered as non-matching because their values in the two result-sets diverge.

Now, if we go back to our initial SQL query (so without the distinct) but we introduce arbitrarily an additional where clause, we can test the behavior of NBi when the two result-sets don’t match in term of rows’ count.

select
	'CY ' + cast(year([OrderDate]) as varchar(50)) as CivilYear
	, count([CustomerID]) as CountOfCustomer
from
	[Sales].[SalesOrderHeader]
where
	[OnlineOrderFlag]=1
        and year([OrderDate])<2008
group by
	'CY ' + cast(year([OrderDate]) as varchar(50))

If we just add this line to our query in the nbits file, we’ll have troubles when trying to load the test in NUnit. Indeed, the symbol “<" is ambiguous in an xml file and so we should mark it as a non-xml-symbol. This is quickly done by introducing a CDATA element in the xml. The assertion should look as

<assert>
    <equalTo>
        <query connectionString="@SqlAdventureWorks2012">
            <![CDATA[
            select
                'CY ' + cast(year([OrderDate]) as varchar(50)) as CivilYear
                , count(distinct [CustomerID]) as CountOfCustomer
            from 
                [Sales].[SalesOrderHeader]
            where
                [OnlineOrderFlag]=1
                and year([OrderDate])<2008
            group by 
                'CY ' + cast(year([OrderDate]) as varchar(50))
            ]]>
        </query>
    </equalTo>
</assert>

unexpected-rowsAs expected, if the adapted test is run, NBi will display a red light and will help you to find the difference between the result-sets by identifying the unexpected row in the system-under-test (the section is highlighted in red).

In the next posts we’ll see how to tune NBi to detect missing rows and unexpected rows in more complex cases but also how to introduce a tolerance when comparing values of rows.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s