The Power of Power BI Marc Schöni, Microsoft Schweiz Meinrad Weiss, Trivadis AG

April 2014 BASEL

1

BERN BRUGG

LAUSANNE

ZUERICH

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

DUESSELDORF

FRANKFURT A.M.

FREIBURG I.BR.

HAMBURG

MUNICH

STUTTGART

VIENNA

2

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

The company Trivadis is a market leader in IT consulting, system integration, solution engineering and the provision of IT services focusing on and technologies in Switzerland, Germany and Austria. We offer our services in the following strategic business fields:

OPERATION

Trivadis Services takes over the interacting operation of your IT systems. 3

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

More than 600 Experts 12 Trivadis branches and more than 600 employees 200 Service Level Agreements

Hamburg

Over 4,000 training participants Düsseldorf

Research and development budget: CHF 5.0 / EUR 4 million

Frankfurt

Financially self-supporting and sustainably profitable

Stuttgart Freiburg

Basel

Brugg

Bern Zürich Lausanne 4

4

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Wien München

Experience from more than 1,900 projects per year at over 800 customers

All levels, one partner

5

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Easier through holistic strategy We reduce the complexity of the challenges for our customers through the interaction: Build

 Own methods (Best Practices) and products  Translation of business requirements into IT

Plan

 Comprehensive Know-how (Plan-Build-Run-Train)  Project support on entire lifecycle  Transfer of knowledge through practice proven training 6

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Run Train

Some of over 800 customers

7

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Five reasons 

Single-source IT consulting and system integration



Combined technology and specialised expertise



19 years of experience and 1,900 successful Microsoft and Oracle-based projects per year



Accompaniment throughout the entire IT project lifecycle at every level



Transfer of know-how with hands-on training

efficient – experienced – personal 8

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Let‘s start!

9

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Agenda 

Introduction



Excel as a Client Tool



Power View & Power Map



Power Pivot  Tabular Model  Introduction to DAX  Power View & Power Map



10

Power Query

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014



Empowering the Enterprise     



Rules & (Data) Governance SharePoint and Office 365 Scale to the Max Master Data Solution Big Data

Next Steps

Introduction

11

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

The world of data has changed 10x

4.3

increase every five years

connected devices per adult

85%

from new data types

Data explosion

Consumerization of IT

27%

using social media input

By 2015, organizations integrating high-value, diverse, new information types and sources into a coherent information management infrastructure will outperform their industry peers financially by more than 20%. – Gartner, Regina Casonato et al., “Information Management in the 21st Century”

Evolution of BI

Evolution of BI

Data extraction

Building models

The classic way

The hybrid way

IT builds everything from top to bottom

IT builds the data models and some standardized reports; users leverage models

Traditional

The agile way Self-service IT controls access to source data, enabling users to build models and visualizations

Creating reports IT/Developer 2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Power User

End User

Scope

Classic vs. Self-service BI

15 years of innovation

The Power of Power BI R 1.01

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Data Sources

Data Preparation

Data Storage

Extract

OLTP Database

Data Analysis

Data Access

Reports

Portals

Transform

- Tablix - Drill - Visualisations

Data Mining

Load Data Mart

ERP Database

Graphs

- Charts - Ad-hoc - Dynamic

Departmental Database

Filter

Data Warehouse

Cleanse

OLAP

Dashboard

In-Memory Analytics

Schedule

- Mash-up - Collaborate - Search

Power View MDS DBMS

Power Query

SSIS

Power Pivot

Power Pivot

DBMS

SSAS

Power Map

Excel (Pivot) Reporting Services

Data Sources

Data Preparation

Data Storage

Extract

OLTP Database

Data Analysis

Data Access

Reports

Portals

Transform

- Tablix - Drill - Visualisations

Data Mining

Load Data Mart

ERP Database

Graphs

- Charts - Ad-hoc - Dynamic

Departmental Database

Filter

Data Warehouse

Cleanse

OLAP

Dashboard

In-Memory Analytics

Schedule

- Mash-up - Collaborate - Search

Power View MDS DBMS

Power Query

SSIS

Power Pivot

Power Pivot

DBMS

SSAS

Power Map

Excel (Pivot) Reporting Services

Trivadis Management Reporting & Analysis 19

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

The Initial Situation (2009) Time consuming reporting

Low degree of transparency  Different and contradictory figures from various sources (Excel islands) Manual and partly inefficient controlling processes

 Reorientation necessary

20

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

The Development – What have we done? Analysis (incl. technology evaluation)

1 Month

Trimaran prototype for pilot users

2 Months

Modular development in small steps

Every 3 Months

-

21

Services, revenue (SAP ERP) Planning (budget) Migration existing reporting Cost and profit/loss (SAP ERP) Employee master data (SAP ERP) Opportunities / leads (SAP CRM) Automated monthly closure Standard Cockpits Planning (rolling forecast) Project profitability & standard cost rates Demo version and reference story 2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Evaluated Vendors and Products

22

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Reasons for Microsoft State of the art products (End-to-End)

Know-How  Within Trivadis  Worldwide (Newsgroup, Training, …) Moderate License prices Excel Integration Customer references Stability, Performance, Scalability Big Vendor 23

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

The Development Organisation

Users Ad-Hoc Reporting

Executive Board (Steering Commitee) Sponsoring Priorities

Special Development Technical Knowhow Infrastructure Concepts

Operations Zürich / Bern (Development Partner) 24

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Standard Reports Development Support

Controlling (Project Owner)

The Tasks – Who is using Trimaran within Trivadis? 1.

Monthly closure / controlling

2.

Internal accounting and Profit/loss

3.

4. 5.

25

Planning  Budget  Forecast  Target Deviation analysis Controlling Calculations  Revenue accruals  Bonus accruals  Various cost allocations  Etc. 2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

The Usage 12 locations >170 users 

Executive Board



Location Managers



Unit Managers



Project Managers



Key Account Managers



BackOffice (HR, Accounting, Order Processing, Controlling Etc.)

> 380’000 Reports Total ~ 500 reports per day

26

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Trimaran Architecture

Integration Services

Integration Services

SQL Server

27

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Analysis Services

Excel / Reporting Services

Wiki

Reporting Services Sample

30

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Standard Lifecycle

Quality! Duration?

Flexibility? Agility?

Agile Lifecycle 24 hours Product Backlog

Quality… Duration…

4 – 6 weeks

Flexibility! Agility! Sprint Backlog

Product Increment

The Benefits – What is Trivadis gaining? Simple and fast reporting

Highly automated effortless processes

Modular and future-proof sustainable development

High degree of transparency in corporate management

33

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Excel as a Client Tool

34

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Data Sources

Data Preparation

Data Storage

Extract

OLTP Database

Data Analysis

Data Access

Reports

Portals

Transform

- Tablix - Drill - Visualisations

Data Mining

Load Data Mart

ERP Database

Graphs

- Charts - Ad-hoc - Dynamic

Departmental Database

Filter

Data Warehouse

Cleanse

OLAP

Dashboard

In-Memory Analytics

Schedule

- Mash-up - Collaborate - Search

Power View MDS DBMS

Power Query

SSIS

Power Pivot

Power Pivot

DBMS

SSAS

Power Map

Excel (Pivot) Reporting Services

A very popular tool ;)

1.000.000.000 Users worldwide (Estmation by Microsoft)

 14% of Worldpopulation

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing (Power) Pivot Reporting Excel reporting based on the Power Pivot or SSAS data models:  PivotTables and PivotCharts  CUBE functions  Power View

