(Artículo publicado previamente en el número 88 de dNM+, enero 2012)

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 [1], 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 [2], 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 en [1].

 

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.

 

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.

 

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])

 

 

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.

 

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.

 

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.

 

¿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.

 

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.

 

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.

 

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.

 

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)))

 

 

SQL Server 2012. La solución

En el caso de que trabajemos con SQL Server 2012 (Denali) [3], no será necesario que utilicemos las técnicas relatadas anteriormente, ya que la versión de PowerPivot para SQL Server 2012 [4] 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.

 

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.

 

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 [3], 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.

REFERENCIAS

[1] Blanco, Luis Miguel. "PowerPivot, DAX y Excel. Business Intelligence para todos los públicos".

http://geeks.ms/blogs/lmblanco/archive/2015/04/22/powerpivot-dax-y-excel-business-intelligence-para-todos-los-p-250-blicos.aspx

[2] AdventureWorks. Base de datos de ejemplo para Microsoft SQL Server 2008 en CodePlex.

http://msftdbprodsamples.codeplex.com/releases/view/55926

[3] SQL Server 2012 RC

http://www.microsoft.com/sqlserver/en/us/future-editions.aspx

 [4] SQL Server 2012 PowerPivot para Excel 2010.

http://www.microsoft.com/downloads/es-es/details.aspx?FamilyID=C61EE610-B63E-4033-A934-01D2AB567A45

 

(Artículo publicado previamente en el número 93 de dNM+, junio 2012)

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 [1], 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.

 

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.

 

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 [2], 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.

 

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.

 

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.

 

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.

 

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

 

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.

 

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 [3].

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.

 

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.

 

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.

 

REFERENCIAS

[1] Blanco, Luis Miguel. "PowerPivot, DAX y Excel. Business Intelligence para todos los públicos".

http://geeks.ms/blogs/lmblanco/archive/2015/04/22/powerpivot-dax-y-excel-business-intelligence-para-todos-los-p-250-blicos.aspx

[2] AdventureWorksDWDenali.

http://msftdbprodsamples.codeplex.com/releases/view/55330

[3] Blanco, Luis Miguel. "Cubos de datos en SQL Server 2008 Analysis Services".

http://geeks.ms/blogs/lmblanco/archive/2013/09/11/cubos-de-datos-en-sql-server-2008-analysis-services.aspx

 

(Artículo publicado previamente en el número 83 de dNM+, Julio-Agosto 2011)

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 en [1], 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.

 

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.

 

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 [2] 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 [3], 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 [4]. 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, disponible en [5], 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.

 

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.

 

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.

 

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.

 

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.

 

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])

 

 

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.

 

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.

 

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))

 

 

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.

 

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.

 

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.

 

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.

 

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.

 

REFERENCIAS

[1] Blanco, Luis Miguel. "Cubos de datos en SQL Server 2008 Analysis Services". http://geeks.ms/blogs/lmblanco/archive/2013/09/11/cubos-de-datos-en-sql-server-2008-analysis-services.aspx

[2] Chris Webb's BI Blog - PASS Summit Day 2.

http://cwebbbi.wordpress.com/2010/11/11/pass-summit-day-2/

[3] Analysis Services - Roadmap for SQL Server "Denali" and Beyond.

http://blogs.technet.com/b/dataplatforminsider/archive/2010/11/12/analysis-services-roadmap-for-sql-server-denali-and-beyond.aspx

[4] Microsoft SQL Server 2008 R2 - PowerPivot para Microsoft Excel 2010 - RTM .

http://www.microsoft.com/downloads/es-es/details.aspx?FamilyId=e081c894-e4ab-42df-8c87-4b99c1f3c49b&displaylang=es

[5] Base de datos ContosoRetailDW.

http://www.microsoft.com/downloads/es-es/details.aspx?FamilyID=868662dc-187a-4a85-b611-b7df7dc909fc&displayLang=es

 

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

 

 

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.

 

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

 

 

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;

 

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;

 

 

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

 

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;

 

 

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.

Publicado por Luis Miguel Blanco | con no comments

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.

 

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

 

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.

 

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.

 

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.

 

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

 

Incluyendo dicha consulta a continuación.

 

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

 

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.

 

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.

 

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.

 

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 1 (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.

 

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

 

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.

 

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
            )
)

 

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

 

 

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

 

 

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

 

 

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.

 

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 6 caracteres delimitadores (exceptuando aquellas con exceso de dicho carácter), que a su vez definen 7 campos.

 

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

 

 

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.

 

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 7 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.

 

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.

 

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.

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))
...
...

 

 

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;

 

 

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;

 

 

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

 

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;

 

 

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

 

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;

 

 

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.

 

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.

 

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.

 

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;

 

 

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

 

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

 

 

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

 

 

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

 

 

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.

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.

 

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
            --...
            --...

 

 

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.

 

En la primera parte de este artículo explicábamos en qué consiste una tabla numérica auxiliar, así como varias técnicas para su creación. En esta segunda entrega continuaremos exponiendo formas adicionales de generar tablas numéricas, de modo que el lector disponga de un amplio abanico de opciones que pueda aplicar en las más variadas situaciones.

 

Pseudo Cursor + IDENTITY()

Una variante de la operación expuesta en el apartado sobre pseudo cursores de la primera parte consistiría en utilizar una sola sentencia para crear, al mismo tiempo, la tabla numérica y rellenarla de valores, utilizando para tal propósito la instrucción SELECT...INTO junto a la función IDENTITY(), que en este caso realizaría idéntico trabajo de generación de números que ROW_NUMBER() en el ejemplo anterior.

 

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

DECLARE @dtInicio AS datetime;
DECLARE @dtFin AS datetime;

SET @dtInicio = CURRENT_TIMESTAMP;

SELECT
IDENTITY(int,1,1) AS NumeroID
INTO Numeros
FROM sys.all_columns AS tbl1
CROSS JOIN SYS.all_columns AS tbl2
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST 30000 ROWS ONLY;

SET @dtFin = CURRENT_TIMESTAMP;

SELECT DATEDIFF(millisecond,@dtInicio,@dtFin);

ALTER TABLE Numeros
ADD CONSTRAINT PK_Numeros
PRIMARY KEY (NumeroID);

 

Al igual que en el apartado anterior, si nuestra versión de SQL Server no soporta el uso de OFFSET...FETCH, emplearemos TOP para especificar el número de filas a rellenar en la tabla numérica auxiliar.

 

..........
..........
SELECT TOP (30000)
IDENTITY(INT,1,1) AS NumeroID
INTO Numeros
FROM sys.all_columns AS tbl1
CROSS JOIN SYS.all_columns AS tbl2;
..........
..........

 

Cruce múltiple de CTEs

A continuación es el turno de un interesante método desarrollado por Itzik Ben-Gan en su obra "Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions" [4], que se basa en el empleo de una serie de CTEs combinadas mediante cláusulas CROSS JOIN, para obtener el rango secuencial de números con el que rellenaremos nuestra tabla numérica.

La primera CTE, a la que daremos el nombre cteGenNumsBase (de generador base de números), se encarga de crear un conjunto de resultados que devuelve dos filas con el valor 1 en cada una de ellas, empleando un constructor de valores para tabla (Table Value Constructor) en forma de tabla derivada dentro de una cláusula FROM.

 

WITH
cteGenNumsBase AS (
            SELECT NumBaseID
            FROM (
                        VALUES
                                   (1),
                                   (1)
            ) AS tblNums(NumBaseID)
)
SELECT NumBaseID
FROM cteGenNumsBase;

 

 

A continuación agregaremos otra CTE con el nombre cteGenNums1 (de primer generador de números con CROSS JOIN), conteniendo una consulta contra la CTE cteGenNumsBase, que realizará una CROSS JOIN sobre esa misma CTE, obteniendo esta vez cuatro filas con el valor 1.

 

WITH
cteGenNumsBase AS (
            SELECT NumBaseID
            FROM (
                        VALUES
                                   (1),
                                   (1)
            ) AS tblNums(NumBaseID)
),
cteGenNums1 AS (
            SELECT 1 AS NumID
            FROM cteGenNumsBase AS tblGenNumsA
            CROSS JOIN cteGenNumsBase AS tblGenNumsB
)
SELECT NumID
FROM cteGenNums1;

 

 

Aunque no hemos completado todavía el código de este ejemplo, observe el lector que estamos empezando a aplicar nuevamente la técnica del pseudo cursor, ya que en la CTE cteGenNums1 utilizamos la presencia de filas obtenida a partir de la CTE cteGenNumsBase para generar nuevas filas, doblando su número hasta cuatro. Para volver a doblar dicho número, tan sólo necesitamos añadir otra CTE que utilice la misma lógica en su código, y así sucesivamente hasta concluir con una CTE (que aquí llamamos cteGenNumsTotal), que como ya conocemos de anteriores ejemplos, mediante la función ROW_NUMBER() se encargaría de generar la secuencia de números. La unión de todos estos elementos es lo que Jeff Moden denomina en [3] CTEs cruzadas en cascada (CROSS JOINED Cascaded CTE).

Seguidamente vemos el código del ejemplo al completo, en el que utilizando cinco CTEs para generar filas a partir de la CTE base, podríamos obtener hasta 4.294.967.296 filas, cantidad que raramente nos encontraremos en situación de necesitar; de ahí que mediante la cláusula OFFSET...FETCH de ORDER BY, limitemos el número de registros obtenidos.

 

WITH
cteGenNumsBase AS ( 
             --devuelve 2 filas
            SELECT NumBaseID
            FROM (
                        VALUES
                                   (1),
                                   (1)
            ) AS tblNums(NumBaseID)
),
cteGenNums1 AS ( 
             --devuelve 4 filas
            SELECT 1 AS NumID
            FROM cteGenNumsBase AS tblGenNumsA
            CROSS JOIN cteGenNumsBase AS tblGenNumsB
),
cteGenNums2 AS ( 
             --devuelve 16 filas
            SELECT 1 AS NumID
            FROM cteGenNums1 AS tblGenNumsA
            CROSS JOIN cteGenNums1 AS tblGenNumsB
),
cteGenNums3 AS ( 
             --devuelve 256 filas
            SELECT 1 AS NumID
            FROM cteGenNums2 AS tblGenNumsA
            CROSS JOIN cteGenNums2 AS tblGenNumsB
),
cteGenNums4 AS ( 
             --devuelve 65.536 filas
            SELECT 1 AS NumID
            FROM cteGenNums3 AS tblGenNumsA
            CROSS JOIN cteGenNums3 AS tblGenNumsB
),
cteGenNums5 AS ( 
             --devuelve 4.294.967.296 filas
            SELECT 1 AS NumID
            FROM cteGenNums4 AS tblGenNumsA
            CROSS JOIN cteGenNums4 AS tblGenNumsB
),
cteGenNumsTotal AS (
            SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroID
            FROM cteGenNums5
)
SELECT NumeroID
FROM cteGenNumsTotal
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST 30000 ROWS ONLY;

 

El bloque de código anterior, aun cumpliendo su cometido, puede resultar un tanto sobrecargado debido a la extensión de los nombres utilizados y a la disposición de las sentencias; ello se debe a que el propósito principal es que la lógica aplicada resulte lo más comprensible al lector. Por dicho motivo, a continuación presentamos una versión más compacta del mismo, donde tras reducir los nombres de los identificadores, la colocación de las sentencias y las líneas de código, obtenemos la misma funcionalidad. Aprovechamos también para incluir aquí las sentencias de creación de la tabla numérica auxiliar.

 

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

CREATE TABLE Numeros
(
            NumeroID int NOT NULL
);

DECLARE @dtInicio AS datetime;
DECLARE @dtFin AS datetime;

SET @dtInicio = CURRENT_TIMESTAMP;

WITH
GNBase AS (SELECT N FROM (VALUES (1),(1)) AS NBase(N)),
GN1 AS (SELECT 1 AS N FROM GNBase AS tblA CROSS JOIN GNBase AS tblB),
GN2 AS (SELECT 1 AS N FROM GN1 AS tblA CROSS JOIN GN1 AS tblB),
GN3 AS (SELECT 1 AS N FROM GN2 AS tblA CROSS JOIN GN2 AS tblB),
GN4 AS (SELECT 1 AS N FROM GN3 AS tblA CROSS JOIN GN3 AS tblB),
GN5 AS (SELECT 1 AS N FROM GN4 AS tblA CROSS JOIN GN4 AS tblB),
GNTotal AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroID FROM GN5)
INSERT INTO Numeros (NumeroID)
SELECT NumeroID
FROM GNTotal
ORDER BY NumeroID
OFFSET 0 ROWS
FETCH FIRST 30000 ROWS ONLY;

SET @dtFin = CURRENT_TIMESTAMP;

SELECT DATEDIFF(millisecond,@dtInicio,@dtFin);

ALTER TABLE Numeros
ADD CONSTRAINT PK_Numeros
PRIMARY KEY (NumeroID);

 

Uno de los puntos fuertes de esta técnica reside en su excelente aprovechamiento de recursos, ya que durante la generación de las filas, el procesador de consultas del motor de SQL Server se detiene justo en el momento de alcanzar el número de registros especificado en ORDER BY...OFFSET...FETCH.

Como ya hemos explicado en ejemplos previos, para establecer el número de registros que necesitamos obtener, también podemos conseguir el mismo resultado mediante la cláusula TOP en caso de usar una versión de SQL Server que no implemente OFFSET...FETCH.

 

..........
..........
GNTotal AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroID FROM GN5)
INSERT INTO Numeros (NumeroID)
SELECT TOP (30000) NumeroID
FROM GNTotal;
..........
..........

 

De igual forma, si la versión de SQL Server con la que trabajamos no soporta constructores de valores para tabla, en la primera CTE utilizaremos dos consultas que devuelvan el valor 1, aplicando sobre ellas el operador UNION.

 

..........
..........
WITH
GNBase AS (SELECT 1 AS N UNION ALL SELECT 1 AS N),
..........
..........

 

Incluir el valor cero como primer registro

La tabla numérica auxiliar creada en todos los ejemplos mostrados hasta el momento contiene el valor 1 en su primera fila, pero podríamos encontrarnos con situaciones en las que necesitáramos que ese primer registro de la tabla fuera un cero.

