1/28/2009

Visita a Teotihuacán

La gente de Datavisión (partner de Microsoft con experticia en SQL) tuvo la gentileza de llevarme a Teotihuacán el pasado 10 de enero. Teotihuacán es uno de los lugares arqueológicos más importantes de América y lugar de visita obligatoria cuando se está en México. Durante esta visita finalmente pude cumplir mi sueño de subir tanto la pirámide del sol como la de la luna. Antes había estado en Teotihuacán pero siempre subía primero la pirámide del sol, y me quedaba sin fuerzas para subir la pirámide de la luna. Esta vez la hice a la inversa, y con duras penas, pero llegue.

En orden acostumbrado: Héctor Eugenio Jiménez, Javier Loria, Miguel Angel Granados y Jesús Gil Velasco.

Subiendo la pirámide de la Luna

Pirámide del Sol, vista desde la pirámide de la Luna

Arriba, en la pirámide del Sol.

Pirámide de la Luna vista desde la pirámide del Sol

Posted at 8:31 PM by Javier Loria | Category: Pura Vida | Permalink | Email this Post | Comments (0)

1/19/2009

OPIs (Indicadores de Desempeño Operativos): Parte 2

En la primera parte expuse (http://blogs.solidq.com/ES/jloria/Lists/Posts/Post.aspx?ID=25), que con frecuencia las organizaciones no tienen el nivel de madurez de inteligencia de negocios y de planeamiento estratégico necesario para construir paneles de mando o tableros control (Scorecards y/o Dashboards); y estas herramientas deben ser el fruto del planeamiento estratégico y fluyen de arriba (dirección/gerencia) hacia abajo (mandos medios y empleados) en la organización. Quiero resaltar, que no es que no crea en estos componentes, ni mucho menos; creo que son una herramienta invaluable, pero con mayor frecuencia de lo me gustaría reconocer, estos paneles y tableros de control no son herramientas efectivas de administración y no son más que piezas decorativas de software que sirven para satisfacer el ego corporativo y justificar excesos de gasto en infraestructura de BI.

A pesar de esto, es mi opinión los departamentos de tecnología de empresas que no están maduras para implementar una infraestructura de monitoreo (scorecards/dashboards) pueden beneficiarse de las tecnologías de indicadores inteligencia de negocios, si cambian la perspectiva de KPIs por OPIs.

Definiciones:

Empecemos entonces por definir y separar los términos:

KPI: por sus siglas en ingles (Key Performance Indicator): Medida que ayuda a una organización a definir y medir el progreso hacia objetivos estratégicos corporativos.

OPI: por sus siglas en ingles (Operational Performance Indicator): Medida que ayuda a una organización a examinar y monitorear sus resultados operativos.

Los KPIs son fruto del planeamiento estratégico. La dirección o gerencia, evalúa estrategias y desarrolla un plan para garantizar el éxito de dicha estrategia; luego busca cuales son las mejores medidas que indiquen que se mueve la organización en la dirección correcta; más tarde fija objetivos para estas medidas y finalmente evalúa su cumplimiento.

OPIs y Metadata

Los OPIs por el contrario no son fruto del planeamiento estratégico, son fruto del conocimiento operativo (know how) de la empresa. El proceso para definir OPIs, es más simple que el KPIs, se medidas que son relevantes para la empresa y se capturan y se controlan contra un valor esperado que no necesariamente es un objetivo. A pesar de es fácil definir OPIs, requieren mas trabajo operativo por lo que sus características de "medatadata" que le son fundamentales. Debe para cada OPI definirse:

Definición de Negocios: la definición de negocios sirve para que usuarios no expertos en la materia entiendan la naturaleza del OPI. Por ejemplo si se define un OPI sobre la Razón o Prueba del Acido (Razón Financiera) debería ser algo como: Muestra la capacidad de la empresa para hacer frente a sus obligaciones de corto plazo y se calcula como la división de los activos corrientes menos el inventario entre los pasivos corrientes.
Perspectiva: Indica dentro del marco de referencia de establecido por Kaplan & Norton cuál es la perspectiva más importante que se ve afectada por este OPI. Las perspectivas definidas por Kaplan y Norton son: Financiera, Cliente, Interna y Crecimiento/Aprendizaje. Las perspectivas hacen que sea luego más fácil pasar de OPIs a KPIs.
Proceso de Negocios: El agrupar los OPIs por procesos de negocios y no de forma departamental, permiten analizar de forma más integral la empresa.
Propietario de Negocios: Es la persona responsable de dicha medida, es una referencia a la persona a la que debería llamar un directivo/ejecutivo para discutir los resultados de esta OPI. Los propietarios de negocios suelen ser mandos medios de la organización.
Definición Técnica: Esta definición puede ser para los especialistas del negocio y lleva más detalle que la de negocios o es la definición que permite al departamento de tecnología dar trazabilidad a la medida.
Propietario Técnico: Es la persona responsable técnico de la medida, o sea una referencia en el caso de que se crea que la medida no este disponible o se crea que tiene errores de cálculo. Los propietarios técnicos son DBA's o super-usuarios de aplicación.
Intención: Como debe interpretarse la medida cuando se compara con el valor esperado: más es mejor, menos es mejor, medio es mejor o referencia. Por ejemplo: Ventas Totales seria más es mejor, Costo Total es menos es mejor, Apalancamiento Financiero puede ser medio es mejor, y Tipo de Cambio es referencia (no es mejor ni peor, es algo que ocurre y no podemos cambiar).
Tipo y Unidad de Medida: El tipo separa a Flujos (Movimientos), de saldos, razones y tasas. La separación por tipos es fundamental por la relación que con el tiempo tienen cada uno de ellos. Los Flujos pueden agregarse a través del tiempo y suelen moverse con mucha rapidez, los saldos por otra parte no se pueden agregar a través del tiempo y se mueven con mayor lentitud porque no cambian la historia. Las razones y tasas dependen de sus elementos base. Por otra parte la unidad de medida clarifica los valores y evita errores, pueden ser Kilos, Libras, Miles de Dólares, Euros por Semana, etc.
Método de Cálculo del Valor Esperado: Indica cómo se calcula el valor Esperado del KPI:
1. Valor Histórico: Este valor puede ser un promedio ponderado de resultados histórico. Por ejemplo el promedio de las últimas 5 semanas, o el promedio del año anterior.
2. Valor Proyectado: Valor que es calculado con un mecanismo de Series de Tiempo, basado únicamente en el mismo OPI.
3. Valor Estimado: Valor que es calculado basado en modelos de minería más complejos, que analizan las relaciones estadísticas entre diferentes variables.
4. Promedio de Industria: Valor que es promedio de la industria o sector y sirve como punto de referencia.
5. Valor Presupuestado/Meta: Es el único valor que es fijado con intervención humana directa, y refleja un objetivo de negocios y no un comportamiento esperado. El fijar metas o presupuestos implica planificación y mueve lentamente a los OPIs hacia los KPIs porque comienzan a reflejar objetivos, se mantienen como OPIs hasta que se defina la medida como estratégica.
Regla de Clasificación: Indica la regla que se usa para clasificar el resultado, cuando compara el valor real con el valor esperado. Esta regla es por naturaleza subjetiva, a menos que sea general o estadística. La general seria indicar los valores como "Amarillo" cuando sean superiores al 95% de la meta para todos los valores (asumiendo que más es mejor). O cuando se utiliza alguna medida de dispersión estadística (ejemplo: varianza o variación estándar) pero de forma general. Finalmente puede ser fijado a mano en cuyo caso es un criterio personal de un especialista de mercado
Peso/Factor: Numero que sirve para asignar mayor valor a las desviaciones de algunos elementos. Se recomienda que el factor sea un número de 1 a 5, fijado por un experto de negocio y constante para evitar su manipulación y afectar los resultados.

Técnicamente hablando los OPIs tienen también dos elementos de Metadata adicionales:

Dimensión/Jerarquía de Responsabilidad: Indica cual es la dimensión natural para navegar esta medida, cuando se quiere establecer responsabilidad sobre los resultados de esta medida en colaboradores de la empresa. Permite que las herramientas de análisis dividan los resultados generales puntualizando aéreas de resultados extraordinarios, pero bajo un marco administrativo jerarquías administrativas.
Dimensión/Nivel de Captura de Tiempo: Indica el nivel más bajo sobre el cual tiene sentido capturar los resultados, desde el punto de vista de negocios. Aunque sea posible técnicamente posible capturar a niveles inferiores, no se reflejara.
Los OPIs son una comunicación de expertos del negocio hacia los ejecutivos, por eso es crítico que los OPIs completen con información no numérica los valores numéricos.

Posted at 8:58 PM by Javier Loria | Category: Inteligencia de Negocios | Permalink | Email this Post | Comments (0)

Paso a Paso de Temas Dinámicos en WPF/Silverlight

El siguiente es un paso a paso de como implementar en una aplicación WPF o de Silverlight con temas que el usuario puede seleccionar de acuerdo a su gusto. Como simplificación solo implantaremos un tema controle el fondo (background) de la ventana principal y el color de los botones. Este paso a paso usa Web Expression 2.

Usando Web Expression 2, inicie un proyecto de WPF.
En la ventana del proyecto agregue las dos imágenes jpg que se usaran como fondo de la ventana.
Renombre las imágenes, como Tema1Back.jpg y Tema2Back.jpg
Agregue al proyecto un archivo de recursos, haciendo clic derecho sobre el proyecto y agregue un ítem tipo recursos. Nombre el archivo Tema1.xaml
Arrastre y deje caer la imagen Tema1Back.jpg a la ventana principal. Esto agregará un control imagen que se usará para crear un "Brush".
Con la imagen recién creada haga en el menú principal seleccione Tools-Make Brush Resource-Make Image Brush.
Nombre al recurso FondoPrincipal y agréguelo al diccionario de recursos Tema1.xaml, y presione OK.
Borre la imagen que sirvió para crear el recurso.
Seleccione la ventana principal (no el grid), seleccione propiedades, y en la sección de "Background" seleccione Brush Resources, y dentro de la lista de Local Brush Resources seleccione el FondoPrincipal. Esto cambiara el fondo de la pantalla principal y lo agregara al archivo Tema1.
A la ventana principal agregue dos botones. Estos botones servirán para cambiar de tema.
Renombre los botones Tema1 y Tema2 y ponga como contenido de cada uno su nombre. Es fundamental que el nombre esté relacionado con el Tema, ya que basado en el nombre del botón (y no en su contenido) se escribirá el código dinámico.
Con uno de los botones seleccionados, cambie el color del fondo y presione la opción de +Brush
Nombre el recurso como Botones Tema y agréguelo al diccionario de recursos Tema1.

de

Seleccione el otro botón y asígnele como fondo, el recurso BotonesTema recién creado, para que ambos botones tengan el mismo color.
Seleccione uno de los botones y en las propiedades, seleccione la opción de eventos, para agregar un evento clic llamado: CambioTema. Cuando presione "Enter", se abrirá Visual Studio, para que pueda editar el código.
Agregue el siguiente código:

Button b = sender as Button;

ResourceDictionary r = new ResourceDictionary();

r.Source = new Uri(b.Name.ToString() + ".xaml", UriKind.Relative);

Application.Current.Resources.MergedDictionaries.Clear();

Application.Current.Resources.MergedDictionaries.Add(r);

Vuelva a Web Expression 2, sin cerrar Visual Studio, y seleccione el otro botón, y asigne al evento clic a CambioTema. Volverá a activarse Visual Studio.
Grabe el código y cierre Visual Studio.
En la ventana de proyecto, seleccione con clic derecho el archivo de recursos Tema1.xaml y escoja Copy (Copiar).
Seleccione el proyecto con clic derecho y escoja Paste (Pegar).
Seleccione el Diccionario de recursos "Copy of Tema1" y renómbrelo como Tema2.
Haga doble clic sobre el App.xaml, para editar la configuración de diccionarios de recursos.
Elimine la referencia a "Copy of Tema1", para que quede de la siguiente forma:

<ResourceDictionary.MergedDictionaries>
<ResourceDictionary Source="Tema1.xaml"/>
</ResourceDictionary.MergedDictionaries>

Grabe y cierre el archivo App.xaml.
Con el archivo de Window1.xmal abierto, seleccione la opción de Recursos (Resources).
Navegue al Diccionario de recursos Tema1 y cambie el color de BotonesFondo.
Haga clic derecho sobre el FondoPrincipal seleccione View XMAL.
Reemplace la referencia a la imagen Tema1Back.jpg por Tema2Back.jpg
Grabe todos los archivos y pruebe el código.

Posted at 5:58 AM by Javier Loria | Category: Desarrollo | Permalink | Email this Post | Comments (0)

Concatenando Textos en SQL

En un posteo reciente http://blogs.solidq.com/ES/jloria/Lists/Posts/Post.aspx?ID=20 use un "patrón" de comando de SQL que tiene la siguiente forma:

SET @Comando=''
SELECT TOP 10 @Comando=@Comando+CHAR(13)+CHAR(10)+Indice
FROM dbo.FragmentacionIndices
ORDER BY TotalPages DESC;

Este comando permite concatenar un texto usando una variable, pero mi amigo Eladio Rincón, que el equipo de desarrollo de SQL, sostiene que ese comando no está garantizado que brinda resultados consistentes.

Ahora para otro proyecto tengo que concatenar una de nombres que están relacionados con una tabla. Despues de estudiar algunas alternativas me gusta la siguiente solución:

; WITH ProveedoresOrdenados (ProductID, VendorId, NumProveedor) AS

(

SELECT ProductVendor.ProductID

, ProductVendor.VendorID

, ROW_NUMBER() OVER ( PARTITION BY ProductID ORDER BY VendorID) AS NumProveedor

FROM Purchasing.ProductVendor

)

, ProdVendedorRecursivo (ProductId, VendorId, Description, NumProveedor)

AS (

SELECT ProveedoresOrdenados.ProductID

, ProveedoresOrdenados.VendorId

, CAST(ProveedoresOrdenados.VendorID AS VARCHAR(8000))

, ProveedoresOrdenados.NumProveedor

FROM ProveedoresOrdenados

WHERE NumProveedor=1

UNION ALL

SELECT ProveedoresOrdenados.ProductId, ProveedoresOrdenados.VendorId

, ProdVendedorRecursivo.Description + ', ' + CAST(ProveedoresOrdenados.VendorId AS VARCHAR(8000))

, ProveedoresOrdenados.NumProveedor

FROM ProveedoresOrdenados

JOIN ProdVendedorRecursivo

ON ProveedoresOrdenados.ProductID=ProdVendedorRecursivo.ProductId

AND ProveedoresOrdenados.NumProveedor=ProdVendedorRecursivo.NumProveedor+1

)

SELECT ProdVendedorRecursivo.ProductId, ProdVendedorRecursivo.Description

FROM ProdVendedorRecursivo

JOIN (SELECT ProductId, MAX(ProdVendedorRecursivo.NumProveedor) AS NumProveedor

FROM ProdVendedorRecursivo GROUP BY ProductId) AS Last

ON ProdVendedorRecursivo.ProductId=Last.ProductId

AND ProdVendedorRecursivo.NumProveedor=Last.NumProveedor

El ejemplo se basa en la BD de AdventureWorks y la tabla ProductVendors. ProductVendors es captura una relación de muchos a muchos entre Products y Vendors el comando pretende obtener para cada producto una columna con la lista de Proveedores (Vendors).

El comando usa 2 CTEs y algunas funciones disponibles desde SQL 2005 para obtener el resultado deseado. La primera consulta usa ROW_NUMBER para obtener un número consecutivo de proveedor para cada producto, este número facilita los JOINS de la segunda consulta. La segunda consulta aprovecha las consultas recursivas para concatenar la descripción, de manera tal que cada línea tiene la descripción de todos los anteriores. Finalmente la consulta filtra y despliega solo la última fila de cada producto.

No estoy seguro de su desempeño en tablas de tamaño considerable.

Posted at 12:17 AM by Javier Loria | Category: Desarrollo | Permalink | Email this Post | Comments (0)

1/17/2009

Llenar Fechas en México, Versión 2

Esta versión corrige algunos errores de la anterior, esa versión puede encontrarse en: http://blogs.solidq.com/ES/jloria/Lists/Posts/Post.aspx?ID=26

SET LANGUAGE Spanish;

GO

ALTER FUNCTION dbo.Pascua (@Yr as int)

RETURNS DATETIME

AS

BEGIN

-- Codigo encontrado en: http://www.tek-tips.com/faqs.cfm?fid=5075

Declare @Cent int, @I int, @J int, @K int, @Metonic int, @EMo int, @EDay int

Set @Cent=@Yr/100

Set @Metonic=@Yr % 19

Set @K=(@Cent-17)/25

Set @I=(@Cent-@Cent/4-(@Cent-@K)/3+19*@Metonic+15) % 30

Set @I=@I-(@I/28)*(1-(@I/28)*(29/(@I+1))*((21-@Metonic)/11))

Set @J=(@Yr+@Yr/4+@I+2-@Cent+@Cent/4) % 7

Set @EMo=3+(@I-@J+40)/44

Set @EDay=@I-@J+28-31*(@EMo/4)

RETURN CAST(CAST(@Yr*10000+@Emo*100+@Eday AS VARCHAR(8)) AS DATETIME);

/*This algorithm is from the work done by JM Oudin in 1940 and is accurate from year 1754 to 3400.*/

END;

GO

-- Llena Dimension con Datos

SET LANGUAGE Spanish;

GO

; WITH Fechas(Fecha, DimFechaId) AS

(

SELECT CAST('19010101' AS DATETIME)

, YEAR(CAST('19010101' AS DATETIME))*10000

+MONTH(CAST('19010101' AS DATETIME))*100

+DAY(CAST('19010101' AS DATETIME))

UNION ALL

SELECT DATEADD(DAY, 1, Fecha), YEAR(DATEADD(DAY, 1, Fecha))*10000+MONTH(DATEADD(DAY, 1, Fecha))*100+DAY(DATEADD(DAY, 1, Fecha))

FROM Fechas

WHERE DATEADD(DAY, 1, Fecha)<'20210101'

)

, Festividades (Festividad, Festivo, TipoFeriado, Dia, Lunes)

AS( SELECT 'Dia de todos los Santos' ,'Festivo NO Obligatorio', 'NoAplica', 1101, 0

UNION ALL SELECT 'Dia de Muertos' ,'Festivo NO Obligatorio', 'NoAplica ', 1102, 0

UNION ALL SELECT 'Aniversario de la Batalla Puebla' ,'Festivo NO Obligatorio', 'NoAplica', 505, 0

UNION ALL SELECT 'Aniversario de la Revolución Mexicana' ,'Festivo Obligatorio', 'Pasar', 1120, 3

-- 3er Lunes Noviembre

UNION ALL SELECT 'Natalicio de Benito Juarez''s' ,'Festivo Obligatorio', 'Pasar', 321, 3

-- 3er Lunes Marzo

UNION ALL SELECT 'Navidad', 'Festivo Obligatorio' ,'Fijo', 1225, 0

UNION ALL SELECT 'Dia de la Raza' ,'Festivo NO Obligatorio', 'NoAplica', 1012, 0

UNION ALL SELECT 'Dia de la Bandera' ,'Festivo NO Obligatorio', 'NoAplica', 224, 0

UNION ALL SELECT 'Dia de Independencia' ,'Festivo Obligatorio', 'Fijo', 916, 0

UNION ALL SELECT 'Dia del Trabajo', 'Festivo Obligatorio' ,'Fijo', 501, 0

UNION ALL SELECT 'Año Nuevo' ,'Festivo Obligatorio', 'Fijo', 101, 0

UNION ALL SELECT 'Día de la Virgen de Guadalupe' ,'Festivo Obligatorio', 'Fijo', 1212, 0

UNION ALL SELECT 'Dia de la Madre' ,'Festivo NO Obligatorio', 'NoAplica', 510, 0

UNION ALL SELECT 'Aniversario de la Constitucion' ,'Festivo Obligatorio', 'Pasar', 205, 1

-- Primer Lunes de Febrero

)

-- Agregar INSERT a la tabla.

SELECT DimFechaId

, Fechas.Fecha AS Fecha

, (DimFechaId/10000)*10000+101 AS AnioId

, CAST(Year(Fechas.Fecha) AS CHAR(4)) AS AnioNombre

, YEAR(Fechas.Fecha)*10000

+ (1+(DATEPART(Quarter, Fechas.Fecha)-1)*3)*100

+1 AS TrimestreId

, 'Q'+CAST(DATEPART(Quarter, Fechas.Fecha) AS VARCHAR(1))

+ '-' +CAST(Year(Fechas.Fecha) AS CHAR(4)) AS TrimestreNombre

, (DimFechaId/100)*100+1 AS MesId

, CAST(DATENAME(Month, Fechas.Fecha) AS VARCHAR(25))+ '-' + CAST(Year(Fechas.Fecha) AS CHAR(4)) AS MesNombre

, case when YEAR(DATEADD(DAY, 1, DATEADD(DAY, -DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) ))*10000

+MONTH(DATEADD(DAY, 1, DATEADD(DAY, -DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) ))*100

+DAY(DATEADD(DAY, 1, DATEADD(DAY, -DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) ))

>YEAR(Fechas.Fecha)*10000+101 THEN

YEAR(DATEADD(DAY, 1, DATEADD(DAY, -DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) ))*10000

+MONTH(DATEADD(DAY, 1, DATEADD(DAY, -DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) ))*100

+DAY(DATEADD(DAY, 1, DATEADD(DAY, -DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) ))

ELSE YEAR(Fechas.Fecha)*10000+101 END AS SemanaId

, 'S'+RIGHT('0'+CAST(DATENAME(Week, Fechas.Fecha) AS VARCHAR(25)),2)+ '-' + CAST(Year(Fechas.Fecha) AS CHAR(4)) AS SemanaName

, DATEPART(Weekday, Fechas.Fecha) AS DiaSemanaID

, DATENAME(Weekday, Fechas.Fecha) AS DiaSemanaNombre

, CASE

WHEN DATEPART(Weekday, Fechas.Fecha) IN (6,7) THEN 'FinSemana'

ELSE 'EntreSemana'

END AS DiaEntreFinSemana

, CASE

WHEN YEAR(Fechas.Fecha)<1991 THEN 'No Aplica'

WHEN DATEPART(Weekday, Fechas.Fecha) IN (7) THEN 'NO Hábil'

WHEN Fechas.Fecha BETWEEN DATEADD(day, -2, dbo.Pascua(YEAR(Fechas.Fecha)))

AND dbo.Pascua(YEAR(Fechas.Fecha)) THEN 'NO Hábil'

WHEN (Fechas.DimFechaId%10000)=1201 -- Primero de Diciembre

AND ((Fechas.DimFechaId/10000)-1994)%6=0 -- 1 de Cada 6 años

THEN 'NO Hábil'

WHEN Festividades.Festivo='Festivo Obligatorio'

AND (Festividades.TipoFeriado='Fijo' or YEAR(Fechas.Fecha)<=2006) -- Nueva Ley Aplica del 2006 en Adelante

THEN 'NO Hábil'

WHEN FLunes.Festivo='Festivo Obligatorio'

AND DATEPART(Weekday, Fechas.Fecha) =1

THEN 'NO Hábil'

WHEN FLunes.Festivo='Festivo Obligatorio'

AND Festividades.TipoFeriado='Pasar'

AND YEAR(Fechas.Fecha)>2006

THEN 'NO Hábil'

ELSE 'Hábil'

END AS DiaHabilFeriadoCorp

, CASE

WHEN YEAR(Fechas.Fecha)<1991 THEN 'No Aplica'

WHEN dbo.Pascua(YEAR(Fechas.Fecha))=Fechas.Fecha THEN 'Pascua'

WHEN Fechas.Fecha BETWEEN DATEADD(day, -1, dbo.Pascua(YEAR(Fechas.Fecha)))

AND DATEADD(day, -3, dbo.Pascua(YEAR(Fechas.Fecha)))

THEN DATENAME(Weekday, Fechas.Fecha) + ' Santo'

WHEN (Fechas.DimFechaId%10000)=1201 -- Primero de Diciembre

AND ((Fechas.DimFechaId/10000)-1994)%6=0 -- 1 de Cada 6 años

THEN 'Transmisión del Poder Ejecutivo Federal'

ELSE COALESCE(Festividades.Festividad

, FLunes.Festividad

, 'NO Festivo') END AS Festividad

, CASE

WHEN YEAR(Fechas.Fecha)<1991 THEN 'No Aplica'

WHEN Fechas.Fecha BETWEEN DATEADD(day, -2, dbo.Pascua(YEAR(Fechas.Fecha)))

AND dbo.Pascua(YEAR(Fechas.Fecha)) THEN 'Festivo Obligatorio'

WHEN (Fechas.DimFechaId%10000)=1201 -- Primero de Diciembre

AND ((Fechas.DimFechaId/10000)-1994)%6=0 -- 1 de Cada 6 años

THEN 'Festivo Obligatorio'

WHEN Festividades.Festivo IS NOT NULL and Festividades.TipoFeriado='Pasar'

THEN 'Festivo Pasado'

WHEN Festividades.Festivo IS NOT NULL

THEN Festividades.Festivo

WHEN FLunes.Festivo IS NOT NULL

THEN 'Lunes: Festivo Pasado'

ELSE 'NO Festivo' END AS TipoFestivoFestivo

FROM Fechas

LEFT JOIN Festividades

ON (Fechas.DimFechaId % 10000)=Festividades.Dia

AND YEAR(Fechas.Fecha)>=1990

LEFT JOIN Festividades AS FLunes

ON DATEPART(Weekday, Fechas.Fecha)=1 -- Dia Lunes

AND FLunes.TipoFeriado='Pasar' -- Que el Feriado sea Pasar

AND FLunes.Dia/100=month(Fechas.Fecha) -- Que sea el mismo Mes

AND YEAR(Fechas.Fecha)>2006

AND DATEADD(DAY

, ((8-DATEPART(WEEKDAY, CAST(CAST(YEAR(Fechas.Fecha)*10000+(FLunes.Dia/100)*100+1 AS CHAR(8)) AS SMALLDATETIME)))%7)

+ ((FLunes.Lunes-1)*7)

, CAST(CAST(YEAR(Fechas.Fecha)*10000+(FLunes.Dia/100)*100+1 AS CHAR(8)) AS SMALLDATETIME))

=Fechas.Fecha

AND DATEPART(Weekday

, CAST(CAST( FLunes.Dia+10000*YEAR(Fechas.Fecha) as varchar(8)) AS SMALLDATETIME))

NOT IN (1, 6,7)

OPTION (MAXRECURSION 0);

Posted at 5:35 PM by Javier Loria | Category: Inteligencia de Negocios | Permalink | Email this Post | Comments (0)

1/12/2009

Llenar Dimensión de Fechas en México

El siguiente código lo genere para que mis amigos del Nacional Monte de Piedad y tiene la lógica necesaria para generar un calendario de fechas desde 1901 hasta 2020, incluyendo los feriados obligatorios y no obligatorios de ley (Día de todos los Santos, Día de Muertos, Aniversario de la Batalla Puebla, Aniversario de la Revolución Mexicana, Natalicio de Benito Juarez, Navidad, Día de la Raza, Día de la Bandera, Día de Independencia, Día del Trabajo, Año Nuevo, Día de la Virgen de Guadalupe, Día de la Madre, Aniversario de la Constitución), semana santa (jueves santo, viernes santo, sábado santo y pascua), y el día del traslado del poder ejecutivo de ley.

El código se complica por:

Desde año 2006 algunos de los feriados son fijos y otros se pasan a un lunes específico dentro del mes, por ejemplo el tercer lunes de noviembre.
Los cálculos para la semana santa, son complejos.
La transmisión del Poder Ejecutivo ocurre cada seis años en una fecha fija.

El código puede ser limpiado, para ser simplificado y probablemente requiere ser más comentado, NUEVA VERSION EN: http://blogs.solidq.com/ES/jloria/Lists/Posts/Post.aspx?ID=27:

SET LANGUAGE Spanish;

GO

CREATE FUNCTION dbo.Pascua (@Yr as int)

RETURNS DATETIME

AS

BEGIN

-- Codigo encontrado en: http://www.tek-tips.com/faqs.cfm?fid=5075

Declare @Cent int, @I int, @J int, @K int, @Metonic int, @EMo int, @EDay int

Set @Cent=@Yr/100

Set @Metonic=@Yr % 19

Set @K=(@Cent-17)/25

Set @I=(@Cent-@Cent/4-(@Cent-@K)/3+19*@Metonic+15) % 30

Set @I=@I-(@I/28)*(1-(@I/28)*(29/(@I+1))*((21-@Metonic)/11))

Set @J=(@Yr+@Yr/4+@I+2-@Cent+@Cent/4) % 7

Set @EMo=3+(@I-@J+40)/44

Set @EDay=@I-@J+28-31*(@EMo/4)

RETURN CAST(CAST(@Yr*10000+@Emo*100+@Eday AS VARCHAR(8)) AS DATETIME);

/*This algorithm is from the work done by JM Oudin in 1940 and is accurate from year 1754 to 3400.*/

END;

GO

-- Codigo para llenar fecha de dimension Fecha en Mexico.

; WITH Fechas(Fecha, DimFechaId) AS

(

SELECT CAST('19010101' AS DATETIME)

, YEAR(CAST('19010101' AS DATETIME))*10000

+MONTH(CAST('19010101' AS DATETIME))*100

+DAY(CAST('19010101' AS DATETIME))

UNION ALL

SELECT DATEADD(DAY, 1, Fecha), YEAR(DATEADD(DAY, 1, Fecha))*10000+MONTH(DATEADD(DAY, 1, Fecha))*100+DAY(DATEADD(DAY, 1, Fecha))

FROM Fechas

WHERE DATEADD(DAY, 1, Fecha)<'20210101'

)

, Festividades (Festividad, Festivo, TipoFeriado, Dia, Lunes)

AS( SELECT 'Dia de todos los Santos' ,'Festivo NO Obligatorio', 'NoAplica', 1101, 0

UNION ALL SELECT 'Dia de Muertos' ,'Festivo NO Obligatorio', 'NoAplica ', 1102, 0

UNION ALL SELECT 'Aniversario de la Batalla Puebla' ,'Festivo NO Obligatorio', 'NoAplica', 505, 0

UNION ALL SELECT 'Aniversario de la Revolución Mexicana' ,'Festivo Obligatorio', 'Pasar', 1120, 3

-- 3er Lunes Noviembre

UNION ALL SELECT 'Natalicio de Benito Juarez' ,'Festivo Obligatorio', 'Pasar', 321, 3