Microsoft Data Analytics Applied Microsoft Power BI Bring your data to life! Teo Lachev Prologika Press Applied Microsoft Power BI Bring your data to life! Published by: Prologika Press [email protected] http://www.prologika.com Copyright © 2016 Teo Lachev All rights reserved. No part of this book may be reproduced, stored, or transmitted in any form or by any means, without the prior written permission of the publisher. Requests for permission should be sent to [email protected]. Trademark names may appear in this publication. Rather than use a trademark symbol with every occurrence of a trademarked name, the names are used strictly in an editorial manner, with no intention of trademark infringement. The author has made all endeavors to adhere to trademark conventions for all companies and products that appear in this book, however, he does not guarantee the accuracy of this information. The author has made every effort during the writing of this book to ensure accuracy of the material. However, this book only expresses the author’s views and opinions. The information contained in this book is provided without warranty, either express or implied. The author, resellers or distributors, shall not be held liable for any damages caused or alleged to be caused either directly or indirectly by this book. Printed in the United States of America ISBN 13 978-0-9766353-6-9 ISBN 10 0-9766353-6-4 Author: Teo Lachev Editor: Edward Price Reviewer: Jen Underwood Cover Designer: Zamir Creations contents CHAPTER 1 INTRODUCING POWER BI PART 1 POWER BI FOR BUSINESS USERS CHAPTER 2 THE POWER BI SERVICE CHAPTER 3 VISUALIZING DATA CHAPTER 4 POWER BI MOBILE PART 2 POWER BI FOR DATA ANALYSTS CHAPTER 5 DATA MODELING FUNDAMENTALS CHAPTER 6 TRANSFORMING DATA CHAPTER 7 REFINING THE MODEL CHAPTER 8 IMPLEMENTING CALCULATIONS PART 3 POWER BI FOR PROS CHAPTER 9 ENABLING TEAM BI CHAPTER 10 ORGANIZATIONAL BI PART 4 POWER BI FOR DEVELOPERS CHAPTER 11 PROGRAMMING FUNDAMENTALS CHAPTER 12 EMBEDDING REPORTS CHAPTER 13 CREATING CUSTOM VISUALS GLOSSARY OF TERMS foreword For eight consecutive years, Microsoft has been positioned as a Leader in the Gartner Magic Quadrant for Business Intelligence and Analytics Platforms. While each year has seen advances across our product line, this past year’s progress is truly unprecedented. We firmly believe Microsoft now offers the industry’s most complete and modern business intelligence product family (with unmatched breadth and depth) on premises or in the cloud. Although our teams knew where we were headed, our customers did not. We addressed those concerns by revealing the first ever, public Business Intelligence roadmap. Now everyone knows that our future is bright with Power BI at our core. Microsoft Power BI 2.0 has just begun to fundamentally disrupt the business intelligence market. For Microsoft business intelligence fans, the journey up to this point has been unquestionably challenging at times. Today our challenge is keeping up with all of the exciting monthly, weekly and even daily releases. Power BI development teams are moving at an astonishing pace. The data visualization framework was openly shared via GitHub empowering anyone to extend the offerings. Unlike Power BI 1.0 that went unnoticed, Power BI 2.0 is making waves in the market. Since the December 2014 Power BI preview, there has already been widespread adoption in over one million organizations across 185 countries worldwide. Looking back there were many invaluable lessons learned from the prior unsuccessful launch. Most importantly the engineering teams learned to listen more closely to customers and partners on a daily basis. Since the cloud environment enables agile development with continuous release cycles, the teams are able to respond to market changes faster than they ever could in the past. From technical architecture to authoring tools and user experiences, the entire solution was completely reimagined and redeployed as a preview in just a few months. The cloud is a game changer. One of the most significant and sensational changes was the introduction of free Power BI Desktop. Power BI Desktop unifies the former Excel Power Tools (Power Pivot, Power Query and Power View) into one vastly improved, stand-alone, data discovery desktop application built on a modernized HTML5 visualization framework. Unlike the Excel Power Tools predecessors, Power BI Desktop has absolutely no dependencies on Excel or Office. Power BI Desktop removes adoption friction, improves the analytics authoring user experience, allows for third-party extension and ultimately is the result of Microsoft listening to the analytics market. The move of Power BI outside of Excel was no easy feat politically and technically. The July 2015 GA release of Power BI 2.0 unveiled a sleek new design along with the new visualization framework and scalable hybrid cloud architectural foundation. It also brought native mobile apps for iOS, Android and Windows tablets and smart phones. The new architecture now includes a plethora of in-memory or direct connect data sources for large scale deployments. To further supplement the third-party open source custom data visualization extensions, R script integration was added to open up a world of unlimited visualization options. Pre-packaged Software-as-a-Service (SaaS) and organizational level data models, reports and dashboards can be shared via content packs making Power BI easier than ever for non-technical users to enjoy. Natural language query has improved and now includes voice commands via Cortana. Intelligent automated analytics is just starting to surface with the latest Get Insights release that highlights key findings in a data set from identifying outliers to noting significant changes. Power BI Enterprise Gateway, Developer APIs and the sea of incremental feature releases continuously surprises and delights a growing worldwide community. It has never been a better time to be a Microsoft BI fan. Even our skeptics have voiced appreciation for the changes in our development approach and our latest offerings. As Power BI swiftly blossoms into the market leading business intelligence solution, it will be a weapon that all data analysts will want to include in their analytics arsenal. The true power in Power BI cannot be appreciated without understanding what the offering can do and how to best use it. That is why resources like this fantastic book will become instrumental for you. This book starts by providing an overview of the foundational components of Power BI. It introduces Power BI Desktop, data modeling concepts, building reports, publishing and designing dashboards. Readers will be up and running in no time. It then moves on to bring you up to speed on deeper dive topics such as data gateways, data refresh, streaming analytics, embedding and the Power BI data visualization API. Not only is Teo one of the first people in the world to learn and write about Power BI 2.0, he also brings a wealth of knowledge from deploying the first real-world implementations. Much like Teo’s previous books on Analysis Services and Reporting Services, this Power BI book will be a must read for serious Microsoft professionals. It will also empower data analysts and enthusiasts everywhere. On a closing note, please share your feedback with Teo and the Microsoft Power BI teams as you embark on your own Power BI journey. Microsoft Power BI teams do actively monitor social media channels, the Power BI community site, external blogs, YouTube videos, and User Voice more closely than ever before. You can be heard. You can make a difference. This is not only a new, different and better Power BI 2.0 – it is a new, different and better Microsoft. Jen Underwood Principal Program Manager Microsoft Business Intelligence preface To me, Power BI is the most exciting milestone in the Microsoft BI journey since circa 2005, when Microsoft got serious about BI. Power BI changes the way you gain insights from data; it brings you a cloud-hosted, business intelligence and analytics platform that democratizes and opens BI to everyone. It does so under a simple promise: “five seconds to sign up, five minutes to wow!” Power BI has plenty to offer to all types of users who’re interested in data analytics. If you are an information worker, who doesn’t have the time and patience to learn data modeling, Power BI lets you connect to many popular cloud services (Microsoft releases new ones every week!) and get insights from prepackaged dashboards and reports. If you consider yourself a data analyst, you can implement sophisticated self-service models whose features are on a par with organizational models built by BI pros. Speaking of BI pros, Power BI doesn’t leave us out. We can architect hybrid organizational solutions that don’t require moving data to the cloud. And besides classic solutions for descriptive analytics, we can implement innovative Power BI-centric solutions for real-time and predictive analytics. If you’re a developer, you’ll love the Power BI open architecture because you can integrate custom applications with Power BI and visualize data your way by extending its visualization framework. From a management standpoint, Power BI is a huge shift in the right direction for Microsoft and for Microsoft BI practitioners. Not so long ago, Microsoft BI revolved exclusively around Excel on the desktop and SharePoint Server for team BI. This strategy proved to be problematic because of its cost, maintenance, and adoption challenges. Power BI overcomes these challenges. Because it has no dependencies to other products, it removes adoption barriers. Power BI gets better every week and this should allow us to stay at the forefront of the BI market. As a Power BI user you’re always on the latest and greatest version. And Power BI has the best business model: most of it it’s free! I worked closely with Microsoft’s product groups to provide an authoritative (yet independent) view of this technology and to help you understand where and how to use it. Over more than a decade in BI, I’ve gathered plenty of real-life experience in solving data challenges and helping clients make sense of data. I decided to write this book to share with you this knowledge, and to help you use the technology appropriately and efficiently. As its name suggests, the main objective of Applied Microsoft Power BI it so to teach you the practical skills to take the most of Power BI from whatever angle you’d like to approach it. Some people discouraged me to write this book. After all, trying to cover a product that changes every week is like trying to hit a moving target! However, I believe that the product’s fundamentals won’t change and once you grasp them, you can easily add on knowledge as Power BI evolves over time. Because I had to draw a line somewhere, Applied Microsoft Power BI covers all features that were announced at the PASS Summit 2015 and that were released by December 2015. Although this book is designed as a comprehensive guide to Power BI, it’s likely that you might have questions or comments. As with my previous books, I’m committed to help my readers with book-related questions and welcome all feedback on the book discussion forums on my company’s web site (http://www.prologika.com/cs/forums). Consider following my blog at http://prologika.com/cs/blogs and subscribing to my newsletter at www.prologika.com to stay on the Power BI latest. Happy data analytics with Power BI! Teo Lachev Atlanta, GA acknowledgements Writing books is hard! And writing a book about a cloud platform, which adds features weekly, is even harder. On the upside, I can claim that this book has no bugs. After all, if something doesn’t work now, it used to work before, right? On the downside, I had to change the manuscript every time a new feature popped up. Fortunately, I had people who supported me. The book (my seventh) would not have been a reality without the help of many people to whom I’m thankful. As always, I’d like to first thank my family for their ongoing support. The main personas mentioned throughout the book, as imagined by my 12-year old son, Martin, and 15-year old daughter, Maya. As a Microsoft Most Valuable Professional (MVP), I’ve been privileged to enjoy close relationships with the Microsoft product groups. It’s great to see them working together! I must mention a few names. Jen Underwood (Principal Program Manager at the Power BI team) contributed the most to this book! She helped me connect the Power BI dots (bars), reviewed the book manuscript, and provided valuable feedback. Thanks to Lukasz Pawlowski (Senior Program Manager at the Power BI team) for shedding light on report embedding. As always, the Analysis Services team has been very responsive. Special thanks to Akshai Mirchandani, Ashvini Sharma, Jeffrey Wang, Kasper de Jonge, and Marius Dumitru. Thanks to my editor, Ed Price, for helping me polish my writings. Finally, thank you for purchasing this book!