making the most of power bi with sql server 2014 and azure

Making the Most of Power BIwith SQL Server 2014 and Azure

facebook.com/perficient twitter.com/Perficient_MSFTlinkedin.com/company/perficient

Presented by Andy Tegethoff

Perficient is a leading information technology consulting firm serving clients throughout

North America.

We help clients implement business-driven technology solutions that integrate business

processes, improve worker productivity, increase customer loyalty and create a more agile

enterprise to better respond to new business opportunities.

About Perficient

• Founded in 1997

• Public, NASDAQ: PRFT

• 2013 revenue ~$373 million

• Major market locations throughout North America• Atlanta, Boston, Charlotte, Chicago, Cincinnati, Columbus,

Dallas, Denver, Detroit, Fairfax, Houston, Indianapolis, Los Angeles, Minneapolis, New Orleans, New York City, Northern California, Philadelphia, Southern California, St. Louis, Toronto and Washington, D.C.

• Global delivery centers in China, Europe and India

• >2,100 colleagues

• Dedicated solution practices

• ~85% repeat business rate

• Alliance partnerships with major technology vendors

• Multiple vendor/industry technology and growth awards

Perficient Profile

BUSINESS SOLUTIONSBusiness IntelligenceBusiness Process ManagementCustomer Experience and CRMEnterprise Performance ManagementEnterprise Resource PlanningExperience Design (XD)Management Consulting

TECHNOLOGY SOLUTIONSBusiness Integration/SOACloud ServicesCommerceContent ManagementCustom Application DevelopmentEducationInformation ManagementMobile PlatformsPlatform IntegrationPortal & Social

Our Solutions Expertise

Our Microsoft Practice

Andy TegethoffAndy is a Microsoft BI architect at Perficient and a senior-level consultant with more than 14 years of experience exploring, planning and building cost-effective software and process-oriented solutions for challenging business problems. A Microsoft Certified IT Professional (MCITP) for SQL Server BI, his solutions integrate elements such as data warehousing, dashboards, and broad-based reporting in order to deliver vital, actionable information at every level of the enterprise.

Our Speaker

A nationwide Microsoft BI practice offering both• Strategic Engagements

• Strategy Assessments • Program Mentoring

• Tactical Engagements • End-to-End Implementations• Current State Evaluations• Proof of Concept Projects

BI Expertise

• Introduction

• Power BI Review

• Accessing Your Data with Power BI

• SQL Server 2014: What’s New?

• Cloud (BI) on the Horizon

• Q & A

Agenda

Introduction

Cloud-Enabled BI with Microsoft

Presentation TierPower BI for Office 365

Service Tier/Data TierMicrosoft Azure/SQL Server 2014

What is Power BI?

Power BI is:

A collection of Excel-based tools and Office 365 SharePoint Online features and services that enables self-service BI and provides a data collaboration portal featuring mobile access to analytics.

Power BI for Office 365

What are the components of Power BI?

There are two major elements:

Self-Service BI with Excel (AKA “The Power Tools”)

Power QueryPower PivotPower ViewPower Map

Power BI withOffice 365

Power BI SitesPower BI for MobileData Management Gateway

Power BI for Office 365

The Power Tools

Power Query

Find and connect data across a wide variety of sources – both internal and external to your organization

Connect to and consume Big Data in Excel• Hadoop (HDFS)• Azure HD Insight,

PDW

The Power Tools

Power Query

Power Pivot

Find and connect data across a wide variety of sources – both internal and external to your organization

Connect to and consume Big Data in Excel• Hadoop (HDFS)• Azure HD Insight,

PDW

Host a significant analytical database inside Excel in-memoryusing the xVelocityanalytics engine, with no Admin or Configuration –and share it via SharePoint

Access data from standard a variety of platforms, work with millions of rows, plus keep larger file sizes in Power BI (250MB model size limit, 10MB file size limit)

The Power Tools

Power ViewInteract with data from various sources –including Excel itself, a Power Pivot model, or SQL Server Analysis Services (Tabular model or OLAP Cube)

Create charts and other visualizations with filtering, highlighting, slicing and sorting• Multiple Views• Solid performance• HTML 5

The Power Tools

Power View

Power Map

Interact with data from various sources –including Excel itself, a Power Pivot model, or SQL Server Analysis Services (Tabular model or OLAP Cube)

Create charts and other visualizations with filtering, highlighting, slicing and sorting• Multiple Views• Solid performance• HTML 5

3D visualization tool for mapping, exploring and interacting with geospatial and time data

Plot data on Bing maps and visualize with 3D columns, charts, heat maps, and regions

Capture screenshot "scenes" and build cinematic video "tours"

Power BI SharePoint Online App

Power BI SitesAnalogous to a PowerPivot Gallery in on-premise SharePoint, but BETTER! (Allow larger files, more advanced browsing)

Key Features:• Q & A (Natural

Language query)• Connectivity to

external data sources


Power BI Sites

Power BI for Mobile

Analogous to a PowerPivot Gallery in on-premise SharePoint, but BETTER! (Allow larger files, more advanced browsing)

Key Features:• Q & A (Natural

Language query)• Connectivity to

external data sources

Available in the Windows Store (currently only for Windows 8, 8.1 devices)

iOS version due by Q3

Connects to Power BI site, allows browsing for Power BI content from any device


Administration

Power BI Admin Center allows IT management of “gateways” to on-premises data sources, and is accessible from O365 Admin or any Power BI site


