Public Sector / Modern BI

Large City and County Government Modernizes Aging Mainframe Data Management System Using Power BI

Featured Image_SB_Cit Gov2


When a large city and county government sought to modernize their aging mainframe data management system, they identified the safe and secure handling of private, sensitive citizen data as a top priority. Our client, the Department of Justice for the municipality and county, manages the data of nearly 1 million community members. This highly sensitive data is the lifeblood of city and county operations spanning police, sheriff, public defender, courts, district attorney, and adult/juvenile probation, among others.

The Challenge

Like many organizations seeking to modernize their data estate, the Department of Justice wanted to update its aging mainframe reporting system with a state-of-the-art data management and reporting solution. Power BI met their needs for flexible data sharing as well as the use of Azure Government Cloud for secure management of data.

“Power BI offers a wide range of options to meet the demands of data consumers, but sometimes there are so many options that it is easy to lose focus on what is core to a particular client. That’s the situation we found here, and it isn’t just this particular client, it’s anyone really that has the need to conform to specific process constraints.” explains Kirtis Carpenter, Solution Architect for BlueGranite.

The benefits of a highly flexible system like Power BI also need to work within the regulatory restrictions around data access and sharing often found within Departments of Justice. Some software systems cannot adapt to required processes – and instead may need the processes to change. A software system that cannot change based on the regulatory requirements may cause frustration or delays in project implementation. While sometimes processes do need to change and a software implementation provides that impetus, the implementation itself should not require a change.

The Solution

“Fundamentally, we needed to configure Power BI to best leverage its flexibility where that was desired while constraining elements of that flexibility where it was not desired.” continues Kirtis. “We knew that in order to solve this challenge, we would need a detailed understanding of usage patterns across the environment and knowledge of how those patterns mapped to both the capabilities of Power BI and to constraints on the different processes.”

First, BlueGranite started with an inventory of the features provided within Power BI and the methods available within the platform to extend or constrain these features to different users. “Fortunately, we had both a broad and deep understanding of what Power BI can provide based on our long experience with the product” recounts Kirtis. “We were able to quickly begin real conversations about the different needs for data and reports by users across the department. Because our feature lists were comprehensive, we were able to clarify which features for certain cases we wanted to constrain in those same conversations – like exporting of reports containing sensitive data.”

A Place for Everything…

Once information about the different usage patterns across the department had been collected, BlueGranite worked with the client team to identify a set of roles that would meet the needs of users. The roles considered both the needs of each type of user, as well as the controls available in Power BI to either extend or constrain features from that role. What emerged was a set of standards for how to best manage Power BI to ensure that each user had access to the appropriate features to carry out their duties, while constraining features that, although quite useful in some other context, were not desirable to maintain privacy and security of sensitive information.

“Was there tension about lack of access to certain data or certain features in some circumstances? Yes, there was, but to manage that tension, our project sponsor had established strong relationships with the users before we even arrived. Working in a regulated environment, they were already accustomed to working in a way that upheld concern for the big picture. We were able to rely on those relationships, which was a big help.” recalls Kirtis.

Armed with the knowledge gleaned from analysis of the users’ needs and unwavering support from the community of users, BlueGranite devised custom configurations of the Power BI platform for each defined role. Each configuration spanned many key capabilities of Power BI:

  • Tenant Settings – platform-level settings that enable or disable features for specific groups of users
  • Workspaces – spaces containing datasets, reports, and associated access configurations of different levels of access to the space’s content
  • Datasources – conduits for retrieving information from operational systems
  • Dataflows – processes that read data from operational systems into Power BI
  • Datasets – data organized for use in reporting and analysis
  • Workbooks – spreadsheet documents managed within Power BI
  • Row-Level Security – data security that constrains access to specific data based on context such as user, time of day, place of access, or other criteria
  • Reports – data listings and interactive visualizations for data exploration
  • Apps – collections of reports targeted for use by a specific group of users
  • Dashboards – Collections of key visualizations that bring together what is most important across many detailed reports
  • Gateways – components to transfer data from on-premises data sources to Power BI with high security and detailed monitoring

The collection of standards and corresponding platform configurations positioned the client to best leverage Power BI to meet their needs for data delivery for core processes while maintaining the privacy and security of sensitive data. “Data driven government is an exciting prospect where decisions based on evidence are made and ultimately result in better service delivery to the community. That said, complying with regulations and being sensitive to keeping data private where needed is critical. The solution we implemented here shows that with Power BI, that balance is possible,” said Sam Edelstein, senior consultant with BlueGranite and former municipal Chief Data Officer. 

…and Everything in its Place

“As we were establishing the standards to take care of sensitive data, we were also building a framework to monitor the Power BI environment” recounts Kirtis. “Our client was subject to periodic audits of who accessed what and, maybe more importantly, whether unintended access occurred due to gaps either in our standards or in the application of them.”

In tandem with ensuring that privacy and security of sensitive data is maintained, regulatory statute requires that access events associated with the data be monitored and logged so that they can be audited to see what access did happen and demonstrate that inappropriate access did not happen. These records have specific retention requirements and must be made available to state and federal agencies responsible for oversight of the city and county.

BlueGranite worked with the client team to identify the specific information that was to be captured from the Power BI environment. This included specific events:

  1. Successful and unsuccessful system log-on attempts
  2. Sharing / viewing / printing / exporting of reports
  3. Update of apps, reports, datasets, dataflows, workbooks, and dashboards
  4. Any and all access to the audit log information
And for each event:
  1. Date and time of the event
  2. Where the event occurred in the system
  3. Type of the event
  4. Subject (user and/or target) of the event
  5. Outcome (success or failure) of the event

“Fortunately, Power BI has robust monitoring and tracking mechanisms that capture most of what we needed” recalls Kirtis. “Much of the information was readily available through the out-of-the-box Power BI monitoring logs. Another smaller amount was available with a little effort. Due to the close alignment between the client’s need and our previous experience, we were able to quick start much of the solution from BlueGranite’s Catalyst for Modern BI.”

To meet the client’s need for collection and retention of the monitoring information, BlueGranite designed an automated process to gather the information from applicable sources and store the information in a purpose-built, secure database. In addition, custom reports were created to enable access to the information to support the anticipated periodic audits from agencies responsible for oversight. “Since the audit reports were themselves built in Power BI, we were able to meet the requirement to monitor their usage with no additional effort.” explains Kirtis.


BlueGranite’s custom solution positioned the client to demonstrate that the approach to maintaining the privacy and security of sensitive citizen data met regulatory requirements. “As regulatory requirements continue to evolve, a solution that provides up to date and accurate data and necessary monitoring is critical for internal and external audits. That’s why we have captured our best experiences in meeting these challenges into a BlueGranite Catalyst framework that helps our clients to make it easier to adapt to these changes.” explains Mike Depoian, BlueGranite VP Business Development.

The Results

In a very short time, BlueGranite was able to help this city and county Department of Justice position itself for success in maintaining privacy and security of sensitive data for its nearly one million community members. Working together, we were able to quickly identify the core elements for a secure Power BI configuration, elaborate standards consistent with regulatory statute, enable meaningful monitoring of the environment, and prepare for anticipated auditing by state and local agencies responsible for oversight.

Interested in learning more about how BlueGranite accelerates Power BI Deployment and Adoption? Contact us today.


Kickstart Your Digital Transformation With BlueGranite's Business Intelligence Solutions