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

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. 

La función TOPN devuelve un subconjunto de una cantidad determinada de las primeras filas pertenecientes a una tabla, en base a una expresión utilizada para ordenar dicha tabla.

Como primer parámetro pasaremos el número de filas a recuperar; en segundo lugar indicaremos la tabla de la que se obtendrán las filas; el tercer parámetro corresponderá a la expresión que realizará la ordenación de las filas; y opcionalmente, en el cuarto parámetro, pasaremos un número (0 ó 1) indicativo del sentido (ascendente o descendente) en que se realizará la ordenación especificada en el tercer parámetro.

Supongamos que de la tabla DimProduct queremos averiguar las siete marcas de productos (columna BrandName) que han generado más ventas (tabla FactSales, columna SalesAmount). Comenzaremos nuestro análisis del problema desde el motor relacional, ejecutando la siguiente consulta SQL, que nos devuelve todas las marcas de producto con sus totales de venta correspondientes.

 

WITH tblMarcas AS
(
            SELECT DP.BrandName,
            SUM(FS.SalesAmount) AS VentasMarca
            FROM FactSales AS FS, DimProduct AS DP
            WHERE FS.ProductKey = DP.ProductKey
            GROUP BY DP.BrandName
)
SELECT BrandName, FORMAT(VentasMarca, 'C', 'es-es') AS Ventas
FROM tblMarcas
ORDER BY VentasMarca DESC

 

 

Para obtener aquí las siete marcas con más ventas, simplemente añadiremos la partícula TOP junto a la cantidad de filas que necesitamos obtener.

 

WITH tblMarcas AS
(
            SELECT DP.BrandName,
            SUM(FS.SalesAmount) AS VentasMarca
            FROM FactSales AS FS, DimProduct AS DP
            WHERE FS.ProductKey = DP.ProductKey
            GROUP BY DP.BrandName
)
SELECT TOP 7 BrandName, FORMAT(VentasMarca, 'C', 'es-es') AS Ventas
FROM tblMarcas
ORDER BY VentasMarca DESC

 

Vamos a realizar un primer intento para conseguir este mismo resultado en DAX, utilizando la función TOPN en la siguiente consulta.

 

EVALUATE
TOPN(
            7,
            VALUES('DimProduct'[BrandName]),
            SUM('FactSales'[SalesAmount]),
            0
)

                      

Los resultados, no obstante, no serán nada satisfactorios porque, por un lado, obtenemos todos los nombres de marcas de producto, y por otro, no aparecen los importes de ventas por marca. Observando detenidamente la documentación de la función veremos que esto es completamente lógico, ya que TOPN devuelve las filas de la tabla pasada en el segundo parámetro según el resultado de aplicar la expresión del tercero.

Como primer paso para acercarnos a la solución añadiremos a la consulta una medida calculada, que será la encargada de sumar la columna SalesAmount; esta medida la emplearemos a continuación como tercer parámetro de TOPN.

 

DEFINE
MEASURE DimProduct[VentasPorMarca] = SUM(FactSales[SalesAmount])

EVALUATE
TOPN(
                       7,
                       VALUES('DimProduct'[BrandName]),
                       DimProduct[VentasPorMarca],
                       0
)

 

 

Con esto casi hemos cumplido con nuestro objetivo, tan sólo nos falta visualizar la columna con los importes de ventas, para lo cual recurriremos a la función ADDCOLUMNS, a la que pasaremos como parámetro la función TOPN y la expresión con la columna calculada que realiza la suma de las ventas.

 

DEFINE
MEASURE 'DimProduct'[VentasPorMarca] = SUM('FactSales' [SalesAmount])

EVALUATE
ADDCOLUMNS(
                       TOPN(
                                   7,
                                   VALUES('DimProduct'[BrandName]),
                                    'DimProduct'[VentasPorMarca],
                                    0
                       ),
                       "Ventas por nombre marca", FORMAT('DimProduct'[VentasPorMarca], "Currency")
)
ORDER BY 'DimProduct'[VentasPorMarca] DESC

 

 

Aunque podamos estar acostumbrados al uso de la partícula TOP en Transact-SQL, en el contexto de las consultas y expresiones con DAX es probable que el empleo de TOPN no resulte tan intuitivo como a priori cabría esperar. En este enlace se proporciona una explicación más detallada sobre dicha función, y en este post se muestra un interesante caso de uso.

A raíz de las consultas desarrolladas en la entrega anterior, supongamos que ahora debemos visualizar, además de la marca del producto y el año de venta, el nombre del país y tipo de almacén en el que ésta se ha producido; datos que encontramos en las tablas DimGeography y DimStore respectivamente.

 

CROSSJOIN. Aumentando la cantidad de tablas a combinar

Puesto que la función GENERATE admite solamente dos tablas en su lista de parámetros debemos modificar nuestra consulta, de forma que soporte la inclusión de nuevas columnas, por lo que vamos a recurrir a la función CROSSJOIN, que al igual que GENERATE, realiza un producto cartesiano de las tablas que recibe como parámetro, pero con la diferencia de poder utilizar más de dos tablas, como vemos en la siguiente consulta.

 

EVALUATE
CROSSJOIN(
            VALUES('DimGeography'[ContinentName]),
            VALUES('DimStore'[StoreType]),
            VALUES('DimProduct'[BrandName]),
            VALUES('DimDate'[CalendarYear])
)
ORDER BY [ContinentName], [StoreType], [BrandName], [CalendarYear]

 

 

Para obtener el mismo resultado en Transact-SQL podemos utilizar la cláusula CROSS JOIN, pero con el inconveniente de que el tiempo de ejecución de la consulta será notablemente mayor que en DAX.

 

SELECT DISTINCT g.ContinentName,s.StoreType,p.BrandName,d.CalendarYear
FROM DimGeography AS g
CROSS JOIN DimStore AS s
CROSS JOIN DimProduct AS p
CROSS JOIN DimDate AS d
ORDER BY g.ContinentName,s.StoreType,p.BrandName,d.CalendarYear

 

Para añadir a la consulta DAX la columna con las cifras de ventas emplearemos ADDCOLUMNS como uno más de los parámetros de CROSSJOIN.

 

EVALUATE
CROSSJOIN(
            VALUES('DimGeography'[ContinentName]),
            VALUES('DimStore'[StoreType]),
            VALUES('DimProduct'[BrandName]),
            ADDCOLUMNS(
                        VALUES('DimDate'[CalendarYear]),
                        "Ventas",FORMAT(SUMX(RELATEDTABLE(FactSales), 'FactSales'[SalesAmount]), "Currency")
            )
)
ORDER BY [ContinentName], [StoreType], [BrandName], [CalendarYear]

 

 

Tras ejecutar la consulta, con toda seguridad habremos advertido que el cálculo resultante en la columna de ventas es incorrecto, ya que para cada fila únicamente se está teniendo en cuenta la columna 'DimDate'[CalendarYear] al realizar la suma de 'FactSales'[SalesAmount]. Este resultado para cada grupo de años es el mismo que si utilizáramos la siguiente consulta SQL.

 

SELECT YEAR(DateKey) AS AñoVenta,
FORMAT(SUM(SalesAmount), 'C', 'es-es') AS ImporteVentas
FROM FactSales
GROUP BY YEAR(DateKey)
ORDER BY YEAR(DateKey)

 

  

Sin embargo, nuestro objetivo consiste en efectuar la suma de 'FactSales'[SalesAmount] para cada combinación de 'DimGeography'[ContinentName], 'DimStore'[StoreType], 'DimProduct'[BrandName] y 'DimDate'[CalendarYear]. Supongamos que tuviéramos que construir esta consulta para ejecutarla en el motor relacional. La sentencia SQL a emplear, utilizando CTEs, sería parecida a la siguiente.

 

WITH
tblGeography AS
(
            SELECT DISTINCT GeographyKey, ContinentName
            FROM DimGeography
),
tblStore AS
(
            SELECT StoreKey, StoreType, tblGeography.ContinentName
            FROM DimStore
            INNER JOIN tblGeography
            ON DimStore.GeographyKey = tblGeography.GeographyKey
),
tblProduct AS
(
            SELECT ProductKey, BrandName
            FROM DimProduct
),
tblDate AS
(
            SELECT Datekey, CalendarYear
            FROM DimDate
)
SELECT ContinentName, StoreType, BrandName, CalendarYear, FORMAT(SUM(SalesAmount), 'C', 'es-es') AS Ventas
FROM FactSales
INNER JOIN tblStore
ON FactSales.StoreKey = tblStore.StoreKey
INNER JOIN tblProduct
ON FactSales.ProductKey = tblProduct.ProductKey
INNER JOIN tblDate
ON FactSales.DateKey = tblDate.Datekey
GROUP BY ContinentName, StoreType, BrandName, CalendarYear
ORDER BY ContinentName, StoreType, BrandName, CalendarYear


 

