Share <variable> value between <system-under-test> and <assert>

[phampdx]

When testing ETL process, we need to validate between source and target. Typically, query of system-under-test should return same value as in assert with a given parameter input. If that parameter input is dynamic generate during runtime, how can we ensure both queries using the same value. Following is my test suite code:

<testSuite name="testSum test suite" xmlns="http://NBi/TestSuite">
 <variables>
  <variable name="FiscalWeekNumberVar">
      <query-scalar connectionString="@Assert-Staging">
          <![CDATA[ SELECT TOP 1 FiscalWeekNumber FROM dbo.DimDate WHERE FullDate < GETDATE() ORDER BY NEWID();]]>
       </query-scalar>
   </variable>
  <variable name="RegionNameVar">
      <query-scalar connectionString="@Assert-Staging">
          <![CDATA[ SELECT TOP 1 RegionName FROM dbo.DimRegion ORDER BY NEWID(); ]]>
        </query-scalar>
    </variable>	  
 </variables>   
 <test name="TestId:999. Validate [Sum]" uid="999">
    <system-under-test>
      <resultSet> 
        <query> < ![CDATA[      
		  SELECT SUM(ISNULL(f.Empl,0)), @RegionName AS RegionName, @FiscalWeekNumber AS FiscalWeekNumber      
		  FROM DimDate AS d      INNER JOIN dbo.Fact AS f ON f.DateKey=d.DateKey      
		  INNER JOIN dbo.DimOrg o ON f.OrgKey=o.OrgKey      WHERE d.FiscalWeekNumber = @FiscalWeekNumber       
			AND o.OrgRegionName = @RegionName
		]]>  
          <parameter name="@FiscalWeekNumber">@FiscalWeekNumberVar</parameter>
          <parameter name="@RegionName">@RegionNameVar</parameter>
        </query> 
      </resultSet> 
    </system-under-test>
    <assert>
      <equalTo behavior="single-row">
        <column index="0" role="value" type="numeric" tolerance="0.000001"/>
		<column index="1" role="value" type="text"/>
		<column index="2" role="value" type="numeric"/>
        <query connectionString="@Assert-DW_MART">
          < ![CDATA[       
		  SELECT SUM(Empl), @RegionName AS RegionName, @FiscalWeekNumber AS FiscalWeekNumber       
		  FROM dbo.FactPerformance f 
		  INNER JOIN dbo.DimCostCenter AS cc ON f.CostCenterKey = cc.CostCenterMasterKey      
		  INNER JOIN dbo.DimDate d ON f.FiscalWeekKey = d.DateKey      
		  WHERE d.FiscalWeekNumber = @FiscalWeekNumber      
			AND cc.RegionName = @RegionName
			]]>
          <parameter name="@FiscalWeekNumber">@FiscalWeekNumberVar</parameter>
          <parameter name="@RegionName">@RegionNameVar</parameter>   
        </query>
      </equalTo>
    </assert>
  </test>
</testSuite>

Test result show parameter value of system-under-test and assert are different because the test case pulls value in for each query which designs to return discrete value every time invoke the command.

NBi version v1.19

Any suggestion?

Thanks.

[Seddryck]

This is not the expected behavior of a variable. Once the value of a variable has been evaluated it should not be re-evaluated. I'll treat this has a bug ... should be fixed within 15 days.

From the top of my head, the problem is that the variable is accessed in parallel by the system-under-test and the assertion ... both check if the variable has been evaluated and return false then perform the evaluation (due to non-predictability of your evaluation, will return two different values).

As a work-around, I'd suggest to write a dummy test, that will trigger the evaluation of the variable ... on your real test, the evaluation of the variable will already be performed and you should have the same value for assert and system-under-test.

[phampdx]

Please point me to a sample of the suggestion provided. I think the work around may solve the issue.
As a work-around, I'd suggest to write a dummy test, that will trigger the evaluation of the variable ... on your real test, the evaluation of the variable will already be performed and you should have the same value for assert and system-under-test.

Thank you very much for your support,

Khanh

[Seddryck]

The fix is available at https://ci.appveyor.com/project/Seddryck/nbi/builds/24542854 or in nuget with version 1.21.0-beta0033
Please keep me posted.

[phampdx]

What I've done so far to work around since last post that I create a control that was populated with input parameter values. This way we can control in put value rather than random in puts. One set back with this approach is only a discrete input at a time. I work around this limitation is via that update control table for new values.

THANKS!