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. 

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: 

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:

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.

 

Power BI vs. Tableau vs. Qlik: Which BI Tool is Best?

“Just give me the data.” When Kenway Consulting engages in a Business Intelligence (BI) project, many of them begin with that simple phrase— “Just give me the data.” Organizations want their data from various source systems in the hands of their power users. Doing so allows them to leverage the industry expertise and analytical mindsets for which they hired these resources. To maximize our value during a BI project, we believe in getting our clients the data that addresses their highest impact business questions early in the data discovery phase and then iteratively developing it in an in-memory data visualization tool.

We use in-memory analytics and data visualization tools because they allow:

However, just as no two clients’ needs are the same, we have learned that we cannot simply pick one tool to address every engagement. In an effort to best serve our clients, Kenway recently undertook a hands-on research project to vet Power BI vs Tableau vs Qlik.

Power BI vs. Tableau vs. Qlik: Our Research

Here is how it worked. We built a report in Qlik Sense, and used it to provide a benchmark against two major competitors: Microsoft Power BI and Tableau so we could compare Power BI vs Tableau vs Qlik. We reviewed the products on their ability to fulfill a few of the common use cases we have seen with our clients:

Before we begin our intergalactic adventure in data, here is some background on the exercise:

So let’s compare Power BI vs Tableau vs Qlik!

Data Extraction

Directly importing our data files using all three tools was quite easy. They all had user-friendly data loading wizards that allow you to quickly find files on your hard drive, make some minor manipulations, and incorporate them into your application.

The most striking difference was the number of data sources available via the versions we used. Power BI Desktop led the way in this category—out of the box, it allows users to utilize the wizard to extract from various file structures, databases, online services, and other applications. Qlik Sense also allows for a large spectrum of data sources to be incorporated; however, it requires a bit more technical savvy and/or searching to do so. Tableau Public limits users to local files, OData connections, and the Azure Marketplace DataMarket. However, if you choose to upgrade to the Professional Version, you get access to the same breath of sources as above and out-of-the-box connectivity as Power BI.

Outside of using the data loading wizards, Qlik Sense and Power BI provided much more robust scripting languages than Tableau. Qlik Sense’s data load editing language resembles SQL, a language familiar to many people with database experience. Power BI utilizes a language called Power Query. It is similar to F#, an object-oriented coding language. Tableau’s data loader allows users to make minor transformations for a loaded dataset (adding calculated values, grouping values, defining joins between tables, etc.); however, its lack of a coding language limits the number of tasks you can accomplish. For most use cases, the data will have to be prepared at the source level (e.g. modifying the files, creating views and/or tables in the desired model, etc.).

Once the data was loaded into the applications, Qlik Sense is able to differentiate itself from the other two products by the final data model it is able to utilize. Qlik’s associative data model allows Qlik Sense to string together connections between each table with every other table in the data model. This allows users to develop unique analyses across seemingly disparate data tables. While Tableau and Power BI are also able to bring in multiple data sets and data sources into their models, as users add on varying layers of complexity to the data model, they must also be more cognizant of the impacts on the data model.

For more information around each application’s connectivity, scripting, data load times, data compression abilities, and data modeling strengths and weaknesses, please see our full Data Wars Whitepaper.

Data Loading Breakdown

Executive Dashboard

Not surprisingly, all three of the tools were able to address our baseline reporting case—the Executive Dashboard.

As you can see, each tool was able to make a polished, user-friendly dashboard. Users are able to make line charts, scatter plots, and bar charts easily and can enhance them by adding filters. Furthermore, each of them supports a community of custom developed add-ons. The one we used here is by our friends at Tableau Augmented Analytics, formerly Narrative Science, (denoted by their logo). They have developed an add-on for Qlik Sense, Power BI, and Tableau that creates text summaries of your visualizations.

When it comes to Power BI vs Tableau vs Qlik from a default visualization standpoint, Tableau and Power BI came with more visualization types than Qlik Sense. While utilizing Qlik’s marketplace and customizing its standard visualizations allows Qlik Sense to make up some ground, this could be overly burdensome for less technical audiences.

