Biz Data Partners, 6200 Stoneridge Mall Road, Suite 300, Pleasanton, CA 94588

Ph # (925) 214-0724, roncash@bizdatapartners.com

  • roncash

Power BI Implementation Best Practices

Introduction

Business Intelligence practitioners typically define data-savvy companies as those that benefit from the use of factual information to support decision making. We even describe certain organizations as having a “data culture.”

Whether at the organizational level, or at a departmental level, a data culture can positively alter a company’s ability to adapt and thrive. Data insights must not always be of enterprise scope to be far-reaching: small operational insights that can alter day-to-day operations can be transformational as well.

Measuring Success: User Adoption

How close is your company or line of business for becoming a data-driven organization? A common criterion used to gauge success of Business Intelligence projects is user adoption2. These criteria also applies to Power BI deployments; given reports and dashboards are to be consumed by people, adoption can validate the delivery approach chosen was the proper one.

The enterprise gateway can be utilized to obtain certain usage statistics.

Power BI is a versatile data modeling and reporting tool, but its intent should not be to replace Corporate BI strategy. Rather, it is one component of an overall Corporate BI and Self-Service strategy.

Power BI Champions

Beyond the corporate sponsor, Power BI champions can help evangelize the vision of rapid and highly valuable report and dashboard development. Power BI champions can come from any role, but typically they are subject matter experts (SMEs) who are also Excel savvy and are willing to collaborate with the Power BI corporate sponsor to strategically deploy Power BI across the organization.

Approach to Governed and Ungoverned Data Sources

A critical aspect of success and adoption relates to data source usage. One of the first planning activities of deploying Power BI is to assess your current data scenario. It is important to understand if the data Power BI users will expect to consume includes external data not currently maintained as part of an enterprise process (including data which is currently cleansed, cataloged by a dedicated team, typically IT). Improving data quality, reliability, and accuracy can have a positive impact on innovation and data exploration efforts.

This distinction around source data access is key to understanding how to ultimately deploy Power BI, as well as the process, roles and responsibilities associated with this deployment. In the following section we discuss the distinction of operating in ‘IT-Managed Self-Service BI’ mode versus the ‘Business-Led Self-Service BI’ mode entirely on this important topic of data source utilization.

If data source usage is well understood, the decisions made around this key topic can ultimately promote long-term self-service BI success.

Power BI Delivery: Three Approaches

Power BI is a very flexible set of tools that can be used for data preparation, data modeling, and/or report development activities. We see three primary approaches to delivery of Power BI solutions: Due to its flexibility and options, this can lead to confusion as to the “best” approach.

Power BI Delivery Approaches

Business-Led

Self-Service BI

Bottom-Up Approach


IT-Managed

Self-Service BI

Blended Approach


Corporate BI

Top-Down Approach

Analysis using any type of data source; emphasis on data exploration and freedom to innovate

Ownership:

Business supports all elements of the solution

Scope of Power BI use by business users:

Data preparation, data modeling, report creation & execution

Governed by:

Business


A “managed” approach wherein reporting utilizes only predefined/governed data sources

Ownership:

IT: data + semantic layer

Business: reports

Scope of Power BI use by business users:

Creation of reports and dashboards

Governed by:

IT: data + semantic layer

Business: reports


Utilization of reports and dashboards published by IT for business users to consume

Ownership:

IT supports all elements of the solution

Scope of Power BI use by business users:

Execution of

published reports

Governed by:

IT

Ownership Transfer

Over time, certain self-service solutions deemed as critical to the business may transfer ownership and maintenance to IT. It’s also possible for business users to adopt a prototype created by IT.

Chart by Microsoft

The “Corporate BI” approach can be morphed into the “IT-Managed Self-Serve BI” approach over time. But as mentioned above, adoption is a critical issue. Adoption of the business and user base would be the driver for the change to a “IT-Managed Self-Serve BI”. Adoption will give you the measure of who is ready to make the change or if any at all is ready. In my opinion this decision should itself be a highly managed IT decision, so that new data requirements can be vetted and incorporated into the data layer, along with any calculations that the business is dependent on. Which implies, that IT will continue to be in charge of the one source of truth. A scenario for this would be that a user has shown strong adoption for a number of reports, but through their analysis see’s that an additional calculation would be beneficial, so they might add it in a Dax formula. Once that formula becomes adopted by the business, that calculated field gets deployed by IT in the data layer as an aggregated column, so then anyone in the business would have access to it and can drag and drop it into a visualization.