These report types can be rendered by Excel Services in a web browser

 Apps for Office If the workbook will be rendered by Excel Services, only use Excel client features that are supported

37

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing (Power) Pivot Reporting Excel Client Features Not Supported by Excel Services  VBA macros

 ActiveX controls  External formula references (to other workbooks)  Query tables, SharePoint lists, Web queries, text queries

 Cell comments  OfficeArt technology Shapes, WordArt, SmartArt, organization chart, diagrams, signature lines, ink annotations

 Macro-enabled workbooks (.xlsm)  Protected Workbooks

38

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing (Power) Pivot Reporting PivotTables

The PivotTable Fields exposes the resources of the data model:  Calculated fields, KPIs, and hierarchies – grouped by table

Four drop zones are used to configure the layout of the PivotTable  Filters Add Slicers for interactive filtering

 Columns  Rows

 Values – calculated fields only 39

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing (Power) Pivot Reporting PivotCharts

PivotCharts can be based on a PivotTable

Can be placed on the same or separate worksheet as the PivotTable Configuration is similar to the PivotTable Two drop zones change name and behavior:  Column Labels becomes Legend Fields  Row Labels becomes Axis Fields

40

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing (Power) Pivot Reporting CUBE Functions

Excel includes seven CUBE functions that can retrieve data model members and values:  CUBEMEMBER returns a member from a cube dimension  CUBEVALUE returns a value from the cube, and can be filtered using report filters and slicers  CUBESET returns a set of members  CUBESETCOUNT returns the count of members in a set  CUBERANKEDMEMBER returns the nth member in a set  CUBEMEMBERPROPERTY returns a member property  CUBEKPIMEMBER returns a KPI property 41

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

The Power Pivot data model does not support member properties

Introducing (Power) Pivot Reporting CUBE Functions (continued)

The CUBE functions allow creating Excel report layouts cell-by-cell based on an Analysis Services BI Semantic Model (BISM)  Only works for data models that can be queried with Multidimensional Expressions (MDX)  Power Pivot and Analysis Services Cubes can be queried by using MDX

Report layouts can allow user input by using slicers, timelines and cell values (simulating parameters) The CUBE function arguments can be defined by using expressions – both by using Excel expressions and MDX expressions

42

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing (Power) Pivot Reporting CUBE Functions (continued)

A CUBE function report layouts can be created by converting a PivotTable to formulas – it is a one-way conversion, and can be helpful for beginners learning to write CUBE formulas  Learning MDX to retrieve set of members will be helpful

Using the CUBE functions can work well for some report designs, and not so well for others

 Formulas for cells must be created at design time, so report layouts work best for known set sizes (four quarters for a year, top 10 customers, etc.)

43

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing (Power) Pivot Reporting Apps for Office

Apps for Office are new in Excel 2013

Allow visualizing data from Excel tables or worksheet ranges They carry a light footprint and use web standards-based technologies such as HTML5, JavaScript, CSS3, XML, and REST API Can be embed into Office documents and use a browser control in a secure app runtime environment Apps are discoverable and are downloaded from the Office Store Some apps are free

44

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Enhancing Reports with Excel Features Report layouts can be enhanced with additional features supported by Excel Services:  Charts  Embedded Images  Conditional Formatting  Slicers  Sparklines

 Hyperlinks  Outlining

45

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Enhancing Reports with Excel Features Charts

A rich range of charts can be added and based on worksheet data

There are numerous options to configure the look and feel

46

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Enhancing Reports with Excel Features Embedded Images

Images can be inserted into worksheets

Ideal for corporate logos, etc. Excel Services does not support linked images

47

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Enhancing Reports with Excel Features Conditional Formatting

Numerous rule types can be used to format cells:  Highlight Cells  Top/Bottom

 Data Bars  Color Scales  Icons, ideal for KPI status

48

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Enhancing Reports with Excel Features Slicers

Visual controls that support quick, easy, interactive filtering

Position on a worksheet like charts and shapes Slicers can pass selected member(s) to:  PivotTables  The CUBEVALUE function

Slicers can:     49

Be referenced by multiple items Be parameterized Be formatted using styles Visually indicate items with no data 2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Enhancing Reports with Excel Features Timeline

Timelines allows filtering by calendar time periods

It requires a visible Power Pivot data model column that stores date values  This could remove the need to create a date table and/or a calendar hierarchy

It allows filtering by:  An individual calendar year, calendar quarter, month or date, or  A contiguous range of calendar years, calendar quarters, months or dates

Like slicers, timelines can be:  Connected to multiple PivotTables  Passed into the CUBEVALUE function 50

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Enhancing Reports with Excel Features Sparklines

Represent a workbook data range, and typically a time series

Types include:

Can be customized with the Axis Options and formatted using styles Use merged cells to enlarge their appearance

51

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Enhancing Reports with Excel Features Hyperlinks

Hyperlinks are useful for accessing and viewing related information

Scenarios:  Open a document, file, or web page in a new window  Start an email program and create a message

 Navigate to different locations within the current workbook  Open another Excel workbook in web browser view to a specific location

52

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Enhancing Reports with Excel Features Outlining

Excel can create an outline of data to allow users to expand/collapse levels of details Available from the Data ribbon tab, inside the Outline group

Supports up to eight levels 53

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Resources Excel Cube Functions wiki site  http://www.excelcubefunctions.com

Book: “Microsoft SQL Server 2008 MDX Step by Step”  Publisher: Microsoft

 Author: C. Ryan Clay

Book: “Excel Dashboards and Reports”  Publisher: Wiley  Author: Michael Alexander

54

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Power View

55

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power View Power View is a SQL Server Reporting Services authoring experience now available in Excel 2013

Power View in Excel 2013 requires a tabular data model, including a Power Pivot data model Report sheets can be added to the workbook and configure to produce interactive data exploration, visualization, and presentation experiences 

Highly visual design experience



Rich meta-driven interactivity



Presentation-ready at all times

It can enable intuitive ad-hoc reporting for business users Available also in SharePoint 2013 with the Reporting Services add-in

56

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power View Example

57

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power View System Requirements

Power View for Excel 2013 requires:  Office Professional Plus edition, or  Office 365 Professional Plus edition

Silverlight

The Map data visualization requires internet connectivity (Bing Maps)

58

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power View Power View Audiences

Business Analysts

Users

• Create Power View reports

• Search for, and view and interact with, published workbooks that contain Power View reports

• Publish workbooks consisting of reports

59

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Creating Power View Reports A workbook can consist of multiple Power View reports

A Power View report is created by using the Insert ribbon tab  If a range of data is selected when creating the report, the range will be automatically added to the workbook data model, and the report will display the data  If no range is selected, and a workbook data model is created, the Power View Fields will display the data model fields

All reports commence by creating a table, and this is achieved in the Power View Fields by selecting a table (if default field list was defined), or checking individual fields 60

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Creating Power View Reports (continued) A table can be converted to the different data visualizations (charts or map) and its layout configured  It is possible to drag fields into the layout zones  Also, selecting a field reveals a down arrow, to select the placement zone  Different visualization types have a different layouts

To create a new table, click on a blank area of the report canvas 61

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Creating Power View Reports Data Visualization Types

Table, Matrix and Card (single visualization per table record)  Table has fixed columns  Matrix allows grouping on columns

Charts:  Bar (stacked, 100%, clustered)  Column (same as bar, but vertical)  Other (line, scatter, other) The scatter chart can be animated across time

Maps  The Map requires internet connectivity to communicate with Bing Maps

62

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Creating Power View Reports Enhancing Reports

Reports can be enhanced with:  Themes (fixed styles of fore and background colors, and fonts)  Images – free-standing and background  Textbox labels