Para obtener un resultado equivalente en DAX debemos cambiar la posición de las funciones CROSSJOIN y ADDCOLUMNS dentro de la consulta.

Como primer parámetro de ADDCOLUMNS pasaremos la llamada a CROSSJOIN, y como segundo la expresión que crea la columna calculada que realiza la suma de 'FactSales'[SalesAmount]. Adicionalmente situaremos todo este código dentro de la función FILTER, añadiendo una condición para quitar las filas que carezcan de importe de venta.

 

EVALUATE
FILTER(
            ADDCOLUMNS(
                        CROSSJOIN(
                                   VALUES('DimGeography'[ContinentName]),
                                   VALUES('DimStore'[StoreType]),
                                   VALUES('DimProduct'[BrandName]),
                                   VALUES('DimDate'[CalendarYear])
                        ),
                        "Ventas",FORMAT(SUMX(RELATEDTABLE(FactSales), 'FactSales'[SalesAmount]), "Currency")
            ),
            LEN([Ventas]) > 0
)
ORDER BY [ContinentName], [StoreType], [BrandName], [CalendarYear]

 

 

Como alternativa a lo anterior podemos volver a usar GENERATE, pasando en el primer parámetro la llamada a CROSSJOIN y en el segundo la función ADDCOLUMNS.

 

EVALUATE
FILTER(
            GENERATE(
                        CROSSJOIN(
                                   VALUES('DimGeography'[ContinentName]),
                                   VALUES('DimStore'[StoreType]),
                                   VALUES('DimProduct'[BrandName])
                        ),
                        ADDCOLUMNS(
                                   VALUES('DimDate'[CalendarYear]),
                                   "Ventas",FORMAT(SUMX(RELATEDTABLE(FactSales), 'FactSales'[SalesAmount]), "Currency")
                        )
            ),
            LEN([Ventas]) > 0
)
ORDER BY [ContinentName], [StoreType], [BrandName], [CalendarYear]

  

En la segunda parte de esta serie ilustrábamos cómo mediante el trabajo en conjunto de VALUES más ADDCOLUMNS, lográbamos crear consultas cuyos resultados ofrecieran al mismo tiempo la información de una tabla más una operación de agregado sobre otra tabla relacionada.

Pero habrá ocasiones en que esto no sea suficiente, siendo necesario añadir a la consulta datos de otras tablas del modelo, para ampliar y mejorar la información proporcionada al usuario.

Por ejemplo, tomando como punto de partida la consulta con la función VALUES de la entrega anterior, supongamos que además de la columna BrandName, necesitamos añadir al resultado la columna CalendarYear de la tabla DimDate y un cálculo basado en la suma de las ventas realizadas por la combinación de las dos columnas.

Si con ADDCOLUMNS intentáramos ejecutar una consulta como la siguiente se produciría un error, ya que de los parámetros admitidos por esta función, sólo el primero puede ser una tabla o expresión de tabla, siendo el resto expresiones que generan columnas calculadas.

 

EVALUATE
ADDCOLUMNS(
            VALUES('DimProduct'[BrandName]),
            VALUES('DimDate'[CalendarYear]),
            "Ventas por marca y año", CALCULATE(SUM('FactSales'[SalesAmount]))
)

 

GENERATE. Enriqueciendo el resultado mediante la combinación de tablas

Para resolver este problema recurriremos a la función GENERATE, que recibe como parámetro dos tablas o expresiones de tabla, generando como resultado el producto cartesiano de ambas. A continuación vemos una consulta de ejemplo que emplea esta función para combinar las tablas DimStore y DimGeography.

 

EVALUATE
GENERATE('DimStore','DimGeography')

 

Observando el resultado, apreciaremos ocurrencias de filas en la tabla DimStore que tienen una correspondencia geográfica incorrecta al combinarse con la tabla DimGeography, debido precisamente al modo en el que se realiza el cruce entre los registros de ambas tablas. En breve explicaremos cómo arreglar este aspecto en particular.

La forma de aplicar GENERATE para resolver nuestro problema consiste en pasarle como primer parámetro la función VALUES, creando así la columna con las marcas de productos; y como segundo parámetro la función ADDCOLUMNS, para crear tanto la columna de años como la columna calculada con la suma de ventas. Todo ello lo vemos en la siguiente consulta.

 

EVALUATE
GENERATE(
            VALUES('DimProduct'[BrandName]),
            ADDCOLUMNS(
                        VALUES('DimDate'[CalendarYear]),
                        "Ventas por marca y año", SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount])
            )
)
ORDER BY [BrandName], [CalendarYear]

 

En el conjunto de resultados obtenido, como ya hemos mencionado, habrá filas con el valor de la columna "Ventas por marca y año" vacías, que corresponderán a registros de la tabla FactSales para los que no se han producido ventas en la fecha con la que se están cruzando.

 

Para evitar la visualización de las filas que no tienen valor en la columna de importe de venta agregaremos a la consulta la función FILTER, en la que situaremos como primer parámetro la expresión anterior, mientras que en el segundo parámetro escribiremos una expresión que actúe como filtro, eliminando las filas sin valor de venta.

 

EVALUATE
FILTER(
            GENERATE(
                        VALUES('DimProduct'[BrandName]),
                        ADDCOLUMNS(
                                   VALUES('DimDate'[CalendarYear]),
                                   "Ventas por marca y año", SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount])
                        )
            ),
            NOT(ISBLANK([Ventas por marca y año]))
)
ORDER BY [BrandName], [CalendarYear]

 

Otra variante de la expresión de filtro podría ser la siguiente:

 

[Ventas por marca y año] > 0

 

Con cualquiera de estas formas de aplicar el filtro, el conjunto de resultados ya no mostrará importes vacíos. 

 

Con Transact-SQL podemos obtener este mismo resultado utilizando una sentencia muy similar a la última empleada, pero añadiendo la combinación con la tabla DimDate y su campo DateKey, así como el campo CalendarYear en la cláusula GROUP BY.

 

WITH tblFactSales AS
(
            SELECT DateKey, ProductKey, SalesAmount
            FROM FactSales
)
SELECT BrandName, CalendarYear, SUM(tblFactSales.SalesAmount) AS [Ventas por marca y año]
FROM DimProduct
INNER JOIN tblFactSales
ON DimProduct.ProductKey = tblFactSales.ProductKey
INNER JOIN DimDate
ON DimDate.Datekey = tblFactSales.DateKey
GROUP BY BrandName, CalendarYear
ORDER BY BrandName, CalendarYear

 

Corrección de efectos no deseados en la ordenación de números con formato

Si en la anterior consulta DAX aplicamos un formato (moneda, separador de millar, etc.) sobre la columna "Ventas por marca y año", creada dinámicamente, y ordenamos por dicha columna de la siguiente manera.

 

EVALUATE
FILTER(
            GENERATE(
                        VALUES('DimProduct'[BrandName]),
                        ADDCOLUMNS(
                                   VALUES('DimDate'[CalendarYear]),
                                   "Ventas por marca y año", FORMAT(SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount]), "Currency")
                           )
            ),
            LEN([Ventas por marca y año]) > 0
)
ORDER BY [Ventas por marca y año]

 

El resultado no se mostrará ordenado tal y como esperamos.

 

Esto es debido a que la columna "Ventas por marca y año", por causa del formateo aplicado, contiene datos de tipo carácter en lugar de numéricos, lo que produce el aparentemente extraño comportamiento en la ordenación, que en realidad no es tal, ya que se trata del modo correcto de ordenar una columna de tipo string (obsérvese también que para la condición de filtro hemos utilizado la función LEN, que se emplea en el manejo de cadenas).