Para conseguir tal propósito podemos añadir una consulta a la CTE final del grupo de CTEs cruzadas, en la que devolvamos un conjunto de resultados con el valor cero, que combinaremos mediante el operador UNION con la consulta ya existente, encargada de los cruces múltiples de las CTEs. Por último, para poder obtener el último número que necesitamos del intervalo, tenemos que incrementar en uno el valor indicado en el modificador FETCH de la cláusula ORDER BY.

Como ejemplo de lo que acabamos de explicar, si queremos crear una tabla numérica con el rango de números de 0 a 30000, el valor de FETCH deberá ser 30001.

 

 

Intervalo de números con valor de inicio mayor de 1

Otra situación con la que podríamos encontrarnos, relacionada con la primera fila de la tabla numérica, sería que su valor tuviera que ser superior a 1, debiendo especificar, adicionalmente, los límites inferior y superior de la tabla.

Este problema puede ser resuelto mediante el uso de un procedimiento almacenado, al que pasaremos dos parámetros con los mencionados límites de la tabla, los cuales utilizaremos dentro del código del procedimiento en los modificadores OFFSET...FETCH tal y como vemos a continuación.

 

IF OBJECT_ID('spGenerarTablaNumeros','P') IS NOT NULL
BEGIN
            DROP PROCEDURE spGenerarTablaNumeros;
END
GO

CREATE PROCEDURE spGenerarTablaNumeros
            @nPrimerNumero AS int,
            @nUltimoNumero AS int
AS
BEGIN

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

CREATE TABLE Numeros
(
            NumeroID int NOT NULL
);

WITH
GNBase AS (SELECT N FROM (VALUES (1),(1)) AS NBase(N)),
GN1 AS (SELECT 1 AS N FROM GNBase AS tblA CROSS JOIN GNBase AS tblB),
GN2 AS (SELECT 1 AS N FROM GN1 AS tblA CROSS JOIN GN1 AS tblB),
GN3 AS (SELECT 1 AS N FROM GN2 AS tblA CROSS JOIN GN2 AS tblB),
GN4 AS (SELECT 1 AS N FROM GN3 AS tblA CROSS JOIN GN3 AS tblB),
GN5 AS (SELECT 1 AS N FROM GN4 AS tblA CROSS JOIN GN4 AS tblB),
GNTotal AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroID
ROM GN5)
INSERT INTO Numeros (NumeroID)
SELECT NumeroID
FROM GNTotal
ORDER BY NumeroID
OFFSET (@nPrimerNumero - 1) ROWS
FETCH FIRST ((@nUltimoNumero + 1) - @nPrimerNumero) ROWS ONLY;

ALTER TABLE Numeros
ADD CONSTRAINT PK_Numeros
PRIMARY KEY (NumeroID)

END
GO

 

Si ahora queremos crear una tabla numérica cuya primera y última fila sean respectivamente los valores 12537 y 28754, ejecutaríamos la siguiente sentencia.

 

EXECUTE spGenerarTablaNumeros 12537,28754;

 

 

Para lograr mediante la cláusula TOP el mismo resultado realizaremos la siguiente modificación al código del procedimiento almacenado.


..........
..........
INSERT INTO Numeros (NumeroID)
SELECT TOP((@nUltimoNumero + 1) - @nPrimerNumero) (@nPrimerNumero - 1) +
NumeroID
FROM GNTotal
..........
..........

 

Una vez completado este ejemplo concluimos el presente artículo en el que a lo largo de sus dos entregas hemos realizado una introducción a las tablas numéricas auxiliares o tally tables, presentando algunas de las técnicas más destacadas en la creación de las mismas. En un próximo artículo expondremos ejemplos prácticos del uso de esta herramienta confiando en que resulte de utilidad al lector.

 

Referencias

[1] Jeff Moden. "The "Numbers" or "Tally" Table: What it is and how it replaces a loop". http://www.sqlservercentral.com/articles/T-SQL/62867/

[2] Itzik Ben-Gan. "Microsoft SQL Server 2012 T-SQL Fundamentals". http://blogs.msdn.com/b/microsoft_press/archive/2012/07/16/new-book-microsoft-sql-server-2012-t-sql-fundamentals.aspx

[3] Jeff Moden. "Hidden RBAR: Counting with Recursive CTE's". http://www.sqlservercentral.com/articles/T-SQL/74118/

[4] Itzik Ben-Gan. "Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions". http://blogs.msdn.com/b/microsoft_press/archive/2012/04/26/new-book-microsoft-sql-server-2012-high-performance-t-sql-using-window-functions.aspx

Una tabla numérica auxiliar (denominada también tabla de conteo, tally table o simplemente tabla de números) es una tabla compuesta por una única columna, que almacena una cantidad variable de registros con valores numéricos consecutivos, y que se emplea como herramienta de soporte en operaciones tales como la generación de registros en otras tablas y la manipulación de cadenas en escenarios en los que tradicionalmente se recurre a algún tipo de estructura repetitiva, como pueda ser un cursor o bucle WHILE.

A lo largo de las dos entregas de que consta este artículo abordaremos algunas de las principales técnicas utilizadas en la creación de este tipo de tabla, dejando para un próximo artículo su aplicación práctica en diversos escenarios de uso.

 

Bucle WHILE

Si se trata de rellenar una tabla con una serie de números consecutivos, es probable que lo primero en lo que pensemos sea en utilizar un bucle WHILE, que se ejecute tantas veces como números necesitemos añadir a la tabla, de una forma parecida a la que mostramos a continuación (nótese que a lo largo de los ejemplos incluimos también diversas sentencias para evaluar los tiempos de ejecución de las técnicas presentadas y comparar así su rendimiento).

 

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

CREATE TABLE Numeros
(
            NumeroID int NOT NULL
);

DECLARE @nContador AS INT;
DECLARE @dtInicio AS DATETIME;
DECLARE @dtFin AS DATETIME;

SET @dtInicio = CURRENT_TIMESTAMP;
SET @nContador = 1;

WHILE(@nContador <= 30000)
BEGIN
            INSERT INTO Numeros (NumeroID)
            VALUES (@nContador);

            SET @nContador += 1; 
END

SET @dtFin = CURRENT_TIMESTAMP;

SELECT DATEDIFF(millisecond,@dtInicio,@dtFin);

ALTER TABLE Numeros
ADD CONSTRAINT PK_Numeros
PRIMARY KEY (NumeroID)

 

El anterior bloque de código utiliza el operador compuesto +=  para incrementar la variable @nContador. Si nuestra versión de SQL Server no soporta dicho operador, emplearemos la construcción clásica: 

SET @nContador = @nContador + 1;

Aunque el código del ejemplo funciona correctamente y cumple su cometido de rellenar la tabla Numeros con un conjunto de registros cuyos valores están comprendidos entre 1 y 30000, no estamos empleando la potencia de la programación basada en conjuntos, tal y como Jeff Moden, uno de los grandes especialistas en SQL Server, explica en "The 'Numbers' or 'Tally' Table: What it is and how it replaces a loop" [1]. Es por ello que en los siguientes apartados veremos cómo podemos corregir dicha situación.

 

Expresión Común de Tabla (Common Table Expression - CTE) recursiva

Una expresión común de tabla (CTE a partir de ahora) según detalla Itzik Ben-Gan en su obra "Microsoft SQL Server 2012 T-SQL Fundamentals" [2], es una construcción del lenguaje SQL, que según la consulta a abordar, nos permite crear expresiones de tabla organizando el código de la consulta de una forma más limpia y legible que mediante el uso de tablas derivadas o subconsultas.

Para crear una CTE utilizaremos la palabra clave WITH, seguida del nombre que damos a la CTE, la partícula AS, y entre paréntesis, el código de la consulta propio de la CTE o consulta interna (inner query). A continuación escribiremos el código de la consulta externa (outer query) que se ejecuta contra la CTE. Todo ello lo vemos seguidamente en un ejemplo que hace un cruce entre el resultado de una CTE basada en la tabla DimCustomer y la tabla FactInternetSales, de la base de datos AdventureWorksDW2012.

 

 

Esta explicación introductoria sobre CTEs viene motivada por el hecho de que es un elemento del lenguaje SQL dotado de una funcionalidad recursiva, que vamos a aplicar como otro de los medios para la creación de tablas numéricas; de ahí que para aquellos lectores que todavía no han trabajado con CTEs resulte un requisito imprescindible conocer su funcionamiento aunque sea de un modo somero.

Para construir una CTE recursiva, que nos evite el uso de un bucle WHILE, debemos utilizar al menos dos consultas. La primera, denominada elemento ancla, que se ejecuta una sola vez, devolverá un resultado simple que será utilizado como valor inicial por la segunda consulta, denominada elemento recursivo, que hace que la CTE se llame a sí misma, consiguiendo de esta manera la capacidad recursiva

El límite de llamadas recursivas lo establecemos en el elemento recursivo mediante una cláusula WHERE, como muestra el siguiente código, que genera un conjunto de números del 1 al 50.

 

En el anterior ejemplo, al llamar a la CTE desde la consulta externa (1) se ejecuta una única vez el elemento ancla (2) de la CTE, que devuelve el valor 1 con el alias de columna Num.

A continuación se produce, dentro de la CTE, una llamada a sí misma desde el elemento recursivo (3). Como la columna Num ya tiene el valor 1, fruto de la primera ejecución contra el elemento ancla, se suma 1 a dicha columna, por lo que ahora devuelve el valor 2.

Las sucesivas llamadas continúan ejecutando el elemento recursivo (3), incrementando la columna Num, hasta que se cumple la condición de la cláusula WHERE.

La cantidad de llamadas recursivas que pueden hacerse por defecto es de 100. No obstante, podemos cambiar este valor mediante el modificador OPTION (MAXRECURSION NumLlamadas), donde NumLlamadas representa la cantidad de ejecuciones recursivas que puede realizar la CTE, estando su valor comprendido entre 0 y 32767.

En el siguiente código, gracias al uso de dicho modificador, obtenemos una lista de números comprendida entre los valores 1 y 5000.

 

WITH cteContador AS
(
            SELECT 1 AS Num

            UNION ALL

            SELECT Num + 1
            FROM cteContador
            WHERE Num < 5000
)
SELECT Num
FROM cteContador
OPTION (MAXRECURSION 5000);

 

En caso de necesitar un número de llamadas recursivas mayor que el límite de 32767, utilizaremos cero como parámetro de MAXRECURSION.

 

WITH cteContador AS
(
            SELECT 1 AS Num

            UNION ALL

            SELECT Num + 1
            FROM cteContador
            WHERE Num < 39500
)
SELECT Num
FROM cteContador
OPTION (MAXRECURSION 0);

 

Una vez visto el funcionamiento de las CTEs recursivas volvemos a retomar nuestro objetivo inicial sobre creación de tablas numéricas, utilizando la técnica que acabamos de explicar, para lo cual emplearemos el siguiente código.

 

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

CREATE TABLE Numeros
(
            NumeroID int NOT NULL
);

DECLARE @dtInicio AS DATETIME;
DECLARE @dtFin AS DATETIME;

SET @dtInicio = CURRENT_TIMESTAMP;

WITH cteContador AS
(
            SELECT 1 AS Num

            UNION ALL     

            SELECT Num + 1
            FROM cteContador
            WHERE Num < 30000
)
INSERT INTO Numeros (NumeroID)
SELECT Num
FROM cteContador
OPTION (MAXRECURSION 30000);

SET @dtFin = CURRENT_TIMESTAMP;

SELECT DATEDIFF(millisecond,@dtInicio,@dtFin);

ALTER TABLE Numeros
ADD CONSTRAINT PK_Numeros
PRIMARY KEY (NumeroID);

 

Si comparamos los tiempos de ejecución, comprobaremos que este último método para cargar la tabla numérica es, con diferencia, mucho más rápido que el utilizado con el bucle WHILE, por lo que aparentemente parece una solución más recomendable, ya que esta mayor velocidad en su funcionamiento puede llevarnos a pensar que también proporciona un mejor rendimiento en ejecución.

Sin embargo, como acabamos de decir, esta ventaja es sólo aparente, ya que el análisis de la mecánica interna utilizada por una CTE recursiva revela que su funcionamiento no es tan óptimo como podría pensarse en un principio, tal y como explica detalladamente Jeff Moden en su interesante artículo "Hidden RBAR: Counting with Recursive CTE's" [3].

Ha llegado, por lo tanto, la hora de mostrar una técnica para crear una tabla numérica, que ofrezca una velocidad todavía mayor que los ejemplos anteriormente explicados, y un rendimiento en ejecución que sea realmente bueno.

 

Pseudo Cursor

Según se expone en [3], un pseudo cursor es un bucle basado en un conjunto de resultados obtenidos a partir de una consulta contra una tabla. También podría describirse como el producto de una consulta contra una tabla o combinación de tablas, pero utilizando solamente la presencia de filas de la tabla(s) involucrada(s) en la consulta. Puesto que es probable que estas explicaciones no resulten muy reveladoras para el lector, veamos lo que es un pseudo cursor a través de un ejemplo.

Si ejecutamos la siguiente consulta en alguna de nuestras bases de datos.

 

SELECT name FROM sys.all_columns;

 

Nos devolverá un conjunto de resultados con los nombres de las columnas de las tablas que componen la base de datos, en un número que variará dependiendo de la cantidad de tablas existentes. En mi caso obtengo 8453 filas como puede apreciarse en la siguiente imagen.

 

Supongamos ahora que aprovechamos este conjunto de resultados para generar, además, una nueva columna con numeración secuencial mediante la función ROW_NUMBER().

 

SELECT
name,
ROW_NUMBER() OVER(ORDER BY name) AS NumeroFila
FROM sys.all_columns;

 

 

Como el objetivo que realmente perseguimos es la creación, única y exclusivamente, del intervalo de números, en el anterior bloque de código sobraría la columna name de la tabla sys.all_columns, pero si quitamos de la consulta dicha columna nos encontraremos con un error en ROW_NUMBER(), ya que la cláusula OVER(ORDER BY <cláusula>) es obligatoria.

 

Sin embargo, si en la cláusula ORDER BY empleamos la expresión SELECT NULL, conseguiremos un doble propósito: por un lado solucionaremos el error que se producía en ROW_NUMBER(); y por otro, lograremos nuestro objetivo de crear el rango de valores numéricos.

 

SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NumeroFila 
FROM sys.all_columns;

 

