Everand Logo

Welcome to Everand!

  • Discover millions of ebooks, audiobooks, and so much more with a free trial

    Only $9.99/month after trial. Cancel anytime.

    Implementing Power BI in the Enterprise
    Implementing Power BI in the Enterprise
    Implementing Power BI in the Enterprise
    Ebook382 pages2 hours

    Implementing Power BI in the Enterprise

    By Greg Low

    4.5/5

    ()

    Read preview

    About this ebook

    Power BI is an amazing tool. It's so easy to get started with and to develop a proof of concept. 


    Enterprises want more than that. They need to create analytics using professional techniques. 


    In this unique book, Dr Greg Low shows you how he has implemented many successful Power BI

    LanguageEnglish
    PublisherSQL Down Under Pty Ltd
    Release dateJun 7, 2021
    ISBN9781922654014
    Implementing Power BI in the Enterprise

    Related to Implementing Power BI in the Enterprise

    Related ebooks

    Computers For You

    View More
    View More

    Related podcast episodes

    Related articles

    • Article

      Understanding ELT & ETL

      Apr 1, 2021

      Understanding ELT & ETL
      8 min read

    • Article

      Three Low-code Options

      Nov 12, 2020

      Counting Intel, Vodafone and VW among its customers, OutSystems helps businesses create cloudbased, on-premises and hybrid applications for mobile and web. Its development environment is predominantly drag-and-drop, with views for processes, data and

      3 min read

    • Article

      Buying The Tool

      Apr 1, 2021

      Buying The Tool
      3 min read

    • Article

      Real World Computing

      May 11, 2023

      Migrating to Azure isn’t necessarily the toughest part of a successful cloud migration, explains our guest columnist Many organisations succeed at deploying resources in or migrating to Microsoft Azure. But many of those same organisations fail to en

      6 min read

    • Article

      Assessing Ease Of Use

      Jul 28, 2020

      Assessing Ease Of Use
      3 min read

    • Article

      Enterprise Soaring Success

      Aug 27, 2019

      Enterprise Soaring Success
      7 min read

    • Article

      Getting The edge

      Feb 25, 2021

      Getting The edge
      7 min read

    • Article

      Craft A Perfect Personal Document Library

      Jan 26, 2023

      There are countless apps available that can be used to organise your notes and also many word processors designed to help you create smart-looking documents, but Craft aims to do both in style. The idea is to include a huge number of advanced documen

      1 min read

    • Article

      Opinion

      Aug 20, 2024

      Italo Vignoli is one of the founders of LibreOffice and the Document Foundation. “Think about the personal and confidential information in your office suite documents; it’s essential your office suite respects user privacy. LibreOffice does not ask y

      3 min read

    • Article

      Choices, Choices

      Apr 5, 2022

      Matt Yonkovit is the head of Open Source Strategy at Percona “Many modern programs are built with dozens of different open source components, constructed like LEGO from pre-built blocks. This approach to picking and choosing the best tools and compon

      1 min read

    • Article

      Choices, Choices

      Apr 5, 2022

      Matt Yonkovit is the head of Open Source Strategy at Percona “Many modern programs are built with dozens of different open source components, constructed like LEGO from pre-built blocks. This approach to picking and choosing the best tools and compon

      1 min read

    • Article

      FLASK Web Frameworks

      Jun 4, 2019

      The main focus of Python has always been to get you cracking on with your coding – the language was never made for web programming. However, this has just made it more interesting to extend the language for the web, or to create an interface to web-b

      9 min read

    • Article

      Tools Of The Trade

      Mar 29, 2018

      Tools Of The Trade
      4 min read

    • Article

      Code An Admin Back-end In Django

      Dec 13, 2022

      Credit: www.djangoproject.com OUR EXPERT Matt Holder has been a fan of the open source methodology for over two decades and uses Linux and other tools where possible. More featurepacked source code for this project can be downloaded from https://

      6 min read

    • Article

      Cloudy With No Chance Of Erp

      Nov 11, 2019

      ERP (enterprise resource planning) was born around the time the first ‘[Something] for Dummies’ book was published*. It’s typically inflexible, uncompromising software designed for large businesses, like banks, large corporations, manufacturing and s

      2 min read

    • Article

      It’s Collabora time!

      May 2, 2023

      It’s Collabora time!
      8 min read

    Related categories

    Reviews for Implementing Power BI in the Enterprise

    4.5/5

    2 ratings0 reviews

    What did you think?

    Tap to rate

    Review must be at least 10 words

      Book preview

      Implementing Power BI in the Enterprise - Greg Low

      Implementing Power BI®

      in the

      Enterprise

      Dr Greg Low

      SQL Down Under Pty Ltd

      Implementing Power BI® in the Enterprise

      Dr Greg Low

      SQL Down Under Pty Ltd

      @greglow

      https://enterprisepowerbibook.sqldownunder.com

      First edition June 2021

      Power BI is a registered trademark of Microsoft Corporation

      Cover Awesome image by the amazing Pang Yuhao

      (c/- Unsplash https://unsplash.com/photos/OPwYu6nhWFc )

      This eBook is copyright material and must not be copied, reproduced, transferred, distributed, leased, licensed, or publicly performed or used in any way except as specifically permitted in writing by the publishers, as allowed under the terms and conditions under which it was purchased or provided as strictly permitted by applicable copyright law. Any unauthorized distribution or use of this text may be a direct infringement of the author’s and publisher’s rights and those responsible may be liable in law accordingly.

      I have done my best to make this eBook as error free as possible at the time of publication, but I do not promise that it is error free or that anything we describe will work for you or continue to work for you. Every one of these technologies is a moving target. This eBook does not replace professional advice.

      Note from the author:

      I have worked with data for decades. This eBook is a compilation of the lessons I have learned when implementing Power BI based systems across a variety of organizations.

      We intend to keep enhancing and upgrading this book. If you have feedback for it, please send that to [email protected]

      About the Author Dr Greg Low

      A person smiling for the camera Description automatically generated with low confidence

      Greg is one of the better-known database consultants in the world. In addition to deep technical skills, Greg has experience with business and project management and is known for his pragmatic approach to solving issues. His skill levels at dealing with complex situations and his intricate knowledge of the industry have seen him cut through difficult problems.

      Microsoft has specifically recognized his capabilities and appointed him to the Regional Director program. They describe it as consisting of 150 of the world's top technology visionaries chosen specifically for their proven cross-platform expertise, community leadership, and commitment to business results.

      Greg leads a boutique data consultancy firm called SQL Down Under. His clients range from large tier-1 organizations to start-ups.

      Greg is a long-term Data Platform MVP and considered one of the foremost consultants in the world on Microsoft data-related technologies. He has provided architectural guidance for some of the largest SQL Server implementations in the world and helped them to resolve complex issues. Greg was one of the two people first appointed as SQL Server Masters worldwide. Microsoft use him to train their own staff. He has worked with Power BI since before it was initially released.

      A talented trainer and presenter, Greg is known for his ability to explain complex concepts with great clarity to people of all skill levels. He is regularly invited to present at top level tier-1 conferences around the world. Greg's SQL Down Under podcast has a regular audience of over 40,000 listeners.

      Outside of work and family, Greg's current main passion is learning Mandarin Chinese, determined to learn to read, write, speak, and understand it clearly.

      Graphical user interface, text, application, email Description automatically generated

      Need to learn about data? SQL Down Under offer online on-demand courses that you can take whenever you want. We have many data-related courses.

      You can learn with Greg right now!

      We are rapidly expanding our list of courses.

      Check us out now at https://training.sqldownunder.com

      Need assistance with a project? Want help with the architectural design, or with getting a project back on track?

      Contact https://sqldownunder.com to see how we can help.

      TABLE OF CONTENTS

      About the Author Dr Greg Low

      Introduction

      What this book is and is not about

      Useful background knowledge

      Structure of the book

      Chapter 1: Power BI Cloud Implementation Models

      Overview

      Cloud-Native Clients

      Characteristics

      Starting Point

      Typical Implementation

      Tools Used

      Azure SQL Database

      Cloud-Friendly Clients

      Characteristics

      Starting Point

      Typical Implementation

      Tools Used

      Cloud-Conservative Clients

      Characteristics

      Starting Point

      Typical Implementation

      Enterprise Gateway

      SQL Server Integration Services (SSIS)

      Tools Used

      Cloud-Unfriendly Clients

      Characteristics

      Starting Point

      Typical Implementation

      Tools Used

      Chapter 2: Other Tools That I Often Use

      Overview

      SQL Server Reporting Services

      SSRS Tooling

      Tabular Editor

      Vertipaq Analyzer

      DAX Studio

      Azure Storage Explorer

      Chapter 3: Working with Identity

      Overview

      Identity Aims

      Azure Active Directory

      Azure AD Core Directory (AAD)

      Hybrid AD

      Azure AD Business to Business (AAD B2B)

      Azure AD Business to Consumer (AAD B2C)

      AAD and Azure Databases

      Azure SQL

      Azure Analysis Services

      Service Principals

      Managed Service Identities

      Chapter 4: Do you need a Data Warehouse?

      Overview

      Cleansing Data

      Appropriate Naming

      Consistency

      Schema Design

      Data Types

      Invalid Data

      Missing Data

      Unrealistic Data

      Rounding Issues

      Where to Cleanse Data

      Aligning Data from Multiple Systems

      Mapping and Reference Tables

      Where to Align or Map Data

      Data Versioning

      Where Should You Version Data?

      Maintaining Historical Data

      Where to Maintain Historical Data

      What I do: Data Warehouse

      Cloud-Native and Cloud-Friendly Customers

      Cloud-Conservative and Cloud-Unfriendly Customers

      Chapter 5: Implementing the DataModel Schema

      Overview

      Database Schemas

      Object Schemas

      Schemas for Grouping

      Schemas for Security

      DataModel Schema in the Data Warehouse Structure

      DataModel Schema Design Goals

      Design Rules

      Table Structures

      Appropriate Naming

      Data Types

      Table Keys and Relationships

      Versioning Table Rows

      Additional Columns

      Missing Rows

      Lineage

      Table Compression

      Row Compression

      Page Compression

      Columnstore Indexes

      Chapter 6: Implementing the Analytics Schema

      Overview

      Analytics Schema in the Data Warehouse Structure

      Analytics Schema Design Goals

      Minimizing the Attack Surface

      Embedding T-SQL

      Outcome of the Analytics Schema

      Excluding DataModel Data

      Design Rules

      Automating View Creation

      Installing SDU Tools

      Date Tables or Views

      Automating Date Table Creation

      Local DateTime in Azure SQL Database

      Automatic Data Subsetting

      What I Want to Achieve

      My Workaround

      Chapter 7: Using DevOps for Project Management and Deployment

      Overview

      Project Management and Deployment Goals

      Azure DevOps

      Project Wiki

      Azure Boards

      Azure Repos

      Azure Pipelines

      Infrastructure as Code

      Azure Test Plans

      Azure Artifacts

      Azure Monitor / Azure Log Analytics

      GitHub

      GitHub vs Azure DevOps

      Database Projects

      Database Projects in SSDT

      Building a Database Project

      Change Management Advice

      Chapter 8: Staging, Loading and Transforming Data

      Overview

      Accessing Source Data

      What I do: File Processing

      What I do: Database Data

      Transactional Replication

      Transactional Replication Pros and Cons

      Common Data Flow

      Linked server and custom SQL Server Agent job

      Integration Services Package

      Availability Group Replica

      Creating the Staging Schema

      Staging Schema in the Data Warehouse Structure

      What I do: Staging Schema

      NULL or NOT NULL

      Using External Tables to Access Source Data

      What I do: Linked Servers

      Indexing Staged Tables

      Creating the DataLoad Schema

      DataLoad Schema in the Data Warehouse Structure

      What I do: DataLoad Schema

      What I do: Automation Metadata

      Loading File Data into Staging Tables

      What I do: Logging

      Loading Staged Data

      Incremental Data Loading

      Loading and Transforming the Staged Data

      Chapter 9: Implementing ELT and Processing

      Overview

      ELT Tooling

      What I do: On-Premises

      What I do: Azure

      Azure Data Factory (ADF) Overview

      SSIS Packages in ADF

      ADF Project Structure

      Creating ADF Pipelines

      Activities

      Pipeline Parameters

      Pipeline Variables

      Linked Services

      Data Sets

      Timeouts

      Integration Runtimes

      Testing Pipelines

      ADF Triggers and Scheduling

      Schedule Triggers

      Tumbling Window Triggers

      Storage Event Triggers

      Other Triggers

      Other Scheduling Methods

      ADF Security and Monitoring

      Database and Service Connection Security

      Monitoring ADF and Creating Alerts

      Integration with Source Control

      ADF Deployment

      Publishing from Code

      ADF Data Flows

      Chapter 10: Implementing the Tabular Model

      Overview

      Structure and Tools

      What I do: Tabular Data Model

      What I do: Power BI

      What I do: Tooling

      Tabular Projects and Source Control

      Analysis Services Project Tooling

      Projects and Source Control

      Creating Projects and Solutions

      Configuring Workspace Databases

      Configuring Compatibility Level

      Configuring Data Model Properties

      Multi-User Development

      Initial Loading of Tables

      Authentication to Analysis Services

      Authentication to Data Sources

      Selecting and Transforming the Tables

      Initial Commit to Git

      Core Aspects of Tabular Models

      Relationships

      Calculated Tables

      Hiding Columns and Tables from Clients

      Measures

      Summarization for Columns

      Correct Level for Computations

      Mark as Date Table

      Hierarchies

      Data Formats

      Data Categories

      Report Measure Table

      Testing and Deployment Options

      Testing During Development

      Deployment

      Chapter 11: Using Advanced Tabular Model Techniques

      Overview

      Processing Data Models

      Database Processing Option – Default

      Database Processing Option – Full

      Database Processing Option – Clear

      Database Processing Option – Recalc

      Table Processing Options

      Scripting Processing Steps

      Scheduling Processing

      On-Premises or VM-Based – SQL Server Agent

      On-Premises or VM-Based – Integration Services

      Azure Data Factory Based Processing

      Implementing Row Level Security

      Managing Roles

      Role Database Permissions

      Role Members

      Role Table Permissions

      Role Row Filters

      Dynamic User Security

      Testing Roles and Row Filters

      Object Level Security

      Labeling Sensitive Data

      Perspectives

      Translations

      Scripting Database with TMSL

      Partitioning Data

      Database Table Partitions

      Tabular Data Model Partitions

      Table Partition Example

      Optimizing Data Model Size

      Minimizing Data Model Size

      Using DAX Studio and Vertipaq Analyzer

      Configuring Encoding Hints

      Additional Concepts

      Parent-Child Relationships

      Many to Many Relationships

      Checking for Best Practices

      Chapter 12: Connecting Power BI and Creating Reports

      Overview

      Connecting to the Data Model

      Adding Report Measures

      Using Composite Data Models

      Summary

      Glossary

      Introduction

      A picture containing text, indoor Description automatically generated

      What this book is and is not about

      Thanks for reading this book. To make sure we are on the same page (pun intended), I would like to start by spelling out what this book is and is not about. This book is not an introduction to Power BI. It is also not a book that explains how to create the best visualizations in Power BI. In fact, it does not cover much about building reports.

      This book is about putting a framework in place so that you can build great reports using Power BI. It is about all the things that you need to have in place to make it easy to build those great reports, and about doing that in a way that can work in enterprises. Power BI is an amazing tool that is so easy to get started with. But when it comes to making it fit into an enterprise way of thinking, some planning is needed. It is important to understand that Power BI was designed to appeal immediately to power users. It was not targeted at enterprise IT developers.

      I see it a bit like I used to see Microsoft Access years ago. In the data community, the use of Access as a database is almost like a running joke as it is not really considered a database. Worse, many companies have challenges with data that has been spread across Access databases all over the organization in an uncontrolled way. I do not see Access that way. I was never a great fan of it, but I know that there are many applications today that would never have existed if the people who started them, were not able to use Access. It was an enabling technology that let ideas get off the ground.

      Power BI today has a similar potential issue. It enables so many people to get data and reporting ideas started. For many people, that might also be all that has ever needed. Enterprises, though, can end up viewing this differently. Silos of data with varying quality and management are not going to be popular.

      In this book, I will show you how I structure data models, how I stage and process the data, and how I secure it. I will also show you some techniques that I use to automate the process of building the data models.

      There is no one right or wrong way to implement Power BI in an enterprise. In this book, I will tell you how I do it, and I have been implementing many successful projects. I cannot also promise you that I will not think differently about aspects of it in the future. I might. All technology changes fast but Power BI changes faster than most. You might disagree with some of my opinions that I provide in the book. That is fine too. Take what you find useful. What I can tell you is this is how I have implemented a lot of projects, and very successfully.

      Useful background knowledge

      When you are reading this book, it will certainly help if you have some existing background with database

      Enjoying the preview?
      Page 1 of 1