Data Warehouse, Modelo, Conceptos e Implementación orientada a SQL Server (página 2)

Enviado por Erith Eduardo P�rez Gallardo

Partes: 1, 2

El Modelo de
Hechos Dimensionales (DFM)

El análisis de los datos en un tiempo finito, ha
traído consigo estudios sobre la mejor forma de almacenar
y representar estos datos para que puedan ser consultados de una
forma más rápida. El uso del Modelo
Multidimensional es una de las aproximaciones más
acertadas y seguidas por los especialistas en estos días.
Este se basa en el estudio de los eventos del negocio analizados
desde sus distintas dimensiones. Así:

Definición 1: Llamamos evento o
Hecho a una operación que se realiza en el negocio
en un tiempo determinado. Son objeto de análisis para la
toma de decisiones. Se Representan en una caja con su nombre y
las medidas que lo caracterizan. (Robert Wrembel & Christian
Concilia, 2007)

Ej: Figura 1: Representación gráfica de un
Hecho y sus dimensiones

Los Hechos están estrechamente
relacionados con el tiempo. Los eventos que son estáticos
no tiene objetivo de análisis para este modelo, aunque son
muy pocos los hechos que no ocurren con determinada periodicidad
en un negocio. Los hechos están caracterizados por medidas
numéricas como se muestra en el ejemplo de la figura 1: la
cantidad, el precio unitario, el descuento, etc, son las medidas
del Hecho (VENTA).

Nota: Fíjese que el producto que se vende,
su costo y la fecha de la venta no son características de
esta como lo podrían ser en cualquier diseño
relacional. En este caso, esos serían dimensiones de ese
Hecho, por las que, puede ser analizado más
adelante.

Definición 2: Una Medida es una
propiedad de un Hecho (casi siempre numérica), que es
usada para su análisis. (Robert Wrembel & Christian
Concilia, 2007)

Nota: Un hecho puede no poseer ninguna medida. En
ese caso se dice que el Hecho es vacío y solo se usa para
contar la aparición de este en el tiempo.

Definición3: Una Dimensión
es una característica de un hecho que permite su
análisis posterior, en el proceso de toma de decisiones.
(Robert Wrembel & Christian Concilia, 2007)

Nota: Un hecho debe estar relacionado al menos
con una dimensión: "El tiempo".

Es un interés del negocio tomar decisiones sobre
los hechos que ocurren en este, pero para esto se necesita su
análisis. Por ejemplo Las ventas en la semana antes del 14
de Febrero, puede ser un objeto de análisis para un
negocio comercial. Para esto se necesita tener el Hecho Ventas
analizado en la dimensión Tiempo. En este caso en los
Días:

7 <= d <= 14. Si se quisiera saber que productos
fueron los más vendidos en esos días entonces
tendríamos que adicionar una nueva dimensión de
análisis, Producto. Así adicionando dimensiones a
nuestro estudio se pudieran llegar a conclusiones sobre si el
siguiente año en esa época debería comprarse
más objetos de un producto o menos de otro. Elemento este
muy importante para la futura estrategia de la
empresa.

Definición 4: En una empresa pueden
existir varios hechos que sean analizados por dimensiones
iguales. En este caso se les llama a estas dimensiones:
Dimensiones Compartidas. (Robert Wrembel & Christian
Concilia, 2007). Un ejemplo de esto es El Hecho Ventas puede ser
analizado en las dimensiones Tiempo y Producto. Lo mismo ocurre
con el Hecho Compras.

Las dimensiones deben ser atómicas y las
relaciones entre estas crean jerarquías que permiten un
análisis jerárquico de los datos. Un ejemplo de
esto es el Tiempo, que es dividido en tres dimensiones.
Día, Mes y Año. Cada uno es una dimensión
distinta, pero relacionadas jerárquicamente en una
relación de 1 a muchos, que permite el análisis del
Hecho, por días, meses o años, o la
combinación de ellos. Esto da al traste con las
acostumbradas (OLTP) que manejan el Tiempo como una propiedad de
una entidad, y lo tratan como un todo. Por lo que, como podemos
inferir de aquí: en muchos casos hará falta
convertir las bases de datos de estos sistemas a la nueva
filosofía. SQL Server tiene facilidades para esto llamadas
DTS (Data Transformation Services) que permite leer datos desde
cualquier SGBDR que posea un driver ODBC o implemente la nueva
tecnología OLE DB de Microsoft.

Diagrama en
Estrella

Uno de los tipos de consultas más usadas en las
OLAP es la llamada Estrella. Su nombre lo adquiere debido a que
su implementación en un ambiente relacional (MOLAP
Multidimentional Online Analitical Processing) está dado
por varias tablas que almacenan las jerarquías
dimensionales y una tabla que contiene el hecho con una
relación 1:m con estas tablas de dimensiones. Veamos un
ejemplo gráfico:

Figura 2: Diagrama en estrella del Hecho, Ventas.
(Microsoft Data Warehouse Training Kit, 2000)

Como podemos ver en la figura las tablas de dimensiones
están ligadas a la tabla Hecho, por relaciones. La
integridad referencial es llevada a cabo por la creación
de llaves foráneas en la tabla Hecho, que a su ves forman
parte de la llave principal de la esta tabla. Es importante
destacar que las jerarquías completas son guardadas en una
sola tabla dimensión. Este es el formato no normalizado,
existe otro formato que intenta normalizar estas tablas
dimensión. Ejemplo (Time_Dim). Cada tabla dimensión
tiene su propia llave que es mantenida por el sistema Data
Warehouse. A estas llaves se les llama "Surrogate Key". Las
llaves Surrogate Jerárquicas, no son más que una
codificación de cada elemento de la jerarquía
almacenado en la tabla dimensión. Veamos la figura 3 de
cómo se logran estas llaves.

Figura 3: Formación de una llave Surrogate
Jerárquica (Robert Wrembel & Christian Concilia,
2007)

Vamos a ver ahora como sería una consulta sobre
este tipo de diagrama en estrella:

Figura 3: Plantilla de consulta para una consulta en
estrella (ad hoc star query) (Robert Wrembel & Christian
Concilia, 2007)

Nota: En la figura las D1, D2, .. , Dk significan
tablas de dimensión y los LP1, LP2, …,LPk son los
predicados usados para simplificar la consulta. El ejemplo
siguiente muestra mejor como sería esta consulta: (Robert
Wrembel & Christian Concilia, 2007)

En este tipo de procesamiento el mayor de los problemas
es el super join que se crea al procesar las tablas de
dimensiones con los datos de la tabla Hecho, para esto se han
hecho varios estudios sobre la mejor forma de hacer este tipo de
consultas de forma que sean lo más óptimas
posibles, una de las técnicas mejores probadas es la de
reescribir la consulta como lo muestra el siguiente ejemplo que
mostramos:

Ejemplo: Optimizar la consulta en el Data Warehouse
(Robert Wrembel & Christian Concilia, 2007)

Para cerrar con broche dorado este tema es necesario
hacer alusión a los llamados Cubos de datos: Estos no son
más que el conjunto formado por todas las tablas
Dimensión y la tabla Hecho que al final dan una vista en
forma de Cubo cuyas celdas están compuestas por las
medidas de la tabla Hecho. Esta es la base de las aplicaciones
OLAP. El cubo de datos es lo que hace que los reportes sean
obtenidos con un bajo tiempo de respuesta y que el
análisis de los datos pueda ser tan diverso, pues cada
cara del cubo se refiere a un análisis distinto de las
medidas almacenadas. Veamos el ejemplo gráfico del
cubo:

Figura 4: Cubo de datos (Microsoft Books Online,
2000)

Como podemos ver en el ejemplo la cantidad de
producción puede ser analizada por producto, teniendo en
cuenta la dimensión Producto, Por Tiempo, por
Localización de las Industrias o en su conjunto por todas
ellas a la vez o cualquier combinación de estas. Esto le
da al analista o al sistema experto una amplia gama de
posibilidades de las cuales puede tomar ventaja. En nuestro caso
de estudio de las ventas. El cubo de datos formado por la Tabla
Sales_Fac en conjunto con las restantes tablas de
Dimensión nos permite analizar las ventas por Empleado,
por Consumidor, por Tiempo, Etc.

Extracción y Transformación de los
Datos

SQL puede conectarse a cualquiera de los formatos
creados por Microsoft Office, archivos texto y a bases de datos
como FOXPRO y ACCESS muy usadas por múltiples sistemas de
escritorio que resuelven problemas importantes en muchas
empresas. Además puede conectarse a cualquier SGBD
(Sistema Gestor de Bases de Datos) que posea un driver ODBC
compatible con Microsoft o implemente la tecnología OLE
DB. El poder obtener datos de páginas de Excel con datos
resumidos o exportarlas a este, para que los usuarios más
avanzados puedan crear gráficos y otros. Es algo que
convierte a SQL en la herramienta perfecta para crear sistemas
Data Warehouse. Para realizar estas tareas Microsoft cuenta con
una rama dentro del árbol de administración del SQL
Enterprise Manager, que contiene un área de diseño
con los elementos que se necesitan para diseñar un paquete
de transformación de datos (DTS Package).

Para crear un paquete solo se hace clic derecho sobre el
área vacía de esa rama y se selecciona la
opción New Package, inmediatamente se abre una ventana con
un área de diseño que permite empezar a
diseñar el paquete en cuestión. Hay tres elementos
fundamentales a la hora de diseñar un paquete:

Las conexiones: que permiten conectarse a cualquier
fuente de datos como las que relacionamos
anteriormente.
Las tareas: que permiten transformar los datos de
cada conexión antes de que sean copiados a otra
conexión. Estas tareas pueden ejecutar Scripts de
ActiveX, pueden transferir otros archivos desde o hacia un
sitio FTP, pueden enviar un mensaje a un operador de SQL.
Además de poder filtrar datos, crear nuevas tablas en
la BD resultante, etc.
Los flujos de trabajo: que permiten definir hacia
donde irán los datos luego de que se le apliquen las
respectivas acciones que los transformarán a la nueva
forma deseada.

Así usando estos tres elementos fundamentales se
crea una especie de script gráfico que sigue una secuencia
lógica para dar como resultado la transformación de
los datos almacenados de un formato a otro como SQL Server desde
donde podrán ser analizados con el Data
Warehouse.

Aunque desde el Enterprise Manager de SQL Server se
puede ejecutar los paquetes que se crean haciendo clic derecho
sobre ellos y seleccionando Execute o en un Schedule. Existen
varias herramientas adicionales que pueden usarse desde la
ventana de comandos del sistema operativo. De ellas, la
más interesante para el uso de scripts es dtsrun. Para
obtener la ayuda completa de esta podemos ejecutar dtsrun /? En
una pc (Computadora Personal) con SQL Server
instalado.

Ejemplo: Uso de la herramienta dtsrun desde un script de
windows

@echo off

Copy \srvaplicsaplics*.mdb d:convertsql
/Y

if (%ERRORLEVEL% NEQ 0) echo "Error al copiar la base de
datos"

else dtsrun /S SASSQL /U sa /P Pepe2006 /N
loadaccessdb

Como podemos ver a dtsrun se le pasan como
parámetros el nombre del servidor el usuario con derechos
para ejecutar el paquete, el password de este y el nombre del
paquete a ejecutar. Existen otra serie de opciones que pueden ser
consultadas en la ayuda de la herramienta. El uso de esta
herramienta puede estar relacionado como vemos en el ejemplo con
la copia de bases de datos hacia lugares desde donde es
más seguro la obtención de sus datos.

A continuación mostraremos un ejemplo
práctico de cómo diseñar un paquete para
obtener el balance de comprobación desde un fichero TXT
exportado por el sistema contable SENTAI.

Ejemplo: Obtención de Balance de
SENTAI