La clave reside en la presencia de filas de la tabla sys.all_columns (obsérvese que no se hace uso de ninguna de las columnas de la tabla) que forma parte de la consulta, y que aquí actúa como fuente de datos en la generación de las filas para el intervalo, mientras que ROW_NUMBER() proporciona la lógica de la asignación del número correspondiente a cada fila. 

Este funcionamiento combinado de ambos elementos: tabla y ROW_NUMBER(), para obtener un conjunto secuencial de números, que tradicionalmente requeriría el uso de un cursor o bucle WHILE, es lo que recibe el nombre de pseudo cursor. Si aplicamos esta técnica a la creación de la tabla numérica, nuestro código quedaría como vemos a continuación.

 

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

CREATE TABLE Numeros
(
            NumeroID int NOT NULL
);

INSERT INTO Numeros (NumeroID)
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM sys.all_columns;

 

Aquí nos encontramos con una pequeña contrariedad: dado que la lista de números está limitada al número de filas de la tabla fuente (sys.all_columns en este caso) ¿qué podemos hacer si necesitamos crear una tabla numérica mayor? Una táctica sería recurrir al empleo de una tabla fuente con un mayor número de registros, pero si la tabla numérica a crear necesita un rango de valores muy elevado, puede que no dispongamos de una tabla fuente lo bastante grande, por lo que una técnica más recomendable consiste en hacer una CROSS JOIN de la tabla fuente consigo misma de la siguiente manera.

 

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

CREATE TABLE Numeros
(
            NumeroID int NOT NULL
);

DECLARE @dtInicio AS DATETIME;
DECLARE @dtFin AS DATETIME;

SET @dtInicio = CURRENT_TIMESTAMP;

INSERT INTO Numeros (NumeroID)
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Num
FROM sys.all_columns AS tbl1
CROSS JOIN sys.all_columns AS tbl2
ORDER BY Num
OFFSET 0 ROWS
FETCH FIRST 30000 ROWS ONLY;

SET @dtFin = CURRENT_TIMESTAMP;

SELECT DATEDIFF(millisecond,@dtInicio,@dtFin);

ALTER TABLE Numeros
ADD CONSTRAINT PK_Numeros
PRIMARY KEY (NumeroID);

 

Dado que el producto cartesiano de la tabla sys.all_columns al hacer la CROSS JOIN proporciona un número de filas abundante (71.453.209 en mi caso), y que gracias a los modificadores OFFSET...FETCH de la cláusula ORDER BY controlamos la cantidad de registros generados para la tabla numérica, se podría decir que esta técnica abarca prácticamente todas las situaciones que podamos necesitar.

Si la versión de SQL Server con la que trabajamos no soporta el uso de OFFSET...FETCH, emplearemos la cláusula TOP para limitar el número de filas devueltas por la consulta.


..........
..........
INSERT INTO Numeros (NumeroID)
SELECT TOP (30000)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM sys.all_columns AS tbl1
CROSS JOIN sys.all_columns AS tbl2;
..........
..........

Y llegados a este punto finalizamos la primera parte de este artículo en donde hemos realizado una introducción a las tablas numéricas auxiliares tanto en el aspecto conceptual como en su forma de creación. En la segunda entrega continuaremos profundizando en las técnicas de construcción de este tipo de tabla, las cuales pueden resultar de gran ayuda al desarrollador de bases de datos para resolver problemas de muy diverso tipo.

 

Referencias

[1] Jeff Moden. "The "Numbers" or "Tally" Table: What it is and how it replaces a loop". http://www.sqlservercentral.com/articles/T-SQL/62867/

[2] Itzik Ben-Gan. "Microsoft SQL Server 2012 T-SQL Fundamentals". http://blogs.msdn.com/b/microsoft_press/archive/2012/07/16/new-book-microsoft-sql-server-2012-t-sql-fundamentals.aspx

[3] Jeff Moden. "Hidden RBAR: Counting with Recursive CTE's". http://www.sqlservercentral.com/articles/T-SQL/74118/

 

Durante la instalación de SQL Server 2014, si nuestro sistema operativo es Windows 8/8.1, y no tenemos activado .NET Framework 3.5, al llegar al paso del asistente en el que se realiza la comprobación de las características seleccionadas para instalar, es probable que nos encontremos con un error que nos avisa de la falta de .NET Framework 3.5 en nuestro sistema, y que debemos instalarlo si queremos completar con éxito la instalación de esta versión del gestor de bases de datos.

  

 

Podemos resolver este problema de distinta forma en función de nuestra disponibilidad de conexión a Internet. Si esta es viable, accederemos al Panel de control de Windows, y dentro del apartado Programas, sección Programas y características, haremos clic en la opción Activar o desactivar las características de Windows.

 

Esta acción nos mostrará el cuadro de diálogo Características de Windows, en el que seleccionaremos la opción .NET Framework 3.5 (incluye .NET 2.0 y 3.0) y haremos clic en su botón Aceptar.

 

Sin embargo, como me sucedió recientemente, puede ocurrir que en el momento de realizar esta operación no dispongamos de acceso a Internet, ya que en mi caso necesitaba instalar SQL Server 2014 en una máquina virtual, y por diversas circunstancias no contaba en ese momento con conexión a la Red.

Si nos encontramos en dicha situación no debemos preocuparnos, ya que también es posible activar .NET Framework 3.5. Se trata de un proceso un poco más laborioso, pero que no reviste mayor complejidad.

En primer lugar deberemos introducir el DVD de instalación de Windows 8/8.1 en el lector de nuestra máquina, o en el caso de contar con una imagen ISO de dicho DVD, montaremos el archivo ISO correspondiente.

A continuación, en la unidad C: del equipo, crearemos una carpeta (a la que en este ejemplo hemos llamado SourcesNetFx) en la que copiaremos el contenido de la ruta \sources\sxs del disco de Windows 8/8.1, desde una ventana de símbolo de sistema abierta con privilegios de administrador.

  

Seguidamente, desde la misma ventana de símbolo de sistema, utilizaremos la herramienta DISM (Deployment Image Servicing and Management) para realizar la activación de .NET Framework 3.5 empleando el siguiente comando (sustituya el lector las letras de unidad y rutas por las que esté utilizando en su caso).

C:\>DISM /Online /Enable-Feature /FeatureName:NetFX3 /All /LimitAccess /Source:C:SourcesNetFx

 

  

Finalizada la ejecución del comando DISM, .NET Framework 3.5 ya estará activado en nuestra máquina, lo que nos permitirá continuar con la instalación de SQL Server 2014 hasta completarla adecuadamente.

 

A continuación se adjuntan varios enlaces como referencia de la operación explicada en el presente artículo. Esperamos que resulte de utilidad para todos aquellos lectores que puedan encontrarse con esta misma situación.

Instalar .NET Framework 3.5 en Windows 8.1 offline

MSDN Instalar .NET Framework 3.5 en Windows 8 u 8.1

SQL Server 2014. Descarga de evaluación

Publicado por Luis Miguel Blanco | con no comments
Archivado en: ,

Dentro de las características de Inteligencia de Negocio (BI) incluidas en Excel para el análisis de información, nos encontramos con la posibilidad de tener varias tablas dinámicas conectadas a diferentes cubos de datos, tanto en una misma hoja como repartidas en diversas hojas de un mismo archivo, permitiéndonos de esta forma disponer de múltiples vistas sobre nuestro sistema de información de forma centralizada.

Cuando los cubos que están conectados a dichas tablas dinámicas se procesan, existe la posibilidad de actualizar de forma independiente una de estas tablas mediante la opción Actualizar, perteneciente al grupo de opciones Datos, de la pestaña Analizar en la cinta de opciones de Excel; o bien usar la opción Actualizar todo, en el mismo grupo, que actualizará todas las tablas dinámicas existentes en el archivo Excel.

Sin embargo puede haber situaciones en las que solamente queramos actualizar algunas de las tablas dinámicas que componen nuestro archivo Excel, por lo que resultaría de gran ayuda disponer de un mecanismo que nos permitiera seleccionar las tablas y actualizarlas en un solo paso, evitándonos el tedioso trabajo de tener que recorrer el archivo hoja por hoja para actualizar independientemente cada tabla.

La finalidad del presente artículo será precisamente esa: el desarrollo mediante VBA y el sistema de macros de Excel de una pequeña utilidad, que ofrezca al usuario una lista de las tablas dinámicas existentes en el archivo Excel, permitiéndole seleccionar las que quiere actualizar, y realizando dicha operación de actualización en un único paso.

Para ello necesitamos en primer lugar algunos cubos de datos con los que realizar esta prueba, así que emplearemos las bases de datos AdventureWorksDW2012 y ContosoRetailDW, disponibles en CodePlex y el centro de descargas de Microsoft, creando con cada una de ellas un proyecto en SQL Server Data Tools de tipo Analysis Services Multidimensional, a los que daremos los nombres AdventureWorks y ContosoRetail respectivamente. Cada uno de estos proyectos contendrá un par de sencillos cubos de datos, tal y como muestra la siguiente figura.

 

A continuación crearemos un nuevo archivo Excel, al que añadiremos varias tablas dinámicas repartidas en diferentes hojas, conectadas a los cubos que hemos desarrollado. Este archivo, al que daremos el nombre ActualizarTablasDinamicas.xlsm, lo guardaremos con formato de Libro de Excel habilitado para macros.

Para identificar adecuadamente las tablas dinámicas que necesitemos actualizar en el proceso que vamos a desarrollar, asignaremos a cada una de ellas un nombre de la siguiente manera: posicionados en una tabla dinámica, en la pestaña de nivel superior Herramientas de tabla dinámica de la cinta de opciones, seleccionaremos la pestaña Analizar y desplegaremos la opción Tabla dinámica (primera opción a la izquierda en esta pestaña) que mostrará un cuadro de texto en el que escribiremos el nombre para dicha tabla, como vemos en la siguiente figura.

 

Seguidamente, en la pestaña Desarrollador de la cinta de opciones, dentro del grupo Código, haremos clic en la opción Visual Basic, entrando en el entorno de desarrollo de VBA.

 

Si la pestaña Desarrollador no estuviera visible tendremos que ir a la pestaña Archivo y hacer clic en Opciones. En la ventana Opciones de Excel haremos clic en Personalizar cinta de opciones, y pasaremos la pestaña Desarrollador desde la columna Comandos disponibles en hasta la columna Personalizar la cinta de opciones, lo que hará visible a esta pestaña.

 

Volviendo a la ventana principal de VBA, seleccionaremos la opción de menú Insertar | UserForm, para añadir a nuestro archivo Excel un formulario al que daremos el nombre frmActualizarTablasDinamicas, y en el que agregaremos los controles que vemos en la siguiente figura.

 

Para iniciar este formulario añadiremos ahora un módulo mediante la opción de menú Insertar | Módulo, en cuyo editor de código escribiremos el siguiente procedimiento.

 

Sub ActualizarTablasDinamicas()

Dim ofrmActualizarTablasDinamicas As frmActualizarTablasDinamicas
Set ofrmActualizarTablasDinamicas = New frmActualizarTablasDinamicas
Load ofrmActualizarTablasDinamicas
ofrmActualizarTablasDinamicas.Show

End Sub

 

Este procedimiento quedará guardado como una macro del archivo Excel, que ejecutaremos haciendo clic en la opción Macros, disponible tanto en la pestaña Desarrollador como Vista de la cinta de opciones.

 

La siguiente figura muestra el formulario en ejecución. 

 

Aunque nuestro formulario ya dispone de interfaz de usuario, todavía carece de la funcionalidad necesaria para realizar las tareas que necesitamos, por lo que entraremos en su editor de código y nos situaremos en el evento Initialize del objeto UserForm, que representa al formulario, y que se produce al iniciarse su ejecución, escribiendo el siguiente bloque de código.

 

Private Sub UserForm_Initialize()

Dim oSheet As Object
Dim oPivotTable As PivotTable

Me.lstTablasDinamicas.SpecialEffect = fmSpecialEffectSunken
Me.lstTablasDinamicas.MultiSelect = fmMultiSelectMulti
Me.lstTablasDinamicas.ListStyle = fmListStyleOption

For Each oSheet In ActiveWorkbook.Sheets
    oSheet.Select

    For Each oPivotTable In ActiveSheet.PivotTables
        Me.lstTablasDinamicas.AddItem (oSheet.Name & "-" & oPivotTable.Name)
    Next
Next

End Sub

 

Como vemos en el código anterior, después de configurar algunas de las propiedades del control ListBox utilizamos un bucle For Each para recorrer las hojas del archivo, y dentro de este, otro bucle del mismo tipo para obtener los nombres de las tablas dinámicas que pueda haber, con la finalidad de rellenar el ListBox con el nombre de la hoja y tabla dinámica.

 

Al hacer clic en el botón Actualizar, ejecutaremos el siguiente bloque de código para el evento Click de dicho botón, en el que recorreremos los elementos contenidos en el ListBox, y en aquellos en los que su casilla esté marcada, accederemos, a través de la colección PivotTables del objeto ActiveSheet, a la tabla dinámica correspondiente para actualizarla mediante el método Refresh, perteneciente a la propiedad PivotCache de la tabla dinámica.

 

Private Sub cmdActualizar_Click()

Dim nIndice As Integer
Dim sElementoLista As String
Dim aHojaTabla() As String
Dim sHoja As String
Dim sTabla As String

For nIndice = 0 To Me.lstTablasDinamicas.ListCount - 1
    If Me.lstTablasDinamicas.Selected(nIndice) Then
        sElementoLista = Me.lstTablasDinamicas.List(nIndice)
        aHojaTabla = Split(sElementoLista, "-")
        sHoja = aHojaTabla(0)
        sTabla = aHojaTabla(1)        

        ActiveWorkbook.Sheets(sHoja).Select
        ActiveSheet.PivotTables(sTabla).PivotCache.Refresh
    End If
Next

MsgBox "Actualización completada"

Unload Me

End Sub

 

Si realizamos cambios en las tablas de hechos de los cubos desarrollados para este ejemplo, como pueda ser la inserción de nuevas filas, y después de procesarlos ejecutamos el formulario, marcando algunas de las casillas del ListBox; al hacer clic en el botón Actualizar observaremos cómo dichas tablas seleccionadas actualizan sus valores.

 

En el caso de que no queramos realizar la actualización, haciendo clic en el botón Cancelar cerraremos el formulario.

 

Private Sub cmdCancelar_Click()

Unload Me

End Sub

 

