Llaves surrogadas o sustitutas.

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.

La Tabla de Tiempo en Oracle

Finalmente tuve la oportunidad de hacer el equivalente en Oracle de la tabla de tiempo. Asume que hay autoconversion de caracteres a numeros, si no es así en la base de datos que están usando habría que añadirle el TO_NUMBER antes de cada TO_CHAR para las columnas numéricas.

/*
Tabla de Tiempo en Oracle
Adrián Ceballos
HaciendoCubos.com
*/
CREATE TABLE Tiempo (
       TiempoID         int NOT NULL,
       Fecha            date NOT NULL,
       Año              int NOT NULL,
       MesID            int NOT NULL,
       Mes              varchar2(10) NOT NULL,
       Cuarto           int NOT NULL,
       Semana           int NOT NULL,
       NumDiaSemana     int NOT NULL,
       DiaSemana        varchar2(15) NOT NULL
);
ALTER TABLE Tiempo
       ADD PRIMARY KEY (TiempoID);

Y el llenado de la tabla. Hay que jugar con el TO_CHAR para cambiar el día de la semana. En Bochgoch pueden encontrar una excelente referencia de los formatos usados en el TO_CHAR.

declare
FechaFin date;
Fecha date;
begin

Fecha    := to_date(‘2000-01-01′,’yyyy-MM-dd’);
FechaFin := to_date(‘2010-12-31′,’yyyy-MM-dd’);

WHILE Fecha < FechaFin LOOP      DBMS_OUTPUT.PUT_LINE('Mes: ' || TO_CHAR(Fecha,'YYYYMMDD') );      INSERT INTO Tiempo      SELECT      TO_CHAR(Fecha,'YYYYMMDD') AS TIEMPO_ID,      Fecha,      TO_CHAR(Fecha,'YYYY') AS YEAR,      TO_CHAR(Fecha,'YYYYMM') AS MES_ID,      TO_CHAR(Fecha,'MON-YY') AS MES,      TO_CHAR(Fecha,'YYYYQ') AS TRIMESTRE,      TO_CHAR(Fecha, 'YYYYIW') AS SEMANA,      TO_CHAR(Fecha,'D'),      TO_CHAR(Fecha,'DY') AS DIA_SEMANA      FROM DUAL;      SELECT Fecha+1 into Fecha from dual; end loop; end;   [/sourcecode]

Technorati tags: Oracle, data warehouse

¿Estrella o Copo de Nieve?

Cada vez que comenzamos un proyecto nuevo siempre es lo mismo ¿Estrellas o copos de nieve?¿que es mejor? Es una decisión dificil de tomar.

A mi me gusta trabajar más con estrellas. El tiempo de respuesta del servidor es más rápido debido a que se involucran menos tablas en los querys. Sin embargo tienen la desventaja que hay que programar más. Los programas que se encargarán de estar manteniendo los catálogos (o puntas de la estrella) al día son más complejos.

La ventaja que yó le veo a los copos de nieve es que la información se puede pasar casi siempre tal cual viene. O sea que en un 2×3 podemos tener lista la parte del modelo referente a los catálogos.

Respecto al trabajo que se requiere para llenar la tabla de hechos no hay diferencia entre un modelo y el otro.

No son verdades absolutas. En un modelo estrella, cuando el catálogo que representa una dimensión que tiene demasiados registros, involucrar esta tabla no es tán eficiente. No se requiere ser un genio para darse cuenta que si tengo una estrella donde se usa la dimensión cliente con 500,000 registros y si el sexo de nuestro cliente es una columna más de la tabla de clientes, entonces hacer un query que nos dé las compras por el sexo del cliente (¿quién compra más, las mujeres o los hombres?) hará que la base de datos recorra 500,000 registros del catálogo para sacar los valores diferentes de sexo. Esto hará que el servidor trabaje más; aquí el modelo copo de nieve sería más eficiente.

Si usted tiene Redbrick, entonces no se preocupe, puede hacer todo como una estrella perfecta y crearle agregados a los catálogos. Sí, Redbrick recorre las tablas de la estrella y coloca en una tabla de agregados los valores diferentes de las dimensiones (sexo) dentro de la grán dimension (clientes). Si no estoy mal, lo mismo debe suceder con Oracle.

Lo siento, no he tenido la oportunidad de verificar si otras marcas de base de datos tienen esta capacidad.

Si usted tiene el tiempo suficiente para hacer todo como debe de ser digamos que la regla sería: 

