Introducción al desarrollo y optimización de modelos de datos en PowerPivot

Aunque en un artículo anterior de este mismo blog ya hemos abordado la creación de un modelo de datos en PowerPivot, en esta ocasión el objetivo consiste en hacerlo desde una perspectiva que tenga en cuenta aquellos aspectos relacionados con la optimización de las operaciones de carga de datos y posterior consulta del modelo desde una tabla dinámica de Excel, proporcionando algunas recomendaciones sencillas, pero efectivas, que nos ayuden a conseguir un mejor rendimiento y mayor velocidad de ejecución.

A partir de este momento, el resto de apartados muestra la manera de enfrentarnos al desarrollo de nuestro modelo de datos, describiendo algunos trucos y técnicas que pueden ayudarnos a mejorar sus tiempos de respuesta, redundando en una mayor agilidad en la toma de decisiones y rapidez en la construcción de informes que nos permitan conocer el estado de la organización.

 

Identificar el objetivo del modelo

Partiendo de la base de datos AdventureWorksDW de SQL Server, que utilizaremos como fuente de datos de ejemplo en la construcción de nuestro modelo de datos, supongamos que necesitamos calcular el importe que han supuesto las ventas de productos por categoría y periodos temporales (año de la fecha de factura), así como la cantidad de clientes que han realizado las compras. En este último caso, además de mostrar la cifra de clientes por el año de emisión de la factura, también debemos clasificarlos por su estado civil, contando a cada cliente una sola vez por año de facturación, es decir, aunque un determinado cliente, dentro de un mismo año, pueda haber realizado más de una compra, el recuento solamente tendrá en consideración una única vez a cada cliente.

 

Un pequeño ajuste en el origen de datos

Con el fin de poder demostrar más cómodamente en un próximo apartado la creación de relaciones en el modelo de PowerPivot, en primer lugar vamos a eliminar las claves ajenas existentes en la tabla FactInternetSales de la base de datos AdventureWorksDW. Desde SQL Server Management Studio seleccionaremos cada una de las claves y pulsaremos la tecla Supr, o también podemos usar el panel Object Explorer Details, donde seleccionaríamos todas las claves, eliminándolas en una sola operación.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_01

 

Tablas de datos y tablas de búsqueda

En un modelo de datos de PowerPivot, las tablas a partir de las cuales construimos las medidas (campos calculados) que utilizaremos para analizar el estado de nuestra organización reciben también el nombre de Tablas de Datos (Data Tables). Por otro lado, las tablas utilizadas para filtrar la información, mediante las cuales obtenemos diferentes resultados en función de los criterios de filtro aplicados, se denominan Tablas de Búsqueda (Lookup Tables). Ambos tipos de tablas se relacionan mediante columnas clave, siendo esta la característica que proporciona a PowerPivot su potencia de análisis al visualizar el modelo desde una tabla dinámica de Excel o mediante elementos gráficos de PowerView. Esta y otras muchas características interesantes se describen en una ficha de referencia rápida del blog powerpivot(pro) para algunos de los aspectos principales de esta tecnología.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_02

 

Para aquellos lectores con experiencia en el desarrollo de cubos de datos multidimensionales (cubos OLAP), los conceptos de Tabla de Datos y Búsqueda en PowerPivot se identifican respectivamente con los conceptos de Tabla de Hechos y Dimensión en el entorno multidimensional.

 

Selección de las tablas a importar

Según los requerimientos planteados en el apartado anterior, comenzaremos por el cálculo del importe de ventas anuales, identificando las tablas FactInternetSales y DimTime como aquellas necesarias para obtener este indicador. Así pues, crearemos un nuevo archivo Excel al que llamaremos VentasClientes.xlsx, y abriremos la ventana de administración de PowerPivot, situándonos en la pestaña del mismo nombre y haciendo clic en la opción Administrar.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_03

 

Una vez posicionados en dicha ventana iniciaremos el asistente para importar datos desde SQL Server.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_04

 

En el primer paso del asistente facilitaremos el nombre del servidor SQL Server al que nos conectaremos y la base de datos de la cual importaremos contenidos.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_05

 

En el siguiente paso elegiremos como modo de importación la selección en una lista de las tablas y vistas a importar.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_06

 

A continuación marcaremos las tablas a importar: FactInternetSales y DimTime,

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_07

 

Haciendo clic en Finalizar se realizará la importación de los datos, y tras aceptar la ventana final del asistente, obtendremos dos nuevas pestañas en la ventana de PowerPivot que identificarán a cada una de las tablas añadidas al modelo.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_08

 

Creación de medidas

A continuación crearemos, mediante la siguiente expresión en lenguaje DAX, la medida Ventas, encargada de sumar los importes de las facturas emitidas a los clientes, empleando la función SUM y la columna SalesAmount de la tabla de datos FactInternetSales.


Ventas:=SUM(FactInternetSales[SalesAmount])

Para crear esta medida (o campo calculado), en la ventana de PowerPivot seleccionaremos una celda libre en el área de medidas de la tabla FactInternetSales, escribiendo su fórmula en la barra de fórmulas, al igual  que hacemos en la ventana tradicional de Excel. Adicionalmente podemos aplicar un formato que en este caso servirá para mostrar el valor como numérico decimal con separador de miles.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_09

 

¡No sin mi relación!

Después de crear esta medida podemos vernos tentados de consultar las cifras de ventas por año. Haciendo clic en la opción Tabla dinámica del menú Inicio, añadiremos desde la ventana de PowerPivot una tabla dinámica a la hoja de cálculo actual o a una nueva.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_10

 

A continuación, en el panel Campos de la tabla dinámica, desplegaremos la lista de campos de la tabla DimTime, haciendo clic en la casilla del campo CalendarYear, que se situará en el área Filas (si no fuera así, arrastraremos el campo hasta dicha área). De igual forma procederemos para la tabla FactInternetSales, esta vez con la medida Ventas, que situaremos en el área Valores.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_11

 

Sin embargo, como podemos apreciar, el resultado no es el esperado, ya que todas las celdas de valores de la tabla dinámica muestran el mismo dato: el total de la medida Ventas (suma total de la columna SalesAmount del modelo).

¿Por qué esta medida no muestra la suma de los importes de las ventas con la cifra que le corresponde para cada año? Pues porque no hemos establecido una relación entre la columna OrderDateKey de la tabla FactInternetSales y TimeKey de la tabla DimTime. Este motivo hace que el motor de PowerPivot desconozca cuántas ventas se atribuyen a cada año, y por consiguiente se ve imposibilitado para realizar esta suma por periodos de tiempo. Hasta el propio Excel en el panel Campos de tabla dinámica nos advierte de tal circunstancia.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_12

 

Establecer relaciones entre las tablas

Para evitar el problema que acabamos de exponer y obtener valores consistentes por cada año de venta debemos crear una relación entre los campos mencionados en el apartado anterior.

Esto lo conseguiremos desde el menú Diseñar de la ventana de PowerPivot, utilizando las opciones Crear relación o Administrar relaciones, del grupo Relaciones.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_13

 

Alternativamente, también podemos establecer una relación mediante la opción Vista de diagrama del menú Inicio, en el grupo de opciones Ver; arrastrando, desde la tabla de datos, la columna origen de la relación hasta la columna destino en la tabla de búsqueda.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_14

 

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_15

 

Creada la relación, la tabla dinámica mostrará los valores de la medida Ventas correctamente por año de factura.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_16

 

¿Importar todas las tablas de una vez o hacerlo progresivamente?

Revisando el enunciado de requerimientos comprobaremos que aparte de las tablas que ya hemos incorporado al modelo vamos a necesitar añadir tablas adicionales (más concretamente DimCustomer y una combinación de las tablas DimProduct, DimProductCategory y DimProductSubcategory) para poder elaborar todos los indicadores solicitados. Entonces ¿por qué no hemos añadido dichas tablas en el proceso de importación anteriormente explicado?

La respuesta a esta pregunta se halla en una simple motivación pedagógica. Podemos importar todas las tablas que vaya a precisar nuestro modelo en un único paso, o bien hacerlo progresivamente, no hay una norma rígida al respecto. En nuestro caso nos hemos decantado por esta última opción, pero todo depende de las características del modelo a desarrollar. En ciertas ocasiones dispondremos de todas las fuentes de datos necesarias para cargarlas en PowerPivot, y habrá otras situaciones en las que el modelo irá evolucionando y no tendremos posibilidad, en sus fases iniciales, de incorporar todos los datos, pero sí los suficientes como para empezar a trabajar en ciertas parcelas de información. También es posible que aunque tengamos todos los datos a nuestra disposición, dada la complejidad del modelo a desarrollar, optemos por construirlo gradualmente.

Sea cual sea la estrategia de importación que sigamos, es muy importante tener presente que siempre debemos ceñirnos única y exclusivamente a los datos con los que realmente vayamos a trabajar.

El objetivo en este sentido es doble: por un lado optimizamos el tamaño del modelo y por otro agilizamos el rendimiento de las consultas que debe ejecutar el motor de PowerPivot. En el siguiente apartado detallamos un poco más este aspecto.

 

Importar solamente las tablas y columnas que vamos a utilizar

Al trabajar con la tabla dinámica de nuestro ejemplo nos habremos percatado de la numerosa cantidad de campos que cada una de las tablas del modelo pone a nuestra disposición.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_17

 

Esto, que a priori parece una ventaja (más campos, ergo más posibilidades de filtro, análisis, etc.) puede volverse en nuestra contra si no diseñamos adecuadamente la estrategia de carga de datos al modelo, dadas las particularidades que en este sentido presenta PowerPivot.

xVelocity (inicialmente denominado VertiPaq), el motor de PowerPivot (y también de los modelos tabulares de SQL Server Analysis Services), es una tecnología para el análisis de información con un motor de almacenamiento basado en columna, que gracias a una serie de algoritmos de compresión, y a la realización de sus operaciones de manipulación de datos en memoria, logra excelentes rendimientos en el tratamiento de datos de tamaños muy variados (desde unos pocos miles hasta varios millones de registros).

Sin embargo, si queremos llevar a buen puerto el desarrollo de nuestro modelo, debemos analizar cuidadosamente qué contenidos de los orígenes de datos necesitamos importar, así como su cantidad. Esta cuestión se hace, si cabe, más relevante cuando se trata de la tabla de datos, puesto que habitualmente este tipo de tabla contiene, con diferencia, un número mayor de registros y columnas que las tablas de búsqueda.

En cualquier caso, se trate de tablas de datos o búsqueda, como recomendación general, debemos seleccionar exclusivamente aquellas columnas que vayan a tener una participación activa en el análisis de la información del modelo. Esto incluye a las columnas (en tablas de búsqueda) que el usuario utilice para filtrar los resultados de una tabla dinámica, gráfico de PowerView u objeto de otro tipo con capacidad de conectarse al modelo para presentar su información; columnas de campos clave (en tablas de datos y búsqueda) empleadas en el establecimiento de relaciones; columnas (en tablas de datos) que intervengan en fórmulas para la creación de medidas, etc.

Dicho esto, los factores más importantes que debemos tener en cuenta son la cantidad de columnas a trasladar al modelo, así como el grado de variabilidad de valores de cada columna, es decir, la cantidad de valores distintos contenidos en una columna, y el número de registros en los que está presente cada valor. Recordemos que uno de los aspectos destacados de xVelocity es su nivel de compresión, por lo que a menor frecuencia de valores en una columna, mayor ratio de compresión y optimización en la velocidad de las consultas.

Analicemos este aspecto sobre FactInternetSales. Se trata de una tabla que contiene 60398 registros, por lo que si en la fase de importación al modelo de esta tabla sólo traspasamos las columnas ProductKey y OrderDateKey, el impacto sobre el rendimiento del motor de PowerPivot será mínimo, ya que dichas columnas tienen 158 y 1124 valores distintos respectivamente en la tabla. Dicho impacto irá en aumento conforme vayamos agregando columnas con una mayor cantidad de valores diferentes, como pueda ser CustomerKey (18484 valores), y aunque en esta tabla no existe, una posible columna compuesta por la combinación de SalesOrderNumber y SalesOrderLineNumber sería la que más negativamente afectaría al rendimiento del proceso de carga de datos, ya que arrojaría una cantidad de 60398 valores distintos posibles, esto es, la misma cantidad de registros que tiene la tabla.

No obstante, en el contexto de la base de datos AdventureWorksDW, donde la tabla de mayor tamaño contiene una cantidad que ronda los 64000 registros, el hecho de cargar cualquiera de sus tablas al completo en un modelo de PowerPivot no supondría una merma apreciable en el rendimiento, ni penalización en la ejecución de las consultas. Sin embargo, como es conveniente adquirir buenos hábitos, independientemente del volumen de la fuente de datos a emplear, vamos a seguir las pautas recomendadas en este apartado, empezando por modificar el modelo, tomando exclusivamente las columnas de las tablas que van a intervenir en los cálculos de medidas y filtros.

 

Recomponer el proceso de importación de datos al modelo

En su estado actual, el modelo nos permite visualizar la cifra de ventas por cada año de facturación. Analizando los elementos de la base de datos que necesitamos realmente para obtener estos resultados, podemos deducir que de la tabla FactInternetSales únicamente precisamos las columnas SalesAmount y OrderDateKey, mientras que de la tabla DimTime serían las columnas TimeKey y CalendarYear, por lo que vamos a proceder a “adelgazar” nuestro modelo con unos sencillos ajustes.

No se trata de eliminar el modelo por completo y crearlo nuevamente a partir de cero, sino que nos posicionaremos en la ventana de PowerPivot, y en la pestaña correspondiente a la tabla FactInternetSales, elegiremos la opción Propiedades de Tabla del menú Diseñar. En dicha ventana de propiedades desmarcaremos todas las casillas de selección de columna excepto SalesAmount y OrderDateKey, haciendo clic en Guardar para mantener los cambios.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_18

 

Procederemos de la misma manera con la ventana de propiedades de la tabla DimTime, desmarcando todas las columnas salvo TimeKey y CalendarYear. Al retornar a la tabla dinámica que estamos usando para analizar el modelo, comprobaremos que las columnas disponibles se han reducido en consonancia con los cambios recién aplicados.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_19

 

Como ya hemos explicado anteriormente, la disminución en el número de columnas también trae como beneficio una reducción en el tamaño del archivo Excel que alberga el modelo. Aunque en este ejemplo no experimentaremos una ganancia de espacio remarcable, cuando trabajemos con fuentes de datos de mayor magnitud sí que lo apreciaremos en su justa medida.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_20

 

Diseño en estrella mejor que en copo de nieve

El siguiente paso que daremos en la construcción del modelo consistirá en añadir la tabla de búsqueda que nos permita analizar las ventas por la categoría de los productos vendidos.

Revisando entre las tablas de la base de datos elegiremos la columna EnglishProductCategoryName, perteneciente a la tabla DimProductCategory, que representa al literal de categoría que necesitamos. Por otro lado, la columna ProductKey de la tabla FactInternetSales contiene el código del producto vendido. Sin embargo, no existe una conexión directa entre ambas tablas, siendo necesario “recorrer” dos tablas intermedias adicionales: DimProduct y DimProductSubcategory, para poder obtener convenientemente el dato que corresponde a la categoría del producto; dicho de otro modo, no existe una única tabla de búsqueda, sino que la información se encuentra repartida entre varias.

En entornos de almacenamiento de datos (Data Warehousing) e inteligencia de negocio como PowerPivot, SQL Server Analysis Services (Multidimensional o Tabular), etc., esta situación puede resolverse implementando en el diseño del modelo uno de los dos siguientes tipos de esquema: copo de nieve (snowflake schema) o estrella (star schema).

Mediante un esquema en copo de nieve se toman las tablas implicadas, y desde la tabla de datos hasta la última tabla de búsqueda se crea una cadena de relaciones entre las columnas clave. En el siguiente diagrama vemos cómo quedaría un hipotético modelo en el que, además de relaciones simples desde la tabla de datos a una de búsqueda, tenemos dos casos de relaciones con más de una tabla de búsqueda relacionada.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_21

 

Por otro lado, dentro de un esquema en estrella se combinan, en una única tabla, las tablas independientes que representan una entidad informativa del modelo, la cual relacionaremos con la tabla de datos. Este será el tipo de esquema que implementaremos en nuestro ejemplo.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_22

 

Para conseguir esto con las tablas DimProduct, DimProductSubcategory y DimProductCategory, prepararemos previamente en SQL Server la siguiente sentencia, encargada de la combinación de los datos.


SELECT
p.ProductKey
,pc.EnglishProductCategoryName
FROM DimProduct AS p
INNER JOIN DimProductSubcategory AS ps
ON p.ProductSubcategoryKey = ps. ProductSubcategoryKey
INNER JOIN DimProductCategory AS pc
ON ps.ProductCategoryKey = pc. ProductCategoryKey

 

A continuación ejecutaremos el asistente de importación de datos de PowerPivot, eligiendo el uso de una consulta en el paso Elegir cómo importar los datos. Escribiremos la anterior sentencia, y ello dará como resultado en el modelo una nueva tabla que llamaremos CategoriaProducto.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_23

 

Para poder relacionar la nueva tabla CategoriaProducto con FactInternetSales, abriremos la ventana de propiedades de esta última y marcaremos la casilla de la columna ProductKey para incorporarla al conjunto de columnas de la tabla que intervienen en el modelo.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_24

 

Tras esta operación relacionaremos las columnas ProductKey en ambas tablas y volveremos a la tabla dinámica, donde ya tendremos disponible el campo EnglishProductCategoryName, que situaremos en el área de columnas. En el caso de que no aparecieran los nuevos campos agregados al modelo, haremos clic en la opción TODOS, dentro del panel Campos de tabla dinámica.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_25

 

Aunque el esquema de estrella pueda producir una tabla de búsqueda más voluminosa en cuanto a cantidad de columnas, se recomienda su empleo en detrimento de copo de nieve, ya que en xVelocity proporciona un mejor rendimiento. Donde sí que debemos intentar mantener en todo lo posible un número reducido de columnas es en la tabla de datos, puesto que suele tener una elevada cantidad de filas, y este aspecto también influye en el rendimiento del motor.

Otro rasgo que igualmente puede afectar a las prestaciones del modelo, sobre todo con fuentes de datos muy grandes, reside en la cantidad de RAM y la potencia del procesador a nuestra disposición. En la medida de lo posible debemos intentar trabajar con al menos 4 GB de RAM y las versiones de 64 bits del sistema operativo y Excel, o bien SQL Server Analysis Services Tabular, en el caso de que utilicemos SQL Server para el desarrollo de los modelos.

 

Contando clientes

Seguidamente crearemos la medida NumClientes, también descrita en el apartado de requerimientos del modelo, y que consiste en un recuento del número de clientes distintos que han realizado compras.

Para ello traeremos a la tabla FactInternetSales del modelo la columna CustomerKey desde la tabla del mismo nombre del origen de datos (de forma idéntica a la explicada en el apartado anterior para la columna ProductKey), escribiendo a continuación la siguiente fórmula DAX, en la que utilizaremos la función DISTINCTCOUNT, que se encargará de contar los valores únicos del campo CustomerKey.


NumClientes:=DISTINCTCOUNT(FactInternetSales[CustomerKey])

 

Ahora volveremos de nuevo a la tabla dinámica para observar las cifras resultantes de esta nueva métrica.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_26

 

Columnas calculadas. Preferiblemente en el origen de datos

Pero recordemos que las cifras de la medida NumClientes debemos analizarlas a través del estado civil de dichos clientes. Por tal motivo necesitamos traer al modelo la tabla DimCustomer, y de dicha tabla, las columnas CustomerKey y MaritalStatus. De esta última columna, sus valores S y M corresponden a soltero (Single) y casado (Married) respectivamente. Una vez importados estos datos, relacionaremos esta tabla con FactInternetSales por la columna CustomerKey, y analizaremos la información en la tabla dinámica como muestra la siguiente figura.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_27

 

