Microsoft power BI cookbook : creating business intelligence solutions of analytical data models, reports, and dashboards / Brett Powell.

Format:

Book

Author/Creator:

Powell, Brett, author.

Language:

English

Subjects (All):

Cookbooks.

Physical Description:

1 online resource (786 pages)

Edition:

1st ed.

Place of Publication:

Birmingham, England ; Mumbai, [India] : Packt Publishing, 2017.

Biography/History:

Powell Brett: Brett Powell is the owner of and business intelligence consultant at Frontline Analytics LLC, a data and analytics research and consulting firm and Microsoft Power BI partner. He has worked with Power BI technologies since they were first introduced as the PowerPivot add-in for Excel 2010 and has been a Power BI architect and lead BI consultant for organizations across the retail, manufacturing, and financial services industries. Additionally, Brett has led Boston's Power BI User Group, delivered presentations at technology events such as Power BI World Tour, and maintains the popular Insight Quest Microsoft BI blog.

Summary:

Get more out of Microsoft Power BI turning your data into actionable insightsKey FeaturesFrom connecting to your data sources to developing and deploying immersive, mobile-ready dashboards and visualizations, this book covers it allOver 90 hands-on, technical recipes, tips, and use cases from across the Power BI platform including the Power BI Service and Mobile ApplicationsProven development techniques and guidance for implementing custom solutions with DAX and M languagesBook DescriptionMicrosoft Power BI is a business intelligence and analytics platform consisting of applications and services designed to provide coherent, visual and interactive insights of data. This book will provide thorough, technical examples of using all primary Power BI tools and features as well as demonstrate high impact end-to-end solutions that leverage and integrate these technologies and services. Get familiar with Power BI development tools and services, go deep into the data connectivity and transformation, modeling, visualization and analytical capabilities of Power BI, and see Power BI’s functional programming languages of DAX and M come alive to deliver powerful solutions to address common, challenging scenarios in business intelligence. This book will excite and empower you to get more out of Power BI via detailed recipes, advanced design and development tips, and guidance on enhancing existing Power BI projects.What you will learnCleanse, stage, and integrate your data sources with Power BIAbstract data complexities and provide users with intuitive, self-service BI capabilitiesBuild business logic and analysis into your solutions via the DAX programming language and dynamic, dashboard-ready calculationsTake advantage of the analytics and predictive capabilities of Power BIMake your solutions more dynamic and user specific and/or defined including use cases of parameters, functions, and row level securityUnderstand the differences and implications of DirectQuery, Live Connections, and Import-Mode Power BI datasets and how to deploy content to the Power BI Service and schedule refreshesIntegrate other Microsoft data tools such as Excel and SQL Server Reporting Services into your Power BI solutionWho this book is forThis book is for BI professionals who wish to enhance their knowledge of Power BI beyond and to enhance the value of the Power BI solutions they deliver to business users. Those who are looking at quick solutions to common problems while using Power BI will also find this book to be a very useful resource .Some experience with Power BI will be useful.

Contents:

Cover

Title Page

Copyright

Credits

Foreword

About the Author

About the Reviewers

www.PacktPub.com

Customer Feedback

Table of Contents

Preface

Chapter 1: Configuring Power BI Development Tools

Introduction

Configuring Power BI Desktop options and settings

Getting ready

How to do it...

Installing and running Power BI Desktop

Configuring Power BI Desktop options

How it works...

There's more...

See also

Power BI's advantages over Excel

Power BI Security and Data Source Privacy

Installing the On-Premises Data Gateway

Hardware and network configuration

Installation of on-premises gateway

Gateway recovery key

See also...

Installing Power BI Publisher for Excel

Installation of Power BI Publisher for Excel

Installing and Configuring DAX Studio

Installation of DAX Studio

Configuration of DAX Studio

Guy in a Cube video channel

Chapter 2: Accessing and Retrieving Data

Viewing and analyzing M functions

Formula Bar

Advanced Editor window

Query folding

M query structure

Lazy evaluation

Partial query folding

Limitations of query folding

M language references

Establishing and managing connections to data sources

Isolate data sources from individual queries

Query groups

Manage source credentials and privacy levels

Data Source settings

Data source privacy settings

Building source queries for DirectQuery models

How to do it.

Applying M transformations with DirectQuery models

DirectQuery project candidates

DirectQuery performance

Importing data to Power BI Desktop models

Denormalize a dimension

Provide automatic sorting

One GB dataset limit and Power BI Premium

Applying multiple filtering conditions

Query filter example steps

Filtering via the Query Editor interface

Choosing columns and column names