Creación de un nuevo paquete desde el SQL
Enterprise Manager
Arrastrar hacia el área de diseño la
conexión hacia un archivo TXT
Configurar la conexión especificando el
archivo y su formato
Arrastrar hacia el área de diseño una
conexión al servidor SQL al que se va a importar el
estado de las cuentas.
Configura esta conexión especificando la base
de datos a usar
Hacer clic sobre la conexión al archivo texto
para seleccionarlo.
Hacer clic sobre la acción (trasform data
task) y seguido se hace clic sobre la conexión al Server
SQL del área de diseño.
Hacemos clic derecho sobre la línea que une a
ambos ahora y editamos las propiedades de la
transformación para asegurarnos de que están de
acuerdo a nuestros objetivos.
Presionamos el botón de salvar para guardar el
paquete.
Escriba el nombre del paquete y cierre la ventana de
diseño.

Ahora podrá ver el nombre del paquete y si desea
ejecutarlo solo debe hacer clic derecho sobre este y seleccionar
Execute. Así las cuentas y sus saldos serán
insertados en la nueva base de datos de contabilidad de la
empresa.

Ejemplo: Vista gráfica del paquete
creado

Técnicas de desarrollo del soporte de
Hardware

Antes de definir una estrategia en la cual se
desarrollará el hardware que soporta el Data Warehouse, es
necesario hacer un estudio del tiempo, que pueden estar detenidas
las aplicaciones que usan este. En nuestros días muchas
empresas necesitan que estos sistemas ejecuten 24 horas los 365
días del año, por lo que no hay mucho margen de
error. La desconexión de estas aplicaciones por solo una
hora puede hacer que la compañía pierda miles de
usuarios que nunca más visitarán el sitio Web
pensando que está fuera de servicio o porque se buscaron
otro proveedor al no poder contactar con el acostumbrado, para
que esto no ocurra antes de echar a andar un sistema de tanta
importancia hay que prever con tiempo el desarrollo futuro de su
soporte.

Una de las técnicas clásicas para
desarrollar hardware siempre ha sido aumentar memoria, capacidad
de procesamiento y capacidad de almacenamiento, al servidor que
soporta el sistema. Esta técnica es comúnmente
llamada "Scalling Up". En este tipo de desarrollo si usted
necesita subir su capacidad de procesamiento (Adicionar otro
Procesador o cambiar el existente por uno más
rápido) en servidores como: Acer Altos 600, 1200, 500; o
HP Proliant ML350 y ML370; tiene que apagar el sistema para
hacerlo, al igual que si desea aumentar la cantidad de memoria.
Tenga en cuenta que muchos de estos servidores pueden durar
años en fallar y por tanto otra de las situaciones muy
comunes es que a la hora de repararlos o que se necesita aumentar
sus capacidades técnicas es muy difícil encontrar
piezas compatibles en el mercado por el continuo desarrollo de
los sistemas de hardware, por esto es muy necesario comprar
piezas con vista a estas situaciones a tiempo, cosa esta muy
difícil de lograr estando el sistema funcionando
correctamente. Muchos de los directivos de estas grandes empresas
no ven con muy buenos ojos la compra de piezas para un sistema
que todavía no sueña con fallar o que resuelve los
problemas en la actualidad con una buena calidad de respuesta
computacional. El obtener un presupuesto para esto es una gran
responsabilidad del Administrador de Sistemas, gestión que
el futuro cuando ocurran los problemas obtendrá su
merecido reconocimiento por parte de la directiva de la empresa.
En el caso de la capacidad de almacenamiento, si tenemos un
arreglo (RAID) de discos SCSI no hace falta apagar el sistema.
Estos se pueden conectar en caliente sin necesidad de formatear
siquiera, el hardware RAID se encarga del resto.

A pesar de que los discos SCSI siguen siendo muy fiables
y rápidos. En estos días alternativas muy buenas
pueden hacer que un Administrador de Sistemas tome otra
decisión. Existe en el mercado tarjetas RAID de discos
Serial ATA, que permiten hacer arreglos de discos Serial ATA con
gran facilidad. Estos discos son menos fiables que los SCSI, pero
tiene un menor precio y su relación precio/capacidad
disminuye cada año. Por solo mostrar un ejemplo: Un disco
de 72 GB SCSI, la empresa Tecun lo comercializa en Cuba con un
costo de: $370.00, mientas que un Serial ATA de 80 GB pude costar
$75.00. Si decimos que el disco solo durará un año
de explotación lo que es muy raro en ambientes
climatizados y con sistemas eléctricos protegidos por
baterías. Al año siguiente se podrá comprar
un disco Serial ATA por el mismo precio, pero de 160 GB. El doble
de la capacidad del anterior. Si sumamos los costos de los dos
discos en los dos años, no llegará a ser igual al
costo del SCSI, que por supuesto durará funcionando sin
problemas otros 4 años. Sin embargo habrás
aumentado tu capacidad de almacenamiento al doble cosa esta que
es un dato importante en ambientes que aumentan su
colección de datos exponencialmente, como es el caso de un
Data Warehouse.

Es importante destacar que aunque hablo de un solo disco
estoy asumiendo que al menos existe otro que está
configurado como espejo que pude ser usado en caso de que falle
el principal. No es concebible un sistema tan importante que no
tenga este tipo de respaldo. Aunque los discos sean SCSI, debe
haber respaldo, lo que incrementa los costos.

En caso de que no se posea hardware RAID se pueden usar
las facilidades del Sistema Operativo Windows 2k3 que permite
crear arreglos desde el administrador de discos. Este tipo de
arreglos a pesar de ser menos rápido que el manejado
vía hardware. Es una solución muy buena en esos
casos.

Ejemplo de crear una partición espejo para el
sistema:

