Diseñando un cubo de ventas en Analysis Services

Nuestro buen amigo Mauricio Gomez, me escribió hace algunas semanas con algunas preguntas con respecto a sugerencias para diseñar un cubo de ventas para su empresa. Después contar con su consentimiento, publico aquí esa información de manera que pueda servir a muchos de ustedes que también puedan pasar por una situación similar y así aprendemos todos. Mauricio comenzará a implementar este proyecto durante esta semana, así que hemos convenido a que según se encuentre con nuevos retos, él irá publicando sus preguntas a través de la sección de comentarios de este post, así que no olviden echarle un ojo a los comentarios para obtener actualizaciones a este diseño.

Maurico me envió un buen inicio de su modelo de estrellas para su Data Mart que se muestra a continuación:

En resumen estas son las medidas:

• Cantidad vendida (unidades)
• Precio unitario de venta
• Monto total de venta

Y las dimensiones:

• Cliente
• Tipo de cliente
• Empleado (vendedor)
• Tipo de venta
• Producto
• Famila de producto
• Tienda
• Ciudad (de la tienda que vendió)
• Mes_Venta y Dia_Venta (ojo con estas dos)

Muy bien Mauricio señala que con este diseño responde a las preguntas de a Quén se vendió, Cómo se vendió, Cuándo se vendió y Qué se vendió.

Ahora incluyo mi primera respuesta a su email:

“…le heché un ojo al documento y lo primero que debo preguntarte es si has explorado la base de datos de ejemplo que usa Microsoft llamada Adventure Works. El caso que tienes que resolver es bastante idéntico a lo muestra esta base datos. Adventure Works a demás de la DB relacional tiene una base de datos en formato de Data Warehouse llamada AdventureWorksDW. Así mismo viene un proyecto de Analysis Services que tiene todo el diseño de las dimensiones, fact tables, medidas calculadas, tooodo. Para obtener todos los proyectos de Adventure Works debes de bajarla desde este link de CodePlex http://www.codeplex.com/MSFTDBProdSamples tanto para la versión 2005 o 2008 de SQL.

Con respecto al diagrama de estrellas que me enviaste, mi principal sugerencia es que incluyas una dimension Fecha en reemplazo de tus dimensiones Mes_Venta, Dia_Venta. El consolidar estas dimensiones en una sola te dará mucho valor, no sólo ahora para ver tus ventas sino para cualquier otra tabla de hechos que incorpores en el corto plazo a tu data mart además que podrás manejar distintas frecuencias de tiempo que tal vez ahora no te sean muy relevante pero te pueden agregar mucho valor (calendario trimestral, semanal, fiscal, etc.). Este concepto de compartir las dimensiones se llama en inglés "Conforming Dimensions". AdventureWorks tiene el mismo ejemplo para este caso.”

Ahora copio un extracto del data mart de Adventure Works para la parte de ventas por internet (internet sales):

Como se muestra, las dimensiones y medidas que requiere Mauricio, es más, las que requiere un típico escenario de ventas de cualquier compañía, son muy parecidas a las que ya nos muestra Adventure Works, por tanto debemos usarla como una excelente referencia para lo que queremos construir aquí.

Bueno ya presentamos el caso completo, espero que Mauricio nos ponga al tanto de su progreso y de sus actualizaciones. Demás está invitarlos a todos ustedes a que también puedan ayudar a la discusión de este caso, de cómo lo hace Adventure Works y de como modelarlo de la mejor manera.

Alan

SQL Server 2008 R2 August CTP

Hola a todos, desde el día de hoy los suscriptores de MSDN y TechNet podremos tener acceso a descargar el tan esperado primer CTP de SQL Server 2008 R2. Para el público en general estará disponible desde el 12 de agosto.

El R2 de SQL 2008 promete muchas nuevas características principalmente en el campo de Business Intelligence como: Master Data Management Services; el flamante Gemini, desatando el poder de Excel 2010 para análisis de millones de registros en muy pocos segundos; y Reporting Services con el soporte de nuevos controles para incluir mapas en sus reportes, ya sean de algún servicio web como Bing Maps o mapas “off-line”.

