El aprendiz de brujo

Pirámides de población superpuestas con PowerPivot

2012-01-19T22:20:00Z

En los artículos que dedicábamos a la creación de una pirámide de población empleando PowerPivot (parte1 y parte2), pudimos comprobar la potencia que este complemento para Excel proporciona a aquellos usuarios encargados de realizar determinados análisis de información sobre una base de datos de contenido demográfico.

Continuando la línea de trabajo abierta en aquellos artículos, en esta ocasión abordaremos el desarrollo de pirámides de población superpuestas, un aspecto del trabajo con pirámides demográficas, que consiste en observar y analizar las diferencias de estructura por sexo y edad existentes entre dos poblaciones, mediante la comparación directa de sus respectivas pirámides.

Antes de proseguir quisiera reiterar mi agradecimiento a Ricard Gènova Maleras, demógrafo especializado en análisis poblacionales sanitarios, perteneciente al Servicio de Informes de Salud y Estudios (Subdirección de Promoción de la Salud y Prevención, DG Atención Primaria, Consejería Sanidad CM), por su inestimable labor de revisión y orientación en todos aquellos conceptos sobre creación de pirámides demográficas, que tan valiosos han resultado en la elaboración del presente y los anteriores artículos que hemos publicado acerca de este mismo tema en el blog.

Breve introducción a las pirámides de población superpuestas

Si queremos describir gráficamente este escenario de trabajo, en primer lugar tomaremos las pirámides de las poblaciones a comparar...

...y a continuación colocaremos una pirámide sobre la otra, de forma que podamos apreciar y analizar las diferencias de población existentes entre ambas, finalizando así la operación.

Si se trata de dos poblaciones estáticas, la tarea no reviste mayor complejidad que la de rellenar las celdas de la hoja Excel con las cifras de las poblaciones correspondientes, y preparar el gráfico con forma de pirámide que combine los valores. Sin embargo, supongamos que tenemos una población base o de referencia, perteneciente al total de individuos de una región, y queremos establecer comparaciones entre ese total y una clasificación de los subgrupos en que dicha población puede ser dividida, como por ejemplo, las zonas geográficas o de atención sanitaria.

Si el número de zonas es muy elevado, aumentará la cantidad de cálculos y pirámides de subgrupos que necesitaremos elaborar para comparar con la pirámide principal de referencia, incrementándose la complejidad del análisis demográfico a realizar.

Es en este punto del problema donde entra en liza PowerPivot, ya que si lo utilizamos para gestionar los datos de las poblaciones con las que debemos trabajar, simplificaremos en gran medida la elaboración de las pirámides de población, así como su posterior fase de análisis.

Aprovechando la pirámide desarrollada en los artículos mencionados anteriormente, y plasmada en el archivo PiramidePoblacion.xlsx, utilizaremos dicho escenario de trabajo como punto de partida para elaborar, en primer lugar, las pirámides de población de las distintas zonas sanitarias; pasando a continuación a crear la pirámide de referencia, que representa a la población de la Comunidad de Madrid.

Las pirámides de zonificación

A simple vista, esta tarea puede parecer la más complicada, debido a que el objetivo de la misma consiste en crear una pirámide por cada una de las zonas sanitarias de la población que estamos manejando.

Nada más lejos de la realidad sin embargo, puesto que si hemos seguido los artículos ya comentados sobre creación de pirámides, nos habremos dado cuenta de que ese trabajo ya lo tenemos hecho en el archivo PiramidePoblacion.xlsx, creado como ejercicio de ejemplo de dichos artículos, gracias al empleo de una segmentación basada en la tabla Zona del modelo de datos de PowerPivot.

No obstante, con el fin de reorganizar los nombres que utilizaremos en este artículo, vamos a eliminar las medidas y el gráfico de la pirámide que inicialmente habíamos creado para la tabla dinámica. También cambiaremos, en la ventana de PowertPivot, el nombre de la tabla Poblacion por PoblacionZonas, haciendo clic derecho en la pestaña correspondiente a la tabla, y eligiendo la opción de menú "Cambiar nombre".

A continuación crearemos de nuevo las medidas de la tabla PoblacionZonas, con los siguientes nombres y expresiones en lenguaje DAX.

// PoblacionZonasSuma

=SUM([Sexo_Codigo])

// PoblacionZonasTotal

=CALCULATE(COUNTROWS(PoblacionZonas),ALL(Edad),ALL(Sexo))

// PoblacionZonasPorcentaje

=[PoblacionZonasSuma] / [PoblacionZonasTotal]

La forma de crear y configurar el gráfico de pirámide para estas poblaciones será la misma que en los artículos ya indicados, con la excepción del borde de las barras, a las que en esta ocasión asignaremos el color blanco, con un ancho de 2 puntos entre las mismas. Recordemos que para dar formato a las barras haremos clic derecho en cualquiera de ellas, seleccionando la opción de menú "Dar formato a serie de datos".

En la siguiente figura podemos observar el gráfico con las modificaciones que acabamos de comentar (incluyendo la segmentación por el campo Zona_DS de la tabla Zona), mostrando la pirámide de una de las zonas disponibles.

La pirámide de referencia

El siguiente paso consistirá en añadir al gráfico actual la pirámide que representará al total de la población, para lo cual nos situaremos en la ventana de PowerPivot, donde repetiremos el proceso de agregación de la tabla Poblacion desde la base de datos origen hasta el modelo de datos de PowerPivot; pero en esta ocasión daremos el nombre PoblacionReferencia a la tabla en PowerPivot, creando también la columna calculada Sexo_Codigo con la expresión DAX: "=IF([Sexo_ID] = "M", 1, -1)", ya que igualmente necesitaremos sumar la población en función del sexo de los individuos.

Tras esta operación crearemos las oportunas relaciones entre ambas tablas de población y el resto, quedando el resultado mostrado por la siguiente figura.

A continuación volveremos a la ventana de Excel, donde haremos clic en el botón "Actualizar" de la ventana "Lista de campos de PowerPivot", para que se refresque su contenido, y se incorpore la tabla PoblacionReferencia que acabamos de agregar a la ventana de PowerPivot.

En cuanto a las medidas necesarias para generar la pirámide correspondiente a esta población de referencia, en el siguiente bloque de código se muestran las expresiones DAX a utilizar para su creación.

// PoblacionReferenciaSuma

=CALCULATE(SUM([Sexo_Codigo]),ALL(Zona))

// PoblacionReferenciaTotal

=CALCULATE(COUNTROWS(PoblacionReferencia),ALL(Edad),ALL(Sexo),ALL(Zona))

// PoblacionReferenciaPorcentaje

=[PoblacionReferenciaSuma] / [PoblacionReferenciaTotal]

Comparadas las medidas de la tabla PoblacionZonas y PoblacionReferencia, encontramos en estas últimas una importante diferencia dentro de las operaciones encargadas de sumar la población y calcular su total, consistente en el uso de la función ALL aplicada a la tabla Zona, la cual es pasada como parámetro. En la medida PoblacionReferenciaSuma utilizaremos, además, la función CALCULATE, para que la operación de suma sobre la tabla de población de referencia se efectúe correctamente.

La función ALL, cuando interviene en una expresión DAX utilizada para construir una medida calculada que empleamos, por ejemplo, en una tabla dinámica, anula aquellos filtros aplicados en la tabla dinámica, que estén relacionados con la tabla pasada como parámetro a la función ALL, y que en nuestro caso, como ya hemos mencionado, afecta a la tabla Zona.

Para comprobar este comportamiento, que al mismo tiempo dará como resultado la pirámide de referencia, en la ventana de campos de PowerPivot arrastraremos el campo PoblacionReferenciaPorcentaje hasta el panel Valores. Esto producirá la inserción, en la tabla dinámica, de nuevas columnas para dicha medida; y en el gráfico de pirámide, de barras para representar las nuevas cifras de población.

Como siguiente paso aplicaremos el formato de porcentaje a las nuevas columnas de cifras de la tabla dinámica, y a continuación haremos clic derecho en una de las nuevas barras de población del gráfico, seleccionando la opción "Dar formato a serie de datos"; acción esta que abrirá la ventana del mismo nombre, donde modificaremos las siguientes propiedades/valores.

--Relleno: Sin Relleno.

--Color del borde: Línea sólida, color negro.

--Estilos de borde: Ancho de 1 punto.

Repitiendo esta misma operación sobre la otra barra de datos del gráfico completaremos la configuración visual de la nueva pirámide de población de referencia, que quedará superpuesta a la pirámide por zonas, logrando así nuestro objetivo. Este resultado nos permitirá apreciar las diferencias entre los dos tipos de población, facilitando su análisis.

Llegados a este punto damos por concluido el presente artículo sobre construcción de pirámides de población superpuestas, empleando PowerPivot como herramienta para modelar nuestros datos. En un próximo artículo abordaremos el desarrollo de pirámides acumuladas, una variedad de pirámide poblacional en la que las barras de la pirámide aportan mayor información, gracias a que permiten la inclusión de una categoría adicional de clasificación.

Optimizando la creación de estructuras de bases de datos en archivos de script mediante SSIS

2011-10-15T09:43:00Z

En el artículo "Utilizando SSIS para crear scripts de respaldo con la estructura de la base de datos" (parte 1 - parte 2), publicado con anterioridad en este blog, dejábamos pendiente de desarrollar una mejora que nos permitiera acelerar los tiempos de creación de los archivos de script, ya que el uso de algunas colecciones proporcionadas por la clase Database, más concretamente Views, StoredProcedures y UserDefinedFunctions, al contener una importante cantidad de objetos del sistema, provocaba que el tiempo de ejecución del proceso se extendiese más de lo necesario, en el caso de que no necesitáramos añadir al script tales tipos de objetos.

Por tal motivo, la finalidad del presente artículo será ofrecer una solución a dicho problema, exponiendo una técnica mediante la cual conseguiremos reducir los mencionados tiempos de ejecución.

Creación del proyecto de Integration Services

Como gestor de datos utilizaremos SQL Server Denali CTP3, y como entorno de desarrollo "SQL Server Business Intelligence Development Studio" (SSBIDS), con el que crearemos un nuevo proyecto de tipo "Integration Services Project" al que daremos el nombre EstructurasBBDDOptimizado (este proyecto de ejemplo puede descargarse en el siguiente enlace). Para más detalles acerca de la configuración del proyecto: paquete de integración .dtsx, variables, referencias a ensamblados, etc., consultar el proyecto desarrollado en el artículo mencionado al comienzo, ya que su modo de creación es equivalente. En cuanto al código fuente del paquete, utilizaremos como punto de partida el existente en dicho proyecto, sobre el que progresivamente iremos realizando las modificaciones necesarias.

Las vistas INFORMATION_SCHEMA.XXX

Si queremos averiguar cuáles son las vistas que el usuario ha creado en una base de datos, tenemos a nuestra disposición la vista del sistema INFORMATION_SCHEMA.VIEWS, que podemos localizar en el nodo "Views | System Views" de SQL Server Management Studio.

Una consulta sobre esta vista nos revelará, a través de sus campos TABLE_NAME y TABLE_SCHEMA, el nombre y esquema respectivamente de las vistas de usuario de la base de datos a la que estemos conectados.

De igual manera ocurre para los procedimientos almacenados y funciones, cuyos nombres y esquemas podemos encontrar en los campos ROUTINE_NAME y ROUTINE_SCHEMA de INFORMATION_SCHEMA.ROUTINES, otra vista del sistema, en la que para diferenciar el tipo de objeto emplearemos el campo ROUTINE_TYPE, comprobando que su valor sea "PROCEDURE" o "FUNCTION".

Utilizando las anteriores vistas del sistema crearemos, dentro del código del paquete de nuestro proyecto de integración, un conjunto de colecciones auxiliares, que contendrán, respectivamente, los nombres de las vistas, procedimientos almacenados y funciones de usuario de la base de datos. Posteriormente recorreremos estas colecciones, y por cada uno de sus elementos, accederemos a la colección-elemento equivalente que existe en la clase Microsoft.SqlServer.Management.Smo.Database, para ejecutar su método Script, agregando la sentencia de creación resultante al archivo de script.

Los espacios de nombres System.Data.SqlClient, System.Collections.Specialized y la creación de colecciones auxiliares

Accediendo al editor de código del paquete, comenzaremos a implementar las operaciones que acabamos de describir declarando el espacio de nombres System.Data.SqlClient, mediante el cual tendremos acceso a las clases SqlConnection, SqlCommand y SqlDataReader, necesarias para manipular los objetos de la base de datos con los que deberemos trabajar.

A continuación nos desplazaremos hasta el bucle foreach que recorre la colección Databases del objeto Server, y mediante una conexión (objeto SqlConnection) contra la base de datos en curso, ejecutaremos una consulta a través de un objeto SqlCommand sobre la vista INFORMATION_SCHEMA.VIEWS, que nos devolverá, en forma de objeto SqlDataReader, el conjunto de resultados con los nombres de las vistas y esquemas de la base de datos. Seguidamente recorreremos este SqlDataReader y añadiremos sus valores a un objeto StringCollection (para usar este tipo de colección declararemos el espacio de nombres System.Collections.Specialized). Como resultado, obtendremos una colección con los nombres de las vistas de usuario y esquemas de la base de datos que queremos incluir en el script, evitando de esta manera las vistas del sistema.