Si los valores de las dimensiones son pocos (los catálogos) use el modelo estrella. Para dimensiones gigantescas use el modelo copo de nieve.

¿qué es gigantesco y qué no lo es? La experiencia y el hardware de su servidor lo dirán.

Como vé, en muchos proyectos no se pueden tener estrellas ó copos de nieve. A veces lo que se requieren son híbridos, mitad y mitad.

La Dimensión Minutos

Muchas veces se requiere analizar la información en base al tiempo. Por ejemplo, desglosar las ventas por hora, por minuto o si ocurrieron en la mañana, tarde o noche. En otras palabras, requerimos una dimensión de tiempo, pero nó la de tiempo normal sinó una en base al minuto del día en el que transcurrieron las cosas.

CREATE TABLE Minutos (
MinutoID int NOT NULL,
Minuto varchar(50) null,
HoraID int NOT NULL,
Hora varchar(50) NULL,
ParteDelDiaID int NOT NULL,
ParteDelDia varchar(50) NULL,
PRIMARY KEY (MinutoID)
)

El anterior código es el create table para nuestra dimensión Minutos. La llave es el MinutoID, para esta llave yo siempre uso el minuto del día a partir de las cero horas. Así por ejemplo para las 5:30 uso 5*60+30=330 como llave.

Un select sobre la tabla Minutos nos daría el anterior resultado. Minuto y Hora son varchar para dejar ahí la descripción de los minutos y hora. Como por ejemplo: «12:58» y «12:00»; «00:34» ó «01:00».

Esto se requiere para el order by, sobre todo cuando se quiere una gráfica hora por hora.

El código para llenar la tabla minutos es el siguiente:

Declare @Fecha datetime

set @Fecha = ‘2007-01-01 00:00:00’
while @fecha < = '2007-01-01 23:59:00' begin insert into minutos select datepart( hh, @fecha)*60+datepart(mi, @fecha) as MinutoID, left(convert( varchar, @fecha, 108),5) as Minutos, datepart( hh, @fecha) as HoraID, left(convert( varchar, @fecha, 108),3)+'00' as Hora, case when datepart( hh, @fecha) between 0 and 12 then 1 when datepart( hh, @fecha) between 13 and 19 then 2 else 3 end as ParteDelDiaID, case when datepart( hh, @fecha) between 0 and 6 then 'Madrugada' when datepart( hh, @fecha) between 6 and 11 then 'MaÃ�±ana' when datepart( hh, @fecha) between 12 and 19 then 'Tarde' else 'Noche' end as ParteDelDia set @Fecha=dateadd( mi, 1, @fecha) end [/sourcecode] La tabla únicamente guarda los 1440 minutos del día, nó mas. Con esta tabla como parte de la estrella ahora sería posible hacer querys como estos: [sourcecode language='sql'] select hora, sum( NumeroTransacciones) from dbo.FactProcessesPerspective a join Tiempo b on a.tiempoid = b.tiempoid join Minutos c on a.MinutoID = c.MinutoID where b.Mes = '2007-01' group by hora[/sourcecode]o en vez de hora usar el campo ParteDelDia y así tendría cual fué el número de transacciones en la mañana, tarde o noche. Por cierto, esta tabla es algo indispensable para un cubo de delicuencia.

Technorati tags: data warehousing, diseño

La tabla de hechos

Cuando estamos construyendo nuestro Data Warehouse tenemos que diseñar la tabla central que es la que guardará los hechos. A diferencia de un sistema transaccional donde en una tabla tenemos el total de la factura, en otra el total de la orden de compra, en otro el tipo de cambio (y así sucesivamente) en un Data Warehouse (DWH) los hechos (las cosas que sucedieron) están en una única tabla.

Para aclarar la palabra hechos: ¿qué sucedió en mi compañia? Pues vendí, compré, vendí en unidades, tuve un # de empleados. Entonces en la tabla de hechos se guardan las ventas, las ventas en unidades, las compras, etc..

Todo lo que sean indicadores.

Tampoco se trata de hacer una tabla gigantesca que tenga lo de recursos humanos + lo de ventas + lo de produccion + lo de telemarketing + ¡todo!

No hay que exagerar…

Normalmente las cosas que están en la tabla de hechos tienen afinidad entre sí. De esta forma tendremos una tabla de Hechos de Ventas, una de inventario, una de Recursos Humanos, una de produccion, etc.

