Power BI Data Modeling Build Interactive Visualizations, Learn DAX, Power Query, and Develop BI Models (Nisal Mihiranga) (z-library.sk, 1lib.sk, z-lib.sk)

📄 File Format: PDF

💾 File Size: 18.2 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

(This page has no text content)

📄 Page 2

(This page has no text content)

📄 Page 3

Power BI Data Modeling Build Interactive Visualizations, Learn DAX, Power Query, and Develop BI Models Nisal Mihiranga www.bpbonline.com

📄 Page 4

FIRST EDITION 2022 Copyright © BPB Publications, India ISBN: 978-93-89328-837 All Rights Reserved. No part of this publication may be reproduced, distributed or transmitted in any form or by any means or stored in a database or retrieval system, without the prior written permission of the publisher with the exception to the program listings which may be entered, stored and executed in a computer system, but they can not be reproduced by the means of publication, photocopy, recording, or by any electronic and mechanical means. LIMITS OF LIABILITY AND DISCLAIMER OF WARRANTY The information contained in this book is true to correct and the best of author’s and publisher’s knowledge. The author has made every effort to ensure the accuracy of these publications, but publisher cannot be held responsible for any loss or damage arising from any information in this book. All trademarks referred to in the book are acknowledged as properties of their respective owners but BPB Publications cannot guarantee the accuracy of this information. www.bpbonline.com

📄 Page 5

Dedicated to Ruwanthi Abeywickrama My wife, who supports every possible way throughout my journey

📄 Page 6

About the Author Nisal Mihiranga is a seasoned technical consultant who has spent the last ten years designing and implementing business intelligence solutions for customers and partners. Nisal has given talks at major IT conferences such as SQL Saturday, Singapore Datacamp, and user group meetings throughout his career. He has also worked at ZoneTwentyFourSeven, Virtusa, and Fortude as an engineering consultant and in training and mentoring roles. It was back in 2017 that Nisal got his taste for writing when he was started writing blog posts and technical articles. This combining with his passion for the Power BI tool led him to write this book. Outside work, Nisal volunteers his spare time as a tech community contributor, helping, organizing events, and mentoring young people in taking up careers in technology.

📄 Page 7

About the Reviewer Dinesh Asanka has 20+ years of experience in the areas of Data. Dinesh has experienced in the areas of Machine Learning, Data Mining, Data Warehousing and Database Administration. He has obtained MPhil in data warehousing. He is a frequent columnist in popular web sites. Currently he is a Senior Lecturer at University of Kelaniya Sri Lanka.

📄 Page 8

Acknowledgement There are a few people I want to thank for the continued and ongoing support they have given me during the writing of this book. First and foremost, I would like to thank my wife for putting up with me while I was spending many weekends and evenings on writing—I could have never completed this book without her support. This book wouldn’t have happened if I hadn’t had great support from Dinesh Asanka who did the technical review for this book. My gratitude goes to him and all who supported me to make this real. Finally, I would like to thank BPB Publications for giving me this opportunity to write my first book for them.

📄 Page 9

Preface When compared to the traditional distribution of business intelligence solutions, self-service business intelligence tools are a relatively new invention. In the past, data warehouses were built, and a development team developed a web-based application with an agreed-upon set of visuals to offer insights. With the latest and greatest self-service business intelligence products, the BI delivery paradigm has been revolutionized. This move was made to solve a number of significant challenges, including how to undertake ad-hoc analysis, how to lessen reliance on the software application development team, and how the new technology adds value in terms of insight generation. There are various types of users in an organization, including top-level business users with little programming experience, middle-layer operational users, and technical users such as software engineers, data scientists, and data analysts. The difficulty here is to figure out how to provide a single tool that can help all of the users described above. This is where a product like Power BI really benefits. The majority of us are familiar with spreadsheet programs such as Microsoft Excel, Lotus 123, Google Sheets, and Open Office. The most of the functionalities and look and feel of the Power BI software are inherited from Microsoft Excel. As a result, even the most inexperienced user can quickly learn how to use the technology. Power BI is a product that has been on the market since mid-2014. It had swiftly grown since then, with many new features added to the broader ecosystem. There is strong community support, and the product has recently become very popular among us. There are numerous online tools for learning how to use Power BI. When compared to other tools, it is quite simple and straightforward. The true gold, though, is learning how to accurately design data models that work effectively even as data accumulates over time. This book is all about it. The main purpose of this book is to give you the knowledge and skills you need to implement Power BI data models in your own scenarios. This book includes real-world examples that demonstrate how to install, configure,

