Blog

Blog

June 19, 2024
15 minutes read
Information Insight

Power of BI: Enable Reporting to Monitor Key Compliance Metrics

Introduction

In today’s data-driven landscape, BI reporting is crucial for complying with increasingly stringent data privacy regulations. Publicity around security issues is gaining an increased level of attention. Enterprises must prioritize compliance with these mandates to avoid significant financial and reputational repercussions. Penalties for non-compliance can reach millions of dollars, with additional daily fines for ongoing violations. For corporations managing vast amounts of customer and/or employee data, achieving and maintaining compliance can be a complex undertaking. 

To ensure the ability to swiftly and accurately respond to data privacy requests to remain in  compliance with data privacy regulations and avoid security issues, organizations need to understand their data flows, the root cause of any internal failures (system, technology, process, etc.), and ensure timely rectification. This requires an enterprise-level reporting initiative that continuously updates to ensure the accuracy of data being stored, tracks data breaches, monitors system performance, and verifies that issues or fallouts are resolved within the mandated timeframes. In this blog, we will explore the significance of BI reporting and the role of business intelligence tools in facilitating data-driven decision-making.

Problem and Solution

Problem Statement: Our client’s extensive customer base exposes them to potential fines from the FCC for non-compliance with Customer PII (personally identifiable information) regulations. Non-compliance can result in hefty fines, jeopardizing the bottom line. Currently, they lack sufficient visibility into their data storage systems, making it difficult to access and ensure compliance in a timely manner. The challenge lies in gaining clear visibility into the client’s data storage systems. Without this enhanced observability, assessing and ensuring compliance becomes a guessing game, or sheer luck.

Solution: Kenway provided a mix of services to build a solution that uniquely met the needs of this organization, including Data Management, Data Integration, and Business Intelligence which delivered a Comprehensive Reporting Package, Business Process Assessment, and Data Flow Diagrams. Ultimately, Kenway worked to identify the appropriate backend source systems and engaged the database admins to ingest data into the BI reporting tool, Power BI. Kenway also wrote custom scripts to parse through a system fallout mailbox to load that data into the BI tool to reconcile against the backend data sources. The Kenway team delivered reliable real-time reporting to the client team with a dashboard to address their gaps, effectively enabling leadership to promptly identify finable offenses and correct within the mandated timeframes.

Optimizing BI Reporting with a Product Centric Approach

What happens when you’re dealing with a vast amount of client, customer, vendor, supplier, employee, and/or system data? At an enterprise level, these datasets can quickly become overwhelming. It is important to ensure all relevant data sources are identified to provide true compliance visibility. Let’s explore a few of the key considerations the Kenway team defined during the discovery stage for this solution. 

  • What are all the different data sources pertaining to the business requirement I need to solve?
  • Are these data sources on-prem or cloud hosted? How can I access them? Will I need to leverage something like a Power BI Cloud Gateway?
  • What business intelligence tool will work best for my situation: Power BI, Tableau, Qlik, Domo, or other?
  • What strategies should I deploy for loading the data in from the various data sources? How do I ensure security when loading my data?
  • How should I establish relationships in the data model?

After these discovery conversations, the team quickly understood that there were two primary data sources. The first data source captured historical customer elections on whether the customer allows the client the ability to use their data. The second data source captured system failures pertaining to the update of the customer election within the source system. Once Kenway was granted access to the underlying data sources, our assumptions were confirmed that the size of the datasets from which reporting would need to be enabled were extremely large. 

The client already had a Power BI Workspace stood up by an adjacent Data & Analytics team, existing Power BI Pro and Premium licenses, and positioned themselves as a Microsoft enterprise. The team knew from previous project experience that Power BI is one of the most powerful business intelligence tools on the market for gaining real-time visibility into FCC compliance, and similarly for FTC, FPC, and other agency mandates. With that in mind, Kenway selected Power BI to solve the problem statement. 

Next, the Kenway team facilitated requirements gathering sessions with the business stakeholders to further refine the BI reporting tasks ahead. With a clear understanding of the business needs, Kenway recommended the adoption of a Minimum Viable Product (MVP) approach. This product-centric mindset prioritized delivering the most valuable features first, while providing further product hardening in later sprint iterations.

For businesses with limited visibility and insight into their data, an MVP Analytics Solution, using a business intelligence tool like Power BI, can be transformative. By focusing on the core business problem at hand, teams can quickly establish reliable reporting solutions that provide immediate value to the business organization. This initial stage of success lays the foundation for iterative improvements and higher magnitudes of analysis in the future.

Through iterative development and feedback loop process, the initial MVP was continuously enhanced. New data sources or details were included in the report, new visuals were developed, performance was optimized, and additional KPIs were derived to enhance the overall value of the reporting product. Approaching the effort with a product mindset ensures the stakeholders are continuously receiving value, starting from day one.

To learn more about Kenway’s approach to Product Management, check out our Case Study here.

Enhancing BI Reporting Performance and Responsiveness 

A few months into solutioning, the team was able to build a powerful report in Power BI, empowering business users to explore the data and gain valuable insights. Users were able to view historical election records for any given customer, historical system fallouts, and identify whether those fallouts had been rectified in accordance with FCC thresholds. The client was a big step closer to ensuring the organization was compliant as they gained real-time visibility into the compliance status for over 6 million customers.