As indicated in the previous chart, Power BI can be used in different ways which result in a fundamentally different user experience:

1. Business-Led Self-Service BI: In this scenario, the business users have the most involvement and control. Although some governed data sources certainly may be utilized as part of the overall solution (which is encouraged), there very well may also be non-standard, non-governed data sources involved (such as industry statistics purchased from a third party) which allow for exploration of patterns that can go well beyond the data recorded in the corporate data warehouse. The critical difference here is that the business unit takes ownership and support for this type of solution.

2. IT-Managed Self-Service BI: In this scenario, business users utilize Power BI as a reporting layer over standardized and governed data sources. In this mode, IT produces and governs a data layer of high quality which adheres to conformed enterprise master data. At the same time, the business owns the reporting layer which may or may not adhere to the same development cycles and governance standards promoted by IT.

3. Corporate BI: This scenario is frequently referred to as ‘enterprise reporting’ or sometimes ‘canned reporting’ wherein IT has full ownership of the entire solution and releases reports for user consumption.

These are the 3 standard approaches. But I think a 4th approach can be added.

4. Where approach #2 makes a clear 50 50 split between the data layer controlled by IT and the presentation layer

controlled by the business. I think a good idea in starting off, is the Corporate BI approach and eventually a small percentage of the #2 IT-Managed Self-Service BI approach, which can be gradually increased over time if needed. This way IT maintains the standard for the presentation layer, while training the user base how to use the self-service features of Power BI, while continuing to enforce the established standards and governance for Corporate BI data and report formats.

This cycle of adding requirements then becomes user driven and additional calculations are folded into the IT controlled data layer. I refer to this as the two-track approach.

Whereas VertiPaq is an in-memory column store engine (for tabular model) that combines state-of-art data compression and scanning algorithms to deliver blazing fast performance with no need for indexes, pre-calculated aggregates or tuning

ROLAP is optimized for large fact tables and relatively small dimension tables (star schema), while DirectQuery is mostly neutral towards the backend database structure though in SQL Server 2012, DirectQuery is supported only for models that are based on a single SQL Server relational data source.

Analysis Services Tabular – Tabular models are in-memory databases in Analysis Services. Using compression algorithms and multi-threaded query processing, the xVelocity in-memory analytics engine delivers fast access to tabular model objects and data by client applications such as Excel and Power View.

Tabular models use columnar storage which is already loaded into memory when needed by a query. During query execution, only the columns needed in the query are scanned. The VertiPaq engine underlying Tabular models achieves high levels of compression allowing the complete set of model data to be stored in memory, and it achieves blazingly fast data scan rates in memory, providing great query performance.

Tests have shown amazing performance:

Commodity laptop hardware can service VertiPaq scans at 5 billion rows per second or more and can store in memory billions of rows.

Commodity server hardware tests have shown VertiPaq scan rates of 20 billion rows per second or more, with the ability to store tens of billions of rows in memory.

To contrast these technologies, try loading a typical two billion row fact table into both a Multidimensional model and a Tabular model. Simple aggregation query performance tests would show results in the following order of magnitude:

The multidimensional model query performance on large models is highly dependent on whether the administrator has anticipated query patterns and built aggregations.

In contrast, Tabular model query performance requires no aggregations built during processing time in order to achieve great query performance.

IT may decide at some point to adopt a particular end-user Power BI solution if the solution provides enough critical business value. Given the report has already proven valuable to the business, requirements are already known, then IT adoption can represent a win/win scenario for both IT and business users.

Typically the following benefits are seen by the business when ownership transfer to IT occurs:

· Data can be centrally refreshed, often on a faster time schedule.

· Data size limits are typically no longer a constraint.

· Additional security capabilities become available.

· The solution can receive formal IT support and fall under existing service level agreements (SLAs).

· Frees up business users to continue exploring new data patterns while maintaining other Power BI solutions which are not yet production-ready, or an ownership transfer to IT does not make sense from a cost/effort perspective.

Reuse of existing data sources is a best practice in self-service BI. It improves standardization and can save refactoring time later.

Adoption of Power BI reports by IT can become a standard process, in which Power BI assets (queries, models, and/or reports and dashboards) are certified by adhering to IT compliance rules.