Sin embargo, creo que el lector estará de acuerdo en que los valores S y M del campo MaritalStatus no resultan muy adecuados para interpretar el estado civil de los clientes, por lo que debemos buscar un medio que nos permita visualizar un literal más apropiado.

Podemos solucionar este problema creando una columna calculada en la ventana de PowerPivot. Haciendo clic en la primera columna vacía disponible en la tabla, escribiremos la siguiente expresión DAX en el recuadro de fórmulas (el nombre de la columna será EstadoCivil).


=IF(DimCustomer[MaritalStatus]="S";"Soltero";"Casado")

 

Empleando la función IF comprobamos el valor de la columna MaritalStatus, generando el literal adecuado en cada caso.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_28

 

Como resultado de esta nueva columna creada dinámicamente mostraremos un literal más legible en la tabla dinámica.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_29

 

No obstante, siempre que sea posible, en lugar de crear la columna calculada en el modelo se recomienda considerar si la misma puede ser creada mediante alguna otra técnica, ya que este tipo de columnas introducen un potencial factor de penalización, puesto que necesitan almacenamiento extra.

Como demostración de lo que acabamos de exponer, eliminaremos del modelo la tabla DimCustomer y repetiremos la importación de la misma usando la siguiente consulta  SQL, que contendrá una expresión para crear dinámicamente la columna MaritalStatus. Recuerde el lector que deberemos volver a relacionar esta tabla con la tabla de datos del modelo.


SELECT
CustomerKey,
CASE
  WHEN MaritalStatus = 'S' THEN 'Soltero'
  WHEN MaritalStatus = 'M' THEN 'Casado'
END AS MaritalStatus
FROM DimCustomer

 

La visualización de los datos en la tabla dinámica no se verá afectada salvo que el campo volverá a llamarse MaritalStatus.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_30

 

Ocultar columnas en herramientas cliente

De todas las columnas repartidas entre las tablas de un modelo de datos habrá un subconjunto que emplearemos en la tabla dinámica para analizar la información por filas, columnas o como filtro; pero también existirá otro grupo de columnas que no tendrán una participación visual, ya sea porque se utilicen como elementos de cálculo para medidas, como relaciones entre tablas, etc.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_31

 

Este tipo de columna, utilizado para operaciones internas del modelo, no tiene mucho sentido que sea puesta a disposición del usuario de la tabla dinámica, ya que no aporta funcionalidad analítica. Por lo tanto resulta más oportuno ocultarlas desde la ventana de PowerPivot haciendo clic derecho en el título de la columna, y eligiendo la opción Ocultar en herramientas cliente.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_32

 

De esta forma conseguiremos en la tabla dinámica una visualización mucho más “limpia” de los campos realmente útiles a efectos de análisis.

IntroduccionDesarrolloOptimizacionModelosDatosPowerPivot_33

 

Conclusiones

Y después de esta última mejora sobre el modelo de datos llegamos al final del artículo, en el que además de realizar una introducción a la creación de modelos de datos en PowerPivot, hemos descrito algunas técnicas de optimización que pueden ayudarnos a mejorar el rendimiento de nuestros modelos. Espero que os resulte de utilidad.

Tratamiento de datos duplicados en SQL Server (y 2)

Tal y como apuntábamos al finalizar la primera parte del artículo, en esta segunda entrega expondremos algunas técnicas adicionales para la manipulación de datos duplicados, consistentes en el acceso a valores situados en la primera y última fila de las particiones que constituyen nuestros conjuntos de resultados.

Pero antes de proseguir, puesto que en el último ejemplo de la anterior entrega realizábamos un borrado de filas en la tabla Ventas, tendremos que volver a ejecutar la sentencia de creación e inserción de datos en dicha tabla, que podemos encontrar al comienzo de la primera parte de este artículo.

 

Encontrar la primera fila en un conjunto de datos duplicados

Si necesitamos obtener de un conjunto de resultados con datos repetidos el valor de una columna situada en la primera fila podemos emplear la función FIRST_VALUE. Aunque es cierto que lograríamos el mismo efecto mediante el uso de TOP 1, FIRST_VALUE, al ser una función de ventana, presenta como ventaja adicional la definición de particiones mediante la cláusula OVER (al igual que ROW_NUMBER), con lo cual obtendremos el primer valor de cada partición. Veamos este aspecto de forma práctica.

SELECT *, 
FIRST_VALUE(LineTotal) 
    OVER(PARTITION BY SalesOrderNumber ORDER BY ProductID) AS PrimerValor
FROM Ventas
WHERE SalesOrderNumber IN ('SO58536','SO68514')
ORDER BY SalesOrderNumber, ProductID

TratamientoDatosDuplicadosSQLServer_10

Lo que hace la anterior consulta es añadir una nueva columna al conjunto de resultados con el primer valor de cada partición, pero lo que nosotros perseguimos es la obtención de la primera fila de cada una de dichas particiones. Para conseguir dicho objetivo situaremos dentro de una CTE (expresión común de tabla) una consulta que nos devuelva la información necesaria de cada primera fila. Cruzando este resultado con la tabla Ventas mediante una combinación de tipo INNER JOIN, obtendremos única y exclusivamente las primeras filas objeto de nuestra búsqueda.

WITH ctePrimeraFila AS
(
    SELECT DISTINCT
    SalesOrderNumber,
    FIRST_VALUE(LineTotal) 
        OVER(PARTITION BY SalesOrderNumber ORDER BY ProductID) AS PrimerValor
    FROM Ventas
    WHERE SalesOrderNumber IN ('SO58536','SO68514')
)
SELECT Ventas.*
FROM Ventas
INNER JOIN ctePrimeraFila
ON Ventas.SalesOrderNumber = ctePrimeraFila.SalesOrderNumber
AND Ventas.LineTotal = ctePrimeraFila.PrimerValor

TratamientoDatosDuplicadosSQLServer_11

En el caso de que nuestra versión de SQL Server (por ejemplo 2005) no disponga de FIRST_VALUE, y queramos crear una columna calculada conteniendo el valor de la primera fila de cada partición, también acudiremos al uso de expresiones de tabla (CTE), pero combinándolas esta vez con la función ROW_NUMBER.

WITH
cteCalcularPrimerValor AS
(
    SELECT
    SalesOrderNumber,
    LineTotal,
    ROW_NUMBER()
    OVER(PARTITION BY SalesOrderNumber ORDER BY ProductID) AS NumeroFila
    FROM Ventas
    WHERE SalesOrderNumber IN ('SO58536','SO68514')
)
, ctePrimerValor AS
(
    SELECT SalesOrderNumber, LineTotal AS PrimerValor
    FROM cteCalcularPrimerValor
    WHERE NumeroFila = 1
)
SELECT v.*, p.PrimerValor
FROM Ventas AS v
INNER JOIN ctePrimerValor AS p
ON v.SalesOrderNumber = p.SalesOrderNumber
ORDER BY v.SalesOrderNumber, v.ProductID

En el anterior bloque de código necesitamos dos CTEs: la primera nos ayuda a generar un número de orden para cada una de las filas del conjunto de resultados, mientras que en la segunda filtramos las filas de la primera CTE que en la columna del número de orden tengan el valor 1. Finalmente, en la consulta externa a las CTEs combinamos la tabla Ventas con la segunda CTE para obtener la columna correspondiente a los primeros valores, es decir, la columna que obtendríamos en caso de disponer de la función FIRST_VALUE.

TratamientoDatosDuplicadosSQLServer_12

Por otro lado, y basándonos en la anterior consulta, quizá nos interese recuperar solamente las filas de cada partición que correspondan al primer valor, por lo que la consulta se simplificaría, filtrando en el resultado de la CTE aquellas filas en las que la columna calculada NumeroFila sea igual a 1.

WITH
cteCalcularPrimerValor AS
(
  SELECT
  *,
  ROW_NUMBER() 
    OVER(PARTITION BY SalesOrderNumber ORDER BY ProductID) AS NumeroFila
  FROM Ventas
  WHERE SalesOrderNumber IN ('SO58536','SO68514')
)
SELECT *
FROM cteCalcularPrimerValor
WHERE NumeroFila = 1

TratamientoDatosDuplicadosSQLServer_13

 

Encontrar la última fila en un conjunto de datos duplicados

De igual forma nos hallaremos en algún momento ante la necesidad de obtener la última fila de un conjunto de resultados, con la dificultad añadida de que si hemos definido particiones, en un buen número de ocasiones la última fila de cada bloque de datos repetidos no estará en la misma posición, es decir, en una partición puede que la encontremos en la cuarta fila, en otra la sexta y así sucesivamente.

TratamientoDatosDuplicadosSQLServer_14

Para abordar este escenario disponemos de la función LAST_VALUE, que representa la versión opuesta de la función explicada en el apartado anterior, ya que su finalidad consiste en devolver un valor correspondiente a la última fila de un conjunto de resultados. Si dicho conjunto tuviera varias particiones creadas con la cláusula obligatoria OVER, obtendríamos el último valor de cada una de ellas. No obstante, comencemos por un ejemplo simple, sin particiones.

SELECT *, 
  LAST_VALUE(LineTotal) OVER(ORDER BY Name) AS UltimoValor
FROM Ventas
WHERE SalesOrderNumber IN ('SO58536','SO68514')
ORDER BY Name

TratamientoDatosDuplicadosSQLServer_15

¿Qué ha ocurrido aquí? Aparentemente el uso que hacemos de LAST_VALUE es correcto: pasamos como parámetro la columna LineTotal, de la cual queremos obtener el valor de la última fila, y mediante la cláusula OVER establecemos el orden que tendrán las filas a efectos de esta función. Sin embargo, la columna calculada UltimoValor no devuelve en todos los casos 4.990000, que es el valor que debería tener si observamos los datos resultantes en la columna LineTotal de la última fila., por lo que podemos deducir que el uso de LAST_VALUE no resulta, a priori, tan intuitivo como el de FIRST_VALUE.

El problema radica en que dentro de OVER no hemos especificado el criterio que establece los límites de la ventana/partición (si no declaramos una partición, SQL Server toma implícitamente todas las filas de la consulta como una especie de partición predeterminada), por lo que una función de ventana como LAST_VALUE no sabe “orientarse” a la hora de calcular el último valor, puesto que no tiene los puntos de referencia necesarios para determinarlo.

Consultando la documentación relativa a OVER veremos que existe un conjunto de palabras clave (ROWS, CURRENT ROW, PRECEDING, FOLLOWING, UNBOUNDED, etc.) que serán las encargadas de dotar a las filas de este comportamiento, por lo que si escribimos la sentencia de la siguiente manera obtendremos el resultado deseado.

SELECT *, 
  LAST_VALUE(LineTotal)
  OVER(
    ORDER BY Name
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  )
  AS UltimoValor
FROM Ventas
WHERE SalesOrderNumber IN ('SO58536','SO68514')
ORDER BY Name

Lo que hemos indicado ahora en la función de ventana se podría traducir como: “Obtener el valor de la última fila (LAST_VALUE) ordenando (ORDER BY) por la columna Name las filas (ROWS) que se encuentran entre (BETWEEN) la actual (CURRENT ROW) y el final de la ventana/partición (UNBOUNDED FOLLOWING)”.

De la sentencia y explicación del párrafo anterior lo que quizá pueda ocasionar una mayor confusión al lector sea la mención al concepto de fila actual (CURRENT ROW), por lo que esta cuestión merece que le dediquemos un tratamiento más detallado.

En la consulta sobre la que estamos trabajando, una vez que se realiza la extracción principal de registros de la tabla, la cláusula OVER de la función LAST_VALUE toma el control para ordenar internamente las filas por la columna Name. Hecho esto, comienza el cálculo del valor para cada fila de la columna UltimoValor, siguiendo la pauta explicada a continuación.

La primera fila del conjunto de resultados pasa a ser la fila actual (CURRENT ROW), LAST_VALUE se desplaza hasta la última fila del conjunto de resultados, toma el valor de la columna LineTotal, vuelve a la fila actual y deposita dicho valor en la columna calculada UltimoValor.

A continuación, la segunda fila del conjunto se convierte en fila actual, realizando idéntica operación que en el caso anterior. Esta acción continuará repitiéndose hasta haber completado la asignación de valor en la columna UltimoValor para todas las filas.

TratamientoDatosDuplicadosSQLServer_17

Volviendo a nuestro objetivo original: encontrar la última fila en un conjunto compuesto por grupos de datos duplicados; el único cambio que tendríamos que añadir a nuestra consulta sería la cláusula PARTITION BY, especificando el modo de particionamiento.

SELECT *, 
	LAST_VALUE(LineTotal)
	OVER(
		PARTITION BY SalesOrderNumber
		ORDER BY Name
		ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
	)
	AS UltimoValor
FROM Ventas
WHERE SalesOrderNumber IN ('SO58536','SO68514')
ORDER BY SalesOrderNumber, Name

TratamientoDatosDuplicadosSQLServer_18

Con la anterior sentencia, como habrá comprobado el lector, lo que realmente obtenemos es la información acerca de un valor en la última fila de cada partición en forma de columna calculada. Si al igual que ocurría al utilizar FIRST_VALUE, lo que ahora queremos conseguir con LAST_VALUE son las últimas filas de cada partición del conjunto de datos, volveremos a utilizar una CTE que devuelva esas filas, que cruzaremos en la consulta externa a la CTE con la tabla Ventas mediante INNER JOIN, logrando de esta manera las últimas filas de cada partición.

WITH cteUltimaFila AS
(
	SELECT DISTINCT
	SalesOrderNumber,
	LAST_VALUE(LineTotal)
	OVER(
		PARTITION BY SalesOrderNumber
		ORDER BY Name
		ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
	) AS UltimoValor
	FROM Ventas
	WHERE SalesOrderNumber IN ('SO58536','SO68514')
)
SELECT Ventas.*
FROM Ventas
INNER JOIN cteUltimaFila
ON Ventas.SalesOrderNumber = cteUltimaFila.SalesOrderNumber
AND Ventas.LineTotal = cteUltimaFila.UltimoValor

TratamientoDatosDuplicadosSQLServer_19

Supongamos ahora que necesitamos implementar esta funcionalidad en una versión de SQL Server que no soporta la función LAST_VALUE, en cuyo caso tendremos que optar por una solución al problema un poco más artificiosa a través de ROW_NUMBER.

SELECT *,
ROW_NUMBER() 
	OVER(PARTITION BY SalesOrderNumber ORDER BY Name) AS NumeroFila
FROM Ventas
WHERE SalesOrderNumber IN ('SO58536','SO68514')

Aplicando ROW_NUMBER a la consulta en la forma que acabamos de mostrar obtendremos dos particiones, en una de ellas la última fila será la cuarta, y en la otra la quinta.

TratamientoDatosDuplicadosSQLServer_20

Como hemos comentado anteriormente, si trabajamos con un conjunto de resultados compuesto por muchas particiones, la variación en la posición de la última fila para cada partición será muy grande, por lo que para acceder a dicha fila de un modo uniforme podemos recurrir a un sencillo truco, consistente en ordenar de forma descendente la partición usando la partícula DESC en la cláusula ORDER BY de OVER. De este modo, en la columna calculada NumeroFila, a las últimas filas se les asignará el número 1.

SELECT *,
ROW_NUMBER() 
	OVER(PARTITION BY SalesOrderNumber ORDER BY Name DESC) AS NumeroFila
FROM Ventas
WHERE SalesOrderNumber IN ('SO58536','SO68514')

TratamientoDatosDuplicadosSQLServer_21

Si ahora queremos conseguir el mismo efecto que la función LAST_VALUE utilizaremos una combinación de dos CTEs: en la primera de ellas, cteCalcularUltimoValor, calcularemos el número de posición para la última fila tal y como acabamos de ver, mientras que en la siguiente, cteUltimoValor, filtraremos las filas obtenidas de cteCalcularUltimoValor, quedándonos sólo con las que tienen el valor 1 en la columna NumeroFila, o lo que es lo mismo, las últimas filas de cada partición. Por último, en la consulta externa a las CTEs combinaremos la tabla Ventas y cteUltimoValor para obtener todos los datos, incluyendo la columna calculada del último valor.

WITH
cteCalcularUltimoValor AS
(
	SELECT
	SalesOrderNumber,
	LineTotal,
	ROW_NUMBER() 
		OVER(PARTITION BY SalesOrderNumber ORDER BY Name DESC) AS NumeroFila
	FROM Ventas
	WHERE SalesOrderNumber IN ('SO58536','SO68514')
)
, cteUltimoValor AS
(
	SELECT SalesOrderNumber,LineTotal AS UltimoValor
	FROM cteCalcularUltimoValor
	WHERE NumeroFila = 1
)
SELECT v.*, u.UltimoValor
FROM Ventas AS v 
INNER JOIN cteUltimoValor AS u
ON v.SalesOrderNumber = u.SalesOrderNumber
ORDER BY v.SalesOrderNumber, v.Name

TratamientoDatosDuplicadosSQLServer_22

En el caso de que solamente necesitemos los últimos registros de cada grupo, retocaremos la anterior sentencia, filtrando aquellos en que la columna NumeroFila tenga el valor 1.

WITH
cteCalcularUltimoValor AS
(
	SELECT
	*,
	ROW_NUMBER() 
		OVER(PARTITION BY SalesOrderNumber ORDER BY Name DESC) AS NumeroFila
	FROM Ventas
	WHERE SalesOrderNumber IN ('SO58536','SO68514')
)
SELECT * 
FROM cteCalcularUltimoValor
WHERE NumeroFila = 1

TratamientoDatosDuplicadosSQLServer_23

 

Conclusión

La incorporación progresiva de las funciones de ventana al motor de datos de SQL Server constituye una potente herramienta, aplicable a multitud de operaciones tales como la gestión de duplicados presentada en el presente artículo. Confiamos en que los ejemplos aquí expuestos ayuden al lector a la hora de resolver los problemas que pueda encontrar en su trabajo de tratamiento de datos duplicados.

Tratamiento de datos duplicados en SQL Server (1)

Una de las operaciones a las que con mayor frecuencia se enfrenta cualquier desarrollador de bases de datos es la detección y tratamiento de datos duplicados, ya sea para encontrar varios registros exactamente iguales en una tabla, debido a problemas en el diseño y consistencia de la propia base de datos, o bien para localizar determinados subconjuntos de datos con condiciones que se repitan dentro de una misma tabla.

En el presente artículo vamos a explorar diversas técnicas para enfrentarnos a este tipo de escenario, que nos permitan localizar casos de información duplicada en tablas, para proceder a su análisis, o bien para poder eliminar los datos sobrantes en el caso de repeticiones innecesarias.

En primer lugar crearemos una nueva base de datos con el nombre PruebasDuplicados, y dentro de esta, una tabla que llamaremos Ventas, cuyas filas procederán de la siguiente sentencia sobre la base de datos AdventureWorks, que devuelve datos duplicados con los que podremos realizar nuestras pruebas.

IF (OBJECT_ID('Ventas','U') IS NOT NULL)
BEGIN
  DROP TABLE Ventas
END

SELECT
h.SalesOrderID,
h.OrderDate,
h.SalesOrderNumber,
h.TotalDue,
d.ProductID,
d.LineTotal,
p.Name
INTO Ventas
FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID
INNER JOIN AdventureWorks.Production.Product AS p
ON d.ProductID = p.ProductID

 

TratamientoDatosDuplicadosSQLServer_01

 

GROUP BY…HAVING…COUNT

La combinación de las cláusulas GROUP BY y HAVING de la sentencia SELECT, junto con la función COUNT representa la forma más sencilla de detectar si en una tabla existen valores duplicados para una columna o combinación de columnas, como podemos apreciar en la siguiente consulta sobre nuestra tabla de ejemplo Ventas, donde obtendremos aquellos valores de la columna SalesOrderNumber en los que se está produciendo una duplicidad, y la cantidad de filas en las que se produce para cada ocurrencia de la mencionada columna.

