Error en SSAS: The attribute key cannot be found when processing...

Este error es el más común que te encontrarás si te dedicas a crear cubos OLAP con SQL Server Analysis Services. Para mí el modo en que este error se corrige dará credibilidad o no a los datos presentados por el cubo.

Pero empezamos por el principio. Comienzas creando las dimensiones y grupos de medida del cubo, repasas las relaciones entre estas dimensiones y cada uno de estos grupos de medida, defines las jerarquías y relaciones entre atributos, incluso defines agregaciones y particiones para optimizar rendimiento y espacio en disco. En el momento en el que vas a procesar el cubo para ver los resultados el siguiente error se muestra:

The attribute key cannot be found when processing: Table: 'TableName', Column: 'ColumnName', Value: 'ErrorValue'. The attribute is 'AttributeName'.

El error en el fondo es muy sencillo. En la tabla de hechos TableName tienes una clave foranea ColumnName que apunta a un registro de la dimensión cuyo atributo es AttributeName, y este registro, seguramente una clave subrogada, no se ha encontrado en la dimensión. Hablando en términos relacionales, se ha roto la integridad referencial pues la clave foranea apunta hacia un valor de clave primaria que no existe.

En Analysis Services tenemos básicamente 4 modos de solventar esta situación:

Opción 1) Modificamos el valor no encontrado con un valor correcto correspondiente a un miembro de la dimensión. Reprocesamos la dimensión y posteriormente volvemos a procesar el cubo.
Opción 2) Modificamos la propiedad ErrorConfiguration del cubo o bien del grupo de medida, indicamos custom, y cambiamos la propiedad KeyErrorLimitAction a StopLogging y la propiedad KeyErrorAction a DiscardRecord. Finalmente procesamos el cubo.
Opción 3) Modificamos la propiedad ErrorConfiguration del cubo o bien del grupo de medida, indicamos custom, y cambiamos la propiedad KeyErrorLimitAction a StopLogging, la propiedad KeyErrorAction a ConvertToUnknown y la propiedad UnknownMember de la dimensión a Visible. Reprocesamos la dimensión y posteriormente volvemos a procesar el cubo.
Opción 4) Añadimos un miembro más a la dimensión llamado Unknown con una clave conocida (0 por ejemplo) y se la asignamos a este registro, de forma que se cumpla la integridad referencial. Reprocesamos la dimensión y posteriormente volvemos a procesar el cubo.

Y ahora las implicaciones. La opción 1 nos puede servir cuando siempre exista un valor correcto y conocido para la dimensión, aunque esto no siempre es válido. En cualquier caso será la opción predilecta si podemos aplicarla. La segunda opción es un suicidio profesional en toda regla. El registro será ignorado por el cubo lo cual quiere decir que no se incluirá en el cálculo de las medidas. En definitiva, el resultado que lanzará el cubo no coincidirá con el resultado calculado en el relacional y el proyecto (y el autor del mismo) perderá toda la credibilidad. La opción 3 es una buena opción a la vez que rápida, el registro se incluirá en un miembro especial de la dimensión llamado miembro desconocido y los resultados calculados por el cubo serán correctos. El único problema puede ser que los usuarios vean raro un miembro fantasma que englobe todos los valores perdidos en el procesado, aunque es cuestión de acostumbrarlos. Finalmente una de mis opciones preferidas, crear un miembro desconocido propio que nos permita tener controlados los resultados que asignamos a este fantasma. De esta manera podemos tener claro qué valores se agregan en el miembro desconocido y solventar los valores que realmente son errores.

En conclusión y por orden de preferencia utilizar la opción 1 siempre que se pueda, la opción 4 si tenemos valores desconocidos y queremos mantener el control total sobre los cálculos, la opción 3 si se quiere ir rápido (en fases de desarrollo y testing por ejemplo), y jamás la opción 2 a no ser que busquemos confundir al usuario o acabar nuestra carrera como ingenieros.

¿Cómo crear una pipeline de ventas? (Cross-Time Sales Pipeline Howto)

Desde que me dedico a la inteligencia de negocio y en especial a la integración de datos, me han preguntado muchas veces cómo crear un informe de pipeline a partir de un histórico de oportunidades de venta, o lo que es lo mismo, un informe dónde se cuenta cuántas filas de una tabla existen en un determinado estado en unos instantes de tiempo marcados (Cross-time), por ejemplo cada mes.

