One of my customers had a specific requirement to monitor the usage of a slicer in their reports for compliance purposes. Adding to the challenge, their data model was stored using Import Mode, making it impossible to directly monitor queries from the database.
As of this writing, Power BI Audit Logs do not provide such granular detail as slicer selections. This prompted me to explore alternative approaches to fulfill this requirement.
Direct Query vs. Import Mode
When using Direct Query, database audit logs can typically provide information about the queries sent to the database, helping address such needs. However, many dashboards use Import Mode, which renders this approach inapplicable.
Luckily Power BI composite data models can use a hybrid approach that combines the benefits of both Direct Query and Import Mode.
The Hybrid Approach
The solution involved creating a hybrid data model in Power BI. This model integrates slicer information from a Direct Query source (with auditing enabled) and connects it to the existing Import Mode data model using a shared column already employed for reporting.
The primary goal was to build an auditing table that registers the following details:
- Timestamp: When the slicer selection was made.
- User: Who used the slicer.
- Selection Content: The value(s) selected in the slicer.
- Report Name: Power BI report where the slicer is being used
Components Used
Azure SQL Database
This database stores a table containing the slicer selections and dimension values to be audited. These values can be included in various Power BI data models.
Log Analytics Workspace
A Log Analytics Workspace is basically a hub in Azure for collecting and analyzing logs and metrics from all sorts of resources. We can configure the Auditing settings to be sent there for reporting, using the KQL language.
Implementation Steps
- Configure the Slicer
Add the slicer to your report using the table created in the Azure SQL Database. Set it up using Direct Query mode and connect it to the desired dimension for filtering (or the original dimension in the existing model). I added the report name as a comment on the query to be able to capture it from the Auditing
- Audit Report
Use a KQL (Kusto Query Language) query in the Log Analytics Workspace to extract auditing data. Leverage the “Export to Power BI” option to bring this data into a Power BI report for visualization.
This approach provides a seamless way to monitor slicer usage in Power BI, even when the primary data model is stored in Import Mode. By leveraging Azure and a hybrid model, you can fulfill detailed compliance requirements while maintaining a robust reporting infrastructure. You can also reuse this same dimension to secure multiple reports and report the slicer usage in one place.
