Power BI Best Practices - Tactics

I've been asked to present Power BI best practices to a client on Monday. This two post series is part reference to great articles and partly my own insights on the topic, some gained by painful experience. Part one addresses BI project strategy, and this is part two on tactics:

#1 Dashboards are "a single place to monitor the current state of the data".
Microsoft support has tips for great dashboards and points out some design techniques that are important to keep in mind:

• Readers will drill down for detailed information, the dashboard should provide high level information, showing what performance indicators require the user's attention.
• Make the most important information the biggest on the page and provide context, including units. For example: show a KPI visual with trend and current value.
• Put the most important items in the upper corner where your users will start reading.
• Consider the visualization and select those that tell the story in the data.
• The Microsoft support article includes a great group of references on design including writings of Stephen Few and Edward Tufte.
• Dustin Ryan points out a nice technique of using visualization elements as navigation (at time 47:23 in the video) as they interact with other visuals in a report.

#2 Design  for the user's reporting needs.
Like Delora Bradish's exhortation to always consider reporting when designing your data warehouse, your data model should provide friendly table and column names that reflect familiar business terms well understood by the system's users.

Include only necessary data and hide any information that might be used for creating the data model or calculations but not shown (like key values) in reports.

#3 Design your data model to support self-service.
From Marco Russo we are guided to take advantage of data views to stabilize our data loading process:

• Use views for your data sources to avoid dependence on table structures
• Create a view schema in the database that consists only of the views for each table to be imported
• Each view should only contain the columns necessary for reporting from the data model
• Use meaningful names that will make sense to your users

Double check relationships in your data model to ensure that self service reporting will return valid data.

Carefully specify check data types and how or whether fields should be summarized.

Separate unrelated data into multiple tables to clarify reporting.

Use star schema when possible, as snowflakes can limit scalability. Use snowflakes when lookup tables can provide usability improvements without performance implications.

#4 Consider security and data refresh early in the design of your project.
Work with appropriate administrators, whether in Security or your DBA's, to build gateways for data refresh or direct query access from on premises data sources to the Power BI portal.

Schedule refreshes to correspond with your data frequency, there is no reason to update data eight times a day if it is loaded weekly.

#5 Take advantage of Power BI Content Packs internally and to access services.
Use organizational Content Packs to provide read-only pre-built solutions to your organization or other groups. Members of the group can copy these and personalize them as needed.

Use Content Packs for services published for many common business services. Use this link to see available content packs from the Power BI portal.

#6 Configure your solution to maximize Q&A Natural Language capabilities.
Chuck Schaeffer's CRM Search article provides considerable information about designing for Q&A:

• "Datasets not represented with tiles are not considered, anticipate the types of question that may be asked and design the dashboard with tiles from datasets which may respond to sure questions".
• Questions will join entities to provide answers (example sales and fiscal calendar to answer a question about "Last year's sales revenue").
• Be sure to fix data types so that Q&A can correctly represent the answers.
• Be sure to mark those that should not be aggregated with do not summarize.
• Add data categories wherever possible.
• Put your topics in their own datasets, break out each topic from a given entity.
• Add synonyms to improve Natural Language capabilities of PBI on your data set.

#7 Be wary of performance issues that might affect your data model's performance.
These are a few tips from my blog entry on Pragmatic Work's site:
Tall, narrow tables are faster than wide tables with many columns, especially if the columns are highly unique (columns that are highly unique are said to exhibit "high cardinality").

Use integer keys for primary/foreign key relationships.

Reduce uniqueness in columns by splitting date and time values, and by reducing the "granularity" of time to hours if that is required for reporting.

Include only relevant data in the data model. One powerful example is not to include unnecessary dates in your Date

Please comment on my choices, or add your own best practices to this growing list.

The first post in this short series addresses strategic concerns for developing BI projects.

Power BI Best Practices - Strategy

I've been asked to present Power BI best practices to a client on Monday. This two post series is part reference to great articles and partly my own insights on the topic, some gained by painful experience. Part one, below, addresses BI project strategy and part two covers tactics:

#1 Find a significant business need and address it.
To start out using best practices it is wise to begin with the operating environment. The Business.
 Your BI solution is a system designed to address a business need, it is not a stand alone project. The opportunities for BI solutions are numerous. The ComputerWorld Weekly article by Forrester in August of 2013 shows a chart that categorizes business needs from BI. This chart also shows that many, if not most BI programs miss on these outcomes. Don't forget to address these objectives as you design, build and implement your solutions.

#2 Address the business opportunity where BI can make the most impact with the least delay.
Find the need, then partner closely with sponsors, other stakeholders, and most closely with those who will use your system, to define and deliver success. Without these three levels of buy-in throughout the project you are at significant risk of delivering a product that doesn't fulfill expectations.

Identify data sources and their availability to determine feasibility of this project. If there are significant challenges in data gathering or validation for this project - identify them, determine responsible parties in the business to resolve the issues (use your company's Data Governance organization or create one) and then either find other data or find other opportunities. Do not get bogged down by data issues, this can kill a BI project.

#3 Iterate to quickly deliver solutions to the business. 
Every week that you wait to deliver as you perfect or add features to the BI solution is a week that the business doesn't have visibility to their information. Produce the minimum viable product, test it thoroughly and validate the results, then get it to your business partners. Their feedback on the "incomplete" product will be invaluable as you continue to develop your solution and they will quickly benefit from your hard work.

#4 Find a BI tool that best supports the business need, then design a solution that supports users.
A 2014 article on CIO.com "9 Common BI Software Mistakes" calls out selecting a tool in isolation as one of the worst BI mistakes. Invest your time and money in a tool that will fit business requirements, work with the business to validate your selection. The overriding objective of any BI solution is to deliver a system that users find helpful. Then, build your solution with the user in mind.

Do you have other strategic concerns that must be addressed when beginning BI projects? Please comment or share your thoughts.

The next post in this short series addresses tactical issues related to the development of a specific BI solution.

Power BI on Premises and Pyramid Analytics

Power BI is a great tool and more than a few BI managers have told me that this tool has changed the way that they think about their BI environment. Unfortunately some of those managers also admit that they can't use it because their data cannot leave their network. I know that there is a whole conversation that we need to have about security, but for now, let's leave that aside and respect the customer's need to publish Power BI on premises.

My customers are delighted to that there is a solution coming from Microsoft in SQL Server Reporting Services. The May announcements from Pyramid Analytics and Microsoft provided much hoped for news! The twist is that this current solution has been created by another reporting platform and that causes many questions. To the customer there are two systems already: Microsoft SQL Server reporting stack SSRS and Power BI, with the on premises solution adding the third: Pyramid Analytics.

The information below may help you to decide whether Pyramid Analytic's BI Office is the right choice to get Power BI deployed inside your network.

Kudos must go out to Pyramid Analytics for building a great solution that really solves a problem for many customers!

Images from the Pyramid Analytics video: Best-in-Class On-Premise Business Intelligence for Power BI from Pyramid Analytics

Conceptual slide, describing context for combing the systems.

Image of a Pyramid BI Office Storyboard with a slicer affecting all visualizations.

This video posted by Pyramid Analytics shows the integration, not only to securely host the Power BI solution, but to integrate Power BI visualizations in to a Pyramid Storyboard (link).

This video is a one hour interview of Jen Underwood, Impact Analytics and Peter Sprague, VP Product Marketing , Pyramid Analytics discussing this very topic and reviewing the capabilities of Pyramid's BI Office (link).

Announcement in the May update feature summary (link).