Administration

Data Management Gateway - Client app runs on

machines on-premises- Connect to cloud

service in Power BI

Power BI Admin Center allows IT management of “gateways” to on-premises data sources, and is accessible from O365 Admin or any Power BI site

Introducing Data Management Gateway

Power BI Data Connectivity

A client agent that runs as Windows Service on data source computer. • Free DL -- install to

multiple computers• Connects to cloud

service which runs in the Power BI App in SharePoint Online



A client agent that runs as Windows Service on data source computer. • Free DL -- install to



Administrator registers and shares a Data Management Gateway client with the cloud service, and can then create Data Sources:• Cloud Refresh of

workbooks• Shareable OData feeds



A client agent that runs as Windows Service on data source computer:• Free DL -- install to



Administrator registers and shares a Data Management Gateway client with the cloud service, and can then create Data Sources:• Cloud Refresh of

workbooks• Shareable OData feeds

Data Source credentials can be stored securely in the cloud:• Business continuity• Data source migration

purposes • Requires certificate

So where do you keep your data?

The Power BI Power Tools support most industry-standard data sources via PowerPivot and/or Power Query:

SQL Server, Oracle, Teradata, IBM DB2, Access, Excel, OData feeds, and more

Data Management Gateway can be used to access SQL Server and Oracle.

On-Premises

• RDBMS Systems• LOB Systems• DW Systems

• Small databases (Excel, Access)

In the Cloud

• Azure SQL Database• Amazon AWS data

store• Public data• Vendors



Access to Data Anywhere• On-Premises sources via Data Management Gateway • Cloud sources via Excel Power Query

SQL Server 2014:What’s New?

Enhancements to the Database Engine:• In-Memory OLTP (AKA “Hekaton” or Memory-Optimized Tables)• Improved In-Memory Columnstore Indexes• Even deeper Azure integration

Enhancements to the Analysis Services and BI:• Power View for dimensional models• Chrome browser support for reporting services

SQL Server 2014

SQL Server 2014:“What’s In It For Me?”

Dramatic query performance boosts (e.g. 30x faster OLTP, 100x faster star schema) with In-Memory DB options

Improved integration with cloud for backup, hot failover, DR, or even primary data storage (Azure-synced files, Hybrid Cloud architectures)

SQL Server 2014

SQL Server 2014

Transformational performance: - Up to 30x OLTP

performance gains- Up to 100x faster start

join queries- Up to 90% disk space

savings

New features:- In-Memory OLTP

(Memory-Optimized Tables)

- Updateable In-Memory columnstore

Low Barriers to Adoption:- Minimal app rewrite - Integrated into core

DB engine- Maximizes existing

hardware

In-Memory Database Technology• If you know SQL, you can use In-Memory technology

– No new APIs– Don’t rewrite existing apps!

• Part of Core SQL Server – Does not restrict other features– Not a separate license – included in all editions

• Can run in concert with traditional disk-based tables– Will even tell you which tables should reside in memory!

• Designed for speed– Locks and latches removed, but durability ensured– Harness all your CPU/Core capacity!

SQL Server 2014

High-efficiency, high-value: - Enhance on-premises

installations with cloud capabilities

- Pay-per-use scalability- Simple offsite DR

New features:- SQL Server Backup to

URL/Azure- Add Azure Replica

Wizard- SQL Server Data Files

in Azure

Flexible cloud solution:- Move entire servers to

the cloud, or just particular functionality

- Easily expand resources as you grow

Microsoft Azure Integration

• Hybrid cloud architecture support– Blending on-premises and cloud technologies

• Scenarios:– Business continuity

• Cloud backup and restore• Disaster recovery and/or transparent/Hot failover

– Archive data files to the cloud– Development or QA environment

• Azure features:– Azure storage and Azure virtual machines– Low-cost, virtually limitless storage and compute capacity

Microsoft Azure

Speaking of

Microsoft Azure

Azure Virtual Machines

Only path to fully-functional SQL Server BI in the cloud

Integrate into existing AD network/domain

Pre-built VM images provided by Microsoft – including a SQL 2014 Data Warehousing server

Speaking of

Microsoft Azure

Azure Virtual Machines

Only path to fully-functional SQL Server BI in the cloud

Integrate into existing AD network/domain

Pre-built VM images provided by Microsoft – including a SQL 2014 Data Warehousing server

Big Data with HDInsight

Wraps an Apache Hadoop implementation

Uses Azure Tables for storage

Excellent option for integrating Big Data into existing applications, BI solutions, reporting environments

Speaking of

Cloud BI

BI in the Cloud with Azure and SQL Server 2014:What do I need?

• Azure Virtual Machine– With Windows Server 2012 and SQL Server 2014 Data

Warehousing edition licenses

• Azure HDInsight cluster and Azure Storage– Supports Hadoop Big Data

Cloud BI

… and for Power BI in the Cloud with Office365:

• Office 365 SharePoint Online– with Power BI for Office 365 app Office 365 Excel

* Not an “officially” Microsoft-supported configuration

Cloud BI

SQL Server Cloud BI Advice

• Use an A7 VM for SQL Server 2014• Use a point-to-site or a site-to-site VPN connection for

efficient data transfer• Use page compression for your data• Use one file per filegroup to prevent multilevel striping

(negatively affects throughput)• Set up the Power BI data source from the Azure VM

itself

Questions?

Connect with Perficient