Vamos a solucionar este inconveniente recurriendo de nuevo a la creación de medidas mediante la instrucción DEFINE, con la que crearemos la medida que realiza la suma de las ventas, la cual emplearemos en los siguientes puntos de la consulta: dentro de ADDCOLUMNS, como expresión de filtro, y como criterio de ordenación.

 

DEFINE                   
MEASURE 'FactSales'[Ventas] = SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount])

EVALUATE
FILTER(
    GENERATE(
        VALUES('DimProduct'[BrandName]),
        ADDCOLUMNS(
            VALUES('DimDate'[CalendarYear]),
            "Ventas por marca y año", FORMAT('FactSales'[Ventas], "Currency")
        )
    ),
    [Ventas] > 0
)
ORDER BY [Ventas] DESC

 

 

La obtención de resultados procedentes de dos tablas resulta una estupenda posibilidad de la que disponemos al escribir consultas DAX, aunque en escenarios en los que debamos manejar información de un mayor número de tablas resultará insuficiente. En la siguiente parte de esta serie abordaremos dicha cuestión. 

En la primera parte de esta serie comenzamos abordando la creación de columnas calculadas, que uníamos al resto de columnas de la tabla mediante la función ADDCOLUMNS. En esta segunda entrega veremos cómo construir consultas basadas en los valores de una única columna.

 

VALUES. Valores distintos de una columna

Aunque la función ADDCOLUMNS es adecuada para dotar de valores adicionales a una tabla, puede suceder que en determinados momentos estemos más interesados en obtener el contenido de una sola de sus columnas, y más concretamente, de los distintos valores que la componen, bien para visualizarlos o como parte de una expresión más compleja.

Para tal cometido disponemos de la función VALUES, que recibe como parámetro una columna de la que extrae sus distintos valores, retornándolos en forma de tabla de una sola columna, como vemos en el siguiente ejemplo, donde se muestran los nombres de las marcas de productos de la tabla DimProduct.

 

EVALUATE
VALUES('DimProduct'[BrandName])
ORDER BY [BrandName]

 

 

En una consulta SQL disponemos de la cláusula DISTINCT, con la que obtendremos los distintos valores de uno de los campos de la tabla.

 

SELECT DISTINCT BrandName
FROM DimProduct
ORDER BY BrandName

 

Puesto que el tipo de dato devuelto por VALUES es una tabla, y ADDCOLUMNS recibe un valor de este tipo en su primer parámetro, podemos combinar ambas funciones para crear una consulta en la que mostremos las marcas de productos de la tabla DimProduct, y una columna calculada que sume las ventas de la tabla FactSales, obteniendo como resultado las cifras de ventas por cada marca. Recordemos que para que la operación de suma se lleve a cabo correctamente emplearemos las funciones CALCULATE y SUM, o la combinación de SUMX y RELATEDTABLE.

 

EVALUATE
ADDCOLUMNS(
            VALUES('DimProduct'[BrandName]),
            "Ventas por marca", CALCULATE(SUM('FactSales'[SalesAmount]))
)
ORDER BY [BrandName]

 

 

La función DISTINCT también devuelve, al igual que VALUES, los valores únicos de una columna, por lo que en la mayoría de las situaciones los resultados serán iguales utilizando indistintamente una u otra. No obstante, en algunos casos, VALUES devolverá un valor vacío o elemento desconocido, cuando al combinar dos tablas no exista correspondencia entre alguno de sus miembros.

 

EVALUATE
ADDCOLUMNS(
            DISTINCT('DimProduct'[BrandName]),
            "Ventas por marca", CALCULATE(SUM('FactSales'[SalesAmount]))
)
ORDER BY [BrandName]

 

Desde Transact-SQL necesitamos en este caso emplear la cláusula GROUP BY, puesto que junto al campo BrandName vamos a usar la función de agregado SUM. Para la combinación entre las tablas DimProduct y FactSales definiremos sobre esta última una expresión común de tabla o CTE (Common Table Expression).

 

WITH tblFactSales AS
(
            SELECT ProductKey, SalesAmount
            FROM FactSales
)
SELECT BrandName, SUM(tblFactSales.SalesAmount) AS [Ventas por marca]
FROM DimProduct
INNER JOIN tblFactSales
ON DimProduct.ProductKey = tblFactSales.ProductKey
GROUP BY BrandName
ORDER BY BrandName

 

DEFINE y MEASURE. Creación y reutilización de medidas calculadas

Si en la anterior consulta con VALUES quisiéramos además ordenar el resultado por la columna "Ventas por marca", tendríamos que repetir la expresión que contiene la operación de suma en la cláusula de ordenación.

 

EVALUATE
ADDCOLUMNS(
            VALUES('DimProduct'[BrandName]),
            "Ventas por marca", CALCULATE(SUM('FactSales'[SalesAmount]))
)
ORDER BY CALCULATE(SUM('FactSales'[SalesAmount]))

 

Esta situación puede resultar bastante incómoda en el caso de consultas extensas, provocando además, que nuestro código se vuelva más complicado de leer y mantener.  

Una forma de evitar este tipo de reiteraciones pasa por utilizar las instrucciones DEFINE y MEASURE al comienzo de la consulta, las cuales, tal y como sus nombres indican, nos permiten definir una medida calculada que podemos reutilizar tantas veces como queramos a lo largo de la consulta. Como ventaja adicional, en el ejemplo que nos ocupa, al crear la medida con DEFINE ya no será necesario emplear la función CALCULATE.

 

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

EVALUATE
ADDCOLUMNS(
            VALUES('DimProduct'[BrandName]),
            "Ventas por marca", FORMAT(FactSales[Ventas], "Currency")
)
ORDER BY FactSales[Ventas] DESC

 

Para mejorar la visualización de los datos al crear esta nueva versión de la consulta incluimos la función FORMAT, aplicando un formato de moneda a la medida/columna calculada.

 

El siguiente paso consistirá en construir una consulta compuesta por columnas provenientes de varias tablas, cuestión esta que abordaremos en las próximas entregas. 

Como ya explicábamos en el artículo sobre creación de consultas para modelos tabulares (primera y segunda parte), el lenguaje DAX puede ser utilizado para emitir consultas contra un modelo tabular, obteniendo como respuesta un conjunto de resultados, dentro de una mecánica similar a la que estamos acostumbrados a emplear cuando trabajamos con el motor relacional, es decir, mediante sentencias SQL del estilo "SELECT ListaDeCampos FROM Tabla". Esta funcionalidad del lenguaje recibe el nombre de Consultas DAX (DAX Queries), siendo la instrucción EVALUATE la encargada de llevarla a cabo empleando sentencias del tipo "EVALUATE Expresión".

Aunque inicialmente, las consultas DAX no estaban disponibles en el lenguaje cuando PowerPivot hizo su primera presentación, la llegada de SQL Server 2012, que posiciona a Business Intelligence Semantic Model (BISM) como el nuevo paradigma en el desarrollo de soluciones de inteligencia de negocio, ha supuesto una ampliación de las características del lenguaje, que ahora nos va a permitir escribir consultas contra los nuevos modelos tabulares pertenecientes a BISM.

Con el presente artículo iniciamos una serie en la que continuaremos la línea de exploración iniciada en el artículo antes mencionado, por lo que son recomendables, al menos, unos conocimientos básicos sobre DAX, de forma que los ejemplos que vamos a exponer puedan ser seguidos adecuadamente. Adicionalmente, en aquellos casos en que se estime oportuno, mostraremos el código de la consulta tanto en lenguaje DAX como en Transact-SQL, lo que nos permitirá observar las diferencias entre ambos a la hora de abordar la resolución de un mismo problema.

 

El proyecto de ejemplo

Como base de los ejemplos que iremos desarrollando vamos a utilizar el modelo ContosoTabular, anteriormente creado aquí; aunque en esta ocasión volveremos a abrir el proyecto de dicho modelo en SQL Server Data Tools (SSDT) para añadir la tabla DimEmployee (necesaria en alguna de las diversas pruebas a realizar), tras lo cual desplegaremos nuevamente el modelo en el servidor de análisis de SQL Server, actualizando así los cambios realizados. A continuación ejecutaremos SQL Server Management Studio (SSMS) abriendo una ventana de consulta, que a partir de este momento utilizaremos para ilustrar las funciones empleadas en la construcción de consultas DAX.

  

ADDCOLUMNS. Creación dinámica de columnas