Numeric table values can be formatted Different data visualizations have different layout abilities  Refer to the available commands on the Layout ribbon tab when configuring a data visualization

63

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Creating Power View Reports Filtering Reports

Reports can be filtered in different ways:  Filter the entire report in the Filters pane  Drag and drop fields and set filter conditions

 Filter a single data visualization  Select the filter icon in the top right corner of the data visualization

 Add a slicer  Single-column table can be converted to a slicer to allow user interactive filtering  Multi-selection is possible by pressing the control key

 Select chart areas (pie segments, bars or lines) 

64

This will filter the entire view, and can be reset by clicking a blank area of the chart

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Creating Power View Reports Sorting Data

Table columns can be sorted by clicking the column header  First click is ascending, the second click descending, etc.

Sort can also be configured by clicking the options in the top right corner of the data visualization  Fields and sort order can be set

65

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Power Map

66

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power Map Power Map is a new add-in for Excel to interactively visualize spatial data in 3D

Requires a tabular data model, including a Power Pivot data model Animated tours can be created and played in the Excel client or exported to MP4 video

 Static images can be copied to the clipboard

67

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power Map Power Map Audiences

Business Analysts

Users

•

Produce tours

•

•

Render and publish tour



published Power Map tour



videos



videos

68

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Search for, and view,

Introducing Power Map System Requirements

Supported Operating Systems:  Windows 7, Windows 8, Windows Server 2008 R2

Office versions:  Office Professional Plus 2013

 Office 365 ProPlus, Midsize, E3, E4, A3, A4, G3, G4

DirectX10 graphics card Internet connectivity for Bing maps Available for 32-bit (x86) and 64-bit (x64) platforms

 The selection must match the architecture of the installed version of Office 69

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power Map Power Map Ribbon

The Power Map Window allows creating and managing a single tour Tours consist of scenes, and scenes can be configured with layers of data

70

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power Map Development Methodology

1. Create a workbook and develop a Power Pivot data model, or connect to an external tabular database 2. Create tours, consisting of a sequence of scenes 3. Configure the map and map layout for each scene 4. Introduce layer(s) to visualize data superimposed on each scene 5. Annotate the map with charts, textboxes and legends 6. Configure transition properties between the scenes

7. Produce and share an MP4 video of the tour

71

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Map Configuring Scene Settings

A tour connects to a single data model and can defines an ordered set of scenes

Each scene represents a map area, and settings allow configuring:  Name  Time duration  And transition effects into the next scene

Themes can be applied to the map

72

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Map Configuring Layers

A scene can have zero or more layers

Each layer relates analytic data via a spatial relationship to a map  Spatial relationships can be defined by latitude and longitude, or text labels of spatial locations (city names, country names, zip codes, etc.) which require geocoding by Bing Maps

Data visualization properties allow setting:  Type: Column, Bubble, HeatMap, Region  Layout fields according to the visualization type  Shape types can be configured also (square, triangle, etc.)  The Time property allows data visualization animated over time 73

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Map Configuring Layer Settings

Each layer can define formatting and data properties

Each data visualization type has its own specific layer settings

74

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Map Expressing Feedback

Each scene can include (when appropriate):  2D charts  Textbox labels  Legends

 Annotations, based on static text, or dynamic based on data values

75

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Map Sharing Tour Videos

The tour can be watched from inside Excel providing the Power Map add-in is installed Videos can be produced

76

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Resources Microsoft Download Center  Microsoft Power Map for Excel (Preview)  http://www.microsoft.com/en-us/download/details.aspx?id=38395

77

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

1*1 Power Pivot

78

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Data Sources

Data Preparation

Data Storage

Extract

OLTP Database

Data Analysis

Data Access

Reports

Portals

Transform

- Tablix - Drill - Visualisations

Data Mining

Load Data Mart

ERP Database

Graphs

- Charts - Ad-hoc - Dynamic

Departmental Database

Filter

Data Warehouse

Cleanse

OLAP

Dashboard

In-Memory Analytics

Schedule

- Mash-up - Collaborate - Search

Power View MDS DBMS

Power Query

SSIS

Power Pivot

Power Pivot

DBMS

SSAS

Power Map

Excel (Pivot) Reporting Services

Excel 2010/13 Power Pivot “Traditional” Excel

Excel Workbook (with Power Pivot) In Process “Analysis Services”

BISM (DAX)

Pivot Table Services

Data Sources Raw Data Store UD M

(MD X) Reportin g Services

xVelocity store

80

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power Pivot  Excel 2013 includes the workbook data model to mash-up and analyze data 

Allows importing tables of data from different data sources



Creating relationships between tables



Creating simple calculated fields (aggregation of single columns)

 The workbook data model is delivered with a client-side version of Analysis Services, known as the xVelocity In-Memory Analytics Engine  The Power Pivot add-in for Excel provides a ribbon tab and a development window to create more sophisticated data models

81



Office Professional Plus and Office 365 Professional Plus editions only



The add-in is disabled by default 2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power Pivot A Power Pivot data model delivers:  An intuitive query-able resource that serves business user experiences  Integrates data from a variety of data sources, including:  

Traditional data source, such as relational databases Non-traditional sources, such as data feeds, text files and spreadsheets

 Delivers accelerated access to potential extremely large data volumes – well beyond the row limits of an Excel worksheet  Encapsulates business rules with calculations and key performance indicators (KPIs)

 Supports a rich variety of client tools, including those that deliver: 

Interactivity, data visualization, reports, scorecards, dashboards, and custom experiences

• Represents a “single version of the truth” 82

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Power Pivot: Data Sources

Up to 50% faster

83

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Up to 15x Compression

Save time & cost

Importing Data Data can be imported into the Power Pivot data model by:

- Copy-and-paste - Querying external sources - Linking Excel tables - Power Query queries Data imported into the data model is read-only, and can be refreshed

84

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Importing Data Copy and paste

Copied Excel or Power Pivot data ranges can be pasted to: - Create a new Power Pivot table - Append data to, or replace data in, an existing Power Pivot table that was created by a paste operation

When creating a table, Power Pivot automatically detects and sets the column data types - The data type setting can be manually modified

Data can never be refreshed, only replaced by a copy-and-paste operation

85

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Importing Data External Data Sources

External data can be imported from: - Databases - Data feeds - Files

Data can be refreshed (reloaded) from sources

86

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Importing Data External Data Sources (continued)

Database:

Other Sources: Microsoft SQL Azure

Oracle

Microsoft SQL Server Parallel Data Warehouse

Teradata Sybase Informix

Data Service:

IBM DB2 Others (OLEDB/ODBC) Excel File

Text File 87

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Importing Data External Data Sources – Table Import Wizard

Each option in the Get External Data group launches the Table Import Wizard The Table Import Wizard:  Guides the analyst through the process of loading external data  Creates a data connection  Imports data and creates relationships

88

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Importing Data Linking Excel Tables

Excel tables can be linked to create Power Pivot tables

When the data in the Excel table is modified, the linked Power Pivot table data can be updated Use the Power Pivot ribbon tab in the Excel workbook to:

Create new linked tables Update all tables or the currently selected table Use the Linked Table tab in the Power Pivot Window to update the linked table data

In the Power Pivot Window, linked tables are distinguished by a chain-link icon

89

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Power Pivot: Raw Data Storage

Columnstore Provides Dramatic Performance • •

Stores data in columnar format Memory-optimized for next-generation performance Up to 50% faster

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Up to 15x Compression

Save time & cost

Realtime work

Introducing the Power Pivot Window Data View

