使用 NBI 测试 DAX 计算 [英] Testing DAX calculation with NBi

问题描述

我正在对我们的 SSAS 表格项目的自动化测试工具进行一些研究.我找到了 NBI，觉得它真的很酷.我尝试设置它并成功运行了一些基本测试.但是，当我尝试测试 dax 计算时，它显示未找到函数"(见屏幕截图).听起来它不支持 SUM，但鉴于 SUM 是一个基本功能，我想它应该可以工作.因为我是这个工具的新手，所以我想仔细检查我是否做错了什么，或者这只是错误的意思......(不支持的功能).

I'm doing some research on automated test tool for our SSAS Tabular project. I found NBi and thought it is really cool. I attempted to set it up and successfully ran some basic tests. However, when I attempted to test dax calculation, it says "Function not found" (see screenshot). It sounds like it does not support SUM, but given that SUM is a basic function I would imagine it should work. Since I'm new to this tool, I wanted to double check if I've done something wrong or it is simply what the error is saying... (not supported function).

我回去查看了 NBi 文档，其中提到要检查他们的 NCAL.dll 以获取所有可用的表达式.不幸的是，我无法打开该 dll 文件的可读版本.任何帮助表示赞赏.

I went back and review NBi documentation and it mentioned to check out their NCAL.dll for all available expression. Unfortunately, I'm unable to open a readable version of that dll file. Any help is appreciated.

这是我要测试的公式:

=SUMX(FILTER(MyTable, AND(MyTable[Date] = EARLIER(MyTable[Date]), MyTable[Account] = EARLIER(MyTable[Account]))), MyTable[Amount])

=SUMX(FILTER(MyTable, AND(MyTable[Date] = EARLIER(MyTable[Date]), MyTable[Account] = EARLIER(MyTable[Account]))), MyTable[Amount])

XML 代码 (nbits) 文件

 <test name="My second test: Calculated column compared to DAX formula">
  <system-under-test>
   <execution>
    <query connectionString="Provider=MSOLAP.7;Data Source...">
     <![CDATA[    
    EVALUATE
    SUMMARIZE (MyTable, MyTable[Date], MyTable[Account], MyTable[Amount], MyTable[CalculatedAmount])
     ]]>
    </query>
   </execution>
  </system-under-test>
  <assert>
    <evaluate-rows>
      <variable column-index="0">Date</variable>
      <variable column-index="1">Account</variable>
      <variable column-index="2">Amount</variable>
      <variable column-index="3">CalculatedAmount</variable>
      <expression column-index="3" type="numeric" tolerance="0.01"> = SUMX(FILTER(MyTable, AND(MyTable[Date] = EARLIER(MyTable[Date]), MyTable[Account] = EARLIER(MyTable[Account]))), MyTable[Amount])</expression>
    </evaluate-rows>
  </assert>
 </test>

推荐答案

NBi 支持在查询标签中评估 DAX 查询，但不支持在表达式标签中.表达式和评估行标签并非旨在比较两个查询.为此，请更改您的测试以在两个查询之间使用断言 equalTo.它会更容易并且会起作用.

NBi supports the evaluation of DAX queries in the query tag but not in an expression tag. Expression and evaluate-rows tags are not designed to compare two queries. To achieve this, change your test to use the assertion equalTo between your two queries. It will be easier and will work.

我想一个更好的问题是如何测试一个度量和一个计算列以确保另一个开发人员不不小心更改了我输入的计算/表达式设计表格模型?

I guess a better question would be how do I test a measure and a calculated column in term of ensuring that another developer doesn't accidentally change the calculation/expression I entered when designing the Tabular model?

我将从三个层面回答:概念、逻辑和技术.

I'll answer at three levels: conceptual, logical and technical.

在概念层面，您的测试是错误的:您永远不应该在断言和被测系统中使用相同的实现.这不是特定于 NBI 或任何框架，而是特定于所有自动化测试.测试的作用不是确保某人不会改变某事，而是确保某事给出正确的结果.即使您的实现是错误的，将工件与其自身进行比较总是会导致绿色测试.在这种情况下，您必须使用具体的静态结果来更改您的断言，或者您需要创建一个 sql 语句来导致数据库的相同计算，或者在 MDX 中找到另一个查询，从而产生相同的结果.

At the conceptual level, your test is wrong: you should never use the same implementation in your assertion and in your system-under-test. This is not specifc to NBi or to any framework but to all automated tests. The role of a test is not ensure that someone doesn't change something but to ensure that something gives the correct result. Comparing an artifact to itself will always result in a green test even if your implementation is wrong. In this case, you must change your assertion with a concrete static result or you need to create a sql statements resulting in the same calculation of your database or find another query in MDX resulting in the same result.

在逻辑层面，下面这句话是不正确的

At the logical level the following sentence is not correct

这是我要测试的公式:

您已经在 assert 中定义了这个公式，而不是在 system-under-test 中.这意味着这不是您要测试的内容，而是您的参考资料(您 100% 确定它是正确的).您正在测试的是查询 EVALUATE SUMMARIZE (MyTable, MyTable[Date], MyTable[Account], MyTable[Amount], MyTable[CalculatedAmount]).

You've defined this formula in your assert and not in your system-under-test. It means that it's not what you're testing but it's your reference (something you're 100% sure that it's correct). What you're testing is the query EVALUATE SUMMARIZE (MyTable, MyTable[Date], MyTable[Account], MyTable[Amount], MyTable[CalculatedAmount]).

在技术层面，使用评估行不是正确的选择.这个断言不需要函数或查询，而是基于行变量的表达式(没有 DAX，没有 SQL，...).EARLIER 的使用清楚地表明它是不可能的.在您的情况下，您必须比较两个查询，可能是:

At the technical level, using an evaluate-rows is nopt the correct option. This assertion is not expecting a function or a query but an expression based on row's variable (no DAX, no SQL, ...). The usage of EARLIER is a clear sign that it won't be possible. In your case, you must compare two queries probably something as:

<assert>
  <equalTo>
    <column index="0" role="key" type="dateTime"/>
    <column index="1" role="key" type="numeric"/>
    <column index="2" role="value" type="numeric"/>
    <column index="3" role="value" type="numeric" tolerance="0.01"/>
    <query>
      EVALUATE SUMMARIZE (MyTable, MyTable[Date], MyTable[Account], MyTable[Amount], SUMX(FILTER(MyTable, AND(MyTable[Date] = EARLIER(MyTable[Date]), MyTable[Account] = EARLIER(MyTable[Account]))), MyTable[Amount])
    </query>
  </equalTo>
</assert>

PS:我显然不是 DAX 专家，从语法的角度来看，我不确定上面的查询是否有效.

PS: I'm clearly not a specilist of DAX and I'm not sure the query above is valid from a syntax point of view.

这篇关于使用 NBI 测试 DAX 计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！