//....
using System.Data.SqlClient;
using System.Collections.Specialized;
//....
foreach (Database oDatabase in oServer.Databases)
{
    if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
    {
        Dts.Events.FireInformation(0, string.Empty, "Procesando BBDD: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);


        // conexión
        SqlConnection oConnection = new SqlConnection("Data Source=" + oServer.Name +
            ";Initial Catalog=" + oDatabase.Name + ";Integrated Security=True");

        SqlCommand oCommand = new SqlCommand();
        oCommand.Connection = oConnection;
        oCommand.CommandType = CommandType.Text;

        oConnection.Open();


        // vistas
        oCommand.CommandText = "SELECT TABLE_NAME + ' ' + TABLE_SCHEMA AS Vista " +
            "FROM INFORMATION_SCHEMA.VIEWS ORDER BY TABLE_SCHEMA, TABLE_NAME";

        SqlDataReader oDataReader = oCommand.ExecuteReader();
        StringCollection cllVistas = new StringCollection();

        while (oDataReader.Read())
        {
            cllVistas.Add(oDataReader["Vista"].ToString());
        }

        oDataReader.Close();

        //....

Con respecto a los procedimientos almacenados y funciones procederemos de igual modo, pero empleando en este caso la vista INFORMATION_SCHEMA.ROUTINES para crear las colecciones auxiliares, como vemos en el siguiente bloque de código.

//....
// procedimientos almacenados
oCommand.CommandText = "SELECT ROUTINE_NAME + ' ' + ROUTINE_SCHEMA AS ProcedimientoAlmacenado " +
    "FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' " +
    "ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME";

oDataReader = oCommand.ExecuteReader();
StringCollection cllProcedimientosAlmacenados = new StringCollection();

while (oDataReader.Read())
{
    cllProcedimientosAlmacenados.Add(oDataReader["ProcedimientoAlmacenado"].ToString());
}

oDataReader.Close();


// funciones
oCommand.CommandText = "SELECT ROUTINE_NAME + ' ' + ROUTINE_SCHEMA AS Funcion " +
    "FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' " +
    "ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME";

oDataReader = oCommand.ExecuteReader();
StringCollection cllFunciones = new StringCollection();

while (oDataReader.Read())
{
    cllFunciones.Add(oDataReader["Funcion"].ToString());
}
oDataReader.Close();

oConnection.Close();
//....

Creación de las sentencias para el script

Finalizada la creación de las colecciones auxiliares, nuestro siguiente paso consistirá en crear las sentencias de script para la base de datos y tablas; operaciones que no han sufrido modificación con respecto al anterior artículo, ya que en este caso no debemos lidiar con objetos del sistema.

//....
// base de datos
oScriptingOptions.FileName = sRutaArchivo + oDatabase.Name + ".sql";
oDatabase.Script(oScriptingOptions);

// tablas
foreach (Table oTable in oDatabase.Tables)
{
    Dts.Events.FireInformation(0, string.Empty,
        "Procesando tabla: " + oDatabase.Name + '-' + oTable.Schema + '.' + oTable.Name,
        string.Empty, 0, ref bOtraVez);
    oTable.Script(oScriptingOptions);
}
//....

A continuación crearemos un bucle foreach para recorrer cada una de las colecciones auxiliares, accediendo, en cada iteración, al elemento de la colección equivalente perteneciente al objeto Database. Para ello, cada cadena (conteniendo los nombres del objeto y esquema, separados por un carácter de espacio) que recuperemos de la colección auxiliar, será asignada, mediante el método String.Split, a un array de tipo string con dos elementos. Como separador para el método Split, utilizaremos un array de tipo char con un carácter de espacio en blanco.

Seguidamente, utilizando los valores del array string, accederemos al elemento de la colección que corresponda en cada caso (Views, StoredProcedures o UserDefinedFunctions) perteneciente al objeto Database, para ejecutar su método Script. Adicionalmente, emplearemos la enumeración ObjetosBD en el índice del array, declarada con ámbito de la clase ScriptMain, para facilitar la lectura del código.

//....
enum ObjetosBD : int
{
    Nombre,
    Esquema
};

public void Main()
{
//....
    foreach (Database oDatabase in oServer.Databases)
    {
        if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
        {
            //....
            char[] aSeparador = { ' ' };


            // vistas
            string[] aVista;

            foreach (string sVista in cllVistas)
            {
                aVista = sVista.Split(aSeparador);
                Dts.Events.FireInformation(0, string.Empty,
                    "Procesando vista: " + oDatabase.Name + '-' +
                    aVista[(int)ObjetosBD.Esquema] + '.' + aVista[(int)ObjetosBD.Nombre],
                    string.Empty, 0, ref bOtraVez);

                ((View)oDatabase.Views[

                     aVista[(int)ObjetosBD.Nombre],

                     aVista[(int)ObjetosBD.Esquema]]).Script(oScriptingOptions);
            }


            // procedimientos almacenados
            string[] aProcedimientoAlmacenado;

            foreach (string sProcedimientoAlmacenado in cllProcedimientosAlmacenados)
            {
                aProcedimientoAlmacenado = sProcedimientoAlmacenado.Split(aSeparador);
                Dts.Events.FireInformation(0, string.Empty,
                    "Procesando procedimiento almacenado: " + oDatabase.Name + '-' +
                    aProcedimientoAlmacenado[(int)ObjetosBD.Esquema] + '.' + aProcedimientoAlmacenado[(int)ObjetosBD.Nombre],
                    string.Empty, 0, ref bOtraVez);

                ((StoredProcedure)oDatabase.StoredProcedures[

                     aProcedimientoAlmacenado[(int)ObjetosBD.Nombre],

                     aProcedimientoAlmacenado[(int)ObjetosBD.Esquema]]).Script(oScriptingOptions);
            }


            // funciones
            string[] aFuncion;

            foreach (string sFuncion in cllFunciones)
            {
                aFuncion = sFuncion.Split(aSeparador);
                Dts.Events.FireInformation(0, string.Empty,
                    "Procesando función: " + oDatabase.Name + '-' +
                    aFuncion[(int)ObjetosBD.Esquema] + '.' + aFuncion[(int)ObjetosBD.Nombre],
                    string.Empty, 0, ref bOtraVez);

                ((UserDefinedFunction)oDatabase.UserDefinedFunctions[

                     aFuncion[(int)ObjetosBD.Nombre],

                     aFuncion[(int)ObjetosBD.Esquema]]).Script(oScriptingOptions);
            }
//....

Tras añadir el anterior bloque de código al paquete de integración, volveremos a ejecutar éste, comprobando cómo los tiempos de proceso se reducirán notablemente.

Ajustar el tamaño de la base de datos en la sentencia de creación

Si abrimos cualquiera de los archivos de script generados tras la ejecución del paquete, observaremos que la sentencia "CREATE DATABASE" asigna, para los archivos de datos y log, el tamaño actual de la base de datos.

Pero supongamos que algunos de estos archivos de datos y log tienen un tamaño considerable, y necesitamos que al ser creada la base de datos correspondiente mediante la ejecución del script, dicho tamaño inicial no sea elevado.

Por tal motivo, la técnica que proponemos para solucionar este problema consiste en acceder y manipular el contenido del archivo de script mediante los métodos de las clases File y String, modificando el tamaño inicialmente asignado por uno menor. Para ello, pasaremos el contenido del archivo a una variable de tipo string, dentro de la cual, buscaremos las asignaciones de tamaño para la base de datos, reemplazándolas por los nuevos valores. Finalmente, borraremos el archivo original, creando uno nuevo a partir del contenido modificado existente en la variable string.

//....
Dts.Events.FireInformation(0, string.Empty, "Ajustando tamaño inicial de archivos: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);


string sTextoScript = File.ReadAllText(oScriptingOptions.FileName);

int nPosicionInicio = sTextoScript.IndexOf(", SIZE");
int nPosicionFin = sTextoScript.IndexOf(", MAXSIZE");

sTextoScript = sTextoScript.Replace(sTextoScript.Substring(nPosicionInicio,

(nPosicionFin - nPosicionInicio)), ", SIZE = 3072KB");


nPosicionInicio = sTextoScript.IndexOf(", SIZE", nPosicionInicio + 6);
nPosicionFin = sTextoScript.IndexOf(", MAXSIZE", nPosicionFin + 6);

sTextoScript = sTextoScript.Replace(sTextoScript.Substring(nPosicionInicio,

(nPosicionFin - nPosicionInicio)), ", SIZE = 1024KB");


File.Delete(oScriptingOptions.FileName);

File.WriteAllText(oScriptingOptions.FileName, sTextoScript);

Realizadas las anteriores modificaciones sobre nuestro código fuente, damos por concluido este artículo, en el que hemos expuesto una técnica para optimizar el tiempo empleado por un paquete de Integration Services en generar los archivos de script conteniendo las estructuras de las bases de datos de SQL Server. Espero que os sirva de ayuda.

Un saludo.

Utilizando SSIS para crear scripts de respaldo con la estructura de la base de datos (y 2)

2011-10-03T19:02:00Z

En la primera entrega de este artículo realizábamos una introducción al desarrollo de proyectos de Integration Services desde el entorno de BIDS, con el objetivo de crear scripts de la estructura de las bases de datos de nuestro servidor utilizando Script Task, un componente de los paquetes de integración. Será en esta segunda parte, cuando nos centraremos en la generación de los mencionados archivos de script.

La clase ScriptingOptions, el método Script y la generación del archivo de script

Pasemos a la siguiente fase en el desarrollo de nuestro proceso (el proyecto de Integration Services con los ejemplos puede descargarse aquí), que consistirá en crear un archivo de script, que de momento solamente incluirá las sentencias de creación de la base de datos. Esta operación será realizada dentro del bucle que recorre la colección de bases de datos del servidor, obteniendo un archivo por cada base de datos. Previamente deberemos crear la carpeta "C:\EstructurasBBDDCopias", que será donde se generen los archivos.

La manera de especificar el archivo de script pasa por utilizar un objeto de la clase ScriptingOptions, que como adivinamos por su nombre, es la encargada de configurar las opciones relativas a la generación del archivo. Una vez instanciado dicho objeto, la propiedad FileName será la que utilicemos para indicar el nombre y ruta del archivo que se va a crear; mientras que el acto de generación lo llevaremos a cabo llamando al método Server.Script, pasándole como parámetro el objeto ScriptingOptions.

Dado que este es un proceso que probablemente ejecutaremos con cierta frecuencia, añadiremos las instrucciones necesarias para crear, dentro de la carpeta "C:\EstructurasBBDDCopias", una subcarpeta cuyo nombre sea la fecha de ejecución del paquete, siendo esta última, la carpeta en donde se depositarán los archivos de script. Para poder utilizar los objetos necesarios para la manipulación de carpetas, añadiremos a nuestro código la declaración del espacio de nombres System.IO.

using System.IO;
//....
Server oServer = new Server("WINTERFELL-PC");

bool bOtraVez = false;

string sRutaArchivo = @"C:\EstructurasBBDDCopias\" + DateTime.Today.ToString("yyyyMMdd") + @"\";


if (Directory.Exists(sRutaArchivo))
{
    Directory.Delete(sRutaArchivo, true);
}


Directory.CreateDirectory(sRutaArchivo);


ScriptingOptions oScriptingOptions = new ScriptingOptions();


foreach (Database oDatabase in oServer.Databases)
{
    Dts.Events.FireInformation(0, string.Empty, "Procesando BBDD: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);
    oScriptingOptions.FileName = sRutaArchivo + oDatabase.Name + ".sql";
    oDatabase.Script(oScriptingOptions);
}
//....

Después de ejecutar nuevamente el paquete, en la ruta especificada aparecerán los archivos recién creados, en cuyo interior encontraremos las sentencias de creación de la base de datos.

CREATE DATABASE [Chinook]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Chinook', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Chinook.mdf' , 
SIZE = 5184KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Chinook_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Chinook_log.ldf' , 
SIZE = 1040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE Modern_Spanish_CI_AS
GO
ALTER DATABASE [Chinook] SET COMPATIBILITY_LEVEL = 110
GO
--....

Con toda seguridad habremos observado que se han generado archivos tanto para las bases de datos de usuario como de sistema, aunque probablemente en estas últimas no estemos interesados. Si este es el caso, podemos evitar su creación interrogando a la propiedad Database.IsSystemObject, de tipo bool, mediante la que averiguaremos si se trata de una base de datos de sistema o usuario.

Con las bases de datos del servidor de informes estamos en una situación parecida, pero como se trata de bases de datos de usuario, si no queremos generar script para ellas lo haremos comprobando el comienzo de su nombre.

//....
foreach (Database oDatabase in oServer.Databases)
{
    if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
    {
        Dts.Events.FireInformation(0, string.Empty, "Procesando BBDD: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);
        oScriptingOptions.FileName = sRutaArchivo + oDatabase.Name + ".sql";
        oDatabase.Script(oScriptingOptions);
    }
}
//....

Modificación de valores a través de variables

Para especificar el nombre del servidor con el que trabajamos y la ruta en la que se crearán los archivos, actualmente utilizamos valores fijos de cadena, lo cual se convertirá en un inconveniente cuando tengamos que instalar el paquete en un servidor distinto del utilizado para el desarrollo, o bien queramos emplear una ruta diferente para la creación de los scripts.

Podemos remediar este problema gracias a la posibilidad de declarar variables en el diseñador del paquete, cuyos valores pueden ser recuperados desde el código fuente en el momento de su ejecución.

Para declarar una variable nos situaremos en la ventana "Variables" de Visual Studio y haremos clic en el botón "Add Variable" de su barra de herramientas, creándose la nueva variable vacía, cuyos campos de propiedad (Name, Data type y Value) completaremos con los valores oportunos. En la siguiente imagen vemos la creación de la variable RutaArchivos, de tipo cadena, con el valor "C:\EstructurasBBDDCopias\".

En el caso de que esta ventana esté oculta, la mostraremos seleccionando la opción de menú de Visual Studio "SSIS | Variables".

Además de las variables creadas por el desarrollador, disponemos también de un conjunto de variables de sistema, que podemos visualizar haciendo clic en el botón "Show System Variables" de la barra herramientas de esta misma ventana. Para el ejemplo que estamos desarrollando, haremos uso de la variable "MachineName", que nos proporcionará el nombre del servidor en el que se ejecuta el paquete.

El siguiente paso consistirá en preparar las variables para que puedan estar accesibles desde el código fuente del paquete. Para ello, abriremos la ventana "Script Task Editor" del objeto Script Task, y en la propiedad "ReadOnlyVariables", perteneciente a la categoría "Script", escribiremos los nombres de las variables como una lista separada por comas, o bien haremos clic en el botón de puntos suspensivos, que abrirá la ventana "Select Variables", en donde realizaremos la selección, lo que tendrá el mismo efecto.

A continuación volveremos a la ventana del editor de código, donde para recuperar el valor de las variables emplearemos la colección "Variables" del objeto Dts, situando en el índice de la colección una cadena con el nombre de la variable a obtener, como vemos en el siguiente bloque de código.

//....
Server oServer = new Server(Dts.Variables["System::MachineName"].Value.ToString());
bool bOtraVez = false;
string sRutaArchivo = Dts.Variables["User::RutaArchivos"].Value.ToString() + DateTime.Today.ToString("yyyyMMdd") + @"\";
//....

Para pasar valores a las variables del paquete, una vez éste ha sido importado al servidor de Integration Services, podemos utilizar la ventana "Execute Package Utility". En la categoría "Command Line" haremos clic en el RadioButton "Edit the command line manually", añadiendo al final de las opciones del comando DTExec la siguiente opción /SET, con la que cambiaremos la ruta en donde se crearán los scripts.

/SET "\Package.Variables[User::RutaArchivos].Value";"C:\Pruebas\\"

Estos mismos valores serán los que usaremos en el caso de ejecutar el paquete desde la línea de comandos en una ventana "Símbolo del sistema" mediante la utilidad DTExec.exe.

Incorporando las tablas al proceso

Los siguientes objetos que vamos a generar en el script serán las tablas, por lo que siguiendo la misma mecánica utilizada con las bases de datos, crearemos un bucle que recorrerá la colección Tables del objeto Database en el que estemos posicionados, añadiendo cada tabla al archivo por medio de su método Script.

//....
foreach (Database oDatabase in oServer.Databases)
{
    if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
    {
        Dts.Events.FireInformation(0, string.Empty, "Procesando BBDD: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);
        oScriptingOptions.FileName = sRutaArchivo + oDatabase.Name + ".sql";
        oDatabase.Script(oScriptingOptions);


        foreach (Table oTable in oDatabase.Tables)
        {
            Dts.Events.FireInformation(0, string.Empty, "Procesando tabla: " + oDatabase.Name + '-' + 
                oTable.Schema + '.' + oTable.Name, string.Empty, 0, ref bOtraVez);
            oTable.Script(oScriptingOptions);
        }
    }
}
//....

Al ejecutar el paquete los archivos serán generados de nuevo, pero si abrimos cualquiera de ellos nos llevaremos una desagradable sorpresa, ya que sólo contendrán una única sentencia de creación de tabla. Esto es debido a que, por defecto, el método Script, cada vez que es llamado, sobrescribe el contenido del archivo con la sentencia recién generada.

Igualmente nos encontraremos ante otra serie de inconvenientes como son la ausencia de las sentencias para la creación de los índices de las tablas, claves primarias, externas, etc.

Para evitar este comportamiento recurriremos nuevamente al objeto ScriptingOptions, asignando el valor true a las propiedades que describimos a continuación.

--AppendToFile. Las sentencias generadas mediante el método Script se irán agregando al archivo sobre el que estamos trabajando, evitando así que la última sentencia reemplace su contenido.

--IncludeDatabaseContext. Antes de cada sentencia de creación de tabla se creará una sentencia "USE NombreBBDD", que nos asegure que la tabla sea creada en la base de datos correcta.

--Indexes. Añade las sentencias para la creación de índices (agrupados / no agrupados) y claves primarias en las tablas.

--DriForeignKeys. Añade las sentencias para la creación de las claves externas de las tablas.

ScriptingOptions oScriptingOptions = new ScriptingOptions();
oScriptingOptions.AppendToFile = true;
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.Indexes = true;
oScriptingOptions.DriForeignKeys = true;

Dentro del apartado de creación de índices, gracias a las propiedades DriPrimaryKey, ClusteredIndexes y NonClusteredIndexes, pertenecientes a la clase ScriptingOptions, vamos a tener la capacidad de ser más selectivos a la hora de elegir el tipo de índice a incluir en el script. Si por ejemplo, en el script solamente queremos añadir sentencias para crear los índices agrupados y las claves primarias, dejando fuera los índices no agrupados, en primer lugar asignaremos false a la propiedad Indexes, para impedir así la creación de todos los tipos de índices, y después realizaremos la asignación de las propiedades antes mencionadas, para que sean creados los tipos de índices elegidos.

oScriptingOptions.Indexes = false;
oScriptingOptions.DriPrimaryKey = true;
oScriptingOptions.ClusteredIndexes = true;
oScriptingOptions.NonClusteredIndexes = false;

Vistas, procedimientos almacenados, funciones y objetos del sistema

La inclusión en el proceso de otros objetos de la base de datos tales como vistas, procedimientos almacenados, funciones, etc., no reviste mayor complicación que la de añadir un nuevo bucle foreach, que recorra la colección correspondiente del objeto Database: Views, StoredProcedures, UserDefinedFunctions, etc.

Entre los tipos de objetos que acabamos de mencionar, además de los creados por el usuario, existen versiones del sistema, y adicionalmente, algunos de estos procedimientos almacenados y funciones del sistema se encuentran encriptados (los reconoceremos en SQL Server Management Studio porque su icono muestra un candado), provocando un error si intentamos incluirlos en el script. Es por ello que debemos evitar esta situación, consultando su propiedad IsEncypted, como vemos en el siguiente bloque de código.

//....
if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
{
    //....
    foreach (View oView in oDatabase.Views)
    {
        Dts.Events.FireInformation(0, string.Empty, "Procesando vista: " + oDatabase.Name + '-' + 
           oView.Schema + '.' + oView.Name, string.Empty, 0, ref bOtraVez);
        oView.Script(oScriptingOptions);
        }


    foreach (StoredProcedure oStoredProcedure in oDatabase.StoredProcedures)
    {
        if (!oStoredProcedure.IsEncrypted)
        {
            Dts.Events.FireInformation(0, string.Empty, "Procesando procedimiento almacenado: " + oDatabase.Name + '-' + 
                oStoredProcedure.Schema + '.' + oStoredProcedure.Name, string.Empty, 0, ref bOtraVez);
            oStoredProcedure.Script(oScriptingOptions);
        }
    }


    foreach (UserDefinedFunction oUserDefinedFunction in oDatabase.UserDefinedFunctions)
    {
        if (!oUserDefinedFunction.IsEncrypted)
        {
            Dts.Events.FireInformation(0, string.Empty, "Procesando función: " + oDatabase.Name + '-' + 
                oUserDefinedFunction.Schema + '.' + oUserDefinedFunction.Name, string.Empty, 0, ref bOtraVez);
            oUserDefinedFunction.Script(oScriptingOptions);
        }
    }
}
//....

Por otra parte, la inclusión de estos objetos del sistema hará que el archivo de script crezca hasta alcanzar un tamaño considerable, por lo que es posible que no deseemos añadirlos.

Si este es nuestro caso, el modo más sencillo de resolver el problema consiste en asignar false a la propiedad ScriptingOptions.AllowSystemObjects. De esta forma tampoco sería necesario comprobar si ciertos objetos están encriptados, obteniendo además un archivo de script con un tamaño más reducido.

ScriptingOptions oScriptingOptions = new ScriptingOptions();
//....
oScriptingOptions.AllowSystemObjects = false;
//....
foreach (View oView in oDatabase.Views)
{
    Dts.Events.FireInformation(0, string.Empty, "Procesando vista: " + oDatabase.Name + '-' + 
        oView.Schema + '.' + oView.Name, string.Empty, 0, ref bOtraVez);
    oView.Script(oScriptingOptions);
}


foreach (StoredProcedure oStoredProcedure in oDatabase.StoredProcedures)
{
    Dts.Events.FireInformation(0, string.Empty, "Procesando procedimiento almacenado: " + oDatabase.Name + '-' + 
        oStoredProcedure.Schema + '.' + oStoredProcedure.Name, string.Empty, 0, ref bOtraVez);
    oStoredProcedure.Script(oScriptingOptions);
}


foreach (UserDefinedFunction oUserDefinedFunction in oDatabase.UserDefinedFunctions)
{
    Dts.Events.FireInformation(0, string.Empty, "Procesando función: " + oDatabase.Name + '-' + 
        oUserDefinedFunction.Schema + '.' + oUserDefinedFunction.Name, string.Empty, 0, ref bOtraVez);
    oUserDefinedFunction.Script(oScriptingOptions);
}
//....

Sin embargo, el hecho de no añadir los objetos del sistema al script no va a suponer una mejora significativa en la duración del proceso, ya que a pesar de que el valor de la propiedad AllowSystemObjects sea false, las colecciones Views, StoredProcedures y UserDefinedFunctions siguen conteniendo los objetos del sistema.

Con el fin de agilizar estos tiempos de ejecución, las colecciones que utilizamos para construir el script deberían contener única y exclusivamente aquellos objetos que vayamos a incluir en el archivo. No obstante, esta es una tarea de optimización que queda fuera del ámbito del presente artículo, pero que abordaremos próximamente. Por el momento, confiamos en que la solución aquí propuesta resulte de utilidad para todos aquellos lectores que precisen crear scripts con las estructuras de sus bases de datos.

Un saludo.

Utilizando SSIS para crear scripts de respaldo con la estructura de la base de datos (1)

2011-10-02T14:53:00Z

Dentro del contexto de cualquier sistema de información, resulta fundamental la existencia de una adecuada política de copia de seguridad, como medio de contar con un respaldo para proteger los datos almacenados.

Si nos ceñimos, sin embargo, al desarrollo de sistemas Business Intelligence, con cierta frecuencia podemos encontrarnos ante un almacén de datos en el que cada vez que debamos procesar los cubos y dimensiones que lo integran, primeramente habremos de vaciar las tablas asociadas a dichos elementos, para recargarlas por completo con datos actualizados provenientes de muy diversas fuentes.

En un escenario como el que presentamos, antes que la salvaguardia de los datos resulta más importante hacer una copia de la estructura de las tablas, vistas, procedimientos almacenados, etc., de la base de datos, máxime si se trata de un almacén de datos que se encuentra en pleno proceso de desarrollo, ya que la información reside en las fuentes de datos originales que se usan para realizar la carga del almacén, las cuales, habitualmente, disponen de sus propios mecanismos de copia de seguridad.

Por dicho motivo, en el presente artículo vamos a centrarnos en desarrollar un proceso que se ocupe de generar, para cada una de las bases de datos de nuestra instancia de SQL Server, un archivo de script con todas las instrucciones de definición (Data Definition Language) de los objetos integrantes de la base de datos. Para lograrlo emplearemos paquetes de SSIS (SQL Server Integration Services), y más concretamente, el componente "Script Task", que nos otorgará un alto grado de flexibilidad y control sobre la generación de los archivos de script y los objetos de la base de datos a incluir en dichos archivos.

Scptxfr.exe. Los orígenes en SQL Server 2000

Pero antes de adentrarnos en el desarrollo del proceso en SSIS, queremos hacer mención a scptxfr.exe, una herramienta que puede resultar de utilidad para aquellos lectores que todavía tengan que realizar el mantenimiento de sistemas basados en SQL Server 2000.

Ejecutada en modo línea de comando desde una ventana "Símbolo del sistema", o bien desde un procedimiento almacenado, también nos permite, aunque con menos flexibilidad que en SSIS, como veremos más adelante, generar un conjunto de archivos con las instrucciones de creación de tablas, índices, claves primarias, vistas, etc., necesarios para la adecuada regeneración de una base de datos.

Entre los parámetros de que dispone, destacaremos los siguientes:

-- /s. Nombre del servidor SQL Server con el que vamos a trabajar.

-- /d. Nombre de la base de datos a partir de la que vamos a generar los scripts.

-- /I. Tipo de autenticación.

-- /F. Nombre (incluyendo ruta si es preciso) del archivo script destino.

Para una descripción más detallada de todos los parámetros, sugerimos al lector que ejecute la instrucción SCPTXFR /? desde una ventana de símbolo del sistema.

De esta forma podríamos crear un procedimiento almacenado como el que vemos a continuación, que recorriera las bases de datos del servidor SQL Server, y creara, por cada una de ellas, una carpeta con los archivos de script correspondientes.

CREATE PROCEDURE CrearEstructurasDB
AS

CREATE TABLE #NombresDBs
(
Linea_ID int identity(1,1), 
Nombre varchar(150)
)


INSERT INTO #NombresDBs
SELECT name FROM master..sysdatabases 
ORDER BY name



DECLARE @sDirectorioBak varchar(50)
SET @sDirectorioBak = 'c:\pruebas\' + convert(varchar,getdate(),112) + '\'


DECLARE @nLineaID int
SET @nLineaID = 1


DECLARE @nLineaIDUltimo int
SET @nLineaIDUltimo = (SELECT MAX(Linea_ID) FROM #NombresDBs)


DECLARE @sCmdCrearDirectorio varchar(200)
DECLARE @sNombreDB varchar(150)
DECLARE @sCmd varchar(300)


WHILE (@nLineaID <= @nLineaIDUltimo)
BEGIN
    SELECT @sNombreDB = Nombre FROM #NombresDBs WHERE Linea_ID = @nLineaID


    SET @sCmdCrearDirectorio = 'MKDIR' + @sDirectorioBak + @sNombreDB


    EXECUTE master..xp_cmdshell @sCmdCrearDirectorio


    SET @sCmd = '"' + 'C:\Archivos de programa\Microsoft SQL Server\MSSQL\Upgrade\scptxfr' + '" /s ' +
        @@servername + ' /d ' + @sNombreDB + ' /I /F ' + @sDirectorioBak + @sNombreDB


    EXECUTE master..xp_cmdshell @sCmd


    SET @nLineaID = @nLineaID + 1
end


DROP TABLE #NombresDBs

Dado que el anterior procedimiento almacenado crea los archivos de script en una ruta cuyo nombre incluye la fecha del sistema, podemos programar su ejecución dentro de una tarea de SQL Server, y obtener de esa forma una copia de respaldo diaria o con la frecuencia que establezcamos, de los scripts de creación de las bases de datos.

SSIS y los paquetes de integración. La evolución

Pero según reza su título, nos encontramos ante un artículo basado en SSIS, por lo que tendremos que usar SQL Server 2005 o superior, ya que es a partir de dicha versión cuando se incluyó esta tecnología de integración de datos.

Utilizaremos por lo tanto SQL Server Denali CTP3, dado que se trata de la versión más reciente disponible de SQL Server en el momento de desarrollar los ejemplos del artículo. Para generar los scripts de estructuras emplearemos las bases de datos AdventureWorks2008R2 y Chinook.

El proyecto de servicios de integración

En primer lugar iniciaremos "SQL Server Business Intelligence Development Studio" (SSBIDS), o lo que es lo mismo, el entorno de desarrollo de Visual Studio 2010, situado en la ruta de menú "Inicio |Microsoft SQL Server Denali CTP3".

Esta versión especial de Visual Studio 2010, que acompaña a SQL Server, contiene las plantillas específicas para la creación de proyectos de inteligencia de negocio. Por lo que seleccionando la opción de menú "File | New | Project", abriremos el diálogo "New Project", donde elegiremos un proyecto de tipo "Integration Services Project" al que daremos el nombre EstructurasBBDD.

Este tipo de proyecto incluye por defecto un paquete llamado Package.dtsx, al que cambiaremos el nombre por PckEstructurasBBDD.dtsx, utilizando la propiedad "File Name" de la ventana de propiedades. También podemos hacer clic derecho en el paquete y elegir la opción de menú "Rename".

Script Task. Desarrollo programático de operaciones personalizadas

Una vez abierto el paquete, el siguiente paso consistirá en añadir al diseñador una tarea de código fuente haciendo doble clic en el icono "Script Task", situado en la ventana "SSIS Toolbox" del entorno de desarrollo.

También cambiaremos el nombre predeterminado de este elemento por scptEstructurasBBDD editando su propiedad Name.

Escritura y ejecución del código de Script Task

Para escribir el código de nuestras operaciones haremos clic derecho sobre el objeto Script Task, y elegiremos la opción de menú "Edit", que abrirá el diálogo "Script Task Editor". De las diversas propiedades disponibles, ScriptLanguage, ubicada dentro de la categoría "Script", nos permite establecer el lenguaje que vamos a usar para programar el proceso (por defecto es C#, aunque también podemos utilizar Visual Basic). Haciendo clic en el botón "Edit Script" abriremos el editor de código de Visual Studio 2010, ventana que nos resultará familiar si hemos trabajado anteriormente con este entorno de desarrollo.

El punto de entrada en la ejecución del paquete lo representa el método Main de la clase ScriptMain, que viene integrada por defecto en el código del paquete. En este método escribiremos el código con aquellas operaciones necesarias para interactuar con los objetos de las bases de datos de la instancia de SQL Server con la que vayamos a trabajar.

Para familiarizarnos con el modelo de objetos a manejar y facilitar así el aprendizaje del proceso, vamos a comenzar implementando una operación muy sencilla: recorrer las bases de datos del servidor y visualizar su nombre.

En primer lugar necesitamos tener acceso a las clases que representan los objetos del servidor SQL, por lo que debemos añadir las siguientes referencias al paquete:

--Microsoft.SqlServer.ConnectionInfo

--Microsoft.SqlServer.Management.Sdk.Sfc

--Microsoft.SqlServer.Smo

Haciendo clic derecho sobre el nodo "References" de la ventana "Solution Explorer" elegiremos la opción "Add Reference", que abrirá un diálogo con el mismo nombre desde cuya pestaña ".NET" realizaremos la selección y agregación de componentes al paquete.

A continuación escribiremos el siguiente bloque de código, que creará una instancia de la clase Microsoft.SqlServer.Management.Smo.Server (que representa al servidor SQL), pasando como parámetros a su constructor una cadena con el nombre del servidor. Utilizando dicho objeto Server, recorreremos su colección Databases, visualizando el nombre de cada una mediante una ventana MessageBox.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
//....
public void Main()
{
    Server oServer = new Server("WINTERFELL-PC");


    foreach (Database oDatabase in oServer.Databases)
    {
        MessageBox.Show(oDatabase.Name);
    }


    Dts.TaskResult = (int)ScriptResults.Success;
}

Seguidamente cerraremos el editor de código y aceptaremos el diálogo "Script Task Editor", poniendo en ejecución el paquete mediante la opción de menú "Debug | Start debugging" o pulsando la tecla F5, que irá mostrando los mensajes correspondientes con los nombres de las bases de datos.

Finalizada la ejecución, haremos clic en el enlace "Package execution completed..." que aparece en la parte inferior del diseñador del paquete, o seleccionaremos la opción de menú "Debug | Stop Debugging" para salir de la ejecución desde el depurador.

No obstante, a pesar de resultar factible, no consideramos muy adecuado el uso de MessageBox para comunicar información al usuario acerca de la ejecución del paquete, ya que estimamos que en un alto porcentaje de las ocasiones, un paquete de estas características se desarrolla para su ejecución desatendida a través de una tarea (Job) del Agente de SQL Server.

Si bien con el depurador podemos obtener toda la información necesaria en tiempo de ejecución, su uso se limita a Visual Studio, por lo que solamente puede ser utilizado durante la fase de desarrollo del paquete. Debemos, por lo tanto, encontrar un medio alternativo para poder informar al usuario de lo que está sucediendo durante la ejecución.

La solución a este problema la encontramos en el método Dts.Events.FireInformation, que hace que el paquete envíe un evento informativo al exterior. Sustituyendo la llamada a MessageBox.Show por este otro método, nuestro código quedará como vemos a continuación.

//....
bool bOtraVez = false;
foreach (Database oDatabase in oServer.Databases)
{
    Dts.Events.FireInformation(0, string.Empty, "Procesando BBDD: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);
}
//....

De esta forma, al ejecutar el paquete podremos realizar un seguimiento en la ejecución del mismo desde la pestaña "Execution Results" ("Progress" en tiempo de ejecución) del diseñador, y también desde la ventana "Output" del depurador, donde veremos los mensajes generados mediante el método FireInformation.

Ejecutando el paquete fuera del entorno de SSBIDS

Pero comprobemos, ejecutando el paquete de forma independiente, que estos mensajes de notificación también funcionan fuera del entorno de SSBIDS. Supongamos que el archivo PckEstructurasBBDD.dtsx del proyecto de integración que estamos desarrollando se encuentra en la ruta C:\pruebasSSIS\EstructurasBBDD\EstructurasBBDD; lo primero que tenemos que hacer es iniciar SQL Server Management Studio (SSMS), conectando con el servicio "Integration Services" como vemos en la siguiente imagen.

A continuación desplegaremos los nodos "Stored Packages | File System", haremos clic derecho en este último, y seleccionaremos la opción "Import Package", abriéndose una ventana con el mismo nombre. En esta ventana desplegaremos la lista "Package location", eligiendo "File System", mientras que en el campo "Package path" introduciremos la ruta y el nombre del archivo .dtsx perteneciente al paquete que estamos programando. El nombre del paquete (campo Package name) se rellena por defecto con el mismo del archivo .dtsx, aunque podemos utilizar otro distinto.

Una vez completados los campos de esta ventana haremos clic en "OK", siendo el paquete agregado al servidor de Integration Services de SQL Server.

Ejecutaremos el paquete haciendo clic derecho en el mismo y seleccionando la opción "Run Package", que abrirá la ventana "Execute Package Utility", en la que haremos clic en el botón "Execute".

Esta ventana utiliza internamente el ejecutable DTExec.exe, que también podemos usar desde una ventana "Símbolo del sistema", como explicaremos en la segunda parte del artículo.

Durante la ejecución del paquete, la ventana "Package Execution Progress" mostrará una serie de mensajes informativos acerca de su progreso, incluyendo aquellos que enviamos desde el código fuente utilizando el método FireInformation anteriormente explicado.

Llegados a este punto hacemos una pequeña parada en el camino y concluimos esta primera parte del artículo. En la segunda entrega abordaremos la creación de los archivos de script que contendrán las sentencias para crear los objetos de nuestras bases de datos. Mientras tanto, el lector que quiera experimentar con el proyecto de integración desarrollado a lo largo del artículo, puede descargarlo aquí.

Saludos.

Pirámides de población con PowerPivot. Creación del gráfico (y 2)

2011-08-21T17:58:00Z

En la primera parte de este artículo desarrollábamos un modelo de datos en PowerPivot que representaba las cifras de una población por edad y sexo. En esta segunda entrega plasmaremos dichas cifras en un gráfico con forma de pirámide de población.

Gráfico de pirámide. Primer acercamiento

En su estado actual, la tabla dinámica ya tendría la información suficiente (cifras de población, edad y sexo) como para intentar crear un gráfico que represente una pirámide de población; aunque adelantamos al lector que en esta primera aproximación no vamos a conseguir el efecto deseado.

Posicionados en la tabla dinámica, dentro de la cinta de opciones de Excel seleccionaremos la opción "Gráfico dinámico", perteneciente al grupo "Herramientas" de la ficha "Opciones", que a su vez está contenida en la ficha de nivel superior "Herramientas de tabla dinámica".

Esta selección abrirá la ventana "Insertar gráfico", que contiene el conjunto de tipos de gráfico disponibles. Aquí nos percataremos de que no existe una plantilla específica para crear un gráfico de pirámide; por lo tanto, entre toda la oferta a nuestra disposición elegiremos, dentro de la categoría "Barra", el tipo "Barra agrupada", que como veremos más adelante será el que mejor se adaptará al resultado que queremos conseguir.

Aceptando esta ventana, el gráfico será creado a partir de los datos de la tabla dinámica, y tal y como habíamos anticipado, el resultado no se parecerá a la imagen presentada al principio del artículo.

No obstante, la principal diferencia radica en el dibujo de la barra correspondiente a la población de hombres, cuyo sentido debería ser hacia la izquierda; el resto de aspectos son básicamente cuestiones de configuración visual, que posteriormente explicaremos cómo resolver.

Solucionando la trayectoria de las barras de población

Centrándonos en la barra de población masculina, la solución para conseguir que se dibuje en dirección opuesta a la que actualmente tiene, consiste en poner en negativo los valores de las celdas de la tabla dinámica correspondientes a este segmento de la población.

Si nos encontráramos en una hoja de cálculo simple, sin conexión a PowerPivot, la solución sería tan sencilla como editar las celdas de la columna Hombre, pasando sus valores a negativo, pero estamos en un escenario de trabajo en el que los datos están siendo obtenidos desde el modelo de datos de PowerPivot, por lo que no es posible editar directamente los valores de la tabla dinámica.

Para solucionar esta clase de problemas tendremos que recurrir a la creación de columnas y/o medidas calculadas, que a través de expresiones DAX proporcionen los resultados que necesitamos. En el caso del paso a negativo de los valores de la columna Hombre, abriremos la ventana de PowerPivot, y situándonos en la primera columna vacía disponible en la tabla Población, escribiremos la siguiente expresión en la barra de fórmulas:

=IF([Sexo_ID] = "M", 1, -1)

Acabamos de crear una columna calculada que se evaluará para cada fila de la tabla Población, comprobando si el valor del campo Sexo_ID es igual a la letra "M", en caso afirmativo, el valor de la columna en dicha fila será 1, en caso contrario (cuando el campo contenga "H") el valor devuelto será -1.

Seguidamente haremos doble clic sobre su cabecera para asignarle el nombre Sexo_Codigo. También podemos darle nombre haciendo clic derecho sobre la cabecera y eligiendo la opción "Cambiar nombre de columna".

Volviendo nuevamente a la ventana de Excel, eliminaremos el gráfico que habíamos creado en la hoja y desmarcaremos la medida RecuentoPoblacion, quedando vacía la zona de valores de la tabla dinámica.

El próximo paso a dar consistirá en crear una nueva medida con el nombre SumaPoblacion, basada en la siguiente expresión DAX:

=SUM( [Sexo_Codigo] )

Al aplicar esta medida a la tabla dinámica, la función SUM realiza la suma de los valores de la columna pasada como parámetro, por lo que las cifras de la población de hombres ya aparecerán en negativo. Esto significa que al volver a crear el gráfico en el modo antes explicado, las barras indicadoras de los valores de población por sexo se dibujarán ahora en direcciones opuestas. Como detalle adicional, en la ficha "Herramientas de tabla dinámica", dentro de la ficha "Diseño", en el grupo "Diseño" desplegaremos la opción "Totales generales", seleccionando el valor "Activado sólo para columnas", que ocultará la columna de totales de fila, ya que su presencia en este contexto resulta irrelevante.

Configuración visual de las barras de población

Aunque las barras del gráfico ya se muestran según el efecto que nos habíamos propuesto como objetivo, sería deseable que su aspecto visual recibiera algunos retoques para mejorar la calidad de su presentación.

En primer lugar haremos clic derecho sobre las etiquetas de los rangos de edad, seleccionando la opción "Dar formato a eje". En la ventana del mismo nombre, dentro del apartado "Opciones del eje", asignaremos el valor "Bajo" a la propiedad "Etiquetas del eje", lo que tendrá como resultado que esta columna de etiquetas quede alineada a la izquierda del gráfico.

A continuación haremos clic derecho sobre cualquiera de las barras del gráfico, eligiendo la opción "Dar formato a serie de datos". En la ventana de configuración de la serie, dentro del apartado "Opciones de la serie", en la propiedad "Superposición de series", desplazaremos el marcador de posición hasta el extremo derecho (totalmente superpuesta), mientras que en la propiedad "Ancho del intervalo" desplazaremos el marcador de posición hasta el extremo izquierdo (sin intervalo en absoluto). De esta forma conseguiremos que las barras aumenten su grosor y eliminen el espacio intermedio entre las mismas, quedando completamente unidas para formar la pirámide de población.

Calculando la población en porcentajes

La representación de los datos obtenida hasta este momento, tanto en la tabla dinámica como en el gráfico de pirámide, se basa en cifras absolutas de población. Sin embargo, lo habitual y recomendable es que dicha representación se realice como proporción de cada grupo de edad y sexo sobre el total de la población.

Por ejemplo, en nuestra tabla dinámica, la población de mujeres con edades comprendidas entre los 55 y 59 años es de 184.888 personas; para obtener el porcentaje que este grupo de población constituye en relación al total de individuos con el que estamos trabajando (6.458.684), dividiremos el grupo entre el total, y formatearemos el resultado como porcentaje, obteniendo 2,86 %.

Si queremos que la tabla dinámica realice esta operación para todos los grupos de población tendremos que añadir cálculos adicionales en forma de medidas, pero antes de eso eliminaremos el actual gráfico de población, ya que volveremos a construirlo a partir de una de las nuevas medidas.

Partimos de la existencia de una medida, SumaPoblacion, que como ya sabemos, devuelve la cifra de población sumando el campo Sexo_Codigo. El siguiente paso consistirá en crear una nueva medida, que al incluirse en la tabla dinámica, proporcione el total de población en todas las celdas.

Nuestra primera reacción podría ser volver a utilizar la medida RecuentoPoblacion, creada en las fases iniciales de nuestro ejemplo, pero pronto nos daremos cuenta de que no sirve para este propósito, ya que aunque esta medida cuenta las filas de la tabla Población, los resultados de las celdas se ven afectados por los campos utilizados en filas y columnas, así como otros filtros que pudiera tener activos la tabla dinámica.

Para que una medida cuente siempre todas las filas de una tabla, sin importar los filtros que pueda haber activos, utilizaremos la función CALCULATE, a la que pasaremos como primer parámetro la operación a realizar, en este caso el recuento de filas de la tabla Población mediante la función COUNTROWS. A continuación pasaremos tantos parámetros como filtros queramos eliminar, utilizando la función ALL(NombreTabla) por cada tabla que de alguna manera esté actuando como filtro.

Bajo tales premisas crearemos una nueva medida llamada TotalGlobalPoblacion, con la siguiente expresión DAX:

=CALCULATE( COUNTROWS( Poblacion ), ALL( Edad), ALL( Sexo ) )

Al aplicar sobre la tabla dinámica esta medida, todas sus celdas mostrarán el mismo valor: el total de la población.

Ahora necesitamos una tercera medida que haga la división entre las dos anteriores y muestre el resultado en formato porcentaje. Esta nueva medida tendrá el "original" nombre de PorcentajePoblacion y utilizara la siguiente fórmula:

=[SumaPoblacion] / [TotalGlobalPoblacion]

Como ya vimos en la primera entrega del artículo, para aplicar el formato a esta medida haremos clic derecho en una de sus celdas eligiendo la opción "Formato de número". En la ventana de formato seleccionaremos esta vez la categoría "Personalizada", a través de la cual introduciremos en el campo "Tipo" la siguiente cadena de formato propia.

0,00%;0,00%

Esta cadena, además de formatear el número como porcentaje, mostrará sin el signo negativo los valores de la columna de población masculina, aunque internamente, dichos valores seguirán siendo negativos.

A continuación desactivaremos todas las medidas de la tabla dinámica a excepción de PorcentajePoblación, que será la única que permanecerá visible. Seguidamente volveremos a añadir un gráfico de barras agrupadas, en el que además de utilizar los pasos de configuración explicados con anterioridad, agregaremos nuevas características de formato para mejorar su presentación.

Aplicar formato de porcentaje al eje horizontal

En primer lugar haremos clic derecho en el eje horizontal de etiquetas seleccionando la opción "Dar formato a eje", que abrirá la ventana de formato, en cuyo apartado "Número" seleccionaremos la categoría de formato "Personalizado" y en el campo "Código de formato" escribiremos la siguiente cadena de formato:

0%;0%

Haciendo clic en el botón "Agregar", la cadena se añadirá a la lista de cadenas personalizadas, de modo que al aceptar la ventana se aplicará el formato al eje horizontal de etiquetas.

Remarcando los bordes de las barras de población

Seguidamente haremos clic sobre alguna de las barras del gráfico, volviendo a elegir la opción "Dar formato a serie de datos". En esta ocasión, dentro del apartado "Color del borde" haremos clic en la opción "Línea sólida", seleccionando el color negro; mientras que en el apartado "Estilos del borde" asignaremos a la propiedad "Ancho" el valor "2 pto". Esta operación la realizaremos para ambos grupos de barras del gráfico.

Después haremos clic derecho sobre las etiquetas de los rangos de edad, seleccionando la opción "Dar formato a eje". En esta ventana de formato asignaremos los mismos valores para las propiedades de color y estilo de borde que acabamos de utilizar para las barras del gráfico.

Como resultado de estas acciones, el gráfico mostrará sus bordes con un contorno claramente remarcado.

Reubicando la posición de la leyenda

En el momento de crear el gráfico, Excel sitúa por defecto la leyenda (campo Sexo_DS) en el lado derecho. No obstante, es posible cambiar la ubicación de este elemento si queremos proporcionar más espacio al dibujo de las barras de población. Para ello, haremos clic derecho en la leyenda y seleccionaremos la opción "Formato de leyenda", en la ventana de formato, dentro del apartado "Opciones de leyenda", haremos clic en la opción "Superior".

Como podemos apreciar, el gráfico ha ganado en superficie de dibujo, pero los indicadores de la leyenda han quedado colocados en orden inverso a las barras. Para solucionar este problema haremos clic en el botón de campos de la leyenda (Sexo_DS), de forma que despleguemos sus opciones de filtro, donde seleccionaremos "Ordenar de Z a A".

Con esta acción, los indicadores de leyenda quedarán colocados adecuadamente, pero ahora nos encontraremos con el inconveniente de que los colores de las barras han quedado invertidos, y hemos perdido el borde de las barras del gráfico.

Restauraremos los bordes de las barras en la manera explicada anteriormente, mientras que en cuanto a los colores, para cada lado de la pirámide haremos clic derecho en una de las barras, y en la opción "Relleno de forma", cambiaremos el color actual por el que originalmente tenía el gráfico.

Para completar los ajustes que estamos realizando sobre la leyenda, arrastraremos ésta hasta que quede situada al nivel del elemento superior de la pirámide, y aumentaremos su anchura, para que los indicadores queden más separados.

Despejar el área de campos y agregar título

Seguidamente haremos clic derecho en cualquiera de los botones de campos del gráfico y seleccionaremos la opción "Ocultar todos los botones de campos en gráfico". Con este movimiento impediremos que el usuario aplique filtros sobre el eje horizontal y/o vertical de la pirámide, manteniendo su estructura consistente y evitando así la posibilidad, por ejemplo, de ocultar rangos de edad o alguno de los sexos. No obstante, seguirá existiendo tal posibilidad de filtro desde la tabla dinámica.

Por otra parte, en el grupo de fichas "Herramientas del gráfico dinámico" haremos clic en la ficha "Presentación", y dentro del grupo "Etiquetas" haremos clic en la opción "Título del gráfico", que desplegará una serie de elementos entre los que elegiremos "Encima del gráfico", añadiéndose un cuadro de texto al gráfico, que editaremos para asignarle un título. Llegados a este punto habremos completado el desarrollo de nuestra pirámide de población.

Dinamizando los datos de la pirámide mediante segmentaciones

Aunque hemos logrado el objetivo propuesto de crear una pirámide de población, resultaría interesante dotarla de cierto valor añadido, tal y como vamos a hacer en este apartado.

Si observamos el modelo de datos en la ventana de PowerPivot, caeremos en la cuenta de que todavía no hemos hecho uso de la información que sobre zonificación sanitaria existe en el mismo, por lo que podemos aprovechar estos datos para construir un filtro que muestre la pirámide en base a la población de una o varias de estas zonas sanitarias.

Las tablas dinámicas de PowerPivot, además del filtro tradicional, incorporan un nuevo tipo de filtro denominado "segmentación", que además de la funcionalidad habitual de filtrado ofrece una interfaz de usuario más flexible para la manipulación de los valores a tratar.

Vamos a crear en nuestra pirámide una segmentación basada en la información de zona. Para ello, en el panel de la lista de campos, arrastraremos el campo Zona_DS de la tabla Zona hasta el bloque "Segmentaciones de datos horizontales", obteniendo como resultado una segmentación situada encima de la tabla dinámica y el gráfico de pirámide.

Para filtrar los datos mediante la segmentación simplemente tenemos que seleccionar el nombre de la zona sanitaria que queramos emplear como filtro. También es posible filtrar simultáneamente varias zonas manteniendo pulsada la tecla Ctrl, mientras vamos haciendo clic en las zonas a filtrar (como muestra la siguiente figura). Si queremos eliminar todos los filtros activos haremos clic en el icono situado a tal efecto en la parte superior derecha de la segmentación.

Y llegados a este punto damos por finalizado el artículo, en el que a lo largo de sus dos entregas hemos mostrado el modo de construir una pirámide de población en Excel 2010, utilizando PowerPivot como herramienta de gestión de los datos poblacionales. No obstante, la potencia de esta tecnología va más allá del mero tratamiento de la información demográfica, abarcando su campo de acción a cualquier entorno en el que tengamos que realizar un análisis con elevados volúmenes de datos.

Pirámides de población con PowerPivot. Preparación del entorno de datos (1)

2011-08-18T19:13:00Z

Una pirámide poblacional es una herramienta que nos permite analizar el estado y evolución de una población en función de su edad y sexo. Se trata de un elemento característico en demografía y estadística, aunque sus aplicaciones también abarcan campos tales como el sanitario, educativo, comercial, etc. Es por ello, que su integración en un sistema de información perteneciente a alguna de las áreas recién mencionadas, supone un enriquecimiento sustancial en la calidad de los resultados obtenidos por los usuarios de tales sistemas.

En el presente artículo abordaremos la construcción de pirámides de población utilizando PowerPivot, un complemento para Excel 2010, que permite el acceso a orígenes de datos de gran volumen, así como su posterior manejo y análisis.

Como ya apuntábamos en el artículo Generación de datos demográficos desde SQL Server, publicado en este mismo blog, nuestro objetivo actual (utilizando la base de datos PiramidePoblacion creada en el mencionado artículo) consistirá en construir un gráfico que represente una pirámide de población similar a la que vemos en la siguiente figura.

La primera parte del artículo estará dedicada a la preparación del modelo de datos de PowerPivot: conexión contra un origen de datos, carga y creación de una tabla dinámica para analizar las cifras de población. La segunda parte la destinaremos a la construcción del gráfico que represente a la pirámide de población, partiendo de los datos sobre los que hemos trabajado en la primera entrega.

Al igual que en el artículo mencionado anteriormente, quiero agradecer nuevamente a los integrantes del Servicio de Informes de Salud y Estudios (Subdirección de Promoción de la Salud y Prevención, DG Atención Primaria, Consejería Sanidad CM): Jenaro Astray Mochales, María Felicitas Domínguez Berjón, María Dolores Esteban Vasallo, Beatriz Elvira Rodríguez y en especial a Ricard Gènova Maleras, el soporte y orientación recibido sobre los conceptos demográficos necesarios con los que poder desarrollar apropiadamente una pirámide de población utilizando Excel 2010 en combinación con PowerPivot. Además de todo esto, Ricard ha accedido muy gustosamente a realizar una estupenda labor de revisión de este artículo, con lo que el agradecimiento va por partida doble.

Igualmente quisiera expresar mi agradecimiento a Enrique Barceló por todos sus estupendos trucos sobre desarrollo OLAP, que comparte con el equipo de trabajo, y que hacen un poco más fácil nuestra labor de desarrollo en estas tareas del Business Intelligence.

Un poco de teoría

Pero antes de entrar de lleno en su proceso de creación, vamos a proporcionar unos breves apuntes teóricos sobre las pirámides de población, que nos permitan entender mejor cuáles son sus principales características y la información que de ellas podemos obtener y analizar.

En esencia, una pirámide de población es un doble histograma que representa la distribución por edad y sexo de los efectivos de una población, bien en cifras absolutas, bien como porcentaje sobre la población total.

Si estamos interesados en obtener una información más detallada acerca de los aspectos conceptuales que rodean a las pirámides de población, la ayuda del programa Epidat 4.0 puede resultarnos de utilidad. Se trata de una aplicación desarrollada por la Consejería de Sanidad de la Xunta de Galicia y la Organización Panamericana de la Salud (OPS, Washington), de cuya documentación citamos a continuación algunos de los puntos más importantes en relación con el tema que nos ocupa. En primer lugar comenzaremos con algunas nociones relativas a la construcción de la pirámide de población.

"El mejor modo de visualizar la distribución por sexo y edad de una población es, sin duda, la pirámide de población, verdadero icono de la demografía. Una pirámide es un doble histograma que permite, con un solo golpe de vista, hacerse una idea clara no sólo acerca de las características generales de la población (joven, envejecida, con algún desequilibrio en función del sexo) sino también sobre particularidades específicas que remiten a algún acontecimiento concreto concerniente a la población objeto de estudio.

Usualmente las pirámides de población se construyen siguiendo varias convenciones y reglas:

- representar a los hombres a la izquierda y a las mujeres a la derecha del eje central,

- ubicar las edades de manera que cuanto más bajas sean, más cerca estén de la base y viceversa,

- mantener cierta proporcionalidad entre base y altura (3 anchuras por 2 alturas, o 4 por 3, aproximadamente),

- respetar la misma escala a ambos lados del eje central (para favorecer la comparación entre sexos),

- representar el peso de cada intervalo etario por la superficie de cada barra del histograma, no por su longitud, algo especialmente importante cuando se trabaja con grupos de edad desiguales,

- pueden construirse con valores absolutos, pero es preferible hacerlo con las proporciones de cada edad y sexo respecto de la población total.

El último punto es importante y diferencia a la pirámide de los indicadores de estructura [proporción de jóvenes o ancianos, índice de envejecimiento, etc.], que se calculan separadamente sobre el total de cada sexo respectivo. En el caso de la pirámide de población, usar como denominador de las proporciones a la población total de ambos sexos reunidos garantiza la comparabilidad no sólo de la distribución por edad, sino también por sexo".

Y finalizaremos con aquellas cuestiones relacionadas con la interpretación de la pirámide.

"Una pirámide por sexo y edad resume la historia demográfica de una población de por lo menos los cien años anteriores a la fecha de referencia (el tiempo que tarda, aproximadamente, una generación en pasar de la base a la cima de la pirámide). En ocasiones, el efecto indirecto de algunos acontecimientos demográficos es visible más allá de ese salto de un siglo (por ejemplo, el impacto del profundo descenso de la fecundidad de las últimas décadas experimentado por muchas poblaciones se apreciará, sin duda, en las pirámides de la primera mitad del siglo XXII). Una pirámide por edades simples [edad por edad: 0, 1, 2, 3 ... 99, 100 ...] permite un análisis más preciso que otra realizada por grupos de edades agregadas (quinquenales, decenales), pero corre también el riesgo de verse afectada por problemas de calidad de los registros, o ser vulnerable a la inestabilidad de las distribuciones en poblaciones pequeñas.

La primera percepción de una pirámide permite identificar los rasgos generales de la población representada: una pirámide de base ancha y que se estrecha rápidamente da idea de una población joven, con una alta proporción de niños y adolescentes, y baja de adultos y ancianos, resultado de una alta natalidad y mortalidad. Inversamente, una pirámide con perfil estrecho en la base y ancho en el centro y la cima representa una estructura madura o envejecida. Cuanto mayor es la esperanza de vida de una población, mayor suele ser la desigualdad por sexo en la cima de la pirámide (más llena por el lado de las mujeres, por su mayor nivel de supervivencia).

La pirámide no da respuestas por sí misma, sino que propicia que se planteen preguntas pertinentes. Las explicaciones para su perfil deben buscarse en la historia, en los avatares sociales, políticos, económicos, que generan consecuencias en el devenir demográfico -es decir, en la fecundidad, la mortalidad o la migración, que son los fenómenos que modelan el contorno y fijan el tamaño de una población- y que tienen una interpretación en función de la triple perspectiva temporal: edad, periodo y cohorte".

Incorporando los datos a PowerPivot

Gracias a VertiPaq, el motor de procesamiento de datos de PowerPivot, podemos conseguir una potencia y velocidad sin precedentes en la manipulación, filtrado, creación de expresiones de lógica de negocio, y en definitiva, en todas aquellas operaciones analíticas que debamos realizar sobre un modelo de datos creado con esta herramienta.

El siguiente enlace proporciona acceso a la página principal de PowerPivot, desde donde podremos descargarlo, para posteriormente instalarlo en nuestra máquina, de forma que podamos seguir los ejemplos del artículo.

Una vez que hayamos instalado PowerPivot iniciaremos Excel 2010, creándose una nueva hoja de cálculo a la que daremos el nombre PiramidePoblacion.xlsx. En la cinta de opciones dispondremos ahora de una nueva ficha con el nombre "PowerPivot". Al hacer clic en la misma seleccionaremos la opción "Ventana de PowerPivot", perteneciente al grupo "Iniciar", lo que abrirá la mencionada ventana de trabajo de PowerPivot, en la que definiremos la estructura de tablas que vamos a utilizar, o "modelo de datos", tal y cómo se denomina dentro del contexto de PowerPivot.

A continuación, en la pestaña "Página principal" de la ventana de PowerPivot, nos conectaremos a la base de datos mediante la opción "Desde base de datos | De SQL Server", situada en el grupo "Obtener datos externos".

Esta acción iniciará el asistente para importar tablas, en el que indicaremos el origen de datos al que nos queremos conectar.

Una fuente de datos válida para elaborar una pirámide de población ha de tener como mínimo información acerca del sexo y edad de la población, como es el caso de nuestra base de datos PiramidePoblacion. Adicionalmente, y como forma de enriquecer el análisis a realizar, la base de datos puede disponer de información complementaria como pudiera ser la zonificación sanitaria, nacionalidad de la población, etc.

Tras elegir la base de datos PiramidePoblacion, el siguiente paso nos ofrecerá una lista de las tablas y vistas que podremos importar, donde marcaremos las siguientes: Edad, Población, Sexo y Zona, comenzando la importación al hacer clic en el botón "Finalizar".

Si la importación se desarrolla correctamente, el asistente mostrara una ventana resumen del proceso.

Una vez terminada la incorporación de datos haremos clic en "Cerrar" para volver a la ventana de PowerPivot, donde hallaremos organizadas en diversas pestañas cada una de las tablas importadas.

Análisis mediante tabla dinámica

Antes de pasar a la fase de construcción de la pirámide de población, vamos a utilizar una tabla dinámica de PowerPivot para analizar las cifras del modelo de datos, por lo que situados en su ventana de trabajo haremos clic en la opción "PivotTable", perteneciente al grupo "Informes".

Esta acción nos posicionará en la ventana de Excel, donde se abrirá un diálogo en el que elegiremos la hoja de cálculo en la que se ubicará la tabla dinámica.

En nuestro caso seleccionaremos la primera opción y aceptaremos el diálogo, creándose la tabla dinámica en una nueva hoja cálculo

Desde el panel "Lista de campos de PowerPivot" tenemos a nuestra disposición los campos de las tablas del modelo de datos, que situaremos en las diferentes zonas de la tabla dinámica (etiquetas de filas, columnas, valores, etc.) para llevar a cabo nuestras operaciones de análisis. Utilizaremos para ello un estilo de trabajo muy semejante al que emplearíamos si estuviéramos consultando un cubo OLAP, ya que el manejo de los campos de PowerPivot en este sentido es similar al que podemos realizar con las dimensiones y medidas de un cubo de datos cuando es consultado desde Excel.

Vamos a comenzar por una consulta sencilla, consistente en contar los registros de la tabla Población, agrupando la información por rangos de edad, los cuales situaremos en las filas de la tabla dinámica.

El recuento de registros lo realizaremos mediante una expresión DAX (el lenguaje de consultas de PowerPivot) situada en una medida calculada, que crearemos seleccionando la opción "Nueva medida", perteneciente al grupo "Medidas" de la ficha "PowerPivot". Esta medida la aplicaremos sobre la tabla Población, por lo que deberemos estar previamente posicionados en la misma, dentro del panel de la lista de campos.

Al seleccionar esta opción se abrirá la ventana "Configuración de medida", en la que dentro del cuadro de texto reservado para la fórmula escribiremos la siguiente expresión:

=COUNTROWS(Poblacion)

La función COUNTROWS, como su nombre indica, cuenta las filas de la tabla pasada como parámetro. Para terminar la creación de nuestra medida le daremos el nombre "RecuentoPoblacion" y haremos clic en "Aceptar".

Nada más terminar de crear la medida, ésta se añadirá automáticamente a la lista de campos de la tabla Población y al bloque "Valores", visualizándose en la tabla dinámica el número total de filas de la tabla.

A continuación seleccionaremos, en el panel de la lista de campos, el campo Edad_Grupo de la tabla Edad, lo que situará automáticamente al mencionado campo en el bloque "Etiquetas de fila". Si este no fuera el comportamiento obtenido, arrastraremos manualmente el campo hasta colocarlo en dicho bloque.

Como consecuencia de la acción anterior la tabla dinámica se verá actualizada, mostrándose en el eje de las filas los valores del campo Edad_Grupo. Respecto a las cifras de la medida RecuentoPoblacion, éstas deberían repartirse entre los tramos de cada edad, para así reflejar el número de registros (población) correspondiente a cada uno de dichos tramos. Sin embargo, como vemos en la siguiente figura, es algo que no está ocurriendo, ya que la medida muestra el mismo valor para todas las filas, lo cual es incorrecto.

Relaciones entre las tablas del modelo

Observando el panel de campos vemos que PowerPivot ya se ha percatado del problema que acabamos de comentar, porque en la parte superior de dicho panel aparece un aviso, que nos informa acerca de que puede ser necesaria una relación entre las tablas que se están utilizando para componer la consulta de la tabla dinámica.

Este problema no habría tenido lugar si hubiesen existido las oportunas relaciones entre las tablas de la base de datos, aunque como ya dijimos en el artículo sobre la creación de la base de datos de población, dichas relaciones no fueron creadas intencionadamente, para así demostrar que también es posible hacerlo desde PowerPivot, como veremos a continuación.

Para dejar que PowerPivot detecte automáticamente la relación que necesita, haremos clic en el botón "Crear" que aparece junto al aviso mostrado en el panel de campos. Como resultado se abrirá una ventana encargada de crear la oportuna relación, ofreciéndonos información adicional sobre la misma a través de los enlaces "Detalles".

Una vez creada la relación, ésta será aplicada inmediatamente, sin intervención del usuario, sobre la tabla dinámica, que de esta forma ya mostrará correctamente los valores de la medida agrupados por edad.

También es posible crear manualmente las relaciones entre las tablas del modelo de datos utilizando la ventana de trabajo de PowerPivot. Para ello haremos clic en la opción "Crear relación", perteneciente al grupo "Relaciones", que está situado en la ficha "Diseño" de la mencionada ventana.

Esta opción abrirá una ventana con el mismo nombre, en la que seleccionaremos la tabla y columna que representarán el origen y destino de la relación.

De esta forma estableceremos dos nuevas relaciones, que tendrán a la tabla Población como origen y a las tablas Sexo y Zona como destino. Adicionalmente, desde la opción "Administrar relaciones" podemos ver un resumen de las relaciones creadas así como gestionarlas (crear, editar, eliminar, etc.).

Estos cambios que acabamos de realizar en la ventana de PowerPivot pueden afectar directa o indirectamente a los datos que estamos presentando en la tabla dinámica; por dicho motivo, al retornar a la ventana de Excel veremos un aviso al respecto en el panel de campos, donde haremos clic en el botón "Actualizar", para refrescar los datos con los que estamos trabajando.

Agregando datos en el eje de columnas

Solucionado el problema de las relaciones, ahora es el momento de añadir nuevos datos en las columnas de la tabla dinámica mediante el campo Sexo_DS de la tabla Sexo.

Al ser seleccionado, este campo se situará por defecto en el bloque "Etiquetas de fila", de modo que tendremos que moverlo manualmente hasta el bloque "Etiquetas de columna"; o bien al hacer clic en él lo arrastraremos directamente hasta el bloque de etiquetas de columna.

Adicionalmente, daremos formato a las celdas numéricas haciendo clic derecho en cualquiera de ellas y seleccionando la opción "Formato de número", en el cuadro de diálogo de formato definiremos éste sin decimales y con separador de miles.

Una vez realizadas estas operaciones, la tabla dinámica ya mostrará los datos de población de manera acorde a los requerimientos planteados.

Llegados a este punto concluimos la primera parte del artículo. En la siguiente entrega alcanzaremos nuestro objetivo de crear un gráfico que represente la pirámide de población utilizando los datos que hemos preparado con PowerPivot.

Generación de datos demográficos desde SQL Server

2011-07-18T20:25:00Z

Con motivo de un próximo artículo que dedicaremos a la elaboración de pirámides de población desde PowerPivot, nos hemos encontrado recientemente ante la tesitura de desarrollar un proceso para crear una base de datos con información demográfica, que sirva como punto de partida para la construcción del modelo de datos en PowerPivot.

A diferencia del artículo Generar datos de prueba para SQL Server desde Excel, publicado anteriormente en este mismo blog, donde utilizábamos Excel para generar datos ficticios, también de población, en esta ocasión trabajaremos con datos reales, los cuales representan un mayor volumen de información a manejar con respecto al mencionado artículo.

El objetivo del presente artículo consistirá en crear una base de datos que contenga una tabla de población, en la que cada registro represente a un individuo, con su correspondiente edad, sexo y zona de atención sanitaria.

Antes de proseguir, quisiera expresar mi agradecimiento a Ricard Gènova Maleras, demógrafo especializado en análisis poblacionales sanitarios, perteneciente al Servicio de Informes de Salud y Estudios (Subdirección de Promoción de la Salud y Prevención, DG Atención Primaria, Consejería Sanidad CM), por su valioso asesoramiento en aquellos aspectos demográficos esenciales para la elaboración de éste y el próximo artículo sobre pirámides de población, así como al resto de integrantes del mencionado Servicio: Jenaro Astray Mochales, María Felicitas Domínguez Berjón, María Dolores Esteban Vasallo y Beatriz Elvira Rodríguez, por su apoyo y ayuda en el resto de conceptos sanitarios relacionados con poblaciones, necesarios para el adecuado enfoque de este artículo.

Por último, aunque no menos importante, agradezco a Enrique Barceló, compañero de fatigas en estas labores del Business Intelligence, los interesantes ratos que pasamos departiendo acerca de los aspectos relacionados con el desarrollo de sistemas de información en general, y sobre cubos de datos en particular, así como por compartir conmigo sus asombrosos conocimientos sobre OLAP.

Obtención y preparación de los datos

Existe un importante número de sitios Web, pertenecientes a diversos organismos, donde encontraremos la información demográfica necesaria para realizar nuestros análisis, organizada por criterios tales como sexo, edad, región geográfica, etc. De todos estos organismos destacaremos los correspondientes a Naciones Unidas, Instituto Nacional de Estadística (España), Instituto de Estadística de la CM (Madrid) y CELADE (datos demográficos de América Latina y Caribe). En todos ellos, además de la consulta directa en la propia web, disponemos de la posibilidad de descargar la información en diversos formatos tales como Excel, CSV, etc.

Para el desarrollo de los ejemplos de este artículo utilizaremos los datos del padrón de población de la Comunidad de Madrid correspondientes al año 2010, clasificados por edad, sexo y la nueva zonificación sanitaria, vigente a partir de dicho año, que se encuentran en el siguiente enlace (T10Z2_SALUD10) de la página web del Instituto de Estadística de la CM.

Estrategia para la extracción de datos

El archivo Excel pc10t10z2_salud10.xls, obtenido del anterior enlace, organiza los datos de población alrededor de tres hojas de cálculo que contienen, respectivamente, los valores de población totales, por hombres y mujeres.

Cada hoja de cálculo tiene en sus dos primeras columnas el código y nombre de la zona de atención sanitaria, mientras que el resto de las columnas contienen las cifras de población clasificadas en agrupaciones quinquenales, donde para cada grupo existe una columna con el total quinquenal, y varias columnas que representan el desglose por edad simple de las edades que conforman el grupo.

Los datos que necesitamos extraer son, por un lado, los códigos y nombres de la zonificación sanitaria, para lo que podemos usar cualquiera de las hojas de cálculo del archivo Excel. Por otra parte, tendremos que obtener el total quinquenal de cada grupo de edad, tanto de la hoja de población masculina como femenina.

Todos estos valores los llevaremos a una nueva hoja de cálculo en la que copiaremos cada combinación de códigos de zona sanitaria, grupo de edad y tipo de población (hombre/mujer) siguiendo un orden de pasos parecido al de la siguiente figura.

A continuación observamos una aproximación de lo que sería la hoja de cálculo resultante.

El empleo manual de las operaciones seleccionar, copiar y pegar, para traspasar los datos que necesitamos a una nueva hoja puede resultar una tarea muy laboriosa y pesada. Es por ello que proponemos el uso de una macro que automatice toda esta labor, lo que nos permitirá repetir el proceso cuantas veces queramos, así como aplicarlo a otras poblaciones cuyos datos se encuentren estructurados de la misma forma.

Para crear la macro, en la cinta de opciones de Excel haremos clic en la opción "Macros", perteneciente al grupo del mismo nombre situado en la ficha "Vista".

Esta opción también está disponible en la ficha "Programador" dentro del grupo "Código"

En el caso de que la ficha "Programador" no esté visible haremos clic en la ficha "Archivo", y dentro de esta en "Opciones". En la ventana "Opciones de Excel" haremos clic en "Personalizar cinta de opciones", y desplegando la lista situada a la derecha seleccionaremos el elemento "Fichas principales". Del panel situado debajo de la lista marcaremos la casilla "Programador", lo que hará visible esta ficha en la cinta de opciones.

Independientemente del modo elegido para su creación, aparecerá la ventana "Macro", en la que daremos el nombre "TraspasarDatosPoblacion" a nuestra macro y haremos clic en "Crear", lo que nos llevará al editor de VBA (Visual Basic for Applications), donde escribiremos el código de la macro.

Vamos a dividir el trabajo que realizará la macro en tres partes: la creación de los datos para la columna de códigos de zona sanitaria; la columna de rangos de edad; y por último, las columnas de cantidades de población por sexo.

En el siguiente bloque de código podemos ver las instrucciones encargadas de crear una nueva hoja de cálculo, a la que asignaremos un nombre y los títulos de columna. Posteriormente seleccionaremos los códigos de zona de una de las hojas origen, que pegaremos en la nueva hoja tantas veces como rangos de edad existan.

Option Explicit

Private Const RANGOS_COLUMNAS As Integer = 20
 
Sub TraspasarDatosPoblacion()

' crear nueva hoja y asignarle nombre
Dim sHojaDestino As String
sHojaDestino = "DatosBasePoblacion"
 
Dim oWorksheet As Worksheet
Set oWorksheet = Sheets.Add(After:=Sheets(Sheets.Count))
oWorksheet.Name = sHojaDestino
 
' asignar títulos en columnas
Range("A1").Select
ActiveCell.FormulaR1C1 = "Zona"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Rango_Edad"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Poblacion_H"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Poblacion_M"
 
' volver a la hoja de datos origen
Sheets("Hombres").Select
 
' establecer fila inicial y final de datos
Dim sFilaInicial As String
Dim sFilaFinal As String
sFilaInicial = "14"
sFilaFinal = "299"
 
' calcular cantidad de zonas existentes
Dim nCantidadZonas As Integer
nCantidadZonas = (CInt(sFilaFinal) - CInt(sFilaInicial))
 
' códigos de zona
' ---------------
Dim sColumna As String
sColumna = "A"
 
' seleccionar códigos, copiarlos al portapapeles y pegarlos en nueva hoja
Range(sColumna & sFilaInicial & ":" & sColumna & sFilaFinal).Select
Selection.Copy
Sheets(sHojaDestino).Select
 
Dim nContador As Integer
Dim nFilaVacia As Long
 
' realizar el pegado por cada grupo de edad
For nContador = 0 To RANGOS_COLUMNAS
    ' obtener el siguiente número de fila vacío de la columna
    nFilaVacia = FilaVaciaEnColumna(1)
    
    ' posicionar en celda
    Range(sColumna & CStr(nFilaVacia)).Select
    
    ' pegar datos
    ActiveSheet.Paste
Next
 
' asignar fondo color blanco a columna
Columns("A:A").Select
Selection.Interior.Pattern = xlNone
Selection.Interior.TintAndShade = 0
Selection.Interior.PatternTintAndShade = 0

Antes de cada operación de asignación de valores en las celdas, necesitamos conocer la fila vacía a partir de la que pegaremos los datos, para lo que utilizaremos la función auxiliar "FilaVaciaEnColumna", que recorrerá la colección de filas de la hoja hasta encontrar la fila que contiene una celda vacía en aquella columna sobre la que estamos operando.

Function FilaVaciaEnColumna(ByVal nPosicionCelda As Integer) As Long
 
Dim vFila As Variant
Dim nFila As Long
 
For Each vFila In ActiveSheet.Rows
    nFila = vFila.Row

    If vFila.Cells(nPosicionCelda).Text = "" Then
        Exit For
    End If
Next
 
FilaVaciaEnColumna = nFila
 
End Function

La siguiente fase en la creación de la macro consistirá en asignar los valores a la columna de rangos de edad. Para ello crearemos un array conteniendo dichos valores, y al recorrerlo, haremos que en cada iteración se copie el valor del rango en curso el mismo número de veces que la cantidad de zonas sanitarias existentes. Cabe destacar que antes de asignar los valores a esta columna le aplicaremos el formato texto (propiedad Selection.NumberFormat), ya que en caso contrario, el formato predeterminado puede hacer que algunos valores de los rangos de edad sean interpretados como mes-año.

' rangos de edad
' --------------
Dim aRangosEdad(RANGOS_COLUMNAS) As String
aRangosEdad(0) = "0-4"
aRangosEdad(1) = "5-9"
aRangosEdad(2) = "10-14"
aRangosEdad(3) = "15-19"
aRangosEdad(4) = "20-24"
aRangosEdad(5) = "25-29"
aRangosEdad(6) = "30-34"
aRangosEdad(7) = "35-39"
aRangosEdad(8) = "40-44"
aRangosEdad(9) = "45-49"
aRangosEdad(10) = "50-54"
aRangosEdad(11) = "55-59"
aRangosEdad(12) = "60-64"
aRangosEdad(13) = "65-69"
aRangosEdad(14) = "70-74"
aRangosEdad(15) = "75-79"
aRangosEdad(16) = "80-84"
aRangosEdad(17) = "85-89"
aRangosEdad(18) = "90-94"
aRangosEdad(19) = "95-99"
aRangosEdad(20) = "100+"
 
' establecer el formato de la columna de edad a texto
sColumna = "B"
Columns(sColumna & ":" & sColumna).Select
Selection.NumberFormat = "@"
 
' repetir cada valor del array aRangosEdad
' tantas veces como número de zonas existen
Dim vRangoEdad As Variant
 
For Each vRangoEdad In aRangosEdad
    ' obtener el siguiente número de fila vacío de la columna
    nFilaVacia = FilaVaciaEnColumna(2)
 
    ' asignar el valor del rango de edad en las dos siguientes celdas hacia abajo
    Range(sColumna & CStr(nFilaVacia)).Select
    ActiveCell.FormulaR1C1 = vRangoEdad
     
    Range(sColumna & CStr(nFilaVacia + 1)).Select
    ActiveCell.FormulaR1C1 = vRangoEdad
    
    ' establecer el rango de celdas y rellenar con el valor de las anteriores celdas
    Range(sColumna & CStr(nFilaVacia) & ":" & sColumna & CStr(nFilaVacia + 1)).Select
    Selection.AutoFill Destination:=Range(sColumna & CStr(nFilaVacia) & ":" & sColumna & CStr(nFilaVacia + nCantidadZonas)), Type:=xlFillDefault
Next

Y terminaremos la macro con la asignación de las columnas dedicadas a las cifras de población por sexo, donde esta vez utilizaremos dos arrays combinados, conteniendo las cabeceras de columna que corresponden a los totales quinquenales de población, y el tipo de población. Al recorrer ambos de forma anidada, iremos extrayendo, para un tipo de población determinado, sus cifras de individuos, asignándolas a la hoja de cálculo destino.

' cifras de población por sexo
' ----------------------------
Dim aColumnasPoblacion(RANGOS_COLUMNAS) As String
aColumnasPoblacion(0) = "D"
aColumnasPoblacion(1) = "J"
aColumnasPoblacion(2) = "P"
aColumnasPoblacion(3) = "V"
aColumnasPoblacion(4) = "AB"
aColumnasPoblacion(5) = "AH"
aColumnasPoblacion(6) = "AN"
aColumnasPoblacion(7) = "AT"
aColumnasPoblacion(8) = "AZ"
aColumnasPoblacion(9) = "BF"
aColumnasPoblacion(10) = "BL"
aColumnasPoblacion(11) = "BR"
aColumnasPoblacion(12) = "BX"
aColumnasPoblacion(13) = "CD"
aColumnasPoblacion(14) = "CJ"
aColumnasPoblacion(15) = "CP"
aColumnasPoblacion(16) = "CV"
aColumnasPoblacion(17) = "DB"
aColumnasPoblacion(18) = "DH"
aColumnasPoblacion(19) = "DN"
aColumnasPoblacion(20) = "DT"
 
Dim aTiposPoblacionColumnas(1) As String
aTiposPoblacionColumnas(0) = "Hombres,C,3"
aTiposPoblacionColumnas(1) = "Mujeres,D,4"
 
Dim vTipoPoblacionColumna As Variant
Dim sTipoPoblacion As String
Dim nPosicionColumna As Integer
Dim vColumnaPoblacion As Variant
 
' por cada tipo de población
For Each vTipoPoblacionColumna In aTiposPoblacionColumnas
    ' obtener la información del tipo de población:
    ' sexo, columna destino, posición numérica columna destino
    sTipoPoblacion = Split(vTipoPoblacionColumna, ",")(0)
    sColumna = Split(vTipoPoblacionColumna, ",")(1)
    nPosicionColumna = Split(vTipoPoblacionColumna, ",")(2)
 
    ' recorrer las columnas que contienen las cifras de población
    ' y pegarlas en la nueva hoja
    For Each vColumnaPoblacion In aColumnasPoblacion
        ' posicionar en hoja con datos origen población
        Sheets(sTipoPoblacion).Select
     
        ' seleccionar celdas y copiarlas
        Range(vColumnaPoblacion & sFilaInicial & ":" & vColumnaPoblacion & sFilaFinal).Select
        Selection.Copy
         
        ' posicionar en hoja destino de los datos
        Sheets(sHojaDestino).Select
        
        ' obtener siguiente fila vacía, posicionar en celda y pegar los datos
        nFilaVacia = FilaVaciaEnColumna(nPosicionColumna)
        Range(sColumna & CStr(nFilaVacia)).Select
        ActiveSheet.Paste
    Next

Next
 
' cambiar estilo de columnas para que se visualicen bordes de celdas
Columns("C:D").Select
Selection.Style = "Normal"
 
Range("A1").Select
 
End Sub

Seguidamente crearemos una nueva macro con el nombre "TraspasarDatosZonificacion", que trasladará a una nueva hoja del archivo Excel los códigos y nombres de las zonas de asistencia sanitaria. Estos datos servirán para cargar una de las tablas catálogo de la base de datos que crearemos más adelante.

Sub TraspasarDatosZonificacion()

' crear nueva hoja y asignarle nombre
Dim sHojaDestino As String
sHojaDestino = "DatosZonificacion"
 
Dim oWorksheet As Worksheet
Set oWorksheet = Sheets.Add(After:=Sheets(Sheets.Count))
oWorksheet.Name = sHojaDestino
 
' poner títulos de columna
Range("A1").Select
ActiveCell.FormulaR1C1 = "Zona_ID"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Zona_DS"
 
' volver a la hoja de datos
Sheets("Hombres").Select
 
' copiar códigos y nombres de zonas al portapapeles
Range("A14:B299").Select
Selection.Copy
 
' volver a hoja destino y pegar los datos
Sheets(sHojaDestino).Select
Range("A2").Select
ActiveSheet.Paste

' quitar color de fondo de celdas
Columns("A:B").Select
Selection.Interior.Pattern = xlNone
Selection.Interior.TintAndShade = 0
Selection.Interior.PatternTintAndShade = 0
 
Range("A1").Select
 
End Sub

Cuando copiamos los códigos y nombres de las zonas sanitarias a las nuevas hojas dentro del archivo Excel, también se copia la configuración de colores de las celdas correspondientes a estos valores. Para conseguir que el color de fondo de estas celdas sea blanco utilizamos las siguientes líneas de código.

Selection.Interior.TintAndShade = 0
Selection.Interior.PatternTintAndShade = 0

Pero si nuestra versión de Excel es anterior a 2007 estas líneas producirán un error en tiempo de ejecución, por lo que deberemos comentarlas para evitar que sean ejecutadas.

Finalmente, desde la ventana de administración de macros, seleccionaremos cada una y haremos clic en "Ejecutar".

Como resultado obtendremos dos nuevas hojas con los datos generados por el código de las macros.

Creación de la base de datos

Nuestro próximo paso consistirá en crear la base de datos PiramidePoblacion, que alojará la información que acabamos de preparar en Excel. El siguiente bloque de código muestra el script de Transact-SQL que tendremos que ejecutar en nuestra instancia de SQL Server (la versión utilizada en este artículo ha sido SQL Server 2008 R2).

CREATE DATABASE PiramidePoblacion
GO
 
USE PiramidePoblacion
GO
 
CREATE TABLE DatosBasePoblacion
(
    Fila_ID int IDENTITY(1,1) NOT NULL,
    Zona varchar(3) NULL,
    Rango_Edad varchar(10) NULL,
    Poblacion_H int NULL,
    Poblacion_M int NULL
)
GO
 
CREATE TABLE Poblacion
(
    Fila_ID int IDENTITY(1,1) NOT NULL,
    Zona_ID varchar(3) NULL,
    Edad_ID int NULL,
    Sexo_ID char(1) NULL,
             CONSTRAINT PK_Poblacion PRIMARY KEY CLUSTERED (Fila_ID ASC)
)
GO
 
CREATE TABLE Zona
(
    Zona_ID varchar(3) NOT NULL,
    Zona_DS varchar(30) NULL,
             CONSTRAINT PK_Zona PRIMARY KEY CLUSTERED (Zona_ID ASC)
)
GO
 
CREATE TABLE Edad
(
    Edad_ID int NOT NULL,
    Edad_Grupo varchar(20) NULL,
             CONSTRAINT PK_Edad PRIMARY KEY CLUSTERED (Edad_ID ASC)
)
GO
 
CREATE TABLE Sexo
(
    Sexo_ID char(1) NOT NULL,
    Sexo_DS varchar(10) NULL,
             CONSTRAINT PK_Sexo PRIMARY KEY CLUSTERED (Sexo_ID ASC)
)
GO

La tabla Población, como podemos adivinar por su nombre, será la que contenga los datos principales de nuestra población, mientras que las tablas Zona, Edad y Sexo contendrán información de catálogo (código/descriptor).

Con toda seguridad, en el script habremos notado la carencia de claves ajenas entre la tabla Población y el resto. Se trata de un olvido intencionado, ya que en el próximo artículo sobre pirámides de población esto nos servirá para demostrar cómo a través de PowerPivot también es posible crear relaciones entre las tablas del modelo.

Después de ejecutar el script, la primera acción que realizaremos en la nueva base de datos será el volcado, desde el archivo pc10t10z2_salud10.xls, de la hoja de cálculo DatosBasePoblacion en la tabla del mismo nombre, utilizando la siguiente sentencia.

INSERT INTO DatosBasePoblacion
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=C:\DatosOrigen\pc10t10z2_salud10.xls',
'SELECT * FROM [DatosBasePoblacion$]')

Generando los registros de población en la base de datos

La finalidad del proceso que estamos llevando a cabo hasta este momento consiste en generar, para la tabla Población, una cantidad de registros que represente a la población con la que vamos a trabajar, y que será igual a la suma de las columnas Poblacion_H y Poblacion_M de la tabla DatosBasePoblacion.

Para tal volumen de datos, el uso de un bucle que vaya creando uno a uno los registros resulta totalmente inviable por cuestiones de tiempo y rendimiento, por lo que debemos buscar un sistema alternativo, que nos permita trabajar utilizando conjuntos de resultados, para poder crear la cantidad de registros que necesitamos empleando un reducido número de operaciones.

La técnica por la que vamos a decantarnos se muestra en el libro Microsoft SQL Server 2008: T-SQL Querying, escrito por Itzik Ben-Gan junto a otros grandes nombres de SQL Server: Lubor Kollar, Dejan Sarka y Steve Kass; y consiste en utilizar varias "Expresiones Comunes de Tabla" o CTE (Common Table Expression) anidadas, mediante las cuales generaremos un conjunto de resultados del tamaño necesario, que combinaremos con una sentencia INSERT INTO, para añadir a la tabla Población un número de registros determinado. Todo ello lo incluiremos en el siguiente procedimiento almacenado.

CREATE PROCEDURE GenerarRegistrosPoblacion
@sZona_ID AS varchar(3),
@sRango_Edad AS varchar(10),
@sSexo_ID AS char(1),
@nPoblacion AS int
AS
BEGIN
 
DECLARE @nEdad_ID AS int =
    CASE
        WHEN CHARINDEX('-',@sRango_Edad) = 0 THEN 100
        WHEN CHARINDEX('-',@sRango_Edad) = 2 THEN CAST(LEFT(@sRango_Edad,1) AS int)
        WHEN CHARINDEX('-',@sRango_Edad) = 3 THEN CAST(LEFT(@sRango_Edad,2) AS int)
    END;
 
WITH
Numeros0 AS (SELECT 1 AS Numero UNION ALL SELECT 1),
Numeros1 AS (SELECT 1 AS Numero FROM Numeros0 AS TblPrim CROSS JOIN Numeros0 AS TblSeg),
Numeros2 AS (SELECT 1 AS Numero FROM Numeros1 AS TblPrim CROSS JOIN Numeros1 AS TblSeg),
Numeros3 AS (SELECT 1 AS Numero FROM Numeros2 AS TblPrim CROSS JOIN Numeros2 AS TblSeg),
Numeros4 AS (SELECT 1 AS Numero FROM Numeros3 AS TblPrim CROSS JOIN Numeros3 AS TblSeg),
Numeros5 AS (SELECT 1 AS Numero FROM Numeros4 AS TblPrim CROSS JOIN Numeros4 AS TblSeg),
NumerosTotal AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS NumeroFila FROM Numeros5)
INSERT INTO Poblacion
SELECT @sZona_ID, @nEdad_ID, @sSexo_ID 
FROM NumerosTotal
WHERE NumeroFila <= @nPoblacion;
 
END
 
GO

A continuación escribiremos un script en el que recorreremos la tabla DatosBasePoblacion, y por cada uno de sus registros tomaremos el valor de los campos Poblacion_H y Poblacion_M, insertando en la tabla Población, mediante la ejecución del procedimiento almacenado GenerarRegistrosPoblacion, un número de filas igual al valor de los mencionados campos.

DECLARE @nContador AS int = 1;
DECLARE @nDatosBaseFilaFinal AS int = (SELECT MAX(Fila_ID) FROM DatosBasePoblacion);
DECLARE @sZona AS varchar(3);
DECLARE @sRango_Edad AS varchar(10);
DECLARE @nPoblacion_H AS int;
DECLARE @nPoblacion_M AS int;
 
WHILE (@nContador <= @nDatosBaseFilaFinal)
BEGIN
    SELECT @sZona = Zona,
        @sRango_Edad = Rango_Edad,
        @nPoblacion_H = Poblacion_H, 
        @nPoblacion_M = Poblacion_M
        FROM DatosBasePoblacion
        WHERE Fila_ID = @nContador;
 
    EXECUTE GenerarRegistrosPoblacion @sZona, @sRango_Edad, 'H', @nPoblacion_H
    EXECUTE GenerarRegistrosPoblacion @sZona, @sRango_Edad, 'M', @nPoblacion_M
    
    SET @nContador += 1
END

El tiempo empleado en la ejecución de este script ha sido de 1 minuto y 37 segundos en una máquina virtual dotada de una CPU Core 2 Duo y 1,5 GB de RAM, lo cual representa un excelente rendimiento, dado el elevado número de registros añadidos a la tabla Población.

Terminamos las operaciones de preparación de la base de datos con las sentencias utilizadas para añadir datos en las tablas que actuarán como catálogos de la tabla Población. Como vemos a continuación, para la tabla Zona también obtenemos los registros del archivo pc10t10z2_salud10.xls.

--////
INSERT INTO Zona
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=C:\DatosOrigen\pc10t10z2_salud10.xls', 
'SELECT * FROM [DatosZonificacion$]')
 
--////
DECLARE @nContador AS int = 0;
WHILE (@nContador <= 120)
BEGIN
    INSERT INTO Edad
    SELECT @nContador,
        CASE 
            WHEN @nContador BETWEEN 0 AND 4 THEN '000-004'
            WHEN @nContador BETWEEN 5 AND 9 THEN '005-009'
            WHEN @nContador BETWEEN 10 AND 14 THEN '010-014'
            WHEN @nContador BETWEEN 15 AND 19 THEN '015-019'
            WHEN @nContador BETWEEN 20 AND 24 THEN '020-024'
            WHEN @nContador BETWEEN 25 AND 29 THEN '025-029'
            WHEN @nContador BETWEEN 30 AND 34 THEN '030-034'
            WHEN @nContador BETWEEN 35 AND 39 THEN '035-039'
            WHEN @nContador BETWEEN 40 AND 44 THEN '040-044'
            WHEN @nContador BETWEEN 45 AND 49 THEN '045-049'
            WHEN @nContador BETWEEN 50 AND 54 THEN '050-054'
            WHEN @nContador BETWEEN 55 AND 59 THEN '055-059'
            WHEN @nContador BETWEEN 60 AND 64 THEN '060-064'
            WHEN @nContador BETWEEN 65 AND 69 THEN '065-069'
            WHEN @nContador BETWEEN 70 AND 74 THEN '070-074'
            WHEN @nContador BETWEEN 75 AND 79 THEN '075-079'
            WHEN @nContador BETWEEN 80 AND 84 THEN '080-084'
            WHEN @nContador BETWEEN 85 AND 89 THEN '085-089'
            WHEN @nContador BETWEEN 90 AND 94 THEN '090-094'
            WHEN @nContador BETWEEN 95 AND 99 THEN '095-099'
            WHEN @nContador >= 100 THEN '100+'
        END
 
    SET @nContador += 1;
END
 
--////
INSERT INTO Sexo VALUES ('H','Hombre')
INSERT INTO Sexo VALUES ('M','Mujer')

Carga de datos completada

Llegados a este punto podemos dar por finalizado el proceso de creación y carga de información demográfica en nuestra base de datos. Como hemos comentado al comienzo, en un próximo artículo utilizaremos esta base de datos como punto de partida para la construcción de pirámides de población con PowerPivot.

Generar datos de prueba para SQL Server desde Excel. El traspaso a SQL Server (y 2)

2011-04-28T18:30:00Z

Después de la generación de datos realizada en Excel, que explicamos en la primera parte del artículo, en esta segunda entrega trataremos acerca de la forma de insertar dicha información en una base de datos SQL Server.

Creación de la base de datos

Finalizada la creación de la hoja de cálculo, volcaremos su contenido en una base de datos SQL Server, que crearemos a continuación ejecutando el siguiente script desde SQL Server Management Studio.

USE master
GO
CREATE DATABASE Poblacion
GO

USE Poblacion
GO

CREATE TABLE DatosPoblacion (
Fila_ID int NOT NULL,
Edad_ID int NULL,
Sexo_ID char(1) NULL,
CCAA_ID int NULL,
Pais_ID int NULL,
Fecha_Alta datetime NULL,
CONSTRAINT PK_DatosPoblacion PRIMARY KEY CLUSTERED (Fila_ID ASC))
GO

CREATE TABLE Sexo (
Sexo_ID char(1) NOT NULL,
Sexo_DS varchar(10) NULL,
CONSTRAINT PK_Sexo PRIMARY KEY CLUSTERED (Sexo_ID ASC))
GO

CREATE TABLE CCAA (
CCAA_ID int NOT NULL,
CCAA_DS varchar(50) NULL,
CONSTRAINT PK_CCAA PRIMARY KEY CLUSTERED (CCAA_ID ASC))
GO

CREATE TABLE Pais (
Pais_ID int NOT NULL,
Pais_DS varchar(50) NULL,
CONSTRAINT PK_Paises PRIMARY KEY CLUSTERED (Pais_ID ASC))
GO

Como acabamos de comprobar, además de la tabla que albergará los datos que generamos desde Excel, también crearemos las tablas catálogo, que contendrán las descripciones de ciertos campos de código existentes en la tabla DatosPoblacion, con las que estableceremos las oportunas relaciones.

Importar los datos de Excel desde SQL Server

Para insertar datos en la tabla DatosPoblacion de SQL Server, utilizaremos la función OPENROWSET de Transact-SQL de la siguiente manera.

INSERT INTO DatosPoblacion
SELECT Fila_ID,Edad_ID,Sexo_ID,CCAA_ID,Pais_ID,Fecha_Alta
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=C:\DatosOrigen\GenerarDatosPoblacion.xlsx', 
'SELECT * FROM [Hoja1$]')

Sin embargo, es posible que al intentar ejecutar esta sentencia de inserción, nos encontremos con un error, que nos informa de que SQL Server no está configurado para realizar consultas de estas características, denominadas 'Ad Hoc Distributed Queries'.

Si queremos comprobar la configuración de opciones de SQL Server, debemos ejecutar el procedimiento almacenado de sistema sp_configure. No obstante, lo más probable es que entre las opciones mostradas, no veamos la configuración de consultas distribuidas. Si estamos en este caso, tenemos que activar la visualización de opciones avanzadas utilizando las siguientes sentencias.

EXECUTE sp_configure 'show advanced options',1
GO
RECONFIGURE
GO

Ahora ya podremos ver el valor de la opción 'Ad Hoc Distributed Queries' al ejecutar sp_configure. Para activarlo ejecutaremos lo siguiente.

EXECUTE sp_configure 'Ad Hoc Distributed Queries',1
GO
RECONFIGURE
GO

Al volver al ejecutar sp_configure, veremos que ya está activada la posibilidad de ejecutar consultas distribuidas.

De esta forma, la anterior sentencia con OPENROWSET ya funcionará correctamente, llenando la tabla DatosPoblacion con el contenido del archivo GenerarDatosPoblacion.xlsx.

Optimizando la importación de datos de Excel

En el punto actual podemos encontrarnos, no obstante, ante un problema de rendimiento, ya que si hemos seguido los pasos indicados anteriormente durante la creación del archivo Excel, tendremos una hoja de cálculo con un millón de filas, que puede tardar alrededor de quince minutos en cargarse en la tabla de SQL Server. Para el ejemplo que estamos desarrollando en este artículo se ha utilizado una máquina virtual con Windows 7 como sistema operativo y 1,5 GB de memoria, por lo que los mencionados tiempos pueden variar en función de la configuración del equipo utilizado para estas pruebas.

Si queremos disminuir estos tiempos de carga podemos optar por el uso de la técnica alternativa de traspaso de datos que explicamos seguidamente.

En primer lugar volveremos a abrir desde Excel el archivo GenerarDatosPoblacion.xlsx, guardándolo como archivo de tipo "CSV (delimitado por comas)".

De esta forma obtendremos un archivo de texto con los campos delimitados por el carácter de punto y coma. Podemos ver su contenido abriéndolo con el Bloc de notas.

A continuación crearemos en la base de datos una nueva tabla con la siguiente estructura.

CREATE TABLE DatosPoblacionExcel (
Fila_ID varchar(20) NULL,
Edad_ID varchar(20) NULL,
Sexo_ID varchar(20) NULL,
CCAA_ID varchar(20) NULL,
Pais_ID varchar(20) NULL,
Anualidad varchar(20) NULL,
Mes varchar(20) NULL,
Dia varchar(20) NULL,
Fecha_Alta varchar(20) NULL)

En esta nueva tabla importaremos el contenido del archivo GenerarDatosPoblacion.csv, utilizando la sentencia BULK INSERT de Transact-SQL. Mediante la opción FIELDTERMINATOR especificaremos el carácter usado como separador de campos, mientras que con la opción FIRSTROW indicaremos que la lectura de datos comience en la segunda fila del archivo, ya que la primera contiene los nombres de las columnas.

BULK INSERT DatosPoblacionExcel
FROM 'C:\DatosOrigen\GenerarDatosPoblacion.csv'
WITH (FIELDTERMINATOR =';', FIRSTROW=2)

El tiempo consumido por esta operación de inserción masiva en la tabla DatosPoblacionExcel será de unos treinta segundos.

Para terminar con este proceso, insertaremos en la tabla DatosPoblacion los registros de la tabla DatosPoblacionExcel, excluyendo los campos innecesarios, como vemos en la siguiente sentencia, cuya ejecución tardará unos 15 segundos aproximadamente.

INSERT INTO DatosPoblacion
SELECT Fila_ID,Edad_ID,Sexo_ID,CCAA_ID,Pais_ID,Fecha_Alta
FROM DatosPoblacionExcel

Como acabamos de comprobar, esta técnica de inserción de datos, si bien nos obliga a realizar un paso adicional, supone una importante ganancia de tiempo, ya que emplea menos de un minuto en el traspaso de datos a la tabla DatosPoblacion, frente a los quince minutos utilizados por la función OPENROWSET.

Importando el resto de tablas catálogo

Para las tablas CCAA y Pais, recurriremos a dos archivos Excel que contienen, respectivamente, la clasificación oficial de comunidades autónomas y países. Estos archivos se encuentran disponibles en formato comprimido, en el sitio Web del Instituto de Estadística de la Comunidad de Madrid. Una vez descargados y descomprimidos ejecutaremos las siguientes sentencias SQL para importarlos a nuestra base de datos.

INSERT INTO CCAA
SELECT DISTINCT ccaa, liteccaa
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\DatosOrigen\ccaaprov.xls', 
'SELECT * FROM [ccaaprov$]')


INSERT INTO Pais
SELECT DISTINCT isopais,lpaisc
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\DatosOrigen\cozonu.xls', 
'SELECT * FROM [cozonu$]')

Observemos que según la versión de Excel del archivo a importar, en la función OPENROWSET utilizaremos un proveedor distinto para obtener los datos. Si el archivo corresponde a Excel 2007-2010 emplearemos 'Microsoft.ACE.OLEDB.12.0', mientras que para versiones anteriores será 'Microsoft.Jet.OLEDB.4.0'.

Ajustando el código de país en la tabla DatosPoblacion

Si observamos con detenimiento los registros de la tabla Pais, nos percataremos de que los valores del campo Pais_ID no son correlativos; siendo, además, el valor menor 4 y el mayor 894.

Esto contrasta con los datos existentes en el campo del mismo nombre correspondiente a la tabla DatosPoblacion, ya que, si bien el valor máximo y mínimo de dicho campo también está entre 4 y 894, encontraremos una buena cantidad de registros en los que el campo Pais_ID no corresponderá a ningún valor en la tabla Pais.

Para solucionar este problema vamos a recurrir a un par de técnicas, de las cuales, la primera consistirá en tomar, de la tabla DatosPoblacion, cada uno de los valores del campo Pais_ID que no existan en la tabla Pais, sumándole uno hasta que lleguemos a un valor que sí exista en la mencionada tabla catálogo de países. Este proceso lo implementaremos en la función de SQL Server que vemos a continuación.

CREATE FUNCTION dbo.ObtenerPais(@nPais_ID int)
RETURNS int
AS
BEGIN

WHILE (SELECT COUNT(*) FROM Pais WHERE Pais_ID = @nPais_ID) = 0
BEGIN
    SET @nPais_ID = @nPais_ID + 1
END    

RETURN @nPais_ID

END
GO

La actualización del campo Pais_ID en la tabla DatosPoblacion la llevaremos a cabo con la siguiente sentencia.

UPDATE DatosPoblacion
SET Pais_ID = dbo.ObtenerPais(Pais_ID)
WHERE Pais_ID NOT IN (SELECT Pais_ID FROM Pais)

La segunda de las técnicas resulta más directa, ya que evitamos el uso de la función de búsqueda del campo Pais_ID en la tabla de países. Lo que hacemos en este caso es una actualización del campo Pais_ID para toda la tabla DatosPoblacion, buscando en la tabla Pais, el valor de Pais_ID más próximo al que existe en el mismo campo de la tabla DatosPoblacion.

UPDATE DatosPoblacion
SET Pais_ID = (SELECT TOP 1 Pais.Pais_ID FROM Pais 
    WHERE Pais.Pais_ID >= DatosPoblacion.Pais_ID 
    ORDER BY Pais.Pais_ID)

En ambos casos, conseguimos que todos los registros de la tabla DatosPoblacion crucen correctamente con la tabla Pais por el campo Pais_ID.

Inserción manual de datos

Después de las operaciones anteriores, la única tabla que permanece sin datos es Sexo, por lo que ejecutaremos las siguientes sentencias, que crearán los registros necesarios.

INSERT INTO Sexo VALUES ('H','Hombre')
INSERT INTO Sexo VALUES ('M','Mujer')

Estableciendo relaciones entre las tablas

Para finalizar la creación de la base de datos, estableceremos las oportunas relaciones entre los campos de la tabla DatosPoblacion y el resto de tablas catálogo, utilizando las siguientes sentencias.

ALTER TABLE DatosPoblacion WITH CHECK ADD
CONSTRAINT FK_DatosPoblacion_Sexo FOREIGN KEY(Sexo_ID) REFERENCES Sexo (Sexo_ID),
CONSTRAINT FK_DatosPoblacion_CCAA FOREIGN KEY(CCAA_ID) REFERENCES CCAA (CCAA_ID),
CONSTRAINT FK_DatosPoblacion_Pais FOREIGN KEY(Pais_ID) REFERENCES Pais (Pais_ID)

Después de esta operación damos por concluido este artículo, en el que hemos explicado las diferentes partes de un proceso para generar, desde Excel, un volumen considerable de datos de prueba, que poder utilizar posteriormente desde SQL Server. En el siguiente enlace se encuentra disponible el código del ejemplo. Esperamos que os resulte de utilidad.

Un saludo.

Programación en Silverlight 4.0, de Marino Posadas

2011-04-27T18:59:00Z

Marino Posadas acaba de publicar un libro sobre Silverlight 4.0, en el que como es su costumbre, además de realizar una introducción a esta tecnología, aborda con profundidad y profusión todos aquellos aspectos necesarios para capacitarnos en la construcción de aplicaciones para esta versión de Silverlight.

Tras la parte inicial dedicada a la arquitectura de Silverlight y las aplicaciones RIA, así como el obligado capítulo sobre XAML, el texto nos ofrece una serie de detallados capítulos sobre las características visuales, layout, aplicación de transformaciones, 3D, interacción con el modelo HTML-DOM y un abordaje de Expression Blend 4 desde la perspectiva del desarrollador. Finalmente, como colofón, encontramos un capítulo dedicado a Windows Phone 7, esencial para todo aquel que quiera iniciarse en el desarrollo de aplicaciones para los dispositivos móviles que funcionan bajo esta plataforma.

En resumen, un excelente libro de un no menos excelente autor, que ya está disponible en dotNetManía.

Un saludo.

Generar datos de prueba para SQL Server desde Excel. Operaciones en Excel (1)

2011-04-26T17:20:00Z

La creación de datos de muestreo

Durante las diversas fases del desarrollo de una aplicación, en la mayor parte de las ocasiones, nos encontramos ante la necesidad de tener a nuestra disposición un conjunto de datos de prueba, para poder utilizarlos en los diferentes procesos que estamos desarrollando. Una situación similar se produce si estamos construyendo un sistema de información basado en cubos de datos OLAP mediante SQL Server Analysis Services, ya que, por lo general, en estos casos necesitaremos además, que el volumen de los datos sea de un tamaño considerable, para poder realizar simulaciones de análisis.

Supongamos que tenemos que generar una base de datos con información poblacional, en la que una tabla albergaría los datos de los individuos tales como edad, códigos de comunidad autónoma de residencia, país de procedencia, sexo, fecha de alta en el registro, etc. Por otro lado, necesitaríamos también una serie de tablas catálogo de países, comunidades autónomas, y demás valores relacionados con los campos de la tabla de individuos.

Entre todo el abanico de utilidades, trucos, etc., que existen para llevar a cabo esta tarea, en el presente artículo haremos uso de Excel como herramienta para la generación del conjunto de datos ficticios correspondiente a los individuos, que posteriormente volcaremos en una base de datos SQL Server, la cual podríamos usar como fuente de datos de la aplicación o sistema en desarrollo.

Creando los datos con Excel

Una vez iniciado Excel 2010 (también podemos utilizar versiones anteriores), nuestra primera tarea será la creación de una columna con los valores que nos servirán para identificar las filas de la tabla. La forma más simple de generarlos, consiste en introducir un par de números correlativos en sendas celdas de una de las columnas de la hoja de cálculo, seleccionar ambas celdas, y arrastrar el marcador de relleno hasta la última fila para la cual queramos generar los números.

No obstante, la generación de valores mediante esta técnica puede resultar un tanto engorrosa en el caso de que debamos producir una gran cantidad de filas y/o columnas, por lo que para la creación de datos en todas las columnas de la hoja vamos a recurrir a un medio más flexible a la par que potente: las macros de Excel.

Creación de una macro

Para crear una macro haremos clic en la pestaña Vista de la cinta de opciones de Excel, y dentro del grupo Macros haremos clic en la opción del mismo nombre, que abrirá el cuadro de diálogo Macro, donde escribiremos el nombre de nuestra macro: CrearDatosPoblacion.

Haciendo clic en el botón Crear, se abrirá la ventana de Visual Basic para Aplicaciones (VBA), con el editor de la nueva macro recién creada, para que podamos empezar a escribir su código.

Dentro del cuerpo del procedimiento CrearDatosPoblación, escribiremos el siguiente bloque de código, donde en primer lugar, limpiaremos las celdas de la hoja de cálculo en la que estemos actualmente posicionados. A continuación solicitaremos al usuario que introduzca, mediante una caja de diálogo InputBox, el número de filas a generar. Después de insertar el título de la columna, introduciremos los dos primeros valores que inician la serie, la cual generaremos mediante el método Selection.AutoFill.

Option Explicit

Sub CrearDatosPoblacion()

Dim nFilaDestino As Long
Dim sCeldaOrigen As String
Dim sCeldaDestino As String

'limpiar las celdas de la hoja de cálculo
Cells.Select
Selection.ClearContents

nFilaDestino = InputBox("Número de registros a generar")
nFilaDestino = nFilaDestino + 1

'columna fila_id
'---------------

'título de columna
Range("A1").Select
ActiveCell.FormulaR1C1 = "Fila_ID"

'valores iniciales de la serie a generar
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "2"

'seleccionar rango de valores iniciales
Range("A2:A3").Select

'rellenar rango total de celdas
sCeldaOrigen = "A2"
sCeldaDestino = "A" & nFilaDestino
Selection.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

End Sub

Para ejecutar la macro seleccionaremos la opción de menú Ejecutar | Ejecutar Sub/UserForm, o pulsaremos la tecla F5, rellenándose la primera columna de la hoja con la cantidad de valores indicada en el InputBox.

Antes de proseguir grabaremos nuestro trabajo desde la ventana de VBA o de Excel, teniendo en cuenta que deberemos hacerlo en un archivo de tipo "Libro de Excel habilitado para macros" (extensión .xlsm), de lo que seremos avisados por un cuadro de diálogo en el momento de la grabación. Haciendo clic en el botón No de dicho diálogo, guardaremos nuestra hoja de cálculo en un archivo con el nombre GenerarDatosPoblacion.xlsm.

Generación de datos aleatorios

La siguiente columna a crear corresponderá a la edad de las personas; en ella, necesitaremos generar valores aleatorios entre un intervalo de números, que representen las edades máxima y mínima que una persona puede tener, por ejemplo, entre 0 y 120.

Para crear, en una celda de Excel, un valor aleatorio comprendido entre dos números, podemos utilizar una fórmula que incluya la función ALEATORIO.ENTRE, que recibe como parámetro los mencionados números, devolviendo como resultado el número generado.

Trasladando esta funcionalidad a la macro que estamos desarrollando, añadiremos a la misma el siguiente bloque de código, en el que asignaremos a una celda de la segunda columna una expresión de fórmula conteniendo RANDBETWEEN(0,120) (equivalente a la función ALEATORIO.ENTRE), seleccionaremos dicha celda, y repetiremos la fórmula que contiene a lo largo del rango de celdas, usando el método ActiveCell.AutoFill.

'edad
'----
Range("B1").Select
ActiveCell.FormulaR1C1 = "Edad_ID"

sCeldaOrigen = "B2"
sCeldaDestino = "B" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,120)"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

Ejecutando otra vez la macro, obtendremos la nueva columna con los datos de edad.

En nuestro siguiente paso le toca el turno a la columna con los valores para el sexo de los individuos. Aquí podríamos haber usado dos números: 1 y 2, generados igualmente de forma aleatoria, para identificar respectivamente a hombre y mujer, pero vamos a complicar un poco esta operación, utilizando en su lugar las letras H y M como valores para la columna, por lo que el problema se encuentra ahora en cómo generar aleatoriamente estas letras en las celdas de la columna, ya que la función RANDBETWEEN sólo recibe y devuelve resultados numéricos.

La solución es muy simple, ya que también consiste en utilizar la función RANDBETWEEN, pero combinándola con la expresión de decisión IF. A RANDBETWEEN le pasaremos los números 1 y 2 como parámetros, y según el resultado obtenido, mediante IF devolveremos la letra H o M. A continuación se muestra el bloque de código para esta columna, que añadiremos a la macro.

'sexo
'----
Range("C1").Select
ActiveCell.FormulaR1C1 = "Sexo_ID"

sCeldaOrigen = "C2"
sCeldaDestino = "C" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=IF(RANDBETWEEN(1,2)=1,""H"",""M"")"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

Para las dos siguientes columnas: código de comunidad autónoma de residencia y código de país de procedencia, seguiremos la misma mecánica que en la columna de edades, si bien utilizando distintos intervalos numéricos.

'ccaa
'----
Range("D1").Select
ActiveCell.FormulaR1C1 = "CCAA_ID"

sCeldaOrigen = "D2"
sCeldaDestino = "D" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(1,19)"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

'país
'----
Range("E1").Select
ActiveCell.FormulaR1C1 = "Pais_ID"

sCeldaOrigen = "E2"
sCeldaDestino = "E" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(4,894)"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

Nuestro siguiente paso consistirá en crear los datos de una supuesta fecha de alta de los individuos en este sistema poblacional, tarea que realizaremos en dos fases. En primer lugar, crearemos cada parte de la fecha en columnas separadas.

'elementos de fecha:
'===================
'año
'---
Range("F1").Select
ActiveCell.FormulaR1C1 = "Anualidad"

sCeldaOrigen = "F2"
sCeldaDestino = "F" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(2008,2010)"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

'mes
'---
Range("G1").Select
ActiveCell.FormulaR1C1 = "Mes"

sCeldaOrigen = "G2"
sCeldaDestino = "G" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(1,12)"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

'día
'---
Range("H1").Select
ActiveCell.FormulaR1C1 = "Dia"

sCeldaOrigen = "H2"
sCeldaDestino = "H" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=2, RANDBETWEEN(1,28), IF(OR(RC[-1]=4, RC[-1]=6, RC[-1]=9, RC[-1]=11), RANDBETWEEN(1,30), RANDBETWEEN(1,31)))"
ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

Como acabamos de ver, el día es la parte de la fecha que requiere mayor trabajo, ya que debemos poner cuidado en evitar la generación, por ejemplo, de un día 31 si en la columna de mes tenemos el valor 2. Esto lo solucionaremos comprobando, en primer lugar, el valor de dicha columna de mes, y en función del mismo, usaremos un intervalo distinto para generar el día; para ello, en la fórmula utilizaremos varias expresiones IF combinadas con funciones RANDBETWEEN.

En la segunda fase de esta operación, crearemos una última columna en la hoja de cálculo con una fecha en un formato comprensible para SQL Server, fruto de la concatenación de las anteriores columnas de anualidad, mes y día.

Es necesario tener en cuenta, al componer la fecha de esta manera, que debemos añadir un cero al mes y al día, cuando estos valores tengan solamente un dígito.

'componer fecha
'--------------
Range("I1").Select
ActiveCell.FormulaR1C1 = "Fecha_Alta"

sCeldaOrigen = "I2"
sCeldaDestino = "I" & nFilaDestino
Range(sCeldaOrigen).Select
ActiveCell.FormulaR1C1 = "=RC[-3] & IF(LEN(RC[-2])=1,""0"" & RC[-2],RC[-2]) & " & _
    "IF(LEN(RC[-1])=1,""0"" & RC[-1],RC[-1]) "

ActiveCell.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

Al volver a ejecutar la macro, obtendremos la fecha correctamente formateada en la última columna. En esta ocasión, además, estableceremos la cifra de registros a generar en un millón, de esa forma probaremos el potencial de creación de los datos.

Para finalizar las operaciones con Excel grabaremos el archivo, esta vez con formato de "Libro de Excel" (extensión .xlsx). Nuevamente aparecerá el cuadro de diálogo de aviso, que nos informará de que no podemos guardar las macros en un archivo .xlsx, al que contestaremos haciendo clic en Sí.

Llegados a este punto concluimos la primera parte del artículo; en la segunda entrega explicaremos cómo traspasar los datos que acabamos de crear a SQL Server.

Un saludo.

Acceso a datos en Silverlight mediante DomainDataSource

2011-03-29T18:00:00Z

En el pasado número 78 de dotNetManía publiqué un artículo titulado "DomainDataSource. Un gestor de datos en Silverlight para la interfaz de usuario", sobre el uso del componente DomainDataSource en la realización de las operaciones de manejo de datos en una aplicación Silverlight. Se trata de un artículo que también se encuentra disponible en los siguientes sitios web: enlace1 y enlace2. Espero que os resulte de utilidad.

Un saludo.

La licencia de .NET Reflector pasa a ser de pago

2011-02-02T18:17:00Z

Después de la adquisición de .NET Reflector por parte de Red Gate hace alrededor de dos años y medio, finalmente ha ocurrido algo que más o menos creo que todos pensábamos que sucedería. Red Gate empezará a cobrar a principios de marzo por la licencia de .NET Reflector. En el siguiente video, Simon Galbraith, uno de los responsables de la compañía, explica las motivaciones que les han llevado a tomar esta medida.

Un saludo.

Mejorando la experiencia de usuario en el control DataForm de Silverlight (y 3)

2011-01-26T17:57:00Z

En esta tercera entrega del artículo seguiremos con nuestra tarea de editar los campos del DataForm usando dos controles sobradamente conocidos por la gran mayoría de desarrolladores: ComboBox y RadioButton. El código fuente del proyecto está disponible aquí.

ComboBox. Seleccionando el valor del campo en una lista desplegable

Continuamos con las operaciones de selección en listas de valores de la mano de uno de los grandes clásicos entre los controles de usuario: ComboBox.

Del mismo modo que en los anteriores controles, el control ComboBox también necesita una colección de elementos para mostrar en su lista desplegable; pero en este caso, en lugar de tratarse de una colección simple de valores, emplearemos la colección de entidades de tipo Customer obtenidas a partir del control DomainDataSource ddsCustomers, que anteriormente añadimos a la página MainPage.xaml.

En primer lugar trasladaremos el control ddsCustomers al bloque de recursos de la página XAML, operación necesaria para que el ComboBox tenga acceso a los datos que ddsCustomers proporciona. Adicionalmente, ordenaremos por la propiedad CustomerName de los objetos Customer el resultado devuelto por este DomainDataSource, utilizando para ello una etiqueta SortDescriptors.

A continuación añadiremos un ComboBox a la plantilla EditTemplate del DataForm, dentro del DataField reservado a la información del cliente de la factura. El código XAML que emplearemos será el siguiente.

<UserControl.Resources>
    <!--....-->
    <riaControls:DomainDataSource x:Name="ddsCustomers" QueryName="GetCustomers">
        <riaControls:DomainDataSource.DomainContext>
            <domainctx:MusicaGestDomainContext />
        </riaControls:DomainDataSource.DomainContext>

        <riaControls:DomainDataSource.SortDescriptors>
            <riaControls:SortDescriptor PropertyPath="CustomerName" />
        </riaControls:DomainDataSource.SortDescriptors>
    </riaControls:DomainDataSource>
</UserControl.Resources>
<!--....-->
<toolkit:DataForm.EditTemplate>
<!--....-->
    <toolkit:DataField Label="Cliente:">
        <StackPanel Orientation="Horizontal">
            <TextBox Text="{Binding Path=CustomerId, Mode=TwoWay}" 
                     Width="40" 
                     IsEnabled="False" />

            <ComboBox x:Name="cboCustomers" 
                      Width="200" 
                      Margin="5,0,0,0"
                      ItemsSource="{Binding Source={StaticResource ddsCustomers}, Path=Data}"
                      DisplayMemberPath="CustomerName"
                      SelectedValuePath="CustomerId" />
        </StackPanel>
    </toolkit:DataField>

Respecto a la configuración de las propiedades del ComboBox, a la propiedad ItemsSource le asignaremos una expresión de enlace a datos cuya fuente sea el DomainDataSource que hemos situado como recurso; mientras que las propiedades DisplayMemberPath y SelectedValuePath contendrán, respectivamente, los valores CustomerName y CustomerId, que corresponden a los nombres de las propiedades de los objetos Customer contenidos en la colección de entidades asignada al ComboBox. Con DisplayMemberPath le indicamos al ComboBox la propiedad a utilizar para los valores a mostrar en la lista, y SelectedValuePath es la propiedad que el control utilizará internamente para informar al DataForm del identificador de cliente seleccionado para la factura.

En el estado actual de la aplicación, cada vez que hagamos clic en el botón de edición del DataForm, el ComboBox siempre mostrará, para CustomerName, el primer valor de la colección, sin mantener la adecuada correspondencia con el valor de CustomerId.

Para corregir este comportamiento erróneo, en primer lugar, a través de la propiedad x:Name, asignaremos un nombre al control TextBox que contiene el valor del campo CustomerId.

<TextBox x:Name="txtCustomerId"
         Text="{Binding Path=CustomerId, Mode=TwoWay}"
         Width="40"
         IsEnabled="False" />

Seguidamente escribiremos en el manipulador del evento ContentLoaded del DataForm un bloque de código en el que obtendremos la instancia del mencionado TextBox y el contexto de dominio del control ddsCustomers. Ambos objetos nos permitirán construir una expresión LINQ, que tendrá como resultado el objeto Customer cuya propiedad CustomerId corresponde al cliente actual de la factura. Como último paso de este proceso recuperaremos la instancia del ComboBox y asignaremos a su propiedad SelectedItem el objeto Customer obtenido.

private void frmInvoices_ContentLoaded(object sender, DataFormContentLoadEventArgs e)
{
    if (e.Mode == DataFormMode.Edit)
    {
        //....
        TextBox txtCustomerId = (TextBox)this.frmInvoices.FindNameInContent("txtCustomerId");

        MusicaGestDomainContext oDomCtxCustomers = (MusicaGestDomainContext)this.ddsCustomers.DomainContext;

        Customer oCustomerActual = (from oCustomer in oDomCtxCustomers.Customers
                         where oCustomer.CustomerId == int.Parse(txtCustomerId.Text)
                         select oCustomer).Single();

        ComboBox cboCustomers = (ComboBox)this.frmInvoices.FindNameInContent("cboCustomers");

        cboCustomers.SelectedItem = oCustomerActual;
    }
}

A partir de ahora, el elemento visualizado por el ComboBox sí corresponderá con el valor adecuado cada vez que entremos en el modo de edición del formulario de datos.

No obstante, el comportamiento del ComboBox dentro del formulario de datos sigue sin ser adecuado. Expliquemos esto con más detalle: cuando situados en modo de edición, el usuario modifica un campo del DataForm, éste detecta el cambio habilitando el botón OK para poder hacer clic en él y confirmar las modificaciones. Esta situación no se está produciendo actualmente para el ComboBox, ya que la selección de un nuevo valor en dicho control no hace que se active el botón OK.

Para conseguir esta funcionalidad vamos a escribir un bloque de código en el manipulador del evento SelectionChanged del ComboBox. Dentro de dicho evento recuperaremos las instancias de los controles ComboBox y TextBox, asignando a este último el valor seleccionado en la lista desplegable, lo que producirá la activación del botón OK del DataForm.

<ComboBox x:Name="cboCustomers" 
....
          SelectionChanged="cboCustomers_SelectionChanged" />


private void cboCustomers_SelectionChanged(object sender, SelectionChangedEventArgs e)
{            
    ComboBox cboCustomers = (ComboBox)this.frmInvoices.FindNameInContent("cboCustomers");

    if (cboCustomers != null)
    {
        string sCustomerIdComboBox = cboCustomers.SelectedValue.ToString();
        TextBox txtCustomerId = (TextBox)this.frmInvoices.FindNameInContent("txtCustomerId");
        txtCustomerId.Text = sCustomerIdComboBox;
    }
}

Empleando esta técnica ya hemos conseguido que el control ComboBox trabaje de manera coordinada con la maquinaria del DataForm. Sin embargo, demos otra vuelta de tuerca a esta situación: supongamos que en la plantilla EditTemplate del formulario prescindimos del TextBox txtCustomerId, ¿cómo conseguimos entonces que el DataForm se percate de los cambios de selección que hagamos en el ComboBox?

La solución pasa por manipular la propiedad DataForm.CurrentItem, la cual contiene el objeto que representa a la entidad actualmente en edición en el formulario de datos; en nuestro caso un objeto Invoice.

Primeramente escribiremos el siguiente bloque de código en el evento DataForm.ContentLoaded, que nos permitirá establecer el valor correcto en el ComboBox al entrar en el modo de edición del formulario.

private void frmInvoices_ContentLoaded(object sender, DataFormContentLoadEventArgs e)
{
    if (e.Mode == DataFormMode.Edit)
    {
        //....
        Invoice oInvoiceActual = (Invoice)this.frmInvoices.CurrentItem;

        MusicaGestDomainContext oDomCtxCustomers = (MusicaGestDomainContext)this.ddsCustomers.DomainContext;

        Customer oCustomerActual = (from oCustomer in oDomCtxCustomers.Customers
                                    where oCustomer.CustomerId == oInvoiceActual.CustomerId
                                    select oCustomer).Single();

        ComboBox cboCustomers = (ComboBox)this.frmInvoices.FindNameInContent("cboCustomers");

        cboCustomers.SelectedItem = oCustomerActual;
    }
}

A continuación procederemos de forma similar en el evento ComboBox.SelectionChanged; esta vez para asignar el valor seleccionado en el ComboBox a la propiedad Invoice.CustomerId de la entidad actualmente en edición.

private void cboCustomers_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    ComboBox cboCustomers = (ComboBox)this.frmInvoices.FindNameInContent("cboCustomers");

    if (cboCustomers != null)
    {
        int nCustomerIdComboBox = (int)cboCustomers.SelectedValue;
        Invoice oInvoiceActual = (Invoice)this.frmInvoices.CurrentItem;
        oInvoiceActual.CustomerId = nCustomerIdComboBox;
    }
}

Como resultado, al editar ahora la entidad en el DataForm, en el campo del cliente sólo aparecerá el ComboBox.

RadioButton. Selección de opciones autoexcluyentes

RadioButton es un control que representa una alternativa más en la manera en que podemos editar/seleccionar los valores para un campo en el DataForm, ya que mediante un conjunto de controles de este tipo, podemos ofrecer al usuario varias opciones entre las cuales elegir una para asignar como valor al campo del formulario.

Vamos a emplear este control para editar el campo Region de la tabla Invoice. Dicho campo tiene cinco valores posibles en esta tabla: Asia, Europe, North America, South America y Oceania, por lo que añadiremos el mismo número de controles RadioButton a nuestra página, dentro de la plantilla EditTemplate del DataForm, usando el siguiente bloque de código XAML.

<toolkit:DataField Label="Región:">
    <StackPanel>
        <RadioButton x:Name="rbtAsia" Content="Asia" GroupName="Region" Checked="rbtRegion_Checked" />
        <RadioButton x:Name="rbtEurope" Content="Europe" GroupName="Region" Checked="rbtRegion_Checked" />
        <RadioButton x:Name="rbtNorthAmerica" Content="North America" GroupName="Region" Checked="rbtRegion_Checked" />
        <RadioButton x:Name="rbtSouthAmerica" Content="South America" GroupName="Region" Checked="rbtRegion_Checked" />
        <RadioButton x:Name="rbtOceania" Content="Oceania" GroupName="Region" Checked="rbtRegion_Checked" />
    </StackPanel>
</toolkit:DataField>

Para que el formulario considere a todos estos controles como pertenecientes a un mismo grupo, de forma que solamente uno de ellos pueda estar seleccionado a la vez, hemos asignado el mismo valor a su propiedad GroupName.

A continuación necesitamos codificar la lógica para que el RadioButton adecuado quede marcado cuando entramos en modo de edición de una entidad, para lo cual añadiremos el siguiente código al evento ContentLoaded del formulario de datos, en el que una vez obtenida la instancia de la entidad a editar, y basándonos en el valor de su propiedad Region, obtendremos del formulario el RadioButton correspondiente, para marcarlo mediante su propiedad IsChecked. Nótese que puesto que algunos nombres de región están formados por dos palabras, para componer el nombre del RadioButton, eliminamos los espacios en blanco mediante el método string.Replace.

private void frmInvoices_ContentLoaded(object sender, DataFormContentLoadEventArgs e)
{
    if (e.Mode == DataFormMode.Edit)
    {
        //....
        Invoice oInvoiceActual = (Invoice)this.frmInvoices.CurrentItem;
        //....
        string sRegion = oInvoiceActual.Region;
        RadioButton rbtRegion = (RadioButton)this.frmInvoices.FindNameInContent("rbt" + sRegion.Replace(" ", string.Empty));
        rbtRegion.IsChecked = true;
    }
}

La otra parte de la funcionalidad que debemos implementar para estos controles corresponde al cambio en la selección del RadioButton mientras estamos en modo de edición, ya que debemos actualizar la propiedad Region de la entidad Invoice que estamos editando con el valor del RadioButton seleccionado. Con tal finalidad, en la declaración de los controles en el código XAML hemos incluido la llamada al método rbtRegion_Checked, que actuará como manejador del evento Checked. La labor de dicho método consiste en comprobar si el RadioButton pulsado es distinto del valor de la propiedad Region de la entidad en edición; en caso afirmativo actualizamos el valor de la propiedad.

private void rbtRegion_Checked(object sender, RoutedEventArgs e)
{
    Invoice oInvoiceActual = (Invoice)this.frmInvoices.CurrentItem;
    RadioButton rbtRegion = (RadioButton)sender;

    if (oInvoiceActual.Region != rbtRegion.Content.ToString())
    {
        oInvoiceActual.Region = rbtRegion.Content.ToString();
    }
}

Y después de esta demostración de las capacidades de edición del control RadioButton en el formulario de datos concluimos este artículo, en el cual hemos abordado una manera de potenciar las características de edición en el control DataForm, a través del uso de controles alternativos para los campos, en reemplazo del habitual TextBox, utilizado usualmente como control de edición por defecto. Espero que os resulte de interés.

Mejorando la experiencia de usuario en el control DataForm de Silverlight (2)

2011-01-24T17:52:00Z

En la primera parte de este artículo, sentamos las bases para empezar a trabajar en la optimización de la interfaz de usuario del DataForm construyendo el proyecto en Visual Studio 2010, la fuente de datos, y el formulario con una funcionalidad básica para las plantillas de lectura y edición. En esta segunda entrega será cuando realmente comencemos con el proceso de mejora sobre los controles de edición.

NumericUpDown para valores numéricos

Iniciamos nuestro periplo de optimizaciones por el campo Total del formulario. Se trata de un campo de tipo numérico que admite decimales, por lo que revisando la Barra de herramientas de Visual Studio 2010, en busca de un control más adecuado para tratar estos valores, encontramos que NumericUpDown se adapta como un guante a este propósito.

Todo lo que tenemos que hacer es añadir a la plantilla de edición del DataForm un nuevo DataField que contenga un NumericUpDown. A la propiedad Value de este control le asignaremos la expresión de enlace a datos que muestra el valor del campo. Adicionalmente, configuraremos el control para que admita decimales, el valor a incrementar cada vez que hagamos clic en los botones de aumentar/disminuir, y la alineación horizontal.

<toolkit:DataField Label="Importe:">
    <toolkit:NumericUpDown Value="{Binding Path=Total, Mode=TwoWay}" 
                           DecimalPlaces="2" Width="50" Increment="0.5" 
                           HorizontalAlignment="Left" />
</toolkit:DataField>

DatePicket y TimePicker. Combinando controles para editar campos de tipo datetime

Cuando el control DataForm construye su interfaz de usuario predeterminada, genera controles DatePicker para editar los campos de tipo datetime. Esto generalmente funciona bien para la mayoría de las situaciones, pero ¿qué ocurre si necesitamos editar la parte horaria correspondiente a este tipo de campo?

Una posible solución consiste en utilizar un control TimePicker, el cual nos permitirá editar esta información. Por lo tanto, añadiremos una copia del mismo a la plantilla EditTemplate del formulario de datos, situándolo al lado del control DatePicker ya existente, y encerrando ambos en un StackPanel.

La propiedad utilizada por TimePicker para visualizar la hora es Value, a la que asignaremos la expresión de enlace a datos que la unirá con el campo InvoiceDate. En ambos controles, DatePicker y TimePicker, dicha expresión de enlace es igual, siendo la maquinaría interna de cada control la encargada de editar la parte (fecha u hora) que le corresponda.

<toolkit:DataField Label="Fecha:">
    <StackPanel Orientation="Horizontal">
        <sdk:DatePicker SelectedDate="{Binding Path=InvoiceDate, Mode=TwoWay}" 
                        Width="110" />

        <toolkit:TimePicker Value="{Binding Path=InvoiceDate, Mode=TwoWay}" 
                            Width="110" />
    </StackPanel>
</toolkit:DataField>

Para modificar la parte de fecha del campo InvoiceDate con DatePicker, podemos editar directamente la caja de texto que contiene el valor de fecha, o hacer clic en el icono de este control que despliega el calendario.

Respecto a la edición de la parte horaria del campo con TimePicker, podemos igualmente, editar la caja de texto de este control escribiendo directamente el número, mediante las teclas de flecha arriba/abajo, o bien podemos hacer clic en el icono con forma de reloj, para desplegar una lista de horas.

AutoCompleteBox. Editar y seleccionar valores de una lista dinámica

En ciertas tablas de una base de datos pueden existir columnas cuyos valores se repiten a lo largo de los registros que componen la tabla, existiendo, además, la certeza de que en los nuevos registros a incorporar, tales valores se volverán a repetir, como es el caso del campo BillingCity de nuestra tabla de ejemplo Invoice.

Para ayudar al usuario en la introducción del contenido para este campo vamos a recurrir al control AutoCompleteBox, el cual, aparte de proporcionar la funcionalidad de una caja de texto, muestra de forma dinámica una lista desplegable de sugerencias con los valores más parecidos al contenido que la caja tenga en cada momento.

El punto principal en la configuración de este control radica en la confección y asignación a su propiedad ItemsSource de la mencionada lista de valores con la que lo alimentamos. Existen diversas técnicas para llevar a cabo esta tarea, algunas de las cuales explicaremos en los próximos apartados.

En primer lugar añadiremos a la plantilla EditTemplate del DataForm una copia de este control. La propiedad Text, al igual que en el TextBox, es la encargada de contener el valor de la caja de texto, por lo que le asignaremos la expresión de enlace a datos que obtenga el valor de la propiedad correspondiente a la entidad de la colección.

<toolkit:DataField Label="Ciudad:">
    <sdk:AutoCompleteBox x:Name="acbBillingCity" 
                         Text="{Binding Path=BillingCity, Mode=TwoWay}" 
                         Width="120" 
                         HorizontalAlignment="Left" />
</toolkit:DataField>

AutoCompleteBox. Creación de la lista utilizando la colección de entidades

Cada vez que editemos un elemento de la colección de entidades asignada al DataForm, se activará la plantilla EditTemplate, cargando la entidad actual en los controles del formulario de datos.

Tal acción desencadenará el evento DataForm.ContentLoaded, en cuyo código comprobaremos el modo de edición actualmente establecido en el formulario interrogando a la propiedad Mode (tipo enumerado DataFormMode) del parámetro DataFormContentLoadEventArgs que recibe el evento. En el caso de que su valor sea Edit, obtendremos del DomainDataSource el objeto que representa al contexto de dominio (MusicaGestDomainContext), y mediante su colección de entidades Invoices, empleando una expresión de LINQ, obtendremos todos los valores distintos correspondientes a la propiedad BillingCity de las entidades. A continuación recuperaremos el control AutoCompleteBox añadido al formulario usando el método DataForm.FindNameInContent, asignando a la propiedad ItemsSource la lista de valores obtenida.

<toolkit:DataForm x:Name="frmInvoices" . . . .
                  ContentLoaded="frmInvoices_ContentLoaded">

using ControlesDataForm.Web;
//....
private void frmInvoices_ContentLoaded(object sender, DataFormContentLoadEventArgs e)
{
    if (e.Mode == DataFormMode.Edit)
    {
        MusicaGestDomainContext oDomainContext = (MusicaGestDomainContext)this.ddsInvoices.DomainContext;
        var oConsulta = (from oInvoice in oDomainContext.Invoices
                         select oInvoice.BillingCity).Distinct();
        AutoCompleteBox acbBillingCity = (AutoCompleteBox)this.frmInvoices.FindNameInContent("acbBillingCity");
        acbBillingCity.ItemsSource = oConsulta;
    }
}

En tiempo de ejecución, al comenzar a teclear un valor dentro de este control, se abrirá debajo del mismo una lista desplegable compuesta por los valores que acabamos de cargar, pero de los que sólo se mostrarán los que comiencen por el mismo valor que hay contenido en la caja de texto del AutoCompleteBox.

AutoCompleteBox. Creación de la lista mediante un recurso

Utilizando código XAML es posible crear declarativamente la lista de valores empleando un tipo ObjectCollection, dentro del cual incluiremos los elementos que formarán parte de la colección.

Para declarar tipos de datos de .NET Framework tales como int, double, string, etc., debemos añadir un atributo xmlns a la etiqueta UserControl de la página, que apunte al espacio de nombres System del ensamblado mscorlib. Una vez creada la colección (en la zona de recursos de la página XAML) asignaremos ésta como un recurso estático a la propiedad ItemsSource del AutoCompleteBox. El campo del formulario al que aplicaremos esta técnica será BillingState.

<UserControl x:Class="ControlesDataForm.MainPage"
....    
    xmlns:System="clr-namespace:System;assembly=mscorlib"
....    
>
    <!--....-->
    <UserControl.Resources>
        <!--....-->
        <toolkit:ObjectCollection x:Key="colEstados">
            <System:String>AB</System:String>
            <System:String>AZ</System:String>
            <System:String>BC</System:String>
            <System:String>CA</System:String>
            <System:String>DF</System:String>
            <System:String>Dublin</System:String>
            <System:String>FL</System:String>
             <!--....-->
        </toolkit:ObjectCollection>
    </UserControl.Resources>

    <toolkit:DataForm x:Name="frmInvoices"
        &lt;!--....-->
        <toolkit:DataForm.EditTemplate>
            <!--....-->
            <toolkit:DataField Label="Estado o Provincia:">
                <sdk:AutoCompleteBox Text="{Binding Path=BillingState, Mode=TwoWay}" 
                                     Width="100" HorizontalAlignment="Left"
                                     ItemsSource="{StaticResource colEstados}" />
            </toolkit:DataField>
            <!--....-->

DomainUpDown. Edición y navegación entre un conjunto de valores

El objetivo de DomainUpDown, al igual que AutoCompleteBox, consiste en seleccionar un valor de una lista (dominio de valores), aunque la diferencia en este caso reside en que dicha lista no se despliega, sino que nos movemos por ella mediante sus controles de desplazamiento.

El modo de creación y configuración de este control también es muy similar al de AutoCompleteBox, siendo ItemsSource la propiedad a la que tendremos que asignar la lista de valores. En nuestro ejemplo emplearemos este control para editar el campo BillingCountry, creando la lista de elementos mediante LINQ, como ya vimos anteriormente. En este caso aplicaremos también la partícula orderby a la expresión LINQ para obtener los valores ordenados.

<toolkit:DataField Label="País:">
    <toolkit:DomainUpDown x:Name="dudBillingCountry" 
                          Value="{Binding Path=BillingCountry, Mode=TwoWay}" 
                          Width="120" HorizontalAlignment="Left" />
</toolkit:DataField>

private void frmInvoices_ContentLoaded(object sender, DataFormContentLoadEventArgs e)
{
    if (e.Mode == DataFormMode.Edit)
    {
        //....
        var qryConsulta = (from oInvoice in oDomainContext.Invoices
                           orderby oInvoice.BillingCountry
                           select oInvoice.BillingCountry).Distinct();

        DomainUpDown dudBillingCountry = (DomainUpDown)this.frmInvoices.FindNameInContent("dudBillingCountry");
        dudBillingCountry.ItemsSource = qryConsulta;
    }
}

El conjunto de valores asignado a un control DomainUpDown inicialmente es cerrado. Esto quiere decir que si escribimos un valor en la caja de texto que no esté en la lista asignada al control, y pulsamos la tecla Intro o cambiamos el foco a otro control del formulario, dicho valor será rechazado, volviendo la zona de edición a recuperar su valor original; por lo que a priori, los únicos valores admisibles son los existentes en la propiedad ItemsSource.

Una forma de alterar este comportamiento del control pasa por asignar a su propiedad InvalidInputAction el valor UseFallbackItem, y en la propiedad FallbackItem un valor igual a uno de los elementos de la lista de valores asignada al control. De esta forma, si el usuario introduce un valor incorrecto, se asignará como valor por defecto el existente en FallbackItem.

<toolkit:DomainUpDown x:Name="dudBillingCountry" 
                      Value="{Binding Path=BillingCountry, Mode=TwoWay}" 
                      Width="120" HorizontalAlignment="Left" 
                      InvalidInputAction="UseFallbackItem" 
                      FallbackItem="Germany" />

Otra técnica consiste en asignar a la propiedad InvalidInputAction el valor TextBoxCannotLoseFocus, lo que mantiene el foco en el control mientras el usuario no introduzca un valor que concuerde con alguno de los existentes en la colección asignada a ItemsSource.

Pero supongamos que el usuario necesita que el valor tecleado en el control sea añadido a su lista de valores en el caso de que no exista. En este tipo de situación, el control DomainUpDown desencadena el evento ParseError, de manera que si escribimos un manipulador para dicho evento podemos adaptar el comportamiento del control a nuestras necesidades.

En el código de este evento obtendremos la instancia del control DomainUpDown a través del parámetro sender que el manipulador recibe, y acto seguido recuperaremos la lista de valores mediante su propiedad Items, volcándola a un tipo List<object>.

Añadiremos al objeto List el valor que el usuario ha escrito en la caja de texto, que se encuentra disponible en la propiedad Text del parámetro UpDownParseErrorEventArgs que recibe el manipulador del evento. Finalmente ordenaremos la lista llamando a su método OrderBy, asignándola de nuevo a la propiedad ItemsSource del control.

Al producirse este evento se vacía la caja de texto del DomainUpDown, por lo que volveremos a asignarle a su propiedad Value el valor que el usuario había tecleado, que como ya hemos indicado, se encuentra en la propiedad UpDownParseErrorEventArgs.Text.

<toolkit:DataField Label="País:">
    <toolkit:DomainUpDown x:Name="dudBillingCountry" 
                          Value="{Binding Path=BillingCountry, Mode=TwoWay}" 
                          Width="120" HorizontalAlignment="Left" 
                          ParseError="dudBillingCountry_ParseError" />
</toolkit:DataField>

private void dudBillingCountry_ParseError(object sender, UpDownParseErrorEventArgs e)
{
    DomainUpDown dudBillingCountry = (DomainUpDown)sender;
    List<object> lstPaises = dudBillingCountry.Items.ToList();
    lstPaises.Add(e.Text);
    dudBillingCountry.ItemsSource = lstPaises.OrderBy(sPais => sPais);
    dudBillingCountry.Value = e.Text;
    e.Handled = true;
}

Con este control llegamos al final de la segunda parte del artículo, en el que hemos abordado varias maneras de mejorar la forma en que podemos editar los valores de los campos de un DataForm. En la tercera parte, que concluye la serie, continuaremos mostrando controles adicionales, que consigan hacer que la edición de los campos por parte de nuestros usuarios, sea una labor un poco más fácil y grata. En el siguiente enlace tenemos disponible el proyecto de ejemplo

Mejorando la experiencia de usuario en el control DataForm de Silverlight (1)

2011-01-23T15:06:00Z

En el artículo dedicado a la edición de datos con plantillas en el DataForm, apuntábamos la posibilidad de mejorar la interfaz de usuario para este control, debido a que los controles de edición que se proporcionan por defecto pueden no ser los más indicados en todos los escenarios a desarrollar.

Las características de los valores a manipular hacen que en ciertas situaciones, un TextBox, por ejemplo, no resulte suficiente si además de escribir el valor del campo queremos ofrecer al usuario una lista de posibles valores para evitar que tenga que teclearlos.

Por tales razones, a través de las diversas entregas que componen este artículo, nos dedicaremos a intentar optimizar la experiencia de usuario en algunos aspectos susceptibles de ser mejorados con respecto al comportamiento predeterminado que ofrece el DataForm. El código fuente del ejemplo que desarrollaremos puede descargarse aquí.

Elaboración de la fuente de datos

Al igual que en otros artículos, el primer paso fundamental consiste en preparar un conjunto de datos de prueba. Como en otras ocasiones, nuestro punto de partida será la base de datos Chinook, que podemos descargar desde CodePlex.

Empleando, con algunas variaciones, los datos de las tablas Invoice y Customer pertenecientes a la base de datos Chinook, crearemos sendas tablas de igual nombre en una nueva base de datos que llamaremos MusicaGest, utilizando las sentencias SQL del siguiente script.

CREATE DATABASE MusicaGest
GO

USE MusicaGest
GO

CREATE TABLE Invoice
(
    InvoiceId int NOT NULL,
    CustomerId int NOT NULL,
    InvoiceDate datetime NULL,
    BillingAddress varchar(70) NULL,
    BillingCity varchar(50) NULL,
    BillingState varchar(50) NULL,
    BillingCountry varchar(50) NULL,
    Region varchar(50) NULL,
    Total numeric(10, 2) NULL,
    FirstInvoice bit NULL,
    CONSTRAINT PK_Invoice PRIMARY KEY CLUSTERED (InvoiceId ASC)
)
GO

INSERT INTO Invoice
SELECT InvoiceId,CustomerId,InvoiceDate,
BillingAddress,BillingCity,BillingState,BillingCountry,
CASE
    WHEN BillingCountry IN ('Austria','Belgium','Czech Republic','Denmark','Finland','France',
        'Germany','Hungary','Ireland','Italy','Netherlands','Norway','Poland','Portugal','Spain',
        'Sweden','United Kingdom') THEN 'Europe'
    WHEN BillingCountry IN ('Argentina','Brazil','Chile') THEN 'South America'
    WHEN BillingCountry IN ('Canada','USA') THEN 'North America'
    WHEN BillingCountry IN ('Australia') THEN 'Oceania'
    WHEN BillingCountry IN ('India') THEN 'Asia'
END,
Total,
(CustomerId % 2)
FROM Chinook.dbo.Invoice
GO

CREATE TABLE Customer
(
    CustomerId int NOT NULL,
    Name varchar(61) NOT NULL,
    CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED (CustomerId ASC)
)
GO

INSERT INTO Customer
SELECT CustomerId, FirstName + ' ' + LastName AS CustomerName 
FROM Chinook.dbo.Customer
GO

Creación del proyecto en Visual Studio 2010

De igual forma que en otros artículos dedicados al DataForm, iniciaremos Visual Studio 2010 y crearemos un nuevo proyecto de tipo Silverlight con el nombre DataFormUX, en el que activaremos WCF RIA Services.

Los primeros pasos que daremos en el desarrollo de este proyecto serán la creación de un modelo de datos (ADO .NET Data Model) con el nombre MusicaGestModel, al que añadiremos las tablas Invoice y Customer, que serán convertidas en entidades dentro del modelo. También agregaremos un servicio de dominio (Domain Service) con el nombre MusicaGestDomainService que contendrá las operaciones de manipulación de dichas entidades. Consulte el lector el siguiente artículo para un mayor detalle acerca de la creación del modelo de datos y del servicio de dominio.

El formulario de datos

Nuestro siguiente paso consistirá en escribir, dentro de la página MainPage.xaml, el código necesario para crear un DataForm y su fuente de datos correspondiente, representada por un control DomainDataSource.

Aprovecharemos igualmente para incluir sendas plantillas ReadOnlyTemplate y EditTemplate, con las que respectivamente presentaremos y editaremos los elementos de la colección de entidades conectada al formulario de datos.

<UserControl x:Class="DataFormUX.MainPage"
    xmlns:riaControls="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.DomainServices"    
    xmlns:toolkit="http://schemas.microsoft.com/winfx/2006/xaml/presentation/toolkit"  
    xmlns:sdk="http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk"  
    xmlns:domainctx="clr-namespace:DataFormUX.Web"
....
<UserControl.Resources>        
    <Style TargetType="TextBox" >
        <Setter Property="HorizontalAlignment" Value="Left" />
    </Style>
</UserControl.Resources>
<!--....-->
<riaControls:DomainDataSource x:Name="ddsInvoices" QueryName="GetInvoices">
    <riaControls:DomainDataSource.DomainContext>
        <domainctx:MusicaGestDomainContext />
    </riaControls:DomainDataSource.DomainContext>
</riaControls:DomainDataSource>
<!--....-->
<toolkit:DataForm x:Name="frmInvoices" 
                    Margin="10" 
                    ItemsSource="{Binding ElementName=ddsInvoices, Path=Data}" 
                    AutoEdit="False"
                    AutoCommit="False">
    <toolkit:DataForm.ReadOnlyTemplate>
        <DataTemplate>
            <StackPanel>
                <toolkit:DataField Label="Código factura:" Description="Número identificador de la factura">
                    <TextBlock Text="{Binding Path=InvoiceId, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Código cliente:" Description="Identificador del cliente">
                    <TextBlock Text="{Binding Path=CustomerId, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Fecha:">
                    <TextBlock Text="{Binding Path=InvoiceDate, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Dirección:">
                    <TextBlock Text="{Binding Path=BillingAddress, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Ciudad:">
                    <TextBlock Text="{Binding Path=BillingCity, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Estado o Provincia:">
                    <TextBlock Text="{Binding Path=BillingState, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="País:">
                    <TextBlock Text="{Binding Path=BillingCountry, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Region:">
                    <TextBlock Text="{Binding Path=Region, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Importe:">
                    <TextBlock Text="{Binding Path=Total, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Primera factura?:">
                    <TextBlock Text="{Binding Path=FirstInvoice, Mode=OneWay}" />
                </toolkit:DataField>
            </StackPanel>
        </DataTemplate>
    </toolkit:DataForm.ReadOnlyTemplate>

    <toolkit:DataForm.EditTemplate>
        <DataTemplate>
            <StackPanel>
                <toolkit:DataField Label="Código factura:">
                    <TextBlock Text="{Binding Path=InvoiceId, Mode=TwoWay}" Width="40" HorizontalAlignment="Left" />
                </toolkit:DataField>

                <toolkit:DataField Label="Código Cliente:">
                    <TextBox Text="{Binding Path=CustomerId, Mode=TwoWay}" Width="40" />
                </toolkit:DataField>

                <toolkit:DataField Label="Fecha:">
                    <sdk:DatePicker SelectedDate="{Binding Path=InvoiceDate, Mode=TwoWay}" 
                        Width="110" HorizontalAlignment="Left" />
                </toolkit:DataField>

                <toolkit:DataField Label="Dirección:">
                    <TextBox Text="{Binding Path=BillingAddress, Mode=TwoWay}" Width="150" />
                </toolkit:DataField>

                <toolkit:DataField Label="Ciudad:">
                    <TextBox Text="{Binding Path=BillingCity, Mode=TwoWay}" Width="120" />
                </toolkit:DataField>

                <toolkit:DataField Label="Estado o Provincia:">
                    <TextBox Text="{Binding Path=BillingState, Mode=TwoWay}" Width="100" />
                </toolkit:DataField>

                <toolkit:DataField Label="País:">
                    <TextBox Text="{Binding Path=BillingCountry, Mode=TwoWay}" Width="80" />
                </toolkit:DataField>

                <toolkit:DataField Label="Región:">
                    <TextBox Text="{Binding Path=Region, Mode=TwoWay}" Width="100" />
                </toolkit:DataField>

                <toolkit:DataField Label="Importe:">
                    <TextBox Text="{Binding Path=Total, Mode=TwoWay}" Width="50" />
                </toolkit:DataField>

                <toolkit:DataField Label="Primera factura?:">
                    <CheckBox IsChecked="{Binding Path=FirstInvoice, Mode=TwoWay}" />
                </toolkit:DataField>
            </StackPanel>
        </DataTemplate>
    </toolkit:DataForm.EditTemplate>
</toolkit:DataForm>

Comenzando con la mejora

Llegados a este punto habremos logrado un formulario de datos con una interfaz de usuario muy similar a la que el DataForm hubiera generado automáticamente, pero en la que el control TextBox queda algo escaso de funcionalidad para la edición de los valores de ciertos campos.

A partir de la segunda entrega de este artículo propondremos soluciones puntuales para cada uno de los campos que consideremos mejorables, sustituyendo el control TextBox inicial por otro más adecuado para los valores que el usuario deba editar. Sin embargo, antes de entrar en el desarrollo de tales funcionalidades, modificaremos en primer lugar la plantilla ReadOnlyTemplate, de forma que, por un lado, además del código del cliente (campo CustomerId) también se visualice el nombre (campo CustomerName de la tabla Customers); mientras que por otra parte, haremos que el campo InvoiceDate muestre la fecha con un formato más adecuado. Toda esta funcionalidad la lograremos utilizando convertidores de tipo, como vimos en los artículos dedicados a las plantillas de presentación en el DataForm y uso de convertidores en el DataGrid, publicados anteriormente en este blog.

Primeramente añadiremos a la página XAML un nuevo control DomainDataSource que obtenga la colección de entidades Customer.

<!--....-->
<riaControls:DomainDataSource x:Name="ddsCustomers" QueryName="GetCustomers">
    <riaControls:DomainDataSource.DomainContext>
        <domainctx:MusicaGestDomainContext />
    </riaControls:DomainDataSource.DomainContext>
</riaControls:DomainDataSource>

<StackPanel Background="SkyBlue">
    <toolkit:DataForm x:Name="frmInvoices"
....

El código de los convertidores lo escribiremos en un archivo con el nombre Convertidores.cs, que agregaremos al proyecto Silverlight de la solución que estamos desarrollando. Dentro de este archivo crearemos las clases FechaConvertidor y CustomerConvertidor, que utilizaremos respectivamente para formatear la fecha de la factura y obtener el nombre del cliente.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Data;

namespace DataFormUX.Web
{
    public class FechaConvertidor : IValueConverter
    {
        public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            return ((DateTime)value).ToString("dd \\de MMMM \\de yyyy; HH:mm");
        }

        public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            return null;
        }
    }

    public class CustomerConvertidor : IValueConverter
    {
        public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            MusicaGestDomainContext oDomainContext = (MusicaGestDomainContext)((MainPage)App.Current.RootVisual).ddsCustomers.DomainContext;
            Customer oCustomer;

            if (oDomainContext.Customers.Count > 0)
            {
                oCustomer = (from oCust in oDomainContext.Customers
                             where oCust.CustomerId == (int)value
                             select oCust).Single();
            }
            else
            {
                oCustomer = new Customer() { CustomerId = -1, CustomerName = "--" };
            }
            
            return oCustomer.CustomerName;
        }

        public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            return null;
        }
    }
}

