In this series, learn how 3Cloud is helping a large local government with user and data security as they modernize operational reporting using Power BI.

3Cloud recently had the opportunity to work with a large local government to help enable modernization of their systems using Power BI to support everyday operations. Like many, they seek to realize the benefit of greater capability for lower cost that comes from modernization. This series provides a window into our experience and some details how Power BI can help local governments to realize their modernization goals.

3Cloud’s insights in this series come from experience with a county and city government with more than 30,000 reporting system users and numerous government agencies including Police, Sheriff, Public Defender, Superior Court, District Attorney, and Adult/Juvenile Probation.  Specifically, 3Cloud is engaged to help with the challenges of keeping data secure within and across agencies; securely developing and distributing sensitive data and reports; and monitoring and auditing Power BI usage to meet internal, state, and federal data protection requirements.

Role-Based Workspace Access

Managing access to Power BI content is a fundamental security requirement for any reporting solution. The primary avenue for distributing Power BI reports is through App Workspaces in the cloud-based Power BI Service. App Workspaces can hold many different pieces of content, which are often grouped around a department, team, or role. When medium to large government agencies can easily have tens of thousands of users it is impractical to manage access to these Workspaces for each individual user – which is why security groups are crucial. Using security groups to implement logical grouping of users, and nesting those groups appropriately, makes managing access to Power BI content possible and effective. When App Workspace content is grouped according to teams or departments, assigning access becomes an intuitive business decision.


When designing a data reporting solution, key questions arises around storing and connecting to the agency data. The size of the data, the current location of the data, ‘real-time’ requirements, and the agency’s perspective on cloud or on-premises technology are all factors when implementing a solution. DirectQuery is a data connection mode that creates a connection between Power BI and the data source, without importing and storing the data in the .pbix file itself. Instead, DirectQuery stores the metadata and connection details to issue queries directly against the data source whenever a user filters or interacts with a report. This connection mode allows the data in the reports to stay in sync with source systems and all data to stay in its source system without being stored elsewhere.

User Login Audit

As with all systems that manage sensitive citizen data, governmental agencies are required to track and capture system usage details to ensure that the security and integrity of sensitive data is maintained. Federal or internal audit requirements often require that system access be monitored – the most fundamental of which is user logins. Login activity is available through the Office 365 Management API, but that API also includes hundreds of other auditable activity in the entire Office 365 ecosystem. When looking for just user login activity, the Office 365 API can be too big to manage effectively. Rather than having scripts run for hours to collect the entire Office 365 audit log only to filter down to the tiny group of login activities, a better option is available. The Exchange Online Rest API has a Search-UnifiedAuditLog cmdlet which can call the API from a PowerShell script and return just the user login activity – a much more manageable subset of data.

Power BI Activity Audit

The user login activity by itself is insufficient to satisfy even the most basic auditing requirements. To really have an accurate picture of the who, what, when and where of what’s happening with the Power BI content, the Power BI Activity Log API is required. With the Power BI Activity Log, a simple cmdlet can pull a log of numerous different activities happening in the Power BI environment. These logs collect activities for the gateways, workspaces, datasets, reports, dashboards, apps, and record activities like logging in, adding, deleting, updating, printing, publishing, refreshing, and others. This, by itself, provides a detailed picture of what is happening in the Power BI environment. After pulling these logs with Powershell, the records can be stored in a long-term repository like a DB or file system. From there, developers can build reports on the data to measure user activity and overall platform engagement.

Learn More

Local government agencies face unique security challenges when looking to modernize their data and reporting systems.  For additional information and to learn more about 3Cloud’s recent experience working to help a large local government to navigate these challenges, reach out today.