Saturday, June 11, 2016

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:
#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.

7 comments:

Rachael said...

A recent article from Ginger Grant additionally stresses the importance of group workspaces and gateways. link.

Helmut Dempewolf said...


You can definitely see your skills within the article you write. The world hopes for even more passionate writers such as you who aren't afraid to mention how they believe. Always follow your heart. capitalone.com login

viswanath said...

AWS Training in Bangalore - Live Online & Classroom
myTectra Amazon Web Services (AWS) certification training helps you to gain real time hands on experience on AWS. myTectra offers AWS training in Bangalore using classroom and AWS Online Training globally. AWS Training at myTectra delivered by the experienced professional who has atleast 4 years of relavent AWS experince and overall 8-15 years of IT experience. myTectra Offers AWS Training since 2013 and retained the positions of Top AWS Training Company in Bangalore and India.


IOT Training in Bangalore - Live Online & Classroom
IOT Training course observes iot as the platform for networking of different devices on the internet and their inter related communication. Reading data through the sensors and processing it with applications sitting in the cloud and thereafter passing the processed data to generate different kind of output is the motive of the complete curricula. Students are made to understand the type of input devices and communications among the devices in a wireless media.

Family Matters in Islam said...

I loved your Blog for the earth activities, I'm going to Follow Your Post for my Trip on Earth day. You are an amazing nature loving Person.

sangeetha sathyan said...

I really admired your post, such great and useful information that you have mentioned here.
AWS Training in Chennai
AWS course in Chennai
VMware Training in Chennai
VMware course in Chennai
Cloud Computing Training in Chennai
Cloud Computing courses in Chennai
DevOps Training in Chennai
AWS Training in Chennai
AWS course in Chennai

Rajesh said...

thanks for sharing this information
UiPath Training in Bangalore
tableau training in bangalore
tableau training in bangalore marathahalli
best tableau training institutes in bangalore
tableau classroom training in bangalore
best data science training institute in bangalore
best data science training in bangalore

htop said...

nice blog
best python training in chennai
selenium training in chennai
selenium training in omr
selenium training in sholinganallur
best java training in chennai
best hadoop training in chennai
data Science training in chennai