No todas las herramientas de explotación de Data Warehouse permiten hacer reportes o informes tomando información de 2 o más tablas de hechos; es por esto que a veces en un DWH se suelen encontrar cosas extrañas como las ventas y el # de empleados en la misma tabla de hechos ( para hacer el calculo de Ventas/#Personas). El problema de esto es que el DWH vuelve caótico: cada vez que necesite hacer un calculo entre 2 tablas de hechos hago un nueva table de hechos que junte las 2 y entonces me lleno de tablas de hechos o cubos ( si trabaja con cubos Rolap a esto se le llama cubitis).

Mejor encuentre una herramienta que permita tomar información de multiples tablas de hechos. Artus soporta crear indicadores calculados con columnas de 2 o más tablas de hechos. No solo eso, si no que también permite mezclar en el tablero de control información de diferentes proveedores de cubos: SAP, Analysis Services, Rolap ( Oracle, Sybase, DB2, Redbrick ).

Technorati tags: Data Warehouse, cubos

Llenando la tabla de tiempo

Recurro muy seguido a este script de SQL para llenar mi tabla de tiempo. Casi siempre lo hago y lo vuelvo a hacer una vez al mes. Todavía no comprendo como no demonios lo guardo en alguna parte…bueno, esperemos que al publicarlo lo tenga en un solo lado.

/*
Llena la tabla de tiempo
Haciendo cubos: Adrian Ceballos

Solo para asegurarnos que esto corre
en cualquier servidor independientemente
del idioma ademas de especificar en que
idioma saldran los nombres de los dias de la semana
*/
SET LANGUAGE spanish

/*Ponemos que el primer dia de la semana
1 es Lunes. Para el Domingo usar 7*/

SET DATEFIRST 1

declare @fi datetime, @ff datetime

set @fi = '2004-01-01'
Set @ff = '2004-31-12'

while @fi < = @ff
begin
insert into Tiempo (TimeID, Fecha, Anio, MesID, Mes,
Cuarto, Semana, NumDiaSemana, DiaSemana)
select year(@fi)*10000+month(@fi)*100+day(@fi) as TimeId,
@fi as Fecha,
year(@fi) as Anio,
year(@fi)*100+month(@fi) as MesID,
case when Month(@fi)  < 10
then datename(year,@fi)+'-0'+convert(varchar,month(@fi))
else datename(year,@fi)+'-'+convert(varchar,month(@fi))
end as Mes,
year(@fi)*10+datepart(q,@fi) as Cuarto,
datename(year,@fi)+datename(ww,@fi) as Semana,
datepart( dw, @fi) as NumDiaSemana,
datename( dw, @fi) as DiaSemana
set @fi = @fi + 1
end

Solo hay que darle copy-paste y ¡voilá!…No olviden sustituir los valores fechas para el rango que quieren se genere en la tabla de tiempo. El detalle de las columnas aquí.

El concepto es el mismo para cualquier marca de base de datos pero habrá que traducir el script. El script para crear y llenar la tabla de tiempo puede bajar aquí.

Technorati tags: Data Warehouse, Cubos

Use nombres amigables

La mayoría de las herramientas de cubos soportan 2 nombres: el físico y el lógico. El lógico es el nombre que normalmente el usuario vé en la aplicación.

Pongase como obligación  el usar nombras amigables en los campos lógicos. Si usted utiliza nombres como si fueran los campos de una tabla de SQL el usuario no lo comprenderá y esto puede hacer que si aplicación fracase.

A ver diganme ¿con que cara le digo a un director, CEO o lo que sea que haga drill-down por desc_prod?

—Vamos a desglosar las ventas por desc_tda…
—¿qué es eso? —.
—las tiendas. Y ahora vamos a hacer drill-down por desc_prod
—mmmm..
—…así le llamamos en sistemas a la dimensión Producto… Y finalmente está el dato que estamos buscando…el Total
—¿total de qué?¿de ventas, de unidades, de merma, de cumplimiento?
—de ventas…

Y luego nos quejamos…

Si queremos que nuestra aplicación tenga éxito, que el usuario se enamore de ella y la use;  entonces use nombres amigables.

Technorati tags: tableros de control, cuadros de mando, artus

Exportando a Excel

En columnas: Los 12 meses del año, el acumulado (YTD), el acumulado año pasado.
En renglones: Los 14,348 clientes que la compañia tiene.

Esto es un problema recurrente que seguido encuentro. Los usuarios finales de la información tienen tableros de control, cuadros de mando, o reportes o informes que tienen tablas con:

13 columnas * 14,348 renglones = 200,872 celdas