When working with large data sets, Power BI reporting in the web interface can experience noticeable latency and this was quickly identified as an impact during the testing phase. Kenway had to find a way to optimize the reports and identified several strategies to resolve the issues. 

Let’s dive into the various strategies that were deployed to improve responsiveness and performance: 

General Optimization

These are some of the easier things to start with. Kenway evaluated all loaded data tables in Power Query, inclusive of data loaded in from data warehouses and shared mailboxes. The team connected with the client to align on the following questions: 

  • Which of these attributes do you need to report on? 
  • Which attributes (columns) are not needed now? 
  • Which attributes (columns) will never be needed?

Once answered, Kenway dropped columns from the Power BI data model that were marked as not needed. One example of data dropped was the date/time columns which reduced the size of the Power BI file substantially and improved the responsiveness and performance of the reports in the web service. 

The Kenway team noted that this should generally be handled in the data warehouse upstream rather than in the PowerBI data model. However, conversations with the database administrators revealed that the primary upstream data source served as the source of truth across numerous business units and the database team expressed concerns with the analytical querying capacity and the potential to break the database entirely. Given this, the Kenway team ultimately decided to optimize downstream.

Ensure Security

Open Database Connectivity (ODBC) allows for Business Intelligence (BI) tools to seamlessly connect to any data source, regardless of its format or location. Think of it as a universal translator, or a middleman, that allows BI reporting tools (like Power BI) to connect with Data Lakes/Warehouses (ex. Oracle DB, Snowflake) and access the data stored within them. They play a key role in ensuring data flows between systems and applications are secure. Let’s investigate how they work.

  1. First, the application wanting to access the data (Power BI) will send a request through the ODBC driver manager indicating as such. 
  2. Next, the ODBC driver manager will identify the appropriate driver based on the target database. 
  3. Then, the ODBC driver will translate the application’s request into the data manipulation language (SQL) understood by the target database. 
  4. Finally, the database will process the translated request and return the requested data to the application (Power BI) via the ODBC driver.

Kenway leveraged ODBC drivers on the compliance solution to ensure data connectivity was secure, scalable, standardized, and most importantly, improved the performance of the reports in the web service. Here is a summary of the additional benefits that ODBC drivers provide:

  • Flexible & Scalable: Provides the ability to connect to a wide range of databases, given a compatible ODBC driver exists.
  • Standardized: Applications can connect to any database and retrieve data without having to be built to work with each individual database system.
  • Improved Performance: Data Loads can take a fraction of the time and development needs are reduced as custom code is not required for each database connection.

To learn more about using ODBC drivers and implementation tips, read more here.

Cloud Gateways

As the client needed to know compliance statuses and updates daily, the team needed to ensure incremental data refreshes were also secure in the Power BI web service. 

Cloud gateways provide a secure entry point that controls access to valuable cloud resources. Unlike traditional gateways that operate within a network, cloud gateways bridge the gap between on-premises infrastructure and the cloud.  This secure bridge ensures that only authorized data flows between on-premises databases and the Power BI web service, via the defined routing paths. 

Cloud gateways are essential for adhering to modern cloud security best practices. They provide centralized authentication and access control, allowing users to monitor traffic flow, application performance, and security metrics with ease. This enhanced security goes together with scalability, as cloud gateways can be easily scaled up or down based on the current traffic needs.

Gateways offer more than just secure access. They act as powerful control centers, governing how applications interact with backend services in the cloud. This translates to several key functionalities including versioning APIs, rate limiting requests, and implementing authentication and authorization.

By offering a centralized point of control, gateways streamline cloud service management and reduce administrative burden. Gateways also act as an additional layer of protection, constantly monitoring traffic flow and identifying potential external threats before they can harm critical systems.

Kenway collaborated with the client teams to correctly configure Cloud Gateways to automate scheduled refreshes in the web service, and ensure the client had the latest data in their view. 

To learn more about Gateways, check out the following page.

Conclusion

Throughout this solution walkthrough, we have explored how to navigate the complexities of data privacy regulations and explained how the lack of visibility into customer data can pose a significant challenge for compliance. We have delved into the power of Business Intelligence and what it can do for any organization, particularly in terms of enhancing BI reporting. Here are some key takeaways:

  1. Embrace the MVP mindset.
  2. Align with business needs.
  3. Map the current state data landscape.
  4. Choose the optimal BI tool.
  5. Optimize the data model for performance.
  6. Leverage Open Database Connectivity (ODBC) and Cloud Gateways for secure connectivity.

After partnering with Kenway, the organization had reliable data visibility for the first time to verify that they were compliant with FCC regulations. They received comprehensive data lineage documentation to streamline troubleshooting, allowing them to trace fallouts presented in the dashboard to their root cause and conduct swift investigations and rectifications. Moreover, the secure environment with daily data refreshes ensured they were always working with the most up-to-date information.

To learn more about Kenway’s expertise with helping clients become compliant to data privacy regulations, visit our Customer Data Compliance page. For additional information about Kenway’s Data & Analytics Practice, check out our Modern Data Enablement page.

How Can We Help?

REQUEST A CONSULTATION