Centrándonos en la clase CustomerConvertidor, la técnica que hemos empleado para obtener el nombre del cliente consiste en tomar el objeto App, que representa a la aplicación en ejecución (deriva de la clase Application), y a través de sus propiedades, acceder al DomainDataSource ddsCustomers situado en el objeto MainPage, que deriva de UserControl y representa a la página XAML en la que construimos la interfaz de usuario.

Desde ddsCustomers obtenemos el contexto de dominio (propiedad DomainContext), y comprobamos que la colección de entidades contiene elementos interrogando a su propiedad Count, ya que hemos detectado que, aleatoriamente, este DomainDataSource tarda un cierto tiempo en cargarse, y en algunas ocasiones, a la hora de mostrar la primera entidad en el DataForm, todavía se encuentra vacío.

En el caso de que ddsCustomers contenga valores, para obtener el objeto Customer adecuado utilizamos una expresión LINQ, en la que empleamos el parámetro value del método Convert, que contiene el valor del campo CustomerId. Si ddsCustomers no tiene elementos, creamos un objeto Customer sin nombre. Como último paso devolvemos la propiedad CustomerName del objeto Customer como valor de retorno, la cual será visualizada en un campo de la plantilla ReadOnlyTemplate del DataForm. Previamente, tendremos que haber instanciado los convertidores en la zona de recursos de la página XAML.

