Using NBi with hierarchy’s level in MDX queries

NBi is a toolset dedicated to test BI and data-centric applications. One of the key feature is to compare the results returned by two queries. This can be used to compare the result of a query on the relational datawarehouse and on the OLAP cube. A pitfall that you could face when using NBi with MDX queries is the count of columns returned by this kind of query when a hierarchy’s level is defined on an axis.

When executing the following query (on the Adventure Works 2012 sample database/cube), you’ll see two columns in the result displayed by SSMS. It’s probably what you’re expecting, you’re only selecting one specific level of the hierarchy [Date].[Calendar Date] and one measure.

You’re probably expecting that NBi will also consider two columns. Unfortunately, it’s not the case: NBi will consider 4 columns! What are the additional and unexpected columns? The [Date].[Calendar].[Calendar Year] and [Date].[Calendar].[Calendar Semester] are also returned. In reality, this is not something specific to NBi, it’s just the standard behaviour of the ADOMD library and SSMS is cheating when only displaying one column for the level!

If we don’t care about this specificity of NBi and setup a test where the MDX query is the system-under-test, then we’ll have to face the following exception

NBi.NUnit.Runtime.CustomStackTraceErrorException :
The column with index '[Date].[Calendar].[Calendar Semester].[MEMBER_CAPTION]'
 is expecting a numeric value but the first row of your result set contains a value 'H1 CY 2008'
 not recognized as a valid numeric value or a valid interval.

When comparing the second column of your assertion (the orders’ count so the value, so by default a numeric value) to the second column of your system-under-test (what you’re expecting to be the orders’ count but is in reality the [Date].[Calendar].[Calendar Semester]) … NBi is not able to convert “H1 CY 2008” to a numeric value.

To overcome this issue, the easiest is to fake in the SQL query the additional columns returned by the MDX query. In place of having the following SQL query on your assertion:

select
	, 'Q' + cast([CalendarQuarter] as char(1)) + ' CY 2008' as QY
	, count(distinct SalesOrderNumber)
from
	dbo.factInternetSales f
inner join
	Dimdate d
	on d.DateKey = f.OrderDateKey
where
	f.OrderDateKey between 20080000 and 20089999
group by
	[CalendarQuarter]

Just fake the two additional columns filling them by NULL.

select
	null as [Year]
	, null as [HY]
	, 'Q' + cast([CalendarQuarter] as char(1)) + ' CY 2008' as QY
	, count(distinct SalesOrderNumber)
from
	...

But it’s not the end! You’ll have to inform NBi that it shouldn’t care about these two additional columns. It can be done in the equalTo element where you can specify to ignore some columns. Ignoring means that these columns are not keys and neither values … they don’t interfere in the comparison process. To successfully implement that you’ll define the role as ignore for these columns.

<equalTo keys="all-except-last">
   <column index="0" role="ignore"/>
   <column index="1" role="ignore"/>
   <query>
      select
        null as [Year]
        , null as [HY]
        , 'Q' + cast([CalendarQuarter] as char(1)) + ' CY 2008' as QY
        , count(distinct SalesOrderNumber)
      from
        ...

Now that you expectly said to NBi that he should expect four columns and don’t care about the two firsts, you’ll be able to run your test and find real issues or success!

NBi.NUnit.Runtime.TestSuite.Order's count by Quarter Year for CY 2008:
NBi.NUnit.Runtime.CustomStackTraceAssertionException : Execution of the query doesn't match the expected result 

  Expected: 
Result-set with 3 rows

     #0 (Column1) | #1 (Column2) | #2 (Column3) | #3 (Column4)   
     (Text)       | (Text)       | KEY (Text)   | VALUE (Numeric)
     ------------ | ------------ | ------------ | --------------- 
     (null)       | (null)       | Q1 CY 2008   | 6080           
     (null)       | (null)       | Q2 CY 2008   | 6886           
     (null)       | (null)       | Q3 CY 2008   | 976            


  But was:  
Result-set with 3 rows

     #0 ([Date].[Calendar].[Calendar Year].[MEMBER_CAPTION]) | #1 ([Date].[Calendar].[Calendar Semester].[MEMBER_CAPTION]) | #2 ([Date].[Calendar].[Calendar Quarter].[MEMBER_CAPTION]) | #3 ([Measures].[Order Count])
     (Text)                                                  | (Text)                                                      | KEY (Text)                                                 | VALUE (Numeric)              
     ------------------------------------------------------- | ----------------------------------------------------------- | ---------------------------------------------------------- | ----------------------------- 
     CY 2008                                                 | H1 CY 2008                                                  | Q1 CY 2008                                                 | 6082                         
     CY 2008                                                 | H1 CY 2008                                                  | Q2 CY 2008                                                 | 6886                         
     CY 2008                                                 | H2 CY 2008                                                  | Q3 CY 2008                                                 | 976                          





Non matching value rows:
------------------------

Result-set with 1 row

     #0 ([Date].[Calendar].[Calendar Year].[MEMBER_CAPTION]) | #1 ([Date].[Calendar].[Calendar Semester].[MEMBER_CAPTION]) | #2 ([Date].[Calendar].[Calendar Quarter].[MEMBER_CAPTION]) | #3 ([Measures].[Order Count])
     (Text)                                                  | (Text)                                                      | KEY (Text)                                                 | VALUE (Numeric)              
     ------------------------------------------------------- | ----------------------------------------------------------- | ---------------------------------------------------------- | ----------------------------- 
     CY 2008                                                 | H1 CY 2008                                                  | Q1 CY 2008                                                 | 6082 <> 6080