📄 Page 10

and design Power BI reports, as well as deploy and maintain Power BI solutions. You will learn the following during the course of this book's 19 chapters: Chapter 1 introduces the Power BI to the readers. It mainly talks about the history of the Power BI and the journey so far it came as a BI product. This chapter also focused to mention the position of Power BI as a BI tool comparing other tools in the market. Chapter 2 discusses the different products in the Power BI ecosystem and how each connects each other to provide end-to-end self-service BI solutions for all types of users. Chapter 3 with this chapter, you have installed Power BI desktop, this chapter gives you a guided tour of how to connect into data source using Power BI desktop Chapter 4 is a chapter that discusses, how to turn the data into quick visuals with minimum steps. The focus was to give a quick guide for reporting Chapter 5 is also a key chapter that discusses, in-depth, dimensional modeling and why we need to create data models. This also covers some data warehousing key elements. Chapter 6 describes how to connect Power BI with relational databases like Microsoft SQL Server. Predominantly talked about connection types and when to use it. Chapter 7 introduces the data transformation with Power Query. This covers some key elements of data cleansing techniques and transformations. It is vital to have clean data in data projects. Chapter 8 describes how to create data models through creating relationships. Chapter 9 discussed DAX query language and key elements like calculated columns, calculated measures, and hierarchies. Chapter 10 describes how to deliver the insightful report in Power BI. details about key features in data visualization you can find in Power BI. Chapter 11 describes how to implement row-level security in Power BI. Chapter 12 discusses how to use Calculation Groups as a feature to reduce the complexity of Power BI data models.

📄 Page 11

Chapter 13 discusses about AI features offered in Power BI and its usage. Chapter 14 When you are having large datasets its impossible to reduce the data refresh time. In this chapter discussed about how Incremental refresh feature helps to reduce the data refresh time. Chapter 15 Big datasets its a norm in modern era. Power BI has techniques to cater big data requirements. Discusses aggregations and composite models. Chapter 16 Dataflows is another cool feature in Power BI. This chapter discusses how to create dataflows and collaborate. Chapter 17 discusses how to optimize DAX queries. Chapter 18 discusses how to collaborate the Power BI workloads in your organization. Chapter 19 It is important to improve the performance in your models This chapter discusses how to improve performance.

📄 Page 12

Code Bundle and Coloured Images Please follow the link to download the Code Bundle and the Coloured Images of the book: https://rebrand.ly/d05c7e The code bundle for the book is also hosted on GitHub at https://github.com/bpbpublications/Power-BI-Data-Modeling. In case there's an update to the code, it will be updated on the existing GitHub repository. We have code bundles from our rich catalogue of books and videos available at https://github.com/bpbpublications. Check them out! Errata We take immense pride in our work at BPB Publications and follow best practices to ensure the accuracy of our content to provide with an indulging reading experience to our subscribers. Our readers are our mirrors, and we use their inputs to reflect and improve upon human errors, if any, that may have occurred during the publishing processes involved. To let us maintain the quality and help us reach out to any readers who might be having difficulties due to any unforeseen errors, please write to us at : errata@bpbonline.com Your support, suggestions and feedbacks are highly appreciated by the BPB Publications’ Family. Did you know that BPB offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.bpbonline.com and as a print book

📄 Page 13

customer, you are entitled to a discount on the eBook copy. Get in touch with us at: business@bpbonline.com for more details. At www.bpbonline.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on BPB books and eBooks.

📄 Page 14