Y de esta forma concluimos este pequeño truco, que esperamos pueda resultar de utilidad para todos aquellos que necesiten trabajar con diferentes vistas de cubos de datos dentro en un mismo archivo Excel.

La obtención de detalles (drillthrough) es una característica de los cubos de datos multidimensionales consistente en proporcionar a un usuario acceso a datos de la tabla de hechos que no se encuentran visibles a través de los canales habituales utilizados en la consulta del cubo: dimensiones y medidas. De este modo, cierta información del cubo que pueda considerarse reservada, solamente estará disponible para un grupo de usuarios limitado.

En el presente artículo vamos a explicar los pasos necesarios para crear una acción de obtención de detalles (drillthrough action) en un cubo desarrollado con SQL Server 2012, utilizando como base de datos AdventureWorksDW2012, la cual modificaremos según las indicaciones del siguiente apartado. 

 

Preparación de la base de datos

En primer lugar crearemos una nueva tabla de hechos con el nombre FactInternetSalesDetalles a partir de la tabla FactInternetSales.

 

SELECT *
INTO FactInternetSalesDetalles
FROM FactInternetSales; 

 

La tabla FactInternetSales original tiene una clave primaria formada por las columnas SalesOrderNumber y SalesOrderLineNumber, pero al objeto de simplificar nuestro ejemplo, en la nueva tabla FactInternetSalesDetalles añadiremos una columna con el nombre OrderNumberID, a la que asignaremos la concatenación de las dos columnas antes mencionadas, convirtiéndola en clave primaria de la tabla. 

 

ALTER TABLE FactInternetSalesDetalles
ADD OrderNumberID nvarchar(25) NULL;

UPDATE FactInternetSalesDetalles
SET OrderNumberID = SalesOrderNumber + CAST(SalesOrderLineNumber AS nvarchar(1));

ALTER TABLE FactInternetSalesDetalles
ALTER COLUMN OrderNumberID nvarchar(25) NOT NULL;

ALTER TABLE FactInternetSalesDetalles
ADD CONSTRAINT PK_FactInternetSalesDetalles PRIMARY KEY CLUSTERED (OrderNumberID); 

 

Supongamos que la tabla de hechos del cubo que vamos a construir necesita contener ciertos datos del comprador (nombre, apellido y fecha de nacimiento), considerándose éstos información sensible que no debe estar al alcance de todos los usuarios que consulten el cubo.

Por este motivo añadiremos a continuación tres nuevas columnas a esta tabla, que contendrán información relativa al cliente que ha realizado la compra. 

 

ALTER TABLE FactInternetSalesDetalles
ADD
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
BirthDate date NULL;           

                  

Mediante la siguiente sentencia asignaremos valores a estas columnas cruzando con la tabla DimCustomer, terminando por el momento con las operaciones sobre la base de datos relacional y pasando a la etapa de creación del cubo.

 

UPDATE FactInternetSalesDetalles
SET
FirstName = DimCustomer.FirstName,
LastName = DimCustomer.LastName,
BirthDate = DimCustomer.BirthDate
FROM DimCustomer
WHERE FactInternetSalesDetalles.CustomerKey = DimCustomer.CustomerKey;

 

Creación del cubo

La siguiente fase de nuestro ejemplo consistirá en crear un cubo con un modelo de datos (Data Source View) compuesto por la tabla FactInternetSalesDetalles como tabla de hechos, y las tablas DimDate, DimCurrency y DimPromotion como tablas de dimensiones. Sugerimos al lector la consulta del artículo Cubos de datos en SQL Server 2008 Analysis Services, para cualquier duda acerca de los aspectos básicos relacionados con la creación de un cubo de datos.

Iniciaremos SQL Server Data Tools y crearemos un nuevo proyecto de tipo Analysis Services Multidimensional con el nombre ObtencionDetalles, en el que definiremos el modelo de datos que acabamos de describir.

 

 

Las dimensiones tomarán los nombres FechaVenta (tabla DimDate), Moneda (DimCurrency) y Promo (DimPromotion).

 

Tanto la dimensión Moneda como Promo serán dimensiones sencillas de un único atributo, mientras que FechaVenta tendrá una jerarquía del tipo Año-Mes-Día, construida utilizando las columnas CalendarYear, MonthNumberOfYear y DayNumberOfMonth.

 

 

 

El cubo de datos, al que daremos el nombre VentasInternet, tendrá una medida llamada ImporteVentas, que realizará la suma de la columna SalesAmount de la tabla de hechos.

 

Una vez procesado el cubo y sus dimensiones, nos conectaremos desde Excel para comprobar que su funcionamiento sea correcto.

 

Como vemos en la anterior imagen, la celda seleccionada en la tabla dinámica muestra el importe de las ventas por la fecha en que se realizaron y la moneda de pago utilizada, todo ello filtrado por una campaña o promoción comercial.

Pero en algunos casos nos gustaría saber, además, a qué clientes corresponden dichas ventas, y aunque bien es cierto que podríamos resolver esta cuestión con una dimensión de clientes, como ya hemos explicado al principio del artículo, en este escenario se trata de un tipo de información especial, por lo que aquí es donde entra en liza la obtención de detalles, elemento mediante el que averiguaremos los compradores relacionados con una determinada cifra de ventas. 

 

Configurar la obtención de detalles

Para crear una acción de obtención de detalles necesitamos una dimensión que devuelva, de la tabla de hechos del cubo, aquellas columnas que deban formar parte del detalle, y que en nuestro ejemplo serían OrderNumberID, SalesAmount, FirstName, LastName y BirthDate.

Podríamos emplear directamente la tabla de hechos para crear dicha dimensión, pero en su lugar vamos a utilizar la siguiente vista sobre la tabla FactInternetSalesDetalles, que devuelve las mencionadas columnas.

 

CREATE VIEW vFactInternetSalesDetalles
AS
SELECT OrderNumberID, SalesAmount, FirstName, LastName, BirthDate
FROM FactInternetSalesDetalles;

 

La utilización de una vista para crear una dimensión orientada a servir como base del detalle de un cubo de datos resulta de gran utilidad, ya que simplifica su mantenimiento puesto que en teoría solamente contiene aquellas columnas que formarán parte del detalle.

A continuación añadiremos al modelo de datos de nuestro proyecto de Analysis Services esta vista, y la utilizaremos en la creación de una dimensión con el nombre DetalleVentas, cuyos atributos se corresponderán con sus columnas.

 

 

Como siguiente paso, abriremos el diseñador del cubo y agregaremos la dimensión recién creada. También asignaremos a su propiedad Visible el valor False, puesto que no es una dimensión orientada al usuario sino para uso interno del cubo.

 

Continuaremos haciendo clic en la pestaña Actions del diseñador del cubo para crear una nueva acción de obtención de detalle que configuraremos de la siguiente manera:

Haciendo clic en el icono New Drillthrough Action crearemos la acción de detalle. En el campo Name le asignaremos el nombre DetalleVentasClientes.

 

En el apartado Action Target, dentro de la lista desplegable Measure group members seleccionaremos el grupo de medida que asociaremos al detalle que estamos definiendo. Esto quiere decir que los registros de detalle estarán accesibles a las medidas incluidas en el grupo seleccionado, que en nuestro ejemplo será ImporteVentas, ya que es la única medida del grupo MedidasVentasInternet. 

 

Finalmente, en el apartado Drillthrough Columns, seleccionaremos la dimensión DetalleVentas, que proporcionará acceso a los registros de detalle de la tabla de hechos a través de la vista vFactInternetSalesDetalles, creada anteriormente en la base de datos; y también marcaremos aquellos atributos de la dimensión que se mostrarán al solicitar el detalle.

 

Consulta del detalle

Volviendo nuevamente a Excel, nos conectaremos otra vez al cubo mediante una tabla dinámica, situando la dimensión Moneda en columnas y FechaVenta en filas (expandiendo hasta el nivel Día).

Centrándonos en las ventas realizadas en dólares australianos correspondientes al día 10/01/2007, vemos que arrojan una cifra de 8.978,64. Para averiguar los clientes que han efectuado las compras resultantes de dicho importe, haremos clic derecho en esa celda y seleccionaremos la opción Acciones adicionales, que mostrará un submenú con las acciones definidas en el cubo para la medida ImporteValores con la que estamos trabajando, en este caso DetalleVentasClientes.

 

Como resultado de la selección de dicha opción, se creará una nueva hoja en el archivo Excel, formada por los registros de la vista vFactInternetSalesDetalles que nos proporciona la dimensión DetalleVentas, y que componen la cifra que estábamos visualizando en la tabla dinámica conectada al cubo de datos.

 

Todos los registros de detalle de esta nueva hoja se almacenan como tipo de dato texto, por lo que al intentar comprobar si la suma de la columna SalesAmount concuerda con la celda de la tabla dinámica seleccionada para detalle, no obtendremos el resultado esperado.

Para realizar esta comprobación, seleccionaremos los valores de la columna SalesAmount y los pegaremos en una nueva hoja del archivo Excel. Como además, el separador decimal usado es el punto, aplicaremos la siguiente fórmula a las celdas recién añadidas, para sustituir el punto por la coma y al mismo tiempo convertir el valor de texto a numérico.

=VALOR(SUSTITUIR(Celda;".";","))

Una vez realizada la conversión, aplicaremos la función SUMA() a la columna, obteniendo el mismo valor que el seleccionado en la tabla dinámica.

=SUMA(B1:B6)

 

 

 

Establecer los permisos de acceso al detalle

Puesto que con toda probabilidad somos administradores de la máquina en la que estamos desarrollando este ejemplo, no habremos tenido impedimentos al acceder al detalle del cubo explicado en el apartado anterior. Sin embargo, un usuario sin los adecuados privilegios no tendrá disponible de forma predeterminada esta posibilidad.

 

La forma de conceder este permiso pasa por añadir un rol al proyecto de Analysis Services del modo explicado en el artículo Establecer el acceso a cubos de datos para usuarios y grupos de usuarios. Una vez creado el rol y agregado al mismo los usuarios necesarios, en la pestaña Cubes desplegaremos la lista Local Cube/Drillthrough Access seleccionando el valor Drillthrough.

 

Después de guardar los cambios procesaremos el proyecto al completo, es decir, la base de datos multidimensional incluyendo dimensiones, cubo, etc., con lo cual, los usuarios incluidos en el rol ya tendrán acceso al detalle del cubo.

Y en este punto damos por concluido el presente artículo, en el que hemos abordado el modo de activar, en un cubo de datos multidimensional, el acceso al detalle de los registros que conforman el valor seleccionado de una medida. Espero que os resulte de utilidad.

En el anterior artículo sobre pirámides de población con PowerPivot, emplazábamos al lector a una próxima entrega dedicada a pirámides acumuladas, una variedad de pirámide demográfica, en la que cada segmento, además de indicar el sexo y rango de edad de la población a la que pertenece, permite aportar información adicional, como pueda ser el estado civil, nacionalidad, etc.

Al igual que en el resto de artículos dedicados a esta serie sobre pirámides de población, quisiera expresar mi agradecimiento a Ricard Gènova, demógrafo especializado en análisis estadístico sanitario, cuya ayuda y asistencia en todos aquellos conceptos sobre demografía en general y pirámides de población particular, han resultado claves en la elaboración de este artículo.

El objetivo del presente artículo, como acabamos de mencionar, será la construcción de una pirámide de población acumulada en Excel a partir de un modelo de datos de PowerPivot, para cuya creación partiremos, al igual que en el resto de artículos que giran alrededor de esta temática, de la base de datos PiramidePoblacion, cuyo proceso de creación se describe en el artículo sobre generación de datos demográficos, publicado anteriormente en este blog. No obstante, en esta ocasión utilizaremos SQL Server 2012 como motor de datos y Excel 2013 (Office 2013) para la capa de presentación, lo que nos permitirá disponer de las versiones más recientes de las herramientas para la construcción de la pirámide.

Entre las ventajas de utilizar Excel 2013 se encuentra el hecho de que PowerPivot ya viene integrado en el producto, aunque es probable que no esté habilitado. Para habilitarlo haremos primeramente clic en la pestaña Archivo, y a continuación en el botón Opciones de la columna izquierda.

  

En la ventana Opciones de Excel haremos clic en la opción Complementos, mostrándose la configuración de complementos en la parte derecha de la ventana.

 

En la lista desplegable Administrar seleccionaremos Complementos COM y haremos clic en el botón Ir. Una vez abierta la ventana de complementos, marcaremos la casilla Microsoft Office PowerPivot for Excel 2013.

 

Aceptando esta ventana se agregará la pestaña PowerPivot a la cinta de opciones de Excel.

 

 

La información a representar en cada segmento

El ejemplo que vamos a desarrollar consistirá en añadir a cada segmento-barra de la pirámide, la información sobre el estado civil de la población (soltero, casado, separado, etc.), de manera que el resultado sea similar al mostrado en la siguiente figura.

 

 

Ajustes sobre la estructura de la base de datos

Antes de comenzar la preparación del modelo de datos con PowerPivot debemos realizar algunas modificaciones en la base de datos PiramidePoblacion, encaminadas a incorporar los nuevos datos que usaremos para representar el estado civil de la población.

En primer lugar añadiremos la tabla EstadoCivil, que representará el catálogo de los distintos tipos de estado civil que puede tomar la población.

 

CREATE TABLE EstadoCivil
(
            EstadoCivil_ID int NOT NULL,
            EstadoCivil_DS varchar(50) NULL,
            CONSTRAINT PK_EstadoCivil PRIMARY KEY CLUSTERED (EstadoCivil_ID ASC)
)
GO

 

A continuación modificaremos la tabla Poblacion añadiendo el campo EstadoCivil_ID, que emplearemos para asignar el identificador de estado civil de cada registro de la población, y cuyo valor literal mostraremos a través de una relación con la tabla EstadoCivil en el modelo de PowerPivot.

 

ALTER TABLE Poblacion
ADD EstadoCivil_ID int NULL
GO

 

Actualizando la población con los datos de estado civil

La tabla que actuará como catálogo de los tipos de estado de civil contendrá los siguientes registros. Nótese que en la descripción del estado civil (campo EstadoCivil_DS) incluimos en primer lugar un número identificador, a efectos de conseguir más adelante una ordenación de estos valores en PowerPivot adaptada a nuestras necesidades.

 

