Connecting Power BI to OnPrem Data with SSAS

Connecting Power BI to On-Prem Data with SSAS Kerry L. Tyler Denny Cherry & Associates Consulting SQL Saturday Atlanta

About Me Recovering Sysadmin 10+ years of data—Varying degrees of SQL Server Engine & BI Currently SQL Server Consultant Chapter Leader for Nash. BI Used to fly little airplanes 3 | 05/21/2016 | SQLSat ATL

Agenda 38 Seconds on “Power BI” Where Does PBI “Get” its Data? How does On-Prem Data Work? The Power BI Gateways Enterprise Gateway Setup (demo) Enterprise Gateway In-Depth Bonus Demo!? 4 | 05/21/2016 | SQLSat ATL

Agenda 38 Seconds on “Power BI” Where Does PBI “Get” its Data? How does On-Prem Data Work? The Power BI Gateways Enterprise Gateway Setup (demo) Enterprise Gateway In-Depth Bonus Demo!? 5 | 05/21/2016 | SQLSat ATL

Power BI Replaced “PBI for Office 365” • like, forever ago Fully stand-alone product Dashboard/Tiles, Reports Desktop designer Mobile Apps Gets updated every 10 sec 6 | 05/21/2016 | SQLSat ATL

Power BI Scenarios Power BI Single User Desktop 7 | 05/21/2016 | SQLSat ATL Multiple User Shared Hosted Data Shared On-Prem Data

Where are y’all at? 8 | 05/21/2016 | SQLSat ATL

Agenda 38 Seconds on “Power BI” Where Does PBI “Get” its Data? How does On-Prem Data Work? The Power BI Gateways Enterprise Gateway Setup (demo) Enterprise Gateway In-Depth Bonus Demo!? 9 | 05/21/2016 | SQLSat ATL

Data Source vs Dataset Data Source Dataset Sales. DB 10 | 05/21/2016 | SQLSat ATL

Data Sources 11 | 05/21/2016 | SQLSat ATL

Datasets—Two Flavors Uploaded to PBI Direct. Query/“Live” 12 | 05/21/2016 | SQLSat ATL

Agenda 38 Seconds on “Power BI” Where Does PBI “Get” its Data? How does On-Prem Data Work? The Power BI Gateways Enterprise Gateway Setup (demo) Enterprise Gateway In-Depth Bonus Demo!? 13 | 05/21/2016 | SQLSat ATL

These, Again Uploaded to PBI Direct. Query/“Live” 14 | 05/21/2016 | SQLSat ATL

Uploaded Dataset 15 | 05/21/2016 | SQLSat ATL

Direct. Query/“Live” 16 | 05/21/2016 | SQLSat ATL

Why Use On-Prem Data in Power BI? Comfort Built-In Security Data Size Limits (now tempered) There, uh, aren’t really a lot of downsides if you have the supporting infrastructure 17 | 05/21/2016 | SQLSat ATL

OK, How Does This Work, Really? 18 | 05/21/2016 | SQLSat ATL

Agenda 38 Seconds on “Power BI” Where Does PBI “Get” its Data? How does On-Prem Data Work? The Power BI Gateways Enterprise Gateway Setup (demo) Enterprise Gateway In-Depth Bonus Demo!? 19 | 05/21/2016 | SQLSat ATL

What’s a Gateway? On. Prem Data 20 | 05/21/2016 | SQLSat ATL Gateways Power BI

Power BI Gateways—Two Flavors “Personal” Gateway Enterprise Gateway For uploaded datasets For uploaded AND Live datasets Supports Scheduled Refresh Can Control/Secure access to Data Sources Can be used with almost all data sources Monitoring Can be run as a Service DR 21 | 05/21/2016 | SQLSat ATL

Personal Gateway Sources Used for Scheduled Refresh Works with all Data Sources under “Get Data”, except for Exchange & Hadoop 22 | 05/21/2016 | SQLSat ATL

Enterprise Gateway Sources • • • Live/Direct. Query Scheduled Refresh SSAS (MD & Tab) SQL Server SAP HANA Oracle Teradata • All of those, plus: • Share. Point list (onpremises) • Web • Odata • DB 2 • My. SQL • Sybase 23 | 05/21/2016 | SQLSat ATL