Identify expensive columns

Select columns

Rename columns

Column memory usage

Fact table column eliminations

Column orders

Transforming and cleansing source data

Remove duplicates

Update a column through a join

Creating custom and conditional columns

Create a dynamic banding attribute

Create a formatted name column

Comparing the current and previous rows

Conditional expression syntax

Case sensitivity

Conditional expression evaluation

Query folding of custom columns

Add column from example

Conditional columns interface

DAX calculated columns

Error handling and comments

Integrating multiple queries

Consolidate files

Self-joining querying

Nested join versus flat join

Append multiple files

Combine binaries

Staging queries versus inline queries

Choosing column data types

Remove automatic type detection steps

Align relationship column data types.

Add numeric columns from text columns

Use fixed decimal number for precision

Automatic data type detection

Numeric data types

Power BI Desktop automatic time intelligence

Data type impacts

Date with locale

Percentage data type

Visualizing the M library

Chapter 3: Building a Power BI Data Model

Designing a multi fact data model

Setting business expectations

Four-step dimensional design process

Data warehouse and implementation bus matrix

Choose the dataset storage mode - Import or DirectQuery

In-Memory mode

DirectQuery mode

DAX formula and storage engine

Project ingestion questions

Power BI delivery approaches

Implementing a multi fact data model

SQL view layer

M queries in Power BI Desktop

Create model relationships

Author DAX measures

Configure model metadata

Shared views

Handling one-to-many and many-to-many relationships

Single, bidirectional, and CROSSFILTER()

Single direction relationships

Bidirectional relationship

CROSSFILTER() Measure

Many-to-many relationships

Bidirectional cross-filtering for many-to-many

Ambiguous relationships

CROSSFILTER()

DirectQuery supported

Assigning data formatting and categories

Data formats

Data category

Model level settings

Configuring Default Summarization and sorting

Sort By Column

DAX Year-Month sorting

DAX Ranking Sort

Default Summarization

How it works.

Default Summarization

Quick measures

Setting the visibility of columns and tables

Isolate measures from tables

Measure home tables

Hiding hierarchy columns

Group visibility

Row level security visibility

Visibility features from SSAS

Embedding business definitions into DAX measures

Sales and cost metrics

Margin and count metrics

Secondary relationships

Date relationships

Measure definitions

Measure names and additional measures

Enriching a model with analysis expressions

Pricing analysis

Geometric mean at all grains

Building analytics into data models with DAX

Cross-selling opportunities

Accessories but not bike customers

Bike only customers

Active verus inactive customers

Actual versus budget model and measures

Filter Context Functions

SUMMARIZECOLUMNS()

Integrating math and statistical analysis via DAX

Correlation coefficient

Goodness-of-Fit test statistic

Correlation coefficient syntax

Goodness-of-Fit logic and syntax

Supporting virtual table relationships

Segmentation example

Summary to detail example

Actual versus plan

Year and month selected

Virtual relationship functions

Multiple dimensions&amp

#160

Alternatives to virtual relationships

Creating browsable model hierarchies and groups

Create hierarchy columns with DAX

Implement a hierarchy

Create and manage a group

DAX parent and child functions

Include other grouping option

Model scoped features

DAX calculated columns as rare exceptions

Natural hierarchies versus unnatural hierarchies

Grouping dates and numbers

DirectQuery models supported

Chapter 4: Authoring Power BI Reports

Building rich and intuitive Power BI reports

Stakeholder Matrix

Report planning and design process

Report Design Example

European Sales and Margin Report Page

European country sales and margin report page

European sales report design

Power BI report design checklist

Custom visuals

Published Power BI datasets as data sources

Creating table and matrix visuals

Table visual exceptions

Identifying blanks in tables

Matrix visual hierarchies

Matrix visual navigation

URL and mail to email support

Percent of total formatting

Measures on matrix rows

Data bar conditional formatting

Utilizing graphical visualization types

Choosing visual types

Waterfall chart for variance analysis

Line chart with conditional formatting

Shape map visualization

Shape map

Enhancing exploration of reports

Drillthrough report page requirements

Enable Cortana integration and Q&amp

amp

A

Create featured Q&amp

A questions

Parameterized Q&amp

A report

Cortana integration

Drillthrough Report Pages

Report themes

Report theme JSON files

Conversational BI - mobile support for Q&amp

Integrating card visualizations

Getting ready.

How to do it.

Notes:

Includes index.

Description based on online resource; title from PDF title page (ebrary, viewed October 23, 2017).

ISBN:

1-78829-378-9

OCLC:

1005565948