INSERT INTO EstadoCivil
VALUES
(1,'1-Soltero'),
(2,'2-Casado'),
(3,'3-Separado/Divorciado'),
(4,'4-Viudo')

 

Puesto que no contamos con una fuente de datos que nos proporcione las cifras reales del estado civil de los individuos que forman la población, vamos a generar dicha información manualmente realizando una estimación, la cual refleje en la medida de lo  posible un escenario real.

De tal forma, a todos los registros de individuos cuya edad sea menor o igual a 20 años, les asignaremos el identificador de estado civil 1 (soltero).

 

UPDATE Poblacion
SET EstadoCivil_ID = 1
WHERE Edad_ID <= 20

 

Mientras que para el resto de registros de la tabla Poblacion, el valor del campo EstadoCivil_ID se asignará según las siguientes especificaciones. 

 

Traducido lo anterior a sentencias SQL, el código a ejecutar contra la tabla Poblacion será el siguiente. 

--///////////////////////////
--edad 25 a 70
WITH tblPoblacion AS
(
            SELECT TOP 1083719
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID BETWEEN 25 AND 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 1
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

WITH tblPoblacion AS
(
            SELECT TOP 2384182
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID BETWEEN 25 AND 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 2
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

WITH tblPoblacion AS
(
            SELECT TOP 606883
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID BETWEEN 25 AND 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 3
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

WITH tblPoblacion AS
(
            SELECT TOP 260093
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID BETWEEN 25 AND 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 4
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;


--///////////////////////////
--edad > 70
WITH tblPoblacion AS
(
            SELECT TOP 9452
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID > 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 1
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

WITH tblPoblacion AS
(
            SELECT TOP 94519
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID > 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 2
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

WITH tblPoblacion AS
(
            SELECT TOP 37808
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID > 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 3
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

WITH tblPoblacion AS
(
            SELECT TOP 330817
            FILA_ID
            FROM Poblacion
            WHERE Edad_ID > 70
            AND EstadoCivil_ID IS NULL
            ORDER BY NEWID()
)
UPDATE Poblacion
SET EstadoCivil_ID = 4
FROM tblPoblacion
WHERE Poblacion.Fila_ID = tblPoblacion.Fila_ID;

 

Como acabamos de comprobar, la técnica empleada para asignar el estado civil a cada grupo de población consiste en crear una expresión de tabla común o CTE (Common Table Expression), que contenga los registros de un determinado rango de edad elegidos aleatoriamente. Para lograr esto último hacemos uso de la función NEWID. Por último, cruzamos la expresión de tabla con la tabla Poblacion por el campo Fila_ID; de esa manera asignaremos el identificador de estado civil correspondiente en el campo EstadoCivil_ID de la tabla Poblacion.

 

 

El modelo de datos en PowerPivot

Terminados los ajustes sobre la base de datos, el siguiente paso consistirá en crear un archivo Excel con el nombre PiramideAcumulada.xlsx e importar las tablas Edad, EstadoCivil, Poblacion, Sexo y Zona a un modelo de datos en PowerPivot, tal y como se explica en el artículo inicial sobre creación de pirámides con PowerPivot.

En líneas generales, el proceso que tenemos que realizar es idéntico, con la excepción de que en esta ocasión añadiremos la tabla EstadoCivil al grupo de tablas a importar. Respecto a las relaciones necesarias entre las tablas del modelo, tendremos que añadir una nueva entre las tablas Poblacion y EstadoCivil.

 

 

La tabla dinámica con la estructura de población

Al igual que en el artículo inicial sobre creación de pirámides, el siguiente paso consistirá en añadir la tabla dinámica a la hoja de Excel, crear las medidas y formatear las celdas; con la salvedad de que para este ejemplo agregaremos el campo EstadoCivil_DS, de la tabla EstadoCivil, al bloque de columnas, situándolo debajo del campo Sexo_DS.

 

 

 

El gráfico con la pirámide acumulada

Para crear el gráfico a partir de la tabla dinámica seguiremos los pasos que se explican aquí, aunque existen algunas diferencias en dicho proceso de creación que describiremos seguidamente.

En primer lugar, el gráfico a utilizar será de tipo Barra apilada.

 

Una vez añadido el gráfico, notaremos que Excel aplica automáticamente al mismo una composición de colores predeterminada, utilizando un color distinto para cada combinación de rango de edad, sexo y estado civil. Sin embargo, nuestros requerimientos en este aspecto pasan por emplear el mismo color para cada combinación de rango de edad y sexo, cambiándolo en función del estado civil.

Para conseguir este resultado haremos clic derecho en un grupo de segmentos de la pirámide pertenecientes a un tipo de estado civil, seleccionando la opción Dar formato a serie de datos.

 

Acto seguido, se desplegará el panel de formato para serie de datos. Aquí haremos clic en el icono de relleno, eligiendo el tipo Relleno sólido y el color a aplicar para este grupo de segmentos de la pirámide.

  

También remarcaremos los bordes de los segmentos desde este mismo panel, empleando la opción Línea sólida del apartado Borde y aplicando el color negro.

 

Repetiremos esta operación para cada grupo de segmentos de estado civil, hasta dejar el gráfico de la pirámide con un aspecto similar al que vemos en la siguiente figura.

 

El cambio de posición de la leyenda supone una labor más artesanal, ya que si hacemos clic derecho en la misma y elegimos la opción Formato de leyenda, al seleccionar la opción Superior como nueva posición, se moverán a dicha ubicación los indicadores de color de la combinación sexo/estado civil.

 

 

El objetivo a conseguir en este caso consiste en mostrar las etiquetas "Hombres" y "Mujeres" encima de la pirámide, mientras que las etiquetas correspondientes al estado civil deberán visualizarse a la derecha.

En primer lugar, combinando la utilidad Recortes de Windows, y otra aplicación que nos permita manipular imágenes (Paint, PowerPoint, etc.), compondremos una imagen con las combinaciones de color/estado civil.

 

A continuación, en la pestaña Analizar, perteneciente al grupo de pestañas Herramientas del Gráfico Dinámico, dentro del apartado Mostrar u ocultar, desplegaremos la lista Botones de campo seleccionando la opción Ocultar todos.

 

De igual modo haremos clic sobre la imagen de la leyenda y la eliminaremos, dejando solamente el gráfico con la pirámide de población.

 

Como siguiente paso, insertaremos la imagen con la leyenda de estado civil que hemos creado manualmente.

 

Para las etiquetas con los literales de sexo insertaremos en la parte superior del gráfico un cuadro de texto donde añadiremos la cadena de caracteres "Hombres Mujeres"; con alineación centrada y una anchura que abarque aproximadamente los extremos más sobresalientes de la pirámide.

 

Situándonos entre ambas palabras del cuadro de texto, insertaremos espacios en blanco hasta que los literales queden en dispuestos en ambos extremos, finalizando de esta manera la confección manual de la leyenda en nuestra pirámide.

 

 

Obtener diferentes perspectivas de la pirámide mediante segmentaciones

Al igual que hacíamos en el anterior artículo sobre creación de gráficos de pirámides poblacionales, en el ejemplo que estamos desarrollando vamos a insertar una segmentación basada en la tabla Zona, que nos permita observar, también en forma de pirámide, un subconjunto de la población total con la que estamos trabajando.

Para ello nos situaremos en la tabla dinámica y seleccionaremos la pestaña Analizar, perteneciente al grupo de pestañas Herramientas de Tabla Dinámica.

 

Aquí haremos clic en la opción Insertar Segmentación de datos, que abrirá una ventana con el mismo nombre, en la que seleccionaremos el campo Zona_DS de la tabla Zona. Si no encontráramos el campo en la pestaña Activo de esta ventana, lo seleccionaremos en la pestaña Todos.

 

Una vez añadida la segmentación, la situaremos junto al gráfico.

 

Manteniendo el foco sobre la segmentación observaremos que en la cinta de opciones de Excel aparece un grupo de pestañas con el nombre Herramientas de Segmentación de Datos, compuesto por una única pestaña llamada Opciones, que contiene todas las opciones para configurar la segmentación.

Para este ejemplo que estamos desarrollando, en el apartado Segmentación de datos haremos clic en la opción Configuración de Segmentación de datos, en cuya ventana de configuración asignaremos un nuevo título a la segmentación.

 

Seguidamente elegiremos un nuevo estilo en el apartado Estilos de Segmentación de datos y añadiremos una columna adicional a la segmentación en la opción Columnas, del apartado Botones, con lo que terminaremos su configuración.

 

A partir de este punto podemos seleccionar una o varias de las zonas incluidas en la segmentación, obteniendo de esta forma diferentes perspectivas de la población, tal y como podemos apreciar en la siguiente figura, en la que se muestran dos vistas de la pirámide, una con la población total y otra filtrada por una de las zonas.

 

Como podemos apreciar, la incorporación de segmentaciones en un modelo de PowerPivot constituye una potente herramienta de análisis, así como una variante del tradicional filtro sobre los datos existente en la tabla dinámica, que actuando tanto por separado como de forma combinada, proporcionan un amplio abanico de posibilidades a la hora de analizar un modelo de datos incluido en una hoja de Excel.

Y llegados a este punto damos por concluido el presente artículo, en el que hemos expuesto el proceso de creación de una pirámide de población acumulada utilizando un modelo de datos de PowerPivot como punto de partida de la información. Esperamos que os resulte de utilidad. 

Durante el desarrollo de un cubo de datos multidimensional con SQL Server 2012 (o bien a su finalización), resulta necesario establecer una adecuada política de permisos para determinar qué usuarios podrán acceder a la información que contiene.

Con tal fin tenemos a nuestra disposición los roles, elementos integrantes de una base de datos multidimensional, mediante los cuales podremos gestionar los diferentes modos de acceso a la misma, según el usuario o grupo de usuarios que necesiten ejecutar consultas contra el cubo.

Como ejemplo introductorio en el uso de roles en una base de datos multidimensional, supongamos que hemos creado desde SQL Server Data Tools (SSDT) un proyecto de tipo Analysis Services Multidimensional, conteniendo un cubo con el nombre VentasInternet, que emplea la base de datos AdventureWorksDW2012. Este cubo está formado por dos dimensiones: Fecha (tabla DimDate) y Moneda (tabla DimCurrency), y una medida basada en la suma de la columna SalesAmount de la tabla FactInternetSales.

 

Por otro lado tenemos una serie de usuarios en nuestro sistema que necesitarán acceder a este cubo.

 

Pero si alguno de estos usuarios intenta actualmente conectarse al cubo desde Excel utilizando la opción Obtener datos externos | De otras fuentes | Desde Analysis Services, de la pestaña Datos, se encontrará con un aviso que le impide acceder al cubo, debido a que no cuenta con los permisos necesarios.

 

 

Para solucionar este inconveniente, en el proyecto de análisis de SSDT en el que hemos creado el cubo, haremos clic derecho en el nodo Roles, seleccionando la opción New Role.

 

 

Una vez creado el rol, en la pestaña Membership haremos clic en el botón Add para añadir un usuario, al que concederemos permiso a través del cuadro de diálogo de selección de usuarios.

 

A continuación, en la pestaña Cubes abriremos la lista desplegable Access seleccionando el valor Read, lo que proporcionará acceso de lectura al cubo.

  

Después de volver a desplegar la solución en el servidor de Analysis Server, el usuario que acabamos de añadir al rol ya podrá conectarse al cubo.

En el caso de contar con un elevado número de usuarios, podemos gestionar más eficazmente las políticas de acceso empleando grupos de usuarios del sistema operativo, lo que facilitará las operaciones de mantenimiento de los permisos sobre los cubos de las diferentes bases de datos de análisis que compongan nuestro sistema de información.

Como muestra hemos creado el grupo ConsultaCubosVentas, integrado por los dos usuarios restantes que hemos utilizado en este ejemplo.

 

 

Ahora bien, cuando vayamos a agregar este grupo al rol de nuestra solución de análisis en la forma explicada anteriormente podemos llevarnos una pequeña sorpresa, ya que el cuadro de diálogo de selección de usuarios es posible que nos avise de que el grupo de usuarios que intentamos añadir no existe.

 

 

Para solucionar este problema, en el cuadro de diálogo haremos clic en el botón Tipos de objeto, y en la ventana de selección que se abrirá a continuación comprobaremos que el motivo de este comportamiento reside en que el objeto Grupos no está seleccionado.

 

 

Marcando la casilla Grupos ya podremos añadir el grupo de usuarios al rol, de forma que los permisos que establezcamos en el rol se aplicarán a todos los usuarios que compongan dicho grupo.

 

 

Esperamos que este pequeño truco resulte de ayuda a todos los lectores que necesiten establecer los permisos de acceso a los cubos desarrollados a través del modelo multidimensional de Analysis Services. Aunque los ejemplos expuestos en el presente artículo se han elaborado utilizando SQL Server 2012, resultan de igual aplicación en versiones anteriores de esta plataforma. 

(Artículo publicado previamente en el número 77 de dNM+, Enero 2011)

Resulta un hecho innegable que en los últimos tiempos, el volumen de datos que las organizaciones deben manejar ha aumentado desmesuradamente. Analizar tal cantidad de datos, con el objetivo de tomar decisiones estratégicas se ha convertido en un auténtico problema. En el presente artículo realizaremos una introducción a los cubos de datos en SQL Server 2008 Analysis Services, una potente herramienta con la que podemos transformar ingentes cantidades de datos en información de utilidad.

La masificación de datos en los sistemas de información de una compañía, sin la adecuada organización ni estructuración, puede acarrear efectos negativos tales como lentitud en el análisis de su estado, o lo que es peor, la toma de decisiones estratégicas inadecuadas, ya que el hecho de disponer de millones de registros repartidos en múltiples orígenes de datos heterogéneos (bases de datos SQL Server, Access, archivos de texto plano, Excel, etc.), no tiene porque ser en todos los casos sinónimo de un sistema que proporcione información de calidad.

Para solventar este tipo de problemas, en SQL Server contamos, desde hace ya algunas versiones, con los Servicios de Análisis (SQL Server Analysis Services o SSAS) o herramientas de Inteligencia de Negocio (Business Intelligence o BI), cuyo exponente principal, el cubo de datos, permite generar información para analizar el estado de la empresa a partir del conjunto de sus fuentes de datos.

 

Aspectos conceptuales

Desde una perspectiva conceptual, un cubo de datos es una pieza más en el engranaje de un sistema de información denominado almacén de datos (data warehouse). El cubo está dotado de una maquinaria interna que le permite procesar elevados volúmenes de datos en un periodo relativamente corto de tiempo, y cuyo objetivo es siempre la obtención de un resultado numérico (importes de ventas, gastos, cantidad de productos vendidos, etc.). Estos resultados pueden cambiar en función de uno o varios filtros que apliquemos sobre el cubo. El tiempo de respuesta es mínimo gracias a que el motor de procesamiento del cubo realiza un cálculo previo de las posibles combinaciones de resultados que el usuario puede solicitar. A los diferentes resultados numéricos obtenidos se les denomina medidas, mientras que los elementos utilizados para organizar/filtrar la información reciben el nombre de dimensiones.

Representado gráficamente, un cubo de datos se mostraría como la forma geométrica de la cual toma su nombre, particionado horizontal y verticalmente en una serie de divisiones que dan lugar a múltiples celdas o casillas, las cuales identifican cada uno de los posibles resultados de las medidas, obtenidos por la intersección en cada celda de las dimensiones que conforman el cubo. La siguiente figura muestra dicha representación gráfica de un cubo, con información de ventas por productos, empleados y monedas. En los lados del cubo se sitúan las dimensiones, cuyo cruce produce los resultados numéricos en las celdas.

 

 

Observando la figura anterior, el lector puede pensar que el número de dimensiones en un cubo está limitado a las que podemos representar a través de dicha forma geométrica. Nada más lejos de la realidad, ya que un cubo puede soportar una elevada cantidad de dimensiones, que permiten cubrir sobradamente los requisitos de la información a obtener.

 

Elementos principales en un almacén de datos

Como hemos mencionado anteriormente, un cubo de datos es una de las piezas de una arquitectura más compleja: el almacén de datos, en cuyo proceso de creación están involucrados diversos componentes, que serán los encargados de tomar el dato original en bruto y pulirlo hasta convertirlo en información lista para su análisis. A continuación vemos un diagrama con las fases de este proceso de transformación.

 

Descrito este proceso a grandes rasgos, en primer lugar se realiza una operación de extracción, transformación y carga (Extract, Transform & Load o ETL) desde las fuentes de datos origen, situadas en el área operacional, a una base de datos que se encuentra en el área de integración, utilizando para ello paquetes de los Servicios de Integración (SQL Server Integration Services o SSIS), los cuales realizarán también tareas de depuración de datos.

A continuación pasaríamos a la fase de construcción del cubo, que desarrollaremos utilizando las herramientas de los servicios de análisis (SSAS). Finalmente, llegaremos a la fase de acceso a los cubos por parte de los usuarios finales, para lo que existen diversos productos tales como Reporting Services (SSRS), Excel, etc.

 

Elementos físicos. Tablas de hechos y dimensiones

A nivel físico, para construir un cubo de datos necesitamos una base de datos que contenga una tabla denominada tabla de hechos, cuya estructura estará formada por una serie de campos, denominados campos de medida, a partir de los cuales obtendremos los resultados numéricos del cubo; y por otro lado, un conjunto de campos, denominados campos de dimensión, que utilizaremos para unir con las tablas de dimensiones, a fin de poder obtener resultados filtrados por las diversas dimensiones de que conste el cubo.

El otro pilar fundamental para la creación de un cubo lo componen las tablas de dimensiones. Para cada dimensión o categoría de consulta/filtro que incorporemos a nuestro cubo necesitaremos una tabla, que uniremos con la tabla de hechos por un campo clave. Esta tabla de dimensiones actuará como catálogo de valores, también denominados atributos, que usaremos de forma independiente o combinados con otras dimensiones, para obtener resultados con un mayor grado de precisión.

 

Desarrollando un cubo de datos

Una vez explicadas las nociones básicas necesarias, entramos en la parte práctica del artículo, donde desarrollaremos nuestro propio cubo de datos. Centraremos todos nuestros esfuerzos exclusivamente en la creación del cubo, sin abordar aquí las operaciones de extracción, transformación y carga, que serían realizadas mediante paquetes SSIS, ya que estos últimos son aspectos que quedan fuera del ámbito de este artículo, quedando pendientes para una futura entrega.

En primer lugar, desde el menú de Windows iniciaremos SQL Server Business Intelligence Development Studio, cuyo acceso directo se encuentra en el grupo de programas Microsoft SQL Server 2008 R2. Se trata de una versión especial de Visual Studio preparada para desarrollar proyectos de BI, en cuyo diálogo inicial seleccionaremos como tipo de proyecto Analysis Services Project, al que daremos el nombre CuboDatosAdvWorks.

A continuación, haciendo clic derecho en el nodo Data Sources del Explorador de Soluciones, seleccionaremos la opción New Data Source, que abrirá el asistente para crear la fuente de datos del cubo, y que en nuestro caso será la base de datos de prueba AdventureWorksDW2008, cuya estructura ya se encuentra preparada para ser utilizada en el diseño de cubos de datos.

Dejaremos las opciones por defecto en el asistente hasta llegar al paso correspondiente a la conexión contra la fuente de datos, donde seleccionaremos AdventureWorksDW2008. Al llegar al paso final de este asistente veremos un resumen de la fuente de datos que hemos creado.

 

Nuestro siguiente paso consistirá en crear una vista de la fuente de datos, que nos permitirá, como su nombre indica, definir una visualización personalizada sobre la base de datos, incluyendo aquellas tablas que necesitemos para crear el cubo.

Lo que en este cubo de ejemplo vamos a medir es el importe de las ventas que los distribuidores de la compañía AdventureWorks han facturado, pudiendo consultar/filtrar los resultados por el tipo de moneda en que se ha realizado la venta, y el área geográfica a la que se ha enviado el pedido. Para ello utilizaremos la tabla FactResellerSales como tabla de hechos, siendo DimCurrency y DimSalesTerritory las tablas de dimensión.

Haciendo clic derecho en el nodo Data Source Views del Explorador de Soluciones seleccionaremos la opción New Data Source View, que abrirá un asistente en cuyo primer paso elegiremos la fuente de datos recién creada, y en el segundo las tablas que acabamos de mencionar.

 

 

Finalizado este asistente aparecerá su ventana de diseño, en la que vemos un diagrama de las tablas seleccionadas, con las relaciones existentes entre las mismas.

 

Creación de dimensiones. Dimensión básica

El siguiente paso consistirá en crear la dimensión que nos permitirá consultar/filtrar la información del cubo por el tipo de moneda con el que se realizó el pago del pedido. Haciendo clic derecho en el nodo Dimensions del Explorador de Soluciones seleccionaremos la opción New Dimension, iniciándose el consabido asistente, cuyo primer paso, Select Creation Method, dejaremos con la opción predeterminada Use an existing table. Al entrar en el paso Specify Source Information, tres listas desplegables nos permitirán configurar la información a obtener para la dimensión: con Main table elegiremos la tabla que utilizaremos en la dimensión: DimCurrency; con Key columns indicaremos la clave primaria; y finalmente, con Name column seleccionaremos el campo CurrencyName, que identificará el atributo a visualizar.

 

En el paso Select Dimension Attributes, el asistente nos ofrecerá Currency Key como atributo de la dimensión, el cual utilizaremos, pero cambiando su nombre a Currency. Un atributo es un campo, normal o calculado, perteneciente a la tabla de dimensión, que se mostrará como una etiqueta en cualquier lugar en el que la dimensión participe como parte de una consulta contra el cubo de datos.

 

Al llegar al último paso daremos el nombre Currency a la dimensión y finalizaremos el asistente, mostrándose el diseñador de dimensiones con la estructura que acabamos de crear. Observando las propiedades del atributo Currency, las más importantes son Name, que contiene el nombre que aparecerá en las consultas contra el cubo; KeyColumns, que contiene el campo clave de la tabla que se relacionará con la tabla de hechos; y NameColumn, que contiene el campo de la tabla que mostrará el valor del atributo.

 

En el caso de que necesitemos añadir más atributos a la dimensión, simplemente tendremos que arrastrar y soltar los campos desde la tabla del panel Data Source View hasta el panel Attributes de este diseñador.

Una vez creados todos los atributos procesaremos la dimensión haciendo clic en el botón Process de la barra de herramientas del diseñador, o mediante el menú de Visual Studio Build | Process. Completado el proceso de la dimensión, haremos clic en la pestaña Browser del diseñador, donde podremos examinar cómo ha quedado construida.

 

Creación de dimensiones. Dimensión jerárquica

Además de las dimensiones de un único nivel, como la que acabamos de ver en el apartado anterior, es posible crear dimensiones que agrupen los datos en varios niveles, lo que proporcionará una mayor capacidad de desagregación sobre la información del cubo cuando éste sea consultado a través de una dimensión de este tipo. A este elemento de una dimensión se le denomina jerarquía.

Pongamos como ejemplo la tabla DimSalesTerritory, incluida en el Data Source View de nuestro proyecto de ejemplo. En la misma podemos ver que la combinación de los campos SalesTerritoryGroup, SalesTerritoryCountry y SalesTerritoryRegion permite establecer varios niveles de agrupamiento para los datos.

 

Supongamos que necesitamos crear una dimensión basada en esta tabla que permita, partiendo del campo SalesTerritoryGroup, un efecto similar de "despliege" jerárquico por niveles.

Para ello, crearemos en primer lugar la dimensión utilizando el asistente en la forma explicada en el anterior apartado. El atributo seleccionado por defecto por el asistente será el correspondiente al campo SalesTerritoryKey, clave primaria de la tabla.

Una vez situados en el diseñador de dimensiones, arrastraremos desde la tabla del panel Data Source View los campos SalesTerritoryGroup, SalesTerritoryCountry y SalesTerritoryRegion, y los soltaremos en el panel Attributes de este mismo diseñador.

A continuación arrastraremos el atributo Sales Territory Group hasta el panel Hierarchies, lo cual creará una nueva jerarquía a la que cambiaremos el nombre predeterminado por Sales Territory. También depositaremos en esta jerarquía los atributos Sales Territory Country y Sales Territory Region, observando que junto al nombre de la jerarquía aparece un icono de advertencia que nos informa de que las relaciones entre los atributos de la jerarquía no están adecuadamente creadas, lo que puede afectar negativamente al proceso de la dimensión.

 

Para solucionar este inconveniente haremos clic en la pestaña Attribute Relationships, donde veremos las relaciones entre los atributos que automáticamente ha creado el diseñador.

 

Estas relaciones, sin embargo, no son válidas para nuestros propósitos, por lo que seleccionaremos las flechas del diagrama que las representan y las eliminaremos. Para crear las nuevas relaciones arrastraremos desde el atributo origen hasta el destino, hasta dejarlas tal y como apreciamos en la siguiente figura.

 

 

Antes de procesar la dimensión volveremos a la pestaña Dimension Structure para comprobar que la advertencia ha desaparecido. Por otro lado seleccionaremos todos los atributos del panel Attributes, asignando el valor False en su propiedad AttributeHierarchyVisible, con lo que conseguiremos que los atributos independientes no se muestren, ya que lo que nos interesa en este caso es explorar solamente la jerarquía. A continuación vemos el resultado de esta dimensión, con todos los elementos de la jerarquía expandidos.

 

Creación del cubo

Llegamos a la fase final en el desarrollo de nuestro proyecto de ejemplo: la construcción del cubo de datos. Empezaremos haciendo clic derecho en el nodo Cubes del Explorador de Soluciones, y seleccionando la opción Add Cube, lo que abrirá el asistente de creación, en el que dejaremos sus valores predeterminados hasta llegar al paso Select Measure Group Tables, que como su nombre indica, nos pide seleccionar la tabla que contendrá los campos que usaremos como medidas para el cubo, es decir, la tabla de hechos, que en este caso será FactResellerSales.

 

Haciendo clic en Next entraremos en el paso Select Measures, donde tendremos que seleccionar los campos que actuarán como medidas del cubo. El objetivo de este cubo consiste en averiguar el importe de las ventas realizadas por los distribuidores, por lo tanto, seleccionaremos solamente el campo SalesAmount.

 

El siguiente paso nos solicita la selección de las dimensiones que van a formar parte del cubo. Automáticamente se han detectado las dimensiones creadas por nosotros con anterioridad, las cuales ya se ofrecen seleccionadas por defecto.

 

A continuación, el asistente realiza una búsqueda en la tabla de hechos, por si algún campo pudiera ser susceptible de ser también tratado como una dimensión. Dado que no necesitamos esta característica, desmarcaremos la selección de la tabla de hechos como fuente de origen para la creación de dimensiones.

 

Y llegamos al paso final, donde daremos al cubo el nombre de VentasDistribuidores, finalizando así el asistente.

 

Como resultado obtendremos la pantalla correspondiente al diseñador de cubos, que muestra diversos elementos de importancia, tales como el panel de dimensiones, diagrama de tablas, medidas (Measures), etc.

 

Es precisamente en el panel de medidas donde aparece la medida que hemos seleccionado en el asistente, pero a la cual cambiaremos su nombre por Importe Ventas desde la ventana de propiedades. En esta misma ventana podemos observar el nombre, la función de agregado que se usa para calcular la medida, el campo de la tabla utilizado, cadena de formato, etc.

 

Para que la medida aparezca correctamente formateada, además de asignar la cadena de formato a su propiedad FormatString, en las propiedades del cubo tenemos que asignar el valor Spanish (Spain) a la propiedad Language.

Finalmente, antes de poder consultar el cubo, al igual que hicimos con las dimensiones, debemos procesarlo haciendo clic en el botón Process, que abrirá el cuadro de diálogo de procesamiento del cubo, en el que haremos clic en su botón Run.

 

Una vez que el cubo ha sido procesado, podemos consultar su contenido haciendo clic en la pestaña Browser. En el panel Measure Group expandiremos el nodo Measures hasta llegar a la medida Importe Ventas, que arrastraremos hasta la zona central del visualizador. A continuación arrastraremos la dimensión SalesTerritory hasta el margen izquierdo del visualizador. Podemos hacer clic derecho sobre esta dimensión, seleccionando la opción Expand Items, lo que producirá un despliegue de los elementos de la dimensión. Para terminar arrastraremos la dimensión Currency hasta el margen superior. Como resultado obtendremos una cuadrícula de datos en la que cada celda mostrará el valor de la medida para la intersección de las dimensiones situadas en las columnas y filas del visualizador de datos.

 

Conclusiones

En el presente artículo hemos realizado una introducción al desarrollo de cubos de datos con SQL Server 2008 Analysis Services, un componente de la familia SQL Server destinado a proporcionar soluciones de inteligencia de negocio con las que explotar el potencial de análisis que reside en los datos de las organizaciones. Las posibilidades y potencia de esta herramienta son enormes, y confiamos en que este artículo anime al lector a llevarlas a la práctica. 

Aquellos desarrolladores que migren a SQL Server 2012 desde una versión anterior de esta misma plataforma de administración de bases de datos, al comenzar a trabajar con el editor de consultas de SQL Server Management Studio (SSMS), pueden encontrarse con la desagradable sorpresa de que algunas de sus combinaciones o atajos de teclado favoritas, utilizadas para la ejecución de acciones habituales, no funcionan igual que en versiones previas del producto.

Este comportamiento, a priori extraño, del editor de consultas, viene motivado por una serie de cambios orientados hacia la integración entre SSMS y SQL Server Data Tools (SSDT), el entorno de desarrollo basado en Visual Studio 2010, que acompaña a SQL Server 2012.

A continuación, como ejemplo ilustrativo, vamos a mostrar dos de las acciones que se han visto afectadas por estos cambios, así como el modo de reasignar la combinación de teclado que originalmente tenían en versiones anteriores. Las dos acciones en cuestión corresponden a Ocultar (y mostrar) el panel de resultados de la consulta, y a Poner en minúsculas el código seleccionado en el editor.

 

Mi combinación de teclado ahora no funciona

Si después de ejecutar una consulta intentamos ocultar el panel de resultados pulsando la combinación de teclas Ctrl+R, nos encontraremos con que SSMS no reacciona a nuestra orden, mostrando además un mensaje en la esquina inferior izquierda, en el que podemos intuir que dicha combinación no es reconocida por esta aplicación, ya que se queda esperando una siguiente pulsación de teclado para ejecutar una posible acción.

  

Si por otra parte, queremos convertir el código seleccionado a minúsculas pulsando Ctrl+Mayús+L, nos llevaremos un buen susto, ya que esta combinación de teclas ahora borra el texto seleccionado en el editor. Por suerte, Ctrl+Z sigue funcionando como siempre y nos permitirá deshacer dicho borrado.

Este último cambio de funcionalidad en la combinación de teclas puede resultar especialmente incómodo, si para este caso concreto nos hemos acostumbrado a asociar la última tecla pulsada con la operación a realizar, es decir, Ctrl+Mayús+L(owercase) y Ctrl+Mayús+U(ppercase).

 

Devolviendo la combinación de teclas a su funcionalidad original

No debemos preocuparnos, sin embargo, por este nuevo funcionamiento en nuestros atajos de teclado preferidos, ya que es posible personalizarlos para que se ejecuten de la forma en que lo hacían en la versión con la que anteriormente trabajábamos.

Para ello, en primer lugar, localizaremos en el sistema de menús de SSMS la opción de menú correspondiente a la acción sobre la que queremos reasignar la combinación de teclado; que en el caso del panel de resultados es Ventana | Ocultar panel de resultados (o también Mostrar panel de resultados, según esté o no visible); y en el cambio a minúsculas es Editar | Avanzadas | Poner en minúsculas.

 

 

 

A continuación seleccionaremos la opción de menú Herramientas | Opciones, y en el cuadro de diálogo Opciones desplegaremos el nodo Entorno y haremos clic en Teclado.

En la lista de comandos que aparece en el lado derecho de esta ventana necesitamos encontrar aquellos que vamos a modificar. Para facilitar la búsqueda, en el cuadro de texto Mostrar los comandos que contengan, podemos introducir un valor aproximado  que realice las funciones de filtro. Si escribimos "ventana." se filtrará el contenido de  la lista a las opciones que dependen del menú Ventana. Seguidamente nos desplazaremos por la misma hasta encontrar el comando Ventana.Mostrarpanelderesultados. Una vez localizado, en la lista desplegable Usar nuevo método abreviado seleccionaremos Editor de consultas de SQL, y en el cuadro de texto Presionar teclas de método abreviado pulsaremos la combinación Ctrl+R.

Podemos ver la asignación de teclas actual en la lista desplegable El método abreviado lo utiliza actualmente, y una vez que estemos seguros del cambio que vamos a realizar haremos clic en el botón Asignar, de forma que Ctrl+R quedará asignada a partir de ese momento a la acción de mostrar/ocultar el panel de resultados.

 

Respecto a la nueva asignación de teclas para el cambio a minúsculas, procederemos de la misma forma que acabamos de explicar, pero usando el comando Editar.Ponerenminúsculas y la combinación de teclas Ctrl+Mayús+L.

 

Creación de una barra de herramientas personalizada

Otra técnica para acceder a las acciones que necesitemos ejecutar frecuentemente consiste en crear una nueva barra de herramientas y añadirle los botones para dichas acciones.

Empezaremos seleccionando la opción de menú Herramientas | Personalizar, y en la ventana Personalizar, pestaña Barras de herramientas, haremos clic en el botón Nuevo, creando así una nueva barra de herramientas vacía a la que daremos el nombre MisAcciones.

 

A continuación haremos clic en la pestaña Comandos de esta misma ventana, para proceder a asignar los comandos a nuestra nueva barra de herramientas. Después de hacer clic en el botón de opción Barra de herramientas, abriremos la lista desplegable situada a la derecha seleccionando MisAcciones.

Seguidamente haremos clic en el botón Agregar comando, y en la ventana del mismo nombre que se abrirá a continuación buscaremos el comando Mostrar panel de resultados. Una vez localizado haremos clic en Aceptar para añadirlo a nuestra barra de herramientas personalizada.

 

 

Haciendo clic en el botón Cerrar de la ventana Personalizar, la barra de herramientas quedará añadida junto al resto de barras de SSMS, de manera que a partir de ahora dispondremos de una forma adicional de ejecutar esta acción.

 

Podemos repetir este procedimiento tantas veces como necesitemos para componer una barra de herramientas que agrupe las acciones que más frecuentemente ejecutamos en una sesión de trabajo, evitando de esta forma tener que navegar reiteradamente por las opciones de menú de SSMS.

Y con esto damos por concluido el presente artículo, confiando en que los trucos que acabamos de exponer sirvan al lector para facilitar su trabajo diario con SSMS, permitiéndole ser más productivo con esta herramienta integrante de SQL Server 2012. Como información complementaria recomendamos al lector visitar el siguiente post del blog de Aaron Bertrand. 

La cláusula ROLLUP perteneciente a la función SUMMARIZE genera, en las columnas de agregación que especifiquemos, filas adicionales de resultados acumulados (totales parciales) para las columnas numéricas utilizadas en la consulta.

A modo de ejemplo, en la siguiente consulta utilizamos ROLLUP para la columna StoreType de la tabla DimStore, por lo que obtendremos, además de las cifras de venta por tipo de almacén, una última fila adicional con el total de las ventas.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType]),
            "Ventas", SUM('FactSales'[SalesAmount])
)

 

 

Partiendo de este resultado, supongamos ahora que necesitamos que los tipos de almacén aparezcan ordenados, pero manteniendo la fila de total al final. Si usamos la cláusula ORDER BY en la consulta, el resultado no será totalmente satisfactorio, ya que  se ordenarán los nombres, pero la fila de total quedará al principio.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType]),
            "Ventas", SUM('FactSales'[SalesAmount])
)
ORDER BY [StoreType]

 

 

Aplicando el modificador DESC a la cláusula ORDER BY solucionamos el problema solamente en parte, ya que conseguimos que el total vuelva a colocarse al final del conjunto de resultados, pero como es lógico, los nombres quedan ordenados en sentido descendente.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType]),
            "Ventas", SUM('FactSales'[SalesAmount])
)
ORDER BY [StoreType] DESC

 

 