¿Qués es lo que se vá a controlar aquí?¿qué es lo que se puede monitorear aquí? En menos de 10 segundos…¿quién es el mejor cliente?

Es imposible trabajar con tanta información. Si uno escarba tantito la primera respuesta que encuentra es:

así me lo pidió el usuario…

¿qué es lo que está pasando? Que el usuario no tiene ni la menor idea de como obtener el resultado en la herramienta que usa, así que se le hace más fácil pedir un megareporte que el EXPORTARÁ a EXCEL y el Excel como ya lo sabe manejar, pues ordenará, hará calculos y obtendrá el resultado que quiere…con toda la perdida de tiempo que esto conlleva.

Si analizamos esto:

• El usuario no sabe como obtener el resultado. ¡Capacitalo! Hace unos meses mientras entrenaba a unos usuarios les mostré como obtener el pareto (80/20) con un clic. Cuando vieron como se hacía me dijeron: «¿sábes cuanto tiempo hemos perdido haciendo esto? Todos los días entramos, ejecutamos la consulta, esperamos a que la regrese, la exportamos a excel, la ordenamos, calculamos el acumulado, le sumamos,…., y nos dá el resultado; ¡nos toma un día!….SI TAN SOLO NOS HUBIERAN CAPACITADO ANTES»
• Alguién desconoce  que Excel directamente puede ejecutar Querys sobres bases de datos SQL o Cubos e importarlos directamente a Excel sin necesidad de un intermediario. Hasta te pone un botoncito «actualizar datos». Pero que necesidad…
• El servidor se alenta. Pues claro, de repente quieren la venta a nivel articulo, mes por mes de los 43,567 artículos que la empresa vende…me ha tocado unos casos…

Lo mejor es preguntarle al usuario: Si te doy esa información ¿qué es lo que estás buscando en ella? En base a la respuesta le construimos una consulta que le dé la información en un clic, en poco tiempo y que además le permita monitorear esos 100 productos, clientes, 80/20, o la cobranza, o los ingresos, o la merma, etc.; y el usuario se los agradecerá por que puede dedicarle tiempo a lo que realmente le importa: Tomar decisiones.

Technorati tags: tableros de control, cuadros de mando, mejores prácticas

Tablas Particionadas

¿Cuando está haciendo una consulta en su Data Warehouse el tiempo de respuesta es lento?¿Si se para enfrente del servidor puede ver las lucecitas de los discos duros encendidas a todo lo que dá y el % del procesador se vá al 100% o a niveles altos?¿sus usuarios se quejan?

Estos son sintomas de un grave problema, el DWH ha llegado al límite de su capacidad de acuerdo a como usted ha diseñado el almacenamiento de la información. Si está ustede en este punto, ¡PARTICIONE LAS TABLAS !

El concepto es muy sencillo, en vez almacenar una sola tabla gigantesca lo que se hace es guardarla en pedacitos (particiones); en cada pedacito guardaríamos los datos de un mes, un año o una sucursal. Así, cuando lancemos una consulta que busque los datos de Enero del 2004; la base de datos solo hará la busqueda de la información en la partición que corresponde a ese mes.

Los pedacitos pueden guardarse por ejemplo en diferentes discos duros; de esta forma, puede usted por ejemplo guardar los datos más accesados de la tabla en discos duros rápidos y los datos históricos de años pasados que nadie utiliza en los discos más lentos.

Esto hace que las busquedas se eficienten brutalmente. Los accesos a disco no serán tantos y probablemente se pueda olvidar por un tiempo de comprar un servidor nuevo.

Esto muy importante y critico para las bases de datos y la mayoría lo soporta (les paso las ligas a las páginas donde dice como hacerlo): ORacle, DB2, Sybase, Redbrick (tengo los manuales)….

¿SQLServer de Microsoft? Esta característica viene solo en la versión 2005. Lo que viene en la ayuda de SQLServer 2000 como Vista Particionada es un vil truco sucio para emular la partición de tablas. Lo que no me acuerdo es que versión de SQLServer 2005 se requiere ¿la Enterprise super plus edition?

Esta es una de las características que más alto impacto pueden tener en el desempeño ( o si lo prefieren, performance) de un Data Warehouse pero que por una extraña razón nadie utiliza.

Para los cubos existe el mismo concepto y el mismo impacto. Ahí sí, los Analysis Services de Microsoft soportan cubos particionados de tiempo atrás.

Technorati tags: Data Warehouse, performance