Month: December 2015

Testing a Power BI Desktop solution with NBi

Since the release 1.12 (21-DEC-2015), NBi offers the opportunity to connect to a Power BI Desktop model. Whats does it mean? Simply, that you can interact with the model of your Power BI Desktop solution exactly the same way you’d use a Tabular model hosted by SSAS! You’re able to test the model (existence of tables, columns, …), members of the dimensions (existence, format, ordering, …) and you can execute DAX queries on this model (and compare it to expected results). 100% of feature parity between Power BI Desktop and SSAS Tabular.

How can you achieve this? First, download the last version of NBi and then check how to setup your environment. Once you’re ready, it will be time to define your test-suite.

To define your connection string for a Power BI Desktop solution, you can use the settings element or the connectionString attribute of most of the elements provided by NBi. To explain to NBi that your connection string references a Power BI Desktop solution and not a SQL Server instance, you need to start your connection string by “PBIX = “ followed by the solution’s name (filename without the extension .pbix). At the moment, NBI doesn’t support additional tokens that the PBIX one but it will be improved in a near future.

<settings>
  <default apply-to="system-under-test">
    <connectionString>
      PBIX = My Power BI Desktop Solution
    </connectionString>
  </default>
</settings>

To effectively run your tests, your Power BI Solution must be started! Behind the (not so) closed doors, Power BI Desktop will start a local instance of Tabular SSAS. NBi will detect this instance based on the provided solution’s name and calculate a correct and more complex connection string.

Naturally, you can manually start your PBI Desktop solution and it will be the case when developing your solution. But if your setup is more advanced and you’re on a build or tests server then you’ll need to add some additional steps.

NBi offers a way to the start your Power BI Desktop solution. To do this you need to use the setup element in a test or group.

One of the setuptask is exe-run and the common use is to start some small exe or batch files preparing the environment. In this case we’ll use it to start the Power BI Desktop solution. You need to provide the file path for your pbix solution.

<setup>
  <tasks run-once="true" parallel="false">
    <exe-run name="My Power BI Desktop Solution.pbix"
      path="..\PowerBiDesktop\"/>
  </tasks>
</setup>

This solution has a first problem: the task exe-run will return quickly and the Power BI Desktop solution will not be fully started. At the moment the best way to work-around this issue is to introduce a wait task after the exe-run and ask to wait for 10 seconds.

<setup>
  <tasks run-once="true" parallel="false">
    <exe-run name="My Power BI Desktop Solution.pbix" 
      path="..\PowerBiDesktop\" />
    <wait milliseconds="10000" />
  </tasks>
</setup>

The next problem will occur when you try to run your test-suite many times. You can’t start multiple times the same pbix file. So before proceeding to the exe-run task, we’ll need to ensure that we have no Power BI Desktop solutions open. To do this we’ll kill all the processes corresponding to a Power BI Desktop solutions with the task exe-kill. Note that it will kill all the Power BI Desktop solutions … don’t use this when developing on your laptop!

<setup>
  <tasks run-once="true" parallel="false">
    <exe-kill name="PBIDesktop" />
    <exe-run name="My Power BI Desktop Solution.pbix" 
      path="..\PowerBiDesktop\" />
    <wait milliseconds="10000" />
  </tasks>
</setup>

Feel free to provide feedback by reporting bugs or requesting new features.

Advertisements

SSAS measures’ format and Excel

For one of the cube I’m working on, we needed to format a measure by adding the suffix k€. The front-end is Excel 2010/2013.

My colleague was in charge of this part of the development and had defined a FORMAT_STRING for the measure, as we usually do: FORMAT_STRING ="#,##0 k€". I was in charge of the testing, with NBi, and I had defined a test for the format of a cell and this test was successful. Sun shinning.

A few days later, I received a screenshot of an Excel sheet and my eyes were caught by a poor formatting. In place of an expected 1.075 k€ I had an awful 1075,2357777. My first move was to connect with SSMS and I ran the equivalent MDX query and surprise … the format was correct 1.075 k€. Back to an Excel sheet, connected to this cube, and indeed the format was wrong 1075,2357777. Confirmation from the cube definition, the format was defined and confirmation from NBi the format was correctly applied. WTF? Why Excel wasn’t able to display the value correctly formatted when other tools were able?

Since the development of NBi, I knew that SSAS is returning a VALUE and a FORMATTED_VALUE. NBi is able to retrieve this FORMATTED_VALUE to perform a test of a cell’s format. The documentation of SSAS is describing the relation between FORMATTED_VALUE and FORMAT_STRING:

FORMAT_STRING: The formatting template to be applied to the value of the cell to generate FORMATTED_VALUE property

I decided to use the SSAS profiler to retrieve the query emitted by Excel. The query wasn’t really complex but the CELL PROPERTIES were a bit embarrassing: no trace of FORMATTED_VALUE just FORMAT_STRING. Based on this observation, a search on Google confirmed that Excel is retrieving the FORMAT_STRING and is applying the format by itself (in place of relying on the FORMATTED_VALUE returned by SSAS).

Apparently Excel and SSAS don’t have the same interpretation of the FORMAT_STRING.  A miracle occurred and I took the decision to place a backslash before the k: #.##0 \k€. The format was still correct in SSAS and suddenly a k was also visible in Excel … a few seconds later , I added another backslash in front of the euro symbol and the value was correctly formatted in Excel and SSAS.

Based on  SSAS documentation, the backslash is not needed and indeed SSAS is correcty managing the FORMAT_STRING without the backslash. But apparently Excel is less tolerant and the backslash is mandatory in this kind of formatting.