Business Warehouse Oversight – Part II
By Bruce Johnson
Last month I introduced one part of analytics governance – the high level concept of providing business warehouse oversight to the content stored in a data warehouse, mart, or other analytical database. Key areas of business content oversight included: data mapping and profiling, meta data content, data acceptance, meta data acceptance, data quality assurance, and organizational alignment.
Continuing where we left off last month, this is part 2 of a 2 part series to examine the key roles the business plays in the oversight of both data content and usage. Whether you formally set up the various business data stewardship roles, or just assign resources or teams to oversee the whole solution, they need to be involved. For this part we will focus on oversight of the applications and solutions that provide users access to the data in the solution.
Much like we saw in content oversight, your business resources are the real experts behind how the data can and should be used. That doesn’t mean that every area that contributes data defines exactly who can use their data and how. That would be too overburdening and restrictive. This is about having limited business resources focused on the stewardship of the applications and tools that allow end-users leverage to access information. Ideally a team of power users that are good with tools, knowledgeable about data, and navigate your company well are the perfect people to put in this group. In most cases, this is not a full-time role, which is why having resources that actually use the data and the applications is very valuable. The key areas to map for business involvement in usage oversight include:
Solution Acceptance: During the project, you want to have your business oversight team involved enough in the project to understand requirements and participate in some fashion in system and integration testing. Once your technical solution to access the data has been through system and integration testing, it is time for the end-users to approve the solution for migration to production. However, this is also when the resources you have defined to accept all analytic applications can validate that the security, privacy, auditing, and controls are adequate and standard. Having guidelines and processes that all analytics should use for these will make your solutions more efficient and effective as well as provide consistency for the user and leadership overseeing the solution. Taking a little time up front to establish these processes will make all involvement more effective.
Controlling Access: In most organizations, you can count on having several, to many, different analytical solutions that cover different data or usage needs. Defining who can use the solutions you provide is a business function. IT resources typically maintain the tables and access controls that the system uses to authorize user access. But the business should be defining who is in that control table. As you develop more and more solutions over time, each will have its own distinct service it is trying to provide. Each may have different data content, different usage techniques and patterns, and even different technologies. Tying this level of access to the business ownership of the data helps you to ensure across your solutions that only the right people have access to each piece of data. Setting up a business led access approval body will provide a single source of ownership for making those decisions and thus will help you to become a data centric organization. That doesn’t mean every person has to go through an approval process, often this is done by role, group, or function. Setting up these steps takes the burden of trying to figure out who should use what applications off of the IT developers that you want working on building and extending your solutions.
Analyzing Access: It is one thing to control who has access to your analytics systems, but yet another to analyze that access. Analysis of current access can help you get the most out of your system. User satisfaction of analytical systems is probably the most critical facet to determining the success of your solution both upon implementation and in the years of usage that follow. Here are a few specific functions you should be periodically reviewing, together in a formal meeting with the business oversight and IT support team. In all of these cases, a few simple audit reports, a process, and a formal periodic meeting to review and discuss all of these aspects will provide sound controls and support for your solutions.
- Query Volumes: Understanding how many reports/queries are being run/accessed will help you understand the usage patterns. This can help you in seeing where to add indexes and summary tables (for tables where there is heavy volume on the same fields). It also helps you stay on top of the need for infrastructure projects in a factual way such that the additional hardware is truly needed. I have seen several solutions where users get very upset that their systems are not available at key hours (may or may not be during normal working hours). Understanding what times they use information the heaviest will help you develop SLA’s (service level agreements) that keep the system up and at optimal performance during key access periods
- Run Times: Understanding how long queries and reports are running will give you insight into the performance of your system. This can help you define when to pre-run reports vs. building on the fly. It can also provide you with insight into the effectiveness of your solution. Perhaps changes to your semantic layer are needed if ETL, reports, or queries run for significant amounts of time. Perhaps your ETL jobs are running extensively over, taking away access or response time from your users. Data models and physical database schemas are important factors in implementing a successful system, but the tweaking you provide after implementation to enhance performance can play a much greater role in overall user satisfaction.
- User Areas and Roles: Who is using the system and for accessing what information? In this section we are not looking at this data for security purposes, but for learning about usage patterns. Understanding who is depending on this information will help you know who to engage when you have upcoming releases, so there are no surprises. Who do you target for help in testing new releases of data or applications? Perhaps some of the highest volume users are in this group.
Auditing Usage: Of course, in addition to using monitoring to enhance performance, you need to have some auditing that tracks system usage and access. Almost every business in every industry needs to know who has access to their systems and how they are using them. Any industry with solutions that contain key financial data or numbers need to have a formal auditing process and communication mechanism that is controlled and overseen by corporate audit. Industries like healthcare and finance that contain a wealth of personal information are even accountable to federal regulations. I strongly encourage you to include your corporate audit department in the planning, setup, and ongoing oversight group. Working collaboratively with them, they can make sure that your processes live up to any requirements/standards and will certainly help to make sure process isn’t avoided in a way that would expose vulnerabilities. Also, make sure your IT resources are in the room and involved. Yes, they will provide the reports and data used to monitor, but they can also be key in providing details behind system anomalies and how access is used for technical IDs and jobs. Processes to cover auditing should contain:
Education plan or process for letting users know of any limitations or restrictions that are put on their access that require auditing
Formal definition of what to review, how to review it, how frequently, and who is involved as well as who owns and oversees
Periodic intervals for regular reporting of all access
Formal communications plan – who are the results shared with, what access anomalies require specific reporting and communication
Process for handling audit findings that require research or intervention
Access volumes by users, roles, areas
For organizations that have protected information, reports of access, queries, and access attempts that failed
Any specifically defined criteria – looking for queries that return only a few rows of confidential information – someone looking at details that perhaps they shouldn’t be.
The business, working together collaboratively with their IT counterparts, is a critical part of the implementation and management of any analytics effort, especially large or enterprise data warehouses. Setting up roles and processes to enable this management and support is best done as soon as you can. If you have a solution that has been in place for awhile but hasn’t had business oversight, now is the time to engage them. You will get better business support and engagement on projects, higher quality data, and most importantly, more business usage. This guidance should be used for a significant part of oversight of any analytical applications or solutions, but is not entirely comprehensive and certainly isn’t detailed. If you are setting up a large, formal governance process, this may provide areas that will help, but is not intended to be a comprehensive governance approach.
About the Author
Bruce has over 20 years of IT experience focused on data / application architecture, and IT management, mostly relating to Data Warehousing. His work spans the industries of healthcare, finance, travel, transportation, retailing, and other areas working formally as an IT architect, manager/director, and consultant. Bruce has successfully engaged business leadership in understanding the value of enterprise data management and establishing the backing and funding to build enterprise data architecture programs for large companies. He has taught classes to business and IT resources ranging from data modeling and ETL architecture to specific BI/ETL tools and subjects like “getting business value from BI tools”. He enjoys speaking at conferences and seminars on data delivery and data architectures. Bruce D. Johnson is the Managing director of Data Architecture, Strategy, and Governance for Recombinant Data (a healthcare solutions provider) and can be reached at firstname.lastname@example.org