Ultimately, we give a slight edge to Tableau in the visualization creation and organization space—the application’s interface has users create objects in separate tabs and then consolidate them into a single dashboard using a drag and drop design.

Tableau and Power BI also have an advantage when it comes to data manipulation on the visualization layer. They provide the user with wizards on the visualization layer to group fields, create hierarchies within fields, apply rules to fields, and create auto-filters for fields. The uses for these can range from making calendar fields (month, quarter, year, etc.) to developing drill down logic.

If users are embarking upon data discovery exercises, Qlik Sense’ white-green-gray filter functionality differentiates it from the other two. The white-green-gray color pattern defines whether a field is included in the current set, directly chosen for the current set, or excluded from the current set, respectively. This is useful in highlighting items like missed opportunities.

For further details around how the tools recognize field types (dates, locations, etc.), allow for heat map creation, enable users to build custom fields, and facilitate data discovers, please read our Data Wars Whitepaper.

Executive Dashboard Development

Customer Segmentation

With the basic use-cases covered, we wanted to see which tool handled some of our more complex business needs. The first that we looked into was customer segmentation. Many of our clients look to group their customers based on dynamic, automatically updated business rules. As this dataset was sales data, we decided to try and group them using the following:

Impressively, all of the tools were able to accomplish this segmentation. We used Qlik Sense’s and Power BI’s aforementioned scripting languages to develop these into the data model. For Tableau, we were able to string together multiple custom fields in the visualization layer to develop the needed segmentations.

Flows

Another key transformation in which our clients have found value is flows. This is used in customer service routing, order fulfillment, customer purchase pattern analysis, and other examples. Because of the ability to create custom scripts in Qlik Sense, we are able to recreate the logic for these. While we were unable to accomplish this with Power BI, we believe it could have been re-created with more time. Tableau would require the data to be prepared outside of the tool, likely in the source system.

For more information around how customer segmentations and flows were incorporated into the tools, please see our full Data Wars Whitepaper.

Summary

Here’s what we learned comparing Power BI vs Tableau vs Qlik:

Enjoyed our journey, we hope you did—we certainly learned a lot and got to geek out a little. Stay tuned for more information as these tools evolve and shift and new tools are added to the in-memory analytics ecosystem. Maybe we'll compare Power BI vs Tableau vs Qlik again. Or perhaps a new data visualization tool with in-memory analytics will arise.

Want to learn more about Kenway’s experience with Business Intelligence and data visualization tools? Drop us a line at [email protected].

 

Excuse Me, Can I Power BI your KPI'S?

On March 13, 2018, Kenway unveiled four new corporate strategies, one of which is something we call “Company Performance Transparency.” Up until that point, Kenway’s core strategies focused on “hiring smart people” and “being good and being honest.” Principles we still live by, but ones that are not exactly specific or measurable KPI's.