<UserControl.Resources>
    <domainctx:FechaConvertidor x:Key="cnvFechaConvertidor" />
    <domainctx:CustomerConvertidor x:Key="cnvCustomerConvertidor" />
    <!--....-->
</UserControl.Resources>

<toolkit:DataForm x:Name="frmInvoices" 
....
<toolkit:DataForm.ReadOnlyTemplate>
<!--....-->
<toolkit:DataField Label="Nombre cliente:" Description="Nombre del cliente">
    <TextBlock Text="{Binding Path=CustomerId, Mode=OneWay, Converter={StaticResource cnvCustomerConvertidor}}" />
</toolkit:DataField>

<toolkit:DataField Label="Fecha:">
    <TextBlock Text="{Binding Path=InvoiceDate, Mode=OneWay, Converter={StaticResource cnvFechaConvertidor}}" />
</toolkit:DataField>
<!--....-->

Después de haber realizado estas modificaciones, el DataForm en modo de lectura mostrará las mejoras aplicadas, como vemos en la siguiente figura.

Y con esta mejora sobre la plantilla de lectura del DataForm termina la primera parte del artículo. En la siguiente entrega comenzaremos con las optimizaciones a nivel de la plantilla de edición del formulario, a través del uso de controles alternativos al TextBox.

DataForm EditTemplate y NewItemTemplate. Edición de datos en Silverlight mediante plantillas (y 2)