Allows viewing and exploring the data for a single table

by scrolling, sorting and filtering The data is read-only Calculated columns and calculated fields can be defined in data view only

91

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing the Power Pivot Window Diagram View

Allows viewing the tables, calculated fields, hierarchies and relationships Hierarchies can be defined in data view only

92

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Preparing Data Tables can be renamed, moved, and deleted

Columns can be renamed, copied and deleted Column values can be sorted by a related column  The columns must be in the same table  A one-to-one relationship must exist between the columns

Use the Home ribbon tab to:  Modify connection properties  Set column data type and format  Modify column and row filters (for the purpose of exploring in the Power Pivot Window) 93

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Preparing Data (continued) Use the Design ribbon tab to:

 Freeze/unfreeze columns  Set column width  Set calculation options

 Create and manage relationships  Modify table properties (column and row filters used to load data, or query text)

94

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Preparing Data Defining Relationships

Relationships between tables must be one-to-many

They are automatically created when:  The source database defines foreign key relationships, and  The related tables are imported together using the Table Import Wizard

They can be manually created when:  At least one column stores unique values, and  The column data types are the same

Relationships can be configured by using a dialog box, or by drag-and-drop in diagram view 95

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Enhancing the Data Model Creating Hierarchies

All columns expose a hierarchy with an All level and a single level beneath consisting of members based on the distinct values stored in the column These hierarchies provide limited value as they do not provide exploration across the data Multi-level hierarchies can be created in diagram view Hierarchy levels are based on columns from the same table  A one-to-many relationship should exist between the columns  The hierarchy level names default to the columns names, and can be renamed 96

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Enhancing the Data Model Creating Hierarchies – Example

97

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Enhancing the Data Model Creating Perspectives

Perspectives allow narrowing the resources made available to a user Each perspective is defined by a name and the visible resources, including:  Tables, columns, calculated fields, KPIs and hierarchies

Client tools can connect to the data model or to a perspective of the data model Perspectives are not security mechanisms

98

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Power Pivot: Visualization using Pivot Tables

Introduction to DAX

100

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing DAX DAX = Data Analysis EXpressions

Designed to deliver easy-to-use constructs that allow extending a Power Pivot data model with calculations DAX defines two distinctly different types of calculations:  Calculated Columns to add new columns in tables  Calculated Fields (formerly named measures) to aggregate column values

The Power Pivot data model is tabular, so DAX provides functions that implement relational database concepts The DAX function libraries consist of many familiar Excel functions, as well as many additional functions to support specific data modeling requirements 101

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing DAX Table References

DAX does not support addressing individual cells or ranges within Excel worksheets 

Formulas can only reference tables, columns and calculated fields

Table references are enclosed in single quotes 'US Sales'



The single quotes may be omitted if the table name does not include spaces, reserved characters or words Sales

Table names must be unique with the data model

102

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing DAX Column and Calculated Field References

Columns are expressed in brackets following the table name

 The table name is optional if the formula is defined in a calculation associated with the table Sales[SalesAmount]

Calculated fields are also expressed in brackets and are logically associated with a table name [Sales]

A calculated field cannot have the same name as a column in the associated table Calculated field names must be unique with the data model 103

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing DAX Expressions and Operators All expressions must begin with the equals (=) symbol

Arithmetic and Comparison operators are the same as those in Excel Text concatenation uses the ampersand (&) =[FirstName] & " " & [LastName]

Logical operators: 

AND: Double ampersand (&&)



OR: Double pipe (||)



NOT: Exclamation mark (!)



For example, this filter condition to include all sales for state WA but not for the Bike category [StateCode] = "WA" && [Category] "Bikes"

104

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing DAX DAX Functions

DAX functions can be categorized into the following groups:  Excel functions (~80 functions)  Table functions  Aggregate functions

 Relationship navigation functions  Context modification functions  Time Intelligence functions

105

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Only commonly used functions are introduced in this topic

Introducing DAX Functions Excel Functions Date and Time DATE DATEVALUE DAY EDATE EOMONTH HOUR MINUTE MONTH NOW SECOND TIME TIMEVALUE TODAY WEEKDAY WEEKNUM YEAR YEARFRAC

106

Information ISBLANK ISERROR ISLOGICAL ISNONTEXT ISNUMBER ISTEXT

Logical

AND IF IFERROR NOT OR FALSE TRUE

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Math and Trig ABS CEILING EXP FACT FLOOR INT LN LOG LOG10 MOD MROUND PI POWER QUOTIENT RAND

Math and Trig ROUND ROUNDDOWN ROUNDUP SIGN SQRT SUM SUMSQ TRUNC

Statistical

AVERAGE AVERAGEA COUNT COUNTA COUNTBLANK MAX MAXA MIN MINA

Text

CONCATENATE EXACT FIND FIXED LEFT LEN LOWER MID REPLACE REPT RIGHT SEARCH SUBSTITUTE TRIM UPPER VALUE

Introducing DAX Functions Table Functions Table functions return tables that are input to other DAX functions

 Excel functions do not support table objects FILTER(Table, Condition)  Returns a table filtered to include rows where the condition is True

ALL(Table), ALL(Column)  Returns all data for the specified object, ignoring context filters and without duplicates  This function are commonly used by the CALCULATE function The CALCULATE function will be introduced later in this topic

VALUES(Column) Returns a single-column table containing valid values in a column in the current filter 2013 © Trivadis 107context The Power of Power BI R 1.01 04.03.2014

Introducing DAX Functions Aggregate Functions

DAX implements six commonly used Excel aggregate functions that reference a Power Pivot column (not Excel ranges)  SUM, AVERAGE, MIN, MAX, COUNT, DISTINCTCOUNT  These produce “simple” calculated fields: =SUM(Sales[SalesAmount])

DAX implements five additional functions that aggregate expressions over tables:  SUMX, AVERAGEX, COUNTAX, MINX, MAXX  The following example sums the sales for the Pacific region: =SUMX(FILTER(Sales, [Region] = "Pacific"), Sales[SalesAmount])

108

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing DAX Functions Relationship Navigation Functions

RELATED(Column)  Follows existing many-to-one relationship to fetch the value from the specified column in the related table  The following example retrieves the tax rate for the related state: =RELATED(Region[RegionName])

RELATEDTABLE(Table)  Follows existing relationship (either direction) to return a table containing matching row(s) from the specified table  In a calculated column, the following example sums related SalesAmount values: =SUMX(RELATEDTABLE(Sales), Sales[SalesAmount]) 109

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing DAX Functions Context Modification Functions

CALCULATE(Expression, Filter1, Filter2,...)  Evaluates an expression in a context that is modified by the specified filter(s)  The following example produces a ratio of sales over all regions (it uses the ALL function to override any filter applied to the RegionName column from the State table): =[Sales] / CALCULATE([Sales], ALL(State[RegionName]))

Context will be described later in this topic

110

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing DAX Functions Time Intelligence Functions

Manipulating the time period is a common requirement

The Time Intelligence functions require that at least one data model table be marked as a date table  The table must include a column of type Date

 There must be no missing date rows in the table

The functions work with intervals of days, months, quarters, and years and include functions to:  Return a single date  Return a table of dates  Evaluate expressions over a time period 111

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing DAX Functions Time Intelligence Functions (continued)

There are 35 Time Intelligence functions

Commonly used functions include:  TOTALYTD(Expression, DateColumn [, SetFilter] [,YE_Date]) =TOTALYTD([Sales], 'Date'[Date])

