Introduction

Financial services companies are often required to submit detailed reports to regulators, encompassing everything from financial statements to risk assessments. These reports must be accurate, timely, and in compliance with specific regulatory standards. Microsoft Power BI enables organizations to create dynamic reports, pulling data from various sources. However, the challenge lies in ensuring that these reports are error-free and fully compliant with regulatory standards. Wiiisdom for Power BI offers an analytics governance solution to ensure regulatory compliance.

Below are some common issues that occur with reporting and data analytics in the financial services industry, and how these can be addressed at scale with automated testing.

Check For Up-To-Date Refreshed Data

Regulated companies need to make sure the information they send to the auditors includes the most up-to-date refreshed data! Setting up tests to automatically check for the presence of the most up-to-date data is a breeze with Wiiisdom.

In the example below, we have selected a column “loan_date” in a financial table in our dataset, and set up a simple test to be sure that the most recent (maximum) date in the column was what we expected it to be. The best part—this test can be set up using a static or dynamic date. In the case of a dynamic date, a simple check could be performed to be sure today’s date was always present in your dataset.

TIP: make sure you set up this test to run after your database tables have been refreshed, and when the data is expected to be available in the dataset.

Example of setting up a test to check for the presence of the most up-to-date refreshed data.

Wiiisdom for Power BI will also test to see if there is data present or not in each dataset, such as in the example below:

Detect Any Unexpected Values

Sometimes new values can unexpectedly enter a financial report, wreak havoc for users, lead to a lot of questions for the report developer, and cause detrimental consequences when audited by regulators! You can make these unexpected questions a thing of the past by setting up automated testing on the values in the most critical columns in your report. In a financial report, these could take the form of:

• Fixed rate loan type
• Adjustable rate loan type
• Mortgage type

In the example below, we’ve set up a test to check to be sure that we don’t have any values in the mortgage_type column of our table that are unexpected. These could result in figures appearing in reports that are not associated with the expected sub-categories that a user would expect to see.

Pass and fail examples of testing for unexpected values in a specific column.

A test could also be set up to check for the values, and only the values, that would be expected in a given column.

Identify Business Rule Violations

Financial reporting and analytics will often contain various measures such as loans, revenues, expenses etc. Automated testing can be established to confirm that business rules pertaining to these measures have not been violated. As an example, an organization may have a business rule that a loan rate may not exceed a given threshold. If it does, either a business rule was violated or there is an issue related to data integrity. In either case, the report owner will likely want to know immediately if this occurs.

This can be accomplished by setting up a test such as the one below, that is checking to be sure business rules haven’t been violated on a specific column. A similar test could be set up to perform checks for other critical business or data rule violations around other key measures in your financial report.

Testing to ensure business rules haven’t been violated on a specific column.

Test for Proper Security Group Assignments

Financial reporting is often set up with row-level security (RLS) in place. When user-defined, this allows reports to display limited information based upon whomever is using the report. For example, a bank manager may only have access to data concerning their own branch, while the regional manager has access to data concerning the sector, crossing several bank branches.

Wiiisdom allows you to test to be sure the right data is appearing to the right users. As an example, Wiiisdom for Power BI has a feature that can be enabled (shown below) in the opening step of a testing pipeline to run the testing pipeline as a given user based on their security group assignment.

Schedule Tests & Alerts to Run Continuously

Setup tests to run at the time of day that makes the most sense. If your datasets are refreshed at 6AM daily, then perhaps schedule these tests to run at 6:15AM daily.

Imagine the power of being alerted to issues before you get to the office and being able to address them prior to your users discovering them. With Wiiisdom for Power BI, not only can you be alerted via email but also through Teams, Slack, Google Meet and Web Request (Jira, ServiceNow).

Schedule pipeline execution feature.

Email and communication channels alerts setup.

Mitigate Risks from Changes to Your Data Sources

If you find yourself changing the data source that your financial reporting and analytics use, you will want to test to be sure there are not any expected visual changes to your report.

Fortunately, Wiiisdom allows you to take a snapshot of all your reports before data source changes takes place, and then another snapshot after with our automated regression testing capabilities. You can then see:

• Which reports experienced a change
• What the changes were (highlighted in red as shown in the example below)

This can also be useful when managing through migrations or upgrades to the servers hosting your data. Think of these as instant quality checks and risk mitigation from unexpected changes!

Example of a regression test on a report before and after a data source change.

Have Power BI Reports You Can Trust

Having financial reports that are accurate and reliable are critical for helping to meet regulatory requirements. Wiiisdom for Power BI allows you to easily build and run comprehensive tests and document the results for accountability and trend analysis.

Come and meet the Wiiisdom team at booth #17 during the European Power Platform Conference to learn more about our analytics governance solutions or contact us via our website.

About the author:

Patrick Perrier, VP of Products at Wiiisdom

Patrick is a seasoned business intelligence expert with 20+ years of leadership experience in BI software vendors and U.K. consulting companies. Passionate about tackling complex technical and business challenges, Patrick has a strong background in analytics software products, including SAP BusinessObjects, Microsoft Power BI, and Tableau. Patrick’s journey has led him to Wiiisdom—as the VP of Products, Patricks plays a pivotal role in driving innovation and delivering cutting-edge solutions that build trust in data and analytics.