Introducción a Data Warehousing

Vista previa del material en texto

1
A TRAVES DE UNA IMPLEMENTACIÓN
DATAWAREHOUSING
2
CONCEPTOS INTRODUCTORIOS
Introducción al Data Warehousing.
Soluciones OLAP.
Diseño de un Data Warehouse.
Modelos OLAP.
Cubos OLAP.
Data Mining.
Lic. Nicolás Bocalandro
3
POR QUÉ DATAWAREHOUSING
OLTP Vs OLAP.
Datos Sucios.
Seguridad.
Grandes volúmenes de datos ociosos.
Personalización del cliente.
Lic. Nicolás Bocalandro
4
CARACTERISTICAS
Sus datos se usan para la Toma de 
Decisiones.
Los datos se duplican.
Es una combinación de Hard, Soft y Datos.
Repositorio de datos históricos.
Uno o más Data Marts.
Lic. Nicolás Bocalandro
5
CARACTERISTICAS DE UN 
DATAWAREHOUSE
 Orientado al tema.
 Integrado.
 De tiempo variante.
 No volátil
Lic. Nicolás Bocalandro
6
CARACTERISTICAS DE LOS DATOS
 Detalle de datos actuales. 
 Detalle de datos antiguos. 
 Datos ligeramente resumidos. 
 Datos completamente resumidos .
 Meta data. 
Lic. Nicolás Bocalandro
OLAP
7
RDBMS
SQL ServerCliente
ResultadosResultados
Aplicaciones Cliente
OLTP
ConsultaConsulta
ARQUITECTURA CLIENTE SERVIDOR
OLAP
Lic. Nicolás Bocalandro
8
COMPONENTES CLIENTE-SERVIDOR
Aplicación cliente
Biblioteca de red del clienteBiblioteca de red del cliente
Cliente
SQL Server
Motor relacional
Motor de
almacenamiento
Servidor
Base de
Datos local
biblioteca de base de datos)
API de base de datos
(OLE DB, ODBC,
biblioteca de base de datos)
Procesador
Memoria
Servicios abiertos
de datos
Bibliotecas de red
del servidor
Lic. Nicolás Bocalandro
9
ACCESO A DATOS
Aplicaciones y componentes Explorador
IIS
ActiveX Data Objects (ADO)
Datos
Consumidores
Datos
Proveedores
Access Excel
Cliente
personalizado
Proveedores de datos OLE DB (Servicios)
Cursores
Procesadores
de consultas
Servicios
empresariales
Proveedores de datos OLE DB (Datos)
ODBC Exchange
Active
DirectoryJetSQL Server Excel
Otros proveedores
de OLE DB
Lic. Nicolás Bocalandro
10
CARACTERISTICAS OLTP (1/2)
 OLTP
Normalizado (redundancia mínima).
Orientado a la transacción.
Poco espacio almacenamiento.
Consultas lentas.
Ejecutar cada consulta al momento de requerir datos.
Lic. Nicolás Bocalandro
11
 OLTP
Orientado a la aplicación.
Datos actuales.
Renovación de datos.
CARACTERISTICAS OLTP (2/2)
Lic. Nicolás Bocalandro
12
DIFERENCIAS OLTP Y OLAP (1/3)
Lic. Nicolás Bocalandro
OLTP OLAP
Alineación por Aplicación. Alineación por dimensión.
Típicamente no integradas. Debe ser integrada.
Las consultas son lentas. Las consultas son muy 
rápidas.
Cada tema de negocio puede 
tener información en 
diferentes sistemas. 
Toda información de un tema, 
alimentado de varios 
sistemas, reunidos en una 
sola base de datos. 
13
DIFERENCIAS OLTP Y OLAP (2/3)
Lic. Nicolás Bocalandro
OLTP OLAP
Diferentes sistemas contienen 
diferentes tipos de datos. 
Todos los tipos de datos 
integrados en un solo 
sistema. 
Diferentes formatos de 
archivos. 
Formato de archivos 
Standard. 
Ocupan poco espacio y 
requieren poco Hardware.
Requieren mucho espacio y 
Hardware adecuado.
Diferentes plataformas de 
Hardware. 
Un solo servidor lógico 
(Warehouse). 
14
DIFERENCIAS OLTP Y OLAP (3/3)
Lic. Nicolás Bocalandro
OLTP OLAP
Los usuarios son los que giran 
las ruedas de la Organización.
Los usuarios miran las ruedas 
de la Organización.
Se manejan cientos de 
transacciones por día. 
Se maneja solo una 
transacción que contiene 
cientos de registros. 
Datos operacionales son 
altamente volátiles, cambian a 
medida que opera la 
Organización.
Los datos del DW son 
altamente estables, son 
insertados en intervalos 
definidos y no son 
modificados. 
15
OLTP
OLAP
DIFERENCIAS ENTRE OPERACIONES EN 
OLTP Y OLAP
Consulta
Actualiza
Inserta
Borra
Consulta
Inserta
Los datos se renuevan 
constantemente.
Los datos se cargan en el 
Warehouse y posteriormente se 
consultan.
Lic. Nicolás Bocalandro
16
 Numérico