2010-10-14T21:05:00Z

Después de la introducción a la plantilla EditTemplate del control DataForm realizada en la primera parte, en esta segunda entrega trataremos algunos aspectos adicionales de dicha plantilla, así como la inserción de nuevos datos utilizando la plantilla NewItemTemplate, proporcionada también por este control. Al igual que en la anterior entrega, el código fuente de los ejemplos está disponible en este enlace.

Deshabilitar el modo de edición automática

Cada vez que ejecutamos el formulario habremos comprobado que podemos editar directamente los campos de la entidad en la que nos posicionamos al navegar por la colección de entidades. Ello es debido a que la propiedad AutoEdit del DataForm tiene el valor true por defecto. Si queremos que sea el usuario quien decida cuándo quiere activar el modo de edición tendremos que asignar el valor false a dicha propiedad.

<toolkit:DataForm x:Name="frmInvoices" 
                  ItemsSource="{Binding ElementName=ddsInvoices, Path=Data}" 
                  Margin="5" 
                  Header="Edición de facturas" 
                  AutoEdit="False">

De esta manera, el DataForm ofrecerá inicialmente los campos en modo de lectura, incorporando un nuevo botón en la barra de herramientas con la imagen de un lapicero. Al hacer clic en dicho botón, el estado de los controles pasará de sólo lectura a edición. Para grabar los cambios realizados en los campos tan sólo es necesario hacer clic en cualquiera de los botones de navegación para movernos a otra entidad, o bien hacer clic en el botón Cancel para deshacer los cambios y devolver los campos a sus valores originales.

