It can be confusing at first to know how to structure your SQL for PowerTester test cases. In this article I will go through some tips to get you started, along with some common mistakes and examples.
How to know how to structure your SQL
PowerTester works by comparing the resultset of the SQL output to the data held within a Power BI visualisation. Therefore to help us structure the SQL, lets look at the visualisations in Power BI.
Above is a simple visualisation in Power BI, showing data from the adventure works database. If we we wanted to create a test case for this visual, at first it may be difficult to know where to start. We can see that it has one measure (total sales) aggregated by one dimension (quarter); but how do we format the SQL?
"Export as Excel"
There's one very simple trick to get started. Depending on your visualisation settings, each visualisation will have an option to export your data to Excel or a csv file. This is achieved by clicking the context menu (three little dots at top right), and selecting "Export data".
The exported data will include all of the data used by the visual, including any data displayed in drilldowns.
Now we have a table that we can copy in our SQL output. In this case we require five columns. We also notice that the quarters are not just numbers 1-4 but have the prefix of Qtr; that isn't in the database. Therefore the SQL to test this chart could be:
Common Mistakes
The two most common mistakes are the column headers and the data type of the results.
Column Headers
Column headers must be identical to that in Power BI. Often in databases you do not have spaces in column names or aliases. However it is very common in Power BI to add spaces and rename the column so that its more friendly for the report consumer.
In the example above the measure is 'Total Sales' however in the database this column was a aggregate of two columns called SalesAmount. When writing the SQL we had to ensure that the column name matched Power BI, therefore we set the column alias to [Total Sales], this could also have been 'Total Sales'.
Formatting & Data types
Ensure that the data type output in SQL is the same as that for Power BI. For numerical data types you do not need to apply any string formatting. In the above example, the Total Sales measure in Power BI was formatted with a dollar sign and commas. "$13,221,607". If the data types are the same, PowerTester will apply the same formatting to the values in SQL. Therefore you do not need to manually format the results from SQL.