Después de buscar mucho y encontrar poco, la solución siempre ha sido crear una tabla de snapshots incrementales para cada instante del tiempo que contuviera la información básica con la que crear el informe. En ella utilizaría un procedimiento almacenado o bien un proceso SSIS para crear un bucle que me contase por ejemplo por cada mes y por cada estado, cuántas oportunidades de venta existían y guardar esta cifra en esta tabla.

Hace poco he vuelto a encontrarme con esta necesidad, y en este caso un conjunto de filas reducido y un buen motor y esquema relacional me ha permitido implementar una solución sencilla y directa sin pasar por una tabla temporal. La idea es la misma, se necesita un proceso intermedio que esta vez implemento con una sencilla función escalar en T-SQL, pero puede servir de base a cualquiera que se encuentre con este problema alguna vez.

La idea es utilizar la siguiente función en una consulta donde cruzamos la tabla de históricos con una tabla de dimensión de tiempo para recuperar los valores que buscamos para cada mes:

CREATE FUNCTION [dbo].[fnFINGetPipelineAmount](@date as datetime, @state as varchar(20))
RETURNS numeric(38,20)
AS
BEGIN
DECLARE @total numeric(38,20);
SET @total = (select sum(New_EstRevenueQ) from
(select rn=ROW_NUMBER() over (partition by ContractID, OpportunityId order by StartDate desc), *
from Pipeline
where ((month(@date) between month(StartDate) and MONTH(EndDate)) or (MONTH(StartDate) = month(@date) and EndDate is null))
and YEAR(StartDate) = year(@date)
AND (state = @state)
) as sq where sq.rn = 1)
RETURN @total;
END;

Ahora sólo necesitamos la dimension tiempo para invocar a esta función y recuperar la pipeline completa para un lapso de tiempo determinado, por ejemplo el año pasado.

SELECT month, dbo.fnFINGetPipelineAmount(Date, 'New') AS NewAmount, dbo.fnFINGetPipelineAmount(Date, 'Proof') AS ProofAmount, dbo.fnFINGetPipelineAmount(Date, 'Close') AS CloseAmount
FROM vwFIN_CubeDimTime
where year = 2009 and IsLastDayOfMonth = 1

Cuando el número de filas comienza a dispararse esta solución se vuelve demasiado costosa con facilidad. Para profundizar en este tema podemos leer una solución más compleja y efectiva en el whitepaper de Microsoft titulado "The many-to-many revolution" pág 57., de Marco Russo, el cual recomiendo a aquellos que les guste el mundo OLAP. Lo podéis encontrar aquí.

Mapa con Report Builder 3.0 Maps y SQL Server 2008

Os adjunto un video de una demo que realicé en mis últimos eventos sobre Business Intelligence. Se trata de la creación de un mapa de ventas por territorio hecho con Report Builder 3.0 R2 CTP y una fuente de datos SQL Server 2008. En unos sencillos pasos podemos tener un informe para visualizar sobre un mapa los resultados de ventas de nuestra empresa. Más fácil imposible!!!

Artículo publicado en MSDN

Hola a todos.
Esta mañana me han comunicado que mi artículo sobre procesamiento de dimensiones en SSIS había sido publicado en MSDN España. Podéis echarle un vistazo al mismo en:

http://msdn.microsoft.com/es-es/ee815809.aspx

SQL Server 2008 R2

Hola a todos.
Hace unos días se liberó la CTP de Noviembre de SQL Server 2008 R2. Podéis acceder directamente mediante este link:
http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx
Adicionalmente podéis descargar el complemento Report Builder 3 con nuevas y espectaculares funcionalidades. El acceso directo a la descarga es:
http://download.microsoft.com/download/5/4/D/54D3A5E3-71EF-45B7-BA9D-02F0A7C4ABB7/ReportBuilder3.msi
Os recomiendo instalaros Office Excel 2010 con Powerpivot para probar a fondo las nuevas capacidades de esta versión de Excel. Powerpivot es capaz de conectar a casi cualquier fuente de datos e integrar la información en la hoja de cálculo. Con los datos cargados en memoria, el usuario es capaz de definir relaciones entre los datos recuperados y construir estructuras de cálculo complejas como si de un cubo se tratara.
Puedes descargar la beta 2 de Office Excel en el mismo link que la CTP de SQL Server 2008 R2.

