Regression test-suite for BI solution – Part 2

In the previous blog post of this series, I’ve explained how to query a DMV and how to modify a little bit the set of test-cases generated by the query. In this post, we’ll see how to join two sets of test-cases to obtain a unique set.

Now that you’ve loaded the content of the a first DMV, we can apply the same recipe for the second DMV. Well, It’s not so easy, if we just do this, the second call to case load will override the result of the first query. To keep in memory two sets of test-cases, in genbiL, you’ll have to give them a name. This action is done through the keyword scope (more info). This action must be executed when you want to work on a different set of test-cases.

case scope 'dimensions';
case load query
{
 ...
}
on '...';

case scope 'hierarchies';
case load query
{
 ...
}
on '...';

To specify on which scope you want to apply actions (filter, load, hold, …), you first need to specify the action scope with the name of the scope on which you want to apply actions. It’s possible to switch the scope at any moment and you can o back to a scope previously loaded.

To join the two sets, you’ll need to use the command cross (more info) with a jointure. But, before crossing the two existing datasets, you need to scope on a third empty scope that will contain the result of the cross.

case scope 'dimensions and hierarchies';
case cross 'dimensions' with 'hierarchies' on 'dimension_unique_name';

Now that you’ve understood how to cross (join) two datasets, you can safely load some other DMVs and combine them to build a global picture of your cube. First step, load measures and measuregroups then combine them. Second step, use the DMV returning the intersections between dimensions and measuregroups. If you combine this to the two already combined datasets, you’ll have the interesting interactions between hierarchies and measures. Probably the best starting point to automatically generate queries to apply on your new cube and old cube and compare result-sets. Let’s do this:

Create a new a new template to support comparison query-to-query (save it as regression\my-template.nbitt):

<test name="Compare '$Measure_Caption$' by '$hierarchy_caption$'">
  <description>Assert that the same query, 
  executed on two instances of the cube, 
  returns the same result-set. Specifically, 
  for measure '$measure_caption$' on hierarchy 
  '$hierarchy_caption$'</description>
  <edition author="$username$" created="$now$"/>
  <category>Measuregroup '$measuregroup_name$'</category>
  <trait name="Template">my-template.nbitt</trait>
  <system-under-test>
    <execution>
      <query>
        <![CDATA[
        select 
          $measure_unique_name$ on 0
          , non empty($hierarchy_unique_name$.children) on 1 
        from 
          [$cube_name$] 				
        ]]>
      </query>
    </execution>
  </system-under-test>
  <assert>
    <equalTo keys="all-except-last" tolerance="$tolerance$">
      <query>
        <![CDATA[
        select 
          $measure_unique_name$ on 0
          , non empty($hierarchy_unique_name$.children) on 1 
        from 
          [$cube_name$]
        ]]>
      </query>
    </equalTo>
  </assert>
</test>

Add the following genbiL code to load all the DMV

case scope 'dimensions';
case load query
{
	select
		[Dimension_Unique_Name]
		, [Dimension_Caption]
	from
		[$system].[MDSchema_dimensions]
	where
		[Cube_Name]='your-cube-name'
		and [Catalog_Name]='your-initial-catalog'
		and Dimension_Is_Visible
}
on 'Provider=MSOLAP.4;Data Source=your-data-source;Initial Catalog=your-initial-catalog';

case scope 'hierarchies';
case load query
{
	select
		[Dimension_Unique_Name]
		, [Hierarchy_Unique_Name]
		, [Hierarchy_Caption]
	from
		[$system].[MDSchema_hierarchies]
	where
		[Cube_Name]='your-cube-name'
		and [Catalog_Name]='your-initial-catalog'
		and Hierarchy_Is_Visible
}
on 'Provider=MSOLAP.4;Data Source=your-data-source;Initial Catalog=your-initial-catalog';

case scope 'levels';
case load query
{
	select
		[Hierarchy_Unique_Name]
		, [Level_Unique_Name]
		, [Level_Caption]
	from
		[$system].[MDSchema_levels]
	where
		[Cube_Name]='your-cube-name'
		and [Catalog_Name]='your-initial-catalog'
		and Level_Is_Visible
		and [Level_Number]<>'0'
}
on 'Provider=MSOLAP.4;Data Source=your-data-source;Initial Catalog=your-initial-catalog';

case scope 'measureGroups';
case load query
{
	select
		[MeasureGroup_Name]
	from
		[$system].[MDSchema_measureGroups]
	where
		[Cube_Name]='your-cube-name'
		and [Catalog_Name]='your-initial-catalog'
}
on 'Provider=MSOLAP.4;Data Source=your-data-source;Initial Catalog=your-initial-catalog';
case filter distinct;

case scope 'measures';
case load query
{
	select
		[Cube_Name]
		, [Measure_Unique_Name]
		, [Measure_Caption]
		, [MeasureGroup_Name]
		, [Measure_Display_Folder]
	from
		[$system].[MDSchema_measures]
	where
		[Cube_Name]='your-cube-name'
		and [Catalog_Name]='your-initial-catalog'
		and Measure_Is_Visible
}
on 'Provider=MSOLAP.4;Data Source=your-data-source;Initial Catalog=your-initial-catalog';

case scope 'links';
case load query
{
	select
		[MeasureGroup_Name]
		, [Dimension_Unique_Name]
	from
		[$system].[MDSCHEMA_MeasureGroup_Dimensions]
	where
		[Cube_Name]='your-cube-name'
		and [Catalog_Name]='your-initial-catalog'
		and Dimension_Is_Visible
}
on 'Provider=MSOLAP.4;Data Source=your-data-source;Initial Catalog=your-initial-catalog';

Once we’ve the separated DMV, we can cross them together to obtain our model

case scope 'dimensions-measures';
case cross 'measures' with 'links' on 'MeasureGroup_Name';
case cross 'dimensions-measures' with 'dimensions' on 'Dimension_Unique_Name';
case cross 'dimensions-measures' with 'hierarchies' on 'Dimension_Unique_Name';

Add a field tolerance of 0.001

case add column 'tolerance' values '0.001';

Then, generate the tests based on the test-cases loaded in the newly created scope and the template build above.

template load file 'Regression\my-template.nbitt';
suite generate;

And that’s it.

Now up-to-you to build other queries and filter out the not pertinent test-cases.

Advertisements

One comment

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