Automated Data Warehouse Testing with AnalyticsCreator

Let's take a look at the input data.

For example, here is the input data for the IN Products table. This is the table containing the data imported from the Products table in the Northwind database.

Here you can see how we define the data itself.

There are three rows, or three products. We can see the same data as a table if we click on Table Definition.

Here you can see the Product ID, Category ID and so on. We have three data rows containing three different products.

We can click on Preview, and in this preview we can see the data for the import table.

For example, here is the data in the input Categories table.

If I click Edit Input Data, there is only one row containing Category ID 1, Category Name Category 1 and so on.

Here we can also see the same data as a table in the definition.

Defining Expected Data

Here we can see the expected data.

In our case, this is the STAR Fact Order Details table. This is a fact table in the data mart layer.

After the data processing is finished, we expect to see four rows.

We can open the table definition, and here we can see the different foreign key columns related to the surrogate keys in the different tables.

This is the data we expect to see in the Fact Order Details table after the data processing is finished.

Running a Test Case

How do we execute the test case?

Let's click on the test itself and select Edit Test.

Every test is stored as a stored procedure, and the stored procedure performs the test.

Let me try to run the test.

I click on Run Test, and I see that no errors were found.

But let me try to reproduce an error.

For example, I will modify the expected data in the Fact Order Details table.

Instead of having the quantity 10, I will change the quantity to 5.

Therefore, there should be a difference between the expected data and the actual fact table data.

I go back to the test, update my test script and run the test.

Now you can see there are differences in the expected data.

In the Fact Order Details table, the expected quantity is 5, but the actual data contains 10 in the Quantity column.

This is the difference.

By the way, every test run is stored in a specific log table.

Here you can see a log entry.

I can click on this log entry and select Log Details to see the result of my test.

There was something wrong with the Quantity column. The expected value was 5, and the actual value was 10.

As I said, every test run is stored in the log table.

I can define different tests, and I can execute many tests one after another.

To do that, I click on Test Cases and then Run Tests.

I select the tests I want to run and click Run Tests.

The tests will be executed.

Here you can see the result. In the Fact Order Details table, we have the invalid quantity value in the Quantity column.

And here we can see the log.

When we execute multiple tests at once, a specific log is created.

Let's fix the error in the expected data.

I go to Expected Data, select Edit Expected Data and modify the quantity back to 10.

Then I go to Edit Test, select Update Script and run the test again.

Now the test has finished successfully.

As you can see, every test is stored in the model as a stored procedure.

You can automate your testing by executing these stored procedures.

You can execute the stored procedures from AnalyticsCreator.

When I click on Run Test, the corresponding stored procedure is executed.

Or you can execute these stored procedures outside AnalyticsCreator, in a separate task.

The important point is that every test is stored as a stored procedure.

By the way, when I click on Synchronise, we have a new option called Update Test Cases.

When this option is selected, the test stored procedures will be applied to the generated data warehouse.

The same applies to deployment.

When I create a deployment package, I can select whether the stored procedures containing the test cases should be deployed to the target data warehouse.

For example, if you deploy to a production environment, it is not necessary to deploy the test cases.

But if you deploy to a development environment, you can deploy the test cases too.

Later, you can execute the test cases in the development version of your data warehouse outside AnalyticsCreator.

Let's create a new test case.

I will click on Add New Test and call it New Test.

Here you can see our new test.

Let's add input data.

For example, we will test the IN Categories table.

I can add one sample, and here you can see the definition of the sample data.

I will edit it using Table Definition.

Here I can use an iterator.

Using an iterator, I can generate multiple data rows.

For example, I will generate five rows with the iterator minimum set to 1 and the iterator maximum set to 5.

Here I can use variables which will be incremented using the iterator.

In our case, the Category ID should be an integer.

I can use a variable which will contain the values 1, 2, 3, 4 and 5, depending on the iterator.

I will also create descriptions containing the values 1, 2, 3, 4 and 5.

Let's preview the data.

Here is my definition.

You can see that we have an iterator from 1 to 5. That means five rows will be generated for the test data.

I use special variables, and you can see the definitions of these variables here.

Let's click on Preview.

Now I can see the data that will be generated for the IN Categories table.

The Category IDs range from 1 to 5, with different category names and descriptions.

Let me add expected data.

After processing, I want to check the data in the STAR DIM Categories table.

This is our dimension in the data mart layer.

I assume all the test rows should be processed and stored in the dimension table in the data mart layer.

Let's add a sample.

I go to the definition, and I think there should be five rows.

Again, for the Category ID, the definition is almost the same.

I save the definition.

When I click on Preview, I can see the data I expect to have in the DIM Categories table.

Let me show you the data processing path.

If I set the filter here, I assume the data will be imported into the IN Categories table.

Then the data will be historised, and after that it will appear in the DIM Categories table.

Let me go back to the new test.

I select Edit Test, update the script and run the test.

Oops, sorry. I forgot something.

Here there is an option called Delete All Data Before Start.

I forgot to switch this checkbox on.

Otherwise, old test data might affect my test case.

Now I switch it on, update the script and run the test again.

You can see there is an error in the actual data.

We have one more row with an ID of zero.

This is the default member in the category dimension, and I forgot to add this default member to the expected data.

The member has an ID of zero.

Let me modify my expected data.

I go to Edit Expected Data and open the definition.

I add one more row with the Category ID zero, a null category name, a null description, a null picture and the corresponding date value.

Now let me go back to New Test.

I select Edit Test, update the script and run the test again.

You can see the test executed successfully.

Now I have two different test cases.

I click on List/Run Tests.

I can select both tests and execute them together.

I select all tests and click Run Tests.

Now you can see that all tests have executed successfully.

And this is how it works.

Thank you.