La propiedad AutoEdit y la plantilla ReadOnlyTemplate

Si pretendemos utilizar simultáneamente en el DataForm una plantilla EditTemplate y otra ReadOnlyTemplate, hemos de tener en cuenta que mientras que la propiedad AutoEdit tenga el valor true, el DataForm hará caso omiso de la plantilla ReadOnlyTemplate, utilizando solamente la plantilla de edición. Para solucionar este comportamiento bastará con asignar false a la propiedad AutoEdit como vemos a continuación.

<toolkit:DataForm x:Name="frmInvoices" 
                    ItemsSource="{Binding ElementName=ddsInvoices, Path=Data}" 
                    Margin="5" 
                    Header="Edición de facturas" 
                    AutoEdit="False">

    <toolkit:DataForm.ReadOnlyTemplate>
        <DataTemplate>
            <StackPanel>
                <toolkit:DataField Label="Código factura:" Description="Número identificador de la factura">
                    <TextBlock Text="{Binding Path=InvoiceId, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Código cliente:" Description="Identificador del cliente">
                    <TextBlock Text="{Binding Path=CustomerId, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Fecha:">
                    <TextBlock Text="{Binding Path=InvoiceDate, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Dirección:">
                    <TextBlock Text="{Binding Path=BillingAddress, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Ciudad:">
                    <TextBlock Text="{Binding Path=BillingCity, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="País:">
                    <TextBlock Text="{Binding Path=BillingCountry, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Importe:">
                    <TextBlock Text="{Binding Path=Total, Mode=OneWay}" />
                </toolkit:DataField>

                <toolkit:DataField Label="Primera factura?:">
                    <TextBlock Text="{Binding Path=FirstInvoice, Mode=OneWay}" />
                </toolkit:DataField>
            </StackPanel>
        </DataTemplate>
    </toolkit:DataForm.ReadOnlyTemplate>

    <toolkit:DataForm.EditTemplate>
        <!--....-->
    </toolkit:DataForm.EditTemplate>