Entero
Numérico exacto
Numérico aproximado
Moneda
 Fecha y hora
 Carácter y caracteres Unicode
 Binario
 Otros
TIPOS DE DATOS UTILIZADOS
OLTP OLAP
 Carácter
 Fecha
 Numérico
 Booleano
Lic. Nicolás Bocalandro
17
ALMACENAMIENTO
MOLAP (OLAP multidimensional).
ROLAP (OLAP relacional). 
HOLAP (OLAP híbrido).
Lic. Nicolás Bocalandro
18
Data 
Warehouse
User 
Data AccessData 
Sources Staging
Area
Data Marts
COMPONENTES DE UN 
DATAWAREHOUSE
Lic. Nicolás Bocalandro
19
ALMACENAMIENTO
MOLAP
Vista de 
Usuario
Agregaciones
Detalladas
Vista de 
Usuario
Agregaciones
ROLAP
Detalles
HOLAP
Vista de 
Usuario
Agregaciones
Lic. Nicolás Bocalandro
20
Datos agregados se almacenan en una 
estructura multidimensional de alto 
rendimiento.
MOLAP
Lic. Nicolás Bocalandro
21
Se utiliza una base de datos relacional 
para almacenar datos agregados.
ROLAP
Lic. Nicolás Bocalandro
22
Los datos del cubo se almacenan en 
una base de datos relacional y los datos 
agregados se almacenan en una 
estructura multidimensional.
HOLAP
Lic. Nicolás Bocalandro
23
Tabla 
Hechos
Dimensión
DimAlumnoDimAlumno
Legajo
Nombre
Apellido
DimTiempoDimTiempo
IdTiempo
Fecha
...
DimMateriaDimMateria
IdMateria
Materia
...
DimProfesorDimProfesor
IdProfesor
Apellido
...
DimUniversidadDimUniversidad
IdUniversidad
Universidad
...
HechosAcademHechosAcadem
IdTiempo
Legajo
IdMateria
IdProfesor
IdUniversidad
Nota
Lic. Nicolás Bocalandro
ESQUEMA ESTRELLA
24
Tabla de Hechos
10 10001 22 78 10 5
IdTiempo IdTiempo Legajo IdMateria IdProfesor IdUniversidadIdUniversidadNota
TABLA DE HECHOS
Clave ForáneaClave Foránea MedidasMedidas
Lic. Nicolás Bocalandro
25
 Describen entidades del negocio.
 Proveen contexto a los atributos numéricos.
 Tienen los datos organizados en jerarquías.
CARACTERISTICAS DE LAS 
DIMENSIONES
Lic. Nicolás Bocalandro
26
Provincia
Buenos Aires
Capital Federal
La Plata
Olavarría
Mendoza
Mendoza
San Rafael
OLAP
DIFERENCIAS ENTRE MODELOS
OLTP
Provincia
Buenos Aires 
Mendoza
Localidad
BA Capital Federal
BA La Plata
BA Olavarría
MZ Mendoza
MZ San Rafael
Lic. Nicolás Bocalandro
27
Tiempo
n Año
2002
2003
2004
n n Semestre
I Semestre
II Semestre
n n n Mes
Enero
Febrero
Marzo
Abril
Nivel Año
Miembros
Nivel Semestre
Miembros
Nivel Mes
Miembros
JERARQUIAS EN LA DIMENSION
Lic. Nicolás Bocalandro
28
 Define jerarquías en múltiples 
dimensiones.
 El modelo esta mas normalizado que el de estrella 
(única dimensión).
ESQUEMA DE COPO DE NIEVE
Lic. Nicolás Bocalandro
29
MEDIDAS
 Son valores numéricos interesantes para calcularse.
 Se encuentran en las tablas de hechos.
 Son la interseccion de todas las dimensiones. 
