Data modelling for Power BI using brand new

Data modelling for Power BI using brand new Analysis Services Features Phil Seamark Power. BIWorld. Tour. com #PBIWorld. Tour

Phil Seamark - MVP • • • Radacad Wellington Power BI User Group Microsoft MVP Author Active in Power BI Community • @Phil. Seamark • phil@radacad. com Power. BIWorld. Tour. com #PBIWorld. Tour

Session Agenda • Incremental Refresh • The problem • The solution • The benefits • In memory Aggregations • The problem • The solution • The benefits • Questions and Giveaways Power. BIWorld. Tour. com #PBIWorld. Tour

Incremental Updates Power. BIWorld. Tour. com #PBIWorld. Tour

Incremental Refresh • The Problem • Large data sets refresh IN FULL each time • Refresh duplication • Compounded when refreshing multiple times per day Power. BIWorld. Tour. com #PBIWorld. Tour

Incremental Refresh • The Solution • Unlock and use SSAS Partitions • Auto scripted in the Webservice • Elegant solution using Merge partitions (Merge , Drop & Recovery) • The table to be incrementally refreshed needs to have a Date column • Some manual configuration required (see demo) Power. BIWorld. Tour. com #PBIWorld. Tour

Demo : Incremental Update Power. BIWorld. Tour. com #PBIWorld. Tour

Incremental Refresh • The Benefits (and notes) • Available now!! • Speed of refreshing from data source. The larger the table, the bigger the benefit • Less load on data source • Premium is required for now – but IS coming to shared • XMLA endpoints later this year Power. BIWorld. Tour. com #PBIWorld. Tour

In-memory Aggregations Power. BIWorld. Tour. com #PBIWorld. Tour

In-memory Aggregations • The Problem • I have a very large data set and want to user Power BI to analyse and report • Queries against very large tables are inefficient • Slow reports even with smaller data sets Power. BIWorld. Tour. com #PBIWorld. Tour

In-memory Aggregations • The Solution • Aggregation tables (Summary tables) • Composite models • Mapping Columns • Table Precedence • Profiler – new events • Storage modes (Import, Direct Query and DUAL) …… Power. BIWorld. Tour. com #PBIWorld. Tour

Import vs. Direct. Query Import Power. BIWorld. Tour. com #PBIWorld. Tour

Direct. Query Geography Customer Sales Date Product Reseller Sales Employee Power. BIWorld. Tour. com Employee #PBIWorld. Tour

Direct. Query IMPORT Direct Query Geography Customer Sales Date Product Reseller Sales Employee Power. BIWorld. Tour. com #PBIWorld. Tour

Direct. Query IMPORT Direct Query Geography Customer Sales Agg Date Product Reseller Sales Employee Power. BIWorld. Tour. com #PBIWorld. Tour

Aggregations IMPORT Direct Query Geography Sales Agg Customer Sales Date Product Reseller Sales Employee Power. BIWorld. Tour. com #PBIWorld. Tour

Aggregations IMPORT Direct Query Geography Sales Agg Customer Sales Date Product Reseller Sales Employee Power. BIWorld. Tour. com #PBIWorld. Tour Summarize. Columns( Date[Year], Geography[City], "Sales", Sum(Sales[Amount]) )

Aggregations IMPORT Direct Query Geography Sales Agg Customer Sales Date Product Reseller Sales Employee Power. BIWorld. Tour. com #PBIWorld. Tour Summarize. Columns( Date[Year], Customer[Name], "Sales", Sum(Sales[Amount]) )

Aggregations IMPORT DIRECT QUERY Geography Sales Agg DUAL Customer Sales Date Product Reseller Sales Employee Power. BIWorld. Tour. com “Many side” “One side” Dual Import or Dual DQ DQ or Dual #PBIWorld. Tour

Aggregations IMPORT Invalid Geography Sales Agg DIRECT QUERY DUAL Customer Sales Date Product Reseller Sales Employee Power. BIWorld. Tour. com “Many side” “One side” Dual Import or Dual DQ DQ or Dual #PBIWorld. Tour

In-memory Aggregations DB Table Storage Mode Dimension Agg_A Import Year + Measures 14 100 Kb Agg_B Import Date + Measures 5, 110 32 MB Agg_C Import Date, hour, Payment + Measures 585, 774 39 MB Agg_D Import or Direct Query Date, hour, Payment, Geography 257, 423, 110 8. 8 GB Trip Direct Query ALL 2. 3 Billion ½ TB Power. BIWorld. Tour. com #PBIWorld. Tour Rows Size

Demo : In-memory Aggregations Power. BIWorld. Tour. com #PBIWorld. Tour

In-memory Aggregations • The Benefits (and notes) • Speed, speed and more speed • Can use incremental refresh with AGG tables • DQ tables need to be from the same source to take full advantage for DUAL • Not a one click solution – still requires thought and care • Not for every Power BI scenario • If you have a slow report…. . Power. BIWorld. Tour. com #PBIWorld. Tour

Questions and Prize Draw Power. BIWorld. Tour. com #PBIWorld. Tour

A Big Thanks to Our Knowledge Partners Power. BIWorld. Tour. com #PBIWorld. Tour Data Platform Summit 2017 is a community initiative by e. Dominer Systems

Don’t forget to join your local PUG to enjoy year-round networking and learning.