Note that Date is a reserved word, so the table name is enclosed within single quotes

 DATEADD(DateColumn, Number_of_Intervals, Interval)  DATESBETWEEN(DateColumn, StartDate, EndDate)  DATESINPERIOD(DateColumn, StartDate, Number_of_Intervals, Interval)  PARALLELPERIOD(DateColumn, Number_of_Intervals, Interval) 112

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Describing Context Understanding context is very important when defining DAX formulas

There are there types of context:  Row  Query

Full a complete discussion on context, refer to the MSDN resource “Context in DAX Formulas” included in the Resources for this topic

 Filter

113

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Describing Context Row Context

Row context includes the values from all columns in the current row

If the table is related in a one-to-many relationship to another table, the context also includes all the related rows Row context is used by calculated columns

114

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Describing Context Query Context

Query context refers to the subset of data that is implicitly retrieved for a formula

The set of data that is retrieved is the query context for each cell

115

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Describing Context Filter Context Filter context is added when specific filter constraints are applied on the set of values allowed in a column or table, by using arguments to a formula Filter context applies on top of other contexts, such as row context or query context Used to modify context in a formula  For example, sales (query context) divided by all region sales (filtered) =[Sales] / CALCULATE([Sales], ALL(State[RegionName]))

Common filter functions:

 FILTER, ALL, ALLEXCEPT, ALLSELECTED, and others

116

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Defining Formulas DAX can be used to define formulas for:  Calculated columns  Calculated fields

Key performance indicators (KPIs) are defined by extending calculated fields

117

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Defining Formulas Calculated Columns

Define new columns in tables

Based on a DAX formula, and use row context Column values for each row are materialized in the data model  If possible, to reduce the data model size, avoid creating them

Column values are recalculated when necessary, when:  The table is processed

 Formula dependencies are processed

Can only be defined in the Power Pivot window in Data view 118

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Defining Formulas Calculated Fields Defined aggregation logic in tables

Based on a DAX formula, and use query context Values are never materialized in the data model 

The formula is evaluated at query time by using the query context

Can be created implicitly using the Power Pivot Fields pane, or explicitly on the Power Pivot ribbon tab, or in the Calculation Area in the Power Pivot Window 

Avoid creating implicit calculated fields – they are not visible outside the workbook



Reporting tools, like Power View, may not require the explicit definition of calculated fields

119

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Defining Formulas Key Performance Indicators

Collection of metrics to enable performance monitoring  The Value metric is based on an existing calculated field  The Goal metric is either:  An existing calculated field, or  Absolute value

The Status metric is defined by setting thresholds Status values are 1 (on-track), 0 (slightly off-track) -1 (critically off-track) 120

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Prepare the Model for Power View

121

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Preparing the Data Model for Power View The data model can be prepared to fully exploit the Power View experience by:  Applying friendly names, descriptions and column and calculated field data formats  Enabling images

The Advanced ribbon tab allows:  Customizing “automatic” calculated field behavior (Summarize By)  Configuring Report Properties

122

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Preparing the Data Model for Power View Friendly Names, Descriptions and Data Formats

Use friendly names for tables, columns and calculated fields  These names will be used as labels to describe Power View data visualizations  Embedded spaces are allowed

Provide descriptions for tables, columns and calculated fields  These are surfaced as tooltips in the Power View Fields pane

Set appropriate data formats for columns and calculated fields Hide unnecessary tables, columns and calculated fields

123

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Preparing the Data Model for Power View Enabling Images

Images can be enabled by:  Adding columns of image (binary) data  Creating calculated columns to produce a URL to retrieve external images 

Images can be retrieved from a web server, including a SharePoint document library

Image data cannot be efficiently stored in a Power Pivot data model Avoid storing large images or large sets of images The Table Behavior Row Identifier property must be set to a unique identifying column of the table The Table Behavior properties will be introduced later in this topic

124

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Preparing the Data Model for Power View “Automatic” Calculated Fields

There may not be the need to define explicit calculated fields

Visible numeric columns will be surfaced as “automatic” calculated fields These fields will only be available in the Power View Fields pane, and will not be available in cube reporting tools (PivotTables, CUBE functions, etc.) Use the SummarizeBy property to determine the default aggregate function:  The default is to sum the column values  The Power View report user can identify “automatic” calculated fields, and modify their aggregate function in the report layout

125

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Preparing the Data Model for Power View Reporting Properties

Default Field Set:  Ordered set of columns and calculated fields that can be conveniently added as a table to a Power View report, with a single click

Table Behavior:  Row Identifier: Sets the unique identifier column for a table (like a primary key), and it cannot be based on a calculated column  Keep Unique Rows: Columns that relate directly to the row identifier and that should not be presented as nested groups For example, State Code and State Name Default Label: Behaves as the friendly label for the table Default Image: Behaves as the image for the table 126

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Preparing the Data Model for Power View Reporting Properties (Continued)

Categorize columns by type, especially relevant for:  Spatial data (longitude, latitude, country, state, etc.)  Image URL  Web URL

By default, the Power Pivot settings will use automatic column categorization  This setting can be disabled

 Categorization settings can be changed

127

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Prepare the Model for Power Map

128

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Preparing the Data Model for Power Map Power Map does not:  Support filtering  Surface hierarchies

The data model can be prepared to fully exploit the Power Map experience by:  Ensuring that columns used as hierarchy levels are visible  Ensuring that the Date column from the data model Date table is visible  This will allow the time animation to be configured

Preparing the data model for Power Map may de-optimize the experience for other reporting and analytic tools  Consider creating a data model dedicated to Power Map work 129

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Power Pivot: Power & Pivot Excel Workbook (with Power Pivot) In Process “Analysis Services” BISM (DAX)

Pivot Table Services

Data Sources Raw Data Store UD M

(MD X)

xVelocity store

Reportin g Services

Power

Pivot

Resources Power Pivot site http://www.microsoft.com/en-us/bi/powerpivot.aspx

Book: “Microsoft Excel 2013: Building Data Models with Power Pivot” Authors: Alberto Ferrari and Marco Russo Microsoft Press

131

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Resources DAX Resource Center http://social.technet.microsoft.com/wiki/contents/articles/1088.dax-resource-center.aspx

Whitepaper: DAX in the BI Tabular Model Includes a sample Excel workbook

http://www.microsoft.com/download/en/details.aspx?id=28572

Book: “DAX Formulas for Power Pivot: The Excel Pro's Guide to Mastering DAX” Publisher: Holy Macro! Books

Author: Rob Collie

MSDN: Context in DAX Formulas http://msdn.microsoft.com/en-us/library/gg413423(v=sql.110).aspx 2013 © Trivadis 132 The Power of Power BI R 1.01 04.03.2014

Data Sources

Data Preparation

Data Storage

Extract

OLTP Database

Data Analysis

Data Access

Reports

Portals

Transform

- Tablix - Drill - Visualisations

Data Mining

Load Data Mart

ERP Database

Graphs

- Charts - Ad-hoc - Dynamic

Departmental Database

Filter

Data Warehouse

Cleanse

OLAP

Dashboard

In-Memory Analytics

Schedule

- Mash-up - Collaborate - Search

Power View MDS DBMS

Power Query

SSIS

Power Pivot

Power Pivot

DBMS

SSAS

Power Map

Excel (Pivot) Reporting Services

Power Query

134

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power Query Power Query is a new add-in for Excel to discover, transform and consume data