</toolkit:DataForm>

Confirmando manualmente los cambios realizados a la entidad

Después de editar uno o varios campos del formulario, para que dichos cambios queden guardados en la colección de entidades asociada al DataForm, nos desplazaremos a otra entidad de la colección utilizando los botones de navegación de la barra de herramientas.

Pero en algunos casos puede resultar interesante obligar al usuario a aceptar de forma explícita tales modificaciones. Esto lo podemos conseguir asignando el valor false a la propiedad AutoCommit del control DataForm, la cual contiene true por defecto.

<toolkit:DataForm x:Name="frmInvoices" 
                  ItemsSource="{Binding ElementName=ddsInvoices, Path=Data}" 
                  Margin="5" 
                  Header="Edición de facturas" 
                  AutoEdit="False" 
                  AutoCommit="False">

Una vez hecha esta asignación, cada vez que editemos una entidad del DataForm se mostrará el botón OK, que utilizaremos para aceptar los cambios que hayamos efectuado en los campos del formulario.

El botón OK estará inicialmente deshabilitado y no se habilitará hasta que no hagamos cambios en alguno de los campos del formulario. A partir del momento en el que este botón esté disponible, los botones de la barra de herramientas se deshabilitarán, con lo que el usuario estará obligado a aceptar o cancelar haciendo clic, respectivamente, en OK o Cancel.

