Comprehensive Guide to Optimizing Power BI Roads Where

Comprehensive Guide to Optimizing Power BI Roads? Where we’re going, we don’t need “Roads”.

Overview • Why Optimization Matters • Workspace Optimization • Dataflow Optimization • Dataset Optimization • DAX Optimization • Dimension Optimization • Fact Optimization • Report Optimization • Resources

Why Optimization Matters • Faster Reports • Faster Loads • • Faster Development • Reduce Complexity • Reduced Load Failures • Reduced Maintenance • Reduce Confusion • Reduce Expense • Single Source of Truth

Workspace Optimization • Manage with AAD Groups • Always Use Gen 2 Workspaces • • Dataflow & Dataset Workspaces • 1 Workspace for each BI Team • Report Workspaces • 1 Workspace for each BI Team • Power BI App Workspaces • 1 Workspace for each audience • Link to reports in Reports Workspace • Application Lifecycle Management (ALM)

Dataflow Optimization • Never duplicate entities • Individual Dataflows for Incremental • • Loads • Maximize Query Folding • Filter first • Limit Number of columns • Dataflow Layers • Land- raw data from source • Curate- transformed data from Land • Processed- enriched data from Curated

Dataset Optimization • Build Golden Datasets • 1 Dataset for Many Reports • • Use Row Level Security • Mix ‘In memory’ and ‘Direct Query’ for aggregate or detail data • Have a ‘User Build’ Strategy • Endorsement • None- for personal use • Promoted – for use by teams or departments • Certified – for use by organizations

DAX Optimization • NO NAKED MEASURES • Have all DAX measures in one table • • • Organize DAX measures in folders • Use Variables to minimize recalculations • Use CALCULATE function • Minimize iterative functions (SUMX) • Break up DAX expressions • Use Calculation Groups

Dimension Optimization • Integer Joins ONLY • Have a Date Dimension • • Reduce / Eliminate duplication • Minimize Cardinality (target < 127 k) • Minimize bi-directional joins • Move slowly changing fields to fact table • Minimize fields in tables • Organize fields in folders • Reduce text length • < 256 is good • < 100 is better • < 50 is best

Fact Table Optimization • ONLY INTEGERS / NUMERIC VALUES • NO TEXT FIELDS • • • Favor Long vs Wide Table Designs • Use Smart Date Key YYYYMMDD • Use Aggregated Tables • Review with Verti. Paq Analyzer • Review with DAX Studio

Report Optimization • Minimize Visuals • Use Backgrounds to provide shape and • flow • Minimize Grid / Table Details • Collapse and Drill • Apply Filters • Evaluate Most Expensive Visuals

THANK YOU

References - Workspaces • https: //docs. microsoft. com/en-us/power-bi/collaborate-share/service-new-workspaces • https: //docs. microsoft. com/en-us/power-bi/collaborate-share/service-create-the-new-workspaces • Guy in a Cube – Overview of Power BI Workspaces - https: //youtu. be/1 f. S 8 Mvca. BGE • Guy in a Cube – The new Power BI Workspace experience - https: //youtu. be/c. Kzd. DO 2 W 5 -Y • Guy in a Cube – Power BI Apps vs App Workspaces - https: //youtu. be/n-9 e 8 Atb. Ss. M

References - Dataflows • Matthew Roche - https: //ssbipolar. com/category/power-bi/dataflows/ • https: //docs. microsoft. com/en-us/power-bi/transform-model/service-dataflows-overview • https: //docs. microsoft. com/en-us/power-bi/transform-model/service-dataflows-create-use • https: //powerbi. microsoft. com/en-us/blog/introducing-power-bi-data-prep-wtih-dataflows/ • https: //www. sqlchick. com/entries/2019/3/28/three-ways-to-use-power-bi-dataflows • https: //www. kratosbi. com/dataflows-and-datasets-in-power-bi • Curbal - 5 tips to work faster in Power Query https: //youtu. be/Ev. QRh 4 Q 8 j. GA • Paul Turley – Power Query Performance Optimization https: //youtu. be/2 bz. JFr. JPg_c • Guy in a Cube – A quick look at Power BI Dataflows https: //youtu. be/veuxofp 0 ZIg • Guy in a Cube – Power BI Dataflows - https: //youtu. be/l. Xq 9 GDfpv 0 Y • Reza Rad – What are the use cases for Dataflows - https: //youtu. be/j. BP 66 FRats. Y

References - Datasets • https: //exceleratorbi. com. au/new-power-bi-reports-golden-dataset • https: //docs. microsoft. com/en-us/power-bi/connect-data/service-datasets-understand • https: //docs. microsoft. com/en-us/power-bi/connect-data/desktop-report-lifecycle-datasets • https: //radacad. com/power-bi-shared-datasets-what-is-it-how-does-it-work-and-why-should-you-care • https: //www. kratosbi. com/dataflows-and-datasets-in-power-bi • https: //www. mssqltips. com/sqlservertip/6155/power-bi-datasets/ • Guy in a Cube – 3 REASONS to use a single dataset - https: //youtu. be/OH 6 aek. Ijc. UI • Guy in a Cube – Shared & Certified datasets - https: //youtu. be/AZoe. T 4 ak. V 5 M • https: //powerbi. tips/2019/10/power-bi-datasets-a-method-to-the-madness/