Lic. Nicolás Bocalandro
30
MODELO NORMALIZADO
Lic. Nicolás Bocalandro
31
MODELO DE-NORMALIZADO (1/2)
Lic. Nicolás Bocalandro
32
MODELO DE-NORMALIZADO (2/2)
Lic. Nicolás Bocalandro
33
DEFINIENDO UN CUBO (1/2)
Tablas de Hechos.
Las dimensiones. 
Jerarquías.
Medidas.
Lic. Nicolás Bocalandro
34
DEFINIENDO UN CUBO (2/2)
Q4Q1 Q2 Q3
Dimensión Tiempo
Olavarría
Rosario
Mendoza
Contabilidad I
Adm. General
Análisis I
Capital Federal
Algebra
D
im
en
si
ó
n
 G
eo
rg
rá
fi
ca
Lic. Nicolás Bocalandro
35
 OLAP híbrido que relacionan a más de un cubo, y 
genera particiones adecuadas a necesidades 
específicas. 
CUBOS VIRTUALES Y PARTICIONES
Lic. Nicolás Bocalandro
36
CUBOS VIRTUALES
 Equivalente a una vista de un RDBMS.
 Combinación de uno o más cubos.
 No consume espacio.
Cubo Virtual
Cubo Alumno
Cubo Facultad
Lic. Nicolás Bocalandro
37
Nota
CONSULTANDO UN CUBO
Olavarría
Rosario
Mendoza
Capital Federal
D
im
en
si
ó
n
 G
eo
rg
rá
fi
ca
Q4Q1 Q2 Q3
Dimensión Tiempo
Contabilidad I
Adm. General
Análisis I
Algebra
Lic. Nicolás Bocalandro
38
OPERACIONES A REALIZAR EN UN CUBO
Lic. Nicolás Bocalandro
 DRILL DOWN: Análisis de Profundidad.
 ROLL UP: Resumir información.
 SLIC: Seleccionar una porción o gajo.
 DICE:Seleccionar dos porciones cruzadas.
 PIVOTING: girar un cubo, mostrando distintas caras. 
39
DEFINIENDO UN SLICE
Olavarría
Rosario
Mendoza
Capital Federal
D
im
en
si
ó
n
 G
eo
rg
rá
fi
ca
Contabilidad I
Adm. General
Análisis I
Algebra
Q4Q1 Q2 Q3
Dimensión Tiempo
Lic. Nicolás Bocalandro
40
VISUALIZANDO EL CUBO
Lic. Nicolás Bocalandro
41
CONECTANDOSE AL CUBO OLAP
Lic. Nicolás Bocalandro
42
 Transforma y mueve datos.
 Se puede programar una tarea de DTS.
OLTP
Source
DTS Almacenamiento
DW
Analysis Services
Clientes
DTS
SERVICIO DE TRANSFORMACION DE 
DATOS
Lic. Nicolás Bocalandro
43
 No limitado a SQL Server 2000
SQL Server 6.5, SQL Server 7.0, Microsoft Access 97, 
Microsoft Access 2000, Oracle 7.3, Oracle 8.0
Cualquier Proveedor ODBC / OLE DB 
Origenes 
OLTP
DTS Almacenamiento
DW
Analysis Services ClientesDTS
ALMACENAMIENTO ANALYSIS SERVICES
Lic. Nicolás Bocalandro
44
OLTP
Source
DTS DW
Storage
Analysis 
Services
ClientesDTS
ANALYSIS SERVICES
Lic. Nicolás Bocalandro
45
Origene
s OLTP
DTS Almacen
amiento
DW
Analysis 
Services
ClientesDTS Interfaces
Clientes
 APIs:
Nivel bajo: OLE DB para OLAP y OLE DB para 
Data Mining.
Nivel alto: ADO-MD.
INTERFACES DEL CLIENTES
Lic. Nicolás Bocalandro
46
Origen
OLTP
DTS Almacenamiento 
DW
Analysis
Services
ClientesDTS Interfaz
Cliente
 Office 2000.
 Aplicaciones de terceras partes.
 Aplicaciones a medida.
APLICACIONES CLIENTES
Lic. Nicolás Bocalandro
47
O
L
E
D
B
 fo
r O
L
A
P
O
L
E
D
B
 fo
r D
M
MOLAP
Store
A
p
lic
a
c
ió
n
A
D
O
 M
D
P
iv
o
t T
a
b
le
 S
