RELATIONSHIP MODELING PATTERNS IN SSAS AND POWER BI

RELATIONSHIP MODELING PATTERNS IN SSAS AND POWER BI SQL SATURDAY BOSTON (9/23/2017)

OBJECTIVES 1) Relationship Fundamentals 2) Bidirectional Relationships 3) Dimension Measures 4) Actual vs. Plan (Multiple Grains) 5) Dynamic Row-level Security (RLS) 6) Virtual Relationship Patterns 7) Many-to-Many Relationships 2

SESSION AGENDA Part 1 • Datasets: • Power BI and SSAS Tabular Models • Import and Direct. Query • Relationship Fundamentals • Row Identity, Ambiguity • Relationship Data Structures • Filter Propagation Part II Pattern Examples: • Single and Bidirectional Relationships • Role Playing Dimensions • Dimension Measures • Actual vs. Plan • Dynamic Row-level Security • Virtual Relationships • Many to Many 3

ABOUT ME • Boston BI User Group Leader • BI Consultant, Frontline Analytics • Author of Power BI Book(s) • Blogger, Insight Quest • Sites: • http: //insightsquest. com • http: //frontlineanalytics. net • Contact: • Email: Brett. Powell@Frontline. Analytics. net • Twitter: @Brett. Powell 76 • Linked. In 4

DATASETS DEFINED • Analytical Data Models (not Reports) • SSAS Tabular Instance inside Power BI Desktop Calculations • DAX Measures • Platforms for Reporting & Analysis • User Interface for Client Tools • Embedded Business Logic Data Model • Reports connect to Datasets • Hierarchies, Metadata • Relationships • Tables, Columns Queries • Dataset Layers: 1. Data Access and Transform (M Query) 2. Data Model (Import or Direct. Query) 3. DAX Measures • Tables, Columns • Transformations (Optionally) • Data Source Three Layers of PBI Datasets 6

DATASET DESIGN OBJECTIVES • Intuitive User Interface • Version Control; Reusability • Data Security • Query Performance • Scalability PBIX Report: Live Connection Power BI Publisher for Excel • Analytics • Availability • Manageability Assigning Users/Groups to Row Level Security Roles 7

DATASET DESIGNERS IN POWER BI TEAMS Dataset Designers • Collaborate with: • Data Source Owners • Report Authors • Power BI Admin(s) • Data Access • Privacy Levels • M (or SQL) • Authentication • Data Refresh • Data Model • DAX Measures • Security Roles • Metadata Report Authors • Collaborate with: • Business Users • Dataset Designers • Reports and Dashboards • Design Standards • Interactivity • Mobile Experience • Mobile Optimized • Content Distribution • Apps • Subscriptions • Support Self-Service • Analyze in Excel Power BI Admin(s) • Collaborate with: • O 365 Global Admin • Governance Team • BI Team • Tenant Settings • Security Groups • Premium Capacity • Capacity Allocation • Power BI Licenses • On-Premises Gateway • Usage Monitoring • Resource Monitoring • Organizational Policies 8

• Row Identity RELATIONSHIP FUNDAMENTALS • Single Column Uniqueness Enforced • Referential Integrity • In-Memory Mode: Not Enforced • Direct. Query Mode: Defined by Author • Active and Passive • Role Playing Dimensions • Crossfiltering • Single and Bidirectional • Unambiguous Filter Path • Single Relationship Path Edit Relationships in Power BI Desktop 10

RELATIONSHIPS BY STORAGE MODE • Import Mode • • • Default Mode In-Memory Columnar and Compressed Many Data Sources Supported Multiple Sources per Model • Direct. Query Mode • Near Real-Time Analytics • Semantic Layer Only • SQL Queries Sent to Source • Limited Data Sources • Single Database per Model Relationship Structures of In-Memory Model per SSAS DMV SQL Query Generated by Direct. Query Model 11

RELATIONSHIPS IN FILTER CONTEXT 1. Filter Context Detected • Off Canvas + On Canvas 2. Calculate() or Calculate. Table() • Add, Remove, or Overwrite 3. Model Tables Filtered 4. Related Tables Filtered • Single or Bidirectional 5. Metric Calculation Executed • Filter context for each cell 1. 2. 3. 4. 5. Five Filters Applied to Matrix Cells (ex Subtotals) Date Table = Current Year or Prior Year Promotion Table = Excess Inventory or Volume Discount Sales Territory Table = Europe Product Table = Product Category (Rows) Customer Table = Customer Marital Status (Columns) 12

• Main Benefits GETTING STARTED WITH STAR SCHEMA • Usability • Performance* • Single Direction Only Star Schema Data Model • Demo: • Active Rows • Filter Context vs SQL Query Row Count Metrics by Table to Indicate Filter Context 14

• Many-to-One Filter Context INTRO TO BIDIRECTIONAL RELATIONSHIPS • Use Cases • Many-to-Many • Multiple Grains • Row Level Security • Edge Cases • CROSSFILTER() • Measure-specific relationship filtering • Anti-Patterns • Relationship to Date Table • Common Dimension to Multiple Fact Tables Bidirectional Relationship from Internet Sales to Product Rows (Online Sales) = CALCULATE([Product Rows], CROSSFILTER('Internet Sales'[Product. Key], 'Product'[Product. Key], Both)) Bidirectional cross filtering via DAX 15

ROLE PLAYING DIMENSIONS • Role Playing Dimension Patterns • Dedicated Tables vs. Passive Relationships • Usage/Frequency of Alternative Dates? Date Table with Inactive Relationships 16

DIMENSION MEASURES Example: Four Measures • Product Rows • Distinct Products Sold • Distinct Products Not Sold Two Options • Invoke Filter Context of Fact Table • Bidirectional Relationship Multiple Definitions of Count of Products Distinct Products Sold Online = CALCULATE([Distinct Products], 'Internet Sales’) Distinct Products Not Sold Online = CALCULATE([Distinct Products], FILTER(ALL('Product'[Product Alternate Key]), ISEMPTY(RELATEDTABLE('Internet Sales’)) ) ) Filter Context via DAX 17

ACTUAL VS. PLAN VIA BIDIRECTIONAL 1. Bridge Tables for Plan Grain 2. Bidirectional Relationships • Dims to Bridge Tables 3. Single Relationships • Bridge Tables to Plan 4. DAX Measures to Test Filter Context Date Table with Inactive Relationships 18

DYNAMIC ROW LEVEL SECURITY 1. Users Table of UPNs 2. Permissions Table for Users • Many Countries per User 3. Bridge Table of Countries 4. Bidirectional Relationship 5. Security Role: • USERPRINCIPALNAME() User and User Permissions Table for Dynamic Security Users Table filtered by User Principal Name Measure 19

ACTUAL VS. PLAN VIA VIRTUAL RELATIONSHIP 1. DAX Measures to Filter Plan 2. DAX Measures to Test Filter Context Plan Measure Filtered via DAX No Physical Relationships to Plan 20

MANY-TO-MANY PATTERN • Scenario: • Many Customers per Account • Many Accounts per Customer • Pattern: 1. Bridge Table of Customer Keys 2. Bidirectional Relationship DAX Alternative: • From Bridge to Accounts Filter Applied via DAX functions (TREATAS()) M 2 M Tran Amount = CALCULATE([Tran Amount], SUMMARIZE(Customer. Account, Accounts[Account ID]) ) 21

The End 22