SELECT SalesOrderNumber, COUNT(*) AS RecuentoFilas
FROM Ventas
GROUP BY SalesOrderNumber
HAVING COUNT(*) > 1
ORDER BY SalesOrderNumber

 

TratamientoDatosDuplicadosSQLServer_02

Si además queremos ver el detalle de los registros que cumplen esta condición de duplicidad, situaremos la anterior sentencia (con ligeros retoques) como una subconsulta sobre la tabla Ventas, cruzando por el campo SalesOrderNumber.

SELECT * FROM Ventas
WHERE SalesOrderNumber IN (
	SELECT SalesOrderNumber
	FROM Ventas
	GROUP BY SalesOrderNumber
	HAVING COUNT(*) > 1
)
ORDER BY SalesOrderNumber

 

TratamientoDatosDuplicadosSQLServer_03

Empleando en la consulta externa la instrucción DELETE junto a una condición adicional, podemos borrar selectivamente ciertas filas de cada grupo de datos duplicados, como vemos en el siguiente ejemplo, donde eliminamos aquellos registros con el valor 711 en la columna ProductID.

TratamientoDatosDuplicadosSQLServer_04

 

ROW_NUMBER

Supongamos ahora que del conjunto de resultados con valores duplicados queremos operar sobre una fila muy concreta de las que componen la repetición. Mediante la siguiente consulta tomaremos en primer lugar una muestra de registros para ilustrar mejor nuestro propósito.

SELECT *
FROM Ventas
WHERE SalesOrderNumber IN ('SO58536','SO68514')
ORDER BY SalesOrderNumber, ProductID

 

TratamientoDatosDuplicadosSQLServer_05

Al igual que en los anteriores ejemplos, por cada valor distinto en la columna SalesOrderNumber existe un número variable de registros con dicho valor repetido. Si tuviéramos que seleccionar de cada grupo la fila situada en una determinada posición podríamos conseguirlo mediante la función ROW_NUMBER, como demostraremos posteriormente.

ROW_NUMBER, como su nombre indica, asigna un número de fila a cada registro del conjunto de resultados obtenidos a partir de una consulta.

En su forma de uso más básica, junto al nombre de la función hemos de utilizar la cláusula OVER, y dentro de esta la partícula ORDER BY, para que la asignación del número de fila se haga en un orden concreto. En el siguiente ejemplo generamos un número de fila para la tabla Ventas, ordenando el resultado por la columna LineTotal.

SELECT *, ROW_NUMBER() OVER(ORDER BY LineTotal) AS NumeroFila
FROM Ventas
WHERE SalesOrderNumber IN ('SO58536','SO68514')

 

TratamientoDatosDuplicadosSQLServer_06

Resulta importante resaltar que la columna utilizada dentro de ROW_NUMBER para ordenar las filas no tiene que ser obligatoriamente  la misma que empleemos en la sentencia SELECT. Variando el ejemplo anterior, a continuación vemos que los números de fila asignados por la función seguirán siendo los mismos, pero la ordenación del conjunto de resultados obtenido se hará por la columna Name.

SELECT *, ROW_NUMBER() OVER(ORDER BY LineTotal) AS NumeroFila
FROM Ventas
WHERE SalesOrderNumber IN ('SO58536','SO68514')
ORDER BY Name

 

TratamientoDatosDuplicadosSQLServer_07

ROW_NUMBER se encuentra entre el grupo de funciones englobadas en la categoría conocida como funciones de ventana (Window functions), debido a que aparte del funcionamiento que acabamos de ver, nos permiten definir particiones (ventanas) de filas dentro de un conjunto de resultados sobre los que actuar de forma independiente, generando un número para cada fila de la partición, la cual especificaremos en la cláusula OVER de esta función mediante la partícula PARTITION BY.

Al aplicar, por tanto, ROW_NUMBER sobre nuestra consulta, particionaremos por la columna SalesOrderNumber de la siguiente manera.

SELECT *, 
ROW_NUMBER() OVER(PARTITION BY SalesOrderNumber ORDER BY ProductID) AS NumeroFila
FROM Ventas
WHERE SalesOrderNumber IN ('SO58536','SO68514')

 

TratamientoDatosDuplicadosSQLServer_08

Obsérvese que, tal y como hemos mencionado anteriormente, la columna mediante la que establecemos la partición es independiente de la columna que utilizamos para ordenar las filas dentro de la partición, es decir, que mientras que definimos la partición sobre SalesOrderNumber, dentro de cada partición las filas se ordenarán por ProductID.

Como vemos en el actual apartado, y como tendremos ocasión de comprobar en los siguientes, la cláusula OVER es el denominador común a todas las funciones de ventana, ya que representa el elemento que determina la forma en la que el conjunto de filas de la consulta se particiona y ordena antes de que la función de ventana sea aplicada. Una vez establecidas las particiones y el orden en las filas, se aplica el cálculo correspondiente a cada una de ellas según la función de ventana utilizada: asignación de un número de fila, obtención del primer o último valor, etc.

 

Eliminar una fila con posición intermedia dentro de un grupo de datos duplicados

Como continuación de lo expuesto en el apartado anterior, supongamos que del siguiente conjunto de datos particionado mediante ROW_NUMBER, queremos eliminar la fila de cada partición que ocupa la tercera posición.

SELECT *,
ROW_NUMBER() OVER(PARTITION BY SalesOrderNumber ORDER BY ProductID) AS NumeroFila
FROM Ventas
WHERE SalesOrderNumber IN ('SO43670','SO43676','SO43693','SO43853')
ORDER BY SalesOrderNumber, NumeroFila

 

TratamientoDatosDuplicadosSQLServer_09

La solución pasa por situar la anterior consulta dentro de una expresión común de tabla (CTE, common table expression) y realizar el borrado de registros desde la sentencia externa a la CTE como vemos a continuación, especificando en la cláusula WHERE el número de fila a borrar.

WITH
cteFilas AS
(
	SELECT *, 
	ROW_NUMBER() OVER(PARTITION BY SalesOrderNumber ORDER BY ProductID) AS NumeroFila
	FROM Ventas
	WHERE SalesOrderNumber IN ('SO43670','SO43676','SO43693','SO43853')
)
DELETE FROM cteFilas
WHERE NumeroFila = 3

 

Llegados a este punto concluimos la primera parte del presente artículo. En la siguiente entrega veremos la forma de seleccionar valores en la primera y última fila de las particiones de datos duplicados.

Ordenación de fechas en PowerPivot

El tratamiento de fechas es un aspecto del desarrollo de un sistema de información que puede provocarnos más de un quebradero de cabeza. PowerPivot, como herramienta orientada al análisis de datos, permite, entre todos los tipos de datos de que dispone, el manejo de fechas. En el presente artículo abordaremos el uso de fechas desde PowerPivot, focalizando nuestros esfuerzos en el apartado dedicado a la ordenación de las mismas.

Todo proceso de desarrollo de un sistema de información implica, en mayor o menor grado, el trabajo con elementos temporales. Tomando como ejemplo un sistema Business Intelligence, si observamos los cubos de datos de que está compuesto, nos percataremos de que la mayoría dispone de dimensiones de fecha, destinadas al análisis de la información desde una perspectiva temporal.

PowerPivot, como ya explicamos en un artículo anteriormente publicado en este mismo blog, es una herramienta self-service BI, que puede utilizarse tanto en las fases iniciales del desarrollo de un sistema de inteligencia de negocio, como para realizar un análisis rápido, a partir de un conjunto de resultados obtenidos del propio sistema de información corporativo, o de fuentes externas a éste.

 

Creación de la fuente de datos

Situemos nuestro escenario de trabajo en este último supuesto que acabamos de mencionar: nos entregan una tabla con datos de ventas, perteneciente a una base de datos externa a nuestro sistema de información, sobre la que tenemos que realizar diversos análisis; algunos de ellos implican el uso de uno de los campos de fecha disponibles en la tabla.

Como motor de datos emplearemos SQL Server 2008 R2, mientras que como fuente de datos origen utilizaremos AdventureWorksDW2008, copiando la tabla FactInternetSales a una nueva base de datos con el nombre FechasPowerPivot.

 


USE master
GO
CREATE DATABASE FechasPowerPivot
GO

USE FechasPowerPivot
GO
SELECT * INTO FactInternetSales FROM AdventureWorksDW2008.dbo.FactInternetSales
GO

 

Aunque acabamos de indicar que los análisis que necesitaremos realizar sobre la tabla FactInternetSales requerirán el uso de un campo de fecha, al observar la estructura de la tabla FactInternetSales, advertiremos que los campos cuyos nombres sugieren que su contenido puede contener tal información (OrderDateKey, DueDateKey y ShipDateKey), son realmente de tipo int, teniendo sus valores un formato que podemos identificar con las diferentes partes de una fecha, ya que los cuatro primeros dígitos representan el año, los dos siguientes el mes, y los dos últimos el día.

El motivo de este diseño de tabla se debe a que en AdventureWorksDW2008, la base de datos origen, estos campos de la tabla FactInternetSales, así como del resto de tablas, se relacionan con el campo DateKey de la tabla DimDate, la cual constituye el catálogo de fechas de la base de datos; donde los campos de cada registro, que representa una fecha, identifican los diferentes elementos de la misma: año, mes, nombre de mes, día de la semana, etc.

Podríamos añadir la tabla DimDate a la base de datos FechasPowerPivot, lo que facilitaría enormemente nuestra labor. Sin embargo, hay ocasiones en las que por determinadas restricciones, sólo disponemos de un único conjunto de resultados al que debemos acomodarnos, así que intentando emular dicho escenario, emplearemos solamente la tabla FactInternetSales.

 

Preparación del modelo de datos en PowerPivot

A continuación iniciaremos Excel, creando un archivo con el nombre OrdenarFechas.xlsx. Seguidamente abriremos la ventana de PowerPivot, donde crearemos un modelo de datos conteniendo la tabla FactInternetSales de la base de datos FechasPowerPivot. El proceso de creación de un modelo de datos se explica aquí.

 

La columna de fecha

Puesto que en el modelo de PowerPivot el tipo de dato de la columna OrderDateKey también es numérico, crearemos, partiendo de dicha columna, una nueva columna calculada cuyo tipo de dato sea fecha.

OrdenacionFechasPowerPivot_01

 

Para ello nos situaremos en la primera columna vacía existente en la ventana de PowerPivot, introduciendo la siguiente expresión DAX en la barra de fórmulas.

 


=DATE(
  LEFT(FactInternetSales[OrderDateKey],4), 
  MID(FactInternetSales[OrderDateKey],5,2), 
  RIGHT(FactInternetSales[OrderDateKey],2)
)

 

La función DATE devuelve un valor de tipo fecha a partir de los tres números que recibe como parámetro y que corresponden al año, mes y día de la fecha a crear. Estos números son obtenidos de la columna OrderDateKey mediante el uso de las funciones de cadena LEFT, MID y RIGHT. Aunque el tipo de dato de dicha columna es numérico, las mencionadas funciones realizan una conversión de tipo automática a cadena para extraer la subcadena correspondiente en cada caso; siendo transformados   nuevamente en números al ser empleados en la función DATE. De esta forma habremos creado una nueva columna calculada, a la que daremos el nombre FechaPedido editando el título de su cabecera.

 

OrdenacionFechasPowerPivot_02

 

La jerarquía de fecha

El siguiente paso consistirá en crear tres nuevas columnas calculadas, que representen las diferentes partes de las fechas incluidas en la columna FechaPedido. Para ello utilizaremos las funciones de manipulación de fechas incluidas en el lenguaje DAX de PowerPivot que vemos a continuación.

 


=YEAR([FechaPedido])
=MONTH([FechaPedido])
=DAY([FechaPedido])

 

OrdenacionFechasPowerPivot_03

 

A continuación, en la cinta de opciones de la ventana de PowerPivot, haremos clic en la opción “PivotTable” del grupo “Informes”, creándose de esta manera una tabla dinámica en la ventana de Excel, que nos permitirá analizar la información del modelo de datos de PowerPivot.

Dentro del panel “Lista de campos de PowerPivot” arrastraremos, desde el bloque de selección de campos hasta el bloque “Etiquetas de fila”, los campos que acabamos de crear, formando una jerarquía de fecha de tres niveles (año, mes y día), por la que podremos navegar, expandiendo y replegando sus elementos, para obtener diferentes grados de detalle de los datos, en base a las fechas de venta.

 

OrdenacionFechasPowerPivot_04

 

Obteniendo el nombre del mes

A pesar de ser completamente operativa, nuestra jerarquía adolece de un detalle para ser más funcional: mostrar el mes como nombre en lugar de número.

Solucionaremos este problema desplazándonos a la ventana de PowerPivot, y creando una nueva columna calculada llamada MesNombre, que contenga la expresión =FORMAT([FechaPedido],”mmmm”), en la que como podemos ver, aplicaremos un formato para obtener el nombre del mes de la columna de fecha. Posteriormente sustituiremos, en la tabla dinámica, el campo Mes por MesNombre.

 

OrdenacionFechasPowerPivot_05

 

Observemos, no obstante, que en la tabla dinámica, las etiquetas con los nombres de mes han quedado ordenadas alfabéticamente, en lugar de mantener el orden de fecha, como sería más apropiado.

Para resolver este problema haremos clic en la opción “Ordenar de A a Z” de la cinta de opciones de Excel, perteneciente al grupo “Ordenar y filtrar” de la ficha “Opciones”, que a su vez está contenida en la pestaña de nivel superior “Herramientas de tabla dinámica”. Como resultado, el orden de los nombres de mes cambiará para adaptarse a la disposición del calendario.

 

OrdenacionFechasPowerPivot_06

 

¿Dónde reside la magia que hace posible esta alteración en el comportamiento de la ordenación? La respuesta se halla en las listas personalizadas de Excel, una característica que, como su nombre indica, nos permite la creación de una lista de literales en un orden particular, que no tiene por qué ajustarse al orden predeterminado utilizado por el motor de la hoja de cálculo.

Para acceder a estas listas personalizadas haremos clic en el elemento “Opciones”, perteneciente a la pestaña “Archivo” de la cinta de opciones de Excel. En esta ventana de opciones, dentro del apartado “Avanzadas”, haremos clic en el botón “Modificar listas personalizadas”, que abrirá su ventana de mantenimiento, donde comprobaremos que ya se encuentra presente la lista que acabamos de utilizar con los nombres de meses.

 

OrdenacionFechasPowerPivot_07

 

Para comprobar su funcionamiento, vamos a añadir en la ventana de PowerPivot, una columna calculada que genere los nombres de los meses en un idioma distinto del local, utilizando de forma combinada las funciones IF y MONTH del lenguaje DAX. Llamaremos a esta columna MesNombre2.

 


=IF(MONTH([FechaPedido])=1, "Janvier",
IF(MONTH([FechaPedido])=2, "Février",
IF(MONTH([FechaPedido])=3, "Mars",
IF(MONTH([FechaPedido])=4, "Avril",
IF(MONTH([FechaPedido])=5, "Mai",
IF(MONTH([FechaPedido])=6, "Juin",
IF(MONTH([FechaPedido])=7, "Juillet",
IF(MONTH([FechaPedido])=8, "Août",
IF(MONTH([FechaPedido])=9, "Septembre",
IF(MONTH([FechaPedido])=10, "Octobre",
IF(MONTH([FechaPedido])=11, "Novembre",
"Décembre")
))))))))))

Si utilizamos esta nueva columna como nombre de mes en la jerarquía de fechas, los nombres se ordenaran alfabéticamente, y en esta ocasión permanecerán así aunque seleccionemos la opción “Ordenar de A a Z”.

Agreguemos pues una nueva lista personalizada a Excel, compuesta por los nombres de la columna MesNombre2, y volvamos a ordenar los meses.

 

OrdenacionFechasPowerPivot_08

 

Comprobaremos que ahora ya sí se ordenan tal y como necesitamos, lo que nos proporciona una idea acerca del funcionamiento de estas listas y su aplicación en tablas dinámicas.

 

OrdenacionFechasPowerPivot_09

 

Las listas personalizadas no funcionan en segmentadores

En el caso de que en la tabla dinámica necesitemos utilizar un segmentador basado en el campo del nombre del mes, volveremos a encontrarnos nuevamente con el problema de la ordenación, ya que los segmentadores no soportan la ordenación en base a las listas personalizadas.

 

OrdenacionFechasPowerPivot_10

 

En esta ocasión podemos solucionar el problema creando en PowerPivot otra columna calculada, que llamaremos MesNumeroNombre, con una cadena de formato que en primer lugar contenga los dígitos del mes, seguidos del nombre: =FORMAT([FechaPedido],”mm – mmmm”).

Al utilizar esta columna como campo de la tabla dinámica en un segmentador, el orden mostrado por sus elementos corresponderá al que se espera de un tipo de dato fecha. Adicionalmente podemos retocar la expresión DAX de esta columna para hacer que la primera letra del nombre del mes se muestre en mayúscula.

 


=REPLACE(
  FORMAT([FechaPedido],"mm - mmmm"), 
  6, 
  1, 
  UPPER(MID(FORMAT([FechaPedido],"mm - mmmm"),6,1))
)

 

OrdenacionFechasPowerPivot_11

 

SQL Server 2012. La solución

En el caso de que trabajemos con (Denali) SQL Server 2012 (Denali), no será necesario que utilicemos las técnicas relatadas anteriormente, ya que PowerPivot para SQL Server 2012 permite solucionar este problema de una forma mucho más sencilla y elegante, tal y como explicamos a continuación.

Para empezar, volvamos al momento del desarrollo de nuestro ejemplo en el que añadíamos la columna calculada MesNombre con la función FORMAT. Continuando en la ventana de PowerPivot, en su cinta de opciones seleccionaremos, dentro del grupo “Ordenar y filtrar”, la opción “Sort by Column”. En la ventana del mismo nombre elegiremos, en sendos desplegables, la columna que queremos ordenar, MesNombre, y la columna por la que se realizará la ordenación de la primera, Mes.

 

OrdenacionFechasPowerPivot_12

 

Al actualizar estos cambios en la tabla dinámica, el campo MesNombre ya se ordenará correctamente tanto en la propia tabla como en el segmentador empleando únicamente el nombre del mes, lo que nos evita el uso de las técnicas artificiosas explicadas anteriormente.

OrdenacionFechasPowerPivot_13

 

Conclusiones

En la primera versión de PowerPivot, la ordenación de ciertas partes de una fecha, tales como el nombre del mes, precisa de la utilización de ciertos trucos para poder llevarla a cabo correctamente. La próxima aparición de SQL Server 2012, ya disponible en Release Candidate, conlleva aparejada una nueva versión de PowerPivot, que simplifica en gran medida este aspecto, facilitando el trabajo a todos aquellos usuarios de esta potente herramienta de análisis de información.

Modelos tabulares en SQL Server 2012 Analysis Services

La reciente aparición oficial de SQL Server 2012 (también conocido por el nombre clave Denali) viene acompañada, como es habitual en toda nueva versión, por una serie de interesantes mejoras, entre las cuales, en el presente artículo, destacaremos aquellas relacionadas con el proceso de creación y explotación de un Modelo Tabular de análisis, una de las piezas integrantes del Modelo Semántico de Inteligencia de Negocio (BISM, Business Intelligence Semantic Model), el nuevo paradigma para el desarrollo de soluciones de Inteligencia de Negocio (BI, Business Intelligence), basadas en los Servicios de Análisis pertenecientes a SQL Server 2012 (SSAS, SQL Server Analysis Services).

