1. El almacenamiento multidimensional y estructura interna
1.1 Modelo multidimensional
Una vez que la información ha sido consolidada y transferida al Data Mart (donde reside el esquema STAR o SNOWFLAKE), puede ser enviada a repositorios multidimensionales. SQL Server 2014 implementa el almacenamiento multidimensional a través de los Servicios de Análisis (SQL Server Analysis Services, o SSAS).
Los repositorios multidimensionales se denominan cubos. Estos permiten efectuar consultas muy complejas sobre grandes conjuntos de datos, con alto rendimiento y velocidad. Los datos se encuentran totalizados y precalculados, lo cual permite obtener un tiempo de respuesta reducido para consultas que implican cálculos sobre grandes volúmenes de información.
Desde SQL Server 2005 Analysis Services la única herramienta para modelos de datos BI es UDM (Unified Dimensional Model). Actualmente, esta herramienta ha sido modificada desde la versión SQL Server 2012. Con Business Inteligence Semantic Model (BISM).
El modelo semántico de Analysis services como único modelo utilizado para reportes, datos analíticos, “scorecards”, “dashboards”, experiencias de usuario (personal, equipo y organizacional).
La arquitectura BISM presenta un modelo para herramientas de cliente, con dos maneras de construirlo: Tabular y Multidimensional
Modelos de almacenamiento
- OLAP Multidimensional (MOLAP)
- Los datos se encuentran almacenados en una estructura multidimensional.
- Para optimizar los tiempos de respuesta, el resumen de la información es usualmente calculado por adelantado. Estos valores precalculados o agregaciones son la base de las ganancias de desempeño de este sistema.
- Está compuesta por un motor OLAP en un servidor dedicado.
- Permitir mejoras enormes en la performance con respecto a los tiempos de consulta logrando un análisis más fácil y rápido
- Está diseñada para almacenar poco volumen de datos lo cual generará una limitación respecto al tamaño de BD. Los usuarios que requieren de datos agregados y sumarizados necesitarán de una arquitectura MOLAP
- OLAP Relacional (ROLAP)
- Es un sistema en el cual los datos se encuentran almacenados en una base de datos relacional. Típicamente, los datos son detallados, evitando las agregaciones y las tablas se encuentran normalizadas.
- Está compuesta por un servidor de base de datos Relacional y un motor OLAP en un servidor dedicado.
- Está diseñada para almacenar gran volumen de datos, debido a que su almacenamiento es relacional. Los usuarios que requieren de un alto nivel de detalle necesitarán de una arquitectura ROLAP.
- OLAP Híbrido (HOLAP)
- Mantiene los registros detallados en la BD relacional, mientras que los datos resumidos o agregados se almacenan en una base de datos multidimensional separada.
- Este método de almacenamiento es una combinación de los dos anteriores e intenta rescatar lo mejor de cada uno.
- Está compuesta por un servidor de base de datos Relacional y un motor OLAP en un servidor dedicado.
- Permite que el espacio físico, la performance de las consultas y el procesamiento sea menor que MOLAP y mayor que ROLAP. Los analistas que requieran ambos tipos de análisis necesitarán una arquitectura HOLAP.
1.2 Estructura interna
Un cubo se define a partir de una tabla de hechos y una o varias tablas de dimensión. Las medidas (measures) de la tabla de hechos se convertirán en las medidas del cubo. Las dimensiones del cubo se definen a partir de las tablas de dimensión.
Por ejemplo, un cubo de ventas puede tener las medidas Precio_Producto y Costo_Producto, provenientes de la tabla de hechos Sales_Fact. Estas medidas se cruzan con las dimensiones Almacén, Línea_de_Producto y Año_Fiscal. Las consultas efectuadas sobre este cubo permitirían obtener la evolución del precio de un producto a lo largo del tiempo, por almacén y línea de producto.
Cada dimensión en un cubo tiene uno o varios niveles (levels). Los niveles representan agrupaciones jerárquicas al interior de una dimensión.
Por ejemplo, la dimensión Ubicación_Geográfica puede tener los niveles Continente, Región, País y Ciudad. Los continentes contienen regiones, las regiones contienen países, y los países ciudades. La existencia de niveles permite obtener rápidamente resultados totalizados de información; de esta manera, los cuatro niveles mencionados en el ejemplo anterior permiten obtener las ventas totalizadas por Continente, Región, País y Ciudad.
2. Estructura y Componentes Básicos de un Cubo
La figura superior muestra una representación de la estructura de un cubo.
Los componentes básicos de un cubo son las dimensiones (dimensions), los miembros (members), (estos dos componentes provienen de las tablas dimensionales) y las celdas (cells) (que provienen de la tabla de hechos).
2.1 Dimensiones (dimensions)
Cada dimensión en un cubo representa una perspectiva desde la cual los usuarios consultan y analizan los datos. Desde un punto de vista gráfico, se puede afirmar que las dimensiones corresponden con los ejes del cubo. Por ejemplo, se desea un reporte que nos muestre las ventas por Producto, Tiempo y Ubicación, ellas representan las dimensiones de un cubo. En la figura superior, podemos ver la representación gráfica de las dimensiones Productos (en el eje vertical), Tiempo (en el eje horizontal) y Ubicación (en el eje que atraviesa perpendicularmente esta página).
2.2 Niveles (levels)
Los niveles representan los grados de sumarización de una dimensión. Por ejemplo, la dimensión Tiempo puede contener los niveles Año, Mes y Día.
2.3 Miembros (members)
Cada ocurrencia particular en una dimensión está representada por un miembro. Por ejemplo, en la figura los miembros de la dimensión Productos son Uvas, Fresas, Melones, Manzanas y Peras. Los miembros de la dimensión Ubicación son Loreto, Lima y Cuzco.
2.4 Celdas (cells)
Una celda está definida por una intersección de las dimensiones del cubo. En la figura, la celda de color blanco representa la intersección de los miembros T4 (Tiempo), Fresas (Productos) y Cuzco (Ubicación).
3. Creación de Cubos a través del SQL Server Data Tools
En el capítulo anterior, se utilizó el SQL Server Data Tools para crear proyectos de Integration Services. Con el mismo entorno de desarrollo, se pueden crear proyectos de Analisys Services, a través de los cuales se define la estructura de los cubos en una solución de Business Intelligence.
En versiones anteriores a SQL Server 2012; la creación de bases de datos de análisis requiere una conexión permanente hacia el origen de datos donde residen los esquemas de datos (STAR o SNOWFLAKE). En SQL Server 2012 en adelante, los Data Sources Views (vistas de origen de datos) almacenan localmente información sobre la estructura de las tablas del data mart. De esta manera, el desarrollador puede continuar trabajando sobre el cubo sin necesidad de una conexión con el origen de datos.
El desarrollo de una solución de Business Intelligence a través del entorno de desarrollo de Microsoft Visual Studio .Net permite un desarrollo más rápido y una mejor definición de los componentes del cubo.
Para definir cubos en el SQL Server Data Tools, se debe crear un proyecto de tipo Analysis Services Project.
A continuación se describen algunos de los componentes del entorno de diseño:
- Panel de diseño: Muestra gráficamente los componentes que constituyen el cubo y las dimensiones.
- Solution Explorer Window (explorador de soluciones): Muestra, en una vista de árbol, a los elementos que componen el proyecto: orígenes de datos (Data Sources), vistas de origen de datos (Data Source Views), cubos, dimensiones, modelos de minería de datos, etc.
- Properties Window (ventana de propiedades): Muestra las propiedades configurables del cubo, las dimensiones y sus componentes.
3.1 Data Sources
Un origen de datos de Microsoft SQL Server Analysis Services representa una conexión a un origen de datos y contiene la cadena de conexión que define el modo en que Analysis Services se conecta a un almacén de datos físico mediante un proveedor Microsoft .NET Framework administrado o un proveedor OLE DB nativo.
La cadena de conexión contiene información sobre el nombre del servidor, la base de datos, la seguridad, el tiempo de espera y otra relacionada con la conexión.
Analysis Services admite directamente muchos orígenes de datos. Entre los orígenes de datos admitidos se incluyen las bases de datos de Microsoft SQL Server y las creadas mediante otros productos, como Oracle, DB2 y Teradata.
3.2 Data Sources View
Una vista de origen de datos contiene el modelo lógico del esquema (STAR o SNOWFLAKE) que utilizan los objetos de las bases de datos de Analysis Services: cubos, dimensiones y estructuras de minería de datos. Una vista de origen de datos es la definición de metadatos, almacenada en formato XML.
Una vista de origen de datos:
- Se genera a partir de los orígenes de datos ya definidos en la base de datos conformados por el modelo Start o Snowflake.
- Contiene los metadatos que representan objetos seleccionados a partir de uno o más orígenes de datos.
- Se puede generar con uno o más orígenes de datos, lo que permite definir objetos OLAP y de minería de datos que integren datos de varios orígenes.
- Puede contener relaciones, claves principales, nombres de objeto, columnas calculadas y consultas que no están presentes en un origen de datos y que son independientes de los orígenes de datos físicos.
- Obtiene las relaciones de las tablas automáticamente a partir de las claves foráneas existentes en el origen de datos. Sin embargo el desarrollador puede definir nuevas relaciones directamente en el data source view.
- Dispone de la facilidad de colocar nombres amigables para las tablas y sus columnas.
- Puede incluir nuevas definiciones de columnas, escritas por el desarrollador a partir de expresiones de cálculo.
- Se almacenan de forma permanente en el proyecto de análisis; por tanto, permiten trabajar sin necesidad de una conexión en línea con las bases de datos de origen.
3.3 El Cube Wizard
El entorno de desarrollo de proyectos de análisis incluye un asistente para la definición de cubos.
Dicho asistente incluye dos opciones para la generación de cubos:
Build the cube using a data source:
Esta opción obtiene las definiciones del cubo a partir de la estructura existente en una base de datos, donde reside un esquema STAR o SNOWFLAKE. Si se selecciona la opción Auto build, el asistente analizará la estructura de las tablas, y detectará automáticamente las tablas de hechos y tablas de dimensión. Además, el asistente podrá determinar cuáles serán los niveles que se agregarán a las dimensiones. Si la opción Auto build no se selecciona, el desarrollador definirá manualmente la estructura del cubo.
Build the cube without using a data source:
Esta opción permite que el desarrollador defina la estructura del cubo sin necesidad de elegir un data source view. Posteriormente, a partir de la estructura propuesta por el desarrollador, SSAS generará las definiciones del STAR o SNOWFLAKE. SSAS provee dos plantillas de diseño: una de ellas es compatible con las características de la versión Enterprise Edition, y la otra con las opciones disponibles en la versión Standard Edition.
Cualquiera de las características y definiciones establecidas por el asistente pueden ser modificadas por el desarrollador en un momento posterior.
3.4 El Diseñador de Cubos
El diseñador de cubos permite establecer las propiedades del cubo y sus dimensiones. Provee una interfaz gráfica de fácil utilización, a través de la cual se puede personalizar el diseño de cada cubo en la solución de análisis. Este diseñador posee las siguientes pestañas, visibles en la parte superior de:
- Cube Structure: Permite efectuar cambios en la estructura del cubo: medidas, dimensiones, propiedades de agregación, etc.
- Dimension Usage: Permite definir las relaciones existentes entre el cubo y sus dimensiones.
- Calculations: Permite definir cálculos personalizados en el cubo a través de sentencias MDX (Multidimensional Expressions).
- KPIs: Permite definir Key Performance Indicators (KPIs) para establecer valores comparativos contra los cuales deben evaluarse las medidas.
- Actions: Permite definir las acciones que los usuarios pueden efectuar al navegar a través de la información del cubo.
- Partitions: Permite definir las particiones en que se almacena el cubo y los modos de almacenamiento de cada una de ellas.
- Perspectives: Permite definir perspectivas. Cada perspectiva constituye una vista parcial de la información, disponible para un grupo de usuarios.
- Translations: Permite traducir los nombres de los objetos en el cubo a múltiples idiomas.
- Browser: Permite observar la información almacenada en el cubo.
3.5 Deployment
Para ver los datos de las métricas y las dimensiones, debe implementar el proyecto en una instancia determinada de Analysis Services y luego procesar el cubo y sus dimensiones.
Al implementar un proyecto de Analysis Services se crean y definen objetos en una instancia de Analysis Services. Cuando se procesan los objetos en una instancia de Analysis Services (servidor de análisis), se copian los datos de los orígenes de datos en los objetos del cubo.
Durante la fase de procesamiento, el servidor de análisis se conecta con el data source origen donde residen las tablas de hechos y las tablas de dimensión, y extrae su información para poblar el cubo para luego almacenarlo en una base de datos de análisis.