La función ADDCOLUMNS permite crear una o varias columnas calculadas a partir de una tabla o expresión de tabla. Como primer parámetro situaremos la tabla a la que se añadirá la columna(s), definiendo a continuación cada una de las columnas mediante una cadena para el título y una expresión DAX con la fórmula para el cálculo de la columna.

En la siguiente sentencia mostramos la tabla DimStore junto a una columna calculada que duplica el número de empleados de cada almacén.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "Empleados Duplicados", [EmployeeCount] * 2
)

 

  

Para obtener el mismo resultado utilizando la instrucción SELECT en Transact-SQL escribiremos la siguiente consulta.

 

SELECT *, EmployeeCount * 2 AS [Empleados Duplicados]
FROM DimStore

 

Continuando con la tabla DimStore, supongamos que de la columna OpenDate, que contiene la fecha de apertura del almacén, queremos obtener la parte correspondiente al mes como nombre. Una posibilidad, aunque no la más óptima, como veremos enseguida, sería utilizar la siguiente consulta, en la que combinando ADDCOLUMNS con las funciones MONTH y SWITCH, obtenemos una nueva columna con el nombre del mes correspondiente a OpenDate.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "Mes Apertura", SWITCH(MONTH([OpenDate]),
                        1,"Enero",
                        2,"Febrero",
                        3,"Marzo",
                        4,"Abril",
                        5,"Mayo",
                        6,"Junio",
                        7,"Julio",
                        8,"Agosto",
                        9,"Septiembre",
                        10,"Octubre",
                        11,"Noviembre",
                        12,"Diciembre"
            )
)

 

 

La función MONTH devuelve el número del mes de la fecha pasada como parámetro. Si este resultado lo pasamos a su vez como parámetro a la función SWITCH, para cada número retornamos una cadena con el nombre del mes correspondiente.

Pero como ya decíamos antes, existe una forma mucho más simple de obtener el mismo resultado, consistente en utilizar la función FORMAT, a la que pasaremos como primer parámetro la columna de fecha, y como segundo la cadena con el formato que deseamos aplicar. La siguiente expresión consigue el mismo resultado que la anterior pero con mucho menos código.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "Mes Apertura", FORMAT([OpenDate], "MMMM")
)

 

En Transact-SQL también disponemos de la función FORMAT que se utiliza como vemos a continuación.

 

SET LANGUAGE Spanish

SELECT *, FORMAT(OpenDate, 'MMMM') AS [Mes Apertura]
FROM DimStore

 

Compliquemos ahora un poco la expresión utilizada para crear la columna calculada: supongamos que necesitamos averiguar la cantidad de años transcurridos desde la fecha de apertura del almacén (columna OpenDate) hasta la fecha actual, teniendo en cuenta que si existe valor en la columna CloseDate significará que el almacén ya ha sido cerrado.

La expresión de columna para resolver esta situación implicará, pues, el uso de las siguientes funciones: YEAR, para extraer el año de las columnas de fecha; NOW, para obtener la fecha actual; ISBLANK, para comprobar la existencia de valor en la columna CloseDate; e IF, para determinar la operación que realiza el cálculo de años según la columna CloseDate tenga o no valor.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "Años actividad almacén", IF(ISBLANK([CloseDate]),
                        YEAR(NOW()) - YEAR([OpenDate]),
                        YEAR([CloseDate]) - YEAR([OpenDate])
            )
)

 

 

En SQL usaremos la función DATEDIFF para hallar la diferencia entre las fechas. La expresión CASE nos servirá para utilizar en el cálculo la fecha actual o la de cierre del almacén si la primera es nula.

 

SELECT *,
CASE
            WHEN CloseDate IS NULL THEN
                        DATEDIFF(year, OpenDate, GETDATE())
            ELSE
                        DATEDIFF(year, OpenDate, CloseDate)
END AS [Años actividad almacén]
FROM DimStore

 

Las columnas calculadas creadas hasta el momento con la función ADDCOLUMNS se basan en operaciones sobre las columnas de la propia tabla DimStore, pasada como primer parámetro a la función. No obstante, también es posible generar columnas dinámicas que pertenezcan a otras tablas del modelo tabular con las que DimStore guarda relación.

Observando el diagrama de tablas del modelo tabular en SSDT, vemos que DimStore mantiene relaciones con las tablas FactSales, DimGeography y DimEmployee.

 

Es por lo tanto perfectamente posible crear una columna calculada con ADDCOLUMNS que muestre la información de la columna RegionCountryName, perteneciente a la tabla DimGeography, ya que se relaciona con la tabla DimStore a través de la columna GeographyKey, común en ambas tablas. Es necesario tener en cuenta que en el momento de crear la expresión de la columna calculada debemos indicar explícitamente la existencia de dicha relación utilizando la función RELATED. Esta nueva columna tendrá como título "País ubicación".

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "País ubicación", RELATED('DimGeography'[RegionCountryName])
)

 

Además, tal y como explicábamos al principio de este apartado, con ADDCOLUMNS podemos crear varias columnas calculadas al mismo tiempo, por lo que añadiremos a la consulta anterior otra expresión con el título "Ventas almacén", que realice, empleando la función SUM, la suma de la columna SalesAmount, perteneciente a la tabla FactSales, con la que también se relaciona DimStore a través del campo StoreKey.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "País ubicación", RELATED('DimGeography'[RegionCountryName]),
            "Ventas almacén", SUM('FactSales'[SalesAmount])
)

 

 

Pero observando el resultado obtenido vemos que hay algo que no funciona correctamente, ya que la finalidad de la anterior consulta consiste en obtener la suma del campo SalesAmount para cada uno de los almacenes, y lo que hemos conseguido es que el valor de la columna calculada "Ventas almacén" muestre en todos los casos la suma total de SalesAmount, lo cual no es el objetivo que perseguimos.

Podemos solucionar este problema de dos formas: la primera consiste en sustituir la función SUM por SUMX, que sobre una tabla pasada como primer parámetro realiza la suma de la columna pasada en el segundo parámetro. Aquí también debemos indicar que existe una relación entre DimStore y FactSales, para lo que usaremos la función RELATEDTABLE.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "País ubicación", RELATED('DimGeography'[RegionCountryName]),
            "Ventas almacén", SUMX(RELATEDTABLE('FactSales'), 'FactSales'[SalesAmount])
)

 

De esta forma la suma ya se realizará de forma separada para cada almacén. 

 

La otra vía de abordar la suma de las ventas por almacén consiste en utilizar la función CALCULATE, a la que pasaremos como parámetro la operación de suma. CALCULATE se encargará de evaluar dicha expresión para cada uno de los almacenes (valores distintos del campo StoreKey) existentes en la tabla DimStore, obteniendo de esta manera el resultado deseado.

 

EVALUATE
ADDCOLUMNS(
            'DimStore',
            "País ubicación", RELATED('DimGeography'[RegionCountryName]),        
            "Ventas almacén", CALCULATE(SUM('FactSales'[SalesAmount]))
)

La función CALCULATE puede revestir cierta complejidad inicial, por lo que recomendamos visitar el siguiente post del blog de Marco Russo, en el que realiza una detallada explicación acerca de su funcionamiento.

Para obtener este resultado con Transact-SQL usaremos una combinación de tipo LEFT JOIN entre las tablas DimStore y DimGeography, además de una subconsulta sobre la tabla FactSales, que realice la suma del campo SalesAmount con la función SUM.

 

SELECT S.*, G.RegionCountryName,
(SELECT SUM(F.SalesAmount) FROM FactSales AS F WHERE F.StoreKey = S.StoreKey) AS [Ventas almacén]
FROM DimStore AS S
LEFT JOIN DimGeography AS G
ON S.GeographyKey = G.GeographyKey

 

Como hemos podido comprobar, la función ADDCOLUMNS resulta de gran utilidad a la hora de añadir a nuestras consultas información adicional en forma de columnas calculadas. En las siguientes entregas explicaremos diversas técnicas y funciones del lenguaje, mediante las que conseguiremos seleccionar un subconjunto de columnas de la tabla, evitando la necesidad de obtener la totalidad de las mismas. 

Una de las primeras cosas que busqué en el modo de escritorio tradicional al instalar el nuevo Windows 8 fue el botón "Mostrar escritorio". Se trata de una opción que particularmente considero muy útil y cómoda para limpiar el escritorio cuando está plagado de ventanas. Sin embargo, en un principio sólo encontré esta posibilidad haciendo clic derecho en la barra de tareas.

 

