Processing Analysis Services Tabular Models Brett Powell Power
Processing Analysis Services Tabular Models Brett Powell Power. BIWorld. Tour. com #PBIWorld. Tour
About Me • BI Consultant • Frontline Analytics • Author • Power BI Cookbook • Mastering Power BI • Blog • Insight Quest • PUG Leader • Boston BI • Contact Info: • @Brett. Powell 76 • Brett. Powell@Frontline. Analytics. net Power. BIWorld. Tour. com #PBIWorld. Tour
Session Agenda • Tabular Model Objects and Processing Concepts • Processing Patterns • Orchestration Tools and Methods • Tuning and Optimization Power. BIWorld. Tour. com #PBIWorld. Tour
Tabular Model Objects and Processing Concepts Power. BIWorld. Tour. com #PBIWorld. Tour
What is an Analysis Services Tabular Model? • BI Semantic Layer • • • Intuitive; Self-Service Performance Version Control Security Analytics Scalability • Common Microsoft BI Model • Power BI • Azure Analysis Services • SQL Server Analysis Services Power. BIWorld. Tour. com Tabular Project in Visual Studio Power BI Fields List #PBIWorld. Tour
Why Process the Tabular Model? • Maximum Performance • Columnar, In-Memory, Compression • Maximum Analytical Flexibility • No Restrictions on DAX Functions • Maximum Data Integration Flexibility • Power Query (M) • Limited by Size (RAM) and Data Freshness Requirement • Composite Models and/or Aggregations address common tradeoffs Power. BIWorld. Tour. com #PBIWorld. Tour
Tabular Data Storage 1. Data is fully memory resident 2. Columns stored separately • Dictionary Encoded (Hash or Value) 3. Columns are compressed • Compression driven by cardinality (uniqueness) of column Delivery Order ID Date Quantity Price Amount 1 10/17/2018 3 $2. 00 $6. 00 2 10/18/2018 2 $2. 50 $5. 00 3 10/18/2018 1 $3. 00 4 10/20/2018 2 $3. 00 $6. 00 Power. BIWorld. Tour. com #PBIWorld. Tour
Processing Objectives and Constraints • Time Window • Availability for Queries • Tabular Server Resources • Query Performance • Source System Resources • Manageability Power. BIWorld. Tour. com Better Compression More Time to Process Reduced Tabular Server Resources Reduced Availability for Queries Faster Processing via Parallelization Greater Tabular Server and Source Resources #PBIWorld. Tour
Processing Phases Phase 2: Make Queryable Phase 1: Process Data & Dictionary Read Data • Query source system • • Load via Power Query • Stream data to Analysis Services if possible Encode Compress Dictionary of distinct values • • Value Encoding • Hash Encoding Power. BIWorld. Tour. com Run-length Encoding (RLE) • • Pointers to rows with value A (e. g. 17 to 25) #PBIWorld. Tour Recalculate Derived Structures • Relationships • Hierarchies • Calculated Columns and Tables
Tabular Model Processing Internals 1. Column dictionaries • Column segments (data) 2. Calculated columns (DAX) • Computed after compression 3. Hierarchies • User and System • MDX Queries 4. Relationships • Size drives query performance 5. Partitions • 1+ Segment 2 3 4 1 • Data: Source Data, Column Dictionaries • Derived Structures: Relationships, Hierarchies, DAX Calculated Columns or Tables 5 Example: Sales Fact Table with 100 M rows • 5 Partitions (2014 – 2018), 20 M rows per partition • Internally: 15 segments (max of 8 M/segment; default) Power. BIWorld. Tour. com • Two types of objects processed: #PBIWorld. Tour
Processing Operations • Full • Data • Clear • Default • Add • Default • Defrag • Transactions • Split processing to reduce resource usage • Parallelization • Enabled by default Operation Database Table Partition Full Available Data Not Available Clear Available Recalc Available Not Available Default Available Available Not Available Add Defrag Process Operations by Tabular Object • Tables and Partitions • Configurable via TMSL Power. BIWorld. Tour. com #PBIWorld. Tour
Table Partitions • Defined Rows of a Table • 1+ partition per table • 1+ segment per partition • 8 M rows per segment (default) compressed • 1 CPU Core per segment in parallelized queries • Avoid Over-Partitioning • Primary Use Case: • Reduce processing time and resources • Improve manageability Partition Manager in SQL Server Data Tools (SSDT) • Other Use Case: • Consolidate source data Code View of Model. bim in SSDT Power. BIWorld. Tour. com #PBIWorld. Tour
Processing Patterns Power. BIWorld. Tour. com #PBIWorld. Tour
Processing Patterns Overview • Patterns are primarily driven by: • Other Factors: • Processing time window • Availability of model for queries • Available RAM during processing job Simple • • Impact on source systems Available skills (Power. Shell, TMSL) Performance Optimizations Manageability Intermediate Advanced Model Size Small Medium Large Transactions 1 -2 2 -5+ Many Operation Types Full Only Mixed – Data & Recalc Diverse, Dynamic Partitions None Years, Months, or Weeks Days, Multiple Grains Scripting None to Minimal Cmdlets or Simple TMSL Custom Automation N/A Periodic Maintenance Fully Automated Tabular Processing Job Patterns Power. BIWorld. Tour. com #PBIWorld. Tour
Processing Approach Examples 1. Single Process Full of Database • Maximum memory (2 X+ of database) and time to process 2. Multiple Process Full Transactions • Maintain availability for queries, • Reduce memory required for process 3. Multiple Process Data and Process Recalc Transactions • Eliminate unnecessary Recalc operations • Include Recalc in same transaction to maintain availability 4. Process Clear Transaction followed by Process Full or Data + Calc • Significantly reduce memory of processing • Include Recalc in same transaction to maintain availability Power. BIWorld. Tour. com #PBIWorld. Tour
Best Practice: Keep Model Queryable • Eliminate unavailability, even if refresh process fails • Process Data and Clear transactions result in unavailability • Two options to implement: 1. Process Full operations exclusively 2. Include Process Recalc in same transaction as Process Data or Clear • Secondary Option: • Process Recalc in transaction immediately following Process Data or Clear • Unavailability period limited to duration of Process Recalc Power. BIWorld. Tour. com #PBIWorld. Tour
Tabular Model Scripting Language (TMSL) • Command Object Model Syntax for Tabular databases • 1200 Compatibility Level+ (SSAS 2016) • Refresh Command • Type parameter (“full”, “data. Only”, …) • Sequence Command • Batch Mode: Multiple operations in single transaction • Some model properties exclusive to TMSL • Store TMSL Scripts in XMLA Files Code View of Analysis Services Project in Visual Studio • Reference XMLA files from Power. Shell • Provides full control over processing • Multiple process operations in single transaction • Control/limit parallelization • TMSL Reference Power. BIWorld. Tour. com TMSL in XMLA Query File #PBIWorld. Tour
Analysis Services Power. Shell Cmdlets • Analysis Services cmdlets included in SQLServer module • Invoke-ASCmd to pass custom TMSL to server Analysis Services Processing Cmdlets in SQL Server Module • Can blend Power. Shell dynamic logic/variables with TMSL • Analysis Services Power. Shell Reference Power. BIWorld. Tour. com In-line TMSL Command via Invoke-ASCmd Cmdlet #PBIWorld. Tour
Bonus Demo: Last Processed & Row Counts • Query DMV to Retrieve Last Refreshed • Any table in model • Adjust for UTC Time • Query model to retrieve row counts DMV Metadata • Pass DAX Query to Tabular Model DAX Row Count Query Power Query Editor Power. BIWorld. Tour. com #PBIWorld. Tour
Getting Started with Processing • SSMS • Demo: Queryable State • Process Full • Scripting Out example • Agent Jobs • Insert image from SSMS Power. BIWorld. Tour. com #PBIWorld. Tour
Tabular Model Processing Examples • Intermediate Examples: • • Weekly, Monthly, Yearly Partitions Power. Shell only Power. Shell and TMSL Dynamic Partition Processing • Azure AS or SSAS Power. BIWorld. Tour. com #PBIWorld. Tour
Orchestration Tools and Methods Power. BIWorld. Tour. com #PBIWorld. Tour
SQL Server Integration Services (SSIS) • Align Processing with Data Warehouse ETL/ELT • Leverage SSIS features (checkpoints, logging, email…) • Analysis Services Tasks • Analysis Services Processing Task • Analysis Services Execute DDL Tasks • Pass custom TMSL Command (inline or file) • Power. Shell script via Execute Process task • Use with Azure AS or SSAS SSIS Package with Processing Tasks Power. BIWorld. Tour. com #PBIWorld. Tour
Tuning and Optimization Power. BIWorld. Tour. com #PBIWorld. Tour
Bonus Demo: Memory Cost Analysis • Power BI report to analyze cost of: • Power BI Premium • Azure Analysis Services • Bookmarks and What-if slicers • Memory sizes and pricing variables embedded in PBIX Memory Cost Analysis Page: Power BI Premium Power. BIWorld. Tour. com #PBIWorld. Tour
Top Model Optimization Techniques • Remove or split high cardinality columns • Low cardinality columns compress better; lower memory • Examples: Order Number, Timestamp columns • See SQL BI blog for examples • Remove or replace calculated columns on fact tables • Calculated columns do not compress like standard columns • Move logic to data source or Power Query if possible • Size partitions to fill segments • 8 M+ rows default • 1 CPU core per Segment in queries • Fixed decimal Number (Currency) over Decimal Number if possible • Experiment with custom sort order based on importance of columns • Evaluate with Vertipaq Analyzer; ensure resources are available for sort Power. BIWorld. Tour. com #PBIWorld. Tour
Tabular Optimization Features • Value Encoding Hint • Is. Available. In. MDX • Query Memory Limit • Default Segment Size Value Encoding Hint in Visual Studio is. Available. In. Mdx Property in Visual Studio Analysis Services Instance Settings in SSMS Power. BIWorld. Tour. com #PBIWorld. Tour
Vertipaq Analyzer • Power Pivot for Excel tool for evaluating memory composition of Tabular models • Retrieves DMV data • DISCOVER_STORAGE views • Dedicated report pages • Tables, Compression • Encoding, Data Types Vertipaq Analyzer: Diagram View in Excel Power. BIWorld. Tour. com #PBIWorld. Tour
Don’t forget to join your local PUG to enjoy year-round networking and learning.
Power. BIWorld. Tour. com #PBIWorld. Tour
- Slides: 31