Controlando la ubicación de los acumulados mediante ISSUBTOTAL

Si queremos ordenar los nombres en sentido ascendente, pero manteniendo la ubicación del total al final, debemos recurrir a ISSUBTOTAL, otra de las cláusulas de SUMMARIZE, la cual crea una columna de tipo lógico, en la que cada valor nos indica si la fila actual del conjunto de resultados corresponde a un acumulado (total parcial) o bien se trata de una fila normal de datos. El truco en este caso reside en aplicar una doble ordenación: primero por la columna de ISSUBTOTAL y a continuación por la de datos.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType]),
            "Ventas", SUM('FactSales'[SalesAmount]),  
             "Subtotal_StoreType", ISSUBTOTAL('DimStore'[StoreType])
)
ORDER BY [Subtotal_StoreType],[StoreType]

 

  

Ahora vamos a añadir la columna ContinentName, de la tabla DimGeography, como nueva columna de agrupación a la consulta. De esta manera, cada fila de acumulado resultante se corresponderá con el total de ventas de un continente para todos los tipos de almacén.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType]),
            'DimGeography'[ContinentName],
            "Ventas", SUM('FactSales'[SalesAmount])
)

 

  

La interpretación de los datos que obtenemos, no obstante, es poco amigable, por lo que nuevamente usaremos ISSUBTOTAL y ordenaremos las columnas tal y como muestra el siguiente bloque de código, consiguiendo que las filas de acumulado se sitúen proporcionando la información de un modo más legible.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType]),
            'DimGeography'[ContinentName],
            "Ventas", SUM('FactSales'[SalesAmount]),
            "Subtotal_StoreType", ISSUBTOTAL('DimStore'[StoreType])
)
ORDER BY [ContinentName], [Subtotal_StoreType], [StoreType]

 

 

