New York Power BI Dataset Design Brett Powell BI Consultant Author Objectives Definition of Datasets Dataset Design Process Import vs DirectQuery Factors Managed Data Access Layer Relationship Types and Patterns ID: 1040224
Download Presentation The PPT/PDF document "Welcome! Power BI User Group (PUG)" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
1. Welcome!Power BI User Group (PUG)New York
2. Power BI Dataset Design Brett PowellBI Consultant, Author
3. ObjectivesDefinition of Datasets Dataset Design Process Import vs. DirectQuery FactorsManaged Data Access LayerRelationship Types and PatternsUsability Tips and Examples Performance and Scalability Tips3
4. Session AgendaThe Role of Datasets in Power BIPlanning Scalable DatasetsEnd-to-End Dataset Development Data Source and Access Layers Data Model and MetadataDAX Measures and Client InterfaceTips and Examples:UsabilityPerformance and Scalability Analysis4
5. Meet Your PresenterBoston BI User Group (PUG) LeaderBI Consultant, Frontline AnalyticsAuthor of Power BI Book(s)Blogger, Insight QuestSites:http://insightsquest.comhttp://frontlineanalytics.netContact:Email: Brett.Powell@FrontlineAnalytics.netTwitter: @BrettPowell76LinkedIn5
6. Datasets in Power BI
7. Analytical Data Models (not Reports)Platforms for Reporting & AnalysisUser Interface for Client ToolsEmbedded Business LogicReports connect to DatasetsDataset Layers:Data Access and Transform (M Query)Data Model (Import or DirectQuery)DAX MeasuresPower BI Datasets DefinedThree Layers of PBI Datasets7
8. Intuitive User InterfaceVersion Control; ReusabilityData Security Query Performance ScalabilityAnalyticsAvailability ManageabilityDataset Design ObjectivesPower BI Publisher for ExcelPBIX Report: Live ConnectionAssigning Users/Groups to Row Level Security Roles8
9. Dataset Designers in Power BI TeamsDataset DesignersCollaborate with:Data Source OwnersReport AuthorsPower BI Admin(s)Data AccessPrivacy LevelsM (or SQL)AuthenticationData Refresh Data ModelDAX MeasuresSecurity RolesMetadataReport AuthorsCollaborate with:Business UsersDataset DesignersReports and DashboardsDesign StandardsInteractivityMobile ExperienceMobile OptimizedContent DistributionAppsSubscriptionsSupport Self-ServiceAnalyze in ExcelPower BI Admin(s)Collaborate with:O365 Global AdminGovernance TeamBI TeamTenant SettingsSecurity GroupsPremium CapacityCapacity AllocationPower BI LicensesOn-Premises GatewayUsage MonitoringResource MonitoringOrganizational Policies9
10. Planning Datasets
11. Four Step Dataset Design ProcessIdentify the Business ProcessDeclare the GrainIdentify the DimensionsDefine the FactsExpand and iterate on models:New metrics and attributes New fact and dimension tablesRevised DefinitionsPerformance TuningData Warehouse BUS Matrix11
12. Data SourcesData WarehousePower BI DesktopDataset PlanningWhere is the data? Cloud, On-Premises, BothDatabases, Files, BothHow much integration is required?Where can/should it be implemented?Are we targeting DirectQuery?HTAP (Hybrid Transactional Analytical)Implications for Data SourceImplications for DatasetFact and Dimension TablesSalesGeneral LedgerInventorySQL ViewsM QueriesDAX Calculated Tables and ColumnsDAX MeasuresETL/ELT Data Transformation Process123456Integrate and transform before PBI Dataset if possible (1-3)If not possible, write efficient M queries (4); DAX as last option (5-6) Plan12
13. DirectQuery FactorsDirectQuery Data SourcesSingle source and databaseScalability of Import ModelsSize limit; Full RefreshAnalysis Services RAM; MigrationHow complex is the model?Data Access and AnalyticsAvailable ResourcesDW or DB Relational Database SkillsFeatures and Hardware of Source DBPower BI DirectQuery Data SourcesAmazon RedshiftAzure HDInsight Spark (Beta)Azure SQL DatabaseIBM Netezza (Beta)Impala (v 2.x)Azure SQL Data WarehouseOracle (v12+)SAP BW (Beta)SAP HANASnowflakeSpark (v.9+) (Beta)SQL ServerTeradataDirectQuery Supported Data Sources as of 8/31 13
14. Scaling Power BI DatasetsPower BI Premium at GAIsolation (Noisy Neighbor)48X (vs 8X) Refreshes/DayRemove 10GB/User LimitPremium RoadmapLarge Datasets (1GB Current)Incremental Data Refresh Scale Out, Pin to MemoryAnalysis Services Migrate PBIX to SSAS ModelAzure (AAS) or On-PremisesPartitions, Perspectives, Display FoldersSource Control IntegrationAutomation, AdministrationCapacity NodesBackend CoresMemoryDirect or Live QueriesP1425 GB30/secP2850 GB60/secP316100 GB120/secPower BI Premium Capacities at GA (excluding EM)Azure Analysis Services Standard Tier Instances (as of 9/5/17)InstanceQPUsMemoryS04010 GBS110025 GBS220050 GBS4400100 GBS8320200 GBS9640400 GB14
15. Dataset Development
16. Data Warehouse Schema and ViewsDimension and Fact tablesReferential IntegrityRobust Date TableDate Intelligence ColumnsSlowly Changing DimensionsHistorical TrackingSQL Views for Dataset1:1 with Tables in PBIXDatabase Diagram: SQL Server16
17. Power BI Data Access: M QueriesData Source ParametersProd vs Dev Staging QueriesRefresh OnlyQuery GroupsFacts and DimsParameter TablesLoad OnlyM Queries17
18. Power BI Data ModelsColumnsData TypesSort ByPropertiesTablesRelationshipsHierarchiesMeasuresFormattingColumn Properties and MetadataRelationships View18
19. Relationship Pattern: Actual vs. PlanBridge tablesRelationshipsBidirectional: Dim to BridgeSingle: Bridge to PlanActual to Plan Measures:Check Filter ContextCompare Actual to PlanActual vs. Plan ModelingReturn Blank if Filter Context Not Supported by Plan 19
20. DAX Measures and Client InterfaceCompare and Combine FactsActuals versus BudgetOnline Sales Plus Store SalesDynamic Date ColumnsReport, Page, and Visual FiltersDate IntelligenceCompare Common PeriodsTarget MeasuresKPI and Gauge Goals/Targets20
21. Bonus: Dataset Documentation
22. Power BI Documentation Dataset and ReportIdentify SSAS Server and Database of PBIX via DAX StudioQuery SSAS DMVs via Analysis Services Connector (w/Params)Integrate DMVs to form documentation tablesRetrieving SSAS DMV Metadata into PBIX Sample Documentation Report Visual 22
23. Performance and Scalability
24. Segment 31 M RowsSegment 21 M RowsSegment 11 M RowsImport Dataset Performance and Scale TipsImport ModeHigh Cardinality ColumnsHigh Cardinality Relationships Optimal Sort OrderDAX OptimizationLeverage the Storage EngineEarly, efficient filtersLimit iterations and complex row operations2015201620171.51.81.923235.43.8DatePriceQtySalesImport Mode Columnar Storage24Order #123412351236Can fact table columns be removed?Can DAX Measures eliminate the need for columns?What is the optimal sort order?Are there any calculated columns that can be replaced?
25. DirectQuery Performance Test and TipsOptimize Data SourceColumnstore Index Referential IntegrityEfficient SQL ViewsOptimized DAX MeasuresUse Storage Engine FunctionsSee DAX Formula Compatibility in DQ ModeFact TableDurationCPUReadsClustered Columnstore with Ref Integrity1.3 sec2.3 sec54KPage Compressionwith Ref Integrity2.9 sec4.3 sec178KPage Compression without Ref Integrity3.4 sec4.9 sec178KTest ConditionsSQL Server 20164 CPU Cores, MAXDOP = 212M Row Fact TablesCommon Report QuerySales by Month for a yearDirectQuery Performance Test ModelDirectQuery Performance Test Results25
26. Capture and Analyze Power BI QueriesRetrieve DAX Query from PBIXSQL Server ProfilerAnalyze Query in DAX StudioImport or DirectQueryRetrieve SQL Statement of DirectQuery ModelProfiler Trace of Power BI Desktop DatasetAnalyze DAX Query with DAX Studio (Import Model)26
27. Thank you for Attending!Don’t forget to join your local PUG to enjoy year-round networking and learning. www.pbiusergroup.com/NYNJwww.pbiusergroup.com/philly