Abra el administrador de discos luego de haber
instalado físicamente los dos discos.
Haga clic derecho sobre el cuadro que dice disco 0 y
haga clic en convertir a disco dinámico.
Le aparece una ventana que le indica marcar los
discos que desea convertir. Marque los dos discos el 0 y el 1 y
haga clic en aceptar.
El sistema le preguntara si desea continuar con la
operación a pesar que el disco 0 tiene el sistema
operativo. Acepte y el sistema le avisará que se va
areiniciar para completar la operación. Acepte y espere
a que el sistema se reinice.
Abra el administrador de discos y ya con los discos
siendo dinámicos haga clic derecho sobre la
partición del sistema y presione sobre la opción
adicionar espejo.
En la ventana que aparece seleccione el disco 1 para
usar como espejo y acepte.
El sistema creará una partición
idéntica en el segundo disco y empezará a
duplicar la información existente en la
primaria.

Nota: Para que un disco pueda ser espejo total
del primero deben ser de capacidades idénticas. No puede
haber diferencias. Este error puede ocurrir al usar discos de
varias marcar como Ejemplo: Maxtor y Segate en el mismo
sistema.

A pesar de que los servidores profesionales son muy
fiables, en empresas medianas y pequeñas usarlos, puede
implicar un gasto demasiado elevado. Una alternativa puede ser
usar una PC Moderna con una memoria bastante grande 4 GB pudriera
ser suficiente. Actualmente los procesadores "Pentium D" que
poseen procesamiento en paralelo y una velocidad de más de
3 GHz que lo hacen muy competentes. Esto trae consigo otra
característica importante y es que este tipo de
configuración puede hacer uso de otra técnica de
desarrollo llamada "Scalling Out". Así, si en el
futuro hace falta mejorar la capacidad de este servidor porque el
negocio se ha desarrollado y ahora se procesa una mayor cantidad
de datos, se puede comprar otro servidor o varios iguales o
mejores en dependencia de la disponibilidad del mercado y del
presupuesto permitiendo, conectarlos en forma de Cluster
distribuyendo así la carga de trabajo. Windows 2k3 permite
la configuración de este tipo de soporte con gran
facilidad, dando la posibilidad de ir aumentado en cantidad de
servidores y mejorando en prestaciones, capacidad de
procesamiento y tolerancia a fallos. En mi opinión
personal en estos días este tipo de configuraciones son
mucho más fiables y económicas. El uso de varios
servidores en forma de cluster puede hacer uso de una
característica de SQL Server 2000 que permite la
actualización de los datos de todos estos servidores
según las necesidades del negocio. La replicación
de SQL Server 2000 no solo se puede aplicar sobre servidores
configurados como cluster también puede usarse para
replicar una de las bases de datos o un subconjunto del Data
Warehouse a un servidor de una entidad de la empresa.
Estaríamos hablando de un posible Data Mart. En nuestro
ejemplo clásico de empresa comercial estaríamos
hablando de replicar el Data Mart de las ventas de Ciego, al
servidor de la Sucursal Ciego de Ávila. Allí se
podrían sacar reportes muy provechosos por parte de los
comerciales sin necesidad de congestionar la Red WAN con los
datos de estos reportes. Teniendo en cuenta que los enlaces WAN
entre provincias son bastante costosos y lentos en
comparación con los 100 Mbps de la Red LAN.

Datos
distribuidos

La distribución de los Datos a través de
varios servidores es toda una teoría de la que nosotros
solo vamos a mencionar sus más relevantes rasgos, haciendo
énfasis en la replicación de SQL Server. Para
comenzar debemos decir que existen dos técnicas
fundamentales apara usar datos distribuidos.

Transacciones Distribuidas
Replicación

La primera necesita que todos los servidores
involucrados estén funcionando correctamente y en red
(online). Este tipo de transacciones mantiene actualizadas todas
las bases de datos involucradas en todo momento. Todos los
servidores tienen que estar sincronizados y si falla un solo
elemento de la transacción falla completa. Teniendo que
revertir el proceso en todos los servidores con su
correspondiente consumo de procesamiento. La segunda por su parte
puede mantener un margen de tiempo que permite a los servidores
no tener que estar sincronizados en todo momento. Así como
podemos ver el uso de una u otra técnica está dado
principalmente por dos factores: Latencia entre servidores y
Autonomía de cada servidor. Este último tiene que
ver por ejemplo: Si el Data Mart ubicado en Ciego es actualizado
a partir de la información recolectada del sistema de
inventario de todos los centros comerciales de esta provincia.
Entonces este Servidor posee una autonomía. Estos datos no
tienen por qué ser replicados al servidor de la Provincia
Santiago de Cuba o Matanzas. En este caso esa información
solo sería necesaria en El servidor central de la Empresa
donde se encuentra el Data Warehouse central. Así mismo
estos pueden ser replicados en el horario nocturno evitando que
los enlaces de comunicación WAN se congestionen por el
día que hay un mayor tráfico de correo, Messenger,
sistemas contables y otros. Es decir en nuestro ejemplo la
replicación sería la vía más
óptima de implementación para poder mantener los
datos distribuidos con autonomía a través de todas
las sucursales del país.

Estas dos técnicas de trabajo poseen una serie de
variantes para su implementación que mostramos en forma de
gráfico:

Figura 5: Formas de implementación de datos
distribuidos (Microsoft Training, 2000)

SQL Server 2000 tiene tres formas básicas para
implementar la distribución de datos:

Snapshot Replication
Transactional Replication
Merge Replication

SanapShot Replication: Replica todos los datos en la
publicación hacia el o los subscriptores. En el tiempo
especificado en el Agente. Este tipo de método es muy
usado en ambientes con una gran autonomía y donde se
necesitan los datos publicados con una latencia no muy
baja.

Transactional Replication: En este tipo de
replicación solo se replican los cambios incrementales.
Este tipo de replicación es usada en ambientes donde la
latencia de actualización debe ser baja, puede ser muy
útil además en ambientes conectados por enlaces de
poco ancho de banda pues el tamaño de los datos replicados
es muy bajo. El agente que permite realizar este trabajo tiene
además la opción de replicar los datos
inmediatamente.