These compliance rules include validations such as:

· Usage of standardized, supportable data sources

· Utilization of conformed dimensions (for a consistent user experience

· Calculations follow accepted best practices

· Report layout follows standards

Business-Led

Tactical Prototyping

Bottom-Up Approach


IT-Driven

Strategic Prototyping

Top-Down Approach

Solutions generated during day-to-day work; considered “tactical” since output may be reusable at the Corporate BI level even if it’s not the original intent of the author

Aligns with:

Business-Led Self-Service BI


Purposeful, active, exploration of solutions intended for the enterprise BI environment

Aligns with:

Corporate BI

and

IT-Managed Self-Service BI

As shown in the above chart, prototyping can be approached in the following ways:

1. Business-Led Tactical Prototyping: Power BI is utilized by business users in their routine, day-to-day work. If this type of solution is considered successful, it may capture the attention of the team who manages the corporate BI / business analytics environment. In this way, the business-generated solution is considered a “tactical” prototype because a prototype wasn’t the original intent, but it ultimately served that purpose and helped improve or augment the corporate BI environment.

2. IT-Driven Strategic Prototyping: IT technology teams purposefully utilize Power BI to discover requirements for enterprise data warehousing and business intelligence projects. This is a strategic use of Power BI, given many users find it easier to define what they want or not want when interacting with a functional report sample. The advantage of using Power BI in this way is that data warehousing and business intelligence cost and work effort can be dramatically reduced, given refactoring is diminished as requirements are more closely aligned to business needs from the early stages of the project. This strategic prototyping activity is also sometimes referred to as active prototyping.

Regardless of its origin, Power BI prototypes developed can be used as functional blueprints that guide subsequent data integration, data modeling, or report development.

governance issues that may emerge from inaccurate data that may be found outside of the cleansed data warehouse.

Some organizations have found, however, that to fully leverage users’ creativity when exploring data it is important to set up an analytical sandbox. An analytical sandbox can be as simple as a confined database, or it can be a true Big Data repository of structured and semi-structured data whose main purpose is to allow data exploration.

When using sandbox sources it is important to differentiate data investigation from production reports.

You may find it useful to set up Development / QA / Production Power BI group workspaces

Explain early on what a workspace is!

Group workspaces in Power BI should be used liberally.

Bimodal BI

Mode 2

Bottom-Up Approach

Exploratory and nonlinear, emphasizing agility and speed

Prototyping activities:

Tactical

Aligns with:

Business-Led Self-Service BI


Mode 1

Top-Down Approach

Traditional and sequential, emphasizing safety and accuracy

Prototyping activities:

Strategic

Aligns with:

Corporate BI

and

IT-Managed Self-Service BI

Ownership Transfer

Over time, certain self-service solutions deemed as critical to the business may transfer ownership and maintenance to IT. It’s also possible for business users to adopt a prototype created by IT.

Both modes of a Bimodal BI environment can be defined by phases of deployment. These phases align with the vision of fostering a data-driven culture, and encapsulate technical and process best practices.

Phases of Delivery: Business-Led Self-Service BI

Business-Led SSBI Phase 1 – Current State Assessment

Assessments are typically conducted via surveys and interviews, given the qualitative nature of information it must uncover. The assessment intends to understand the current state of the infrastructure, Power BI skill level, categorize reporting and analytical needs, and to perform a gap analysis.

As an important outcome of this phase, the Power BI champion must identify reporting scenarios that are of low complexity but of high value to the business.

These scenarios become candidates for prototyping in the next phase of delivery.


Starting with a low complexity use case with high business value offers learning opportunities with the greatest chance for success.

Business-Led SSBI Phase 2 – Tactical Prototyping and Solution Creation

During this phase, the Power BI champion leads users in the creation of selected reports which have potential to become highly relevant to the business. This may involve just report creation, or it may also involve data extraction, standardization, modeling, and calculations. These preliminary reports should then be delivered to colleagues and subject matter experts for immediate evaluation and feedback. Receiving feedback quickly is a critical consideration.

It is also important to note some of the early prototypes are experimental in nature and may never be used for normal, routine business operations (not for production purposes, in IT nomenclature).

Specific Power BI group workspaces should be designed to clearly define prototypes that have no immediate business use.

Business-Led SSBI Phase 3 – Publishing and Monitoring

Once the Power BI prototype has proven business value, it is published to the collaboration area. The most common collaboration area for Power BI is the web-based Power BI Service.

Consider making members in a group workspace read-only, which allows edit privileges only for admins of the group.

Once the dataset and/or reports are published, several additional tasks remain:

· Creation of a dashboard (if desired; most frequently in Power BI there is a dashboard that highlights the most common elements from one or more reports)

· Refresh schedule in place (if importing data rather than using a live connection)

· Verification of sharing and/or security settings

· Creation of a content pack (if personalization by others is desired)

· Documentation based on departmental standards

It is uncommon for a new Power BI solution to be perfect in its first iteration, so we recommend the owner plan to make incremental improvements. Additionally, the Power BI champion leading the effort may want to define a governance approach for the source data depending on its sensitivity.

If using files as a source, the owner may want to designate protected shared folders where permissions will prevent accidental data loss or alteration.

Using OneDrive for Business or SharePoint Online, which both offer versioning capabilities, is a best practice.

Additionally, as the report grows in popularity, Power BI champions may want to understand usage patterns. This information is useful as he or she must allocate time and/or resources for maintaining, augmenting, and growing self-service BI solutions.

Business-Led SSBI Phase 4 – Support, Training, and Expansion

The next phase after a Power BI report has been running in an automated fashion, offering high value to the business, is to expand the effort in terms of reach and infrastructure. This is an important phase in which the Power BI champion must lead the way to fully evangelize the vision of a data-driven culture.

Two key parallel approaches are strongly recommended:

Vision expansion

Disseminate Power BI knowledge to the internal community of users and support their efforts. This can typically be done through:

o Sharing of knowledge: Power BI users actively share knowledge through techniques such as:

§ Internal and/or external user groups

§ Lunch and learn sessions

§ Knowledgebase

§ Frequently asked questions

§ Short how-to videos

§ Yammer

§ E-mail distribution lists

o Power BI Center of Excellence: Actively collect and categorize internal practices for streamlined reporting and define a vehicle for sharing with others (such as a newsletter or intranet site).

o Gamification: Power BI internal competitions which encourage the sharing of knowledge in an enjoyable manner and recognizes people who have created clever solutions.

o Power BI training: Training for Power BI report designers and data modelers, as well as for Power BI report consumers.

Infrastructure consolidation

In collaboration with IT, define which additional infrastructure layers can increase the quality and relevancy of the Power BI deployment:

o Power BI production reports: Displaying branded stamp of approval, for instance a logo that symbolizes a production report which has been certified, thus more reliable than a prototype.

o SQL Server services: Handling of master data, data quality processes, semantic layer, and other business intelligence components that have the capacity to enhance the value of the solution.

o Cortana Analytics: Big Data and Advanced Analytics integration for purposes of enhancing the value of the solution. Significant BI and analytics capabilities are being introduced to the Cortana Analytics Suite.

A certain degree of ownership transfer may happen during infrastructure consolidation (ownership transfer is discussed later in this section).

As the environment matures and expands, it is important to develop a recognition plan to reward those users that developed the original Power BI solution. As the product of their work moves to other teams, he or she must be in agreement with the ownership transfer. If this is not the case, the corporate BI environment may be affected by losing contributions of skilled authors of Power BI models and reports.

A successful partnership between business users and IT requires respect for each other’s efforts and different goals.

To enforce row-level security (RLS) for Power BI Pro users browsing content in a workspace, continue to use classic workspaces. Select the Members can only view Power BI content option. Alternatively, publish an Power BI app to those users, or use sharing to distribute content. The forthcoming Viewer Role will enable this scenario in future in new workspace experience workspaces.

When you create one of the new workspaces, you're not creating an underlying, associated Office 365 group. All the workspace administration is in Power BI, not in Office 365. In the new workspace experience, you can now add an Office 365 group in the workspace access list to continue managing user access to content through Office 365 groups.

Administration for new workspace experience workspaces is now in Power BI, Power BI admins decide who in an organization can create workspaces.

For classic workspaces based on Office 365 Groups, administration continues to occur in Office 365 admin portal and Azure Active Directory.

In Workspace settings in the admin portal, admins can use the Create workspaces (new workspace experience) setting to allow everybody or nobody in an organization to create new workspace experience workspaces. They can also limit creation to members of specific security groups.