Tras publicar este post, Antón Molleda (al que agradezco su interés y ayuda) envió un acertado comentario indicando que en el extremo derecho de la barra de tareas, si bien no tiene la forma de un botón, podemos seguir haciendo clic, provocando la desaparición de las ventanas actualmente visibles en el escritorio (efecto que también conseguiremos con la combinación de teclas Windows+D).

Si además de todo esto queremos contar con la posibilidad de disponer de un acceso directo anclado en la barra de tareas para lograr el mismo resultado, podemos seguir lo sencillos pasos que se comentan a continuación. 

En primer lugar haremos clic derecho en una zona vacía del escritorio, seleccionando la opción "Nuevo | Acceso directo". En la ventana de creación del acceso directo que aparecerá seguidamente, escribiremos la siguiente instrucción en el campo de ubicación del elemento: 

C:\windows\explorer.exe shell:::{3080F90D-D7AD-11D9-BD98-0000947B0257}

 

En el siguiente paso daremos un nombre al acceso directo y haremos clic en Finalizar para completar su creación.

 

El icono del acceso directo recién creado es igual al del Explorador de archivos, por lo que puede resultar una buena idea cambiarlo. Haciendo clic derecho sobre el icono seleccionaremos la opción Propiedades, abriéndose una ventana del mismo nombre, en la que haremos clic en el botón "Cambiar icono..."; lo que a su vez abrirá el cuadro de diálogo para realizar esta operación.

Podemos elegir entre los iconos que acompañan al archivo explorer.exe, ofrecido por defecto, o bien usar alguno de los siguientes archivos, que también adjuntan colecciones de iconos. 

%SystemRoot%\system32\Shell32.dll

%SystemRoot%\system32\imageres.dll

 

Aceptando ambas ventanas confirmaremos el cambio de icono para el acceso directo, por lo que ya podemos hacer doble clic sobre el mismo para ocultar todas las ventanas del escritorio.

 

Como detalle final podemos hacer clic derecho en el acceso directo y seleccionar la opción "Anclar a la barra de tareas", para tener esta opción disponible de forma mucho más inmediata.

 

Espero que os resulte de utilidad. 

Publicado por Luis Miguel Blanco | 2 comment(s)
Archivado en:

En la primera parte de este artículo explicábamos la forma de crear un modelo tabular en SQL Server 2012 para su posterior consulta utilizando algunas de las técnicas que ilustraremos en esta segunda entrega.

 

Consultas DAX

Todos aquellos lectores que hayan tenido la oportunidad de trabajar con PowerPivot conocerán en mayor o menor medida el lenguaje DAX y la capacidad que otorga al usuario de construir, en un modelo de datos, expresiones en forma de columnas y medidas calculadas para obtener los resultados analíticos requeridos.

En lo que respecta al desarrollo de modelos tabulares, a las funcionalidades que acabamos de mencionar se suma ahora la posibilidad de escribir consultas con las que obtener conjuntos de resultados a partir de las tablas del modelo.

Para ello haremos uso de la instrucción EVALUATE que representa la pieza fundamental en la construcción de consultas DAX contra un modelo tabular, acompañando a la misma el nombre de la tabla a obtener, tal y como vemos en el siguiente ejemplo.

 

EVALUATE DimStore;

 

Para poder apreciar el resultado de la ejecución de esta sentencia iniciaremos SSMS y nos conectaremos a Analysis Server. A continuación haremos clic en el modelo ContosoTabular recién desplegado en el servidor, que aparece como nodo dependiente de la instancia de Analysis Server, y seleccionaremos la opción de menú "File | New | Query with Current Connection", que abrirá una ventana de consultas MDX que en este caso utilizaremos para escribir sentencias DAX. Antes de proseguir aclararemos al lector que cuando nos encontramos trabajando con modelos tabulares, el editor de consultas de SSMS, a pesar de guardar los archivos de sentencias con la extensión MDX, soporta tanto consultas DAX como MDX.

 

Si ya hemos consultado la documentación oficial que sobre la instrucción EVALUATE existe, veremos que es posible ordenar el conjunto de resultados obtenido mediante la cláusula ORDER BY, de tal forma que si modificamos nuestra consulta de la manera mostrada en la siguiente sentencia, los datos aparecerán ordenados por la columna StoreManager.

 

EVALUATE DimStore
ORDER BY [StoreManager];

 

Podemos combinar varias columnas a la hora de ordenar, así como alterar el sentido de la ordenación con los modificadores ASC y DESC.

 

EVALUATE DimStore
ORDER BY [StoreManager] ASC, [StoreKey] DESC;

 

 

Pero aún hay más respecto a la ordenación, ya que usando la subcláusula START AT conseguiremos que los datos comiencen a mostrarse a partir de un determinado valor, perteneciente a la columna(s) que participa en el orden.

 

EVALUATE DimStore
ORDER BY [StoreManager] START AT 75;

 

Dedicaremos un próximo artículo a profundizar en el uso de EVALUATE y el resto de instrucciones DAX especialmente desarrolladas para el trabajo con modelos tabulares.

 

Elementos calculados. Medidas y columnas

En un modelo de datos tabular, además de la información que obtenemos de las columnas de una tabla, podemos encontrarnos con columnas y medidas calculadas que no forman parte de la estructura original de dicha tabla, sino que son producto de una expresión/fórmula DAX. En este apartado haremos una primera aproximación a este tipo de elementos de un modelo.

Pongamos por ejemplo que para la tabla FactSales queremos saber cuánto ha supuesto el total de ventas, por lo que tendremos que aplicar una operación de suma sobre la columna SalesAmount.

En primer lugar nos situaremos en dicha tabla dentro del diseñador. Como podemos comprobar, la cuadrícula de datos se divide en dos zonas: la superior, correspondiente a los propios registros de la tabla; y la inferior, reservada a las medidas calculadas, y que actualmente se encuentra vacía.

Para crear la medida que sume las filas de la columna SalesAmount haremos clic en una celda vacía de la zona inferior de dicha columna, y a continuación escribiremos la siguiente expresión en la barra de fórmulas del diseñador.

 

TotalVentas:=SUM([SalesAmount])

 

De esta forma obtendremos una medida con el nombre TotalVentas, que mostrará el resultado de la operación de suma sobre la columna.

 

También conseguiremos el mismo resultado mediante la opción de menú "Column | AutoSum | Sum", con la diferencia de que el nombre de la medida (Sum of SalesAmount) será asignado automáticamente, no obstante, siempre podemos recurrir a la ventana de propiedades de la medida y cambiarle el nombre en su propiedad Measure Name.

Pasemos seguidamente a la creación de una columna calculada, donde vamos a suponer que necesitamos una nueva columna para la tabla FactSales, en la que se realice una operación sobre la columna SalesAmount, que arroje como resultado el valor de dicha columna para la fila actual menos el 10%. Esto lo conseguiremos haciendo clic en una celda de la primera columna vacía disponible en la tabla y escribiendo la siguiente sentencia en la barra de fórmulas.

 

=[SalesAmount] - ([SalesAmount] * 0,1)

 

A continuación asignaremos el nombre a esta nueva columna haciendo doble clic en su cabecera y escribiendo el valor VentasConDescuento.

 

Tras la creación de estos miembros calculados proseguiremos con otro de los modos de consulta a nuestra disposición: las consultas MDX.

 

Consultas MDX

Aunque MDX fue desarrollado como lenguaje de consulta para cubos OLAP (modelos multidimensionales), en SQL Server 2012 ha sido adaptado para poder interaccionar también con modelos tabulares, de manera que cuando lo empleamos dentro de este contexto, el nombre del modelo (archivo bim del proyecto en SSDT) representa al cubo de datos, mientras que las tablas identifican a las dimensiones. Las medidas que hayamos calculado en las diferentes tablas quedarán agrupadas en un nodo aparte, funcionando como una dimensión especial, lo que vemos al abrir el modelo en SSMS.

 

Para hacer una consulta MDX contra el modelo emplearemos la instrucción SELECT, donde al  igual que si consultáramos un cubo OLAP, especificaremos, mediante la cláusula ON COLUMNS, la dimensión que ubicaremos en el eje de las columnas, y que en este caso será la medida TotalVentas, ya que como hemos mencionado, las medidas del modelo también pueden comportarse como una dimensión al crear la consulta.

