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.

HabilitandoObtencionDetallesCuboDatosMultidimensional_01

 

HabilitandoObtencionDetallesCuboDatosMultidimensional_02

 

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

HabilitandoObtencionDetallesCuboDatosMultidimensional_03

 

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.

HabilitandoObtencionDetallesCuboDatosMultidimensional_04

 

HabilitandoObtencionDetallesCuboDatosMultidimensional_05

 

HabilitandoObtencionDetallesCuboDatosMultidimensional_06

 

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.

HabilitandoObtencionDetallesCuboDatosMultidimensional_07

 

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

HabilitandoObtencionDetallesCuboDatosMultidimensional_08

 

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.

HabilitandoObtencionDetallesCuboDatosMultidimensional_09

 

HabilitandoObtencionDetallesCuboDatosMultidimensional_10

 

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.

HabilitandoObtencionDetallesCuboDatosMultidimensional_11

 

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.

HabilitandoObtencionDetallesCuboDatosMultidimensional_12

 

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.

HabilitandoObtencionDetallesCuboDatosMultidimensional_13

 

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.

HabilitandoObtencionDetallesCuboDatosMultidimensional_14

 

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.

HabilitandoObtencionDetallesCuboDatosMultidimensional_15

 

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.

HabilitandoObtencionDetallesCuboDatosMultidimensional_16

 

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)

 

HabilitandoObtencionDetallesCuboDatosMultidimensional_17

 

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.

HabilitandoObtencionDetallesCuboDatosMultidimensional_18

 

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.

HabilitandoObtencionDetallesCuboDatosMultidimensional_19

 

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.