Esta es una de las buenas prácticas que a mi juicio muchas veces se omiten en el diseño de un data warehouse. Las llaves surrogadas o sustitutas son una llave generada artificialmente y que viene a sustituir el campo llave de la dimensión.
Para ponerlo en bits y bytes, lo que hacemos es diseñamos la tabla de tal forma que la llave primara sea un campo con un tipo de dato entero y que además tenga la propiedad “autoincrement” ( o identity, o lo que sea, todo depende de la base de datos utilizada). En la tabla existe un campo que servirá para almacenar la llave original. Al llenar la tabla se generarán valores numéricos que usaremos en vez del campo clave original.
Las ventajas son muchas. Para mí la principal es mejorar el tiempo de respuesta de la base de datos ya que buscar entre números enteros es lo que menos trabajo le cuesta a la base de datos; pero también hay otras, por ejemplo, si estamos juntando en un data warehouse información de sistemas transaccionales diferentes y cada uno de ellos tienen llaves que no se parecen en nada la una de la otro, las llaves surrogadas o sustitutas nos permitirán tener una llave homogénea para ambos sistemas.
Nos permite también resolver cosas como por ejemplo mantener la historia en el tiempo y a la vez separarla. Por ejemplo si queremos que un vendedor que ahora pertenece a otra región, su historia se conserve en el data warehouse en la región original, lo que hacemos es crear un nuevo registro en la tabla de vendedores con una nueva llave sustituta para ese vendedor y usamos esa nueva llave para almacenar las ventas de ese cliente a partir de la fecha que el movimiento ocurre. De esta forma en el data warehouse tendremos las 2 cosas.
| IdVendedor | CveVendedor | Vendedor | Región |
| 2345 | A00X456 | Miguel Gutierrez | Norte |
| … | … | … | |
| 5467 | A00X456 | Miguel Gutierrez | Centro |
Si queremos la historia del vendedor podemos hacer la consulta por el # de Vendedor lo cual abarcará a las 2 llaves (la nueva y la antigua).
select sum(ventas)
from HechosVentas A
join DimVendedores B on a.idVendedor = b.IdVendedor
where b.CveVendedor = ‘A00X456′
Si queremos las ventas de cada región:
select region, sum(ventas)
from HechosVentas A
join DimVendedores B on a.idVendedor = b.IdVendedor
group by region
El modelo por sí solo lo resolverá.
Por cierto, en el modelo arriba mostrado (que no tiene nada que ver con el ejemplo de regiones y vendedores que les daba) pueden ver que yo por ejemplo a todas las llaves usrrogadas las identifico por el sufijo “ID” y para los campos llaves originales reservo todos los campos con el sufijo “cve”.
¿implica más trabajo? Si, así es. Muchas veces por falta de tiempo yo en mi caso las omito. También en cierto que hay diferentes maneras de generarlas por ejemplo con algún ETL. Los beneficios son muchos así que cada vez que diseñe un modelo, ponga en la balanza los beneficios que le pueda traer el incluir dichas llaves.
Por cierto, si vá a utilizar el “count distinct” estas llaves sustitutas les vienen como anillo al dedo.
Hola Adrian, ya habia leido acerca de este tipo de diseño, personalmente no la he puesto en práctica. Entiendo que al generar la tabla de dimensión tú generas el ID único consecutivo.
Teniendo diferentes fuentes de datos, la tabla de hechos se carga con la llave original, ¿la idea es realizar un update a la fact table para cambiar las llaves originales a estas llaves basándose en la tabla de dimensión?, si tienes una dimensión en la cual borras completamente la tabla y la vuelves a cargar con más valores, ¿tendrías que actualizar toda la fact table para que coincida con la nueva tabla de dimensión?
Saludos.
Hola Jordi,
La tabla de hechos se carga con la llave sustituta. Lo mas sano es no hacerle un update a la tabla de hechos si no que antes de cargarla en la información se sustituye la llave original por la surrogada.
Las dimensiones deben de conservar en el tiempo el valor de las llaves por que si se regeneran tendrías que reprocesar todo de nuevo. Lo mas sano es que no las borres si no que solo agregues o modifiques lo nuevo.
Saludos
Hola,
Por lo general cargo ambas llaves en la ft a través de una homologación o conversión. A veces se consulta por los códigos operacionales de los registros mientras por abajo puedes mantener la historia, si usaras el código operacional en las dimensiones verías la historia desde el punto de vista presente de los datos y no como fue en ese instante!!.
Una buena práctica es conservar los nombres de los campos de las tablas operacionales, y los nombres de las tablas agregarle prefijos (o sufijos en su defecto) para identificar de donde vienen, una metadata desde los nombres.
Saludos,
Diego
Hola Adrian, yo tengo un problemilla con un cliente, tiene los usuarios catalogados como:
- externos y empleados
- no catalogados
- red comercial
Y quiere crear varias dimensiones, una con el tipo de usuarios, otra para la red comercial y otra para los externos y empleados, ya que tienen una estructura jerarquica diferente, los no catalogados no tiene estructura.
El problema que tengo es que no se si será mejor por un lado meter a todos los usuarios en una sola tabla, incluidos los no catalogados, o separarlos en tablas. Los empleados y externos tienen la misma estructura o niveles en una dimensión, y es completamente distinta a los niveles de los de la red comercial, pero el último nivel, que sería usuario_id, es común en ambas tablas y en la Fact.
Yo lo que he hecho es lo siguiente: por una lado tengo una tabla para externos y empleados, y por otro una para los de la red comercial, por otro lado meto en la fact el identificador de usuario como varchar(7) que es como me llega de la BD, y olvidarme de usar secuencias, ya que no podría asignar una secuencia de usuarios “no catalogados”, porque no los registro en tablas, simplemente muestro los huérfanos en la dimensión que me interesa (tipo de usuario) y les pongo un label de “no catalogados”.
Por otro lado tengo que contolar que un usuario puede pasar de ser externo a empleado o a la red comercial, asíque, viendolo así, no se si me intesaría tener a todos en una tabla, o crearme una tabla con los usuarios No catalogados y usar una secuencia…
¿Que opinas?
Espero haber sido claro,
Un saludo,
Sergio
Hola Diego,
Es una muy buena práctica. Por omisión mía, no había percibido las ventajas que tiene dejar los nombres originales y tienes toda la razón. Con dichos nombres originales la vida sería mucho más fácil.
Saludos
mmm, yo preguntaría ¿y esas dimensiones como las usas?
Me refiero a que si las requieres para el mismo cubo, para la misma metrica o son para cubos diferentes, ¿tienen la misma funcionalidad independientemente d e que son externos o internos?. Es decir si tienes un cubo para los internos o los externos. En base a esa respuesta decidiría yo si los pongo en la misma tabla o en diferente.
Saludos
Hola, supongamos el siguiente escenario: tengo una dimensión cliente, la cual la obtengo de 3 fuentes y genero una tabla con los IDs originales y las descripciones y les asigno la llave sustituta (del 1 al 1,000 por decir un número). Para mi, este es un paso que se hará en mi DWH. Si se supone que la mejor práctica es que tengo que extraer la FT con las nuevas claves, ¿cómo le hago para decirle al proceso de carga de la FT que el cliente con clave original ‘C000545′ tiene el ID sustituto ’50′?, esto a sabiendas de que no puedo modificar nada en la BD fuente y los nuevos códigos están generados pero en mi DWH. ¿Que pasaría si por alguna razón genero otra vez la tabla de clientes y al mismo cliente ya no le toca la misma llabe sustituta?
Saludos!
Hola Adrián, es para extraer información de un único cubo y para hacer informes, las métricas son similares, número de usuarios distintos que acceden… Es un cubo con información de accesos a un portal… Entiendo por tu respuesta que tu recomendación es que lo meta todo en una tabla, pero tendría que crear campos en blanco para cada usuario en función del tipo de usuario que fuese, por ejemplo
Externo y empleados: Divisón–>Unidad–>Entidad–>usuario
Red Comercial: Centro territorial–> Subcentral–>oficina–>usuario
Si creo una sola tabla con los campos y las id, tendría muchos campos en blanco por cada usuario, sin contar los no catalogados que estarían todos en blanco…
Un saludo, y gracias
Hola Sergio
mmm entiendo, dificil decisión ¿eeh?
y si creas las dimensiones?:
Division/ Centro Territorial
Unidad/subcentral
Entidad/Oficina
usuario
Es decir en una sola columna tienes las 2…
Hola Jordi,
puedes usar los Integration Services de Microsoft que te permiten hacer esa sustitucion o también puedes hacer un vil query que haga el join con la tabla para traerte el valor de la llave surrogada
saludos
http://www.dbmsmag.com/9805d05.html#fig1
Hola Adrián,
Creo que es buena idea, podría cargar todos los usuarios en una sola tabla, crear los campos:
Division/ Centro Territorial
Unidad/subcentral
Entidad/Oficina
usuario
Y tener un campo para catalogarlos: Tipo usuario (Red comercial, empleado, externo, no catalogado…)
Creo que será la mejor opción,
Muchas gracias,
Hola Adrián, oye, una pregunta. Desde hace tiempo, me quedó clara la idea de cómo implementar estas llaves sustitutas, de hecho lo estoy tratando de llevar a cabo en los nuevos desarrollos que tenemos. Estamos tratando de migrar la mayoria de los cubos a ROLAP y me encontré con el problema de cómo lidiar con las junk dimensions (# de factura, codigo de envio, etc). Hay alguna forma en la cual se pueda implementar en Artus este tipo de dimensiones, es decir, que se queden en la fact table y no tener que generar un catálogo con estos datos? (algo asi como un Agrupador sin Clave que se usa en los cubos propietarios de Artus). Saludos!!!
Hola Jordi,
si, si es posible. Dejalas en la tabla de hechos. Mapea el cubo con el wizard y posteriormente en el Administrador deberás añadir estas dimensiones al cubo.
tienes que crear uan dimension, cuando te pregunte la tabla dile que es la tabla de hechos y usa el mismo campo para la llave como para el descriptor.
cuando tenga esto listo añadela al cubo. No olvides deshabilitar el cubo y luego habilitarlo para propagar los cambios.
saludos
Adrian
Hola esto de las llaves surrogadas en verdad que es una muy buena practica pero la verdad es que soy nueva en sql server tengo la version 2008 pero como puedo poner una SK si tengo una dimension de cliente que tiene como nivel el tipo de cliente y se requiere hacer analisis no solo por cliente sino por tipo entonces necesito dos SK como puedo generar la de tipo siendo que no puede ser autoincremental ya que estan en la misma tabla? puedo hacerlo con ssis?