Power BI OnPremPower BI for Enterprise Kerry Tyler

Power BI On-Prem/Power BI for Enterprise Kerry Tyler & Paul Turley Kerry works for Denny, Paul works for Intelligent Business SQL Saturday Huntington Beach

About Paul Turley (Blog | Linked. In | Twitter) is a Mentor with Solid. Q and a Microsoft SQL Server MVP. He consults, writes, speaks, teaches & blogs about business intelligence and reporting solutions. He works with companies around the world to visualize and deliver critical information to make informed business decisions. He is a Director of the Oregon SQL PASS chapter & user group, the lead author of Professional SQL Server 2012 Reporting Services and 13 other titles from Wrox, Packt & Microsoft Press.

About Kerry Recovering Sysadmin 10+ years of data—Varying degrees of SQL Server Engine & BI Currently SQL Server Consultant Used to fly little airplanes 3 | 04/02/2016 | SQLSat SNA

Agenda BI Solution Design Patterns Getting Data “into” Power BI Enterprise Connector’s Architecture/How it Works Setup Walkthrough Pros/Cons/Caveats 4 | 04/02/2016 | SQLSat SNA

Universal Rules of Business Intelligence Projects The principles of data are like the laws of physics …and gravity The application of each principle can vary …depending on the size & scale of a solution… some steps can be omitted some rules can be bent or even broken …but there always trade-offs and consequences As the scale & complexity of the solution expands, we will be reminded that the fundamental rules always apply

Universal Data Problems Trustworthiness Data Quality Conformity Completeness Ownership & Stewardship Maintenance

Power BI Technologies & Tools History 1998 2000 2005 2008 update service release SQL Server Analysis Services Power Pivot/Excel Power Pivot/Share. Point Power View Power Query/Excel Power BI/Office 365 Power BI. com/ Power BI Desktop product release 2010 2012 2013 2014 2015 2016

BI Solution Stages Data Sourcing & Transformation Self-service Dimensional & Semantic Modeling Data Presentation & Delivery Power Query Power Pivot Power BI Excel Pivot. Tables & Charts Integration Services SQL Server / Analysis Services PQ Enterprise Reporting Services Power BI Excel Pivot. Tables & Charts

Project Stages § § § § § Requirement sponsorship & validation Data acquisition Cleansing & conformity Data stewardship & maintenance Data modelling Security & access control Processing & scheduling Consumption & usability Education

Power BI Solution Scenarios § Desktop § Shared hosted model § Shared model with on-prem data sources

Desktop Scenario

Shared Service Scenario

On-Prem Data Scenarios

Personal & Enterprise Gateways Personal Gateway Enterprise Gateway Import data and set up scheduled refresh X Coming soon Run as an app for users who aren't administrators on the computer X Run as a single user with your credentials X Serves multiple users with access control per data source X Support for Direct. Query to SQL Server X Support for a live connection to Analysis Services X Monitoring and auditing for gateway and data sources https: //powerbi. microsoft. com/en-us/gateway Coming soon

Data in Power BI Uploaded Dataset Direct. Query Source 15 | 04/02/2016 | SQLSat SNA

Data Sources Uploaded Dataset Direct. Query/“Live” § § § 16 | 04/02/2016 | SQLSat SNA SQL Server Analysis Services Oracle SAP HANA Teradata

Scheduled Refresh Supported through Enterprise Gateway • • SQL Server Analysis Services SAP HANA File Folder Oracle Teradata Share. Point list (on-premises) 17 | 04/02/2016 | SQLSat SNA

Live Connectivity What we’re here for 18 | 04/02/2016 | SQLSat SNA

Power BI Gateway - Enterprise Demo! 19 | 04/02/2016 | SQLSat SNA

Architecture klt@corp. com Power BI Results S a a I re Azucorpklt ? DAX Re. D s. Au. X lts DAX klt@corp. com corpklt 20 | 04/02/2016 | SQLSat SNA

Gateway, AD, & “Effective Username” § The Power BI login needs to match the UPN of an AD user 21 | 04/02/2016 | SQLSat SNA

Gateway, AD, & “Effective Username” (con’t) § This could break in a number of scenarios § O 365 @blah. onmicrosoft. com email address § Email Address doesn’t match AD login name § Multiple domains involved in Trust(s) between gateway server, SSAS, where users live, etc § Dealt with via AAD dirsync § Log in to PBI with actual UPN 22 | 04/02/2016 | SQLSat SNA

Auth. N/Z § Gateway connects to SSAS using dedicated account (more on this later) § Within the SSAS DB (MD or Tabular), security behaves as it normally would § Role membership(s) § Row-level security 23 | 04/02/2016 | SQLSat SNA

Gateway Setup 24 | 04/02/2016 | SQLSat SNA

First, Get the Connector (Signed in)… 25 | 04/02/2016 | SQLSat SNA

Get the Connector (Not Signed In) 26 | 04/02/2016 | SQLSat SNA

Things You’ll Need § Domain-joined servers (SSAS & Gateway) § Decide where to install § Domain account with “sa” rights on target SSAS box* § Security within the model configured “like you usually would” 27 | 04/02/2016 | SQLSat SNA

…about that asterisk § Think about/Treat this like you would any other Service Account § Password Expiry § Treat it separately from the Engine sa accounts 28 | 04/02/2016 | SQLSat SNA

Enterprise Gateway Data Source Setup 29 | 04/02/2016 | SQLSat SNA

Things You Can Do § Use real-time SSAS data to feed any dashboard or report object § Configure multiple sources with a single Gateway § Monitor usage § Restore broken Gateway 30 | 04/02/2016 | SQLSat SNA

Things to Know SSAS Multidimensional: DAX-MD Single Gateway can service multiple Data Sources/Sets Gateway service runs as NT SERVICEPBIEgw. Service RTM/Gold/GA: It’s for real! 31 | 04/02/2016 | SQLSat SNA

More Things to Know Requires Power BI Pro: $10/user/month 32 | 04/02/2016 | SQLSat SNA Replaces the old “Analysis Services Connector” • The ASC worked until Thursday…

The Future! Changes come fast 35 | 04/02/2016 | SQLSat SNA This is out of Preview…Use it!

URLs/References § www. powerbi. com § Pricing/Feature comparison breakdown: http: //powerbi. microsoft. com/pricing § Troubleshooting Enterprise Gateway: https: //powerbi. microsoft. com/enus/documentation/powerbi-gatewayenterprise-tshoot/ 36 | 04/02/2016 | SQLSat SNA

Questions? 37 | 04/02/2016 | SQLSat SNA

Contact Kerry L. Tyler Paul Turley Blog: www. airbornegeek. com Twitter: @Airborne. Geek Email: kerry@dcac. co Blog: Sql. Server. Bi. Blog. com Twitter: @paul_turley Email: paul@intelligentbiz. net 38 | 04/02/2016 | SQLSat SNA