Especificaciones de base de datos para Data Warehouse

15  Download (0)

Full text

(1)

Especificaciones de base de datos

para Data Warehouse

Referencia: AST-ESPEC-BaseDatosOracle10g_DW.doc

Autor: Entidad Pública Aragonesa de Servicios Telemáticos

Fecha de creación: 10/11/2008 Última actualización: 10/11/2008

Versión: v1.0 Clasificación: Restringido

Colección: Especificaciones técnicas para el desarrollo de aplicaciones para el Gobierno de Aragón.

Oracle 10g

(2)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 2 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es

Control del documento

Registro de cambios

Versión Fecha Autor Descripción

v1.0 10/11/2008 Alejandro Moreno y Fabián Salas

Primera versión del documento.

Revisores

Nombre Área

Natividad Porta Royo

Lista de distribución

Nombre Área

Todo el personal Aragonesa de Servicios Telemáticos Personal informático Gobierno de Aragón

(3)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 3 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es

Contenido

1. INTRODUCCIÓN ... 4 2. PROCEDIMIENTO GENERAL ... 5 2.1. ENTORNOS... 5 2.1.1. Desarrollo... 5 2.1.2. Preproducción ... 5 2.1.3. Producción ... 5

3. SISTEMA GESTOR DE BASE DE DATOS ... 6

4. COMPONENTES PARA INSTALACIÓN DE LA BASE DE DATOS... 7

5. ESQUEMAS DE BASE DE DATOS ... 8

6. ESPACIO FÍSICO ... 9

7. NOMENCLATURA... 10

7.1. TIPOS DE TABLA ... 10

7.2. REGLAS... 10

7.3. LONGITUD DE NOMBRES ... 10

7.4. CONVENIO DE NOMBRES (ESQUEMA DE DATOS)... 11

7.5. CONVENIO DE NOMBRES (ESQUEMA DE TRABAJO) ... 12

8. RECOMENDACIONES ... 14

8.1. CLAVE PRIMARIA GENERADA EN LAS DIMENSIONES ... 14

8.2. ÍNDICES EN TABLAS... 14 8.2.1. Claves ajenas... 14 8.2.2. Claves naturales ... 14 8.3. DIMENSIONES ORACLE ... 14 8.4. VISTAS MATERIALIZADAS ... 15 8.5. PARTICIONES... 15

(4)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 4 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es

1. Introducción

El presente documento describe las especificaciones de base de datos para el desarrollo de proyectos sobre la plataforma Data Warehouse / Business Intelligence – en adelante, DW/BI - para el Gobierno de Aragón.

Aragonesa de Servicios Telemáticos – en adelante, AST -, a través del responsable técnico que designe, proporcionará al proveedor externo el conjunto de especificaciones técnicas a tener en cuenta en el diseño físico de la base de datos y que a continuación se relacionan.

(5)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 5 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es

2. Procedimiento general

El proveedor realizará el desarrollo en sus instalaciones conforme a las especificaciones entregadas.

2.1. Entornos

2.1.1. Desarrollo

El Gobierno de Aragón proporcionará los entornos necesarios cuando se trate de proyectos realizados por su propio personal. En el caso de proyectos externos, los proveedores gestionarán sus entornos de desarrollo siguiendo las especificaciones marcadas por AST.

2.1.2. Preproducción

En este entorno, AST proporcionará, gestionará y validará todos los proyectos realizados, para comprobar que cumple con todos los requisitos y normas establecidas.

2.1.3. Producción

Una vez aceptado el proyecto, se procederá a su puesta en producción. Para ello, se utilizarán los mismos componentes y procedimientos de instalación que para preproducción, con la parametrización que corresponda. Se requerirá la participación del propio proveedor en las actividades de instalación, salvo que no se estime necesario.

(6)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 6 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es

3. Sistema Gestor de Base de Datos

El SGBD Relacional que da soporte a la plataforma DW/BI del Gobierno de Aragón es: Oracle Database 10g (10.2.0.4.0) Enterprise Edition –RAC en los entornos de preproducción y producción-.

