Query's template-variables

You can use template-variables to dynamically build the statement of your SQL, MDX or DAX queries. Unlike the parameters, the template-variable inputs are treated as portions of executable code.

When possible, we highly recommend to use the parameters and not the template-variables. The template-variables are more flexible to build dynamically your queries but are obfuscating your tests.

To create template-variables in a query, you identify them by putting some dollar signs ($) before and after the template-variable’s name. For example, $Year$ would be a valid template-variables name.

In your test definition, in addition of the element query, you’ll also need to describe your template-variable by defining its name and value within an element named variable.

<query>
	select * from Customer where CustomerKey $OperatorVar$ (1,2,3)
	<variable name="OperatorVar">
		not in
	</variable>
</query>

The query executed by NBi will be

select * from Customer where CustomerKey not in (1,2,3)

Template-variables defined at the test-suite level

Sometimes, a few template-variables are used in more than one query and their values are constant through the test-suite. In this case, you can save time and define them at the test-suite level. This can be achieved by the usage of the element variable in the element default of the element settings.

Within the code snippet here under we’re defining a template-variable named Clause. This Clause applies a slicer to limit the cube to the years 2010 to 2013.

<settings>
	<default apply-to="system-under-test">
		<variable name="Clause">
			([DimTime].[Year].[2010]:[DimTime].[Year].[2013])
		</variable>
	</default>
</settings>

This template-variable can be used in the following two queries without reapeating the definition of this variable in each test:

<test>
	...
	<query>
		select
			$Clause$ on 0,
			[Measures].[My Value] on 1
		from
			[my cube]
	</query>
	...
</test>
<test>
	...
	<query>
		select
			[Measures].[My Value] on 0
		from
			[my cube]
		where
			$Clause$
	</query>
	...
</test>

If a template-variable is defined at the test-suite level and at the query level for a test, the definition at the query level will be used for this test.

If a template-variable is not used within a query but is provided to this query, this template-variable is simply not used by NBi (so it’s not a problem).

Back

Next