Con la cláusula ON ROWS indicamos que la dimensión utilizada para las filas será DimGeography, visualizando el nombre de las provincias o estados de los países con la función Children de MDX aplicada al atributo StateProvinceName.

 

SELECT

{[Measures].[TotalVentas]} ON COLUMNS,

{[DimGeography].[StateProvinceName].Children} ON ROWS

FROM [Model];

 

 

Agregando la cláusula WHERE a la consulta podremos aplicar filtros al resultado. Por ejemplo, si queremos que las cifras de ventas se circunscriban solamente a las ventas de productos de color azul (tabla DimProduct, atributo ColorName), emplearemos la siguiente consulta.

 

SELECT

{[Measures].[TotalVentas]} ON COLUMNS,

{[DimGeography].[RegionCountryName].Children} ON ROWS

FROM [Model]

WHERE ([DimProduct].[ColorName].&[Azul]);

 

 

Podemos desplazar la medida TotalVentas a la cláusula WHERE y poner en las columnas una selección de los años en los que se realizaron las ventas. Para especificar dichos años como un rango, simplemente hemos de poner el primer y último elemento del mismo separados por dos puntos, tal y como vemos a continuación.

 

SELECT

{[DimDate].[CalendarYear].&[2007] : [DimDate].[CalendarYear].&[2010]} ON COLUMNS,

{[DimGeography].[RegionCountryName].Children} ON ROWS

FROM [Model]

WHERE ([Measures].[TotalVentas]);

 

 

Excel como herramienta de consulta

Y llegamos a Excel, el tercero de los tipos de consulta contra modelos tabulares que estamos presentando en este artículo.

Cuenta como gran ventaja con una potente interfaz de usuario, lo que nos permite construir informes de gran riqueza ya que pone a nuestra  disposición un amplio abanico de elementos visuales tales como tablas dinámicas, gráficos, estilos, formatos, etc.

Una vez iniciado Excel, para acceder al contenido de un modelo tabular haremos clic en la pestaña Datos de la cinta de opciones, y a continuación, dentro del grupo "Obtener datos externos", haremos clic en la opción "De otras fuentes" seleccionando en la lista desplegable el valor "Desde Analysis Services".

 

Como resultado se abrirá un asistente en el que especificaremos el servidor\instancia a la que queremos conectarnos.

 

En el siguiente paso seleccionaremos la base de datos que contiene el modelo tabular (ContosoTabular) junto al propio modelo, que en el asistente estará identificado como un cubo.

 

En el último paso guardamos la información de conexión en un archivo.

 

Como resultado de la ejecución del asistente se establecerá una conexión contra el modelo tabular, y en la hoja de cálculo se creará una tabla dinámica que nos servirá como vehículo para presentar los datos del modelo. En el panel de campos de la tabla dinámica tendremos a nuestra disposición las tablas y medidas que hayamos definido en el modelo, que arrastraremos, bien directamente a la tabla o a los bloques denominados Valores, Etiquetas de fila/columna y Filtro de informe, hasta componer un informe similar al que podemos ver a continuación, donde analizamos las ventas de productos de color azul en función de los años y el país en que se ha producido la venta.

 

Además podemos añadir un gráfico sincronizado con los valores numéricos desde la opción Gráfico dinámico, perteneciente a la pestaña Opciones de la pestaña de nivel superior Herramientas de tabla dinámica. En dicho gráfico también es posible aplicar filtros al igual que desde la tabla dinámica.

 

Y llegados a este punto damos por concluida esta introducción dedicada a la elaboración de consultas contra los nuevos modelos tabulares de SQL Server 2012, espero que resulte de ayuda a todos los que quieran empezar a dar sus primeros pasos con esta interesante tecnología.

Los Servicios de Análisis de SQL Server 2012 (SQL Server Analysis Services, SSAS) han experimentado un importante cambio con la introducción del Modelo Semántico de Inteligencia de Negocio (Business Intelligence Semantic Model, BISM), un nuevo paradigma en la construcción de sistemas analíticos, que aúna el tradicional modelo multidimensional (Unified Dimensional Model, UDM), propio de los tradicionales cubos OLAP, con el novedoso modelo tabular, basado en un motor de datos relacional, el cual propugna una filosofía de trabajo más simple (aunque no menos potente en cuanto al volumen de datos a manejar) que nos permita resolver determinados problemas de una forma más sencilla y ágil que la requerida habitualmente para desarrollar un modelo multidimensional.

Y es en estos últimos, los modelos de datos tabulares, sobre los que vamos a concentrar nuestra atención a lo largo del presente artículo, más concretamente en la capacidad de elaborar consultas y métricas contra dichos modelos, las cuales nos permitan recabar la información necesaria acerca de su estado.

Para la confección de estas consultas emplearemos DAX (Data Analysis eXpressions), el lenguaje desarrollado con el objetivo de crear expresiones analíticas tanto para modelos de datos en PowerPivot como para modelos tabulares en BISM. También usaremos MDX para acceder a una fuente de datos tabular como si de un cubo OLAP se tratara. Finalizaremos nuestro periplo con Excel, como ejemplo de aplicación de usuario final dotada igualmente de la posibilidad de obtener información del modelo, presentándola con la conocida potencia gráfica de esta herramienta.

 

Creando el modelo tabular

Lo primero que necesitamos para poner en práctica lo explicado en el párrafo anterior es un modelo de datos tabular que podamos utilizar como banco de pruebas; así que comenzaremos arrancando SQL Server Data Tools (SSDT), el entorno de desarrollo para SQL Server 2012 basado en Visual Studio, que se encuentra en el mismo grupo de programas de SQL Server, y empleando el tipo de proyecto Analysis Services Tabular Project, crearemos un nuevo proyecto con el nombre ContosoTabular que usará ContosoRetailDW como fuente de datos.

  

El siguiente paso que daremos a continuación consistirá en la importación de las tablas de la base de datos ContosoRetailDW, que posteriormente utilizaremos en nuestras operaciones de análisis contra el modelo tabular, por lo que mediante la opción de menú "Model | Import From Data Source" de SSDT, ejecutaremos el asistente de importación de datos Table Import Wizard, en cuyo primer paso elegiremos el tipo de origen al que deseamos conectarnos.

  

En el siguiente paso introduciremos el servidor e instancia de SQL Server al que nos conectaremos (en este caso EVENDIM como servidor y SQLTABULAR como instancia), así como el nombre de la base de datos desde la que importaremos las tablas. También elegiremos Autenticación de Windows como método de conexión.

 

A continuación deberemos facilitar las credenciales de conexión que Analysis Services utilizará para conectarse a la base de datos al realizar el proceso de importación. En este caso emplearemos la cuenta del usuario que ha iniciado sesión en el equipo desde el que se desea conectar, aunque también podríamos haber usado la cuenta del servicio de Analysis Services, tal y como se explica en el siguiente enlace de este mismo blog.

 

Seguidamente podemos elegir el modo en que se realizará la importación de los datos: mediante una selección de las tablas/vistas de la base de datos o usando una consulta.

 

Para este ejemplo nos decantaremos por la primera opción, seleccionando las siguientes tablas: DimStore, DimGeography, DimDate, DimProduct, DimProductSubcategory y FactSales.

 

Completado este paso haremos clic en el botón Finish, que dará comienzo al proceso de importación, a cuyo término veremos la ventana de resultados, en la que si todo ha marchado bien, observaremos el detalle de las tablas y registros importados.

 

Una vez terminado el proceso de importación, el asistente nos devolverá a la ventana del diseñador del modelo tabular, donde tendremos a nuestra disposición las tablas recién importadas, organizadas en diversas pestañas.

 

También es posible visualizar estas tablas en forma de diagrama mediante la opción de menú "Model | Model View | Diagram View", lo que nos permitirá seleccionar la visualización de sus columnas, medidas, jerarquías, etc., así como las relaciones existentes entre estas.

 

Para retornar a la anterior vista del diseñador usaremos la opción "Model | Model View | Data View".  Podemos conmutar entre estas dos vistas empleando los botones situados en la parte inferior derecha del diseñador.

 

Despliegue del modelo