Pueden ver la hoja de producto oficial del R2 aquí.

Una vez que pueda instalarlo y jugar con él, espero estar compartiendo mis experiencias de las nuevas características súper excitantes de esta nueva entrega de SQL Server 2008.

Alan

Accessing SQL Server Agent Jobs Metadata

In every BI project that we develop SQL Server Integration Services (SSIS) packages, we also need to create some SQL Server Agent jobs to automate the package execution. In addition, according to business needs, we also need to configure different “execution schedules” for the same SQL Agent job. For example, we have an SSIS package that needs to run from Monday to Friday at 1am (first schedule), and from Saturday and Sunday at 6am (second schedule). In other cases you can have more complex schedules like the one shown on the image below:

In this context, we needed an “easy way” to get better manageability and visibility of the existing job execution schedules for our packages (using jobs) instead of using SQL Server Management Studio to open job by job and reviewing its execution schedule. So I decided to query SQL Server Agent (SSA) jobs metadata directly. All the information about SQL Agent jobs (and its other features), is maintained in the MSDB system database, specifically in tables whose names begins with “dbo.sysjobs…”.

You can execute the following query in SQL Server Management Studio:

USE msdb
GO 

SELECT name
FROM sys.tables
WHERE name like 'sysjob%'

The results are:  

Here is a brief description of each of these tables from the SQL Server documentation in MSDN:

* sysjobs: Stores the information for each scheduled job to be executed by SQL Server Agent. This table is stored in the msdb database.


* sysjobactivity: Records current SQL Server Agent job activity and status.


* sysjobhistory: Contains information about the execution of scheduled jobs by SQL Server Agent. This table is stored in the msdb database.


* sysjobschedules: Contains schedule information for jobs to be executed by SQL Server Agent. This table is stored in the msdb database.


* sysjobsservers: Stores the association or relationship of a particular job with one or more target servers.This table is stored in the msdb database.


* sysjobssteps: Contains the information for each step in a job to be executed by SQL Server Agent. This table is stored in the msdb database.


* sysjobstepslogs: Contains the job step log for all SQL Server Agent job steps that are configured to write job step output to a table. This table is stored in the msdb database.

A SQL Server Diagram with the SQL Server Agent job tables and columns is shown below:

So, at this point it is relatively easy to create a query to get all the schedules for my active jobs:

SELECT J.name,  J.enabled, J.description, S.enabled AS 'Schedule Enabled?' , 
S.active_start_date, S.active_start_time, S.active_end_date,  
S.active_end_time, S.freq_type, S.freq_interval, 
S.freq_subday_type, S.freq_subday_interval     
FROM sysjobs J 
INNER JOIN sysjobschedules JS ON J.job_id = JS.job_id 
INNER JOIN sysschedules S ON JS.schedule_id = S.schedule_id 
WHERE J.enabled = 1 
AND J.name LIKE '%NAPS%' 
ORDER BY J.name

Most of the columns are self explanatory but those related to the schedule details are not (freq_type, freq_interval, freq_subday, freq_subday_interval). To get more information about what each of those column values means refer to the sysschedules table documentation on MSDN.

Finally, to make my SQL Agent job schedules results more relevant, I added some CASE conditions to translate values for frequency related columns as shown below:

'Frequency' = CASE S.freq_type 
    WHEN 1 THEN 'One Time' 
    WHEN 4 THEN 'Daily' 
    WHEN 8 THEN  'Weekly' 
    WHEN 16 THEN 'Monthly' 
    ELSE 'Other' 
    END, 