Most companies have some sort of performance strategy, as well as Key Performance Indicators (KPI's) that align. Terms like “Net Promoter Score,” “Conversion Rate,” and “Average Customer Profitability” are commonplace. If you read Sarah Welch’s Net Promoter Score (NPS) blog or Tim Olson’s case study on the subject, you know Kenway has helped clients develop, choose and report on KPI's. Well…guess what our Managing Director and CEO asked us to do?

The following is a summary of the steps we took to get started with our internal metrics and KPI reporting process, which is still a work in process.

STEP 1 – Define Your Metrics

We created a Metrics Catalog app using a Microsoft SharePoint List (i.e., a point-and-click database of metrics) to help ensure there was a “single source of truth” for metric names, definitions, and other metrics metadata (e.g., owner, category, audience, etc.).

Vocabulary is important when it comes to metrics. Before you start defining, make sure you understand the difference between a measure, a metric, a KPI and a dimension. For example, Revenue Growth by Industry might be a KPI that consists of the metric, “Revenue Growth,” and the dimension, “Industry.” (h/t Jonathan Taylor for a great article on the vernacular.)

Example KPI's

STEP 2 – Identify Possible Quick Wins and Prototype Them

Metrics definition is a labor-intensive and seemingly never-ending effort, so it’s important to avoid falling into the “paralysis by analysis” trap. Take a page from Design Thinking, Lean Startup and Nike … just do it! Don’t try to get everything perfect the first time around - it’s a prototype.

We identified metrics where definitions were complete (don’t skip the definitions step) and, we felt, were ready to be turned into prototyped reports or dashboards. The result was the dashboard built using Microsoft’s Power BI tool. This dashboard allows us to analyze Kenway Consulting’s hours over time (the X-axis) and by industry (the colors).

STEP 3 – Operationalize Your Prototype for the Long Term

The next step involves turning your prototype into a product or app that can live on for however long you need it to. We’re not there yet for the metric shown above, but the entire process and approach we follow for these types of projects is illustrated below. As you can see, we are on the “Gather feedback” step.

Do you have feedback? We’d love to hear it! Email us at [email protected] or find us on LinkedIn.

Defining KPI's

 

What-If? Understanding Uncertainty with Power BI

With the right tools, incorporating What-If analysis into existing reporting is quite straightforward. Modeling capabilities can be married with dynamic visuals, giving the end user even more power and flexibility when viewing their organization’s data. Tools such as Power BI, Microsoft Excel or Google Sheets can help your team not only answer the question of “What happened?” but also answer the question “What could happen?”

In the everchanging environment that businesses are being pushed to operate in, having these answers is crucial. Read on to learn more about how you can begin to understand uncertainty with Power BI.

WHAT IS POWER BI? 

Power BI is Microsoft’s offering in the crowded business intelligence space. While many of its competitors are well established (Tableau, Qlik, etc.), Power BI holds its own in terms of capabilities, and has the added benefit of being fully integrated within Microsoft’s platform. In Gartner’s 2022 rankings of analytics and business intelligence platforms, Microsoft continues to be in the position that is furthest along for Completeness of Vision and the highest in the Ability to Execute within the Leaders quadrant.

power bi 2022

As described by Gartner, the chief appeal of Power BI is its ubiquity. Gartner states, “many large organizations already own Power BI through enterprise software agreements,” and the familiarity many users have with other Microsoft products (e.g., Excel) leads to a short learning curve with Power BI.

When it comes to core functionality, Power BI is on par with the leaders. While its visuals might not be as polished as Tableau’s, they are intuitive and appealing. Similarly, while its data transformation capability might not be as robust as Qlik Sense’s, it has an ETL capability and can therefore deliver on the requirements of the vast majority of analytics projects.

HOW CAN WHAT-IF PARAMETERS BE USED FOR REVENUE FORECASTING? 

At Kenway, we have found What-If parameters immensely helpful with the forecasting process. Having built out our internal reporting suite in dynamic Power BI dashboards, we can rapidly pivot from viewing best-case and worst-case scenarios when planning for the next 6-12 months.

For instance, our revenue forecasting is built bottom-up from our internal data. This data is based on deals we have already won and entered into our ERP system. While this is very accurate for the near term, forecasting further into the future can be difficult as a larger percentage of those deals haven’t been won yet and therefore do not appear in the ERP system. To better understand this component, we can layer in data from our CRM pipeline, but this brings uncertainty with it since we must estimate how likely a project is to be won.

Read more about we’ve helped our clients achieve success with Power BI in this case study.

HOW TO LEVERAGE THE WHAT-IF ANALYSIS

Enter the What-If parameter. Though we have a good idea of how many projects we might win based on historical trends, we can use What-If parameters to explore how our revenue forecasts change with different future project mixes.

For even more flexibility, we can use multiple What-If parameters at once. In this example, number of projects, hours per project, and rate per project are all configurable parameters. The product of these three values is what gets added to the revenue forecast but, because of the Power BI functionality, there’s no limit to how intricate you can get with the calculations. In the below video, see how changing all three of these values alters the overall revenue forecast.

In the video below, see how a project planner can swap resources, hours per resource, and project bill rate to rapidly fine tune the staffing mix for a project.

This example shows how an organization can utilize What-If parameters to forecast at the macro level, and it’s not hard to imagine how the same concept could apply at a more micro level on an individual project basis. A common challenge Kenway faces as a consulting firm is optimally staffing our projects. When a project is won, we generally know how many hours it will take and what bill rate the client will pay. We must then decide how to staff the project, so the work is completed on time and, if possible, under budget. By setting up What-If parameters based on hours each resource on a project works, we can combine our internal data with these results to fine tune the best staffing plan to move forward.

VALUE OF WHAT-IF ANALYSIS AND MODELING

The advantages of moving away from static reports toward business intelligence tools, like Power BI, extensively documented elsewhere. What many organizations are seeking is action - how do they take the next step and get more insight out of the reports and dashboards that these applications provide to drive actual change?

What-If analysis can deliver exactly that. Often, even organizations with mature data visualization capabilities keep their decision modeling separate from their dynamic reporting. They tend to use tools like Power BI to create interactive reporting that displays data trends that are easy to filter and drill into, but to model out different potential scenarios they still resort to classic modeling in Excel. 

There are many tools that are capable of creating these What If scenarios including tools, you may already have such as Microsoft Excel and Google Sheets. Below, we’ll go into detail on how to use a particular tool, Power BI, in creating What If Scenarios. 

HOW TO CREATE A WHAT-IF SLICER IN POWER BI

So how exactly does a What-If analysis work in Power BI? Fortunately, it’s almost as simple as clicking a button. Specifically, the “New parameter” button on the Modeling tab, helpfully labeled with “What if.”

1. Leverage the “New parameter” tool in excel:

 

2. Specify Attributes of the Parameter:

Clicking this button brings up a window where you can specify the attributes of the parameter. Along with a name, you can provide the data range of available options to select. Choosing this data range is the key to how the parameter works. The end user will still view the parameter as a truly open-ended What-If question, but Power BI will treat their selection as if they simply filtered the underlying mini dataset of values. Indeed, the input window even has a checkbox that lets you add a slicer to the page upon creating the What-If parameter. This lets the user choose the parameter’s value using Power BI’s traditional slicer visual.

With this slicer, the user can either enter in a value in the free response box or drag the slicer to incrementally change the parameter’s value. The increment you specify in the setup window will determine the sensitivity of the slider, so if you want users to choose between a wide range of data, use a larger increment.

3. Create Calculations

For the developer, the selected parameter value can then be incorporated into calculations so that metrics change dynamically along with the user’s selection. As part of the parameter creation, Power BI automatically creates a table with the data range of values along with a measure that returns the user’s selected value (if you’re familiar with DAX, it does this by leveraging DAX’s “SELECTEDVALUE” function).

Since it’s a measure, it can be easily added into any existing DAX calculation in whatever way makes the most sense for that parameter. For example, if the parameter represents a percent change then it might multiply an existing calculation. If it represents a nominal change, it might just be added to an existing measure.

4. Continuing to Optimize 

Leveraging the actions in steps 1-3 will allow you to reach insights quicker through the use of custom parameters that fit your company’s unique needs. Continuing to optimize from here to ensure your parameters stay up-to-date will help your team realize continued success.

 

HOW KENWAY CAN HELP

Have you been asking more of your data? Do you ever look at reports or dashboards and wonder, “What if….?” If so, Kenway can help. If you want to know how it would work in your organization, read more here or reach out to us today. 

 

COVID-19 Interactive Report

As of March 11, 2020, WHO officially declared COVID-19 (Coronavirus) a pandemic. Amidst this global outbreak, people are bound to experience feelings of fear and anxiety. We at Kenway believe that in circumstances like these, the more information the better, which is why we have developed this report. The report below provides additional information on confirmed cases, recoveries, and deaths, and gives you the ability to learn more about latest COVID-19 statistics across provinces, states, countries that are closest/most relevant to you. Please keep in mind that this dataset is sourced from a GitHub data repository maintained by Johns Hopkins University and that it refreshes every morning at 5 am central time. Over the next few weeks, we will further iterate to build additional features and visuals in this report, if you have any questions or suggestions please reach out to us at [email protected].

 

Lack of Insight = Increased Frustration

When work becomes frustrating, it can often become all consuming. We’ve all been there. Whether it’s a tight deadline, a difficult client, or a complex deliverable that isn’t going right, the frustration can take over and often lead to decreased productivity.  A few years ago,  that was the situation in my household.

My husband is in medical sales. Like most jobs, he is dependent on data to do his job effectively. At the time, his company did all their sales reporting in Excel. I could often hear him swearing at his computer, because the massive Excel document he needed to access would not open. When it did open, it was extremely slow to navigate, the data was static and filled with errors, and it provided him with very little insight into what was happening in his territory and where he had opportunity for growth. This lack of insight was causing him great frustration.

For those of you familiar with Kenway’s “Why” of “To Help and Be Helped,” you know that we are wired to jump in and solve problems. This scenario was no different. One night after dinner, I sat down with my husband, connected his massive Excel spreadsheet to my personal favorite data analytics platform, i.e. Qlik Sense, and built him some dashboards. Suddenly, he had the insight he was craving, and his frustration quickly turned to excitement.

He shared the prototype with his colleagues, his bosses, and his bosses’ boss. Eventually, my husband’s company asked Kenway to help them build a true reporting solution. Kenway used its iterative approach to Information Insight to help their Sales Organization do the following:

Define Requirements

Kenway spent time with the Sales Organization helping them understand what data was available to them, identify what types of information would provide them insight, and document a set of clear business requirements.

Data Cleansing

The Sales Organization received sales tracing from a variety of distributors of medical supplies. There was little to no consistency in how the distributors formatted the names and addresses of customers, making it nearly impossible to identify a “true customer” and their associated sales. Kenway helped develop a master data management solution to clean the data and create a mapping of “true customers” for the Sales Organization.

Business Intelligence

Using the defined business requirements and the newly cleansed data, Kenway built sales dashboards in an interactive business intelligence application using Qlik Sense to show a holistic view of sales, trends and opportunities.

Predictive Analytics

Kenway leveraged an external data set to define sales potential based on number of surgeries at hospitals across the country. This data was used to predict how much of each product a hospital would buy, if the hospital was buying solely from this Sales Organization. That data was married up with actual sales to help set a strategy and identify opportunities for growth.

After successfully using Qlik Sense for the past year, the Sales Organization was told that their entire company was being forced to migrate to Power BI. Not surprisingly, this concept of change made them uneasy. The level of insight the Qlik Sense application provided was extremely valuable and had led to increased sales. Would Power BI provide the same?

As discussed in our “Data Wars” blog post, there are plenty of business intelligence tools from which to choose, and they are not one size fits all. The key to success is understanding the business requirements and ensuring the data is well formed.

Because Kenway and this Sales Organization had taken the appropriate amount of time to document the business requirements and ensure the data was well formed, it was a fairly easy exercise to determine whether a migration from Qlik Sense to Power BI would meet all requirements. With proper change management, the team was able to smoothly migrate to Power BI and continue to get valuable insight from their new business intelligence tool.

Do you need help unleashing the power of your sales data, or determining which application is best to do it? Kenway can use its Information Insight expertise to help. Reach out to us at [email protected] to learn more.

 

Data Wars: White Paper on In-Memory Reporting Tools

“Just give me the data.” When Kenway Consulting engages in a Business Intelligence (BI) project, many of them begin with that simple phrase— “Just give me the data.” Organizations want their data from various source systems in the hands of their power users. Doing so allows them to leverage the industry expertise and analytical mindsets for which they hired these resources. To maximize our value during a BI project, we believe in getting our clients the data that addresses their highest impact business questions early in the data discovery phase and then iteratively developing it in an in-memory data visualization tool...