Finalizada por el momento la etapa de desarrollo del modelo, procederemos a efectuar el despliegue del mismo en el servidor de análisis, de modo que pueda ser consultado por aquellas aplicaciones cliente que lo requieran. Para ello seleccionaremos la opción de menú "Build | Deploy ContosoTabular" o haremos clic derecho en el icono correspondiente al proyecto en el Explorador de Soluciones, eligiendo la opción Deploy. Ambas acciones conducirán a la ventana de despliegue, que nos irá informando acerca del progreso de la operación.

 

Si el despliegue se realiza con éxito, cerraremos su ventana de progreso y abriremos SQL Server Management Studio (SSMS), conectándonos a Analysis Services, donde encontraremos el modelo recién subido al servidor. Expandiendo su nodo principal veremos las tablas que lo componen.

 

A partir de este punto podemos comenzar a enviar consultas al modelo utilizando cualquiera de los medios a nuestra disposición: consultas DAX, consultas MDX o Excel. Todo ello será tratado en la segunda parte de este artículo. 

(The spanish version of this article was previously published in dNM+, issue 83)

PowerPivot is a technology for information analysis whose peculiarity lies in the ability of working with massive amounts of data using Excel as user interface, so it becomes an attractive offer, given the popularity of this tool belonging to the Office suite. This article introduce the reader to PowerPivot as well as DAX (Data Analysis eXpressions), the analytical expression language that accompanies it, reeling off the most important features of this technology, which coupled with traditional Analysis Services and MDX (MultiDimensional eXpressions) language, make SQL Server 2012 one of the most robust and powerful solutions in the Business Intelligence arena. 

With increasing frequency, organizations must perform diverse analysis about their status in the most varied perspectives (business, financial, HR, etc.) and on an increasingly vast amount of data, for which require suitable tools to enable them to obtain a reliable and valid information of such state.

As explained in this blog's article, OLAP data cubes are one of the key elements in this data analysis ecosystem called Business Intelligence (BI).

However, a BI system, as occurs in other areas such as Web applications, desktop, etc., requires a development team and time to its creation, this latter factor sometimes extends for longer than initially estimated.

Given the problems posed above, we can propose as a solution to improve development tools of the information system, which accelerate its creation time, and allow handling a greater data volume, given the widespread increase of this aspect in all corporate areas.

It would also be necessary to promote the development team, incorporating new players into the system development cycle, which so far have played a role of mere validators, but they can bring great value to it, given his extensive knowledge about the structure of the various data sources available within the organization.

PowerPivot comes with the aim of providing solutions to the challenges posed above, covering a number of specific areas in the development of an information system based on BI.

 

Self-service BI

When we talk about self-service, usually framed this concept within the product sales activity, where a person, the client, moves to a place, the supermarket, where products are exposed, and once there the client picks up the products, all generally without requiring the intervention of any intermediary.

Within the BI context we can make an analogy with the self-service model, using a concept called self-service BI, which advocates that end users (advanced or other profile) of an information system can elaborate their own analysis and reports on the system contents, to meet the specific needs emerging in several company departments, without having on these situations to rely from the enterprise BI development team.

As requirements are the access need by these users to those data sources that require analysis and it is also crucial that self-service BI oriented applications have a learning curve as small as possible, so users can start being productive with them almost immediately.

PowerPivot meet these requirements, since being a technology built into Excel has a wide user base already working with both, the functionality of the spreadsheet and access to external data sources, thus reducing or eliminating training in the use of user interface, and focuses on learning specific features about additional support for data analysis.

 

Incorporating new user profiles to the development cycle

In addition to release development teams a significant workload, providing self-service BI tools to end users; we get as a bonus the possibility that advanced users from various departments of the organization, through the use of such tools, to assist in the development cycle of the information system, providing ideas and suggestions.

 

VertiPaq. High performance data processing engine

PowerPivot introduces a new data processing engine: VertiPaq, which through a column-based storage, implements a series of algorithms for data compression, by which is capable of loading millions of records in memory.

However, due to the execution in memory nature of VertiPaq is recommended, whenever possible, work on a 64-bit environment, with adequate software version to this architecture: operating system, SQL Server, Office 2010, PowerPivot, etc., since it does not suffer the memory address limitations found in the 32-bit systems.

 

Architectural elements

PowerPivot, as seen in next image, consists of an assembly that is loaded into Excel process; VertiPaq engine, which handles data loading, query management and DAX expressions execution against the data warehouse, as well as PowerPivot's pivot tables and charts, and finally, the OLAP provider, the Analysis Management Objects (AMO) objects and ADOMD.NET provider, allowing communication with analysis services, for gathering information from data cubes if applicable.

   

Collaboration environment. PowerPivot for SharePoint

Using PowerPivot is expected that the number of analysis models created by users grows noticeably, and it is therefore very important to have a management mechanism, with respect to the publishing and access security within the organization.

This is the purpose of PowerPivot for SharePoint, a plug-in extending Excel Services for SharePoint, in the above paragraphs about management and collaboration.

Additionally, PowerPivot for SharePoint allows users to view PowerPivot models published in the organization simply using a web browser, so you only need to install Excel and PowerPivot for Excel on the machines of users who will develop analytical models using this tool.

 

PowerPivot. BISM cornerstone

(Note: for a more accurate description of BISM than below, we recommend read in first place the SSAS Tabular Models article which includes updated information about this technology).

SQL Server 2012 ships with a new BI model called Business Intelligence Semantic Model (BISM). This model doesn't replace or displace Unified Dimensional Model (UDM), the existing BI model from SQL Server 2005 but coexist with it, complementing and enriching the SQL Server offer in BI area, allowing us to have both models to work with the one that best suits our needs.

SQL Server has been traditionally blamed, referring to UDM, that its implementation in BI area has a difficult learning curve with complex concepts: cubes, dimensions, measures, etc., and that the time needed to develop a solution extends too much when it comes to creating a simple analysis system.

From our point of view, this learning curve isn't much more complicated than we find in other aspects of software development, although we admit that from the perspective of developers used to working with a relational data model, getting familiar with the UDM-OLAP multidimensional model to build a business analytics solution may involve some initial complexity until the developer master the concepts and features of the technology.

BISM comes to solve this problem, providing a simpler way of work, distinguished by its relational philosophy supported by PowerPivot and VertiPaq. While BISM is primarily oriented to the development of self-service analytical solutions or for small teams, UDM is focused in the development of corporate BI solutions, which require more planning.

BISM is a model with a distributed architecture in three layers: data access, business logic and data model.

 

The data access layer is responsible for connecting to data sources to load the data they contain, existing two ways to operate with them: cached or real time. In the case of cached mode, data is loaded using VertiPaq, whereas if we use the real-time mode, this task is left to the original data source from which data are extracted.

The business logic layer will implement the managing and processing of data that allow us to make them relevant information. We will use for this any expression languages ​​at our disposal: DAX or MDX.

DAX is the expression language for data analysis built-in PowerPivot. We'll use it to build queries against the data store located in VertiPaq, while MDX is the language usually employed in UDM to query OLAP cubes.

DAX is not as powerful as MDX, but it is much easier to use, and aims to query data located in a self-service BI model, which theoretically should not require the use of complex queries, reserved to be made against the corporate BI system, which under normal conditions will have been developed by UDM.

Finally, the data model layer will be used by client applications (Excel, SharePoint, Reporting Services, etc.) gathering information from the proper model: relational or multidimensional. Among the reporting tools that use this model is Power View, a project whose objective is to provide a better user experience for data visualization and reporting against BISM based models.

Some prominent voices in the BI-related technical community have expressed concern about the UDM-OLAP position with the BISM arrival in SQL Server 2012, although from Analysis Services development team wanted to send a reassuring message, insisting that BISM is not a replacement for UDM but a complementary technology.

 

PowerPivot in practice

After architectural features review made in previous paragraphs, it's time to get down to working to develop a PowerPivot model, which help us to get our first impressions about the scope of this technology.

First of all we need to have Office 2010 installed, and download and install PowerPivot for Excel plug-in. In the event that our system is 32 bit, we'll download the file PowerPivot_for_Excel_x86.msi, but if we work on a 64 bit we must download the file PowerPivot_for_Excel_amd64.msi. For the sake of testing, in this article we use a Windows 7 operating system virtual machine with 1.5 GB of RAM and Intel Core 2 Duo processor.