Como ya adelantábamos en un artículo anterior, la incorporación de BISM a los servicios de análisis de SQL Server, convierte a estos últimos en una de las soluciones más potentes dentro del panorama actual de BI. No obstante, desde los primeros anuncios hechos públicos acerca de BISM hasta ahora, se han desvelado nuevos detalles acerca de la arquitectura de dicha tecnología, que nos permiten ser más precisos al hacer la descripción de la misma.

 

BISM. Un modelo único para todas las necesidades analíticas

El nuevo BISM se establece como el modelo único para el desarrollo de soluciones de Inteligencia de Negocio, con el que se atenderán los requerimientos de los diversos tipos de usuarios y aplicaciones para el análisis de información.

BISM representa una evolución del anterior Modelo Dimensional Unificado (UDM, Unified Dimensional Model), hacia un modelo combinado, que ofrece todas las funcionalidades del desarrollo multidimensional que ya tenía UDM, más nuevas características basadas en un diseño y motor de análisis relacional; lo que enriquece la actual oferta de SQL Server dentro del campo de los servicios de análisis. Por este motivo, a partir de ahora, el término UDM queda reemplazado en favor de BISM, cuando hagamos mención al modelo de desarrollo utilizado por Analysis Services. De igual manera, cuando actualicemos un proyecto UDM a SQL Server 2012, será considerado a todos los efectos como un proyecto BISM.

Esta integración en las filosofías de trabajo, y por ende, en las tecnologías subyacentes (multidimensional y relacional), da como resultado una arquitectura distribuida en tres capas, con capacidad para dar respuesta a usuarios con necesidades muy dispares en todo lo referente a las tareas relacionadas con el análisis de información.

 

ModelosTabularesSQLServer2012AnalysisServices_01

 

Si observamos el flujo de funcionamiento de esta arquitectura, partiendo de las fuentes de datos nos encontramos, en primer lugar, con la capa de Acceso a datos, que es la encargada de realizar la extracción de la información residente en los orígenes de datos a los que nos conectamos, existiendo dos mecanismos para llevarla a cabo: por caché y con acceso directo (passthrough) a la fuente de datos.

El mecanismo de caché obtiene los datos de la fuente original, y los almacena en una estructura de datos basada en un algoritmo de compresión optimizado para su acceso a gran velocidad. A su vez, dentro de este modo en caché tenemos que optar por dos motores distintos de almacenamiento: MOLAP o xVelocity.

MOLAP es la vía utilizada tradicionalmente en Analysis Services como sistema de almacenamiento intermedio, y como su nombre indica, está optimizado para su uso en el desarrollo de modelos multidimensionales (cubos OLAP).

xVelocity es un novedoso sistema introducido con BISM para su empleo en modelos tabulares, consistente en un motor de almacenamiento de datos en memoria basado en columna, que aúna sofisticados algoritmos de compresión y búsqueda para ofrecer, sin necesidad de utilizar índices o agregaciones, un rendimiento excelente en la velocidad de respuesta a nuestras consultas.

Con respecto a la modalidad de acceso directo, tal y como su nombre indica, lo que hace es enviar la consulta directamente al motor de la fuente de datos origen, para que ambas operaciones: procesamiento de datos y lógica de negocio, sean realizadas   allí. En este caso también existen dos modalidades de ejecución: ROLAP y DirectQuery.

ROLAP es el modo habitualmente empleado por Analysis Services, que utiliza la   propia fuente de datos origen, para procesar las consultas efectuadas contra cubos pertenecientes a modelos multidimensionales.

DirectQuery se utiliza en modelos tabulares para procesar igualmente las consultas en el origen de datos.

En la capa Lógica de negocio incluiremos las consultas en los lenguajes MDX o DAX, según trabajemos respectivamente contra un modelo multidimensional o tabular, con las que implementaremos la lógica de nuestra solución.

Para terminar, en el Modelo de datos encontramos la capa conceptual, en la que utilizando SQL Server Data Tools (antiguo Business Intelligence Development Studio y actual entorno de desarrollo basado en Visual Studio 2010) construiremos nuestro modelo empleando alguna de las plantillas de proyecto disponibles a tal efecto: Analysis Services Multidimensional and Data Mining Project o Analysis Services Tabular Project.

Cabe mencionar que también resulta posible utilizar PowerPivot como herramienta de desarrollo de un modelo tabular, aunque para llevar a cabo la implementación de éste en el servidor tendremos necesariamente que usar SQL Server Data Tools.

 

Instalación de Analysis Services. Un modelo semántico, dos modos de ejecución

Dadas las particulares características de cada uno de los modelos de datos pertenecientes a BISM, en el programa de instalación de SQL Server 2012, al llegar al paso correspondiente a Analysis Services, debemos elegir qué modo de ejecución del servidor de análisis queremos instalar: Multidimensional o Tabular, ya que no es posible la instalación simultánea de ambos en la misma instancia. Por este motivo, el procedimiento recomendado consiste en instalar cada modo en instancias separadas. No obstante, en nuestro caso, bastará con instalar únicamente el modo tabular para el desarrollo del ejemplo.

ModelosTabularesSQLServer2012AnalysisServices_02

 

Manos a la obra. Creación de un modelo tabular

Una vez expuestos todos aquellos aspectos necesarios para que el lector pueda ubicarse conceptualmente dentro del nuevo modelo semántico, pasemos a la parte práctica del artículo, en la que nos ocuparemos de desarrollar un proyecto de análisis de tipo tabular.

La base de datos que emplearemos para el ejemplo será AdventureWorksDWDenali, mientras que el objetivo del modelo a crear consistirá en analizar los gastos de envío de los pedidos realizados por los clientes a través de Internet, así como la cantidad de pedidos emitida, todo ello en función del territorio de residencia del cliente y los detalles de oferta o promoción en que estuvieran los artículos comprados.

En primer lugar iniciaremos SQL Server Data Tools, seleccionando un nuevo proyecto basado en la plantilla Analysis Services Tabular Project, al que daremos el nombre PruebaModeloTabular. Según veremos en el Explorador de Soluciones, este proyecto estará formado por un archivo con el nombre Model.bim, que representa al diseñador del modelo tabular.

ModelosTabularesSQLServer2012AnalysisServices_03

 

El siguiente paso consistirá en añadir al modelo las tablas que formarán parte del mismo, para lo cual seleccionaremos la opción de menú “Model | Import From Data Source”, que iniciará el asistente de importación de datos. Tras elegir el tipo de origen de datos, la instancia de SQL Server correspondiente y la base de datos, llegaremos al paso del asistente en el que deberemos especificar las credenciales para poder conectarnos a la fuente de datos y proceder a su extracción.

En este punto utilizaremos nuestra cuenta de Windows (en caso de que tenga los permisos de acceso suficientes) o bien elegiremos la opción Service Account, que utilizará la cuenta NT SERVICE\MSOLAP$SQL2012TAB, asociada al servicio de Analysis Services. Si empleamos esta última posibilidad, necesitaremos otorgar permiso de acceso y lectura a dicha cuenta sobre la base de datos AdventureWorksDWDenali.

ModelosTabularesSQLServer2012AnalysisServices_04

 

Para asignar el mencionado permiso, en SQL Server Management Studio, haremos clic derecho en el nodo Logins, perteneciente a su vez al nodo Security, eligiendo la opción New Login, que abrirá el cuadro de diálogo para añadir un nuevo login al servidor, donde introduciremos el nombre de la cuenta de Analysis Services. En el apartado User Mapping marcaremos la base de datos a la que daremos acceso.

ModelosTabularesSQLServer2012AnalysisServices_05

 

Continuando con el asistente de importación llegaremos a la selección de tablas, donde marcaremos DimSalesTerritory, DimPromotion y FactInternetSales, procediendo a iniciar el proceso de importación.

ModelosTabularesSQLServer2012AnalysisServices_06

 

Finalizado el proceso de importación de datos, el diseñador mostrará las tablas en forma de cuadrícula, organizándolas en pestañas.

ModelosTabularesSQLServer2012AnalysisServices_07

 

Este modo de visualización del modelo puede alternarse con un modo en diagrama, que será más aconsejable si queremos observar determinadas características, tales como las relaciones entre tablas. Para cambiar este modo de vista utilizaremos los botones situados a tal efecto en la parte inferior derecha de la ventana del diseñador.

ModelosTabularesSQLServer2012AnalysisServices_08

 

Creación de medidas

A pesar de haber importado las tablas al modelo, todavía no podemos realizar un análisis adecuado sobre el mismo, ya que carecemos de los cálculos (medidas o métricas en el contexto de BI) encargados de proporcionar los resultados numéricos, imprescindibles en cualquier sistema de estas características, como por ejemplo cubos OLAP.

Para crear una medida dentro del modelo, en primer lugar, la visualización del diseñador deberá estar establecida en cuadrícula (modo predeterminado). Una vez hecho este ajuste, observaremos que cada tabla muestra dos secciones: la superior, que contiene las filas de la propia tabla; y la inferior, reservada para las medidas calculadas que incorpore el desarrollador al modelo.

Seguidamente, elegiremos la tabla del modelo que contiene aquellas columnas susceptibles de ser utilizadas en la obtención de resultados numéricos (también denominada tabla de hechos dentro del contexto del diseño de modelos multidimensionales), y que en este proyecto será FactInternetSales.

A continuación utilizaremos la columna Freight para crear la primera de nuestras medidas: una suma de los valores de dicha columna para todas las filas de la tabla. En la zona inferior de la cuadrícula seleccionaremos una celda libre debajo de la columna mencionada, y después haremos clic en el botón Sum de la barra de herramientas, que aplicará a esta columna la fórmula “SUM([Freight])”, perteneciente al lenguaje DAX.

Fruto de esta operación será la obtención de la medida calculada Sum of Freight, cuyo nombre, asignado automáticamente por el entorno de desarrollo, cambiaremos en su ventana propiedades a GastosTransporte.

ModelosTabularesSQLServer2012AnalysisServices_09

 

Observando la cifra resultante nos percataremos de que existe un problema de formato con el valor obtenido (7.339.696.091,00 €), ya que el resultado correcto es 733969,6091, lo cual podemos comprobar ejecutando desde SQL Server Management Studio la consulta del siguiente código. No obstante, este escollo solamente lo encontraremos en el ámbito del diseñador del modelo, ya que como veremos en el siguiente apartado, al analizarlo desde una herramienta externa, los valores se visualizarán correctamente.

 


SELECT SUM(Freight) FROM FactInternetSales

 

Seguidamente crearemos la medida que calculará el número pedidos emitidos por la empresa, para lo cual necesitaremos aplicar sobre la columna SalesOrderNumber una operación de recuento de valores distintos (Distinct Count), ya que la tabla FactInternetSales puede tener más de un registro para el mismo pedido.

El botón Sum de la barra de herramientas de Visual Studio puede desplegarse para crear algunos de los cálculos más habituales, Distinct Count entre ellos, aunque en esta ocasión crearemos manualmente la medida FacturasEmitidas, escribiendo la expresión DAX “FacturasEmitidas:=DISTINCTCOUNT([SalesOrderNumber])” en la barra de fórmulas.

 

Analizando el modelo desde Excel

Si hemos instalado Excel 2010 en nuestra máquina, podremos emplearlo como herramienta de análisis para el modelo que estamos desarrollando. Todo lo que tenemos que hacer es seleccionar la opción de menú “Model | Analyze in Excel” de Visual Studio, que abrirá Excel y cargará el modelo en una tabla dinámica.

Una vez situados en Excel, en el panel “Lista de campos de tabla dinámica” marcaremos la medida GastosTransporte, que deberá quedar situada en el bloque Valores. De igual forma procederemos con el campo SpanishPromotionCategory de la tabla DimPromotion, pero en esta ocasión lo situaremos en el bloque Etiquetas de columna. Para terminar, los campos SalesTerritoryGroup y SalesTerritoryCountry serán colocados en el bloque Etiquetas de fila, de forma que podamos observar esta información en orden jerárquico.

ModelosTabularesSQLServer2012AnalysisServices_10

 

Al mismo tiempo, podemos analizar la cantidad de facturas que la empresa ha emitido por los artículos vendidos, añadiendo la medida FacturasEmitidas a la tabla dinámica, de modo que también obtendremos esta información por los campos actualmente situados en las filas y columnas o bien por otros pertenecientes a las tablas del modelo.

Aquellos lectores que hayan tenido la oportunidad de utilizar PowerPivot, encontrarán interesantes similitudes con este modo de trabajo, ya que en ambos casos, la tecnología subyacente es la misma.

 

Conclusiones

Los modelos de análisis tabular, pertenecientes al nuevo paradigma en la construcción de soluciones de Inteligencia de Negocio, BISM, introducido en el recién aparecido SQL Server 2012, representan una estupenda herramienta con la que desarrollar potentes sistemas de información, utilizando los Servicios de Análisis de SQL Server. En el presente artículo hemos realizado una introducción a esta interesante tecnología, mostrando al lector cómo puede empezar a obtener partido de la misma.

PowerPivot, DAX y Excel. Business Intelligence para todos los públicos

PowerPivot (proyecto también conocido con el nombre clave Gemini) es una tecnología para el análisis de información, cuya particularidad radica en la posibilidad de trabajar con cantidades masivas de datos utilizando Excel como interfaz de usuario, por lo que se convierte en una atractiva oferta, dada la popularidad de esta herramienta perteneciente al paquete de Office. En este artículo realizaremos una introducción a PowerPivot así como a DAX (Data Analysis eXpressions), el lenguaje de expresiones analíticas que lo acompaña, desgranando aquellos aspectos más importantes de esta tecnología, que unida a los tradicionales Servicios de Análisis y MDX (MultiDimensional eXpressions), convertirán a la próxima versión de SQL Server (Denali) en una de las más robustas y potentes soluciones en el campo de la Inteligencia de Negocio o BI (Business Intelligence).

Cada vez con mayor frecuencia, las organizaciones deben realizar diversos análisis acerca de su estado desde las más variadas perspectivas (comercial, financiera, RR.HH., etc.) y sobre una cantidad cada vez más ingente de datos, para lo cual precisan de herramientas adecuadas, que les permitan obtener una información veraz y fiable de dicho estado.

Como ya explicábamos aquí, los cubos de datos constituyen uno de los elementos clave dentro de este ecosistema de análisis de información denominado Business Intelligence (BI).

Sin embargo, un sistema BI, al igual que ocurre en otros ámbitos tales como las aplicaciones Web, de escritorio, etc., precisa de un equipo de desarrollo y de un tiempo para su creación, factor este último que, en ocasiones, se dilata por un periodo mayor del que inicialmente se había estimado.

Ante los problemas que acabamos de plantear, cabría proponer como solución una mejora en las herramientas de desarrollo del sistema de información, las cuales aceleren los tiempos de elaboración del mismo, y permitan manejar un mayor volumen de datos, dado el aumento generalizado de este aspecto en todos los ámbitos corporativos.

También sería necesario potenciar al equipo de desarrollo, incorporando nuevos actores al ciclo de elaboración del sistema, que hasta la fecha habrían desempeñado un papel de meros validadores, pero que pueden aportar un gran valor al mismo, dado su amplio conocimiento acerca de la estructura de las diversas fuentes de datos disponibles en la organización.

PowerPivot llega con el ánimo de aportar soluciones a los retos que acabamos de plantear, cubriendo una serie de áreas específicas en el desarrollo de un sistema de información basado en BI.

 

Autoservicio de inteligencia de negocio (self-service BI)

Cuando hablamos acerca de autoservicio, habitualmente enmarcamos dicho concepto dentro del ámbito de la venta de productos, una actividad en la que una persona, el cliente, se desplaza a un lugar, el supermercado, donde los productos se encuentran expuestos, y una vez allí, el cliente va tomando los productos que necesita, todo ello, generalmente, sin precisar la intervención de intermediario alguno.

Dentro del contexto de la inteligencia de negocio podemos establecer una analogía con el modelo de autoservicio, empleando un concepto denominado autoservicio de inteligencia de negocio o self-service BI, el cual propugna que los usuarios finales (avanzados o de otro tipo de perfil) de un sistema de información, puedan elaborar sus propios análisis e informes sobre los contenidos de dicho sistema, para resolver las necesidades puntuales que emergen en los diversos departamentos de la organización, sin tener que depender en estos casos del equipo de desarrollo del sistema BI corporativo.

Como requisitos encontramos la necesidad de acceso por parte de estos usuarios a aquellas fuentes de datos que precisen para realizar los análisis, siendo igualmente crucial el hecho de que las aplicaciones orientadas al self-service BI tengan una curva de aprendizaje lo más reducida posible, de manera que los usuarios de las mismas puedan comenzar a ser productivos con ellas de forma prácticamente inmediata.

PowerPivot cumple con estos requisitos, ya que al ser una tecnología integrada en Excel, dispone de una amplísima base de usuarios, que ya trabajan con las funcionalidades de la hoja de cálculo y el acceso a fuentes de datos externas, por lo que reduce o elimina el entrenamiento en el uso de la interfaz de usuario, y focaliza el aprendizaje en los aspectos puntuales del nuevo complemento para análisis de datos.

 

Incorporando nuevos perfiles de usuario al ciclo de desarrollo

Además de liberar de una importante carga de trabajo a los equipos de desarrollo, al proporcionar a los usuarios finales herramientas de autoservicio de inteligencia de negocio, obtenemos como beneficio adicional, la posibilidad de que los usuarios avanzados de los diferentes departamentos de la organización, a través del uso de dichas herramientas, colaboren en el ciclo de desarrollo del sistema de información, aportando ideas y sugerencias.

 

VertiPaq. Motor de proceso de datos de alto rendimiento

PowerPivot introduce un nuevo motor de procesamiento de datos: VertiPaq, que a través de un sistema de almacenamiento basado en columnas, implementa una serie de algoritmos de compresión de los datos, mediante los cuales es capaz de cargar millones de registros en memoria.

No obstante, debemos tener en cuenta que dadas las características de ejecución en memoria de VertiPaq, se recomienda, siempre que sea posible, trabajar en un entorno de 64 bits, con el software correspondiente a esta arquitectura: sistema operativo, SQL Server, Office 2010, PowerPivot, etc., puesto que no sufre las limitaciones de direccionamiento de memoria que encontramos en los sistemas de 32 bits.

 

Elementos arquitectónicos

PowerPivot, como podemos apreciar en la siguiente figura, se compone de un ensamblado que es cargado en el proceso de Excel; el motor de VertiPaq, que se ocupa de la carga de datos, la gestión de las consultas y la ejecución de expresiones DAX contra el almacén de datos, así como de las tablas y gráficos dinámicos de PowerPivot; y finalmente, el proveedor OLAP, los objetos de análisis AMO (Analysis Management Objects) y el proveedor ADOMD.NET, que permiten la comunicación con los servicios de análisis, para obtener información a partir de cubos de datos si fuera pertinente.

PowerPivotDAXExcelBIParaTodosPublicos_01

 

Entorno de colaboración. PowerPivot para SharePoint

Utilizando PowerPivot es previsible que el número de modelos de análisis creados por los usuarios crezca notablemente, por lo que resulta muy importante disponer de un mecanismo que permita su administración, en lo que respecta a las tareas de publicación y seguridad de acceso dentro de la organización.

Tal es el objetivo de PowerPivot para SharePoint, un complemento que amplía los servicios de Excel para SharePoint, en los mencionados apartados de administración y colaboración.

Adicionalmente, PowerPivot para SharePoint permite a los usuarios consultar los modelos de PowerPivot publicados en la organización utilizando simplemente un navegador Web, por lo que sólo es necesario instalar Excel y PowerPivot para Excel en las máquinas de los usuarios que vayan a desarrollar modelos de análisis utilizando esta herramienta.

 

PowerPivot. Piedra angular de BISM