e
rv
ic
e
Analysis Manager
DSO
SQL Server
Data
Warehouse
Otros
Proveedores
OLAP
Analysis Server
OLAP
Engine
DM
Engine
ARQUITECTURA DEL ANALYSIS 
MANAGER
Lic. Nicolás Bocalandro
48
 Orientado a la administración de una base de 
datos.
 Snap-In to MMC.
 Objetos Orientados a la Decision (DSO).
Analysis ServerDSODSO
Analysis ManagerAnalysis Manager
Interfaz de AdministraciónInterfaz de Administración
ANALYSIS MANAGER
Lic. Nicolás Bocalandro
49
REPOSITORIO DE METADATOS
 Contiene todos los metadatos
 Se encuentra en msmdrep.mdb
 Se puede migrar el repositorio a SQL Server:
SQL Server 7.0 OLAP.
SQL Server 2000. 
Lic. Nicolás Bocalandro
50
CREATE procedure CargarReg11
as
insert Reg11
(
TipoRegistro,
TranAFIP,
NumeroAnticipoOrigCredito, 
AnioAnticipoOrigCredito, 
CreditoPeriodoAnterior
)
select
11,
TRANSACCION_AFIP,
substring(CONTENIDO_C,1,2),
substring(CONTENIDO_C,3,4),
substring(CONTENIDO_C,7,15)
from disquete
where REGISTRO=11
Llenando la dimensión
Tiempo
Lic. Nicolás Bocalandro
51
create procedure InsertarDimUbicacionAlumno
As
insert DimUbicacionAlumno
SELECT A.Legajo, P.Provincia, L.Localidad, 
S.NombreSede, F.Facultad, U.Universidad, 
C.Carrera, A.Apellido, A.Nombre 
FROM Alumno A
inner join Provincia P on A.CodProvincia=P.CodProvincia 
inner join Localidad L on A.CodLocalidad=L.CodLocalidad 
inner join Sede S on L.CodLocalidad=S.CodLocalidad 
and P.CodProvincia=S.CodProvincia 
inner join Facultad F on S.CodSede=L.CodSede 
inner join Universidad L on F.Universidad=L.Universidad 
inner join CarreraAlumno CA on A.Legajo=A.Legajo 
inner join Carrera C on CA.Carrera=C.Carrera 
group by A.Legajo,P.Provincia,L.Localidad,S.NombreSede, 
F.Facultad,U.Universidad,C.Carrera,A.Apellido,A.Nombre 
Llenando la dimensión
Tiempo
Lic. Nicolás Bocalandro
52
create procedure 
InsertarDimBancoSinRepetidos
as
insert DimBancoXML
SELECT Banco, Sucursal, TipoSucursal 
FROM DimBancoXML_Origen
WHERE Banco+Sucursal+TipoSucursal 
NOT IN
(SELECT Banco+Sucursal+TipoSucursal
FROM DimBancoXML)
GO
Eliminando 
Registros
repetidos en la carga
Lic. Nicolás Bocalandro
53
CREATE procedure ListarDimFecha
as
select 
IdFecha,
Fecha=fecha,
Año=datepart(yyyy, fecha),
Trimestre=datepart(quarter, fecha),
Mes=datepart(Month, fecha),
DiaDelAnio=datepart(dayofyear, fecha),
Dia=datepart(day, fecha),
Semana=datepart(week, fecha),
DiaDeLaSemana=datepart(weekday, fecha),
Hora=datepart(hour, fecha),
Minuto=datepart(minute, fecha)
from tiempoexcel
GO
Llenando la dimensión
Tiempo
Lic. Nicolás Bocalandro
54
Utilizando DTS
Lic. Nicolás Bocalandro
55
Utilizando ActiveX
Lic. Nicolás Bocalandro
56
Utilizando ActiveX
Lic. Nicolás Bocalandro
57
Utilizando ActiveX
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("CodLocalidad") = 
DTSSource("CodLocalidad")
DTSDestination("Localidad") = DTSSource("Localidad")
Main = DTSTransformStat_OK
End Function
Lic. Nicolás Bocalandro
58
Ejemplo de XML
Lic. Nicolás Bocalandro
59
CREATE procedure cargarxml
as
SET NOCOUNT ON
CREATE TABLE #tmpFileLines (rowID int IDENTITY, lineData nvarchar(255))
INSERT #tmpFileLines EXEC master.dbo.xp_cmdshell 'TYPE 
E:\DW\Datos\MulatOrg0901Envio000535.fmlr_disquete.xml'
DECLARE @strXMLText nvarchar(4000)
SELECT @strXMLText = 
CASE rowID WHEN 1 THEN 
ISNULL(RTRIM(lineData), '')
ELSE
@strXMLText + ISNULL(RTRIM(lineData), '')
END
FROM #tmpFileLines ORDER BY rowID ASC
Cargando un XML en
una base de datos
Lic. Nicolás Bocalandro
60
DROP TABLE #tmpFileLines
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @strXMLText 
INSERT INTO PROVINCIA 
SELECT *
FROM OPENXML(@hDOC, '/ROWSET', 2)
WITH 
(
CODPROVINCIA VARCHAR(4),
PROVINCIA VARCHAR(60),
)
EXEC sp_xml_removedocument @hdoc 
GO
Cargando un XML en
una base de datos
Lic. Nicolás Bocalandro
61
 Proceso de exploración y análisis - de manera automática o 
