Possibility to compare type text as case insensitive?

[fefeslon]

Hi All,

Is it possible to compare the text columns as case insensitive somehow in NBi?

As per the documentation I have found that the text is case sensitive by default.

It is possible to use tag to transform the expected result set, but I was unable to find any setting for the second side (tested result set) so this is a no go option.

For example if I compare two cubes and the key column is text, is it possible to ignore case sensitivity on both sides?

I do not want to edit the input MDX queries and use the upper/lower case there (by creating the calculated MEMBERs) because in some cases this complicates the automated test creation using nbitt files and dimensions as variables files.

Could you give me the guide (if it is possible) please?

In case it is not possible could it be implemented (I mean just something like "tolerance" option to turn off/on the case sensitivity in the text)?

Cheers.

[Seddryck]

Ignore-case is supported in some places:

• When comparing two members
• When checking the structure (model)
• When using a predicate (for text columns) in filters or none/all rows.

But is not supported when comparing the equivalence of two result-sets (Neither in the key comparison or the value comparison).

For the values, it sounds easy to implement a new TextTolerance (currently supporting fuzzy string equality) named "ignore-case" and use it in the TextComparer. For the keys, it's more complex. You'd need to add a new attribute on the equal-to and then change the classes DataRowKeysComparer. Sounds a bit harder to do.

I'll consider to add this but will surely not be a priority. Feel free to propose a PR.

[Seddryck]

Changed a little bit my mind about priority around columns that are "values" … To specify that two textual values should be compared ignoring the case, the following tolerance should be set:

<equalTo>
    <column index="1" role="value" type="text" tolerance="ignore-case"/>
    ...
</equalTo>

this feature is available in build https://ci.appveyor.com/project/Seddryck/nbi/builds/19805551 or nuget 1.19-beta0090

[Seddryck]

Huuum, in your text you say that it's not possible to alter a result-set from the system-under-test. In fact it's possible within a result-set to define an alteration more info … the documentation is not covering it but these alterations also supports column's transformation.

I opened a new issue (#417) to support some ignore-case features for keys … but I'm not convinced by this one.

[fefeslon]

Hey Cédric,

Thanks for the comments.

I am able to a make a workaround using the calculated member for the dimension and then making it a key column in the nbi tests so it is just a nice to have feature for me.

In case it takes a lot of time to get the tolerance into the key column I have probably no PR power to make this a priority :D (I have hoped that it should have been some easy thing to implement).

Thanks for info and the beta feature anyway.

Cheers.

[Seddryck]

@fefeslon

It's possible to do this without changing your query. Uppercasing the system-under-test and the assertion is possible:

<test name="Transformations" uid="0231">
      <system-under-test>
        <resultSet>
          <query>
            select 'a', 'FOO' union all select 'B', 'bar'
          </query>
          <alteration>
            <transform column-index="0" language="native" original-type="text">
              text-to-upper
            </transform>
          </alteration>
        </resultSet>
      </system-under-test>
      <assert>
        <equalTo keys="first">
          <column index="0" type="text" role="key">
            <transform language="native" original-type="text">text-to-upper</transform>
          </column>
          <column index="1" type="text" role="value" tolerance="ignore-case"/>
          <resultSet>
            <row>
              <cell>A</cell>
              <cell>foo</cell>
            </row>
            <row>
              <cell>b</cell>
              <cell>BaR</cell>
            </row>
          </resultSet>
        </equalTo>
      </assert>
    </test>

[fefeslon]

Cool, thanks!

[fefeslon]

Hey @Seddryck ,

The beta build mentioned above contains a bug (or a new feature?).

After updating the NBi and Genbi to that build it seems that the column name logic has changed.

Example of settings in the test used by me:

    <assert>
      <equalTo>
        <column name="[Measures].[dim]" role="key" type="text" />
        <column name="[Measures].[Vat]" role="value" type="numeric" />

This has worked for me since the beta build.

Now I am getting the following error:
NBi.NUnit.Runtime.CustomStackTraceErrorException :
You've defined some columns named
'Measures].[dim'
, 'Measures].[Vat'
as keys or values but there is no column with these names in the resultset.
When using comparison by columns' name, you must ensure that all columns defined as keys and values are effectively available in the result-set.

It cuts the starting and closing braces so i have to "double them" (column name="[[Measures].[dim]]").
Could you check it please?

Cheers.

[Seddryck]

I created a bug at #420

Not really part of this new feature but linked to this new version (refactoring).

[fefeslon]

Cool, it works now. Thanks for the fix!

[fefeslon]

Hey @Seddryck ,

In the documentation I can see that the transform in the assert part should not be used at all.

http://www.nbi.io/docs/scalar-transform/

Is there any new possibility to do this in kind of "correct" way?

I mean, is there any other way to compare key values case insensitive?

Cheers.

[Seddryck]

The old syntax of using the transform under a column tag shouldn't be used but transform is still part of the alterations supported by the framework.

Specifically for being case-insensitive on keys, I'd upper or lower-case in a transform that is under an alteration, no issue with this.

[fefeslon]

Hmm, then maybe I just don't understand the documentation.

Specfically this part in between the two examples (I thought the second example is the second option that should be avoided):

From your documentation:
The second option is deprecated and should be avoided as much as possible. It can safely be replaced by the previous notation. The idea of this notation was to specify the transformation in the column’s definition of an equal-to assertion.

Anyway, if you say it's ok to be used then I am OK with it.

Thanks for the quick response.