English
Data Warehouse Regression Testing
Automated Data Warehouse Testing with AnalyticsCreator
This video answers
- How can I test data warehouse model changes before deployment?
- How do I define input data and expected results in AnalyticsCreator?
- How does AnalyticsCreator identify wrong or unexpected rows?
- Can I generate many test records using iterators and variables?
- Can I execute test procedures from SQL Server or a separate test environment?
- - How can I run multiple test cases and review their execution logs?
Key takeaways
- AnalyticsCreator test cases follow a given–when–then approach: input data, expected results and comparison against actual processing.
- The Northwind demonstration checks a dimensional warehouse containing staging, persistent staging, core and data mart layers.
- A deliberate expected-data mismatch makes unexpected rows and different values visible in the comparison result.
- Input and expected datasets can be edited as text or in a tabular definition, and saved for reuse.
- Iterators and the int and str variables generate multiple test records from one definition; the demonstration corrects a four-through-ten range to seven records.
- AnalyticsCreator generates SQL stored procedures for test cases, which can also be executed directly in a database or deployed to a separate test environment.
- Each test run creates a log, and the List/Run Tests function supports running selected tests together before deployment.
Transcript
Speaker: Peter Smoly
A very warm welcome also from my side. What you're going to see today is AnalyticsCreator test automation. You'll see how to generate test cases, how to do regression testing, and you will see a live demo from Dmitry.
And it's important to understand that AnalyticsCreator has an additional model. This is the test automation, and in this case you have... It's also very important to understand that AnalyticsCreator is a data warehouse automated test technology which generates the code for your entire data warehouse. So it works like this: you have, at the beginning, your source data, and from the source data we pick the metadata and create automatically a data catalogue.
And from this data catalogue, we create automatically, with wizards, when you use the wizards, a draft data warehouse. And this draft data warehouse is stored in an absolutely independent metadata repository. Yeah? So it has nothing to do with outside technology. This makes it possible that you can migrate easily between technologies, like you have the on-premises Microsoft SQL Server, you have Fabric, you have Azure, and it doesn't matter what you develop. Yeah? You don't have to need it at the beginning. You just develop visually on the computer screen.
So this is what you're going to see. We show you a complete project, and out of that we show you how to do the test automation. Yeah, and when you have this, I would say, model, you generate immediately, automatically, the code. And the code will be packed in deployment packages, and these deployment packages go to the Azure cloud, to Fabric, and to Power BI. So we generate the code from the beginning, from the source data through the pipelines, the model, and to Power BI, also for other technologies, if you like.
And now, in the last years, AI was coming in, and customers tried to use AI, and they are facing many problems with that. And we can absolutely understand this. We do data warehouse automation now since 17 years, and we are very experienced with that. And we see the main two problems with AI in code generation: the foundation, the base of the knowledge of the company, is not really appropriately documented. And when, on top, the AI agents have to generate the code, then the outcome is, in most cases, not very suitable. This is what we see and what the customers tell us.
And also, AI uses an LLM, a large language model, and the code looks every two weeks different, and this is what you cannot use in a business context. But we are pretty sure that in one year, in two years, everything is going better and that AnalyticsCreator will use more and more deeply the LLMs. For now, we use deterministic code generation, and this works really very smoothly. So it is your control plane for starting with AI.
And this is what is new with AnalyticsCreator. This is what I wanted to tell you from the news from this year. And now I think let's start with data warehouse testing. When I want to talk too much about AI and AI generation, this is maybe another webinar. We will have this in December or in January. We will see. But I will take over now the token to Dmitry, so that he can show you how AnalyticsCreator data warehouse automation works.
Speaker: Dmitry Sorokin
Okay, then I will take over the presentation. Hi, my name is Dmitry Sorokin. I'm responsible for development in our company, and today I will present you this new feature. Wait a little bit, I should present my screen. I hope it is. Can you see my screen? Peter, can you confirm, please?
Yes. Okay. No, I see you, and now... Ah, you see me? Now it's Analytics tree. Okay, it's Analytics tree now. Yeah. Okay. Thank you.
Okay. Today, I will not present to you how to create the data warehouse, how to generate it using AnalyticsCreator. You can find on the internet, on our webpage or on YouTube, a lot of videos where we show how to generate a new data warehouse from scratch, how to deploy it, how to publish it, and so on. Today, I will present you only this new feature related to the test cases.
Here you can see our typical demo data warehouse based on the Northwind database from Microsoft. We got the Northwind database from Microsoft and created, based on this database, a data warehouse. And here you can see the structure of this data warehouse from left to right. You can see different layers in the data warehouse. There are the data source, staging layer, persist staging layer where we historicise the data, core layer, and at the end the data mart layer. This is a typical Kimball data warehouse containing, at the end, one fact transformation or fact table and many dimensions. You can see here we have the snowflake schema.
And in the past, the main problem, or main disadvantage, of AnalyticsCreator was that AnalyticsCreator was the modelling tool. You were able to create a data warehouse, a model of the data warehouse, but to decide if your model worked correctly was difficult. It was necessary to deploy the data warehouse, to execute the ETL processes, and so on. It was necessary to materialise the model of your data warehouse somewhere, and only then you were able to decide if your model worked correctly or not. Now you can test your model directly from AnalyticsCreator, and I will show you how it works.
Let us take a look at the input data definition, for example. Edit Input Data. Here you can see the small text script. On the top you can see the comments, just a small description of the possibilities. And on the bottom you can see the structure of the test data. You can see that we, for example, define the data for the table in products. This is the import table for the product data. And you can see here three rows. Instead of seeing the data as text, you can see the data as a tabular definition. Here you can see them, for example, three data rows with product IDs one to three and different product names. There are our test data.
Here I can click on Preview, and I will see how the data will look in the future in the table. And you can modify the data directly in this text file, or you can modify the data here using the tabular definition. The same way I used to create other test data, for example, for the tables in categories, in suppliers, in customers, and so on. We have, for example, here two customers. We have here two sales orders, and so on and so on.
And I defined expected data. I can take a look at it. Let me check. Edit Expected Data. For expected data, I got the fact order details table containing the facts for the order details. And what I can do now, I can edit our test. Okay. Every test, in fact, is a stored procedure. The stored procedure will be generated by AnalyticsCreator, and the stored procedure contains your test definition. And I can run the test. In our case, the test worked without any error.
But let us try to reproduce the error. To do that, I will add one more table to expected data. For example, here you see we have different tables in the data mart layer, and I would check the table dim product. This is a product dimension. Let me add it. Add Expected Data. I will test the table star dim products. And here I can just click on Add Sample. It will create one dummy data row for the target table. Here you can see the columns in the target table. Let me remove some columns because, for example, we don't... Oh, but no, it is okay. Let me click on Save.
And as I said, I expect in the sample table, I think in the sample table, I will see the following row. It is wrong, but it is what I expect from that table. Refresh. And let me execute our test again. Edit Test, Run Test. And here you can see there is an error, an error in the table staging products. And here we can see the error definition.
In the actual data, and only in the actual data, we have three data rows. There are three data rows with source ID zero, two, and three. And one data row which we defined contains the wrong data. You can see there, expected, for example, the product name. And in fact, the data contains another value. Here you can see really the differences between what we expect and what, in fact, is available in the target table. Okay, we can see there is something wrong.
Then let me take a look a little bit at the test data definition. How do we define the test data? For example, this table in product, we have here three rows, three products. Let me do it here. And sure, you can define the data row by row, but sometimes, when you should define a lot of test data, it is really uncomfortable to define every data row separately. In case of analytic data, you can use the iterators.
For example, you can define the iterator. It will be from four to ten, with iterator step one. That means we will generate six new data rows, from four to ten with iterator step one. And we can use iterator variables. For example, for integer values, we can use a variable int, and we will use it as a product ID. Then we can use the iterator variable str, which will contain the string value of the iterator. Okay. Then let me now, for example, zero, one. Okay, here I will... So four multiplied by integer iterator int. And the same, for example, not very wrong, that ten. Ten multiplied by integer and zero.
I just created one data row. Here you can see this data row, and this is a definition of the iterator. And let me see the data which we will have in the table. You see we have in the table now ten data rows. Three of them we defined manually, and six other rows were defined using the iterator. And using such iterators, you can define a lot of test data using only one data row. You can see there, iterator from four to ten will generate for us six new data rows. And using the variables int and str, we defined some test values for product ID, product name, and so on. Okay, I will save my test data, and I will execute my test now. Okay, Run Test.
And of course, I see in the product dimension we have many new data, okay, from four to ten. It is seven, not six, which are available in the table dim products, the target dimension table, but which we didn't define in our expected data for the dim product table. I will modify our dim products. Here, as I said, I have two ways. I can get the samples from the table. I will get the samples from the table dim products, and I will take, for example, the new rows from four to ten and take them into our table. Okay, I did it. Let me execute the test, and now everything looks good.
But if you take a look at our dim products table, Edit Expected Data, as I said, here we defined every row, row by row. Instead, we can redefine this table and... get the definition from our... Or let's redefine it manually. Okay. Table definition. I will do it there. You can see every product name has the same template, and so on. Unit price has the same template, and so on. Therefore we can redefine the definition of these ten or eleven rows and say... One moment, I will remove it there.
From iterator min one to zero or to ten, with iterator step one. Okay, here we should have integer, category one. Okay, here we will have str and A. Supplier ID is always one. Then multiply by int. Then multiply by int. Save. I redefined the expected data for the dim product table. Let me see the data definition. And you see the same ten rows defined using only one iterator instead of defining every row, row by row. Let me try to execute the test if I defined everything correctly. Okay, everything is defined correctly.
Okay, then how it technically works. As you maybe know, AnalyticsCreator generates the empty database containing the model of your data warehouse when you click on the Synchronize DWH button. When we click this button, the model you created in AnalyticsCreator is materialised. Materialised means the database will be generated. The name of the database is usually DWH_ and the name of your model. In our case, this is Demo Northwind Test. And every test which you generate there is stored in one stored procedure. The stored procedure is called... Let me show you. It is here. This is the stored procedure.
Our test, the name of our test case is test, and the stored procedure is called CFG test_test. And you can execute this test not only from AnalyticsCreator; you can execute it directly in the database. For example, I can go there, exec CFG test. And when the result is empty, that means it's correct. Let me, for example, modify something. I will modify here the in product table and Edit Input Data. For example, I will change the prod three to prod four. Okay. When I execute the test, I see expected is prod three. In fact, we have prod four in our dim product table.
Let me synchronise my data warehouse, because when I synchronise my data warehouse, the model of the... Okay, this DWH database will be recreated, and now I can execute the stored procedure, and I will see the same there. There are the differences between dim product expected and dim product actual data. That means you can execute all tests not only from AnalyticsCreator, but you can create your own test procedures and execute stored procedures, for example, to test the data. It is even possible to deploy the test stored procedures.
That means, for example, if you have your test version of your data warehouse, you can deploy the changes from AnalyticsCreator, including test stored procedures, and you can use the test stored procedures in the test version of your data warehouse and perform tests. Not only in the model data warehouse which we generate, but in a real test data warehouse containing maybe some test data.
What is important, every time when we execute the test, the logs will be created. And here, when I click on the log and click on Log Details, I can see what happened, which data were wrong. For example, here I performed the test today, and here I can see the log details for every test which I performed. As I said, every time when you perform the test, test logs will be generated, and you can see the results of the logs.
You can create many tests. For example, we have some customers who created already over one hundred tests. You can create a test for every new development which you create. And then you can execute all tests at once. For example, I can click there on List Run Tests. I have there the list of all my tests which I have. I can select all tests which I will execute, click Run Test, and here you see we have the mismatched data. And the result of all tests will be stored in the separate log test table, and you can see the result of all test executions.
As I said, the usual scenario is you create different tests for different developments which you perform, and then you execute all tests before you, for example, decide to deploy your data warehouse. And then, when you see all tests were executed successfully, you are sure that all new changes you developed are correct, and you are able to deploy the data warehouse.
Okay, that's all from my side, I think, at the moment. If you have questions, please, you can ask me.