Ya que ROLLUP admite más de una columna de agregación como parámetro, vamos a añadirle la columna ContinentName para obtener en esta ocasión, además de los acumulados anteriormente mencionados, una nueva fila de total con el importe de las ventas para todos los tipos de almacén y continentes. Al mismo tiempo crearemos con ISSUBTOTAL sendas columnas para StoreType y ContinentName, que junto a un adecuado orden facilitarán la lectura de los datos.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP('DimStore'[StoreType], 'DimGeography'[ContinentName]),         "Ventas", SUM('FactSales'[SalesAmount]),
            "Subtotal_StoreType", ISSUBTOTAL('DimStore'[StoreType]),          "Subtotal_ContinentName", ISSUBTOTAL('DimGeography'[ContinentName])
)
ORDER BY [Subtotal_StoreType], [StoreType], [Subtotal_ContinentName], [ContinentName]

 

 

Agrupar los acumulados con ROLLUPGROUP

A pesar de existir la posibilidad de utilizar varias columnas con ROLLUP, en determinadas situaciones quizá nos interese tener una única fila de acumulado para todas las columnas de agregación incluidas en esta cláusula, lo que podemos lograr  mediante ROLLUPGROUP, otra de las cláusulas de SUMMARIZE.

Esta cláusula se utiliza como parámetro de ROLLUP, colocando las columnas de agregación como parámetros de la llamada a la función ROLLUPGROUP.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            ROLLUP(ROLLUPGROUP('DimStore'[StoreType], 'DimGeography'[ContinentName])),
            "Ventas", SUM('FactSales'[SalesAmount]),
            "Subtotal_StoreType", ISSUBTOTAL('DimStore'[StoreType])
)
ORDER BY [Subtotal_StoreType], [StoreType], [ContinentName]

 

 

Cabe destacar que la información sobre ROLLUPGROUP disponible en la documentación online de la función SUMMARIZE es una aportación de Marco Russo, puesto que dicha entrada, correspondiente a la referencia de las funciones del lenguaje DAX, no disponía inicialmente de reseña alguna acerca de esta cláusula.

Para lograr un comportamiento similar en Transact-SQL haremos uso del operador ROLLUP cuando especifiquemos las columnas a agrupar, así como de la función GROUPING en la lista de columnas a mostrar de la instrucción SELECT.

 

SELECT
CASE
            WHEN (GROUPING(StoreType)=1) THEN '--Total General--'
            ELSE StoreType
END AS StoreType,
CASE
            WHEN (GROUPING(ContinentName)=1) THEN '--Acumulado por tipo almacén--'
            ELSE ContinentName
END AS ContinentName,
SUM(SalesAmount) AS Ventas
FROM DimStore
INNER JOIN DimGeography
ON DimStore.GeographyKey = DimGeography.GeographyKey
INNER JOIN FactSales
ON DimStore.StoreKey = FactSales.StoreKey
GROUP BY StoreType, ContinentName WITH ROLLUP
ORDER BY StoreType, GROUPING(ContinentName), ContinentName

 

Y llegados a este punto damos por concluida esta serie de artículos en los que hemos expuesto diversas técnicas para la creación de consultas contra modelos tabulares empleando el lenguaje DAX, espero que os resulten de utilidad. 

Si las tablas de las columnas empleadas en la consulta con SUMMARIZE no están relacionadas se producirá un error, como vemos en el siguiente bloque de código, donde combinamos las columnas StoreType y BrandName de las tablas DimStore y DimProduct, para obtener las ventas realizadas.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            'DimStore'[StoreType],
            'DimProduct'[BrandName],
            "Ventas", SUM('FactSales'[SalesAmount])
)

 

 

Tal y como se explica en el mensaje de error, la columna BrandName no existe en la tabla de entrada DimStore, utilizada como primer parámetro de la función SUMMARIZE; aunque en este caso sería más correcto decir que la tabla DimProduct, que contiene la columna BrandName, no está relacionada con la tabla de entrada usada en la consulta.

Si el primer parámetro de SUMMARIZE representa una tabla con las columnas a visualizar (directa o indirectamente a través de relaciones), en el caso de que no existan relaciones con las tablas necesarias podemos crear una tabla al vuelo con las columnas que tenemos que mostrar empleando la función GENERATE.

 

EVALUATE
SUMMARIZE(
            GENERATE(
                        VALUES('DimStore'[StoreType]),
                        VALUES('DimProduct'[BrandName])
            ),
            [StoreType],
            [BrandName],
            "Ventas tipo almacén marca producto", SUM('FactSales'[SalesAmount])
)
ORDER BY [StoreType],[BrandName]

 

 

En el caso de que el número de columnas sin relacionar que tengamos que presentar sea superior a dos, podemos optar por la función CROSSJOIN para crear la tabla de entrada utilizada por SUMMARIZE, como vemos en la siguiente consulta, donde creamos una medida calculada para realizar la suma de las ventas, a la que aplicaremos un formato monetario, y que emplearemos en una condición de filtro para evitar mostrar importes vacíos.

 

