Power BI Internals Eugene Meidinger sqlgene www sqlgene

Power BI Internals Eugene Meidinger @sqlgene www. sqlgene. com/powerbi/

About me • Business Intelligence Developer • Certified in Querying and Administering SQL Server • Summit Speaker • Pluralsight author • Worked for All-Lines / Lantek for 5 years • Went from SQL newb to SQL pro

Why an Power BI Internals? • Sell the Steak, not the Sizzle • Power BI is more than flashy graphics • Power BI is a whole ecosystem • Power BI is a pain to learn • New technology • Focus on entry level issues • Lack of training

What is Power BI? • Cloud-based data visualization and data modeling tool • Competitor to Tableau and Qlikview • Used to be excel addins • Good for: • • KPI’s and Dashboards Self-service analysis Integrating various data sources Saa. S BI

What are we going to cover? Power Query Power BI Desktop DAX Data Gateway Power BI Service Visualizations

Power BI Desktop • Free desktop report authoring tool • You pay for infrastructure, not tooling • The shaver is free so they can charge for blades • Used to create reports • Isn’t really designed for report viewing • Uses the PBIX file format

Power BI Desktop

PBIX Format • It’s just a zip file • Terrible for source control • One. Drive is the recommended way for versioning • Contains: • • Data model Power. Query queries Data visualizations ? ? ?

Demo – Opening a pbix file

Power Query • Declarative language for data wrangling • Macro language for cleaning up your data • If you clean data by hand, this tool is for you • If your data all lives in SQL, you don’t need it • Half of the functionality makes more sense if you live in flat files • It makes sense if your data is gross and dirty

What does the code look like?

What does the GUI look like?

Query folding • Some Power. Query steps can get pushed back to SQL • This can be more performance • Has to happen early on • All of the foldable items have to happen first • Things like string concatenate and filtering are foldable • Things like split by delimiter are not

Lazy evaluation • Similar to function programming languages • Expressions are only evaluated when needed • This means parts of your code may never get used • This means you won’t pull unused columns

Demo – Power. Query

DAX Language • Data modeling language • Adds business logic • Adds meaning and semantics • Great for aggregates • Great for filtering

Learning Curve (The Problem With DAX) Evaluation contexts Nesting evaluation contexts Time Intelligence Difficulty Filtering Scalar functions Measures Calculated columns Functionality http: //www. sqlgene. com/2017/03/30/slides-for-pass-bi-virtual-group/

Dax Engine / Vertipaq Engine • Runs in memory • Columnar database • Columns of data are stored together • Data is encoded and compressed • Quick results • Columns are the fundamental unit of measure

Vertipaq Stores Data as Columns • Super efficient for simple aggregations • Many aggregate functions take columns as parameters • DAX Language is optimized for single-column operations

Color State Quantity Blue Green Red Red PA OH WV OH 1 5 2 3 5 6 2

Compression and encoding • Value Encoding • Dictionary Encoding • Run Length Encoding • Depends on sorting

Color Blue Green Red Red Color Blue, 1 Green, 2 Red, 4

Demo compression

Power BI Engine == SSAS Tabular • Same concepts apply to both • Running SSAS Tabular locally • Runs on a random port • You can connect to it with SSMS / Excel / Trace

Demo – Connect to to Power BI

Data Gateways • Data gateways allow you to access on-premises data • Two types: • Personal Data Gateway • On-premises Data Gateway • Three query modes: • Import • Direct. Query • Live connections

Power BI Service • Saas for hosting and displaying your Power BI reports • Similar to Office 365 or Share. Point Online • $10 per month for pro features • Free version is very limited • Security is managed by Azure AD

Power BI Service

Power BI Service is built on Azure • Azure Traffic manager • Azure Active Directory • Blob Storage (For your data) • Azure SQL Database (For metadata) • Service Bus

Security Whitepaper https: //powerbi. microsoft. com/en-us/documentation/powerbi-admin-power-bi-security/

Your data is tied to a specific datacenter • Determined by your country when you sign up • https: //powerbi. microsoft. com/en-us/documentation/powerbiadmin-where-is-my-tenant-located/

Visualization engine • HTML • CSS • Javascript • D 3. js

Community Visualizations

Demo – Opening a visualization

Resources • Getting started with Power BI by Stacia Misner Varga • Analyzing and Visualizing Data with Power BI By EDx and Microsoft • Power Pivot and Power BI By Rob Collie and Avi Singh • Applied Power Bi By Teo Lachev • Power. BI on Youtube • PASS BI Virtual Chapter • More Resources -> http: //www. sqlgene. com/powerbi/