Merge Replication: En este tipo de replicación se
analizan los artículos publicados y se crea uno solo con
la información resultante de la comparación de
estos en cada servidor. Al final cada servidor se actualiza con
el artículo resultante. En este tipo de replicación
pueden ocurrir colisiones si se usan las mismas bases de datos
con un nivel de autonomía alto. Estas colisiones pueden
ser resultas programando reglas en la
publicación.

SQL Server usa una filosofía llamada
Publicador-Distribuidor-Subscriptor. Análogamente a la
vida real el Publicador crea una a varias publicaciones, que no
son más que objetos de una base datos como: Tablas,
Vistas, Procedimientos almacenados; o Porciones de estos como una
proyección de una tabla determinada o una selección
de una o varias tablas relacionadas y a partir de esa
publicación es que un subscriptor puede suscribirse para
recibir las actualizaciones de esta publicación que le
llega a través del distribuidor. Tenga en cuenta que el
distribuidor puede estar en el propio servidor que publica o
puede estar en otro diferente para eliminar carga al publicador y
buscar un balance en el procesamiento. Atendiendo a esta
filosofía existen tres tipos básicos de
implementación física de los servidores:

Publicador Central y Muchos Suscriptores
Muchos Publicadores y un solo suscriptor
Muchos Publicadores y muchos suscriptores

En nuestro caso particular necesitaríamos
publicadores en todas las sucursales del país y un
subscriptor central en la Habana, con una replicación
transaccional (Tansactional Replication) que se ejecutara en el
horario de la noche. Es importante destacar que bases de datos de
otros sistemas como Lotus Notes. Implementan este sistema de
bases de datos distribuidas con replicación con muy buena
calidad, prestaciones y poco uso de ancho de banda en los enlaces
WAN lo que las hace muy competitivas en el mundo
empresarial.

Consultas
distribuidas

Existen dos formas fundamentales de hacer una consulta
distribuida usando SQL Server. La primera consiste en la
función OPENROWSET u OPENDATASOURCE, que permite ejecutar
una consulta sobre cualquier SGBDR que soporte OLE DB o tenga un
driver ODBC compatible con Microsoft. Este tipo de procesamiento
es llamado en la literatura como: "ad hoc query". La otra
vía es usando servidores enlazados (linked servers) que
soporten también la tecnología OLE DB de Microsoft.
Esta última es usada para hacer consultas frecuentes a
diferencia de la primera que se usa para consultas con
períodos de tiempo largos. (Microsoft Training,
2000)

Sintaxis de OPENROWSET:

OPENROWSET('provider_name'

{'data -source'; 'user_id' ;
'password' | 'provider_string'},

{[catalog.][schema.]object |
'query'})

Descripción de
Parámetros:

Provider_name: Nombre del proveedor OLEDB. Ejemplo
MSDASQL(Para ODBC)

Data-source: Fuente de donde se obtendrán los
datos. Como veremos más adelante no es más que el
nombre del servidor que contiene la BD.

User_id: nombre de usuario de acceso a la
BD

Passord: Contraseña de acceso a la
BD

Provider_string: En caso de ser necesaria una cadena
de conección a la fuente de datos.

Catalog: No es más que el nombre de la base de
datos.

Schema: Nombre del usuario dueño de los
objetos de la BD. Ejemplo en SQL dbo.

Object: Tabla, procediemiento, función, u otro
objeto de la base de datos.

Query: Consulta en cuestión.

Para poder entender mejor la sintaxis vamos a ver un
ejemplo de cómo podemos hacer una consulta sobre un
servidor SQL, otro sobre como hacerlo sobre una base de datos
Access y uno sobre como integrar una consulta de este tipo en una
normal. (Microsoft Training, 2000)

Ejemplo 1: Encuesta a un Servidor
SQL

SELECT a.*

FROM OPENROWSET('SQLOLEDB',
'LONDON1';

'newcustomer';'mypassword',

'SELECT ProductID, UnitPrice

FROM Northwind.dbo.Products ORDER BY
UnitPrice')

AS a

Ejemplo 2: Encuesta a un servidor
Aceess

SELECT a.*

FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0'

'C:MSOfficeAccessSamplesNorthwind.mdb';

'newcustomer'; 'mypassword',

Orders)

AS a

Ejemplo 3:

USE Northwind

SELECT cust.* ord.*

FROM Customers as cust JOIN

OPENROWSET('Microsoft.Jet.OLEDB.4.0'

'C:MSOfficeAccessSamplesNorthwind.mdb'';

'newcustomer'; 'mypassword',

Orders)

AS ord

On cust.customerid =
ord.customerid

Como vemos este tipo de consultas puede ayudarnos mucho
a la hora de extraer datos desde los sistemas que nutren al Data
Warehouse. Aunque ya hemos visto que SQL tiene servicios (DTS) y
otras herramientas gráficas que pueden extraer datos y
transformarlos muy fácilmente. Esta variante nos permite
controlar el trabajo usando sentencias de programación que
fácilmente pueden ser combinadas con otras para obtener
resultados muy profesionales. Otra de las ventajas que nos ofrece
esta filosofía de trabajo es que no tenemos que importar
los datos a SQL para analizarlos. Hay ocasiones en que no
queremos importar esa base de datos porque solo se consulta una
sola vez al año o por otras cuestiones diferentes y de
esta forma podemos consultarla sin mucho esfuerzo.

Existen varios tipos de proveedores de OLE DB que pueden
ser consultados en la ayuda de SQL. Aquí queremos mostrar
solo los ejemplos más usados: (Microsoft Training,
2000)

SQL Server: N'SQLOLEDB'
Microsoft OLE DB Provider for Access (Jet):
'Microsoft.Jet.OLEDB.4.0'
Microsoft OLE DB Provider for Oracle: 'MSDAORA'
data_source is the SQL*Net alias name for the Oracle
database to be added as a linked server
OLE DB Provider for ODBC (Using data_source
parameter): provider_name is 'MSDASQL'
data_source is 'LocalServer'
OLE DB Provider for ODBC (Using
provider_string parameter): provider_name is
'MSDASQL' provider_string is 'DRIVER={SQL Server}
SERVER=servername
UID=login;PWD=password;'