'Interval' = CASE 
    WHEN S.freq_type = 8 AND S.freq_interval = 1 THEN 'Sunday' 
    WHEN S.freq_type = 8 AND S.freq_interval = 2 THEN 'Monday' 
    WHEN S.freq_type = 8 AND S.freq_interval = 4 THEN 'Tuesday' 
    WHEN S.freq_type = 8 AND S.freq_interval = 8 THEN 'Wednesday' 
    WHEN S.freq_type = 8 AND S.freq_interval = 16 THEN 'Thursday' 
    WHEN S.freq_type = 8 AND S.freq_interval = 32 THEN 'Friday' 
    WHEN S.freq_type = 8 AND S.freq_interval = 64 THEN 'Saturday' 
    WHEN S.freq_type = 8 AND S.freq_interval = 127 THEN 'Everyday' 
    WHEN S.freq_type = 8 AND S.freq_interval = 111 THEN 'Except Thursday' 
    WHEN S.freq_type = 8 AND S.freq_interval = 95 THEN 'Except Friday' 
    WHEN S.freq_type = 8 AND S.freq_interval = 94 THEN 'Except Friday and Sunday' 
    WHEN S.freq_type = 8 AND S.freq_interval = 65 THEN 'Weekends' 
    WHEN S.freq_type = 8 AND S.freq_interval = 30 THEN 'Monday to Thursday' 
    WHEN S.freq_type = 4 THEN CAST(S.freq_interval AS varchar(10))+ ' day(s)'  
    WHEN S.freq_type = 16 
        THEN 'Day of the month = ' + CAST(S.freq_interval AS varchar(10))   
    WHEN S.freq_type = 32 AND S.freq_interval = 1 THEN 'Sunday' 
    WHEN S.freq_type = 32 AND S.freq_interval = 2 THEN 'Monday' 
    WHEN S.freq_type = 32 AND S.freq_interval = 3 THEN 'Tuesday' 
    WHEN S.freq_type = 32 AND S.freq_interval = 4 THEN 'Wednesday' 
    WHEN S.freq_type = 32 AND S.freq_interval = 5 THEN 'Thursday' 
    WHEN S.freq_type = 32 AND S.freq_interval = 6 THEN 'Friday' 
    WHEN S.freq_type = 32 AND S.freq_interval = 7 THEN 'Saturday' 
    WHEN S.freq_type = 32 AND S.freq_interval = 8 THEN 'Day' 
    WHEN S.freq_type = 32 AND S.freq_interval = 9 THEN 'Weekday' 
    WHEN S.freq_type = 32 AND S.freq_interval = 10 THEN 'Weekend day' 
    WHEN S.freq_type = 8 THEN CAST(S.freq_interval  AS varchar(10)) 
    ELSE 'N/A' 
    END, 
'Sub Day Frequency' = CASE S.freq_subday_type 
    WHEN 1 THEN 'At the specified time' 
    WHEN 2 THEN 'Seconds' 
    WHEN 4 THEN  'Minutes' 
    WHEN 8 THEN 'Hours' 
    END, 
'Sub Day Interval' = S.freq_subday_interval

To put the cherry on the cake, for better access and automation you can include this query in your Reporting Services report as I did it:

The queries created here where designed for SQL Server 2008 but I’m very sure that it’s very similar for SQL Server 2005.


I have shown you how easy is to access SQL Server Agent metadata to get more visibility for IT operations and better maintenance purposes. Remember that there are more tables in MSDB for accessing other SQL Server Agent features.


Alan

Syntax highlighting bug in Reporting Services 2008 MDX Query Designer

Maybe you have noticed that in difference to Reporting Services 2005, the MDX Editor in Reporting Services 2008 has not the syntax highlighting feature enabled, for this reason it’s very painful to edit MDX queries relatively complicated using this editor.

Here some screenshots showing the problem in Business Development Studio (BIDS) 2008 – where we design the Reporting Services reports, notice that the BIDS 2005 “Data” tab is not more a tab but a dialog window:

 
Below how the same query looks in the BIDS 2005 MDX Query Editor (showing the “Data” tab in the same window when we design the report):