Denali, nombre clave de la próxima versión de SQL Server, vendrá acompañada por un nuevo modelo de inteligencia de negocio denominado Modelo Semántico de Inteligencia de Negocio o BISM (Business Intelligence Semantic Model). Este modelo no llega para sustituir ni desplazar a UDM (Unified Dimensional Model), el modelo de inteligencia de negocio existente desde la versión 2005 de SQL Server, sino para coexistir junto a éste, complementando y enriqueciendo la oferta de SQL Server en el apartado BI; permitiéndonos disponer de ambos modelos para trabajar con el que mejor se ajuste a nuestras necesidades, según sea la solución a desarrollar.

Se ha achacado tradicionalmente a SQL Server, refiriéndose a UDM, que su implementación en el apartado BI tiene una difícil curva de aprendizaje, con conceptos complejos: cubos, dimensiones, medidas, etc., y que el tiempo a dedicar para desarrollar una solución, se prolonga en demasía cuando se trata de crear un sencillo sistema de análisis.

En nuestra opinión, dicha curva de aprendizaje no resulta mucho más complicada que la que podamos encontrarnos en otros aspectos del desarrollo de software, aunque sí admitimos, que desde la perspectiva de los desarrolladores acostumbrados a trabajar con un modelo relacional de datos, familiarizarse con el modelo multidimensional propugnado por UDM-OLAP, para construir una solución de análisis de negocio, puede implicar una cierta complejidad inicial hasta que se dominan los conceptos y características principales de dicha tecnología.

BISM llega para solucionar esta problemática, aportando una forma de trabajo más simple, caracterizada por su filosofía relacional sustentada en PowerPivot y VertiPaq. Mientras que BISM se orienta principalmente al desarrollo de soluciones de análisis de ámbito personal (también denominadas Personal BI) o para pequeños equipos de trabajo, que se enmarcan dentro del self-service BI; el modelo UDM se dirige al desarrollo de soluciones BI corporativas de más envergadura, que precisen una mayor planificación.

BISM es un modelo con una arquitectura distribuida en tres capas: acceso a datos, lógica de negocio y modelo de datos.

PowerPivotDAXExcelBIParaTodosPublicos_02

 

La capa de acceso a datos se ocupa de conectar con las fuentes de datos para proceder a su carga, existiendo dos formas de operar con los mismos, en caché o en tiempo real. En el caso de optar por el modo en caché, los datos se cargarán utilizando VertiPaq, mientras que si utilizamos el modo en tiempo real, se dejará esta tarea a la fuente de datos original de la que se extraen.

En la capa de lógica de negocio implementaremos las operaciones de manipulación y tratamiento de los datos que nos permitirán convertirlos en información relevante. Para ello utilizaremos alguno de los lenguajes de expresiones a nuestra disposición: DAX o MDX.

DAX es el lenguaje de expresiones para análisis de datos incorporado en PowerPivot, que utilizaremos para construir consultas contra los datos situados en un almacén de VertiPaq, mientras que MDX es el lenguaje habitualmente empleado en UDM para consultar cubos OLAP.

DAX no es tan potente como MDX, pero es mucho más sencillo de utilizar, siendo su objetivo atacar los datos situados en un modelo de análisis personal o de tipo self-service BI, que teóricamente no debería necesitar el uso de consultas complejas, las cuales quedan reservadas para ser realizadas contra el sistema BI corporativo, que en condiciones normales, se habrá desarrollado mediante UDM.

Finalmente, la capa de modelo de datos será la utilizada por las aplicaciones cliente (Excel, SharePoint, Reporting Services, etc.) para obtener información del modelo que corresponda en cada ocasión: relacional o multidimensional. Entre las herramientas de informes que utilizarán este modelo se encuentra Crescent, que es el nombre clave de un proyecto cuyo objetivo consiste en proporcionar una mejor experiencia de usuario en la visualización de datos y generación de informes contra modelos basados en BISM, y que también verá la luz con Denali.

Algunas voces importantes en la comunidad técnica relacionada con BI han expresado su preocupación acerca del lugar en el que quedará UDM-OLAP, cuando el nuevo modelo BISM haga su presentación oficial en la próxima versión de SQL Server; aunque desde el equipo de desarrollo de Analysis Services han querido enviar un mensaje de tranquilidad, insistiendo en que BISM no supone una sustitución para UDM sino una tecnología complementaria.

 

PowerPivot en la práctica

Después de la revisión de aspectos arquitectónicos realizada en los anteriores apartados, llega la hora de poner manos a la obra para desarrollar un modelo de PowerPivot, que nos sirva para obtener nuestras primeras impresiones acerca del alcance de esta tecnología.

En primer lugar necesitamos tener instalado Office 2010 y descargar e instalar el complemento de PowerPivot para Excel. En el caso de que nuestro sistema sea de 32 bits descargaremos el archivo PowerPivot_for_Excel_x86.msi; si trabajamos en un entorno de 64 bits, el archivo a descargar será PowerPivot_for_Excel_amd64.msi. Para las pruebas de este artículo se ha empleado una máquina virtual con Windows 7 como sistema operativo, 1,5 GB de RAM y procesador Intel Core 2 Duo.

A pesar de que en el nombre de la página de descarga se hace mención a SQL Server 2008 R2, no es necesario tenerlo instalado si vamos a trabajar con otros formatos de datos tales como Access, archivos de texto, etc. No obstante, en nuestro caso sí que usaremos SQL Server como motor de datos, así como la base de datos ContosoRetailDW, de donde descargaremos el archivo ContosoBIdemoBAK_es.exe, que contiene la base de datos en formato de archivo de copia de seguridad.

El motivo por el cual usaremos esta base de datos se debe a que dispone de algunas tablas con varios millones de registros, lo que nos permitirá poner a prueba una de las características más destacadas de PowerPivot: la capacidad de manejar grandes volúmenes de datos.

 

Creación de un modelo

Después de restaurar la base de datos abriremos Excel, haciendo clic a continuación en la pestaña PowerPivot de la cinta de opciones. Entre las opciones de esta pestaña haremos clic en Ventana de PowerPivot, perteneciente al grupo Iniciar, que como su nombre indica, abrirá la ventana de trabajo de PowerPivot.

La primera tarea en la creación del modelo consistirá en conectarnos a una fuente de datos, para hacer una importación de su contenido, por lo que desde la pestaña Página principal de la ventana de PowerPivot, nos situaremos en el grupo Obtener datos externos, y haremos clic en la opción Desde base de datos, que desplegará una lista de elementos, en la que seleccionaremos De SQL Server.

PowerPivotDAXExcelBIParaTodosPublicos_03

 

Como resultado se abrirá el Asistente para la importación de tablas, en el que incluiremos la información para conectarnos a la base de datos ContosoRetailDW. En el paso correspondiente a la selección de tablas marcaremos FactSales, DimDate, DimStore y DimProduct.

PowerPivotDAXExcelBIParaTodosPublicos_04

 

Tras este paso comenzará el proceso de importación, que una vez concluido, mostrará cada una de las tablas importadas en diferentes pestañas de la ventana de PowerPivot.

PowerPivotDAXExcelBIParaTodosPublicos_05

 

Consultando el modelo

El conjunto de tablas que acabamos de importar, nos permitirá realizar un análisis de las ventas de la compañía, en función de los datos relacionados con la tabla de ventas: fechas, productos, almacenes, etc.

Para ello, haremos clic en la opción PivotTable del grupo Informes, creando una tabla dinámica de PowerPivot con la que realizar nuestras operaciones de análisis. Esta acción nos trasladará a la ventana de Excel, donde un cuadro de diálogo nos pedirá las coordenadas para situar la tabla dinámica en la hoja de cálculo. Aceptando los valores por defecto, la nueva tabla dinámica será creada.

PowerPivotDAXExcelBIParaTodosPublicos_06

 

A la derecha de la tabla dinámica encontramos el panel Lista de campos de PowerPivot, en el que seleccionaremos los campos a utilizar para nuestro análisis, ya sea como etiquetas en los ejes de filas y columnas, valores numéricos, filtros, y segmentaciones.

Todos aquellos lectores con experiencia en la creación de cubos OLAP comprobarán, que consultar un modelo de PowerPivot es muy similar a la consulta contra un cubo de datos, pero sin la existencia de un cubo real, ya que en el panel de lista de campos, los elementos del bloque Valores representarían las medidas o métricas del cubo, mientras que los campos del resto de bloques, representarían los atributos de las dimensiones, ya sean visualizados en las filas, columnas o como filtros de la tabla dinámica.

Comenzaremos la creación de nuestra consulta marcando la casilla del campo SalesAmount, de la tabla FactSales. Al ser un campo numérico, automáticamente será colocado como una medida en el bloque Valores del panel de campos. PowerPivot le aplicará entonces, una operación de suma sobre todos los registros de la tabla a la que pertenece.

El estado actual de la tabla dinámica no ofrece, sin embargo, unas grandes posibilidades analíticas, ya que solamente contamos con el total del campo SalesAmount. Necesitamos añadir elementos adicionales a la consulta, relacionados con la tabla FactSales, como por ejemplo la tabla DimProduct.

La tabla DimProduct, además del nombre de cada uno de los productos de la compañía, contiene información adicional sobre los mismos, como puede ser el fabricante, clase, color, tamaño, etc.

Supongamos que queremos averiguar la cifra de ventas por producto, pero en lugar de usar su nombre, necesitamos hacer un análisis por fabricante. Lo que debemos hacer en tal caso es marcar la casilla Manufacturer, perteneciente a la lista de campos de la tabla DimProduct. Esta acción situará dicho campo en el bloque Etiquetas de fila del panel de campos, y sus valores en el eje de filas de la tabla dinámica. De esta forma sabremos cuánto han supuesto las ventas por cada uno de los fabricantes de productos.

Ya que los valores del campo SalesAmount no se muestran formateados, como labor adicional, haremos clic derecho en cualquiera de las celdas de este campo, eligiendo la opción Formato de número… En el cuadro de diálogo de formato seleccionaremos: Moneda, con dos decimales y símbolo de euro; quedando aplicado a todas las celdas de este campo en la tabla dinámica.

PowerPivotDAXExcelBIParaTodosPublicos_07

 

Escribiendo expresiones DAX

Como ya dijimos anteriormente, DAX es el lenguaje de expresiones a través del cual construiremos la lógica de negocio en PowerPivot, tanto a nivel de tabla dinámica como de modelo de datos.

Antes de comenzar a escribir nuestras primeras sentencias en este lenguaje, debemos saber que, sin habernos percatado de ello, ¡ya hemos escrito una expresión en DAX! Si en el panel de campos de la tabla dinámica, dentro del bloque Valores, hacemos clic sobre la medida SalesAmount, y elegimos la opción Editar medida, se mostrará un cuadro de diálogo con la expresión que PowerPivot ha creado automáticamente para este elemento de la tabla dinámica.


=SUM('FactSales'[SalesAmount])

 

PowerPivotDAXExcelBIParaTodosPublicos_08

 

Como podemos ver en la figura anterior, la creación de una expresión DAX es muy similar a la escritura de una fórmula en Excel, ya que a continuación del signo igual, escribiremos una o más funciones con sus correspondientes parámetros, formando con todo ello la expresión. En la expresión que actualmente nos ocupa, la función SUM realizará una suma del campo SalesAmount para todos los registros de la tabla FactSales. Si el nombre de la tabla tiene espacios en blanco u otros caracteres especiales, deberemos encerrarlo entre comillas, en el resto de casos no será necesario. Respecto al nombre del campo, siempre deberá ir encerrado entre corchetes.

Seguidamente abordaremos la escritura de nuestras propias expresiones, a través de dos de los principales elementos de PowerPivot: columnas calculadas y medidas.

En primer lugar crearemos una columna calculada en la tabla FactSales, que obtenga el importe de venta sin descuento. Para ello, en la ventana de PowerPivot nos situaremos en la última columna vacía disponible, escribiendo en la barra de fórmulas lo siguiente:


=FactSales[UnitPrice] * FactSales[SalesQuantity]

Como ayuda en la escritura de expresiones, la característica Autocompletar nos sugerirá en todo momento una lista de funciones, tablas y campos, dependiendo de lo que vayamos escribiendo.

Después de escribir la expresión, la nueva columna se rellenará con los valores resultantes. Haciendo doble clic en la cabecera de la columna asignaremos el nombre ImporteSinDescuento, finalizando así la creación de la columna calculada.

PowerPivotDAXExcelBIParaTodosPublicos_09

 

Otra forma de crear una columna calculada consiste en hacer clic en la opción Agregar, del grupo Columnas, perteneciente a la pestaña Diseño.

Debido a que los campos utilizados en la fórmula, pertenecen a la misma tabla sobre la que estamos creando la columna calculada, en la sintaxis de la expresión podemos obviar el nombre de la tabla, quedando de la siguiente manera:


=[UnitPrice] * [SalesQuantity]

Para poder usar la nueva columna en la tabla dinámica volveremos a la ventana de Excel, encontrándonos con un aviso en el panel Lista de campos de PowerPivot, que nos informa de que se han producido cambios en los campos. Haciendo clic en el botón Actualizar de dicho aviso, se refrescará la lista de campos de la tabla FactSales, agregándose el nuevo campo ImporteSinDescuento, cuya casilla marcaremos para incluirlo en la tabla dinámica.

PowerPivotDAXExcelBIParaTodosPublicos_10

 

Nuestro siguiente paso consistirá en la creación de una medida para calcular, de cada fabricante, el porcentaje de ventas que han supuesto sus productos con respecto al total de ventas de la compañía.

En la pestaña PowerPivot, dentro del grupo Medidas, haremos clic en la opción Nueva medida, abriéndose la ventana Configuración de medida, en la que asignaremos el literal PorcentajeVentas como nombre de la medida, y la siguiente expresión en el campo de fórmula:


=SUM(FactSales[SalesAmount]) / CALCULATE(SUM(FactSales[SalesAmount]), ALL(FactSales))

 

PowerPivotDAXExcelBIParaTodosPublicos_11

 

La primera parte de esta expresión, como ya hemos visto anteriormente, se encarga de sumar el campo SalesAmount. Esta suma se realizará para cada fabricante, al ser Manufacturer el campo utilizado en las etiquetas de fila de la tabla dinámica.

La segunda parte de la expresión devuelve, para todas las filas, la suma total del campo SalesAmount, lo cual conseguimos combinando las funciones CALCULATE, SUM y ALL. La función CALCULATE evalúa la expresión pasada como primer parámetro, que consiste en una suma del campo SalesAmount, y obliga a que dicha suma sea sobre todos los registros de la tabla FactSales mediante el uso de la función ALL, independientemente de los filtros que estén aplicados en la tabla dinámica.

Para terminar, añadiremos la nueva medida a la tabla dinámica, aplicándole el formato de porcentaje.

PowerPivotDAXExcelBIParaTodosPublicos_12

 

Filtros y segmentaciones

Nuestra tabla dinámica muestra, organizadas por fabricantes de productos, una serie de medidas obtenidas a partir de campos y cálculos de la tabla FactSales, perteneciente al modelo de datos de PowerPivot.

Los resultados de estas medidas se elaboran a partir de todos los registros de la mencionada tabla, pero también nos interesará en algún momento, disponer de la posibilidad de aplicar filtros sobre la información que estamos consultando, con el fin de obtener diferentes perspectivas de análisis.

Las tablas dinámicas de PowerPivot, proporcionan al usuario los filtros y segmentaciones como herramientas de filtrado de los datos. A continuación explicaremos al lector su utilización, como medio de acotar los resultados del informe.

Supongamos que queremos filtrar la información de la tabla dinámica, según el tipo de almacén en el que se encuentran ubicados los productos; dato que obtenemos a partir del campo StoreType perteneciente a la tabla DimStore.

Para crear un filtro de estas características, en el panel lista de campos desplegaremos los campos de la tabla DimStore, y arrastraremos el campo StoreType hasta el bloque Filtro de informe. Como resultado, se añadirá este filtro en la parte superior de la tabla dinámica.

Haciendo clic en su botón de despliegue y seleccionando el valor Almacén, se activará el filtro, provocando que todas las celdas numéricas de la tabla dinámica muestren la información de ventas, pero en función del filtro establecido.

PowerPivotDAXExcelBIParaTodosPublicos_13

 

Las segmentaciones, por otra parte, se comportan como los filtros, aunque su manejo por parte del usuario es ligeramente distinto.

Vamos a crear una segmentación que nos permita filtrar los resultados por el año de venta, para lo cual desplegaremos los campos de la tabla DimDate, arrastrando el campo CalendarYear hasta el bloque Segmentaciones de datos horizontales. Esta operación creará la segmentación con todos los valores de filtro, situándola encima de la tabla dinámica.

Los valores de la segmentación que aparecen en un tono de color más oscuro, son aquellos con los cuales podremos realizar filtros efectivos; mientras que el uso de los que tienen un tono más claro no producirá resultados, ya que no existen ventas realizadas en dichos años.

Si queremos utilizar más de un valor de la segmentación, mantendremos pulsada la tecla CTRL mientras hacemos clic en los diferentes valores. El resultado del filtro no será efectivo hasta que no soltemos la tecla CTRL. Para eliminar todos los filtros de la segmentación haremos clic en el icono con forma de embudo, situado en su parte superior derecha.

PowerPivotDAXExcelBIParaTodosPublicos_14

 

Creación de un gráfico dinámico

En determinadas circunstancias, la representación numérica de datos proporcionada por una tabla dinámica, puede no ser suficiente para analizar el contenido del modelo de PowerPivot, por lo que también contamos con los gráficos dinámicos, como vía alternativa en la visualización de información.

Podemos crear un gráfico dinámico desde cero, o bien usar una tabla dinámica ya existente como base del gráfico. En nuestro caso optaremos por este último método, para lo cual, una vez posicionados en la tabla dinámica, haremos clic en la pestaña Opciones, perteneciente a la categoría Herramientas de tabla dinámica, y seguidamente, seleccionaremos la opción Gráfico dinámico, situada en el grupo Herramientas. Se abrirá el cuadro de diálogo Insertar gráfico, que nos ofrecerá un amplio abanico de tipos de gráfico para insertar en la hoja de cálculo. Una vez hecha nuestra elección, aceptaremos este diálogo, insertándose el gráfico junto a la tabla dinámica.

A partir de este momento, tanto la tabla como el gráfico estarán sincronizados, de forma que las modificaciones de filtros, medidas, etiquetas en ejes, etc., que realicemos en uno, tendrán su reflejo inmediato en el otro. Seguidamente vemos el aspecto de nuestro informe, después de eliminar dos medidas del gráfico y de haber modificado el filtro StoreType.

PowerPivotDAXExcelBIParaTodosPublicos_15

 

Conclusiones

PowerPivot representa una tecnología con grandes posibilidades de convertirse en una pieza importante del conjunto de herramientas disponibles para el desarrollo de sistemas BI con SQL Server. Si unimos su capacidad en el manejo de grandes volúmenes de datos, la potencia de consulta de su lenguaje de expresiones, y el hecho de utilizar Excel como interfaz de usuario, obtenemos un producto que permite crear a los usuarios sus propios modelos de análisis, para resolver necesidades puntuales de información. Al mismo tiempo, dichos modelos pueden servir de ayuda a los equipos de desarrollo de inteligencia de negocio, en la construcción de los sistemas de información corporativos.

Tablas numéricas auxiliares (Tally Tables) en acción. Creación dinámica de sentencias con PIVOT

El presente artículo, que cierra nuestro monográfico dedicado a la construcción y usos prácticos de las tablas numéricas auxiliares o tally tables, se orienta a la utilización de esta técnica como elemento de soporte en la construcción de sentencias que utilicen el operador PIVOT, y en las cuales debamos generar un número considerable de columnas que sigan un determinado patrón, ahorrando por consiguiente una importante cantidad de escritura de código manual. Comencemos pues por una sencilla introducción que nos proporcione una idea aproximada de la situación a resolver.

