Home >> Data Testing Concepts >> BI Testing
Example : A new dashboard page was created with too many reports and prompts in one page which made it difficult for users to gain insights quickly. This affected used adoption.
Example: The default selection for the ‘Quarter’ prompt was supposed to be the current quarter but it was hardcoded by the report developer to a specific quarter.
Example : A set of canned reports were developed for a new BI project. When data accuracy tests were done comparing the report data with the output of equivalent queries in the source system, it was found that more than 50% of them failed the testing. Upon further investigation, several ETL and BI tool modelling issues were discovered by the development team.
Example: One of the prompt for the report was not applied to the drill down report when user navigated to it from the summary report. As a result, the amounts did not match between the summary and drill down report.
Example: The report did not have any default prompt selections and the performance of the report was extremely slow when no prompts (filters) were applied. It not only caused bad user experience but also unnecessary load on the database because users often stopped execution of the report from UI without getting any value from it.
Example: Although the BI tool supported both Firefox and IE, the reports were very slow in IE because of the difference in the image caching features of the corresponding browsers.
BI tools such as OBIEE and Business Objects empower the business users by providing the capability to create their own reports without the help of a developer. These tools generate the database queries automatically for the reports based on the measure and dimension definitions defined on top of the physical data model. For OBIEE, this model is defined in the RPD while Business Objects stores the model in the form of an universe. Business users can select any combination of dimension and measure attributes available in the subject area to come up with their own adhoc report. From a testing perspective, this presents a huge challenge since the number of different combination of dimension and measures can be very large and impossible to test manually.
Subject areas contain related dimensions and measures that can be used for creating new reports on the fly. Verify that the subject areas follow the requirement / design specifications. Some of the items to check are :
Example : Multiple subject areas were created by different developers with different naming convensions which was confusing to the end users.
Verify that all the dimension attributes and measures are mapped properly to available database tables and columns. This can be done by creating adhoc reports by select all the attributes/measures from each folder. Run these reports and validate that the data is as expected.
Example: One of the measure had a typo in the database column name which caused errors when it was included in a report.
When a combination of dimension attributes and measures are added to a report, the database query generated includes joins between the tables involved. If the joins have issues, it usually results in errors.
Example 1 : When a new dimension table was added to the BI model, new joins were created with the fact tables. However, the join condition for an existing report got impacted because this new addition. As a result, the report did not return any results.
Example 2 : When OBIEE is unable to determine the join between two entities, it either throws an ODBC error or in some cases use ‘CAST AS NULL’ in the database query. In the later case, the report won’t throw any error but it won’t return any data either.
BI Validator comes with Subject Area test plan that can automatically generate hundreds or thousands of reports with different combination of dimension attributes and measures. When these reports are executed, any errors in the subject area can be easily identified.
Like any other web application, BI applications also have authentication and authorization security requirements. BI applications are often integrated with single sign-on or embedded with other transactional applications. It is important to test the security aspects of the BI application just like other web applications.
The objective of this test is to validate that the BI users have access to the BI reports, subject areas and dashboards is limited according to their access levels. Access to the reports is generally controlled by role based security in the BI tool.
Example: In a HR analytics application, access to salary and compensation reports are restricted to select users.
In this form of security, different users have access to a report but the data shown in the report is different based on the person running the report.
Example: In a CRM analytics application, all sales managers have access to a pipeline report but the data shown in the report is limited to the opportunity pipeline of his or her direct reports.
Single sign-on is often used as the authentication mechanism for BI applications in large enterprises. The objective of this testing is to ensure that users are able to access BI applications using their single sign-on access (or windows authentication).
BI Applications are sometimes embedded as part of other transaction system applications using a common authentication mechanism. This integration needs to be tested for different users.
BI tools make it easy to create new reports by automatically generating the database query dynamically based on a predefined BI Model. This presents a challenge from a regression testing standpoint because any change to the BI Model can potentially impact existing reports. Hence it is important to do a complete regression test of the existing reports and dashboards whenever there is an upgrade or change in the BI Model.
The primary focus of this test is to verify that the data shown on a report or dashboard page is same before and after the change (or upgrade). In case of the BI tool upgrade, it is possible that the look and feel of the report might change. By validating the data of the report, we can be sure that the report content is same.
Example : When a new dimension table was added to the BI model, new joins were created with the fact tables. However, the join condition for an existing report got impacted because of this new addition. As a result, the report did not return any results.
Verify that there is no change in the look and feel of the report.
Example: After a BI Tool upgrade, one of the chart in an existing report stopped showing in the UI.
Verify that all the prompts are available as expected and the list of values showing in the prompts match the expected values. Apply the prompts and verify that the reports shows data as expected.
Example : One of the filter condition was accidentally deleted from an existing report. As a result, the prompt values are not getting applied as filters to the report.
When data is changing in the underlying tables, it might be difficult to understand if the differences in the report data before and after an upgrade is because of a regression issue or data change. It is helpful to compare the database SQL query in such cases.
Example : A recent database upgrade adversely affected the query execution plans which resulted in performance issues for the reports.
Any upgrade or change in the system can cause regression in performance of existing reports. It is important to verify that the reports are still performing as expected.
Example : A recent database upgrade adversely affected the query execution plans which resulted in performance issues for the reports.
The BI security testing sections delves into various aspects of security testing. From a regression testing standpoint, the objective is to validate that the security is not adversely impacted.
Example : While migrating new report content to production, the BI administrator accidentally modified the security setting for existing HR reports. As a result, employee compensation reports were visible to all BI users.
BI Validator can be used to automate the regression testing of UI, data and performance of the reports and dashboards. Any differences found during the testing are shown visually to the user.
BI Stress testing is similar to testing of any web based application testing. The objective is to simulate concurrent users accessing reports with different prompts and understand the bottlenecks in the system.
A typical BI user will login to the system and navigate to reports (or dashboards), apply prompts and drills down to other reports. After the report is rendered, the BI User reviews the data for a certain time called think time. The BI user eventually logs out of the system. The first step in conducting a stress test is to identify a list of most commonly used reports or dashboards for the testing.
Conducting a stress test requires simulation of the above BI user behaviour concurrently for different user loads. So it is important to have a list of different user logins for the stress test. When executing the reports, each user can pick a different set of prompt values for the same report. The stress test should be able to randomize the prompt selection for each user so as generate a more realistic behaviour.
Most of the BI tools support a caching mechanism to improve the performance of the reports. Database queries and the data blocks used to generate the results are also cached in databases. The question that frequently comes up whether cache should turned off for stress testing. While turning off caching is a good way to understand system bottlenecks, our recommendation is to perform the stress test with the caching turned on because it is more closer to what a production system would look like. The following points should be kept in mind to reduce the impact of caching :
Stress testing is an iterative process. When the first round of stress test is conducted, a particular component of the system (eg. database cache) might max out and bring down the response times. Once this bottleneck has been addressed another round of stress test might find another bottleneck. This process needs to be continued till the target concurrent user load and report performance SLAs are met.
There are several performance data points that need to captured while running a stress test:
Stress testing is one of the key features of BI Validator. Since BI Validator integrates with the BI tool metadata (catalog), user can execute a stress test without writing a single line of code.
Get a Competitive Advantage with BI Validator. Download your 30 day free trial now.
Try BI Validator free for 30 days or contact us for a demo.