semiautomática - de los datos para obtener patrones 
significativos y reglas de negocio.
DATA MINING
Lic. Nicolás Bocalandro
62
 Las herramientas de Data Mining responden a preguntas 
que consumían demasiado tiempo para resolverse.
 Exploran las bases de datos en busca de patrones 
ocultos.
 Encuentran datos, generando conocimiento, que un 
experto no podría llegar a generar.
DATA MINING
Lic. Nicolás Bocalandro
63
 Data Mining deriva de las similitudes entre buscar 
información de negocios en grandes bases de datos y 
minar una montaña para encontrar una veta de metales 
valiosos. 
 Predicción automatizada de tendencias y comportamientos. 
 Descubrimiento automatizado de modelos previamente 
desconocidos.
DATA MINING
Lic. Nicolás Bocalandro
64
 Es un proceso centrado en el usuario que permite explorar 
datos, relaciones entre datos y tendencias, permitiendo 
mejorar la toma de decisiones.
 Incluye un proceso interactivo de acceso a los datos y el 
análisis de los mismos para obtener conclusiones.
BUSINESS INTELLIGENCE
Lic. Nicolás Bocalandro
65
KNOWLEDGE DEVELOPMENT (KD)
Lic. Nicolás Bocalandro
 Descubrimiento de conocimiento: Es practicamente 
el mismo concepto que el de Data Mining pero apunta 
más al descubrimiento de hipótesis o patrones ocultos, 
mientras que DM también abarca la exploración de 
hipótesis muy débiles.
66
APLICACIONES DE BI
Lic. Nicolás Bocalandro
 Marketing Directo.
 Venta cruzada.
 Análisis y pronósticos de venta.
 Análisis de la canasta de compras (MBA)
 Análisis de promociones (pre y postventa).
 Retención de clientes.
 Análisis de rentabilidad.
 Medición del valor del cliente.
 Centros de atención telefónica.
 Análisis de contratos de venta.
67
TERMINOLOGIA (1/3)
 Snapshot: Imagen instantánea de los datos en un tiempo 
dado.
 Modelo Multidimensional: Es una manera de organizar 
Hechos (Facts) de forma tal que estos puedan ser 
analizados desde diferentes perspectivas de negocios.
 Metadatos: Por definición es data sobre dato. En otras 
palabras la información sobre como es estructurada en el 
DW.
 Medidas:Son atributos numéricos de un hecho. Se 
localizan en la tabla de hechos de las bases de datos OLAP.
Lic. Nicolás Bocalandro
68
TERMINOLOGIA (2/3)
 ETL (Extracción, Transformación y Carga de datos):
Herramienta para volcar datos del sistema OLTP (o la
fuente de datos utilizada) al repositorio de datos (OLAP).
 DTS (Servicio de Transformación de Datos): En el
proceso el DTS valida, limpia, consolida y transforma los
datos cuando lo necesita. Utiliza funciones SQL y ActiveX.
 Data Mining: Técnicas de análisis de datos encaminadas a
obtener información oculta en un repositorio de datos.
 Agregación: Actividad de combinar datos desde múltiples
tablas para formar una unidad de información más
compleja, necesitada frecuentemente para responder
consultas del DW en forma más rápida y fácil.
Lic. Nicolás Bocalandro
69
TERMINOLOGIA (3/3)
 Snapshot: Imagen instantánea de los datos en un tiempo 
dado.
Lic. Nicolás Bocalandro
70
PREGUNTAS