Automated Testing to Improve Data Quality in Our Qlik Sense Reports

_{by Lisa Obritzberger, & Tanja Grill, Data Engineers}

To make data accessible for our business users, we are working with a self-service visualization tool called Qlik Sense. It is used as our analytics hub and gives access to data we store in our data warehouse and our Hadoop cluster. Our business users need this data to make decisions concerning the product or for overseeing our KPIs. That’s why it’s critical that these numbers are 100% correct and why we introduced automated tests for these reports. In this blog post, we’ll show you how we implemented those automated tests.

Embrace constant change

When we started implementing and designing our first reports in Qlik, we tested the both the data integration and the visualizations manually before publishing them. With more and more small changes to the reports, we realized that it’s too much effort to test everything over and over again manually. That’s why we looked for possibilities to automate these regression tests. As we didn’t find an existing tool that could help us fulfil our testing needs, we started building our own test suite using the Qlik Sense .Net SDK.

Our goal was to ensure that not only the data integration worked as expected, but also that the numbers in the visualizations were accurate. This is why we wanted to implement black-box tests that compared the numbers shown in the charts with the results of SQL queries from our data warehouse, where the data was originally coming from.

Implementing tests with the .NET SDK

We’d like to show our approach with a sample report where we wanted to make sure that the data shown in the bar chart of this report is the same as we would get by querying our data warehouse. Our approach consisted of three steps which will be further explained in more detail:

1. Get the correct Qlik sheet
2. Get the visualized data from a report
3. Query raw data from data warehouse and compare with visualized data

Get the correct Qlik sheet

A Qlik report can contain multiple sheets, so the first steps were to connect to the Qlik Engine, find the right report and get the sheet where the bar chart we are looking for is located:

public static ISheet CreateSheet(QlikTestConfig qlik)
{
  ISheet sheet;

  // connect to the engine
  Uri uri = qlik.Uri;
  ILocation location = Qlik.Engine.Location.FromUri(uri);
  location.VirtualProxyPath = qlik.VirtualProxyPath;
  NetworkCredential cred = new NetworkCredential(qlik.User, qlik.Password, qlik.Domain);
  location.AsNtlmUserViaProxy(true, cred, true);

  // get the right report
  IAppIdentifier appId = location.AppWithNameOrDefault(qlik.AppName);
  Assert.IsNotNull(appId, "App with Name '{0}' not found", qlik.AppName);
  IApp qlikApp = location.App(appId);

  // get the right sheet
  sheet = qlikApp.GetSheetWithName(qlik.SheetName);
  Assert.IsNotNull(sheet, "Sheet with Name '{0}' not found", qlik.SheetName);

  return sheet;
}

Get the visualized data from a report

Once we had the sheet we were looking for, we wanted to get the numbers shown in our bar chart. This is done with a function that takes the sheet as well as the name or the ID of the chart on that sheet. We used the name of the chart instead of the ID as it’s easier to read and normally we don’t have charts with the same name on the exact same sheet. So for our reports, the sheet name in combination with the chart name is unique:

public static Dictionary<string, int> GetBarchartValues(this ISheet sheet, string barchartNameOrId)
{
  return sheet.Children.OfType<IBarchart>().
    First(c => c.Title == barchartNameOrId || c.Id == barchartNameOrId).
    DataPages.First().Matrix.
    ToDictionary(row => row.ElementAt(0).Text, row => (int)row.ElementAt(1).Num);
}

With those steps done we can implement the actual test cases where we compare the numbers of the data warehouse with the numbers shown in the Qlik report we wanted to update.

Query raw data from data warehouse and compare with visualized data

We create a new method for each chart, which has the SQL statement needed to receive the correct number from the data warehouse. The numbers are read from the data warehouse using ODBC, and from Qlik with the methods shown above. Both result sets are expected to be exactly the same:

[TestMethod]
[TestCategory(APP_NAME)]
public void TestBarChart()
{
  Dictionary<string, int> resultDWH = new Dictionary<string, int>();

  // connect to the DWH and gather the numbers you would expect to see in the tested chart 
  var cmd = "insert first sql statement here"; 
  resultDWH["Estimate"] = DwhConnection.GetScalar<int>(cmd);

  cmd = "insert second sql statement here";
  resultDWH["Actual"] = DwhConnection.GetScalar<int>(cmd);

  cmd = "insert third sql statement here";
  resultDWH["Goal"] = DwhConnection.GetScalar<int>(cmd);

  Dictionary<string, int> resultQlik = SheetUnderTest.GetBarchartValues("Bar Chart#Estimate, Actual, Goal");

  Assert.AreEqual(ToAssertableString(resultDWH), ToAssertableString(resultQlik));
}

Improvements since we introduced automated testing

We are constantly changing and adapting reports. Before having the automated tests in place, we always had to test the old parts of the report manually over and over again. When we changed something in the visualization it also happened that we published the changes without testing them beforehand.

Since the test suite was set up, we run it every time before deploying the changed report and only publish it when all tests are green. Currently, this is still done manually, but we are working on automating this process with Jenkins to have the full process automated. This ensures that we don’t break existing views with our latest changes. It gives us stability and creates trust in the data we are preparing for our business users. Furthermore, it speeds up our daily work and gives us confidence when publishing changes.

Although implementing the tests takes additional time, there are many benefits that are definitely worth the extra effort. How about you? How do you test your business reports?

***