From some time ago, I was looking for some explanation about why syntax highlighting doesn’t work in the current version of BIDS (and with the hope of turn that feature on as in BIDS 2005) but without any success.
Finally, some days ago I decided to contact Microsoft directly about this issue. First at all, I have to say that I was very pleased about the prompt and fast response from Carolyn Chau from the Reporting Services Team, it was a great example of customer service!

Here Carolyn’s response:

“…I’m afraid we lost this feature between 2005 and 2008 due to a strange quirk in the combination of VS and the MDX query designer.  When the query designer is hosted in a dialog page as we have in 2008, syntax highlighting does not work.  We continue to try and find work arounds to this issue…“

Finally, you know what is going on with syntax highlighting in BIDS 2008. Carolyn gave me the explanation response about this “bug” directly related to the integration of custom dialog windows and the Visual Studio IDE.

Extra-officially, this bug could be fixed in the next release of SQL (SQL 2008 R2), so we need to wait until the CTP to see it. Meanwhile, we have to patiently continue creating our MDX queries in SQL Server Management Studio or in BIDS 2005 (if you have it installed side by side with your 2008 version) to then copy and paste to BIDS 2008.

Alan

Implementando dimensiones SCD2 con SQL Integration Services (SSIS)

Desde hace un par de semanas he estado envuelto en una nueva iniciativa de Microsoft llamada SnackTI, la cual busca crear nuevo contenido y de alta calidad, relevante para estos tiempos de nuevos retos.

De acuerdo a esto me animé a contribuir en el área de Business Intelligence y lo siguiente fue pensar en qué tema escoger. Como ven el título de este post, me decidí por mostrar algunas mejores prácticas de cómo implementar “dimensiones lentamente cambiantes” ó slowly changing dimensions (SCD2), ya que en mis primeros años trabajando con Data Warehouses, este fue uno de los temas más interesantes, no sólo por el concepto en si, sino en buscar una forma optima de implementarlas en SSIS.

Por ello he creado una serie de screencasts (casi 90 minutos en total) que muestran en detalle dos formas de implementar dimensiones SCD2. Al final tuve que partir los screencast en varios videos de aproximadamente 10 minutos cada uno para poder publicarlos en YouTube.

Implementando SCD2 con SSIS - Parte I

En esta primera parte explico el problema que busca atacar el uso de SCD2 y explico cómo funciona su procesamiento de manera lógica. El demo de esta parte presenta la forma de implementar un paquete de SSIS para manejo de SCD2 usando el SCD Transformation, el componente “out ot the box” que viene en SSIS para implementar este tipo de casos.

Implementando SCD2 con SSIS - Parte II

La segunda parte se enfoca en demostrar una forma alternativa (y sugerida) de implementar el manejo de SCD2 para dimensiones con “muchos” miembros de datos y de esa forma bajar los tiempos de ejecución del paquete versus el SCD Transformation. La forma alternativa (personalizada) usa básicamente el Merge y Split Transformation para el manejo de la discriminante que decide si el miembro en proceso es una actualización o una inserción, y; una tabla temporal y un SQL Transformation para la ejecución de la actualización en batch (por lotes) en la base de datos.

 

 

Incluyo a continuación una comparación entre los tiempos de ejecución de la primera y segunda carga de las dos formas la cual resumo a continuación:

	SCD Transformation	SCD Personalizado
Primera carga	20.732 Sec.	0.999 Sec.
Segunda carga	1:04:756 Min.	1.248 Sec.

Los resultados salen a la vista. La opción personalizada es mucho más rápida en el procesamiento. Cabe resaltar que no se aplicó ninguna optimización adicional al uso directo de los paquetes..

Pueden descargar ambas versiones de los paquetes, los archivos de backup de las 2 bases de datos usadas (SQL 2008) y la  versión PDF de la presentación, desde aquí:

No olviden truncar la tabla DimCustomer en la base de datos SCDDemo_DW cuando empiecen a hacer sus pruebas.

Alan.