Partimos de la siguiente consulta contra la tabla FactInternetSales, perteneciente a la base de datos AdventureWorksDW2012, que muestra las cifras de ventas por código territorial y año.


SELECT
SalesTerritoryKey,
YEAR(OrderDate) AS Anualidad,
SalesAmount
FROM FactInternetSales

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_01

 

Necesitamos adaptar esta sentencia, de forma que mediante una operación de pivotado, creemos tantas columnas como años existan en OrderDate. Sumando para cada combinación de SalesTerritoryKey y año, el valor de la columna SalesAmount, tal y como describe el esquema de la siguiente figura.

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_02

 

Teniendo en cuenta estos requerimientos, la consulta con PIVOT quedaría de la siguiente forma.


SELECT
SalesTerritoryKey,
[2005],
[2006],
[2007],
[2008]
FROM
(
  SELECT
  SalesTerritoryKey,
  SalesAmount,
  YEAR(OrderDate) AS Anualidad
  FROM FactInternetSales
) AS tblDatosOrigen
PIVOT
(
  SUM(SalesAmount)
  FOR Anualidad IN (
    [2005],
    [2006],
    [2007],
    [2008]
  )
) AS tblPivot

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_03

 

Si además quisiéramos agrupar las cifras de ventas por los productos que pertenecieran a las subcategorías de bicicletas, añadiríamos a la sentencia las tablas DimProduct y DimProductSubcategory, combinándolas para obtener los nuevos datos a mostrar.


SELECT
EnglishProductSubcategoryName,
[2005],
[2006],
[2007],
[2008]
FROM
(
  SELECT
  FIS.SalesAmount,
  YEAR(FIS.OrderDate) AS Anualidad,
  DPS.EnglishProductSubcategoryName,
  DPS.ProductSubcategoryKey
  FROM FactInternetSales AS FIS
  INNER JOIN DimProduct AS DP
  ON FIS.ProductKey = DP.ProductKey
  INNER JOIN DimProductSubcategory AS DPS
  ON DP.ProductSubcategoryKey = DPS.ProductSubcategoryKey
  WHERE DPS.ProductSubcategoryKey BETWEEN 1 AND 3
) AS tblDatosOrigen
PIVOT
(
  SUM(SalesAmount)
  FOR Anualidad IN (
    [2005],
    [2006],
    [2007],
    [2008]
  )
) AS tblPivot;

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_04

 

Hasta aquí todo bien, la cantidad de código a escribir no es muy elevada, pero ahora se nos plantea un nuevo requisito, consistente en mostrar los resultados de ventas por año y mes, lo que supone modificar la sentencia para añadir al código toda la combinatoria de columnas de año y mes, como vemos a continuación.


SELECT
EnglishProductSubcategoryName,
[2005_1],
[2005_2],
[2005_3],
[2005_4],
[2005_5],
[2005_6],
[2005_7],
[2005_8],
[2005_9],
[2005_10],
[2005_11],
[2005_12],
[2006_1],
[2006_2],
[2006_3],
[2006_4],
[2006_5],
[2006_6],
[2006_7],
[2006_8],
[2006_9],
[2006_10],
[2006_11],
[2006_12],
[2007_1],
[2007_2],
[2007_3],
[2007_4],
[2007_5],
[2007_6],
[2007_7],
[2007_8],
[2007_9],
[2007_10],
[2007_11],
[2007_12],
[2008_1],
[2008_2],
[2008_3],
[2008_4],
[2008_5],
[2008_6],
[2008_7],
[2008_8],
[2008_9],
[2008_10],
[2008_11],
[2008_12]
FROM
(
  SELECT
  FIS.SalesAmount,
  CONVERT(varchar(4), YEAR(FIS.OrderDate)) + '_' + CONVERT(varchar(2),
  MONTH(FIS.OrderDate)) AS AnualidadMes,
  DPS.EnglishProductSubcategoryName
  FROM FactInternetSales AS FIS
  INNER JOIN DimProduct AS DP
  ON FIS.ProductKey = DP.ProductKey
  INNER JOIN DimProductSubcategory AS DPS
  ON DP.ProductSubcategoryKey = DPS.ProductSubcategoryKey
  WHERE DPS.ProductSubcategoryKey BETWEEN 1 AND 3
) AS tblDatosOrigen
PIVOT
(
  SUM(SalesAmount)
  FOR AnualidadMes IN (
    [2005_1],
    [2005_2],
    [2005_3],
    [2005_4],
    [2005_5],
    [2005_6],
    [2005_7],
    [2005_8],
    [2005_9],
    [2005_10],
    [2005_11],
    [2005_12],
    [2006_1],
    [2006_2],
    [2006_3],
    [2006_4],
    [2006_5],
    [2006_6],
    [2006_7],
    [2006_8],
    [2006_9],
    [2006_10],
    [2006_11],
    [2006_12],
    [2007_1],
    [2007_2],
    [2007_3],
    [2007_4],
    [2007_5],
    [2007_6],
    [2007_7],
    [2007_8],
    [2007_9],
    [2007_10],
    [2007_11],
    [2007_12],
    [2008_1],
    [2008_2],
    [2008_3],
    [2008_4],
    [2008_5],
    [2008_6],
    [2008_7],
    [2008_8],
    [2008_9],
    [2008_10],
    [2008_11],
    [2008_12]
  )
) AS tblPivot;

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_05

 

Como acabamos de comprobar, el volumen de código a escribir se ha incrementado sustancialmente debido a la necesidad de especificar las columnas que contienen las ventas por año y mes. En el caso de que debamos desarrollar varios escenarios de similares características, su mantenimiento puede convertirse en una tarea bastante molesta y propensa a incurrir en errores. Por este motivo, el uso de una tabla numérica como herramienta de ayuda en la elaboración del código puede resultar una opción a tener en cuenta.

En primer lugar necesitamos la mencionada tabla numérica con base 1, a la que denominaremos Numeros (al igual que en el resto de artículos que sobre este tema  hemos dedicado en el blog), conteniendo al menos 2008 filas, ya que se trata del máximo valor para el año en la columna OrderDate de la tabla FactInternetSales.

Seguidamente crearemos una  sentencia basada en dos expresiones de tabla (CTE) contra la tabla numérica. La primera devolverá el rango de años existente en la columna OrderDate y la segunda los números de mes. A continuación cruzaremos ambas expresiones mediante CROSS JOIN, para comprobar que obtenemos correctamente las combinaciones de año y mes.


WITH
cteAnualidades AS
(
  SELECT NumeroID AS Anualidad
  FROM Numeros
  WHERE NumeroID BETWEEN
  (SELECT MIN(YEAR(OrderDate)) FROM FactInternetSales)
  AND
  (SELECT MAX(YEAR(OrderDate)) FROM FactInternetSales)
),
cteMeses AS
(
  SELECT NumeroID AS Mes
  FROM Numeros
  WHERE NumeroID BETWEEN 1 AND 12
)
SELECT Anualidad, Mes
FROM cteAnualidades
CROSS JOIN cteMeses
ORDER BY Anualidad,Mes

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_06

 

Esta consulta la integraremos posteriormente en otra de tipo dinámico, siendo esta última la que contenga el operador PIVOT. Para recoger y dar formato al resultado, en aquella parte de la sentencia que devuelve el cruce de años y meses emplearemos una variable de cadena (@sAnualidadesMeses) que reutilizaremos en dos ocasiones dentro de la construcción de la consulta principal. Veamos en primer lugar el código necesario para obtener la cadena con las definiciones de columna año-mes.


DECLARE @sSQL AS nvarchar(2000);
DECLARE @sAnualidadesMeses AS nvarchar(1000);

SET @sAnualidadesMeses = '';

WITH
cteAnualidades AS
(
  SELECT NumeroID AS Anualidad
  FROM Numeros
  WHERE NumeroID BETWEEN
  (SELECT MIN(YEAR(OrderDate)) FROM FactInternetSales)
  AND
  (SELECT MAX(YEAR(OrderDate)) FROM FactInternetSales)
),
cteMeses AS
(
  SELECT NumeroID AS Mes
  FROM Numeros
  WHERE NumeroID BETWEEN 1 AND 12
)
SELECT @sAnualidadesMeses += '[' + CONVERT(varchar(4), Anualidad) + '_' +
CONVERT(varchar(2), Mes) + '],'
FROM cteAnualidades
CROSS JOIN cteMeses;

PRINT @sAnualidadesMeses;

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_07

 

Y ahora, la sentencia al completo.


DECLARE @sSQL AS nvarchar(2000);
DECLARE @sAnualidadesMeses AS nvarchar(1000);

SET @sAnualidadesMeses = '';

WITH
cteAnualidades AS
(
  SELECT NumeroID AS Anualidad
  FROM Numeros
  WHERE NumeroID BETWEEN
  (SELECT MIN(YEAR(OrderDate)) FROM FactInternetSales)
  AND
  (SELECT MAX(YEAR(OrderDate)) FROM FactInternetSales)
),
cteMeses AS
(
  SELECT NumeroID AS Mes
  FROM Numeros
  WHERE NumeroID BETWEEN 1 AND 12
)
SELECT @sAnualidadesMeses += '[' + CONVERT(varchar(4), Anualidad) + '_' +
CONVERT(varchar(2), Mes) + '],'
FROM cteAnualidades
CROSS JOIN cteMeses;

SET @sAnualidadesMeses = LEFT(@sAnualidadesMeses, LEN(@sAnualidadesMeses) - 1);

SET @sSQL = 'SELECT EnglishProductSubcategoryName,' + @sAnualidadesMeses +
' FROM (SELECT FIS.SalesAmount,' +
'CONVERT(varchar(4), YEAR(FIS.OrderDate)) + ''_'' + 
CONVERT(varchar(2),MONTH(FIS.OrderDate)) AS AnualidadMes,' +
'DPS.EnglishProductSubcategoryName ' +
'FROM FactInternetSales AS FIS ' +
'INNER JOIN DimProduct AS DP ' +
'ON FIS.ProductKey = DP.ProductKey ' +
'INNER JOIN DimProductSubcategory AS DPS ' +
'ON DP.ProductSubcategoryKey = DPS.ProductSubcategoryKey ' +
'WHERE DPS.ProductSubcategoryKey BETWEEN 1 AND 3) AS tblDatosOrigen ' +
'PIVOT (' +
'SUM(SalesAmount) ' +
'FOR AnualidadMes IN (' + @sAnualidadesMeses + ')) AS tblPivot';

EXECUTE sp_executesql @sSQL;

Comprobaremos que hemos obtenido el mismo resultado pero escribiendo una menor cantidad de código.

Podemos concluir, una vez revisadas las aplicaciones prácticas que hemos ido describiendo tanto en el presente como en los anteriores artículos sobre las tablas numéricas auxiliares, que representan una poderosa herramienta con la que contar en nuestro arsenal de utilidades para SQL Server. Espero que en alguna ocasión le resulten de ayuda al lector en su trabajo.

Tablas numéricas auxiliares (Tally Tables) en acción. Depuración de caracteres delimitadores en columnas de tipo texto

Como continuación al conjunto de artículos dedicados a la aplicación práctica de las tablas numéricas auxiliares, en la actual entrega, al igual que en el anterior artículo, centraremos nuestro objetivo en el tratamiento de los caracteres que actúan como delimitador dentro de un texto, pero en este caso el objetivo consistirá en eliminar los delimitadores sobrantes del mismo, para lo cual, en primer lugar expondremos el escenario de trabajo sobre el que actuaremos.

 

El escenario de trabajo

Supongamos que debemos cargar una tabla de una base de datos con el contenido proveniente de un archivo de texto, en el que las columnas vienen determinadas por un carácter delimitador, por ejemplo, el carácter de barra vertical o pipe. La siguiente figura muestra algunas líneas de este archivo, que incluye los nombres de las columnas en su primera fila.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_01

 

Observando con detalle este archivo comprobaremos que cada fila dispone de seis caracteres delimitadores, que a su vez definen siete campos o columnas, como vemos gráficamente en la siguiente imagen.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_19

 

Preparación del archivo de datos origen

Los datos de este archivo se han obtenido a partir de varias columnas de la tabla DimReseller, perteneciente a la base de datos AdventureWorksDW2012. Para preparar este archivo, desde SQL Server Management Studio haremos clic derecho en dicha base de datos, eligiendo la opción Tasks | Export Data, lo que abrirá el asistente de exportación de datos de SQL Server 2014.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_02

 

Después de hacer clic en el botón Next para pasar la página de presentación del asistente, en el primer paso del mismo seleccionaremos la fuente de datos desde la que vamos a realizar la exportación.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_03

 

A continuación seleccionaremos Flat File Destination como tipo de destino de la exportación, así como el nombre del archivo en el que se realizará el volcado de los datos. También habilitaremos la inclusión de los nombres de las columnas de la tabla en la primera fila del archivo.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_04

 

En el siguiente paso indicaremos que la exportación se realizará mediante una consulta contra la base de datos.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_05

 

Incluyendo dicha consulta a continuación.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_06

 

En el próximo paso seleccionaremos el carácter pipe como delimitador de columna.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_07

 

A partir de este punto avanzaremos hasta el último paso del asistente y haremos clic en el botón Finish para generar el archivo de texto con el resultado de la sentencia.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_08

 

La carga del archivo en la base de datos

Una vez obtenido el archivo, en la base de datos que estemos utilizando para realizar nuestras pruebas crearemos una tabla con la siguiente estructura.


CREATE TABLE Reseller
(
ResellerKey int NOT NULL,
ResellerAlternateKey varchar(15) NULL,
BusinessType varchar(20) NOT NULL,
ResellerName varchar(50) NOT NULL,
NumberEmployees int NULL,
OrderFrequency char(1) NULL,
BankName varchar(50) NULL
)

Mientras que para traspasar el contenido del archivo de texto (situado en una supuesta ruta C:\Datos) a esta tabla utilizaremos la instrucción BULK INSERT.


BULK INSERT Reseller
FROM 'C:\Datos\DimReseller.txt'
WITH (FIELDTERMINATOR = '|', CODEPAGE = 'ACP', FIRSTROW = 2)

¿Y si el archivo de datos no está correctamente normalizado?

En condiciones normales el anterior bloque de código se ejecutaría sin problemas, cargando el contenido del archivo en la tabla. Pero supongamos que los datos que se exportan al archivo se recogen a partir de un formulario en el que el campo correspondiente a la columna ResellerName (columna número cuatro) de la tabla es de texto libre, pudiendo el usuario introducir cualquier carácter, y existiendo, por tal motivo, algunos registros en los que en el contenido de dicho campo podemos encontrarnos con los mismos caracteres pipe que utilizamos como delimitador de columna al exportar los datos al archivo.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_09

 

Esto es algo que desvirtúa completamente nuestra operación de importación de datos desde el archivo, ya que provoca errores como los siguientes.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_10

 

Ante una situación como esta resulta necesario desarrollar un proceso que elimine, de los valores que han de traspasarse a la columna ResellerName, los caracteres pipe que están provocando el conflicto en la operación de importación. Por lo que en primer lugar, vamos a tomar el contenido de una de las filas del archivo que producen el error (la fila 3, por ejemplo) y trabajar sobre dicho texto hasta obtener el código que realice la depuración de los caracteres sobrantes. A continuación se muestra la fila seleccionada.


3|AW00000003|Warehouse|Advan|ced Bi|ke Comp|one|nts|40|Q|Primary International

 

Tabla numérica auxiliar al rescate

Para comenzar, necesitamos averiguar, dentro de la cadena, las posiciones de inicio y fin del campo a depurar, por lo que aquí es donde recurriremos al uso de una tabla numérica auxiliar con base uno (primera fila con valor 1), y compuesta por 100 filas. Como primera aproximación al problema calcularemos las posiciones de todos los delimitadores de la manera que vemos en el siguiente bloque de código.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = '3|AW00000003|Warehouse|Advan|ced Bi|ke Comp|one|nts|40|Q|Primary International';
SET @sDelimitador = '|';

SELECT
ROW_NUMBER() OVER(ORDER BY NumeroID) AS NumeroDelimitador,
NumeroID AS PosicionDelimitador
FROM Numeros
WHERE SUBSTRING(@sTexto,NumeroID,1) = @sDelimitador
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

El conjunto de resultados obtenido podemos verlo a continuación.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_11

 

Analizando la cadena asignada a la variable @sTexto observaremos que el valor para el campo ResellerName contiene cuatro caracteres pipe que necesitamos eliminar.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_12

 

Los números de orden del delimitador de inicio y fin para este campo son el 3 y 8, que corresponden a las posiciones 23 y 52 en el cómputo total de caracteres de la cadena, como muestra el siguiente diagrama.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_13

 

Selección de delimitadores inicial y final

Lo que haremos a continuación será incluir el código que calcula los delimitadores en una expresión común de tabla (cteDelimitadores), seleccionando desde la consulta externa aquellos delimitadores que anteriormente hemos indicado que representan el inicio y fin del campo a depurar. Para facilitar el desarrollo del código asignaremos a variables los datos con los que vamos a trabajar: cadena, carácter delimitador, número de campo sobre el que vamos a realizar la depuración y número total de campos existente en la cadena.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
DECLARE @nCampoDepurar AS int;
DECLARE @nTotalCampos AS int;

SET @sTexto = '3|AW00000003|Warehouse|Advan|ced Bi|ke Comp|one|nts|40|Q|Primary International';
SET @sDelimitador = '|';
SET @nCampoDepurar = 4;
SET @nTotalCampos = 7;

WITH
cteDelimitadores AS
(
  SELECT
  ROW_NUMBER() OVER(ORDER BY NumeroID) AS NumeroDelimitador,
  NumeroID AS PosicionDelimitador
  FROM Numeros
  WHERE SUBSTRING(@sTexto,NumeroID,1) = @sDelimitador
  ORDER BY NumeroID
  OFFSET 0 ROWS
  FETCH FIRST LEN(@sTexto) ROWS ONLY
)
SELECT
NumeroDelimitador,
PosicionDelimitador
FROM cteDelimitadores
WHERE NumeroDelimitador IN (
  @nCampoDepurar - 1),
  (
    ((SELECT MAX(NumeroDelimitador) FROM cteDelimitadores) - 
    (@nTotalCampos - @nCampoDepurar)) + 1
  )
)

 

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_14

 

Averiguadas las posiciones de la subcadena a obtener, y al igual que hicimos en el paso anterior, situaremos el código para calcular las posiciones de inicio y fin en una expresión de tabla (cteDelimitadoresInicioFin), volviendo a generar el número de orden del delimitador, ya que llegados a este punto, el conjunto de resultados con el que vamos a trabajar estará compuesto únicamente por dos filas, facilitando así el inminente trabajo con SUBSTRING, puesto que de antemano sabremos que la fila con el número de delimitador 1 contendrá la primera posición de la subcadena, y la fila con el delimitador 2 corresponderá a la última posición.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
DECLARE @nCampoDepurar AS int;
DECLARE @nTotalCampos AS int;

SET @sTexto = '3|AW00000003|Warehouse|Advan|ced Bi|ke Comp|one|nts|40|Q|Primary International';
SET @sDelimitador = '|';
SET @nCampoDepurar = 4;
SET @nTotalCampos = 7;

WITH
cteDelimitadores AS
(
  SELECT
  ROW_NUMBER() OVER(ORDER BY NumeroID) AS NumeroDelimitador,
  NumeroID AS PosicionDelimitador
  FROM Numeros
  WHERE SUBSTRING(@sTexto,NumeroID,1) = @sDelimitador
  ORDER BY NumeroID
  OFFSET 0 ROWS
  FETCH FIRST LEN(@sTexto) ROWS ONLY
),
cteDelimitadoresInicioFin AS
(
  SELECT
  ROW_NUMBER() OVER(ORDER BY NumeroDelimitador) AS NumeroDelimitador,
  PosicionDelimitador
  FROM cteDelimitadores
  WHERE NumeroDelimitador IN (
    (@nCampoDepurar - 1),
    (
      ((SELECT MAX(NumeroDelimitador) FROM cteDelimitadores) - 
      (@nTotalCampos - @nCampoDepurar)) + 1
    )
  )
)
SELECT * 
FROM cteDelimitadoresInicioFin

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_15

 