Although the name of the download page referred to SQL Server 2008 R2, it is not necessary to have it installed if we are going to work with other data formats such as Access, text files, etc. However, in our case, we will use SQL Server as data engine and the database ContosoRetailDW. Through the latter link we'll download the ContosoBIdemoBAK.exe file containing the database in format backup.

The reason why we use this database is that it has some tables with millions of records, allowing us to test one of the salient features of PowerPivot: the ability to handle large volumes of data.

 

Creating a model

After restoring the database we'll open Excel, then clicking on the ribbon tab PowerPivot. Among the options on this tab we will click PowerPivot window, belonging to Launch group, which as its name suggests, will open the PowerPivot working window.

The first task in creating the model will be to connect to a data source to import its content, so that from the Home tab of the PowerPivot window, in the Get External Data group, we'll click From database option, selecting From SQL Server among the available items.

 

As a result will open the Table Import Wizard, where we'll include information to connect to the database ContosoRetailDW. In the Select Tables and Views step we will check FactSales, DimDate, DimStore and DimProduct.

 

After this step will begin the import process, once completed will show each of the imported tables in different tabs in PowerPivot window.

 

Querying the model

The set of tables we have just import will conduct an analysis of company sales, according to data related to the sales table: dates, products, stores, etc.

To do this we will click on PivotTable option in Reports group reports, creating a PowerPivot's PivotTable with which to perform our analysis operations. This action will move the focus to Excel window, where a dialog box will ask you the coordinates to place the PivotTable in the spreadsheet. Accepting the default values the new PivotTable will be created.

 

On the pivot table's right side we find the PowerPivot Field List panel where we select the fields to use for our analysis, either as axis labels of rows and columns, numeric values, filters, and slicers.

Those readers with expertise in creating OLAP cubes will verify that querying a PowerPivot model is very similar to the query against a data cube, but without the existence of a real cube, as in the field list panel, the items in block Values ​​represent the cube measures or metrics, while the rest of the block fields, represent the dimension attributes, displayed either in rows, columns or filter the PivotTable.

We will begin creating our query by checking the field SalesAmount, from FactSales table. Due this is a numeric field, it will automatically be placed as a measure in the Values block. PowerPivot will apply then an addition operation on all records in the table to which it belongs.

The current state of the PivotTable does not offer, however, great analytical possibilities, since we only have total by SalesAmount field. We need to add additional elements to the query relating to the table FactSales such as DimProduct table. This table contains each product's name and additional information as manufacturer, type, color, size, etc.

Suppose we want to find out the number of sales by product, but instead of using his name, we need to analyze by manufacturer. What we do in this case is check the field Manufacturer, belonging to the field list of DimProduct table. This action will place the field in the Row Labels block and their values in the PivotTable row axis. In this way we will know how sales have meant by each of the manufacturers.

Since the SalesAmount field values are not shown formatted, as additional work we right-click any cell in this field, choosing Number Format... option. In the format dialog box select type Currency with two decimal places and euro symbol, being applied to all cells in this field in the pivot table.

 

Writing DAX expressions

As noted earlier, DAX is the expression language through which we'll build the business logic in PowerPivot, both as PivotTable and data model.

Before starting to write our first sentences in this language, we must know that without us aware of this, we have already written a DAX expression! If we right-click Sum of SalesAmount in Values block and select Edit Measure..., a dialog box will open with the expression automatically created by PowerPivot

=SUM('FactSales'[SalesAmount])

 

 

The creation of a DAX expression is very similar to writing a formula in Excel, since then the equal sign, write one or more functions with their corresponding parameters, forming the whole expression. In the expression currently under review, the SUM function will make a sum of SalesAmount field for all records in the table FactSales. If the table name has spaces or other special characters we enclose it in quotes, in other cases not necessary. With respect to the field name should always be enclosed in brackets.

Now let's approach to writing our own expressions, through two major elements PowerPivot: computed columns and measures.

First we will create a computed column in the table FactSales, to obtain the sales amount without discounting. To this end, in PowerPivot window we should be on the last available empty column, writing in the formula bar as follows:

=FactSales[UnitPrice] * FactSales[SalesQuantity]

To assist in the writing of expressions, the AutoComplete feature will suggest at all times a list of functions, tables and fields, depending on what we go on writing.

After writing the expression, the new column will be filled with the resulting values. Double-clicking the column header we'll assign the name ImporteSinDescuento, ending the creation of computed column.

 

Another way to create a computed column is to click on Add option from Columns group, belonging to Design tab.

Because the fields used in the formula belong to the same table on which we are creating the calculated column, in the expression syntax we can circumvent the name of the table, being as follows:

=[UnitPrice] * [SalesQuantity]

To use the new column in the pivot table we'll back to the Excel window, finding a warning in PowerPivot Field List which informs us that data was modified. Clicking the Refresh button of the warning the field list will update, adding the new field ImporteSinDescuento, which we'll check to include in the PivotTable.

 

Our next step is creating a measure to calculate, for each manufacturer, the percentage of his sales with respect to total sales of the company.

In PowerPivot tab, within Measures group, we click the New Measure option, opening the Measure Settings window, where we'll type PorcentajeVentas as measure's name and the following expression in formula field:

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

 

 

The first part of this expression, as we have seen, is responsible for adding the field SalesAmount. This sum will be made for each manufacturer, because this field is used in PivotTable labels row.

The second part of the expression returns, for all rows, the total sum of SalesAmount field, which we accomplish combining the functions CALCULATE, SUM and ALL. CALCULATE function evaluates the expression passed as the first parameter, which is a sum of SalesAmount field, and requires that the sum is over all FactSales table records by using the ALL function, regardless of the filters that are applied in the PivotTable.

Finally, add the new measure to the pivot table by applying the percentage format.

 

Filters and slicers

Our PivotTable now shows arranged by product manufacturers, a series of measures obtained from fields and calculations of FactSales table belonging to PowerPivot data model.

The results of these measurements are made from all records of that table, but at some point we may also be interested to have the ability to apply filters on the model's information, in order to obtain different perspectives of analysis.

PowerPivot pivot tables provide the user with filters and slicers as data filtering tools. Next, we'll explain to the reader to use them as a means to narrow the results of the report.

Let's suppose we want to filter information in the pivot table depending on the type of store where are the products. We get this data from field StoreType in DimStore table.

To create a filter of this type, in the field list pane we'll open out the field list of table DimStore and drag the StoreType field to the Report Filter block. As a result this filter is inserted in the top of the pivot table.

By clicking on the filter selecting values button, we'll select "Almacén". This action will activate the filter, causing all numeric cells in the pivot table refreshing sales information depending on the filter set.

 

Slicers, moreover, they behave as filters, although its handling by the user is slightly different.

Let's create a slicer that allows us to filter the results by sales year, for which we will drag the CalendarYear field from DimDate table and drop in Slicers Horizontal block. This action will create the slicer with all filter values placed above the pivot table.

 

Slicer values ​​that appear in a darker color tone are those with which we can make effective filters, while the use of those with a lighter shade will not produce results as there are no sales in those years.

If we want to use more than one value in the slicer, we must keep down the CTRL key while clicking on the different values. The result of the filter is not effective until we release the CTRL key. To remove all filters from the segmentation will click the funnel-shaped icon situated at the top right.

 

Creating a PivotChart

In certain circumstances, the numerical data provided by a PivotTable it may not be sufficient to analyze the content of a PowerPivot model, so we also have pivot charts at our disposal, as an alternative in the information display.

We can create a pivot chart from scratch or use an existing pivot table as chart's foundation. In this case we'll choose the latter method, for which, once positioned in the pivot table, we'll click on Options tab belonging to PivotTable Tools category, and then select PivotChart option, located in the group Tools. This opens the Insert Chart dialog box, offering us a wide range of chart types to insert into the worksheet. Having made our choice, we will accept this dialog inserting the chart next to the PivotTable.

From this point, both the table and chart will be synchronized, so that the modifications of filters, measures axes labels, etc. performed in one will be reflected immediately in the other. Next image shows the appearance of our report including pivot table and pivot chart.

 

Conclusions

PowerPivot represents a technology with great potential to become an important part of the toolkit available for BI development with SQL Server. If we add capacity in handling large volumes of data, the power of its query expressions language, and the fact of using Excel as user interface, we obtain a product that allows users to create their own analysis models to address specific information needs. At the same time, these models can be of assistance to BI development teams in building corporate information systems.

 

Más artículos Página siguiente >