Forzar a Vertipaq a actualizar la codificación de tipos de datos

[Syndicate_Admin]

¡Hola, chicos!

Estoy trabajando en un PBIX con un gran conjunto de datos y tratando de mantenerme por debajo del límite comprimido de 1 GB. Mi tabla de hechos más grande tiene una cardinalidad de 28 millones, con una clave primaria entera.

El problema es que, no importa lo que intente, Vertipaq sigue codificando la columna de clave principal como HASH, en lugar de VALUE, lo que consume espacio. Lo he lanzado como un entero aguas arriba, en la capa de base de datos; He cambiado su tipo de datos en Power Query a un número entero; He eliminado y reconstruido la tabla en numerosas ocasiones.

No puedo encontrar ninguna documentación definitiva sobre los pasos necesarios para actualizar la codificación en una sola columna entera. Lo que he encontrado establece que Integer = VALUE codifica por defecto, pero ese no parece ser el caso aquí. ¿Qué me estoy perdiendo? ¿Hay un límite en la cardinalidad que anule esto? ¿Es realmente necesario eliminar la tabla y volver a construirla cada vez que hago un cambio?

Una vez más, la falta de documentación clara y completa me está haciendo quemar el exceso de horas-hombre tratando de resolver esto.

[Syndicate_Admin]

Dado que la prueba y el error parecen ser la única manera de entender realmente cómo funciona PBI bajo el capó, hoy estoy pasando más tiempo jugando.

Teoría: La codificación depende de la cardinalidad absoluta.

Experimento: Cargue las mismas columnas de la misma tabla, incluido mi PK entero, pero con un número diferente de filas.

Resultados:

1. Cargando solo las primeras 100.000 filas: codificación VALUE
2. Cargando solo las primeras 5.000.000 de filas: codificación VALUE
3. Cargando solo las primeras 15.000.000 de filas: codificación VALUE
4. Cargando solo las primeras 20.000.000 de filas: codificación VALUE
5. Cargando solo las primeras 28.000.000 de filas: codificación VALUE
6. Carga de las 28.032.233 filas: codificación HASH

Sin embargo, basándome en lo que @jdbuchanan71 dicho upthread, noté que el 1% superior de mis filas, las últimas 28,000 más o menos, tienen PK enteros que son significativamente más grandes que la mayoría de la tabla. En otras palabras, hay una gran brecha en mi estructura pk.

Así que creé un PK sustituto, usando ROW_NUMBER(), que garantiza que los sustitutos son a) mucho más pequeños en valor, y b) absolutamente secuenciales y consecutivos. Volviendo a cargar las 28.032.233 filas e incluyendo la nueva columna PK sustituta junto con la PK original de las tablas de la base de datos, ¡tenemos un ganador! En esta captura de pantalla, la columna "ResultID" es mi PK original de la base de datos, y la columna "ResultPK" es mi nuevo PK sustituto secuencial. ¡Eso es un buen ahorro de tamaño!

También he aprendido de este ejercicio que el motor Vertipaq volverá a evaluar la codificación con cada cambio de consulta de origen y recarga. Es bueno saberlo.

[Syndicate_Admin]

¡Eso es un gran ahorro de espacio! Me alegro de que hayas podido hacer que funcione.

[Syndicate_Admin · Accepted solution]

Después del primer escaneo, si ve un valor atípico que puede desencadenar una recodificación.

"Una vez tomada la decisión, SSAS comienza a comprimir la columna utilizando el algoritmo elegido. Ocasionalmente, los valores que no estaban en el muestreo original pueden hacer que el proceso requiera una reevaluación. Por ejemplo, SAS podría leer unos pocos millones de filas en las que los valores están en el rango de 100-200, lo que hace que la codificación de valores sea la mejor opción. Sin embargo, después de esos millones de filas, un valor atípico podría aparecer repentinamente, por ejemplo, un gran número como 60,000,000 ~ desencadenando una recodificación de la columna ".
Esto es de "Modelado tabular en Microsoft SQL Server Analysis Services" 2ª edición. p 356

En base a eso, creo que quieres que sean lo más cercanos a la secuencial que puedan ser. Incluso hemos ido tan lejos como para reemplazar una columna clave que se Client_Claim_Seq_ID como "ABC000012357007" con una identidad entera en la tabla maestra donde se insertan los datos, por lo que ayudaría a codificar VALUE ya que la identidad en la tabla aumenta en 1 con cada inserción de registro.

[Syndicate_Admin]

¡Gracias @jdbuchanan71 ! Pero, ya lo intenté. Había visto la mención de clasificarlos en alguna documentación de Microsoft en alguna parte, pero eso tampoco obligó al motor a cambiarlo a VALUE. ¿Necesitan ser consecutivas, sin espacios intermedios? ¿Es necesario espaciar los valores uniformemente (brecha consistente entre ellos)? ¿O es suficiente que simplemente se clasifiquen en orden ascendente? Una vez más, la documentación no indica y el comportamiento no coincide con lo que sí indica la documentación.

Todavía me pregunto exactamente qué se necesita para desencadenar una reevaluación por parte del motor. Un documento dice que el motor evaluará en función del "primer escaneo" de la tabla. ¿Significa eso que utiliza esos valores a partir de entonces, independientemente de los cambios que realice en la capa ascendente? ¿O "primer escaneo" significa "primer escaneo después de que se realiza cada cambio" (lo que no tiene mucho sentido para mí)?

Analizaré el "Disponible en MDX"; No he visto mención de eso antes, ¡así que gracias!

[Syndicate_Admin]

.@ctmullins

Puede intentar ordenar la columna de clave principal en el origen ascendente. Vertipaq toma la decisión de codificación basada en una muestra de las primeras filas (no estoy seguro del tamaño de la muestra), pero si ve una serie como 1, 2, 3, 1297956, 4, 5, elegirá HASH. Ordenarlo debería evitar que se encuentre en ese escenario. También puede establecer una sugerencia de codificación en una columna utilizando el Editor tabular, lo que puede ayudar. También puede desactivar 'Disponible en MDX', lo que le ahorrará el tamaño de la herencia para la columna clave.