Enterprise Gateway Architecture Cloud Power BI 24 | 05/21/2016 | SQLSat ATL On-Prem Ent. Gateway On-Prem SSAS MD/Tab SQL Server

Enterprise Gateway Connectivity Data accessed via PBI User SSAS Server Access (“sa”) Enterprise Gateway Server/Data Access (data reader) Data accessed via this Account 25 | 05/21/2016 | SQLSat ATL SQL Server

Agenda 38 Seconds on “Power BI” Where Does PBI “Get” its Data? How does On-Prem Data Work? The Power BI Gateways Enterprise Gateway Setup (demo) Enterprise Gateway In-Depth Bonus Demo!? 26 | 05/21/2016 | SQLSat ATL

Power BI Gateway - Enterprise Demo! 27 | 05/21/2016 | SQLSat ATL • Installation • Configuration • Usage

Get the Gateway… 28 | 05/21/2016 | SQLSat ATL

Agenda 38 Seconds on “Power BI” Where Does PBI “Get” its Data? How does On-Prem Data Work? The Power BI Gateways Enterprise Gateway Setup (demo) Enterprise Gateway In-Depth Bonus Demo!? 30 | 05/21/2016 | SQLSat ATL

Enterprise Gateway Authorization Overview (SSAS) User Authentication handled by Power BI Gateway connects to • Passes SSAS using “service” Effective. User. Name account in queries Within the SSAS DB • Role membership(s) (MD or Tabular), security behaves as it • Row-level security normally would 32 | 05/21/2016 | SQLSat ATL

When Running Queries in SSAS The Power BI login is passed from the Gateway to SSAS in the “Effective. User. Name” property 33 | 05/21/2016 | SQLSat ATL This can work one of two ways

Power BI Login and SSAS—Option 1 § The Power BI login matches the UPN of a user in Active Directory 34 | 05/21/2016 | SQLSat ATL

Power BI Login and SSAS—Option 2 § If the PBI login doesn’t match AD UPNs, use the new User Name Mapping Apparatus™ to fix 35 | 05/21/2016 | SQLSat ATL

Enterprise Gateway Authorization Overview (SQL & Other Sources) User Authentication handled by Power BI Gateway connects to SQL using supplied creds Everything runs as that account 36 | 05/21/2016 | SQLSat ATL • Everything • In other words: No RLS

More Enterprise Gateway Notes Direct. Query data sources act just like other data sources in Power BI Where to install the Gateway is a consideration Treat that “service” account for SSAS like you would any other Gateway service runs as NT SERVICEPBIEgw. Service Monitor with perfmon 37 | 05/21/2016 | SQLSat ATL

Caveats SSAS MD access requires SQL Enterprise Requires Power BI Pro subscription Remember that non-SSAS data source queries all run as that same user 38 | 05/21/2016 | SQLSat ATL

Agenda 38 Seconds on “Power BI” Where Does PBI “Get” its Data? How does On-Prem Data Work? The Power BI Gateways Enterprise Gateway Setup (demo) Enterprise Gateway In-Depth Bonus Demo!? 39 | 05/21/2016 | SQLSat ATL

Let’s Play with Perfmon & Do Some DR 40 | 05/21/2016 | SQLSat ATL

The Future! Changes come fast 41 | 05/21/2016 | SQLSat ATL This is out of Preview…Use it!

URLs/References § www. powerbi. com § Pricing/Feature comparison breakdown: http: //powerbi. microsoft. com/pricing § Main page for Enterprise Gateway How-To: https: //powerbi. microsoft. com/enus/documentation/powerbi-gateway-enterprise/ § Troubleshooting Enterprise Gateway: https: //support. powerbi. com/knowledgebase/arti cles/505324 -troubleshooting-analysis-serviceconnector 42 | 05/21/2016 | SQLSat ATL

Questions? 43 | 05/21/2016 | SQLSat ATL

Contact Kerry L. Tyler Blog: airbornegeek. com Twitter: @Airborne. Geek Email: kerry@dcac. co 44 | 05/21/2016 | SQLSat ATL