Nota: Para hacer consultas distribuidas es
necesario que estén activadas las siguientes opciones.
ANSI_NULLS y ANSI_WARNIGS. En clientes como Query Analyzer ya
están activadas por defecto, pero en otros como osql
no.

Por otra parte el uso de servidores en lazados permite
que las consultas sean ejecutadas con el previo conocimiento de
los servidores disponibles para hacer esta. Este tipo de
consultas es usada cuando su periodo de ejecución es
relativamente bajo. En el caso nuestro de estudio
podríamos estar hablando de una consulta hecha para
obtener datos de las ventas de los Data Mart de cada servidor SQL
de cada sucursal. Así el servidor central de la empresa
podría estar enlazado con los restantes servidores y desde
este se podrían crear este tipo de consultas o
transacciones distribuidas como veremos más
adelante.

Para enlazar un servidor con otro se usa el
procedimiento almacenado sp_addlinkedserver. Vamos a ver
su uso con algunos ejemplos:

Ejemplo 4: Uso de sp_addlinkedserver (Microsoft
Training, 2000)

— USando SQL Server

EXEC sp_addlinkedserver

@server = 'AccountingServer',

@svrproduct = 'SQL Server'

— Usando Oracle

EXEC sp_addlinkedserver

@server = 'OracleFinance',

@svrproduct = 'Oracle',

@provider = 'MSDAORA',

@datasrc = 'OracleDB'

Sintaxis de ejecución del procedimiento:
(Microsoft Training, 2000)

sp_addlinkedserver [ @server = ]
'server'

[ , [ @srvproduct = ]
'product_name' ]

[ , [ @provider = ]
'provider_name' ]

[ , [ @datasrc = ]
'data_source' ]

[ , [ @location = ]
'location' ]

[ , [ @provstr = ]
'provider_string' ]

[ , [ @catalog = ]
'catalog' ]

@server Name of the linked server to
create

@svrproduct Product name of the OLE DB data
source

@provider The unique, friendly name for the OLE
DB provider corresponding to

this data source

@datasrc Name of the data source as interpreted
by the OLE DB provider

@location Location of the database as interpreted
by the OLE DB provider

@server Name of the linked server to
create

@svrproduct Product name of the OLE DB data
source

@provider The unique, friendly name for the OLE
DB provider corresponding to

this data source

@datasrc Name of the data source as interpreted
by the OLE DB provider

@location Location of the database as interpreted
by the OLE DB provider

En el caso de servidores como SQL Server que necesitan
de un usuario y una contraseña para poder conectarse a
ellos es necesario que ocurra una de las dos
condiciones:

El usuario con que se ejecuta la consulta esté
definido en los dos servidores y tenga derechos para ejecutar
las tareas deseadas.
Se ejecute previamente un mapeo de usuarios usando el
procediemiento: sp_addlinkedsrvlogin.

Sintaxis del procedimiento sp_addlinkedsrvlogin:
(Microsoft Training, 2000)

sp_addlinkedsrvlogin [ @rmtsrvname = ]
'rmtsrvname'

[ , [ @useself = ] 'useself' ]

[ , [ @locallogin = ] 'locallogin'
]

[ , [ @rmtuser = ] 'rmtuser' ]