DEFINE
MEASURE 'FactSales'[Ventas] = SUM('FactSales'[SalesAmount])

EVALUATE
FILTER(
            SUMMARIZE(
                        CROSSJOIN(
                                   VALUES('DimStore'[StoreType]),
                                   VALUES('DimProduct'[BrandName]),
                                   VALUES('DimDate'[CalendarYear])
                        ),
                        [StoreType],
                        [BrandName],
                        [CalendarYear],
                        "Ventas tipo almacén-marca producto-año venta", FORMAT('FactSales'[Ventas], "Currency")
            ),
            NOT(ISBLANK([Ventas]))
)
ORDER BY [StoreType],[BrandName],[CalendarYear]

 

 

Partiendo del anterior resultado, supongamos ahora que necesitamos calcular el porcentaje que la combinación de ventas por tipo de almacén, marca de producto y año de venta ha supuesto con respecto al total de las ventas realizadas.

Para obtener estos resultados precisamos añadir al código de la consulta dos nuevas medidas calculadas: la primera, a la que llamaremos VentasTotales, realizará mediante la función SUMX la suma de la columna SalesAmount para todas las filas de la tabla FactSales; empleando la función ALL para ignorar cualquier filtro que pudiera encontrarse activo.

La segunda medida, PorcentajeVentas, efectuará la operación encargada de calcular el porcentaje de las ventas realizadas. Dicho valor será convenientemente formateado cuando lo mostremos junto al resto de columnas de la consulta.

 

DEFINE
MEASURE 'FactSales'[Ventas] = SUM('FactSales'[SalesAmount])
MEASURE 'FactSales'[VentasTotales] = SUMX(ALL('FactSales'), 'FactSales'[SalesAmount])
MEASURE 'FactSales'[PorcentajeVentas] = ('FactSales'[Ventas] / 'FactSales'[VentasTotales])

EVALUATE
FILTER(
            SUMMARIZE(
                        CROSSJOIN(
                                   VALUES('DimStore'[StoreType]),
                                   VALUES('DimProduct'[BrandName]),
                                   VALUES('DimDate'[CalendarYear])
                        ),
                        [StoreType],
                        [BrandName],
                        [CalendarYear],
                        "Ventas tipo almacén-marca producto-año venta", FORMAT('FactSales'[Ventas], "Currency"),
                        "Ventas totales", 'FactSales'[VentasTotales],
                        "Porcentaje ventas", FORMAT('FactSales'[PorcentajeVentas], "Percent")
            ),
            NOT(ISBLANK([Ventas]))
)
ORDER BY [StoreType],[BrandName],[CalendarYear]

 

 

Aunque la medida calculada VentasTotales se visualiza junto al resto de las columnas, podría perfectamente permanecer oculta, ya que su finalidad consiste en actuar como operando para hallar el porcentaje de las ventas.

Para obtener este mismo resultado mediante SQL, tendremos que establecer, al igual que en anteriores ejemplos, las combinaciones necesarias entre las tablas implicadas en la consulta.

 

SELECT StoreType, BrandName, CalendarYear,
SUM(SalesAmount) AS [Ventas tipo almacén-marca producto-año venta],
(SELECT SUM(SalesAmount) FROM FactSales) AS [Ventas totales],
FORMAT( ( SUM(SalesAmount) / (SELECT SUM(SalesAmount) FROM FactSales) ) , 'P', 'ES-ES' )  AS [Porcentaje ventas]
FROM DimStore
INNER JOIN FactSales
ON DimStore.StoreKey = FactSales.StoreKey
INNER JOIN DimProduct
ON FactSales.ProductKey = DimProduct.ProductKey
INNER JOIN DimDate
ON FactSales.DateKey = DimDate.Datekey
GROUP BY StoreType, BrandName, CalendarYear
ORDER BY StoreType, BrandName, CalendarYear

 

Empleando la tabla de resultados numéricos como tabla de entrada

En los últimos ejemplos de SUMMARIZE hemos planteado la manera de abordar la creación de una consulta introduciendo un problema consistente en la inexistencia de relaciones entre la tabla de entrada y el resto de tablas de las columnas de agregación implicadas en dicha consulta, lo cual nos llevaba a recurrir a soluciones un tanto artificiosas, tales como el empleo de las funciones GENERATE o CROSSJOIN en el código a escribir.

Observando las características de estas consultas nos percataremos de que la tabla FactSales, empleada para calcular la suma de ventas, se encuentra relacionada con las tablas utilizadas para las columnas de agregación. Si a esto unimos la capacidad de utilizar FactSales como tabla de entrada en SUMMARIZE, podremos construir una consulta más simple que evite el uso de trucos rebuscados. Como ventaja añadida, esta consulta no devuelve filas con la columna de ventas vacía, lo que también nos libera de aplicar el correspondiente filtro.

 

EVALUATE
SUMMARIZE(
            'FactSales',
            'DimStore'[StoreType],
            'DimProduct'[BrandName],
            'DimDate'[CalendarYear],
            "Ventas", FORMAT(SUM('FactSales'[SalesAmount]), "Currency")
)
ORDER BY [StoreType],[BrandName],[CalendarYear]

 

 

Las columnas de agrupación de los ejemplos con SUMMARIZE mostrados en la entrega anterior pertenecían a una misma tabla, lo cual puede resultar conveniente en determinados casos, pero con toda seguridad, en algún momento nos encontraremos ante situaciones en las que tengamos que crear una consulta para visualizar columnas pertenecientes a diversas tablas del modelo.

Gracias a la potencia que ofrece el mecanismo de relaciones del motor tabular, esta labor resulta tan simple como añadir tales columnas en la lista de parámetros de la función, siempre y cuando existan las oportunas relaciones entre las tablas integrantes de la consulta.

Observando el diagrama de nuestro modelo de datos de ejemplo vemos que la tabla DimStore se relaciona con las tablas DimGeography, DimEmployee y FactSales, por lo que podemos construir una consulta con SUMMARIZE en la que ofrezcamos al usuario la suma de los descuentos sobre las ventas, agregada por el tipo de almacén, país y nombre del gerente del almacén que ha realizado la venta.

 

EVALUATE
SUMMARIZE(
            'DimStore',
            'DimStore'[StoreType],
            'DimGeography'[RegionCountryName],
            'DimEmployee'[FirstName],
            'DimEmployee'[LastName],
            "Total descuentos", SUM('FactSales'[DiscountAmount])
)
ORDER BY [StoreType],[RegionCountryName],[FirstName],[LastName]

 

 

En SQL esta consulta es muy similar a la anterior que hemos escrito en Transact, con alguna variación en las tablas a combinar.

 

SELECT StoreType, RegionCountryName, FirstName, LastName,
SUM(DiscountAmount) AS [Total descuentos]
FROM DimStore
INNER JOIN DimGeography
ON DimStore.GeographyKey = DimGeography.GeographyKey
INNER JOIN DimEmployee
ON DimStore.StoreManager = DimEmployee.EmployeeKey
INNER JOIN FactSales
ON DimStore.StoreKey = FactSales.StoreKey
GROUP BY StoreType, RegionCountryName, FirstName, LastName
ORDER BY StoreType, RegionCountryName, FirstName, LastName

 

En la tabla DimStore, la columna StoreManager representa al empleado de la organización que realiza las funciones de gerente o director del almacén, existiendo la posibilidad de que un mismo gerente se encuentre al cargo de varios almacenes. Esto nos permite complicar un poco la consulta DAX anterior, de manera que los resultados visualizados se correspondan con los directores que administran más de un almacén.

A continuación mostramos nuevamente esta consulta con los retoques que acabamos de mencionar. Por un lado quitamos la columna StoreType de la tabla DimStore, ya que uno de los objetivos consiste en contar el número de almacenes a cargo de cada director, independientemente del tipo de almacén. Dicho recuento lo realizaremos empleando una medida calculada (cláusula DEFINE) con el nombre NumeroAlmacenes, que reutilizaremos en más de un lugar de la consulta. La función FILTER, por otra parte, nos permitirá establecer la condición para obtener los gerentes a cargo de varios almacenes.

 

DEFINE
MEASURE 'DimStore'[NumeroAlmacenes] = COUNT('DimStore'[StoreKey])

EVALUATE
FILTER(
            SUMMARIZE(
                        'DimStore',
                        'DimGeography'[RegionCountryName],
                        'DimEmployee'[FirstName],
                        'DimEmployee'[LastName],
                        "Total descuentos", SUM('FactSales'[DiscountAmount]),
                        "Número almacenes gestionados", 'DimStore'[NumeroAlmacenes]          ),
            'DimStore'[NumeroAlmacenes] > 1
)
ORDER BY [RegionCountryName],[FirstName],[LastName]

 

 

Utilizando SQL resulta necesario escribir una mayor cantidad de código para conseguir este mismo resultado, como vemos a continuación.

 

WITH
tblGerentes AS
(
            SELECT StoreManager, COUNT(*) AS NumeroAlmacenes
            FROM DimStore
            GROUP BY StoreManager
            HAVING COUNT(*) > 1
),
tblStore AS
(
            SELECT StoreKey, GeographyKey, DimStore.StoreManager, tblGerentes.NumeroAlmacenes
            FROM DimStore
            INNER JOIN tblGerentes
            ON DimStore.StoreManager = tblGerentes.StoreManager
)
SELECT RegionCountryName, StoreManager, FirstName, LastName,
SUM(DiscountAmount) AS [Total descuentos],
NumeroAlmacenes AS [Número almacenes gestionados]
FROM tblStore
INNER JOIN DimGeography
ON tblStore.GeographyKey = DimGeography.GeographyKey
INNER JOIN DimEmployee
ON tblStore.StoreManager = DimEmployee.EmployeeKey
INNER JOIN FactSales
ON tblStore.StoreKey = FactSales.StoreKey
GROUP BY RegionCountryName, StoreManager, FirstName, LastName, NumeroAlmacenes
ORDER BY RegionCountryName, StoreManager, FirstName, LastName, NumeroAlmacenes

 

En la siguiente entrega daremos otra vuelta de tuerca al uso de la función SUMMARIZE, mostrando el modo de resolver consultas en las que se encuentren involucradas tablas no relacionadas. 

La función SUMMARIZE muestra los distintos valores de una o varias columnas (denominadas columnas de agrupación) de una tabla, permitiendo además incluir expresiones que generen columnas calculadas, encargadas de devolver valores numéricos en base a las columnas de agrupación. Con ello logramos un comportamiento similar al obtenido mediante la cláusula GROUP BY de Transact-SQL.

Comenzaremos nuestro periplo acerca del uso de esta función con un ejemplo muy básico: mostrar los distintos valores de la columna RegionCountryName, perteneciente a la tabla DimGeography.

 

EVALUATE
SUMMARIZE(
            'DimGeography',
            'DimGeography'[RegionCountryName]
)
ORDER BY [RegionCountryName]

 

 

Como es lógico, podemos utilizar varias de las columnas correspondientes a la tabla pasada en el primer parámetro, también denominada tabla de entrada.

 

EVALUATE
SUMMARIZE(
            'DimGeography',
            'DimGeography'[ContinentName],
            'DimGeography'[RegionCountryName]
)
ORDER BY [ContinentName],[RegionCountryName]

 

Para conseguir un resultado equivalente en SQL podemos emplear GROUP BY, tal y como hemos mencionado anteriormente, o también la cláusula DISTINCT, ya que hasta el momento no estamos usando una operación de agregado como COUNT, SUM, etc., sobre el conjunto de resultados.

 

SELECT ContinentName, RegionCountryName
FROM DimGeography
GROUP BY ContinentName, RegionCountryName
ORDER BY ContinentName, RegionCountryName

--//////////////////////////////////////////////

SELECT DISTINCT ContinentName, RegionCountryName
FROM DimGeography
ORDER BY ContinentName, RegionCountryName

 

Supongamos ahora que de este resultado nos interesaría saber la cantidad de unidades vendidas para cada una de las combinaciones de continente y país. Para ello escribiremos una expresión que utilice la función SUM aplicada a la columna SalesQuantity de la tabla FactSales, y que añadiremos a SUMMARIZE como último parámetro.

 

EVALUATE
SUMMARIZE(
            'DimGeography',
            'DimGeography'[ContinentName],
            'DimGeography'[RegionCountryName],
            "Cantidades vendidas", SUM('FactSales'[SalesQuantity])
)
ORDER BY [ContinentName],[RegionCountryName]

 

 

En el caso de que necesitemos más columnas calculadas iremos añadiendo las expresiones correspondientes a las mismas como parámetros adicionales al final de la función, tal y como vemos a continuación, donde agregamos dos medidas para obtener el importe de venta y las ventas menos el descuento aplicado.

 

EVALUATE
SUMMARIZE(
            'DimGeography',
            'DimGeography'[ContinentName],
            'DimGeography'[RegionCountryName],
            "Cantidades vendidas", SUM('FactSales'[SalesQuantity]),
            "Importe ventas", SUM('FactSales'[SalesAmount]),
            "Venta con descuento", SUMX('FactSales', 'FactSales'[SalesAmount] - 'FactSales'[DiscountAmount])
)
ORDER BY [ContinentName],[RegionCountryName]

 

Al emplear una sentencia SQL para obtener un resultado equivalente ahora sí será necesario el uso de GROUP BY, dado que aplicamos diversas operaciones de agregado (suma) sobre varias columnas, así como la combinación de la tabla DimGeography con DimStore y FactSales, para que las columnas numéricas reflejen los valores correctos para cada continente y país.

 

SELECT ContinentName, RegionCountryName,
SUM(SalesQuantity) AS [Cantidades vendidas],
SUM(SalesAmount) AS [Importe ventas],
(SUM(SalesAmount) - SUM(DiscountAmount)) AS [Venta con descuento]
FROM DimGeography
INNER JOIN DimStore
ON DimGeography.GeographyKey = DimStore.GeographyKey
INNER JOIN FactSales
ON DimStore.StoreKey = FactSales.StoreKey
GROUP BY ContinentName, RegionCountryName
ORDER BY ContinentName, RegionCountryName

 

En la siguiente parte de esta serie abordaremos la forma de mostrar columnas de agrupación procedentes de múltiples tablas empleando SUMMARIZE. 

Más artículos Página siguiente >