(7)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 7 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es

4. Componentes para instalación de la base de

datos

Los componentes para la instalación de la base de datos se proporcionarán en procedimientos de Oracle Data Integrator. Deberán existir tanto procedimientos para la creación de objetos como para el borrado y vaciado (en caso necesario). Son deseables procedimientos ODI separados para:

Creación y borrado de tablas

Creación y borrado de claves ajenas (relaciones) Creación y borrado de índices

Creación y borrado de secuencias Creación y borrado de vistas Otros

(8)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 8 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es

5. Esquemas de base de datos

Se trabajará con dos esquemas distintos por proyecto: uno para el almacenamiento de los datos finales y el otro para guardar todas aquellas estructuras necesarias para la realización de las cargas.

Los nombres de los esquemas a crear serán los siguientes:

<departamento o entidad>_DM_<nombre proyecto> <departamento o entidad>_ETL_<nombre proyecto>

En <departamento o entidad> figurarán las siglas del departamento o entidad para el cual se realiza el trabajo. Estas siglas serán caracteres alfabéticos en mayúsculas cuyo nombre será indicado por AST.

Para diferenciar entre esquema de datos y esquema de trabajo utilizaremos las siglas DM (Data Mart) y ETL (Extracción, transformación y carga).

En <nombre proyecto>, se indicará el nombre determinado por AST para cada proyecto, la misma convención que seguimos para las aplicaciones; es decir, como si fuera una aplicación más, que daremos de alta en el inventario de aplicaciones (INVAST).

Ejemplos:

Censo de población y viviendas 2001 del Instituto Aragonés de Estadística.

IAEST_DM_CENSO (datos)

IAEST_ETL_CENSO (trabajo)

Nóminas del Servicio Aragonés de Salud.

SALUD_DM_NOMINA (datos)

(9)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 9 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es

6. Espacio físico

Se especificará el tablespace o tablespaces necesarios para el almacenamiento de los objetos de base de datos, con la estimación del espacio necesario para cada uno de ellos en cada uno de los entornos –desarrollo, preproducción y producción-. Por lo general, para el esquema de datos finales se utilizarán un tablespace para tablas, y otro para índices y vistas materializadas, y para el esquema de trabajo un único tablespace. Los nombres de los tablespaces se describen en el apartado siguiente.

/* Por defecto, por motivos de rendimiento, el modo de creación de los tablespaces será el de gestión local con asignación automática de extensiones (MANAGEMENT LOCAL AUTOALLOCATE). Por restricciones de Oracle, cuando existan tablas con campos de tipo LOB el modo de creación de los tablespaces será el de gestión local con asignación uniforme de extensiones (MANAGEMENT LOCAL UNIFORM). Por tanto, para las tablas con campos de tipo LOB se creará un tablespace separado. */ -- A consultar con el administrador Oracle

La estimación del espacio para el entorno de producción se realizará conforme a la actividad prevista (volumen de registros) en el periodo de 2 años.

(10)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 10 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es

7. Nomenclatura

7.1. Tipos de tabla

Existen cuatro tipos de tabla:

1. Tablas de dimensión. Aparecen en los esquemas de datos y son las que forman

las dimensiones del modelo en estrella.

2. Tablas de hechos. Aparecen en los esquemas de datos y son las tablas centrales

del modelo en estrella.

3. Tablas de carga. Son aquellas tablas creadas en el esquema de trabajo para

almacenar los datos de forma temporal. Existen dos tipos:

3.1. Temporales totales. Son aquellas en las que antes de cada carga se eliminan los datos existentes.

3.2. Temporales parciales. Son aquellas en las que los datos se mantienen durante un tiempo determinado.

4. Tablas ODI. Son las creadas por Oracle Data Integrator y empiezan por los

siguientes prefijos: E$_, C$_, I$_, J$_.

7.2. Reglas

El presente convenio sigue unas reglas fáciles de explicar y de aplicar:

Todo objeto que depende jerárquicamente de otro debe ir precedido del nombre del objeto del que depende (p.e. índices sobre tablas, restricciones sobre tablas, etc.). Esta regla da una visión de la jerarquía de objetos a nivel de nombres y proporciona un método rápido de nombrar objetos que no son utilizados en sentencias DML.

Todo objeto debe incorporar en el nombre el tipo del objeto (p.e. v para vistas, seq para secuencias, etc.). Como excepción a esta regla están las tablas, por ser el objeto principal de la base de datos, y los tablespaces, que se definen de manera fija. Esta regla evita dedicar tiempo en pensar nombres distintos para objetos de distinto tipo dentro de la misma aplicación o esquema (mismo namespace).

Todo objeto debe terminar con una cadena de caracteres que debe definirse libremente para distinguir los objetos del mismo tipo y aplicación. Como excepción a esta regla están los tablespaces, que se definen de manera fija.

7.3. Longitud de nombres

La longitud total de un nombre de objeto no puede exceder el límite Oracle de 30 caracteres. Este límite debe reducirse para el caso de las tablas, ya que el nombre de éstas sirve de base para la definición de otros nombres de objetos dependientes.

(11)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 11 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es

7.4. Convenio de nombres (esquema de datos)

Este es el convenio de nombres utilizado para los esquemas de datos:

Tipo de Objeto Nombre Comentario

Tablespace

<esquema>_datos Para las tablas de uso normal

<esquema>_indices Para los índices y vistas

materializadas

Tabla Tablas

Tabla de dimensión DIM_<id> Tablas de dimensión Tabla de hechos FACT_<id> Tablas de hechos Vista <vista>_v Vista

Secuencia < tabla>_SEQ Secuencia para la dimensión Dependencias sobre Tablas

Clave Primaria <tabla>_PK Clave primaria

Clave Natural <tabla>_NK Clave natural de las dimensiones Clave Ajena <tabla>_FK_<id> Clave ajena

Restricción Única <tabla>_UK_<id> Restricciones únicas alternativas Restricción Check <tabla>_CK_<id> Restricciones de control

Índice <tabla>_IX_<id> Índices

Convenio de nombres

Ejemplo: Censo de población y viviendas 2001 del Instituto Aragonés de Estadística. (Esquema de datos) Tablespace: IAEST_DM_CENSO_DATOS IAEST_DM_CENSO_INDICES Tablas: DIM_LOCALIZACION FACT_POBLACION Secuencias: DIM_LOCALIZACION_SEQ Clave Primaria:

(12)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 12 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es Clave Natural: DIM_LOCALIZACION_NK Clave Ajena: FACT_POBLACION_FK_LOCALIZACION Indices: DIM_PERSONA_IX_EDAD FACT_POBLACION_IX_LOCALIZACION

7.5. Convenio de nombres (esquema de trabajo)

Este es el convenio de nombres utilizado para los esquemas de trabajo:

Tipo de Objeto Nombre Comentario

Tablespace <esquema>_datos Único tablespace

Tabla Tabla

Tablas temporales totales <TMPT>_<id> Tablas temporales cuyos datos son borrados antes de cada carga Tablas temporales parciales <TMPP>_<id> Tablas temporales cuyos datos se

mantienen durante un tiempo Tablas ODI Error E$_<tabla> Son tablas utilizadas y gestionadas

por ODI para almacenar los errores Tablas ODI Carga C$_<tabla><num> Son las tablas utilizadas y

gestionadas por ODI para obtener la información de las fuentes de datos Tablas ODI Integración I$_<tabla> Son las tablas utilizadas y

gestionadas por ODI para integrar la información

Vista <vista>_v Vista

Secuencia <tabla>_seq Secuencia para la dimensión Dependencias sobre Tablas

Clave Primaria <tabla>_pk Clave primaria Clave Ajena <tabla>_fk_<id> Clave ajena

Restricción Única <tabla>_uk_<id> Restricciones únicas alternativas Restricción Check <tabla>_ck_<id> Restricciones de control