Allows defining queries which run a sequence of steps to import and reshape data from one or more data sources  Query steps are defined by using Power Query Formula Language (informally known as "M“) 

Simple query step logic does not require writing formulas



Advanced query step logic can be written to leverage the full power of the language

Supports a large collection of data source types

Query results can be loaded into an Excel table or the workbook data model

135

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Power Query Audiences

Business Analysts

IT Professionals

Users

• Create queries

• Configure data services • Create and publish advanced queries

• Search for, and consume, published queries

• Publish queries

136

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power Query System Requirements

Supported Operating Systems:  Windows Vista, Windows 7, Windows 8, Windows 8.1, Windows Server 2008

Office versions:  Office 2010 SP1

 Office 2013  Office Professional Plus and Office 365 Professional Plus editions only

Internet Explorer 9, or greater

Available for 32-bit (x86) and 64-bit (x64) platforms  The selection must match the architecture of the installed version of Office 137

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power Query Power Query Ribbon

Preserves familiarity with the Excel Data tab

 Includes functionality to:    

138

Search for published queries and load their data Source external or workbook data to create a new query Combine queries to create a new query Manage workbook and machine settings

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Query Online Search

Creating Queries Combining Queries Managing Queries Advanced Scenarios

139

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Query Online Search

Power Query can be used to preview and import data from a large collection of public queries, for example:  Wikipedia tables  A subset of Windows Azure Marketplace and data.gov

Filters can be applied to limit the scope of a search:

 My Shared  Organization

 All Filters can further refine searches by name, description, data source, data range and column name 140

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Query Creating Queries

A query is typically created by sourcing external data

Data can be sourced from:  Web (an HTML page)  File

 Database  Other sources

141

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Query Creating Queries – External Data Sources

142

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Query Creating Queries – Query

Queries are defined in the Query Editor window

Once a data source is defined, the query can be named, and steps created Steps can filter and shape data into a desired result  Steps can easily be produced by applying column filters, and by using the commands available from the ribbon, or the query and column context menus  It is possible to select a step and preview the data at that step  It is also possible to remove steps – but take care not to remove a step that downstream steps depend on  Step formulas can be viewed or edited in the formula bar

143

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Query Creating Queries – Query Editor

Import the latest data (run query steps)

144

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Query Creating Queries – Query Editor Context Menus

145

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Query Combining Queries

New queries can be created to:  Merge two queries (joining on a common column)  Append two queries (union)

146

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Query Managing Queries

Each query is added as a workbook connection, and can be refreshed like standard data connections Queries are managed in the Workbook Queries pane

Hovering over the query will produce a preview of the data, and provide commands Queries can be edited, duplicated, referenced, deleted, merged, appended or shared

147

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Query Advanced Scenarios

The Power Query options allow enabling advanced query editing

When enabled, a query can be edited as a script, and new queries can be scripted from scratch

Customized logic can be developed by implementing functionality not exposed by the context menus, by using looping constructs, and parameterizing queries to create functions to be invoked by other queries 148

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Working with Power Query Advanced Scenarios – Example

149

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Power Query: “Data Feed” for Excel Tables and Power Pivot Excel Workbook (with Power Pivot) In Process “Analysis Services” BISM (DAX)

Simple Data Sources

Pivot Table Services

Raw Data Store

UD M

(MD X)

xVelocity store

Reportin g Services

More and complex Data Sources

Power Query

Resources Microsoft Download Center - Microsoft Power Query for Excel (Preview) - http://www.microsoft.com/en-us/download/details.aspx?id=39933

TechEd North America 2013

- DBI-B225: “Microsoft “Data Explorer” for Excel: Discover, Combine, and Refine your Data” by Faisal Mohamood - http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B225 Power Query for Excel Formula Language Specification

- http://go.microsoft.com/fwlink/?LinkID=320633 151

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Data Sources

Data Preparation

Data Storage

Extract

OLTP Database

Data Analysis

Data Access

Reports

Portals

Transform

- Tablix - Drill - Visualisations

Data Mining

Load Data Mart

ERP Database

Graphs

- Charts - Ad-hoc - Dynamic

Departmental Database

Filter

Data Warehouse

Cleanse

OLAP

Dashboard

In-Memory Analytics

Schedule

- Mash-up - Collaborate - Search

Power View MDS DBMS

Power Query

SSIS

Power Pivot

Power Pivot

DBMS

SSAS

Power Map

Excel (Pivot) Reporting Services

The Power of Power BI Empowering Users with Self-service in Excel

Discover

Analyze

Visualize

153

Search and combine public and internal data with Power Query

Model & analyze 100’s of millions of rows lightning fast with Power Pivot Explore and visualize data in new ways with Power View and Power Map

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Rules & (Data) Governance

154

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

The Look & Feel-Hell

155

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Hichert Success Rules

156

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Reduce to the Max Color concept helps to read reports

 Actual numbers in blue style  Forecast-, budget and plan numbers in gray style

157

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Concrete sample dashboard Reporting Services (and Excel) provide a flexible platform for all kind of reporting requirements Allows to implement Hichert Success rules

158

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Simplified Navigation: Concrete @Trivadis

159

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

SharePoint and Office 365

160

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

The Excel (Data)-Hell

161

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Sharing and Collaboration Excel BI solutions are authored by using the Excel client

It is usually inappropriate to share the solutions by forwarding the workbook to other users  Requires that the user has Excel installed, and possibly the add-ins  The user can view data model data, and modify data model/query/report definitions  Current data may not be available as data refresh may not work from their location, or by using their credentials  Workbook versions are difficult to control  IT cannot monitor usage  Permissions can no longer be centrally managed 162

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Sharing and Collaboration (continued) It is preferred to share Excel BI solutions in managed ways

Microsoft provide three managed alternatives:  SharePoint 2013 (on-premise) – Enterprise Edition  Office 365 (cloud) – ProPlus and E3 Editions

 Power BI for Office 365 (cloud)

163

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Sharing and Collaboration SharePoint 2013

SharePoint include Excel Services to enable the loading, calculation, and browserbased rendering of Excel workbooks  The user does not need Excel installed

SharePoint 2013 can also be extended with SQL Server add-ins:

 Power Pivot for SharePoint  SQL Server Reporting Services

164

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Sharing and Collaboration SharePoint 2013 – Power Pivot for SharePoint

The Power Pivot for SharePoint add-in installs a collection of server components that provide query processing and management control for Power Pivot workbooks that are published to SharePoint  Maximum supported document size is 2GB

Features:  Power Pivot Gallery site template to preview workbook contents, and to launch new analytic experiences and a new Power View report  Self-service configured automated data refresh

 Logging and a dashboard to enable IT to monitor server health, workbook sizes, number of connections and queries, and data refresh failures

165

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Sharing and Collaboration SharePoint 2013 – Reporting Services

When Reporting Services is integrated with SharePoint, it enables the creation and rendering of Power View reports in the web browser Supports the same Power View capabilities as those available in Excel 2013, in addition to:  Connecting to multidimensional data models (with SQL Server 2012 CU4)  Producing a report consisting of multiple views  Printing reports

 Exporting a report to produce a PowerPoint presentation  Previewing the reports in the Power Pivot Gallery 166

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Excel / Power Pivot

Power Pivot is not the end of the road

Excel / Power Pivot

SharePoint Server

Save/Publish

• Refresh Engine for Power Pivot Data • File Level Security 167

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

View in Browser

Sharing and Collaboration Power BI for Office 365

Insights in Excel

Collaborate in Office 365

1 Billion Office Users

Discover

Analyze

1 in 4 enterprise customers on Office 365

Visualize

Share

Scalable | Manageable | Trusted 168

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Find

Mobile

Sharing and Collaboration Office 365

• Office 365 provides cloud storage for documents

• Excel Web Apps can render Excel workbooks in the cloud - Users can view and interact with reports based on the Power Pivot data model, including Power View sheets

• Data refresh and IT monitoring is not supported • Maximum supported Power Pivot workbook size is 10MB

169

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Sharing and Collaboration Power BI for Office 365 – Continued

Power BI provides powerful new ways to work with data and Excel in Office 365  Self-service BI with the familiarity of Office and the power of the cloud

Empower users with self-service in Excel Enables connecting and collaborating in Office 365  Maximum supported Power Pivot workbook size is 250MB  Support for data refresh from on-premise data sources  Includes many new compelling capabilities and features

170

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Power BI Connecting and Collaborating in Office 365

Share

Find Q&A

Mobile

171

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Share queries & workbooks and refresh from on-premises data with BI Sites

Find answers with search-based data visualization in Q&A

Stay connected from anywhere with HTML5 and the Power BI mobile app

Exploring Power BI Features and Services Power BI for Office 365

The following services extend the self-service BI capabilities in Excel by making them available in a collaborative online environment  Power BI Sites  Power BI Q&A  Query and Data Management  Power BI Windows Store App

172

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Exploring Power BI Features and Services Power BI for Office 365 – Power BI Sites

Power BI sites application customizes a SharePoint Online site for Power BI purposes and activities Available with a subscription to Power BI for Office 365 Only supports Excel workbooks, which, when rendered to the browser, are viewed with the Excel Web App

 Power View reports can be optionally displayed by using HTML5 Supports workbooks up to 250MB in size Sites provide access to a management portal to:

 View usage analytics for published queries  View and manage data sources 173

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Exploring Power BI Features and Services Power BI for Office 365 – Power BI Q&A

Q&A enables a broad audience of users to easily discover and explore a workbook data model by using natural language Submitted questions in English are responded to with data visualizations and reports

The responses are interactive, and so can be modified to further refine the data exploration requirements Delivers auto-complete and auto-suggest questions to prompt and guide the process

174

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Exploring Power BI Features and Services Power BI for Office 365 – Power BI Q&A – Example

175

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Exploring Power BI Features and Services Power BI for Office 365 – Query and Data Management

Business users can share and manage queries, and can analyze the usage of their shared data by using the Manage Data portal Queries are shared to the Data Catalog, which includes query metadata and data for the purpose of preview

The Manage Data portal enables:  Viewing usage analytics of shared queries (searched or imported)

 Viewing queries that have been shared  Viewing and managing data sources used by Power Query queries, annotating them with information to improve their discovery 176

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Exploring Power BI Features and Services Power BI for Office 365 – Power BI Windows Store App

Provides live mobile access to the important business information stored in a user’s Office 365 account The app enables viewing and interacting with Excel and Power View content Requires Windows 8 or Windows RT tablet or PC, including Surface Currently, the app is not supported on:  Windows 8.1, iPad, Mobile phones or Android devices Consider viewing them in browser with SharePoint Online site by using HTML5

177

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Exploring Power BI Features and Services IT Infrastructure Services for Power BI

From an IT infrastructure perspective, these services get Power BI running, and enable IT professionals to provide, manage, and secure Power BI services

 Provision Power BI for Office 365  Power BI Admin Center

 Data Management Gateway

178

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Exploring Power BI Features and Services IT Infrastructure Services for Power BI – Provision

IT administrators are responsible for provisioning a Power BI tenant

Presently, Power BI can only be provisioned in preview The process is documented, and relatively straight forward and quick (~15 minutes) As an administrator, users and groups can be then be added

Samples can be added to explore the Power BI capabilities

179

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Exploring Power BI Features and Services IT Infrastructure Services for Power BI – Power BI Admin Center

Allows IT administrators to:  Expose data from on-premise data sources as OData data feeds  Enable users to refresh Excel workbooks in SharePoint Online with data from on-premise data sources

A Data Management Gateway must be installed and configured to provide access to on-premise data  At least one gateway must be installed in a corporate environment before creating data sources in the portal

180

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Exploring Power BI Features and Services IT Infrastructure Services for Power BI – Data Management Gateway

Data Management Gateway

Power BI Admin Center

capabilities

X

Enabling Corporate OData Feeds

181

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Enabling Discovery in Power Query

Enabling Excel Workbook Data Refresh using SharePoint Online

Flexible BI Solutions: On-Premises or Cloud

182

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Resources Ahead of the Game site (Partner Marketing Center)

 http://aka.ms/AheadoftheGame  This campaign has three pillars:  Be Lean & Stay Lean  Business Anywhere  Tap Your Data Goldmine

Microsoft Power BI for Office 365 web site

 http://office.microsoft.com/en-us/excel/power-bi-fx104080667.aspx

183

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Resources (continued) Provision Power BI for Office 365

•

- Setup documentation: http://go.microsoft.com/fwlink/?LinkID=314068&clcid=0x409 MSDN Blog: Power BI

•

- http://blogs.msdn.com/b/powerbi/

184

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Scale to the max

185

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Go, get IT! Migration of Power Pivot Model to Analysis Services

New BI Project Excel / Power Pivot

Extract Model

186

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Analysis Services Tabular Mode

187

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Single model for entire organization

A Single Data Model 188

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Conclusion Power Pivot and SSAS Tabular Model Power Pivot is an Excel Add-In 

Modern column store in memory technology



Simple to use



High performance

SharePoint acts as central storage place 

Scalability



Automatic Data Refresh



File level Security

Analysis Services 

Partition Support



Dimension (Data) Security

189

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

(

)

Data Sources

Data Preparation

Data Storage

Extract

OLTP Database

Data Analysis

Data Access

Reports

Portals

Transform

- Tablix - Drill - Visualisations

Data Mining

Load Data Mart

ERP Database

Graphs

- Charts - Ad-hoc - Dynamic

Departmental Database

Filter

Data Warehouse

Cleanse

OLAP

Dashboard

In-Memory Analytics

Schedule

- Mash-up - Collaborate - Search

Power View MDS DBMS

Power Query

SSIS

Power Pivot

Power Pivot

DBMS

SSAS

Power Map

Excel (Pivot) Reporting Services

(C)lean Master Data Solution

191

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Master Data Services: Central place to manage Master Data A little bit like Access … just better

CRUD UI

Define System and Data Model

192

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

 Version Management  TX-Annotations  Security  Business Rules  Workflow

Excel UI

193

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Entities, Attributes, Attribute Groups and Relationships Entities act as container

Attributes define the structure of the entities Attribute Group

Attribute Group

194

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Derived Hierarchies Structure based on relationships between domains  No additional maintenance work

195

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Business Rules Business rules can be defined and applied to verify MDS data

All defined business rules must be passed to commit a version Rules are created via drag & drop interface

drag & drop

196

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

“Batch” Exporting: The path to the BI world Data can be exposed via Subscription Views

 Best practices for practices for naming _

197

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Version Management Integrated Version Management

A version contains meta data and data 

Meta data change impacts all versions



E.g. deleting an attribute deletes it in all versions!

Multiple parallel versions can be accessed Labels can be used to identify specific versions

198

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Status & Flag Management Flags allow external systems to get dynamically the desired version  E.g. the version

Current

199

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Transactions & Annotations Transaction details are provided for the specified Model and Version

Details include prior vs. new values, when the change was made, and who made the change

200

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Workflow SharePoint workflow integration

 “Real workflows” Email notification  Human workflow Both are activated via Business Rules

201

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Big Data

202

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

PDW Polybase SQL Hive HDInsight Fasttrack Appliances ODBC OLE DB C# Web Services

Big Data Corporate BI Team BI

Azure

Cloud

xVelocity

BISM SSAS

SQL SERVER 2012 Analysis Services DAX

SSRS Reporting Services Tabular

Column Store Index Sharepoint

Partitioning

SQL Server Data Tools Services TSQL UDM Power Pivot Maps

VB Skript MDX SSIS

Personal

203

Integration PowerView Office

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Excel Access SelfService BI PerformancePoint

Big Data Big data solutions deal with complexities of:

VOLUME (Size)

VELOCITY (Speed)

VARIETY (Structure)

VALUE Hadoop/HDInsight 204

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Big Data Patterns Petabytes

Terabytes

Gigabytes

Megabytes

Data Complexity: Variety and Velocity 205

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Hadoop/HDInsight

Parallel Data Warehouse: Integration of all Big Data shapes

Single Query; Structured and Unstructured SQL SQL Server 2012 PDW Powered by PolyBase

Database

206

HDFS (Hadoop)

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

• Query and join Hadoop tables with Relational Tables • Use Standard SQL language • Select, From Where

Existing SQL Skillset

No IT Intervention

Save Time and Costs

Analyze All Data Types

Microsoft Big Data Offerings: Cloud & On-Premises “Hadoop” offering as PaaS in the cloud  Extremely quick setup (minutes)

 Cost efficient  Split of data and compute resources

General big data on-premises platform SQL

Database

207

HDFS (Hadoop)

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

 Delivered as appliance  Quick setup

 Integrates all kind of data

Data Sources

Data Preparation

Data Storage

Data Analysis

Extract

Data Access

Reports

Portals

Transform

- Tablix - Drill - Visualisations

Data Mining

Load Data Mart

Graphs

- Charts - Ad-hoc - Dynamic

Filter

Data Warehouse

Cleanse

OLAP

Schedule

Big Data is just another Data Source

MDS DBMS

Power Query

SSIS

Dashboard

In-Memory Analytics

- Mash-up - Collaborate - Search

Power View

Power Pivot

Power Pivot

DBMS

SSAS

Power Map

Excel (Pivot) Reporting Services

Next Steps… or Our Offering to You

209

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Educate, Train and Coach You BI Bootcamp (End-to-End Education)

Standard Training

210

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

SUCCESS Training and Adaption to Your Needs

211

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Data Sources

Data Preparation

Data Storage

Extract

OLTP Database

Data Analysis

Data Access

Reports

Portals

Transform

- Tablix - Drill - Visualisations

Data Mining

Load Data Mart

ERP Database

Graphs

- Charts - Ad-hoc - Dynamic

Departmental Database

Filter

Data Warehouse

Cleanse

OLAP

Schedule

Trivadis biGenius™ MDS DBMS

Power Query

SSIS

Dashboard

In-Memory Analytics

- Mash-up - Collaborate - Search

Power View

Power Pivot

Power Pivot

DBMS

SSAS

Power Map

Excel (Pivot) Reporting Services

biGenius functional overview, modules BI-Architect

MGD Business Department

Modelling Generation Deployment

BRE Business Requirements Engineering

technical Documentation

Data Mart

Business Blueprints

LCM Load Control Monitoring

BI-Admin

DWH-Core Supported Technologies: - Microsoft - Oracle Source

213

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Cleansing

DQT Data Quality Testing

Staging Source

Data Steward Source

Data Sources

Data Preparation

Data Storage

Extract

OLTP Database

Data Analysis

Data Access

Reports

Portals

Transform

- Tablix - Drill - Visualisations

Data Mining

Load Data Mart

ERP Database

Graphs

- Charts - Ad-hoc - Dynamic

Departmental Database

Filter

Data Warehouse

Cleanse Trivadis MDS Toolbox Schedule XMLMD

OLAP

Trivadis biGenius™ MDS DBMS

Power Query

SSIS

Dashboard

In-Memory Analytics

- Mash-up - Collaborate - Search

Power View

Power Pivot

Power Pivot

DBMS

SSAS

Power Map

Excel (Pivot) Reporting Services

What is missing in the current MDS Release? Script based object management

Document based model documentation Graphical visualization of the MDS model Data change reports Trivadis Reports and XMLMD-Toolbox provide a solution

215

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Trivadis Reports and XMLMD-Toolbox (Create & Document)

216

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Trivadis Reports and XMLMD-Toolbox (Visualize Data Changes)

217

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Special Offer TVD

218

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

It used to be the Microsoft BI Burger

219

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Microsoft Business Intelligence Platform

220

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Introducing Microsoft SSBI Microsoft Office 2013 – Excel

Over the past decade, the core spreadsheet capabilities have been enhanced to enable analysts to analyze, communicate, and manage information • Comprehensive support for querying Analysis Services data models • Rich and interactive data visualizations • Add-ins provide rich and integrated BI capabilities:

221

Product

Purpose

Power Query

Data acquisition and preparation

Power Pivot

Data modeling

Power View

Presentation-ready, and interactive reports

2013 © Trivadis Power Map The Power of Power BI R 1.01 04.03.2014

3D geospatial visualization

Excel 2010

Excel 2013

Additional Partner Resources Ahead of the Game Marketing Content

http://aka.ms/AheadoftheGame

SMB and Midmarket Competencies

http://partner.Microsoft.com

Partner Learning Center:

https://mspartner.microsoft.com/en/us/Page s/Training/partner-learning-center.aspx

Microsoft Virtual Academy

http://www.microsoftvirtualacademy.com/

ISV Upgrade Support

Email [email protected] with app name and name of ISV

2013 © Trivadis The Power of Power BI R 1.01

222

Product and Licensing Resources Microsoft Business Intelligence website: http://www.microsoft.com/bi Microsoft BI Partner Resources: https://mspartner.microsoft.com/en/eg/pages/membership/business-intelligence-competency.aspx

Microsoft BI

SQL Server 2012

Power BI for Office 365:

Microsoft Volume Licensing

223

Microsoft SQL Server website: http://www.microsoft.com/sqlserver/ Microsoft SQL Server Partner Resources: https://mspartner.microsoft.com/en/us/pages/solutions/microsoft-sql-server.aspx Microsoft Power BI for Office 365 website: http://office.microsoft.com/en-us/excel/power-bi-fx104080667.aspx MSDN Blog: Power BI: http://blogs.msdn.com/b/powerbi/

Microsoft Volume Licensing Resources: http://www.microsoft.com/licensing/ Microsoft Licensing Advisor: http://mla.microsoft.com/

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Additional Technical Resources Power Pivot website: http://www.microsoft.com/en-us/bi/powerpivot.aspx DAX Resource Center: http://social.technet.microsoft.com/wiki/contents/articles/1088.dax-resource-center.aspx Whitepaper: DAX in the BI Tabular Model http://www.microsoft.com/download/en/details.aspx?id=28572 MSDN: Context in DAX Formulas http://msdn.microsoft.com/en-us/library/gg413423(v=sql.110).aspx Excel Cube Functions wiki site http://www.excelcubefunctions.com

Power Pivot

Microsoft Power Query for Excel (Preview) http://www.microsoft.com/en-us/download/details.aspx?id=39933 TechEd North America: “Microsoft “Data Explorer” for Excel http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B225 Power Query for Excel Formula Language Specification http://go.microsoft.com/fwlink/?LinkID=320633

Power Query

Power Map for Excel (Preview): 224

Microsoft Power Map for Excel (Preview) http://www.microsoft.com/en-us/download/details.aspx?id=38395

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014

Let‘s talk…

225

2013 © Trivadis The Power of Power BI R 1.01 04.03.2014