Piracy If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at business@bpbonline.com with a link to the material. If you are interested in becoming an author If there is a topic that you have expertise in, and you are interested in either writing or contributing to a book, please visit www.bpbonline.com. We have worked with thousands of developers and tech professionals, just like you, to help them share their insights with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea. Reviews Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions. We at BPB can understand what you think about our products, and our authors can see your feedback on their book. Thank you! For more information about BPB, please visit www.bpbonline.com.

📄 Page 15

Table of Contents 1. Introducing Microsoft Power BI Structure Objective Business intelligence History of Business Intelligence Business Intelligence process Self-service BI Introducing Power BI Components in Power BI Why does Power BI stand out from other BI tools in the market? Conclusion Questions Answers 2. Power BI Ecosystem Structure Objectives Power BI ecosystem Power BI desktop Power BI service Power BI mobile Conclusion Questions Answers 3. Getting Started with Power BI and Connect with Data Structure Objective Get data Power BI connectors Connection types Conclusion

📄 Page 16

Questions Answers 4. My First Power BI Report Structure Objective Download the demo materials Load data into Power BI Data cleansing and blending Create product hierarchy Create calculations Data visualizing Add a map visual Add a clustered column chart Add a matrix visual Cross filtering Drill-down Add a slicer Conclusion Questions Answers 5. Introducing BI Building Blocks: Dimensional Modeling Concepts Structure Objective What is data modeling, and why? Classic BI approach Modern self-service BI approach End-to-end BI with Microsoft Power BI Data warehouse Bus matrix architecture Fact tables Dimension tables Granularities Star schema Snowflake schema Conclusion

📄 Page 17

Questions Answers 6. Get Data from Relational Databases Structure Objective Relational database connectors Getting data with SQL Server database Conclusion Questions 7. Cleansing, Blending, and Transforming Data Using Power Query Structure Objective Introducing Power Query Business scenario Connect to the database and extract tables Data cleansing Work with budget data Conclusion Questions Answer 8. Build Relationships Structure Objective Relationships in Power BI Methods of creating relationships Demystify relationships in Power BI Select cardinality of a relationship Select cross filter direction Active and inactive relationships Conclusion Questions 9. Introducing DAX, Calculated Columns, Calculated Measures, and Hierarchies

📄 Page 18

Structure Objective Calculations in Power BI Calculated columns Calculated measures Calculated tables Hierarchies Conclusion Questions 10. Creating Insightful Reports Using Visualization Techniques Structure Objective Data visualization practices Designing the report Filtrations Use slicers in the report Cross-filtering Drill-down visuals Drill-through visuals Theming Mobile layout Conclusion Questions 11. Row-Level Security in Power BI Structure Objective Security overview Static row-level security—role-based Creating roles How to test raw-level security? Dynamic row-level security Conclusion Questions 12. Calculation Groups in Power BI

📄 Page 19

Structure Objective Calculation groups requirement Calculation groups and how it works? Creating calculation groups in Power BI Sorting the calculated items Limitations in calculation groups Conclusion Questions 13. Self-service AI Capabilities in Power BI Structure Objectives Introducing AI capabilities in Power BI Quick insights Q&A visual in Power BI Q&A visual in the Power BI desktop Decomposition tree visual Key influencer Smart narrative Perform text analytics, vision, and Azure Machine Learning R and Python integration Conclusion Questions 14. Incremental Refresh for Data Models Structure Objective Data refresh requirement Incremental data refresh requirement Configuring incremental refresh Defining the filter parameters Applying filter using parameters Defining incremental refresh policy Conclusion Questions

📄 Page 20

15. Composite Models and Perform Aggregations to Improve Query Performance Structure Objective Prerequisites Connection types—recap Import DirectQuery Demo database Let us consume the demo database using Power BI Let us build aggregations Add newly created aggregated table into model How to determine whether the aggregation table is used by the Power BI engine or not? Power BI performance analyzer How to configure aggregations? Let us try some advanced aggregations Composite models Composite models explained Configure storage mode Force engine to select right aggregation table Composite models allow to achieve balanced architecture Conclusion Questions 16. Self-service Data Preparation for Any Data Structure Objective Introducing citizen ETLing Why self-service data preparation? Creating dataflows in Power BI ETL for large data sets Conclusion Questions 17. Optimize DAX Structure