Session: Power BI & BI360 DW

August 29 & 30, 2018Huntington Beach, CA

with

Power BI & BI360 Data Warehouse

Presenters:

Toby Prince: Technical Consultant, Solver USA

Carlos Lameiro: Founder and Director of BIAPRO Information Solutions Inc., a

Solver Platinum partner in Toronto, and exclusive distributor in Spain & Portugal

Agenda

Purpose and Goals

Power BI Overview/Review

Getting data into Power BI

Demo: BI360, Analysis Services & Power BI

Conclusion, Q & A

1

2

3

4

5

Purpose & GoalsWhat we’ll accomplish today…

Purpose: Learn how Power BI can help you get even more value from your

BI360 data warehouse investment (and vice versa)

Goals:

o See use cases for combining BI360DW and Power BI

o Understand your options and next steps

Overview

Overview

Why Power BI?

Visualizations: present data in a visual way (charts, maps, etc.) that can have

greater impact or resonance with end users

Interactive user experience: see instantly how key metrics are influenced by

changes in context (time, geography, etc.)

Analysis: go beyond reporting of results to discover underlying causes

OverviewSample Power BI visualizations based on Solver demo models...

Actual vs. Budget Comparisons

Display actual and budget amounts side by side,

e.g. for income statement line items

Revenue Analyses by Product Line

Compare year over year changes in product

mix

Revenue Analyses by Region

Show which areas have the highest sales

or year over year growth

OverviewSample Power BI visualizations based on BI360 demo models...

Overview

• Power BI Service – cloud-based service where you can share visualizations (charts,

graphs, etc.) and create dashboards. Available on subscription basis.

• Power BI Desktop – develop data models and visualizations, then publish to PBI

service.

• Power BI Gateways (enterprise or personal) – push scheduled, automatic updates of

your data to Power BI service

• Report Server – on premise version of Power BI service. Requires license purchase.

Power BI Components…

For more info: powerbi.microsoft.com

Power BI & BI360DW

Power BI Service

Solver API (coming soon)

BI360 Cloud Tenant

Power BI Desktop

ApplicationsTenant DB(s)

BI360 Cloud Deployment

DatasetsVisualizationsDashboards

Publish Data Model

Schedule Updates

Power BI Service

Power BI gateway

Power BI Desktop

BI360 Private Host Deployment

DatasetsVisualizationsDashboards

BI360DW (or SSAS)

Publish Data Model Schedule Updates

BI360 Private Host Deployment

3 Options for Importing BI360 data into PBI desktop…

BI360 API

SQL Queries/Views

SQL Server Analysis Services (SSAS)

Choose SQL Server from the

“Get Data” menu…

Using SQL Queries in Power BI Desktop…

BI360 Private Host Deployment

Enter the server name,

database name, and SQL

query…

Using SQL Queries in Power BI Desktop…

BI360 Private Host Deployment

For more info…

• Go to support.solverglobal.com• Enter “Power BI” in the search bar

to find our whitepaper and video

Power BI, BI360 DataWarehouse & Analysis Services

Case Study:

Finding Low Margin Clients

& Looking for Opportunities to Increase:1) Customer Margins &

2) Product-Mix Margins

By Carlos Lameiro, MBA, MCSE-DMA, CMC, Founder and Director, BIAPRO®Platinum Partner (Toronto) & Exclusive Distributor (Spain & Portugal)

Power BI & BI360DW

Part 2: Agenda

- Brief Introduction to the Case Study and some

important concepts to look out for during the Demo

- Demo

- More detailed information about

- Publishing Power BI with SSAS as a data

source

- Power BI LifeCycle

- BI360DW Trees

- Reading List and Q&A

Building a Power BI model using a Private Host DW

Power BI & BI360DW

• Objective: Find customers with

• lower Relative Margin %

and

• Net Margin > $2M

• …In about 2 minutes

Building a Power BI model using a Private Host DW

DemoBI360DW, SSAS & Power BI

Power BI & BI360DWDEMO:

Dowloading the Hierarchy Slicer from the PowerBI marketplace that recognizes

the structure of the Hierarchies built for Customers (shown) and Products (if

required)

Power BI & BI360DWDEMO:

Report filtering clients with less than 100% relative Margin % and more than Net

Revenue of $2M:

• Visualization showing bars for Net Revenue & line with Relative Margin %

• Table with Product Mix figures for all Lower Margin clients shown

Power BI & BI360DWDEMO:

By clicking on a specific client (dark green, second from left), we can see:

• Net Revenue of $5.5M & Relative Margin of 74.07% or 98.69% of Group’s Margin of 75.05%

• Product Mix figures for the one specific client, showing lower Margins for specific products (lower 6

rows)

Power BI & BI360DWDEMO:

By clicking on a specific Product such as “Reporting”, (dark grey row) we can see:

• Overall net revenue of $54.96M, and the visual product-mix component for each of the clients Net Revenue bars above

(dark green)

• We can also see the individual Margin % for this specific Product across all clients above, as per the line graph

Power BI & BI360DWDEMO:

By clicking on a specific Product such as “Dashboards”, (dark grey row) we can see:

• Overall net revenue of $54.96M, and the visual product-mix component for each of the clients Net Revenue bars above

(dark green)

• We can also see the individual Margin % for this specific Product across all clients above, as per the line graph

Power BI & BI360DWMethodology to determine Relative Performance

• Margin % for each client

• Segmentation of Clients by Region, to determine Relative Group Margin%

• Look at individual Product Margins across Regions

Building a Power BI model using a Private Host DW

Re gion A

P r o d u c t 1

P r o d u c t 2

Re gion B

7 0 %

6 5 %

7 5 %

7 2 %

6 2 %

8 2 %

7 2 %

Power BI & BI360DWBuilding a Power BI model using a Private Host DW

ERP

CRM

HCM

Publish Reports

SSIS

SSIS

SSISSSASOLAPCube

Live Connection

BI360 DW

Ingredients for Private Hosting BI360DW & Power BI:

What strengths does each component bring to the overall solution?

API, Import orDirect Query

Power BI & BI360DWConnection Types in Power BI

i.e. Not necessaryHandled by T-SQL & SSAS

Power BI & BI360DWBuilding a Power BI model using a Private Host DW

BI360 DataWarehouse & Power BI

Life Cycle of BI

Source of LifeCycle diagram: http://www.ibmbigdatahub.com/blog/insightout-enabling-highly-collaborative-and-data-driven-organization

Power BI & BI360DWBuilding a Power BI model using a Private Host DW

Source of LifeCycle diagram: http://www.ibmbigdatahub.com/blog/insightout-enabling-highly-collaborative-and-data-driven-organization

ERP

CRM

HCM

Publish Reports

SSIS

SSIS

SSIS

SSASOLAPCube

Live Connection

BI360 DW

API, Import orDirect Query

BI360 DataWarehouse & Power BI

Life Cycle and Power BI Connection Types

Data Discovery

&

Data Modeling• Import

• Direct

Query

• API

• OLAP

Production Environment

&

Data Scheduling• Import

• Direct Query

• API

• OLAP Cube

ERP

CRM

HCM

SSIS

SSIS

SSIS

BI360 DW

API, Import or

Direct Query

Power BI & BI360DW

BI360 DataWarehouse (both Private Host and Azure Cloud)

•Robust User-Friendly GUI

•Advanced Tree Functionality

• Parent-Child Hierarchies

• Flat Hierarchies

• Multiple/Dimension

• Multiple Associations

• Ordering

• Percentages

• User Friendly

• Visual

• Drag & Drop

•Integration for multiple processes

• Systems: SSIS to consolidate Data

• Manual: Budget/Forecast data Entry

• Multiple Module/Transaction Capability

• Leverage Excel Data Entry Programmability

Building a Power BI model using a Private Host DW

ERP

CRM

HCM

SSIS

SSIS

SSIS

SSASOLAPCube

Live Connection

BI360 DW