[ , [ @rmtpassword = ]
'rmtpassword'

@rmtsrvname Name of a linked server to which the
login mapping applies.

@useself Determines whether SQL Server login
accounts use their own

credentials or the values of the @rmtuser and
@rmtpassword

arguments to connect to the server specified by the
@rmtsrvname

argument. A value of TRUE for @useself is invalid
for a Windows

Authenticated login account.

@locallogin An optional login account on the
local server. If used, @locallogin

must already exist on the local server. If this value is
null, then all

account on the remote server specified by
@rmtuser.

@rmtuser The optional user name for connection to
@rmtsrvname when

@useself is FALSE.

@rmtpassword The optional password associated
with @rmtuser.

Ejemplo 5: Uso de mapeo de usarios: (Microsoft Training,
2000)

EXEC sp_addlinkedsrvlogin

@rmtsrvname = 'AccountingServer',

@useself = 'false',

@locallogin = 'Accountwriter',

@rmtuser = 'rmtAccountWriter',

@rmtpassword = 'financepass'

EXEC sp_addlinkedsrvlogin

@rmtsrvname = 'AccountingServer',

@useself = 'false',

@rmtuser= 'allcustomers'

Como podemos ver aquí el procedimiento enlaza el
usuario que ejecuta la consulta localmente con el que tiene los
derechos para hacer el trabajo en el servidor remoto. Así
cuando se ejecute la consulta, el servidor usará el
usuario adecuado para ejecutar la parte que corresponde al
servidor remoto con credenciales diferentes. En nuestro caso de
estudio, el servidor de la Sucursal Ciego no tiene que tener la
misma contraseña para el usuario sa que los restantes
servidores de las demás sucursales, para que nuestro Data
Warehouse funcione. Así en el servidor central puede
existir un mapeo para cada servidor de cada sucursal. Esto
aumenta la seguridad, pues si un hacker se hace de una de las
contraseñas de algunos de los servidores de las sucursales
no podrá acceder a las restantes ni al servidor central.
Incluso sin pensar en hacker. En el caso de que algún
administrador quiera consultar los datos de una sucursal
diferente a la que administra esto le sería imposible
debido a esta técnica, lo que daría la posibilidad
de que solo los usuarios de la directiva de la empresa tuvieran
acceso a reportes que incluyan varios territorios.

Hasta aquí ya sabemos como enlazar los
servidores, pero no sabemos como hacer la consulta sobre estos.
La única cosa que diferencia una consulta distribuida
sobre servidores enlazados, de una consulta sobre un solo
servidor es que la primera tiene que usar nombrado de cuatro
partes completo. El nombrado de cuatro partes completo incluye
además de los elementos que ya estamos acostumbrados a
usar como: Catalogo.Squema.Objeto, el nombre del servidor que se
va a encuestar. Así, si el Server de Ciego se llama sassql
el nombre de cuatro partes completo de la tabla de ventas
sería: sassql.datamartventas.dbo.ventasfact. Ahora
mostraremos un ejemplo de cómo se aplica esto en la
consulta.

Ejemplo 6: Consulta sobre servidores enlazados
(Microsoft Training, 2000)

SELECT CompanyName, Phone

INTO PhoneList

FROM
AccountingServer.NorthwindRemote.dbo.Suppliers

SELECT CompanyName

FROM
AccountingServer.NorthwindRemote.dbo.Suppliers

SELECT ProductName, CompanyName

FROM Products p JOIN

AccountingServer.NorthwindRemote.dbo.Suppliers

ON p.supplierid = s.supplierid

Existen algunas restricciones a la hora de hacer
consultas sobre servidores distribuidos. Estas son:

No se pueden usar sentencias create, alter,
drop.
No sepuede usar goup by cuando las tablas contienen
objetos largos como TEXT, NTEXT, etc.
No se pueden usar las sentencias: readtext,
writetext, updatetext

Otra de las cosas más importantes que posee esta
técnica es que normalmente las consultas son ejecutadas en
el servidor local, pero el código puede ser modificado
para que se hagan en el servidor remoto. Esto puede mejorar en
gran medida el rendimiento del servidor, pues la carga de
procesamiento se hace en el otro extremo. Imagine que necesita
una consulta sobre las ventas de todas las sucursales. Esta puede
ser una consulta que consuma mucho tiempo, dado la cantidad de
datos a procesar. Si se divide el trabajo entre los servidores de
cada sucursal el tiempo de respuesta puede mejorar en gran
medida, para usar esta técnica se hace uso de la sentencia
openquery, que veremos con un ejemplo: (Microsoft Training,
2000)

Sintaxis:

OPENQUERY (linked_server,
'query')

Ejemplo 7: Uso de la sentencia OPENQUERY

SELECT * FROM OPENQUERY

(AsiaServer, 'SELECT ProductID, Royalty

FROM Northwind.dbo.ProductInfo')

Como podemos ver solo debemos especificar el servidor
que va a ejecutar la consulta y luego la consulta en
cuestión. Así ese servidor la ejecutará y
retornará los resultados:

Existen dos elementos más de alta importancia a
la hora de trabajar con servidores enlazados. El primero de ellos
es la ejecución de procedimientos almacenados en
servidores remotos. Para esto se usa la misma forma de nombrado
de cuatro partes ejemplo:
sassql.datamartventas.dbo.sp_listarVetas. El otro elemento
importante es el uso de transacciones distribuidas. Esta
característica de miscrosoft nos permite hacer tareas que
necesitan ejecutarse como un todo en servidores distribuidos como
una única transacción. Para usar esta
característica se usa la sentencia BEGIN DITRIBUTED
TRANSACTION <NAME>, vamos a ver esto con un
ejemplo:

Ejemplo 8: Uso de transacciones distribuidas

SET XACT_ABORT ON

BEGIN DISTRIBUTED TRANSACTION

EXEC Savingsdb.dbo.withdraw 1234, 100

EXEC Centralserver.Checkingdb.dbo.deposit 1234,
100

COMMIT TRAN

Nota: Recuerde que un rollback de la
transacción puede llevar este proceso a cabo en todos los
servidores involucrados disminuyendo mucho el rendimiento de los
mismos. Además si no está la primera sentencia del
ejemplo no se hace un rollback completo. Por lo que siempre es
recomendado activar esa opción (XACT_ABORT) para en
última instancia hacer un rollback completo y que la BD no
quede inconsistente.

Salva y restaura
de datos

Una de las cosas más importantes cuando usted ya
posee un Data Warehouse es mantenerlo y para esto salvar sus
bases de datos es algo fundamental. Para ilustrarlos con un
ejemplo les puedo decir que el Data Warehouse que administro
generaba todos los días 87 GB de salvas. Salvar este
volumen de datos se hace engorroso computacionalmente y
también a la hora guardar estos datos en un lugar con
espacio disponible. Si por ejemplo usa DLT que pueden almacenar
hasta 80 GB en cinta el proceso de salvar un casete de este tipo
diario puede ocupar gran parte del procesamiento del servidor en
horario nocturno y requiere de 365 casetes para todo el
año lo que es un gasto considerable. Si por el contrario
decide usar DVD cuya implementación es más barata,
necesitaría nada más que la suma aproximada de 23
DVD diarios para hacer la salva de estos datos, sin mencionar que
necesitaría una persona solo para cambiar los CD cuando se
llenen. Todo esto sin tener en cuenta que alguno de estos pueda
tener defectos de fábrica. La posibilidad de usar
compactadores hace posible que este gasto disminuya en una gran
medida pues usando la máxima compresión de
compactadores como WINRAR el tamaño se reduce
aproximadamente a solo 3 GB lo que haría posible salvar
todo esta información en solo un DVD, pero el uso de esta
compresión hace que el procesamiento de un servidor
profesional (Hacer Altos 600 de dos procesadores 900 MHz, 750 MB
de RAM y arreglo de discos SCSI) se agote al punto de demorar
más de 14 horas para compactar toda esa
información. Disminuyendo así el procesamiento en
horario laboral, lo que hace este proceso inviable. Sin embargo
si usamos una compresión menor en este compactador podemos
lograr una razón cantidad datos/ costo computacional muy
buena. Por ejemplo usando un modo de compresión menor
podemos lograr un tamaño de solo 5 GB en un tiempo de solo
2 horas lo que comparado con los 3 GB en 14 horas es realmente un
gran avance.

Otra de las técnicas más usadas y
necesarias en nuestro caso para salvar servidores Data Warehouse
es la salva incremental, que puede generar el servidor SQL
Server. Esta salva incremental necesita ante todo que ya se tenga
una salva completa de la BD, pero en las restantes salvas se hace
solo salva de los datos que han sido modificados o adicionados,
lo que disminuye en gran medida este volumen de salva. Por su
parte la restaura de los datos salvados con una salva incremental
es mucho más rápida y fiable que restaurar una
salva completa de la BD. Esta sin duda es la mejor de las
opciones cuando se habla de Data Warehouse, ya que los datos de
un sistema así no cambian con tanta frecuencia como los de
las aplicaciones OLTP, por lo que el volumen de la salva
incremental es muy pequeño y por otra parte las adiciones
de datos se hace en muchos casos con una diferencia de tiempo
bastante grande.

Nota: Recuerde siempre que los datos salvados
deben ser llevados a un dispositivo externo y guardados fuera del
local de servidores con una seguridad física buena, pues
si son obtenidos por otras personas estas podrán saber
muchos datos de la empresa.

A continuación vamos a ver un ejemplo de
cómo usar el WINRAR en un script para salvar los datos de
un servidor Data Warehouse.

Ejemplo 9: Salva de datos de un servidor Data
Warehouse.

rem Script para las salvas diarias de los
servidores

@echo off

echo "salva del dia:" >>
F:TareasBackuplog.txt

date /T >> F:TareasBackuplog.txt

time /T >> F:TareasBackuplog.txt

del /f /q E:SharedBackupbk*.rar

FOR /F "usebackq tokens=2,3,4 delims=/ " %%i IN (`date
/t`) DO @SET datebackup=%%k%%i%%j

FOR /F "usebackq tokens=1,2 delims=: " %%i IN (`time
/t`) DO @SET timebackup=%%i%%j

FOR /F "usebackq tokens=1 delims= " %%i IN (`date /t`)
DO @SET dayname=%%i

SET DAYTIME=%datebackup%%timebackup%00

if EXIST daytimelastbackup.txt goto
NoFirstTime

rem primera vez que se salvan los datos

rem Salvar SQL

dir E:SqlDataMSSQLBACKUP*.bak /B /L /S /A:-D >
F:TareasBackupfilelistaplics2.lst

dir E:SqlDataMSSQLBACKUP*.trn /B /L /S /A:-D
>> F:TareasBackupfilelistaplics2.lst

"C:Program FilesWinRARrar.exe" a
E:SharedBackupbkaplics1.rar
@F:TareasBackupfilelistaplics1.lst

"C:Program FilesWinRARrar.exe" a -m1
E:SharedBackupbkaplics2.rar
@F:TareasBackupfilelistaplics2.lst

rem recopilar toda la salva bajo un solo
nombre

"C:Program FilesWinRARrar.exe" a -m0
E:SharedBackupsas9%datebackup%.rar
E:SharedBackupbk*.rar

goto eof

:NoFirstTime

rem ################################ Empieza la salva
incremental

for /F "tokens=1" %%i in (daytimelastbackup.TXT) do @SET
LastBackup=%%i

rem Salvar SQL

dir E:SqlDataMSSQLBACKUP*.bak /B /L /S /A:-D >
F:TareasBackupfilelistaplics2.lst

dir E:SqlDataMSSQLBACKUP*.trn /B /L /S /A:-D
>> F:TareasBackupfilelistaplics2.lst

"C:Program FilesWinRARrar.exe" a -ta%LastBackup%
E:SharedBackupbkaplics1.rar
@F:TareasBackupfilelistaplics1.lst

"C:Program FilesWinRARrar.exe" a -ta%LastBackup% -m1
E:SharedBackupbkaplics2.rar
@F:TareasBackupfilelistaplics2.lst

rem recopilar toda la salva bajo un solo
nombre

"C:Program FilesWinRARrar.exe" a -m0
E:SharedBackupsas9%datebackup%.rar
E:SharedBackupbk*.rar

:eof

rem Fin del Script de Salva

echo %DAYTIME% > daytimelastbackup.TXT

net use x: \sas2salvasdia /user:xxxx
"xxxxxxx"

if %ERRORLEVEL% EQU 0 (copy /Y
E:SharedBackupsas9*.rar x:%dayname%) else (echo "Error al
Conectarse al servidor" >>
F:TareasBackuplog.txt)

if %ERRORLEVEL% EQU 0 (del /F /Q
E:SharedBackupsas9*.rar) else (echo "Error al copiar el
archivo al servidor" >>
F:TareasBackuplog.txt)

if %ERRORLEVEL% EQU 0 (echo "Salva Exitosa" >>
F:TareasBackuplog.txt)

net use x: /d

del /f /Q E:SharedBackupbk*.rar

time /T >> F:TareasBackuplog.txt

Conclusiones

El uso de sistemas Data Warehouse es una poderosa
estrategia para administrar empresas.
Los resultados que arrojan los análisis de los
datos obtenidos y consolidados en el Data Warehouse pueden
hacer que la directiva de la empresa corrija las estrataegias
hasta ahora trazadas y mejore así las
ganancias.
El mantenimiento de un Sistema Data Warehouse es algo
complejo, que requiere de recursos monetarios y
estrategia.
El modelo dimensional brinda una forma muy sencilla
de representación de los datos y mejora así el
tiempo de consultas a la base de datos.
Los sistemas de transformación de datos de SQL
Server brindan una poderosa herramienta a quienes se inicien en
la confección de un Data Warehouse sobre este gestor de
Bases de datos.

Bibliografía

Robert Wrembel & Christian Concilia, DATA
WAREHOUSES AND OLAP Concepts, Architectures and Solutions,
2007.
Microsoft, Microsoft SQL Server 7.0 Data Warehousing
Training Kit, Microsoft Press, 2000.
Microsoft, Microsoft Training and Certification,
Course 2073A, Programing a Microsoft SQL Server 2000 Database,
Microsoft Press, 2000.
Microsoft, Microsoft Training and Certification,
Course 2072A, Administering a Microsoft SQL Server 2000
Database, Microsoft Press, 2000.
Microsoft, SQL Server 2000 Books Online, Microsoft
Press, 2000