Índice <tabla>_ix_<id> Índices

Convenio de nombres

(13)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 13 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es AST_ETL_PORTAL_DATOS Tablas: TMPT_ACCESOS_PORTAL_ARAGON TMPT_ ACCESOS_PORTAL_INAEM E$_FACT_ACCESOS C$_FACT_ACCESOS1 I$_FACT_ACCESOS

(14)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 14 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es

8. Recomendaciones

Se incluye aquí una serie de recomendaciones para validar un esquema de base de datos antes de su creación.

8.1. Clave primaria generada en las dimensiones

Para las tablas de dimensiones, se deberá generar automáticamente una clave primaria de tipo numérico que será la utilizada para enlazar con la tabla de hechos.

8.2. Índices en tablas

La creación de índices útiles permite mejorar la velocidad de algunas consultas, evitando los escaneos totales (full scan) que se producen si una tabla no tiene asociado ningún índice. Estos escaneos son poco aconsejables en tablas de cierto tamaño. Sin embargo, cuando se realizan órdenes de INSERT y UPDATE contra tablas que tienen índices asociados, éstos son trastocados, es decir, Oracle realiza un mantenimiento implícito de los índices. Por tanto, cuantos más índices tiene una tabla mayor será el número de acciones implícitas.

El modo de acceso a los datos en el Data Warehouse es solamente de lectura, excepto en el momento de provisión de datos que deberá tener lugar en períodos y horarios donde no se produzcan muchas consultas a la base de datos. Por tanto, podrán crearse tantos índices como se consideren necesarios, siempre y cuando exista una justificación para ellos, teniendo en cuenta que las cargas de datos durarán más tiempo cuando más índices existan. Deberá seguirse las recomendaciones de Oracle acerca de los tipos de índices a crear (btree, bitmap).

8.2.1. Claves ajenas

Sería recomendable la creación de índices para todas las claves ajenas encontradas en las tablas, especialmente en la tabla de hechos.

8.2.2. Claves naturales

Sería recomendable, también, la creación de un índice para las claves naturales existentes en las diferentes dimensiones.

8.3. Dimensiones Oracle

Si se requiere, sería útil la creación de dimensiones en la base de datos con el comando CREATE DIMENSION, que ayuda al SQLAccess Advisor a recomendar las vistas materializadas necesarias.

(15)

Ref.: AST-ESPEC-BaseDatosOracle10g_DW.doc Fecha: 10.12.2008

Versión: v1.0 Pág. 15 de 15

Entidad Pública Aragonesa de Servicios Telemáticos Pº María Agustín 25-27-29 50004 ZARAGOZA Tel. 976 71 4495 – Fax. 976 71 4395 www.aragon.es

8.4. Vistas materializadas

En determinados proyectos, debido a la cantidad de información a buscar, se puede plantear la creación de algunas vistas materializadas que ayuden a mejorar las prestaciones en la consulta de gran cantidad de datos.

Deberá siempre justificarse su creación, y no excederse en el número de vistas materializadas a crear ya que si se crearán demasiadas, ralentizaría el tiempo de ejecución de la consulta.

8.5. Particiones

En el caso de que las tablas tengan un tamaño considerable, o en previsión de que puedan crecer bastante, se podrían crear particiones sobre algunas tablas: tablas de hechos, y, en caso más excepcional, tablas de dimensiones enormes. Lo recomendable para dividir las tablas de hechos sería crear particiones por grandes períodos de tiempo (Ejemplo: trimestre, año).

Su necesidad deberá, por supuesto, justificarse.

8.6. Análisis de las tablas e índices

Una vez realizada la carga de las tablas de dimensiones y hechos, es muy recomendable realizar un análisis de las mismas para que el optimizador de Oracle pueda buscar el mejor camino para responder a las consultas.

exec DBMS_UTILITY.ANALYZE_SCHEMA('IAEST_DM_CENSO','ESTIMATE', estimate_percent => 25);

Figure

Updating...

References