编码/编码提示 [英] Encoding/Encoding Hints

问题描述

嗨！

我有SQL Server 2017，我有一个带有大约10个度量的事实表的表格模型这些都是数据库中的钱和表格模型中的小数。我已将所有这些设置为将编码提示设置为VALUE。然而，当使用Vertipaq
分析器检查模型时，其中三个最终被HASH编码，其中七个是VALUE编码的。 

我很好奇为什么？我希望所有十个都是VALUE编码或我错过了什么？我怎么能"影响"结果所以他们都被编码为VALUE（我认为我读到的地方更适合测量）比EncodingHint更多？ 

解决方案

< blockquote>

Hi StefanSy，

编码提示，这是一种用于优化处理（数据刷新）或大型内存表格模型的高级功能。 

值编码可以提高通常在聚合中使用的数字列的查询性能。散列编码用于分组列（通常是维度表值）和外键。
字符串列始终是哈希编码的。

还使用

SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS来验证编码方法。

例如：

 SELECT COLUMN_ID，DATATYPE，COLUMN_ENCODING 
 FROM 

SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS
WHERE COLUMN_TYPE ='BASIC_DATA'
AND MEASURE_GROUP_NAME ='Posts_CCI'

参考：

使用编码提示改进Analysis Services表格2017处理

问候，

Pirlo Zhang 

Hi!

I have SQL Server 2017 and i have have a tabular model with a fact table with about 10 measures that all are money in the database and decimal in the tabular model. I have set all of them to have Encoding Hint to VALUE. When checking the model with Vertipaq Analyzer however, three of them ends up being HASH encoded and seven of them VALUE encoded. 

I am curious why? I would like all ten to be VALUE encoded or am I missing something? How can i "influence" the outcome so they all get to be VALUE encoded (i think i read somewhere that it is more preferable for measures) more than the EncodingHint? 

解决方案

Hi StefanSy,

The encoding hints, which is an advanced feature used to optimize processing (data refresh) or large in-memory tabular models. 

Value encoding can improve query performance for numeric columns typically used in aggregations. Hash encoding is for group-by columns (often dimension-table values) and foreign keys. String columns are always hash encoded.

Also using

SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS to verify the encoding method.

For example:

SELECT COLUMN_ID, DATATYPE, COLUMN_ENCODING
FROM

SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS WHERE COLUMN_TYPE = 'BASIC_DATA' AND MEASURE_GROUP_NAME = 'Posts_CCI'

Reference:

Improve Analysis Services Tabular 2017 Processing with Encoding Hints

Regards,

Pirlo Zhang 

这篇关于编码/编码提示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！