Data Warehouse Testing Using Business Intelligence Software

Is data warehouse testing essential?

The need to focus on data-driven decision making has made it essential for organisations to have access to their data. This often takes the form of a data warehouse. Data warehouses created using traditional methods made cumbersome data warehouse testing essential. Traditional ELT/ETL development is lengthy, costly and unreliable, hence the need for extensive testing. With data warehouse automation, all transformation logic is stored in metadata and creates error-free code. This dramatically reduces the emphasis on data warehouse testing.

What is a data warehouse schema?

Data warehouses extract data from relational data models and transform them to be optimised for reporting and analytics. There is many a data warehouse schema in use today, however the best structures use the Kimball approach, resulting in star and snowflake schemas. The schema that is used in your data warehouse depends on the nature of the data you are reporting on.

The star schema (the diagram resembles a star) is the most common model. The star centre comprises a fact table and the points – dimension tables. A typical fact table stores sales data, and dimension tables – data about clients, products, channels, regions and so on.

A fact table normally has two column types: dimension table keys and numeric measurements. Fact tables can contain data at various granularities – e.g. a detailed, transactional view or aggregated view of data.

Dimension tables consist of fields used to categorize data. The primary key of each dimension table is used to link to the fact table’s dimension key. Dimensional attributes (usually textual) are used to describe the corresponding data record in the fact table.

The star schema is the most commonly used data warehouse architecture type, as it has a simple structure and is easily browsable.

The snowflake schema is a somewhat more complex version of the star schema. The main difference between the two is that the star schema displays de-normalised dimension tables, and the snowflake schema – normalised.

The fact constellation schema comprises multiple fact tables sharing numerous dimension tables (called conformed dimensions). A schema becomes a constellation when multiple star or snowflake schemas share conformed dimensions. A report can contain data from multiple fact tables by joining through a conformed dimension.

Minerra data warehouses

Minerra’s data warehousing tools are automated and fully customizable. The choice of schema depends on each client’s specific needs. E.g., the schema may list summarised business events and more granular, transactional fact tables.

What all of our BI and analytics solutions have in common is – descriptive attributes that allow for ultimate flexibility. You may search for overall revenue trends, revenues by periods, top customers by revenue, etc.

Whereas traditional BI and analysis tools use the manual ELT/ETL methods, Minerra’s solutions are fully automated. Automated data warehousing has numerous benefits, with speed and cost-efficiency ranking first.

Namely, the traditional ELT/ETL methods call for a significant amount of manual coding; up to 75%, to be precise. That takes time; and by the time the process has been finished, the information requirements of the organisation have changed and the data warehouse is already behind the eight ball. This is a vicious circle and often leads to project failure. Manual coding is also highly expensive and very slow. The end result is an out of date data warehouse that is prone to errors.

In contrast, automation has eliminated the downsides. Automated data warehouses allow for changes to be made to the data warehouse rapidly – usually in hours and days. In comparison, the average waiting time in case of manual coding ranges from weeks to months.

Advantages of data warehouse testing

As mentioned previously, the advantages of data warehouse are more relevant for data warehouses built using manual methods.

Firstly, manual ELT / ETL methods involve many lines of code to be written from scratch. This leads to frequent errors in the code and incorrect business logic. This leads to issues that need to be found (using tedious methods) before the code is released to production.

Secondly, manual coding leads to numerous inconsistencies across the solution. When some logic is updated in one place, the developer needs to find all other areas where the logic is found and update each correctly. This is often not done correctly and results in problems down the track.

Thirdly, collecting historical data can be problematic. Changing a dimension from slowly changing dimension (SCD) type 1 to type 2 can be very tedious and cumbersome.

Manual data warehouse testing is a complex, time-consuming process. With automated data warehousing solutions, much of the testing is no longer required as the solution creates error-free and consistent code.

When it comes to manual data warehousing solutions, business logic is buried in complex code. Automated BI technology eliminates the issue completely as it is all stored in metadata that is accessible through an intuitive user interface.

With all kinds of information being loaded and stored in a centralised location, data access becomes an easy task. Moreover, the data is structured in a format that is optimised for analytics – allowing you to get the information you need quickly and easily.

All this might appear to be way too complicated, but it doesn’t have to be. A successful organisational plan starts with expert BI consulting services. And that’s where Minerra steps in. We offer customised solutions for each client, but only after we have learned all the specifics.

We listen to your requests, offer business consulting, implement solutions that suit your needs and train in you in dashboard design, report development and effective collaboration. We stay with our clients through every step of the process, making certain that they employ all available resources to achieve their desired business goals.

Data warehouse testing – a final word

Everything considered, automated data warehouse testing will always be required, but is far less relevant (and cumbersome) using data warehouse automation. Minerra will set you on the right track of always making only data-driven decisions, which are, in turn, certain to boost your organisation’s performance in the long run.

Contact Minerra for product demonstration and consulting!