Encoding Hints and SQL Server Analysis Services vNext CTP 1.3

  • Article

The public CTP 1.3 of SQL Server vNext on Windows is available here! The corresponding versions of SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS) will be released in the coming weeks. They include much-anticipated new features, so watch out for the upcoming announcements!

Encoding hints

CTP 1.3 introduces encoding hints, which is an advanced feature used to optimize processing (data refresh) of large in-memory tabular models. Please refer to the Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services whitepaper to better understand encoding. The encoding process described still applies in CTP 1.3.

  • Value encoding provides better query performance for columns that are typically only used for aggregations.
  • Hash encoding is preferred for group-by columns (often dimension-table values) and foreign keys. String columns are always hash encoded.

Numeric columns can use either of these encoding methods. When Analysis Services starts processing a table, if either the table is empty (with or without partitions) or a full-table processing operation is being performed, samples values are taken for each numeric column to determine whether to apply value or hash encoding. By default, value encoding is chosen when the sample of distinct values in the column is large enough – otherwise hash encoding will usually provide better compression. It is possible for Analysis Services to change the encoding method after the column is partially processed based on further information about the data distribution, and restart the encoding process. This of course increases processing time and is inefficient. The performance-tuning whitepaper discusses re-encoding in more detail and describes how to detect it using SQL Server Profiler.

Encoding hints in CTP 1.3 allow the modeler to specify a preference for the encoding method given prior knowledge from data profiling and/or in response to re-encoding trace events. Since aggregation over hash-encoded columns is slower than over value-encoded columns, value encoding may be specified as a hint for such columns. It is not guaranteed that the preference will be applied; hence it is a hint as opposed to a setting. To specify an encoding hint, set the EncodingHint property on the column. Possible values are “Default”, “Value” and “Hash”. At time of writing, the property is not yet exposed in SSDT, so must be set using the JSON-based metadata, Tabular Model Scripting Language (TMSL), or Tabular Object Model (TOM). The following snippet of JSON-based metadata from the Model.bim file specifies value encoding for the Sales Amount column.

 
  {
    "name": "Sales Amount",
    "dataType": "decimal",
    "sourceColumn": "SalesAmount",
    "formatString": "\\$#,0.00;(\\$#,0.00);\\$#,0.00",
    "sourceProviderType": "Currency",
     "encodingHint": "Value" 
  }

Extended events not working in CTP 1.3

SSAS extended events do not work in CTP 1.3. We plan to fix them for the next CTP.

Download now!

To get started, download SQL Server vNext on Windows CTP1.3 from here. Be sure to keep an eye on this blog to stay up to date on Analysis Services.