Power BI & BI360DWBI360 DataWarehouse

Transaction with Maximum Flexibility and

Robust Dimensional Matrix Building a Power BI model using a Private Host DW

ERP

CRM

HCM

Publish Reports

SSIS

SSIS

SSIS

SSASOLAPCube

Live Connection

BI360 DW

Power BI & BI360DWBI360 DataWarehouse Key Feature –

Multiple Hierarchies for the same Dimension Building a Power BI model using a Private Host DW

ERP

CRM

HCM

Publish Reports

SSIS

SSIS

SSIS

SSASOLAPCube

Live Connection

BI360 DW

Power BI & BI360DWSample Client Regional Hierarchies in BI360 DW

Building a Power BI model using a Private Host DW

ERP

CRM

HCM

Publish Reports

SSIS

SSIS

SSIS

SSASOLAPCube

Live Connection

BI360 DW

Power BI & BI360DWSample Product Hierarchy in BI360 DW

Building a Power BI model using a Private Host DW

ERP

CRM

HCM

Publish Reports

SSIS

SSIS

SSIS

SSASOLAPCube

Live Connection

BI360 DW

Power BI & BI360DW

Sample Account Hierarchies

- Create custom account

grouping

- Easily present F/S in other

Account Structures

Building a Power BI model using a Private Host DW

ERP

CRM

HCM

Publish Reports

SSIS

SSIS

SSIS

SSASOLAPCube

Live Connection

BI360 DW

Power BI & BI360DWBI360 Cloud Key Feature –

Multiple Hierarchies for the same Dimension Building a Power BI model using a Private Host DW

ERP

CRM

HCM

Publish Reports

SSIS

SSIS

SSIS

SSASOLAPCube

Live Connection

BI360 DW

Power BI & BI360DW

What strengths does each component bring to the overall solution?

OLAP vs Relational Power BI connection

•Data Model Pre-configured

• Vs Linking Tables

•Data Cleaning and Validation

•Live Connection vs Import (static)

•Domain Level Security Authentication

•Role Based Security

•MDX vs DAX

• Hierarchies

• Calculations

•Maintenance & Adjustments

Building a Power BI model using a Private Host DW

ERP

CRM

HCM

SSIS

SSIS

SSIS

SSASOLAPCube

Live Connection

BI360 DW

Power BI & BI360DW

What strengths does each component bring to the overall solution?

Power BI vs Excel Publishing/Consumption

•Layout

• Desktop: Pages, Reports

• Web Service: Workspaces, Dashboards

•Multiple Visuals

•Filtering

•Version Control

•Ease of Use

Building a Power BI model using a Private Host DW

ERP

CRM

HCM

Publish Reports

SSIS

SSIS

SSIS

SSASOLAPCube

Live Connection

BI360 DW

BI360 Private Hosting Deployment& Power BI Service with SSAS OLAP (Live Connection)

Live Connection

Power BI Service Power BI App

On-Premise Data Gateway

Live Connection

ERP

CRM

HCM

Publish Reports

SSIS

SSIS

SSISSSASOLAPCube

Live Connection

BI360 DW

Live Connection

Power BI Service Power BI App

On-Premise Data Gateway

BI360 Private Hosting Deployment& Power BI Service with SSAS OLAP (Live Connection)

Live Connection

Power BI & BI360DWAdditional Information & References:

Building a Power BI model using a Private Host DW

S S A S a n d D A XP o w e r B I R e f e r e n c e s

Power BI, BI360 DataWarehouse & Analysis Services

Q&A

Case Study:

Finding Low Margin Clients

& Looking for Opportunities to Increase:1) Customer Margins &

2) Product-Mix Margins

By Carlos Lameiro, MBA, MCSE-DMA, CMC, Founder and Director, BIAPRO®Platinum Partner (Toronto) & Exclusive Distributor (Spain & Portugal)

Q & A

A new eLearning experience…always there when you need it

QUESTIONS?

SOLVER ACADEMY

NEXT STEPSContact us at info@solverglobal.com or contact your BI360 partner