SQL Saturday Cork Planning and Deploying PBIRS on

SQL Saturday Cork Planning and Deploying PBIRS on premise! @Bob_Duffy

Bob Duffy • • • 22 years in database sector, 250+ projects SQL Server MCA, MCM, MVP SSAS Maestro Senior Data Platform Consultant with Microsoft 2005 -2008 Database Architect at Prodata SQL Centre of Excellence http: //blogs. prodata. ie/author/bob. aspx bob@prodata. ie

Agenda • • Overview and Features Install and Config Hardware and Capacity Planning Authentication and Kerberos Service Accounts Licensing Migration

The Paginated Report (aka RDL)

What’s wrong with paginated SSRS ? Good Bad Fantastic Skills in the market Self service flopped Pixel perfect printing Poor for ad hoc analysis Hugely scalable Little to no Interaction Cost Effective Licensing: per Core & bundled with SQL Product Limited data visualization Great SDLC Mobile Reporting No Analytics Integration Really an “IT” Tool No dashboarding No KPI concept Not sexy like tableau or Power. BI No real mashup

The official features for PBI Report https: //docs. microsoft. com/en-us/power-bi/whitepapers

Lifecycle and Release Changes • Modern Lifecycle • • Only 12 months support in any release MUST stay current • Release Schedule: • Multiple times a year

Five Main Benefits – IN A SINGLE Portal Feature Benefit Power BI Reports Self Service Interactive Analytics Integration Mobile friendly Data Mashup Sexy ; -) Mobile Reports (x 2) First Class KPIs Best of breed mobile dashboards Offline data caching And Power. BI Mobile too. Office Online Integrated Excel in the Web App SSAS Pivot tables Excel reports and dashboards Ah Hoc Analysis Paginated Reports Latest classic SSRS Engine Pixel perfect printing Static Reporting Email distribution Embedded Analysis Services Host Power. BI Models in local SSAS 1 -2 GB max size

Installation and Configuration

Step 1 Download

Step 2 Run Installer

Step 3 Configure

Step 4 Browse to Web Portal http: //localhost/reports

Manage - PBI + Report Server • Like SSRS. Three config options Ø SSMS Ø Settings on Portal Ø Config file

New Settings for Power BI We haven’t needed to change any (Apart from Office. Online. Discovery. URL)

Office Online Deployment Manual download and Powershell Install Register in Power. BI SSRS Site Settings https: //docs. microsoft. com/en-us/power-bi/report-server/excel-oos

What’s Installed on the Server? Demo

Technical Architecture and Scalability Key Technical Architecture Questions 1. Number and Type of users 2. Support for extranet users v DMZ ? 3. Load Balance or not ? 4. Full SSAS Tabular or Cached Data ?

Capacity Planning https: //docs. microsoft. com/en-us/power-bi/report-server/capacity-planning

Selecting the Right hardware • Sensitive to CPU Speed and Ram Performance • Recommendations: • Intel E 5 -26** Series ok • Intel Skylake+ better (Platinum Series) • Consider locking Turbo mode if you want best performance (and don’t pay the power bill) • Use the fastest ram (2, 400 Ghz+) • Virtualization can bump up license price ! • Don’t use NUMA on large tabular models

Identity in distributed MSBI Solutions Windows SSRS Office Online Power BI Trusted Account Kerberos Constrained Delegation Kerberos Delegation Cloud Impersonation Custom (Forms / Key) Anonymous

Service Accounts for PBIRS Type Example Pros / Cons Virtual Service Account NT ServiceReport. Server Default Automatic SPN Configuration Doesn’t work in Scaled out Config Network Service No Domain account required Automatic SPN Configuration Works in Scaled out Config Domain Account DOMAINsvc. SSRS Centralised Management Manual SPN Configuration Preferred by some security teams Recommendation: - Use Virtual Service Accounts in simple environments - Use Domain service account in more complex environments (multiple names, co-habiting with IIS) https: //docs. microsoft. com/en-us/power-bi/report-server/configure-kerberos-powerbi-reports

Configuring Kerberos 1. 2. 3. 4. Configure SPN for remote data (SQL/SSAS) Config SPN for SSRS Add delegation to service account Change SSRS Config File https: //docs. microsoft. com/en-us/power-bi/report-server/configure-kerberos-powerbi-reports

Licensing – Power BI Premium https: //powerbi. microsoft. com/en-us/calculator/

Licensing Example 1, 000 users • SQL Server EE with Software Assurance • • • Bought in 2 core packs, about 10 k euro a core All manner of discounts and payment deals About 80 k over 3 years • or • Power BI Premium • • Dedicated cloud cores with ”free” on premise cores Example for a P 1 8 cores is about € 150 k over 3 years • Plus • 200 * 8. 40 pm=€ 20 k pear year. https: //powerbi. microsoft. com/en-us/calculator/