References - DAX • https: //docs. microsoft. com/en-us/power-bi/guidance/power-bi-optimization • https: //www. sqlbi. com/p/introducing-dax-video-course/ • https: //dax. tips/ • https: //www. sqlbi. com/books/the-definitive-guide-to-dax-2 nd-edition/ • https: //www. sqlbi. com/books/analyzing-data-with-microsoft-power-bi-and-power-pivot-for-excel/ • https: //www. sqlbi. com/books/dax-patterns-2015/ • https: //maqsoftware. com/expertise/powerbi/dax-best-practices • https: //www. sqlbi. com/tv/dax-optimization-examples/ • https: //www. sqlbi. com/tv/power-bi-dax-optimization-examples/ • https: //powerpivotpro. com/2019/03/dax-optimizations-write-it-like-the-dax-calls-it/ • https: //www. sqlsaturday. com/Session. Download. aspx? suid=20639 • https: //www. youtube. com/watch? v=wvbz. Mwt. ZSKM • https: //www. element 61. be/en/resource/microsoft-power-bi-tabular-model-optimization

References – Data Modeling • https: //docs. microsoft. com/en-us/learn/modules/model-data-power-bi/ • https: //www. red-gate. com/simple-talk/sql/bi/power-bi-data-modelling/ • https: //www. sqlbi. com/p/introduction-to-data-modeling-for-power-bi-video-course/ • https: //www. sqlbi. com/books/tabular-modeling-in-microsoft-sql-server-analysis-services-2 nd-edition/ • Guy in a Cube – Data modeling best practices - https: //youtu. be/ki. VXI 7 zj. Sz. Y • Avi Singh – Implementing Power BI Data Modeling - https: //youtu. be/KJZ 7 do. Wu. Q 7 w • Microsoft – Data modelling in Power BI - https: //www. youtube. com/watch? v=NLb. W 9 Yft-5 M • https: //hub. packtpub. com/building-a-microsoft-power-bi-data-model/ • https: //dax. tips/ • https: //www. kratosbi. com/data-gods • https: //www. kasperonbi. com/

References - Reports • https: //docs. microsoft. com/en-us/power-bi/guidance/power-bi-optimization • https: //docs. microsoft. com/en-us/power-bi/create-reports/desktop-performance-analyzer • https: //www. sqlbi. com/articles/introducing-the-power-bi-performance-analyzer/ • Enterprise DNA – Using Performance Analyzer in Power BI - https: //youtu. be/E 0 l. Tc 4 m 5 -Tc • https: //www. mssqltips. com/sqlservertip/6131/power-bi-performance-analyzer/ • https: //alluringbi. com/

References – Verti. Paq Analyzer • https: //www. sqlbi. com/tools/vertipaq-analyzer/ • Guy in a Cube – Quickly review your Power BI Data model with Verti. Paq Analyzer- https: //youtu. be/1 A 7 g. FFvl. RFI • Guy in a Cube – How to use Verti. Paq Analyzer- https: //youtu. be/m 34 IOHc. NPPo • Curbal– What is Verti. Paq Analyzer and how it works- https: //youtu. be/b 8 o. Sg. TOe. YCE • https: //www. mssqltips. com/sqlservertip/5217/reduce-the-size-of-an-analysis-services-tabular-model--part-2/

References – DAX Studio Training • Power BI Tips– Introduction to DAX Studio Part 1 - https: //youtu. be/jp. Zn. CHRau. PU • Power BI Tips– Model Performance Tuning in DAX Studio Part 2 - https: //youtu. be/ERGp. V 4 yt 30 w • Power BI Tips– DAX Studio Full Feature Review Part 3 - https: //youtu. be/Soq. F-c 4 Kt. HQ • Power BI Tips–DAX Studio Query Performance Tuning Part 4 - https: //youtu. be/d 6 ppy. WQU-f 0

References – Blogs • https: //www. sqlgene. com/2019/09/27/a-comprehensive-guide-to-power-bi-performance-tuning/ • https: //www. skillwave. training/about-us/

References – Power Query • https: //itsnotaboutthecell. com/ • https: //exceleratorbi. com. au/

References – You. Tube • Guy in a Cube – https: //www. youtube. com/channel/UCFp 1 va. Kzpfvo. Gai 0 v. E 5 VJ 0 w • Curbal – https: //www. youtube. com/channel/UCJ 7 Uhlo. HSA 4 w. Aq. Pzyi 6 TOkw • Kratos BI – https: //www. youtube. com/channel/UCUNTl. JDKLzxx. Go 0 tm. KLbd. OQ • Avi Singh – https: //www. youtube. com/channel/UCRNm. Sv 7 m. APYi. C 0 Y 40 TJij. Aw • BI Elite – https: //www. youtube. com/channel/UC-h-w. Arcx. JC 8 z. BOD-Uxf. COg • Power BI Tips – https: //www. youtube. com/channel/UCPw. Pr. Ip. Zwlf. IKco. Up. Rwl 9 OQ • It’s Not About the Cell - https: //www. youtube. com/channel/UCfz 8 k. QWz. Tk. XU 0 er. BLjdc 4 dg • Microsoft Power BI - https: //www. youtube. com/channel/UCy--PYvw. Bw. Aeu. Ya. R 8 JLmrfg • Discover EI - https: //www. youtube. com/channel/UCFF 5 ea. Usht-Wz. Tdj. EAg. PDt. Q • BI Polar - https: //www. youtube. com/channel/UCpsil. Pn-2 q. Flr. YYuvy. Fkp. PQ • PASStv - https: //www. youtube. com/channel/UCCN 1 vy. Lawxr. XAi. TQoi 3 l. Now • How to Excel - https: //www. youtube. com/channel/UCRjl. X 8 o. Z 5 -El__s. VBlv. Lwwg