Una de las ventajas de confirmar la edición de campos mediante el botón OK radica en que una vez pulsado dicho botón seguiremos posicionados en la misma entidad que estábamos editando. Recordemos que cuando sólo disponemos del botón Cancel tenemos que movernos a otra entidad de la colección para hacer efectivos los cambios.

Como apunte estético adicional conviene saber que las propiedades CommitButtonContent y CancelButtonContent permiten asignar a estos botones un título distinto del que se ofrece por defecto.

<toolkit:DataForm x:Name="frmInvoices" 
                    ItemsSource="{Binding ElementName=ddsInvoices, Path=Data}" 
                    Margin="5" 
                    Header="Edición de facturas" 
                    AutoEdit="False" 
                    AutoCommit="False" 
                    CommitButtonContent="Aceptar cambios" 
                    CancelButtonContent="Salir de edición">

Borrar una entidad de la colección

Podemos utilizar el botón Borrar de la barra de herramientas para eliminar la entidad actual sobre la que está posicionado el DataForm, aunque debemos tener precaución, ya que el borrado se realiza sin pedir confirmación al usuario.

Si queremos prevenir borrados accidentales crearemos un manipulador para el evento DeletingItem, que se producirá durante la solicitud de borrado de un elemento de la colección del formulario, lo cual nos permite anular la operación de borrado asignando el valor true a la propiedad Cancel del tipo CancelEventArgs que este manipulador recibe como parámetro.

<toolkit:DataForm x:Name="frmInvoices" 
                  <--....-->
                  DeletingItem="frmInvoices_DeletingItem">

private void frmInvoices_DeletingItem(object sender, System.ComponentModel.CancelEventArgs e)
{
    if (MessageBox.Show("¿Borrar la factura?", "Atención", MessageBoxButton.OKCancel) == MessageBoxResult.Cancel)
    {
        e.Cancel = true;
    }
}

NewItemTemplate. Añadiendo nuevos elementos al DataForm

Gracias a la plantilla NewItemTemplate podemos crear una interfaz de usuario específica para los casos en que tengamos que agregar nuevos elementos a la colección de entidades del formulario haciendo clic en el botón Añadir.

El siguiente bloque de código XAML muestra el modo de declaración de esta plantilla.

<toolkit:DataForm ....>
    <!--....-->
    <toolkit:DataForm.NewItemTemplate>
        <DataTemplate>
            <StackPanel>
                <!--....-->
            </StackPanel>
        </DataTemplate>
    </toolkit:DataForm.NewItemTemplate>
</toolkit:DataForm>

La carencia de esta plantilla no significa que en el formulario no podamos añadir nuevas entidades, ya que en tal situación, el DataForm automáticamente hace uso de la plantilla EditTemplate, utilizándola como plantilla de inserción.

No obstante, la inserción es una operación de edición con ciertas particularidades, que no siempre van a poder ser sustituidas directamente mediante la plantilla EditTemplate.

Por ejemplo, supongamos que en nuestra aplicación, a la hora de crear una nueva factura, los campos del DataForm deben cumplir con una serie de pautas y restricciones, las cuales describiremos en los siguientes apartados, aportando una solución para cada una de ellas.

InvoiceId

La edición de este campo estará restringida en la creación de una factura, ya que como hemos comentado anteriormente, estamos ante un valor que genera automáticamente el motor de datos, por lo que resulta innecesaria su introducción manual por parte del usuario.

Por tal motivo, no incluiremos en la plantilla NewItemTemplate un control para editar este campo.

InvoiceDate

Este campo tampoco podrá ser editado por el usuario, ya que tomará su valor de la fecha del sistema. Sin embargo, a diferencia del anterior, su valor sí tendrá que ser visualizado.

La forma de resolver este problema consistirá, por una parte, en asignar el valor false a la propiedad IsEnabled del DataField que utilizaremos en la definición del campo. Por otro lado, en el code-behind de la página, crearemos un manipulador para el evento ContentLoaded; donde comprobaremos si la operación de edición corresponde a la creación de una nueva entidad (enumeración DataFormMode), y en caso afirmativo, obtendremos la entidad actualmente en curso, asignando la fecha del sistema a su propiedad InvoiceDate.

<toolkit:DataField Label="Fecha:" IsEnabled="False">
    <sdk:DatePicker x:Name="dtInvoiceDate"
                    SelectedDate="{Binding Path=InvoiceDate, Mode=TwoWay}" 
                    Width="110" HorizontalAlignment="Left" />
</toolkit:DataField>

private void frmInvoices_ContentLoaded(object sender, DataFormContentLoadEventArgs e)
{
    if (this.frmInvoices.Mode == DataFormMode.AddNew)
    {
        Invoice oInvoice = (Invoice)this.frmInvoices.CurrentItem;
        oInvoice.InvoiceDate = DateTime.Today;
        //....
    }
}

FirstInvoice

Otro campo que al igual que los dos anteriores no podrá ser modificado, debiendo tomar el valor true, o lo que es igual, mostrar un CheckBox con la casilla marcada.

<toolkit:DataField Label="Primera factura?:" IsEnabled="False">
    <CheckBox IsChecked="{Binding Path=FirstInvoice, Mode=TwoWay}" />
</toolkit:DataField>

private void frmInvoices_ContentLoaded(object sender, DataFormContentLoadEventArgs e)
{
    if (this.frmInvoices.Mode == DataFormMode.AddNew)
    {
        Invoice oInvoice = (Invoice)this.frmInvoices.CurrentItem;
        //....
        oInvoice.FirstInvoice = true;
    }
}

BillingAddress

A diferencia de los campos anteriores, la dirección de la factura sí podrá ser editada por el usuario. Sin embargo, este campo tendrá que tomar como valor inicial la cadena "C/", y adicionalmente, al recibir el foco, tendrá que posicionarse al final del texto del control; esto último lo lograremos codificando el evento GotFocus del control de edición.

<toolkit:DataField Label="Dirección:">
    <TextBox x:Name="txtBillingAddress" Text="{Binding Path=BillingAddress, Mode=TwoWay}" 
                Width="150" 
                GotFocus="txtBillingAddress_GotFocus" />
</toolkit:DataField>

private void txtBillingAddress_GotFocus(object sender, RoutedEventArgs e)
{
    ((TextBox)sender).Select(((TextBox)sender).Text.Length, 0);
}

CustomerId

El DataForm asigna de manera predeterminada el valor cero a los campos numéricos cuando se trata de nuevas entidades. En el campo CustomerId este comportamiento puede resultar especialmente incómodo, ya que en la práctica totalidad de las ocasiones, el usuario debe borrar el cero y teclear un código válido de cliente. Por tal causa, en el evento GotFocus de este control de edición añadiremos el código necesario para que automáticamente sea borrado su contenido si sólo hay un cero.

<toolkit:DataField Label="Código cliente:">
    <TextBox x:Name="txtCustomerId" Text="{Binding Path=CustomerId, Mode=TwoWay}" 
                Width="40" HorizontalAlignment="Left" 
                GotFocus="txtCustomerId_GotFocus" />
</toolkit:DataField>

private void txtCustomerId_GotFocus(object sender, RoutedEventArgs e)
{
    if (this.frmInvoices.Mode == DataFormMode.AddNew)
    {
        TextBox txtCustomerId = (TextBox)this.frmInvoices.FindNameInContent("txtCustomerId");

        if (txtCustomerId.Text == "0")
        {
            txtCustomerId.Text = string.Empty;
        }
    }
}

Tras escribir todo este código declarativo y de comportamiento para la plantilla de inserción, ejecutaremos la aplicación, que tendrá el aspecto de la siguiente figura durante la creación de una nueva entidad.

La creación de nuevos valores utilizando la plantilla NewItemTemplate pone punto final a este artículo, en el que nos hemos aproximado a las operaciones de edición que desde el control DataForm de Silverlight pueden realizarse a través de las plantillas que este control proporciona. Confiamos en que os resulte de utilidad.

Un saludo.