Extracción de la subcadena incorrecta

A continuación agregaremos a la consulta una nueva expresión de tabla (cteCampoOriginal) en la que emplearemos la función SUBSTRING para extraer la subcadena a depurar. Los parámetros start y length de SUBSTRING los obtendremos a partir de los valores de la expresión de tabla anterior, encargada de calcular las posiciones inicial y final de los delimitadores.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
DECLARE @nCampoDepurar AS int;
DECLARE @nTotalCampos AS int;

SET @sTexto = '3|AW00000003|Warehouse|Advan|ced Bi|ke Comp|one|nts|40|Q|Primary International';
SET @sDelimitador = '|';
SET @nCampoDepurar = 4;
SET @nTotalCampos = 7;

WITH
cteDelimitadores AS
(
  SELECT
  ROW_NUMBER() OVER(ORDER BY NumeroID) AS NumeroDelimitador,
  NumeroID AS PosicionDelimitador
  FROM Numeros
  WHERE SUBSTRING(@sTexto,NumeroID,1) = @sDelimitador
  ORDER BY NumeroID
  OFFSET 0 ROWS
  FETCH FIRST LEN(@sTexto) ROWS ONLY
),
cteDelimitadoresInicioFin AS
(
  SELECT
  ROW_NUMBER() OVER(ORDER BY NumeroDelimitador) AS NumeroDelimitador,
  PosicionDelimitador
  FROM cteDelimitadores
  WHERE NumeroDelimitador IN (
    (@nCampoDepurar - 1),
    (
      ((SELECT MAX(NumeroDelimitador) FROM cteDelimitadores) - 
      (@nTotalCampos - @nCampoDepurar)) + 1
    )
  )
),
cteCampoOriginal AS
(
  SELECT
  SUBSTRING(
    @sTexto,
    (SELECT PosicionDelimitador + 1 FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 1),
    (
      (SELECT PosicionDelimitador FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 2) -
      (SELECT PosicionDelimitador FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 1)
    ) - 1
  ) AS CampoOriginal
)
SELECT *
FROM cteCampoOriginal

 

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_16

 

Depurando el exceso de delimitadores

Para eliminar los caracteres pipe de la subcadena que acabamos de obtener utilizaremos la función REPLACE dentro de una última expresión de tabla (cteCampoDepurado), la cual devolverá tanto la subcadena original como la ya depurada sin delimitadores. En la siguiente sentencia vemos el código al completo, donde podemos observar la evolución de nuestro proceso, desde el texto completo original al depurado, pasando por la subcadena original y la depurada.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
DECLARE @nCampoDepurar AS int;
DECLARE @nTotalCampos AS int;

SET @sTexto = '3|AW00000003|Warehouse|Advan|ced Bi|ke Comp|one|nts|40|Q|Primary International';
SET @sDelimitador = '|';
SET @nCampoDepurar = 4;
SET @nTotalCampos = 7;

WITH
cteDelimitadores AS
(
  SELECT
  ROW_NUMBER() OVER(ORDER BY NumeroID) AS NumeroDelimitador,
  NumeroID AS PosicionDelimitador
  FROM Numeros
  WHERE SUBSTRING(@sTexto,NumeroID,1) = @sDelimitador
  ORDER BY NumeroID
  OFFSET 0 ROWS
  FETCH FIRST LEN(@sTexto) ROWS ONLY
),
cteDelimitadoresInicioFin AS
(
  SELECT
  ROW_NUMBER() OVER(ORDER BY NumeroDelimitador) AS NumeroDelimitador,
  PosicionDelimitador
  FROM cteDelimitadores
  WHERE NumeroDelimitador IN (
    (@nCampoDepurar - 1),
    (
      ((SELECT MAX(NumeroDelimitador) FROM cteDelimitadores) -
      (@nTotalCampos - @nCampoDepurar)) + 1
    )
  )
),
cteCampoOriginal AS
(
  SELECT
  SUBSTRING(
    @sTexto,
    (SELECT PosicionDelimitador + 1 FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 1),
    (
      (SELECT PosicionDelimitador FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 2) -
      (SELECT PosicionDelimitador FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 1)
    ) - 1
  ) AS CampoOriginal
),
cteCampoDepurado AS
(
  SELECT
  CampoOriginal,
  REPLACE(CampoOriginal,@sDelimitador,'') AS CampoDepurado
  FROM cteCampoOriginal
)
SELECT
@sTexto AS TextoOriginal,
CampoOriginal,
CampoDepurado,
REPLACE(@sTexto,CampoOriginal,CampoDepurado) AS TextoDepurado
FROM cteCampoDepurado

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_17

 

Organizar el código para su reutilización

Para poder reutilizar todo este código en diferentes escenarios de trabajo vamos a situarlo en una función de tipo escalar que llamaremos DepurarDelimitadores. En los parámetros a pasar a esta función encontramos la cadena que contiene el valor a depurar, el carácter delimitador del texto, el número del campo a depurar y el número total de campos a tratar. El valor de retorno de la función, cuyo código vemos a continuación, será la cadena pasada en el primer parámetro una vez quitados los delimitadores sobrantes de la subcadena correspondiente al campo a depurar.


CREATE FUNCTION DepurarDelimitadores(@sTexto AS varchar(100),
@sDelimitador AS char(1),
@nCampoDepurar AS int,
@nTotalCampos AS int
)
RETURNS varchar(100)
AS
BEGIN

DECLARE @sTextoDepurado AS varchar(100);

WITH
cteDelimitadores AS
(
  SELECT
  ROW_NUMBER() OVER(ORDER BY NumeroID) AS NumeroDelimitador,
  NumeroID AS PosicionDelimitador
  FROM Numeros
  WHERE SUBSTRING(@sTexto,NumeroID,1) = @sDelimitador
  ORDER BY NumeroID
  OFFSET 0 ROWS
  FETCH FIRST LEN(@sTexto) ROWS ONLY
),
cteDelimitadoresInicioFin AS
(
  SELECT
  ROW_NUMBER() OVER(ORDER BY NumeroDelimitador) AS NumeroDelimitador,
  PosicionDelimitador
  FROM cteDelimitadores
  WHERE NumeroDelimitador IN (
    (@nCampoDepurar - 1),
    (
      ((SELECT MAX(NumeroDelimitador) FROM cteDelimitadores) -
      (@nTotalCampos - @nCampoDepurar)) + 1
    )
  )
),
cteCampoOriginal AS
(
  SELECT
  SUBSTRING(
    @sTexto,
    (SELECT PosicionDelimitador + 1 FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 1),
    (
      (SELECT PosicionDelimitador FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 2) -
      (SELECT PosicionDelimitador FROM cteDelimitadoresInicioFin WHERE NumeroDelimitador = 1)
    ) - 1
  ) AS CampoOriginal
),
cteCampoDepurado AS
(
  SELECT
  CampoOriginal,
  REPLACE(CampoOriginal,@sDelimitador,'') AS CampoDepurado
  FROM cteCampoOriginal
)
SELECT
@sTextoDepurado = REPLACE(@sTexto,CampoOriginal,CampoDepurado)
FROM cteCampoDepurado;

RETURN @sTextoDepurado;

END

Una vez creada la función vamos a probar su funcionamiento utilizando la cadena con la que hemos experimentado a lo largo del desarrollo de todo el proceso.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_18

 

Limpieza de los datos origen

Finalizado el desarrollo del código encargado de eliminar los delimitadores sobrantes, la siguiente tarea consistirá en importar el archivo de texto a una tabla formada por una sola columna, realizando la depuración del excedente de caracteres delimitadores sobre esta nueva tabla. A continuación vemos las sentencias encargadas de la creación y carga de datos para dicha tabla.

CREATE TABLE ColumnaTexto
(
Columna varchar(200) NULL
)

BULK INSERT ColumnaTexto
FROM ‘C:\Datos\DimReseller.txt’
WITH (CODEPAGE = ‘ACP’, FIRSTROW = 2)

Analicemos ahora cómo aplicar la función DepurarDelimitadores para eliminar los delimitadores innecesarios en la tabla ColumnaTexto. Recordemos que cada fila de esta tabla tiene seis caracteres delimitadores (exceptuando aquellas con exceso de dicho carácter), que a su vez definen siete campos.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_19

 

Si queremos averiguar, por lo tanto, las filas de la tabla a las que hay que aplicar la depuración, tenemos que encontrar aquellas que contengan más de seis delimitadores; lo que conseguiremos calculando la diferencia entre la longitud total de caracteres de cada fila menos dicha longitud sin los caracteres delimitadores. Para realizar estos cálculos emplearemos de forma combinada las funciones LEN y REPLACE, tal y como muestra la siguiente sentencia.

SELECT
Columna,
(LEN(Columna) – LEN(REPLACE(Columna,’|’,”))) AS NumeroDelimitadores
FROM ColumnaTexto

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_20

 

Adaptando el código anterior dentro de una sentencia de actualización que utilice una expresión de tabla para seleccionar aquellas filas con exceso de delimitadores, la operación de modificación sobre la tabla ColumnaTexto empleando la función DepurarDelimitadores quedaría de la siguiente manera para depurar el cuarto campo de los siete existentes en cada fila.


WITH cteColumnaTexto AS
(
  SELECT
  Columna
  FROM ColumnaTexto
  WHERE (LEN(Columna) - LEN(REPLACE(Columna,'|',''))) > 6
)
UPDATE cteColumnaTexto
SET Columna = dbo.DepurarDelimitadores(Columna,'|',4,7);

Tras la ejecución de la anterior sentencia, las filas con la cantidad incorrecta de delimitadores habrán sido corregidas.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_21

 

Traslado de los datos depurados a la tabla definitiva

Hemos llegado prácticamente al final del proceso. Tan sólo resta traspasar los valores separados por delimitadores de cada fila de la tabla ColumnaTexto a la tabla Reseller, siendo esta una tarea que realizaremos en dos fases.

En la primera fase, ayudándonos de la función ParticionarCadena que creamos en el artículo anterior y de la instrucción CROSS APPLY, generaremos un conjunto de resultados a partir de la tabla ColumnaTexto, donde cada fila de dicha tabla se subdividirá en siete filas. De cada una de estas nuevas filas, la columna Subcadena contendrá el valor de uno de los campos de una fila de la tabla ColumnaTexto, y la columna NumCampoEnFila servirá de identificador del número de columna de la tabla Reseller, a la que más adelante traspasaremos el valor. A continuación se muestra la sentencia que debemos utilizar.

SELECT
C.Columna,
SubC.Subcadena,
ROW_NUMBER() OVER(PARTITION BY C.Columna ORDER BY C.Columna) AS
NumCampoEnFila
FROM ColumnaTexto AS C
CROSS APPLY dbo.ParticionarCadena(C.Columna,’|’) AS SubC

 

El siguiente diagrama ilustra esta parte del proceso.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_22

 

En la segunda fase situaremos la sentencia anterior en una expresión de tabla y pivotaremos el conjunto de resultados obtenido a partir de la misma.


WITH cteColumnaTexto AS
(
  SELECT
  C.Columna,
  SubC.Subcadena,
  ROW_NUMBER() OVER(PARTITION BY C.Columna ORDER BY C.Columna) AS
  NumCampoEnFila
  FROM ColumnaTexto AS C
  CROSS APPLY dbo.ParticionarCadena(C.Columna,'|') AS SubC
)
SELECT
[1] AS ResellerKey,
[2] AS ResellerAlternateKey,
[3] AS BusinessType,
[4] AS ResellerName,
[5] AS NumberEmployees,
[6] AS OrderFrequency,
[7] AS BankName
FROM
(
  SELECT
  Columna,
  Subcadena,
  NumCampoEnFila
  FROM cteColumnaTexto
) AS tblOrigen
PIVOT
(
  MAX(Subcadena)
  FOR NumCampoEnFila IN (
    [1],
    [2],
    [3],
    [4],
    [5],
    [6],
    [7]
  )
) AS tblPivot;

La siguiente figura muestra gráficamente la operación de transposición de filas a columnas conseguida mediante la instrucción PIVOT.

TablasNumericasAuxiliaresTallyTableEnAccionDepurarDelimitadoresColumnasTexto_23

 

Añadiendo una instrucción INSERT INTO sobre la tabla Reseller a la sentencia anterior habremos logrado nuestro objetivo.


WITH cteColumnaTexto AS
(
  SELECT
  C.Columna,
  SubC.Subcadena,
  ROW_NUMBER() OVER(PARTITION BY C.Columna ORDER BY C.Columna) AS
  NumCampoEnFila
  FROM ColumnaTexto AS C
  CROSS APPLY dbo.ParticionarCadena(C.Columna,'|') AS SubC
)
INSERT INTO Reseller (
ResellerKey,
ResellerAlternateKey,
BusinessType,
ResellerName,
NumberEmployees,
OrderFrequency,
BankName
)
SELECT
[1],
[2],
[3],
[4],
[5],
[6],
[7]
FROM
(
  SELECT
  Columna,
  Subcadena,
  NumCampoEnFila
  FROM cteColumnaTexto
) AS tblOrigen
PIVOT
(
  MAX(Subcadena)
  FOR NumCampoEnFila IN (
    [1],
    [2],
    [3],
    [4],
    [5],
    [6],
    [7]
)
) AS tblPivot;

Una vez ejecutada esta sentencia, las columnas de cada una de las filas de la tabla Reseller contendrán los datos que anteriormente se encontraban unidos en la columna única de la tabla ColumnaTexto.

Resumiendo a modo de conclusión, en el presente artículo hemos mostrado una más de las variadas aplicaciones que podemos dar a las tablas numéricas auxiliares, consistente en este caso en permitirnos depurar un archivo de datos con valores  separados por un delimitador. Confío en que esta técnica pueda resultar de utilidad al lector en alguna ocasión.

Tablas numéricas auxiliares (Tally Tables) en acción. Extracción de subcadenas mediante delimitadores

Después de explicar en el anterior artículo el modo de creación de tablas de dimensión para cubos de datos empleando tablas numéricas auxiliares, en esta ocasión continuaremos exponiendo situaciones de aplicación práctica relacionadas con el empleo de dichas tablas, abordando un problema frecuentemente resuelto mediante esta herramienta: la partición de una cadena en varias subcadenas a partir de un delimitador utilizado en la cadena principal.

Para lograr este objetivo el procedimiento a seguir consistirá en combinar la tabla numérica con la cadena; con ello averiguaremos respectivamente las posiciones de inicio y la longitud de cada subcadena, lo que nos permitirá extraerlas de la cadena principal.

En primer lugar necesitaremos crear una tabla numérica con base uno, es decir, que el   valor del primer registro sea el número 1 (ver un ejemplo en este enlace), conteniendo alrededor de 200 filas, cantidad suficiente para las pruebas a realizar, ya que la cadena con la que trabajaremos tendrá una longitud menor de dicha cifra.

Como primera aproximación al problema comenzaremos a combinar la tabla con la cadena para obtener un conjunto de resultados compuesto por sus caracteres independientes. Mediante la cláusula OFFSET…FETCH calcularemos cuántas filas de la tabla numérica necesitaremos emplear para realizar la extracción.


DECLARE @sTexto AS varchar(100);
SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';

SELECT NumeroID,
SUBSTRING(@sTexto, NumeroID, 1) AS Caracter
FROM Numeros
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

También podemos usar la cláusula TOP para averiguar la cantidad de filas a utilizar.


...
...
SELECT TOP(LEN(@sTexto))
...
...

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_01

 

Pero como acabamos de mencionar en un párrafo anterior, los datos esenciales que necesitamos para la extracción de las subcadenas son su posición de inicio y longitud. Para el primer caso utilizaremos una condición en la cláusula WHERE mediante la que conseguiremos que la consulta sólo devuelva las posiciones de la cadena en las que se halle un carácter delimitador, lo que nos servirá para calcular la posición de inicio de cada subcadena.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
SET @sDelimitador = ',';

SELECT
NumeroID AS PosicionCaracterInicio,
SUBSTRING(@sTexto, NumeroID + 1, 1) AS CaracterInicio
FROM Numeros
WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_02

 

El lector habrá comprobado que esta operación no proporciona la posición inicial de la primera subcadena debido a que el inicio (y el final) de la cadena principal carece del carácter delimitador.

No obstante, podemos solucionar este inconveniente desde la cláusula WHERE, concatenando en la función SUBSTRING el delimitador y la cadena, para obtener las posiciones de todos los delimitadores. Cuando esto ocurra, la consulta devolverá el mismo número de posición también a través de otra llamada a SUBTRING, pero aquí aplicaremos dicha posición solamente a la cadena principal, con lo que obtendremos el carácter de inicio de cada subcadena.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
SET @sDelimitador = ',';

SELECT
NumeroID AS PosicionCaracterInicio,
SUBSTRING(@sTexto, NumeroID, 1) AS CaracterInicio
FROM Numeros
WHERE SUBSTRING(@sDelimitador + @sTexto, NumeroID, 1) = @sDelimitador
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_03

 

El siguiente diagrama muestra gráficamente la operativa de la consulta anterior.

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_04

 

En lo que respecta al cálculo para hallar la longitud de la subcadena, mediante la función CHARINDEX y la concatenación de las variables de la cadena y el delimitador   lograremos la posición del delimitador situado al final de cada subcadena. A este valor le restaremos la posición del carácter de inicio obteniendo la longitud, y por último uniremos todos estos elementos en la función SUBSTRING, obteniendo como resultado la subcadena que necesitamos.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
SET @sDelimitador = ',';

SELECT
NumeroID AS PosicionCaracterInicio,
SUBSTRING(@sTexto, NumeroID, 1) AS CaracterInicio,
CHARINDEX(@sDelimitador, @sTexto + @sDelimitador, NumeroID) AS PosicionDelimitador,
CHARINDEX(@sDelimitador, @sTexto + @sDelimitador, NumeroID) - NumeroID AS LongitudSubcadena,
SUBSTRING(@sTexto, NumeroID, CHARINDEX(@sDelimitador, @sTexto + sDelimitador, NumeroID) - NumeroID) as Subcadena
FROM Numeros
WHERE SUBSTRING(@sDelimitador + @sTexto, NumeroID, 1) = @sDelimitador
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) ROWS ONLY;

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_05

 

A continuación podemos ver también un diagrama descriptivo del proceso.

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_06

 

Sin embargo, a pesar de su correcto funcionamiento, el código anterior sufre un problema de rendimiento originado por las concatenaciones realizadas entre las variables que contienen la cadena principal y el delimitador, tal y como Jeff Moden describe en su excelente artículo Tally OH! An Improved SQL 8K “CSV Splitter” Function, donde también se proponen algunas técnicas alternativas para conseguir particionar la cadena sin usar las mencionadas operaciones de concatenación

Una de estas técnicas consiste en utilizar una tabla numérica con base cero (primera fila con valor cero), gracias a la cual evitaremos la concatenación. Esta práctica, por contra, nos obligará a que en la cláusula WHERE, además de la condición para obtener las posiciones que correspondan a un delimitador, tengamos que añadir otra que compruebe los casos en que la columna NumeroID valga cero, ya que este será el criterio para poder calcular y obtener la primera subcadena de la cadena principal. No obstante, antes de realizar la extracción de subcadenas, utilicemos en primer lugar la consulta para analizar los diferentes elementos que obtenemos para la operación.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
set @sDelimitador = ','

SELECT
NumeroID + 1 AS PosicionPrimerCaracter,
CHARINDEX(@sDelimitador, @sTexto, NumeroID + 1) AS PosicionDelimitador,
(CHARINDEX(@sDelimitador,@sTexto, NumeroID + 1)) - (NumeroID + 1) AS LongitudSubcadena
FROM Numeros
WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
OR NumeroID=0
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) + 1 ROWS ONLY;

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_07

 

