April 2021 Tabular Data Modeling Solutions for Challenging

April 2021 Tabular Data Modeling Solutions for Challenging Data Problems Paul Turley Principal Consultant, Microsoft Data Platform MVP Sql. Server. Bi. blog @paul_turley In/pturley April 2021

Thanks to our sponsors April 2021

April 2021 Tabular Data Modeling Solutions for Challenging Data Problems After mastering the fundamental principles of dimensional modeling, learn how to solve real-world problems with Power BI and DAX. We'll explore many-to-many relationships and using disconnected tables to drive dynamic calculations. We will explore options for using composite models to provide real-time access to source data and to combine assets from existing data models. April 2021

AGENDA • • • April 2021 Model schemas Dimensional modeling Role playing Bi-directional relationships Many-to-many Disconnected tables Switch & calculation groups External tools Composite models

Different Schemas for Different Needs Flat Model April 2021 Master/Detail Dimensional April 2021

The World is not Flat Table Flat April 2021

A Star Is Born Dimensional Model • • • General best practice Improved performance Accurate results Not an absolute for simple attributes There are (rare) exceptions April 2021

Dimensional Modeling Rules Who originally defined them? …these people: April 2021 Method: 1. 2. 3. 4. Select the business process Declare the grain Identify the dimensions Identify the facts Steps: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. Kimball. Group. co m April 2021 Load detailed atomic data into dimensional structures Structure dimensional models around business processes Ensure that every fact table has an associated date dimension table Ensure that all facts in a single fact table are at the same grain or level of detail Resolve many-to-many relationships in fact tables Resolve many-to-one relationships in dimension tables Store report labels and filter domain values in dimension tables Make certain that dimension tables use a surrogate key Create conformed dimensions to integrate data across the enterprise Continuously balance requirements and realities to deliver a DW/BI solution that’s accepted by business users and that supports their decision-making

Technical Business Modeling Process April 2021

Role Playing Dimensions April 2021

Role Playing Dimensions Playing Q: Should every fact key value have a corresponding dimension table & relationship? A: It depends April 2021

Bi Di Bidirectional Filters April 2021

M 2 Many To Many relationships April 2021

Disconnected Tables April 2021

SWITC H Switched Dynamic Measures Watch for: calculation groups April 2021

Demos Demonstrations • • Disconnected tables & dynamic measures Switched dynamic time-series measure Dynamic Rank X Orders Pending Delivery April 2021

Demos Who Needs Relationships Anyway? • Create and rely on relationships for primary functionality • Working outside of relationships affords freedom – using DAX April 2021

DQ Direct. Query & Composite Models Many possibilities… • Import mode: 98% of models • Direct. Query: edge cases • Composite models: advanced, mature models https: //docs. microsoft. com/en-us/power-bi/guidance/composite-model-guidance April 2021

The Future of Composite Models • Things are about to get really interesting! • Composite models over Power BI datasets and Azure Analysis Services April 2021

Pimpin’ the Blog Doing Power BI the Right Way • • • • Futureproofing Power BI solutions Preparing source data for Power BI Choosing the best option to shape and transform Power BI data Power Query design best practices Power Query in dataflows or Power BI Desktop Data modeling essentials and best practices in Power BI and AS tabular Validating data model results Planning for separation – data models and reports Power BI delivery options Choosing the right report type: analytic or paginated Designing and managing large datasets in Power BI Multi-developer and lifecycle management for Power BI Certified reports, certified datasets & the self-service mindset https: //sqlserverbi. blog/2020/07/12/doing-power-bi-the-right-way/ April 2021

Thank you Connect to me at: • Sql. Server. Bi. blog • Data. On. The. Road. blog Stay online for my live Q&A sessions April 2021