You have worked with Power BI to produce beautiful visualisations of your data. The Power is its simplicity, but you can encounter the following problems:
- Upgrade - Data model in the reporting tier limits alternative tools
- Security - Users have permission to share data publicly
- Audit - Source data changes lose your audit trail
- Duplication - Different users require the same report with bespoke data requiring duplicate reports
- Size - Your data model exceeds Power BI limits
This article describes 10 simple steps you can take to deliver an enterprise class business intelligence solution using Power BI.
1 Authentication
Power BI utilises Azure Active Directory (AAD) to authenticate users to the service. This provides a robust security mechanism out-of-the-box using an interface familiar to administrators.
Configure multi-factor authentication to the Power BI service via the Azure portal to provide an additional layer of security.
2 Workgroups
Security is of paramount concern when publishing company data to the public cloud. Office 365 groups should be used to only expose Power BI objects to relevant group members. You can take advantage of existing Office 365 groups within your organisation or create new workgroups from within the Power BI service.
OneDrive for Business can be used to provide a workgroup with seamless access to certain data sources including Excel and CSV files. This provides a unified security mechanism to authenticate group members to both the Power BI objects and source data. Updates to source data are automatically propagated to Power BI.
Options when creating a group are:
- Privacy
- Private - only group members can view content and a group administrator must authorise new group members
- Public - anyone within your organisation can add themselves to the group with no administrator authorisation required
- Edit
- Members can edit
- Member can only view
- Members - only individual accounts are supported (as of 30/03/2017), distribution lists within your organisation cannot be specified
After creating the new group, it is available in your group workspaces within the Power BI service. This list includes the special "My Workspace", a private publication location available to all users of the service. Power BI workgroups is a pro feature.
Create appropriate Office 365 groups to assign permissions to Power BI content.
3 Auditing
The ability to audit Power BI user actions is an invaluable tool to assist with security and compliance within your organisation. This can be turned on within the Power BI Admin Portal which is accessible from the settings cog:
Actions audited are:
- CreateDashboard
- EditDashboard
- DeleteDashboard
- PrintDashboard
- ShareDashboard
- ViewDashboard
- ExportTile
- DeleteReport
- ExportReport
- PrintReport
- PublishToWebReport
- ViewReport
- ExploreDataset
- DeleteDataset
- CreateOrgApp
- CreateGroup
- AddGroupMembers
- UpdatedAdminFeatureSwitch
- OptInForProTrial
Audit logs are visible within the Office 365 Admin Portal which allows filtering by date or user. You can also download as CSV to retain an audit trail for a period appropriate to your organisation. You can even consume the audit logs within Power BI itself to provide usage pattern visualisations of your dashboards and reports.
Turn on Power BI auditing and regularly review the logs.
4 Sharing
Dashboards you own can be shared with recipients inside or outside of your organisation. Sharing is a mechanism to provide read-only access to Power BI content.
An internal recipient will receive a Power BI notification and optionally an email including a bespoke message. The dashboard and associated Power BI objects will then be visible to the recipient in their Power BI environment. Sharing with external users is achieved via the same interface. People outside of your organisation (Office 365 tenant) consume the content in a different way. They receive an email with a link to the Power BI dashboard. They have to retain that link to access the Power BI content subsequently, the links are not saved within their Power BI environment.
The dashboard owner can permit internal recipients of a share the ability to subsequently share the content themselves. This option is not available when sharing externally.
If the content you share contains any pro features, the recipient also requires a Power BI pro licence to consume that content.
Share content internally to give colleagues read-only access. Share externally to provide outstanding data visualisations to customers without having to create any supporting infrastructure. Take advantage of the Power BI service, all the recipient requires is a Power BI login.
5 Content Packs
Power BI users have the ability to publish content packs. This pro feature allows the user to combine dashboards, reports and datasets as a single content pack. This individual entity is then published to allow their colleagues to share and collaborate.
Content packs are created via the Settings | Create content pack menu and the audience can be specific groups within your organisation or the entire company.
Your colleagues can then navigate to Get Data | My organisation | Get to view available content packs and import to their own workspace. They also have the option to save a copy that is fully editable.
Publish content packs to collaborate with colleagues. Co-workers can create an editable copy of the content pack to provide a starting point for their own publication without having to duplicate all your effort.
6 Azure Analysis Services
Deploying Azure Analysis Services or an alternative analytical layer will remove some of the issues described at the top of this article. The data model can be defined within this service, reserving Power BI for visualisation.
Data can be joined and further attributes added via DAX functions within the Azure Analysis Services tabular engine. This logic could be applied in Azure Analysis Services or directly within Power BI as both use the same xVelocity in-memory engine. The benefit of using Azure Analysis Services is that shared logic can be consumed by any reporting application your organisation uses in addition to Power BI. Specifying data and business logic within Power BI makes a future upgrade to the next great reporting solution more cumbersome.
Using Azure Analysis Services is a method to overcome Power BI size limitations. Power BI data models can have a maximum size of 1 GB with pro users allowed 10 GB of total space and free users constrained to 1 GB in total. Power BI can query certain data sources, including Azure Analysis Services, using DirectQuery. This connection method removes the upper bound size constraints as data is not imported to Power BI. However, DirectQuery also imposes some limitations on your solution that you should fully understand before implementing this method.
SQL Server Data Tools is the development tool for Analysis Services projects. This environment is fully integrated into Visual Studio, providing granular source code control capabilities not available with Power BI.
Decide where your data model will be deployed. Create the data model within Power BI or use an analytical tier such as Azure Analysis Services.
7 Data Warehouse
An audit trail is lost within your reporting solution if you query OLTP data directly and those sources are updated. If your business requirements dictate that historical point-in-time reporting is required then your solution should include a data warehouse.
A dimensional model should be designed including facts and dimensions. Slowly changing dimensions can be defined that track changes in attributes over time keeping that full audit trail.
If your preference is Platform as a Service within Azure, this dimensional model can be deployed to Azure SQL Database or Azure SQL Data Warehouse. Which platform you decide to use will depend on your resource requirements and budget. Azure SQL Data Warehouse includes a massively parallel processing architecture that can scale to handle the most demanding of workloads. Of course, this power comes with increased cost, so Azure SQL Database might be more appropriate for your needs.
The decision to include a Data Warehouse tier within your infrastructure should not be taken lightly. This requires investment in not only the additional services or infrastructure to host it, but in development time to design and deploy. Dimensional modelling is a specific skillset that requires a deep understanding of data warehousing concepts.
It is perfectly acceptable to deploy your data model within Power BI and report directly from OLTP data as long as the limitations are understood. New features are continually being added to Power BI such as Quick Measures within the April 2017 update. This feature allows you to add new measures to your data model without having to write DAX.
Understand the pros and cons for your organisation to including SQL Server Analysis Services and Data Warehouse tiers within your reporting infrastructure. Write a design document to share this information within your organisation so everyone fully understands what the final solution will be.
8 Pausing Azure Services
You have numerous options for deployment of the data warehouse and analytic tiers within your business intelligence infrastructure. Utilising the Platform as a Service offerings within the Microsoft Azure platform offer many valuable benefits:
- Faster speed to market with no delays in infrastructure procurement
- No capital expenditure for hardware
- Automatic patching
- High availability and disaster recovery built in options
- Scaling up and down via the Azure portal
- Data is available to be consumed by other services such as Azure Machine Learning for predictive analytics
One particular benefit I wish to mention here is the ability to pause compute for the Azure SQL Data Warehouse and Azure Analysis Services instances. This can be a great cost saving method and is an option we simply don't have on-premises with the traditional licensing model. An audit should be performed to ascertain when your reporting instances are required to be available and they should be paused at other times. This can be achieved via PowerShell or REST API keeping in mind the instance needs to be available to serve data if DirectQuery is part of your solution.
Pause Azure services when not in use to save cost.
9 Row Level Security
Row Level Security offers you the ability to horizontally partition data based on the user initiating the query. Bespoke data can be rendered to different users via the same dashboard or report. This technique avoids duplicating development effort removing the requirement to deploy multiple similar reporting solutions. Strict data governance can be applied to only expose appropriate data to each group of users. Row Level Security in Power BI is a pro feature.
An example might be a sales report that only exposes data to a user for their own sales. Deploying a report for each salesperson quickly becomes untenable and very cumbersome to maintain. Row Level Security allows you to create a single report avoiding the need to release additional functionality to multiple locations.
Row Level Security can be achieved within SQL Server by binding a table valued function to a table via a security policy. The function uses a session level attribute such as USER_NAME() or APP_NAME() to drive which rows are appropriate to be displayed in any particular session.
Alternatively, the same concept can be included within Analysis Services. Including DAX filters at the row level can horizontally partition the data.
Use Row Level Security to expose bespoke data to users via the same dashboard or report.
10 Power BI Admin
Add users to the Power BI Admin Role to grant them access to the Admin portal. The portal offers many Power BI security features that you should configure appropriately. Of particular interest is the option to remove the ability of users to share content externally. This assists governance, allowing you to control the exposure of company data outside of the organisation. Only trusted individuals can share externally following due diligence, internal quality control and testing.
Settings that can be configured via the Office 365 Admin Portal are:
- Share content to external users
- Export reports as PowerPoint presentations
- Export Data
- Print dashboards and reports
- Publish content packs to the entire organization
- Create template organizational content packs
- Use Analyse in Excel with on-premises datasets
Power BI admins do not have access to all Power BI content within the tenant. They are still required to be a member of the relevant Power BI workspace to view its contents.
Identify users within your organisation to administrate the Power BI environment and assign them the Power BI admin role. Apply data governance by specifying who can share and export data.
Summary
Power BI is a powerful cloud based data visualisation tool. It is very easy to start publishing your company metrics to the public cloud for analysis by interested parties. Use the techniques within this article to avoid some common problems and deliver an enterprise class solution.
Coeo has extensive experience of delivering successful Power BI solutions. If you feel Coeo could assist in designing a secure, high-performing and enterprise class Business Intelligence solution for your organisation, we would love to hear from you.