Observando los resultados obtenidos nos percataremos de que utilizando los cálculos realizados para averiguar las posiciones del primer carácter de la subcadena, el delimitador y la longitud; si intentamos extraer las subcadenas mediante las funciones SUBSTRING y CHARINDEX obtendremos un error provocado por los valores de la última fila del conjunto de resultados. Esto es debido a que el cálculo para obtener la longitud de la subcadena arroja un valor negativo por la inexistencia de carácter delimitador al final de la cadena principal, produciéndose un error en la función SUBSTRING al pasar este valor a su parámetro length.

Seguidamente vemos el código a utilizar para reproducir este error.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
SET @sDelimitador = ','

SELECT
SUBSTRING(@sTexto, (NumeroID + 1), CHARINDEX(@sDelimitador, @sTexto, NumeroID + 1) - (NumeroID + 1)) AS Subcadena
FROM Numeros
WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
OR NumeroID=0
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) + 1 ROWS ONLY;

Como vemos en la siguiente figura, las cuatro primeras subcadenas son obtenidas correctamente, produciéndose el error en la quinta.

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_08

 

Una forma de evitar el error (que no solucionarlo) consiste en aplicar la función ABS a la expresión utilizada para el parámetro length de la función SUBSTRING, convirtiendo a signo positivo el valor de la longitud de la subcadena.


SUBSTRING(
  @sTexto, 
  (NumeroID + 1), 
  ABS(CHARINDEX(@sDelimitador, @sTexto, NumeroID + 1) - (NumeroID + 1))
) AS Subcadena

Y como acabamos de mencionar, este pequeño truco no soluciona el problema, porque lo que hace no es calcular la longitud de la última subcadena, sino devolver un valor, que en este caso es igual a la posición de inicio de la misma, lo que en algunas ocasiones puede funcionar, pero si la longitud de esa última subcadena es lo suficientemente grande aparecerá truncada, como vemos en el siguiente ejemplo.

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_09

 

Es por ello que para averiguar la longitud de las subcadenas debemos emplear una estrategia distinta que nos permita calcular la última de ellas sin que ocurra este error, y hacerlo además de una forma que suponga la menor penalización posible de rendimiento en la ejecución de la consulta.

Aunque el error se produce en la función SUBSTRING al utilizar un valor negativo en el parámetro length, la raíz del problema reside en el cálculo de la posición del delimitador realizado mediante CHARINDEX, ya que la última subcadena carece de dicho carácter, y por lo tanto obtenemos un cero como resultado.

Vamos a intentar “engañar” a nuestra operación de cálculo del delimitador, de forma que cuando tenga que buscar dicho valor para la última subcadena, al no existir realmente tal delimitador, devolvamos 8000 como posición ficticia, que es el valor máximo del tipo de dato con el que estamos trabajando (varchar), y por consiguiente, la máxima longitud de cadena que utilizaríamos.

Esta operación la realizaremos en dos pasos: en primer lugar, cuando CHARINDEX devuelva el valor cero, mediante la función NULLIF devolveremos NULL; a continuación comprobaremos esta situación mediante la función ISNULL, y en caso afirmativo devolveremos 8000; si todo lo anterior no se cumple se ejecutará solamente CHARINDEX, lo cual ocurrirá para todas las subcadenas excepto la última.

De esta manera, al calcular la longitud de la subcadena nos aseguramos la obtención de un valor que no producirá error al ser utilizado en SUBSTRING, ya que si la suma de los valores para los parámetros start y length es mayor que la longitud de la cadena que estamos manejando, esta función devolverá la subcadena empezando desde el parámetro start hasta el final de la misma.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);
SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
SET @sDelimitador = ',';

SELECT
SUBSTRING(
@sTexto,
NumeroID + 1,
ISNULL(
  NULLIF(CHARINDEX(@sDelimitador, @sTexto, NumeroID + 1 ), 0), 8000) - (NumeroID + 1)
) AS Subcadena
FROM Numeros
WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
OR NumeroID = 0
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST LEN(@sTexto) + 1 ROWS ONLY;

Tras aplicar esta corrección al código obtendremos un conjunto de resultados compuesto por todas las subcadenas contenidas en la cadena principal.

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_10

 

Aunque ya hemos logrado optimizar la consulta eliminando las concatenaciones iniciales y solventando el error de cálculo para la longitud de las subcadenas, el operador OR también podría afectar negativamente al rendimiento, por lo que vamos a incorporar un nuevo ajuste a nuestro código, que elimine la necesidad de usar dicho operador.

Puesto que la utilidad de OR dentro del contexto en el que nos encontramos reside en la capacidad de permitirnos obtener la posición de inicio de la primera subcadena,  para conseguir el mismo resultado evitando el uso de este operador vamos a crear una expresión de tabla (CTE) compuesta por dos consultas unidas mediante el operador UNION. La primera devolverá el valor 1, mientras que la segunda será nuestra vieja conocida (sin OR) encargada de devolver las primeras posiciones de las subcadenas, en este caso a partir de la segunda subcadena en adelante.


DECLARE @sTexto AS varchar(100);
DECLARE @sDelimitador AS varchar(1);

SET @sTexto = 'monitor,teclado,ratón,impresora,smartphone';
SET @sDelimitador = ',';

WITH ctePosicionesInicio AS
(
  SELECT 1 AS PosicionInicio

  UNION ALL

  SELECT NumeroID + 1 AS PosicionInicio
  FROM Numeros
  WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
  ORDER BY NumeroID
  OFFSET 0 ROWS
  FETCH FIRST LEN(@sTexto) + 1 ROWS ONLY
)
SELECT
SUBSTRING(
  @sTexto,
  PosicionInicio,
  ISNULL(NULLIF(CHARINDEX(@sDelimitador, @sTexto, PosicionInicio), 0), 8000) - PosicionInicio
) AS Subcadena
FROM ctePosicionesInicio;

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_11

El siguiente diagrama describe gráficamente la consulta que acabamos de crear.

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_12

 

Completada esta nueva mejora sobre nuestro código podemos dar por concluido su desarrollo, queda ahora por ver el modo de aplicarlo de la manera más práctica posible, ya que el hecho de copiar y pegar esta sentencia en aquellos lugares donde vayamos a necesitarla no parece el modo de uso más adecuado ni más sencillo de cara a su mantenimiento.

Una buena opción de cara a su reutilización sería incluir este código en una función con valor de retorno de tipo tabla (Inline Table-Valued Function) como vemos a continuación, lo que nos permitiría aplicarla sobre la columna de una tabla que contuviera cadenas con valores separados por un delimitador, de forma que el resultado de la partición de las cadenas podríamos emplearlo como entrada en la inserción de otra tabla.


CREATE FUNCTION ParticionarCadena(@sTexto AS varchar(100), @sDelimitador AS char(1))
RETURNS TABLE
AS
RETURN
(

WITH ctePosicionesInicio AS
(
  SELECT 1 AS PosicionInicio

  UNION ALL

  SELECT NumeroID + 1 AS PosicionInicio
  FROM Numeros
  WHERE SUBSTRING(@sTexto, NumeroID, 1) = @sDelimitador
  ORDER BY NumeroID
  OFFSET 0 ROWS
  FETCH FIRST LEN(@sTexto) + 1 ROWS ONLY
)
SELECT
SUBSTRING(
  @sTexto,
  PosicionInicio,
  ISNULL(NULLIF(CHARINDEX(@sDelimitador, @sTexto, PosicionInicio), 0), 8000) - PosicionInicio
) AS Subcadena
FROM ctePosicionesInicio

)

Como demostración del comentario anterior, en la base de datos que estemos utilizando para realizar estas pruebas crearemos una tabla con el nombre Cadenas, que contenga una columna identificadora de fila y otra con los valores concatenados y separados por coma de las columnas EmployeeKey, EmployeeNationalIDAlternateKey, FirstName, LastName, Title y DepartmentName, pertenecientes a la tabla DimEmployee de la base de datos AdventureWorksDW2012.


CREATE TABLE Cadenas
(
CadenaID int NULL,
Cadena varchar(200) NULL
)

--////
INSERT INTO Cadenas (CadenaID,Cadena)
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
CONVERT(varchar(4), EmployeeKey) + ',' +
EmployeeNationalIDAlternateKey + ',' +
FirstName + ',' +
LastName + ',' +
Title + ',' +
DepartmentName
FROM AdventureWorksDW2012..DimEmployee

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_13

 

Si queremos comprobar la ejecución de la función ParticionarCadena contra la tabla que acabamos de crear, combinaremos en una consulta dicha tabla junto a la llamada a la función utilizando el operador CROSS APPLY. En el lado derecho de la consulta situaremos la función pasándole como parámetro la columna Cadena de la tabla Cadenas y el delimitador a usar, mientras que en el lado izquierdo colocaremos las columnas de la tabla Cadenas, y la columna Subcadena devuelta por la función.


SELECT
C.CadenaID,
C.Cadena,
SubC.Subcadena
FROM Cadenas AS C
CROSS APPLY dbo.ParticionarCadena(C.Cadena,',') AS SubC

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_14

 

A continuación crearemos la tabla Subcadenas, que usaremos como destino de la operación de partición aplicada a la tabla Cadenas utilizando la función ParticionarCadena.


CREATE TABLE Subcadenas
(
Subcadena varchar(50) NULL
)

INSERT INTO Subcadenas (Subcadena)
SELECT SubC.Subcadena
FROM Cadenas AS C
CROSS APPLY dbo.ParticionarCadena(C.Cadena, ',') AS SubC

TablasNumericasAuxiliaresTallyTableEnAccionExtraccionSubcadenas_15

 

Y tras este ejemplo concluimos el presente artículo, en el que hemos podido comprobar nuevamente la enorme versatilidad de las tablas numéricas auxiliares o tally tables, desarrollando en esta ocasión un proceso que nos ha permitido tomar una cadena compuesta por subcadenas separadas por un carácter delimitador y extraer de forma independiente cada una de dichas subcadenas, esperamos que los ejemplos  aquí expuestos resulten de utilidad al lector.

Tablas numéricas auxiliares (Tally Tables) en acción. Tablas de dimensión para cubos OLAP

Después de los artículos dedicados a la creación de tablas numéricas auxiliares publicados con anterioridad (parte1 y parte2), en esta ocasión vamos a aplicar de forma práctica esta útil herramienta en algunos escenarios con los que podemos encontrarnos en nuestra labor como desarrolladores de bases de datos.

En el presente artículo vamos a abordar un caso al que nos enfrentaríamos si estuviéramos desarrollando el modelo de datos para un cubo OLAP, cuya finalidad sea analizar la cantidad de población de una determinada región o país, y entre cuyos requerimientos se encuentren la elaboración de sendas dimensiones (con sus correspondientes tablas de datos) para mostrar la edad y fecha de nacimiento de los individuos que componen la población, teniendo en cuenta que los valores de edad deberán estar comprendidos en un rango de 0 a 120 años, mientras que las fechas de nacimiento oscilarán en un intervalo que va desde el 01/01/1900 hasta el 30/09/2014.

Para la estructura de la tabla de edades, además de la propia edad del individuo, añadiremos dos columnas adicionales que servirán para representar los atributos de edad decenal y quinquenal, utilizados habitualmente en sistemas de información demográfica. Por otra parte, en la tabla de fechas de nacimiento, además de las columnas que identifican las partículas de una fecha: año, mes y día, añadiremos columnas para los nombres del mes y día de la semana.


CREATE TABLE Edad
(
EdadID int NOT NULL,
EdadDecenal varchar(15) NULL,
EdadQuinquenal varchar(15) NULL,
CONSTRAINT PK_Edad PRIMARY KEY CLUSTERED (EdadID)
);

CREATE TABLE Fecha
(
FechaID date NOT NULL,
Anualidad int NULL,
Mes int NULL,
MesNombre varchar(20) NULL,
Dia int NULL,
DiaSemana varchar(20) NULL,
CONSTRAINT PK_Fecha PRIMARY KEY CLUSTERED (FechaID)
);

Para rellenar cada una de estas tablas mediante el método tradicional utilizaríamos un bucle WHILE, dentro del cual ejecutaríamos una sentencia INSERT en cada una de sus iteraciones.


DECLARE @nContador AS int;

SET @nContador = 0;

WHILE (@nContador <= 120)
BEGIN
  INSERT INTO Edad (EdadID,EdadDecenal,EdadQuinquenal)
  VALUES (@nContador,
  --...
  --...
  --valores calculados para las columnas EdadDecenal y EdadQuinquenal
  --...
  --...
  );

  SET @nContador = @nContador + 1;
END

--////////////////////////////////////////////////////
DECLARE @nContador AS int;
DECLARE @dtInicio AS date;
DECLARE @dtFin AS date;
DECLARE @dtFechaInsertar as date;

SET LANGUAGE Spanish;
SET @nContador = 0;
SET @dtInicio = '19000101';
SET @dtFin = '20140930';

WHILE (@nContador <= DATEDIFF(day,@dtInicio,@dtFin))
BEGIN
  SET @dtFechaInsertar = DATEADD(day,@nContador,@dtInicio);

  INSERT INTO Fecha (FechaID,Anualidad,Mes,MesNombre,Dia,DiaSemana)
  VALUES (@dtFechaInsertar,
  YEAR(@dtFechaInsertar),
  MONTH(@dtFechaInsertar),
  DATENAME(month,@dtFechaInsertar),
  DAY(@dtFechaInsertar),
  DATENAME(weekday,@dtFechaInsertar)
  );

  SET @nContador = @nContador + 1;
END

Pero si queremos aplicar un método más acorde a un entorno de programación basado en conjuntos (set based programming) como SQL Server, podemos optar por utilizar la técnica que da origen a este artículo: tablas numéricas auxiliares.

En primer lugar crearemos la tabla auxiliar a la que llamaremos Numeros, con base cero (valor cero como primer registro) y conteniendo 50000 filas (ver su modo de creación en este enlace). Dicha cantidad de registros será necesaria, evidentemente, no para la operación de carga de la tabla de edades, sino para la tabla de fechas.

A continuación rellenaremos la tabla Edad a partir de una consulta contra la tabla numérica, que devuelva los registros cuyo valor para la columna NumeroID esté comprendido en el intervalo de 0 a 120. La columna NumeroID se asignará directamente a la columna EdadID de la tabla Edad, mientras que para las otras dos columnas de esta tabla crearemos sendos valores calculados mediante expresiones CASE.


INSERT INTO Edad (EdadID,EdadDecenal,EdadQuinquenal)
SELECT NumeroID,
CASE
  WHEN NumeroID = 0 THEN '0'
  WHEN NumeroID BETWEEN 1 AND 4 THEN '1-4'
  WHEN NumeroID BETWEEN 5 AND 14 THEN '5-14'
  WHEN NumeroID BETWEEN 15 AND 24 THEN '15-24'
  WHEN NumeroID BETWEEN 25 AND 34 THEN '25-34'
  WHEN NumeroID BETWEEN 35 AND 44 THEN '35-44'
  WHEN NumeroID BETWEEN 45 AND 54 THEN '45-54'
  WHEN NumeroID BETWEEN 55 AND 64 THEN '55-64'
  WHEN NumeroID BETWEEN 65 AND 74 THEN '65-74'
  WHEN NumeroID BETWEEN 75 AND 84 THEN '75-84'
  WHEN NumeroID >= 85 THEN '85+'
END
,
CASE
  WHEN NumeroID BETWEEN 0 AND 4 THEN '0-4'
  WHEN NumeroID BETWEEN 5 AND 9 THEN '5-9'
  WHEN NumeroID BETWEEN 10 AND 14 THEN '10-14'
  WHEN NumeroID BETWEEN 15 AND 19 THEN '15-19'
  WHEN NumeroID BETWEEN 20 AND 24 THEN '20-24'
  WHEN NumeroID BETWEEN 25 AND 29 THEN '25-29'
  WHEN NumeroID BETWEEN 30 AND 34 THEN '30-34'
  WHEN NumeroID BETWEEN 35 AND 39 THEN '35-39'
  WHEN NumeroID BETWEEN 40 AND 44 THEN '40-44'
  WHEN NumeroID BETWEEN 45 AND 49 THEN '45-49'
  WHEN NumeroID BETWEEN 50 AND 54 THEN '50-54'
  WHEN NumeroID BETWEEN 55 AND 59 THEN '55-59'
  WHEN NumeroID BETWEEN 60 AND 64 THEN '60-64'
  WHEN NumeroID BETWEEN 65 AND 69 THEN '65-69'
  WHEN NumeroID BETWEEN 70 AND 74 THEN '70-74'
  WHEN NumeroID BETWEEN 75 AND 79 THEN '75-79'
  WHEN NumeroID BETWEEN 80 AND 84 THEN '80-84'
  WHEN NumeroID BETWEEN 85 AND 89 THEN '85-89'
  WHEN NumeroID >= 90 THEN '90+'
END
FROM Numeros
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST 121 ROWS ONLY;

En el caso de que nuestra versión de SQL Server no soporte el uso de OFFSET…FETCH, emplearemos la cláusula TOP para limitar el número de filas a obtener de la tabla Numeros.


INSERT INTO Edad (EdadID,EdadDecenal,EdadQuinquenal)
SELECT TOP(121) NumeroID,
...
...

Seguidamente vemos una muestra del contenido de la tabla Edad tras la inserción de las filas.

TablasNumericasAuxiliaresTallyTableEnAccionTablasDimension_01

 

Respecto a la tabla Fecha, para generar cada una de las filas a insertar utilizaremos nuestra tabla numérica, pasando el valor del campo NumeroID a la función DATEADD, tomando como referencia del cálculo la fecha menor del rango. La cantidad de días a obtener será calculada desde OFFSET…FETCH mediante la función DATEDIFF, a la que pasaremos como parámetro las fechas inferior y superior del rango. Toda esta operación la agruparemos en una expresión de tabla (Common Table Expression o CTE) contra la que ejecutaremos una sentencia para insertar las filas en la tabla Fecha, aplicando funciones de tratamiento de fechas para obtener las diversas partículas comentadas anteriormente.


DECLARE @dtInicio AS date;
DECLARE @dtFin AS date;

SET LANGUAGE Spanish;

SET @dtInicio = '19000101';
SET @dtFin = '20140930';

WITH cteFechas AS
(
  SELECT
  DATEADD(day,numeroid,@dtInicio) AS Fecha
  FROM Numeros
  ORDER BY NumeroID
  OFFSET 0 ROWS
  FETCH FIRST DATEDIFF(day,@dtInicio,@dtFin) + 1 ROWS ONLY
)
INSERT INTO Fecha
SELECT Fecha,
YEAR(Fecha),
MONTH(Fecha),
DATENAME(month,Fecha),
DAY(Fecha),
DATENAME(weekday,Fecha)
FROM cteFechas;

Al igual que en el caso anterior, si no podemos usar OFFSET…FETCH volveremos a emplear TOP.


--...
--...
WITH cteFechas AS
(
  SELECT TOP(DATEDIFF(day,@dtInicio,@dtFin) + 1)
  DATEADD(day,numeroid,@dtInicio) AS Fecha
  FROM Numeros
--...
--...

TablasNumericasAuxiliaresTallyTableEnAccionTablasDimension_02

Como acabamos de comprobar, mientras que el uso de WHILE para rellenar una tabla  con las características presentadas en este artículo supone la ejecución de múltiples sentencias INSERT, con el consiguiente impacto en el rendimiento, la utilización de una tabla numérica permite realizar el mismo trabajo en una única sentencia de inserción, lo que redunda en un mejor aprovechamiento de los recursos del servidor de datos. Confiamos en que los ejemplos aquí presentados resulten de utilidad al lector.