SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Optimizing Your BI Semantic Model for Performance and Scale

Here’s my notes from the ‘Optimizing Your BI Semantic Model for Performance and Scale (BIA-402-M)’ session at PASS Summit, presented by Akshai Mirchandani and Allan Folting of Microsoft.

Tabular Design Principles

  • Speed and performance
  • Optimize query performance over processing performance, focus on end-user experience.
  • Accommodate changes without forcing reload, if possible

Tabular Model Overview

  • Vertipaq is a column based data store.
  • Column based data store is good for analytical queries, can ignore columns that aren’t needed for analysis.
  • Column based data store provides better compression = faster performance.
  • Each column has multiple data structures associated to it (calculated columns are treated like regular columns).
  • Data Structures
    • Dictionary: Stores unique/distinct values found in a column.
    • Segments: Store integer values associated with the dictionary values.  A segment is the data size per read or unit of Vertipaq operation.
    • Hierarchies: Have separate data storage for efficiency, provide quicker access for querying.
    • Relationships: Accelerate lookups across tables.
    • Partitions: Contain a group of segments. Can be created on any table, no distinction between dimension and fact tables.  Partitioning in tabular is for data management purposes (ability to process partial data, expire old data, etc.).
  • Storage on Disk
    • Each column has an associated data file (.idf) that stores data IDs and values.
    • Segments are stored in .idf file.
  • Encoding
    • Map column values to data ID.  Advantage of data ID is compression.
    • Two types of encoding:
      • Value
        • Arithmetic operation to calculate data ID from column value.
        • Great for dense value distribution.
        • Allows computation on data IDs.
        • Stored in metadata.
      • Hash
        • Hash table to map values to data ID
        • Great for sparse value distribution.
        • Requires hash lookup for computation.
        • Persisted on disk.
    • Unique Identifier columns: don’t get much benefit from encoding.  Max number of distinct values allowed is 2 billion.
    • System chooses the encoding type for each column, this is not configurable.
    • Encoding selection (selecting between value or hash)
      • Sample rows and look at values (Sparse?, Dense?, String?)
      • Re-encoding may occur during processing. May change encoding to hash type if it is determined that value encoding won’t work.  Can be VERY expensive, requires all processed segments to be re-encoded.
  • System Views specific to Tabular (documented on MSDN)
    • Connect to tabular instance (in PowerPivot for example)
    • Example query
    • COLUMN_ENCODING column: 1 = Hash and 2 = Value
  • Configuration Settings
    • DefaultSegmentRowCount = 8M for AnalysisServices, = 1M for PowerPivot
      • Value must be power of 2, should be at least 1M.
      • Larger = Generally better compression, faster queries.
      • Smaller = Smaller working set during processing.
    • ProcessingTimeboxSecPerMRow
      • -1 (default) : 10 sec.
      • Smaller = Greedy algorithm, gives most gains in beginning.
      • Larger = Almost always better compression, higher query performance.
      • Increase for large number of columns (>200)
  • Processing
    • Phases
      • Read and encode data for segment N. Segment data is loaded sequentially in tabular.
      • Compress segment N and read and encode data for segment N + 1 (happen in parallel). Compression is a greedy-algorithm.
      • Continue for all remaining segments…
      • Other structures built at end (calculated columns, hierarchies, etc.).
    • Options
    • Incremental Processing
      • Typical
        • Create new partitions for new data
        • ProcessData on new partition or to reload into existing tables
        • ProcessRecalc to rebuild calculated columns, hierarchies, relationships.
      • Avoid
        • Multiple ProcessFull since each causes a Recalc, unless contained in a single transactional batch.  Better to do one Recalc.
    • Advanced Processing
      • Parallel processing
        • Use single transaction batch for processing tables in parallel
        • No parallel processing of a table’s partitions in SQL Server 2012
      • Use ProcessDefrag periodically
        • Deleting partitions may leave outdated values in dictionaries
      • Remove unused columns
        • Make sure you modify source query as well
      • Merge partitions to reduce segment fragmentation
        • Last segment will be incomplete, partial segments
        • Merging compacts the partial segments
      • Error handling
        • RI violations assigned a blank value, do not generate an error during processing
  • Querying
    • Two formula engines: MDX and DAX
    • Formula engine (FE) calls into Vertipaq to retrieve data
      • Query logic pushed to Vertipaq where possible
      • Vertipaq query executes in parallel, one core per segment
      • Optimized for execution on compressed format
      • VertiPaq level caching for chatty FE/Vertipaq communication
    • DAX/MDX vs. Vertipaq Query
      • DAX/MDX
        • Rich
        • Single-threaded per query
        • Designed for expressivity
      • Vertipaq
        • Simple
        • One core per segment
        • Heavily optimized for speed
        • Simpler queries pushed to Vertipaq from formula engine.
        • Will become more powerful over time
    • Inspecting Query Performance
      • Profiler
  • Capacity Planning
    • Memory
      • Ballpark of 3/10th of source data
      • Fully reprocessing a model will require double that (old version that queries are still working against and new version being built during processing)
      • Need to ensure enough memory available for queries, depends on query patterns (granularity, filters)
      • High level estimation: 1/2 of source data, to be safe. Paging is also possible, but not recommended
    • CPU
      • Lower end hardware is often better.  Newer/latest cores often take longer to reach high-end
      • Look for faster cores, better cache systems

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.


Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...