Herramienta de deploy personalizada con SSIS

Hola a todos.
Demasiado tiempo sin publicar, supongo.
Bueno, ya estoy de vuelta otra vez, ahora con una herramienta muy útil si estás usando paquetes SSIS con archivos de configuración y estás intentando hacer un deploy en un servidor de producción.
Imagínate que estás guardando la información de una cadena de conexión sin seguridad integrada, es decir, usando autenticación SQL Server, en un repositorio de código fuente (Team System o Source Safe). No te quedará más remedio que proporcionar la contraseña en la cadena de conexión para conectar a las distintas fuentes.
No podrás almacenar la contraseña en el paquete a menos que utilices cifrado, pero no es una buena idea ya que no queremos ni introducir una contraseña cada vez que abrimos el paquete en Visual Studio, ni cifrar con la clave del equipo porque no vamos a ser capaces de desplegar en producción después.
La manera más sencilla de solucionar este problema es incluir la contraseña en las cadenas de conexión guardadas en la configuración de SSIS, como por ejemplo en fichero de configuración. Puedes sobreescribir el archivo de configuración con el bloc de notas para incluir la contraseña sin que Visual Studio te la quite.
Ahora el problema es que cuando se haga deploy en producción y los servidores de desarrollo no estén visibles desde este entorno, recibiremos un timeout utilizando el wizard de instalación de paquetes de SSIS.
Bien, he desarrollado una pequeña herramienta winforms para instalar los paquetes sin validación que facilitará mucho el proceso de deploy, ya que esta no realiza ningún tipo de validación evitando así el problema del timeout.
Puedes descargar el proyecto completo y la herramienta desde aquí.

Limpieza de datos (Data Cleansing) con Script en SSIS

Hola chicos.
Muchas veces he necesitado hacer frente a información realmente sucia, poblada con cadenas en blanco, valores nulos y fechas futuras. La mayoría de las veces he tenido que limpiar estos valores en varios flujos de datos y varios paquetes. Podemos hacerlo fácilmente incluyendo un componente 'Column Transformation', pero tendríamos que declarar la función de transformación para cada columna que queramos limpiar.
Un enfoque diferente sería utilizar el componente de script para cubrir cada columna que deseáramos limpiar de forma automática, a través de la reflexión de .NET.
En el componente de script tendremos que seleccionar las columnas de entrada que hay que limpiar. A continuación declaramos un bucle que iterará para cada columna y realizará un llamada a la función de limpiar valor, dependiendo del tipo del valor de la columna.
En el ejemplo de código siguiente, se limpia la cadena de columnas con valores en blanco, y se reemplazan las fechas futuras con un null:

Imports System
Imports System.Data
Imports System.Math
Imports System.Reflection
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim str As String
Dim obj As Object
Try
Dim p As PropertyInfo
For Each p In Row.GetType().GetProperties()
'First check, null properties
If p Is Nothing Then
Continue For
End If
'Second check, null values
If p.GetValue(Row, Nothing) Is Nothing Then
Continue For
End If
'Second check by types
If p.PropertyType Is GetType(String) Then
p.SetValue(Row, CleanString(p.GetValue(Row, Nothing).ToString()), Nothing)
End If
If p.PropertyType Is GetType(DateTime) Then
If CType(Row.GetType().GetProperty(p.Name() + "_IsNull").GetValue(Row, Nothing), Boolean) = True Then
Continue For
End If
obj = CleanDate(p.GetValue(Row, Nothing))
If obj Is Nothing Then
Row.GetType().GetProperty(p.Name() + "_IsNull").SetValue(Row, True, Nothing)
Else
p.SetValue(Row, obj, Nothing)
End If
End If
Next
Catch e As Exception
'Do Nothing
End Try
End Sub

Public Function CleanString(ByVal str As String) As String
If str.Trim() = "" Then
Return Nothing
End If
Return str
End Function

Public Function CleanDate(ByVal obj As Object) As Object
Dim dt As DateTime
dt = CType(obj, DateTime)
If dt > DateTime.Now Then
Return Nothing
End If
Return obj
End Function

End Class