not - validar campo null sql server

Manejo de nulos en Datawarehouse

Question

Me gustaría preguntar su opinión sobre cuál es la mejor práctica para manejar valores de datos nulos o vacíos cuando se trata de almacenamiento de datos y SSIS / SSAS.

Tengo varias tablas de hechos y dimensiones que contienen valores nulos en diferentes filas.

Detalles específicos:

1) ¿Cuál es la mejor manera de manejar valores nulos de fecha / hora? ¿Debo hacer una fila "predeterminada" en las dimensiones de fecha y hora y señalar SSIS a la fila predeterminada cuando se encuentra un nulo?

2) ¿Cuál es la mejor manera de manejar nulos / valores vacíos dentro de los datos de dimensión? Ejemplo: Tengo algunas filas en las dimensiones de "Cuentas" que tienen valores vacíos (no NULOS) en la columna Nombre de la cuenta. ¿Debo convertir estos valores vacíos o nulos dentro de la columna a un valor predeterminado específico?

3) Similar al punto 1 anterior: ¿qué debo hacer si termino con una fila de Facttable que no tiene registro en una de las columnas de dimensión? ¿Necesito registros de dimensiones predeterminados para cada dimensión en caso de que esto ocurra?

4) ¿ Alguna sugerencia o sugerencia sobre cómo manejar estas operaciones en los servicios de integración de servidor Sql (SSIS)? Serían útiles las mejores configuraciones de flujo de datos o los mejores objetos de transformación para usar.

Gracias :-)

Answer 1

1. Ya sea NULL o una identificación reservada desde su dimensión de fecha con el significado apropiado. Recuerde que NULL realmente puede tener muchos significados diferentes, podría ser desconocido, inaplicable, inválido, etc.

2. Preferiría cadena vacía (y no NULLable), pero en el proyecto en el que estoy trabajando ahora convierte cadena vacía a NULL y los permite en la base de datos. Un problema potencial que debe discutirse es que una inicial intermedia en blanco (sin segundo nombre, por lo que la inicial del segundo nombre se sabe que está vacía) es diferente de una semántica inicial desconocida o una semántica similar. Por dinero, nuestro modelo permite valores NULL. Tengo un gran problema con esto en los hechos, ya que normalmente deberían ser 0, siempre se usan como 0 y siempre deben estar envueltos con ISNULL (). Pero debido a la política de ETL de convertir cadenas vacías a NULL, se establecieron en NULL, pero esto fue solo un artefacto del formato de archivo de transporte de ancho fijo que tenía espacios en lugar de 0 de algunos sistemas de origen.

3. Nuestras tablas de hechos usualmente tienen un PK basado en todas las dimensiones, por lo que no estaría permitido; estaría vinculado a una dimensión ficticia o desconocida

4. En SSIS, hice un componente de recorte que recorta espacios desde los extremos de todas las cadenas. Normalmente teníamos que hacer una gran cantidad de validación de fecha y conversión en SSIS, lo que hubiera sido mejor en un componente.

Answer 2

Como la respuesta anterior indica que puede haber muchos significados diferentes adjuntos a valores nulos para una dimensión, desconocida, no aplicable, desconocida, etc. Si es útil poder distinguirlos en la aplicación, agregar entradas de dimensión "pseudo" puede ayudar.

En cualquier caso, evitaría tener claves externas de hechos nulos o campos de dimensión, tener incluso un solo valor de dimensión "desconocido" ayudará a los usuarios a definir consultas que incluyan una agrupación global donde la calidad de los datos no sea del 100% (y nunca lo es).

Un truco muy simple que he estado usando para esto y que aún no me ha mordido es definir mis dimensiones claves sustitutas utilizando int IDENTITY (1,1) en T-sql (comenzar en 1 e incrementar en 1 por fila). Las pseudoclases ("No disponible", "Sin asignar", "No aplicable") se definen como entradas negativas y se rellenan con un procedimiento almacenado ejecutado al comienzo del proceso ETL.

Por ejemplo, una tabla creada como

    CREATE TABLE [dbo].[Location]
    (
        [LocationSK] [int] IDENTITY(1,1) NOT NULL,
        [Name] [varchar](50) NOT NULL,
        [Abbreviation] [varchar](4) NOT NULL,
        [LocationBK] [int] NOT NULL,
        [EffectiveFromDate] [datetime] NOT NULL,
        [EffectiveToDate] [datetime] NULL,
        [Type1Checksum] [int] NOT NULL,
        [Type2Checksum] [int] NOT NULL,
    ) ON [PRIMARY]

Y un procedimiento almacenado que puebla la tabla con

Insert Into dbo.Location (LocationSK, Name, Abbreviation, LocationBK, 
                      EffectiveFromDate,  Type1Checksum, Type2Checksum)
            Values (-1, ''Unknown location'', ''Unk'', -1, ''1900-01-01'', 0,0)

He establecido una regla para tener al menos una pseudo fila por dimensión que se usa en los casos en que la búsqueda de dimensión falla y para generar informes de excepción para rastrear el número de hechos que están asignados a dichas filas.

Answer 3

Gracias por el aporte,

Dos cosas que he hecho en mi último proyecto son:

1) Usé la sugerencia de Steve sobre claves de ID negativas para valores de dimensión desconocidos / especiales. Esto funcionó perfectamente y no surgieron problemas durante el proceso de creación de cubos de SSAS.

2) Se crearon transformaciones para verificar si un valor es nulo, y si es así, se convierte a -1 (registro desconocido en dimensión) O si es un valor de medida, se convierte a 0. Las expresiones se muestran a continuación como ejemplos (las utilicé en Transformaciones de columna derivadas):

ISNULL(netWeight) ? 0 : netWeight // This is an example of a Measure column
ISNULL(completeddateid) ? -1 : completeddateid // This is an example of a dimension key column

Espero que esto ayude a alguien más en el futuro ;-)

Answer 4

Otra solución que puedo sugerir es que durante el ETL-step se define una tabla de transferencia en la que los registros importados se almacenan temporalmente DESPUÉS de todas las transformaciones necesarias. Agregaría algunos atributos adicionales a esa tabla de transferencia permitiendo a alguien; al lado de los valores-atributos originales que pueden ser NULL o algún otro valor no deseado; para insertar un valor "codificado" que identifica el problema, por un lado, y el nombre del atributo en el que se produjo el valor erróneo.

Una vez hecho eso, todavía podría decidir cómo usar los datos desnormalizados y transferidos en un paso posterior ... posiblemente filtrando los valores erróneos o mencionándolos en una dimensión de error separada para incluirlos en los informes que indiquen qué valores fueron desviados y cómo pueden / Posiblemente podría afectar los valores agregados.

p.ej

error-code attribute= -1 = NULL date -2 = NULL numerical value -3 = NULL PK -4 = NULL text value

y el otro atributo = IdOrder , BirthDate , OrderAmount , etc.

Por supuesto que tiene muchos más problemas si los registros pueden tener MÁS de 1 valor erróneo (NULO), pero en ese caso uno podría expandir el número de atributos de "rastreo" o "regresar a la fuente" y averiguar dónde y por qué problema ocurrido (junto con desarrollo dep.)

Es un paso algo complicado, sin embargo, en aras de la integridad y la corrección, supongo que es inevitable y necesario porque, de lo contrario, uno podría enfrentarse a información mal agregada.

Quizás esto también ayudará a alguien;)