Support: SSAS format_string and NBi

[CristianaDias]

Is NBi able to retrieve the FORMATTED_VALUE from a mdx query?

Basically, I need to apply a FORMAT_STRING to the query under test to make sure that is equal to the query assert.

But, when executing NBi, it's like the format string was not applied.

My query under test is something like this:

WITH 
MEMBER [Measures].[X] AS
([Area].[Area].&[X],
[Measures].[Value]
),FORMAT_STRING = '###0;-###0;0;0'

SELECT [Measures].[X]
ON COLUMNS,
[Date].[Calendar Year].[Calendar Year].MEMBERS
ON ROWS
FROM [Model]
CELL PROPERTIES FORMATTED_VALUE

Thank you! Regards, Cristiana Dias

[Seddryck]

Hi @CristianaDias

equal-to and most of the other constraints are retrieving the unformatted value from a cell set (result of a MDX/DAX query). If you want to use the FORMAT_STRING, you should use the matchPattern constraint.

I'd like to be transparent, this is a constraint that we internally use rarely (and not recently) and I'm not sure that it's not broken and the syntax is not future-proof. If you're facing issues, please share them.

[FuegoArtificial]

Hi @CristianaDias,

I like using explicit formatting of the value by using the format function for exactly that use case within nbi. Then the formatting is considered properly, as far as I remember. If I'm not mistaken, the format string is just a hint for client tools like excel how to format but doesn't need to take it into account, necessarily.

Does this work?: Format(mycalculation, "###0")

cheers

[FuegoArtificial]

Usually, when both queries return a value of numeric data type in columns 2 or more nbi just compares these numbers and doesn't care about formatting. It does not work as soon as any of the values is considered being a string. having that in mind could also lead to easier solutions than explicitly formatting values for comparison.

[Seddryck]

Ok, reading the comments of @FuegoArtificial I've a better understanding of your initial question.

As I initially explained there are two values for any cell-set. One is the formatted value and the other is the raw value. NBi is using the raw value in most constraints. Applying the format at the end of a MDX query, only has influence on the formatted value and not on the raw value. This is not related to NBi but to SSAS. At the opposite the usage of the FORMAT function will influence both values (again not NBi but SSAS)

[CristianaDias]

Hi!

Thanks for your answers!

@FuegoArtificial, the format function that you suggested does not work. I try to run on SSAS and the function is not recognized. Can you share some documentation about it?

If equal-to use the unformatted value from a cell set, I need to find other solution for my problem...

My test is a crossjoin with 3 measures, comparing two cubes. The problem is that cube 2 (query assert) have multiples rows with 0 and cube 2 (query under test) no. As a result of the crossjoin, cube 2 returns missing rows as null and this causes differences. I try to use filter function in cube 2 but the query runs for a long time and ends in a timeout. I also try to use null-to-zero (native transformation in NBi) but in this case it is not adequate.

My last idea was to apply format string to force all nulls to always be 0...

Do you have any tips on how to overcome this?

[Seddryck]

As a general tip, don't try find work-around before being 100% sure that the main path is not suitable. Let's make steps back and going back to your initial issue.

Do I understand correctly that at the end this is more or less the dataset that you're receiving:

System under test:	Key	Measure 1	Measure 2	Measure 3
A	0	0	0
B	890	121	40300
C	0	0	0
D	100	25	10500

Assert:	Key	Measure 1	Measure 2	Measure 3
A	(null)	(null)	(null)
B	890	121	40300
C	(null)	(null)	(null)
D	100	25	10500

For me you should apply an alteration of type transform to the result-set in your assertion:

<equalTo keys="first">
  <result-set>
    <query>
       ...
    </query>
    <alteration>
      <transform column="#1" language="native" original-type="numeric">
        null-to-zero
      </transform>
      <transform column="#2" language="native" original-type="numeric">
        null-to-zero
      </transform>
      <transform column="#3" language="native" original-type="numeric">
        null-to-zero
      </transform>
    </alteration>
  </result-set>
</equalTo>

If it doesn't work, please explain what's the issue with this solution.

[CristianaDias]

Being more concrete, my dataset is:

System under test:

Key	Measure 1	Measure 2	Measure 3
A	(null)	4	4
B	890	121	40300
C	(null)	100	9
D	(null)	25	10500

Assert:

Key	Measure 1	Measure 2	Measure 3
A	0	4	4
B	890	121	40300
C	0	100	9
D	(null)	25	10500

So, when I apply that alteration (null-to-zero) doesn´t work because will change the query assert and in this case the null lines are in the query under test.

And more, there are cases that are null in both queries. When I apply the transformation, those lines become differences.

I hope I explained myself well.

[CristianaDias]

That's why I need to find a way to apply a transformation to both queries.

[Seddryck]

Just to be clear, you can apply an alteration to both assert but also system-under-test. So you could apply null-to-zero to your first result-set.

<system-under-test>
  <result-set>
     <query>
      ...
     </query>
     <alteration>
        <transform ... />
     </alteration>
  </result-set>
</system-under-test>

Anyway, the issue sounds to be that you've several cases and try to fix everything within a single test-case and that usually means that the test-case is difficult to write but also easily breakable and difficult to understand negative results. It's not a best practice, probably better to write several tiny tests.

I'd write two distinct tests to handle your case. All of them will have the same query in SUT and asserts that the one you've today.

• On the first test, I'd use the null-to-zero to both SUT and assert and an equal-to. This test is not sufficient because there is still a doubt with the key D.
• On the second I'd filter on [Measure 1] is null on assertion (resulting on only the row with key D is tested for a second time). In place of an equal-to, I'll put a superset-of. NBi won't comply about the missing rows in the asserted result-set but will compare that effectively all rows with (null) in the assertion have effectively a (null) in SUT.

[FuegoArtificial]

Thanks for clarifying with the example! Would it also work by changing the member-definition to include a check of 0? In that case return null. If it doesn't matter to you if the result is a 0 or null this way all 0s would be "null"ified and equal between test- and assert-queries. If it would be a way, this just adds to possibilities mentioned by @Seddryck

P.S. unfortunately, I remembered wrong about a format-function in MDX. :(

[CristianaDias]

I was not aware that it was possible to apply changes to both queries. My problem is solved! Thank you for your help!