El aprendiz de brujo

Generate SQL Server test data from Excel. The transfer to SQL Server (and 2)

2012-05-11T19:36:00Z

After data generation in Excel explained in the first part of the article, in this second installment we'll show how to insert that information into a SQL Server database.

Database creation

After spreadsheet creation, we'll transfer its contents into a SQL Server database running the following script from 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

As we have seen, in addition to the table that will house the data generated from Excel, we will also create the catalog tables, which contain descriptions of some fields of code existing in DatosPoblacion table with which establish the necessary relationships.

Import Excel data from SQL Server

To insert data into DatosPoblacion table, we'll use Transact-SQL's OPENROWSET function as follows.

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$]')

However, it's possible we get an error when trying to execute this insert sentence, which informs us that SQL Server is not configured to query in this way, called 'Ad Hoc Distributed Queries'.

If we want to check the SQL Server settings, we must run the system stored procedure sp_configure. However, it is likely that among the options listed, we do not see the distributed queries configuration. If we are in this case, we must enable the advanced options display using the following sentences.

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

Now we see the value of 'Ad Hoc Distributed Queries' option by executing sp_configure.To enable it execute the following.

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

When we run sp_configure back, we will already see activated the ability to run distributed queries.

Thus, the previous sentence with OPENROWSET will work properly, filling the DatosPoblacion table with the content of the GenerarDatosPoblacion.xlsx file.

Optimizing data import from Excel

However, at the current point we may face a performance problem, because if we followed the above steps for creating the Excel file, we have a spreadsheet with one million rows, which can take about fifteen minutes to load into SQL Server table. For the sample developed in this article, we have used a virtual machine with Windows 7 operating system and 1.5 GB RAM, so that the above times may vary depending on the configuration used for these tests.

If we want to reduce these load times we may opt an alternative technique for transferring data which explain below.

In first place we will open back the file GenerarDatosPoblacion.xlsx, saving it as "CSV (Comma delimited)" type.

In this way we'll get a text file with fields delimited by the semicolon character. We can see its contents by opening it with Notepad.

Then we will create a new table in the database with the following structure.

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)

Into this new table we will import the contents of GenerarDatosPoblacion.csv, using the Transact-SQL statement BULK INSERT. Through the FIELDTERMINATOR option, we'll specify the character used as field separator, while the option FIRSTROW indicate that the reading of data starts in the second row of the file, because the first contains the column names.

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

The time consumed by the bulk insert operation in the table DatosPoblacionExcel be about thirty seconds.

To finish this process, we will insert in the table DatosPoblacion the records from DatosPoblacionExcel table, excluding the unnecessary fields, as shown in the following statement, whose execution will take approximately 15 seconds.

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

As we have seen, this inserting data technique, but requires us to perform an additional step, is a significant gain in time, employing less than a minute in the transfer of data to the table DatosPoblacion, compared to the fifteen minutes used by the OPENROWSET function.

Importing the other catalog tables

For tables CCAA and Pais, we will use two Excel files containing, respectively, the official classification of regions and countries .These files are available in compressed format on the website of the Madrid Community Statistics Institute. Once downloaded and unzipped we'll run the following SQL statements for import into our database.

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$]')

Note that depending on the version of Excel file to import, in OPENROWSET function we'll use a different provider to get the data.If the file is for Excel 2007-2010 will use 'Microsoft.ACE.OLEDB.12.0 ', while for earlier versions will be "Microsoft.Jet.OLEDB.4.0 '.

Setting the country code in the table DatosPoblacion

A closer look at the records of the Pais table will unveil that Pais_ID field values are uncorrelated, being also the lowest value 4 and the highest 894.

This contrasts with existing data in the field of the same name for the table DatosPoblacion because, although the maximum and minimum value of this field is also between 4 and 894, we find a lot of records where the field Pais_ID not correspond to any value in the Pais table.

To solve this problem we resort to a couple of techniques, of which the first will be taken from DatosPoblacion table, each Pais_ID field values that do not exist in the Pais table, adding one until we reach a value which does exist in the countries catalog table mentioned. We will implement this process in the SQL Server function below.

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

The update of field Pais_ID in table DatosPoblacion the take out with the next statement.

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

The second technique is more direct, as it avoids the use of the search function of the field Pais_ID in the countries table.What we do here is an update of the field Pais_ID for the entire table DatosPoblacion, looking in the Pais table, the value of Pais_ID closest to that exists in the same field in the table 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)

In both cases, we get that all the records in the DatosPoblacion table join correctly with the Pais table through Pais_ID field.

Manual data entry

After the above operations, the only remaining empty table is Sexo, so we'll run the following statements, which will create the necessary records.

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

Establishing relationships between tables

To finish the database creation, we will establish the appropriate relationships between fields in the table DatosPoblacion and other catalog tables using the following sentences.

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)

After this operation we finish this article, which explained the different parts of a process for generating, from Excel, a considerable amount of test data that can be used later from SQL Server.We hope you find useful.

Regards.

Generate SQL Server test data from Excel. Operations in Excel (1)

2012-05-06T18:09:00Z

Creating sample data

During the application development stages, in most of the time, we are faced with the need to have at our disposal a set of test data to use in the different processes that are developing. A similar situation occurs if we are building an information system based on OLAP data cubes using SQL Server Analysis Services, because in these cases we will need also a large volume of data, to perform analysis simulations.

Suppose we have to generate a database of population data, with a table containing data of individuals such as age, code region of residence, national origin, sex, registration date, etc. On the other hand, also need a series of catalog tables of countries, regions, and other values related to the fields in the table of individuals.

Among the full range of utilities, tricks, and more, which exist to carry out this task, in this article we will use Excel as a tool for generating the dummy data set for individuals that later we'll dump in a SQL Server database, which could be used as a data source for the application or information system.

Creating data with Excel

After starting Excel 2010 (earlier versions may also use), our first task will be to create a column with the values that will serve to identify the rows in the table. The simplest way to generate is to introduce a pair of consecutive numbers in individual cells in a column of the worksheet, select both cells and drag the fill handle to the last row for which we want to generate the numbers.

However, the generation of values using this technique can be somewhat cumbersome in the case that we must to produce a large number of rows and / or columns, so that for the creation of data for all columns of the sheet we'll use a more flexible yet powerful solution: Excel macros.

Creating a Macro

To create a macro we will click on the View tab of the Excel ribbon, and within the Macros group we will click on the option of the same name, which opens the Macro dialog box, where we'll write the macro name: CrearDatosPoblacion.

Clicking the Create button opens the Visual Basic for Applications (VBA) window editor, so that we can start writing the code of the macro.

Within the procedure body CrearDatosPoblación, write the following code block, where first, clean up the cells of the spreadsheet where we are currently positioned. Then ask the user to enter through an InputBox dialog box, the number of rows to generate. After inserting the column title, we will introduce the first two values that start the series, which generate using the method Selection.AutoFill.

Option Explicit

Sub CrearDatosPoblacion()

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

'clean worksheet cells
Cells.Select
Selection.ClearContents

nFilaDestino = InputBox("Number of records to generate")
nFilaDestino = nFilaDestino + 1

'fila_id column
'---------------

'column caption
Range("A1").Select
ActiveCell.FormulaR1C1 = "Fila_ID"

'init values of series to generate
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "2"

'select init values range
Range("A2:A3").Select

'fill total range of cells
sCeldaOrigen = "A2"
sCeldaDestino = "A" & nFilaDestino
Selection.AutoFill Destination:=Range(sCeldaOrigen & ":" & sCeldaDestino), Type:=xlFillDefault

End Sub

To run the macro will select the menu "Run | Run Sub / UserForm", or press the key F5, filling the first column of the sheet with the number of values indicated in the InputBox.

Before continuing we'll save our work in VBA or Excel, bearing in mind that we must do it in a file of type "Excel Macro-Enabled Workbook (.xlsm)", what we will be notified by a dialog box in time to save. We will click "No" in that dialog and save our worksheet in a file named GenerarDatosPoblacion.xlsm.

Generating random data

The next column to create correspond to the age of individuals, in it, need to generate random values within a range of numbers, representing the maximum and minimum age that a person can have, for example between 0 and 120.

To create a random value between two numbers in a cell of Excel, we can use a formula with RANDBETWEEN function, that receives as parameter the above numbers, returning as a result the number generated.

Moving this functionality to the macro that we developed, we will add to it the next code block, which assign the formula expression RANDBETWEEN (0,120) to a cell in the second column. We will select that cell, and repeat the formula over a range of cells using the method ActiveCell.AutoFill.

'age
'----
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

Running the macro again, we'll get the new column with the age data.

Our next step is for the column of sex of individuals. Here we could have used numbers 1 and 2, also generated randomly, to identify male and female respectively, but let's get a little this operation, using instead the letters H and M as values for the column. So the problem now is how to randomly generate these letters in the cells of the column, since RANDBETWEEN only receives and returns numerical results.

The solution is very simple, as it also involves using RANDBETWEEN function, but combining it with the decision expression IF. We will pass to RANDBETWEEN the numbers 1 and 2 as parameters, and according to the result obtained, IF return the letter H or M. Below is the code block for this column, we will add to the macro.

'sex
'---
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

For the next two columns: code region of residence and country of origin code, we'll follow the same mechanics as the column ages, although using different numerical ranges.

'code region of residence
'------------------------
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

'country
'-------
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

Our next step is to create the data of an alleged registration date of individuals in this population system, a task that will make in two phases. First, we create each part of the date in separate columns.

'date elements:
'==============
'year
'---
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

'month
'-----
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

'day
'---
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

As we have seen, the day is the part of the date that requires further work, as we must be careful to avoid the generation, for example, of a day 31 if in the month column we have the value 2. We will solve this checking the month column in first place, and according this we'll use a different range to generate the day. So that in the formula we'll use several IF expressions combined with RANDBETWEEN functions.

In the second phase of this operation, we will create a final column in the spreadsheet with a date in a format understandable to SQL Server, as a result of the concatenation of the above columns: Anualidad, Mes and Dia.

It is necessary to take into account when composing the date in this way, we must add a zero to the month and day when these values are only one digit.

'date composition
'----------------
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

When we rerun the macro, we'll get the correctly formatted date in the last column. This time also establish the number of records to generate in a million, that way we will test the data creation power of our process.

To complete operations in Excel we'll save the file as "Excel Workbook (*.xlsx)". Dialog box appears again warning that we couldn't save macros in a .xlsx file, which we'll reply by clicking Yes.

At this point we conclude the first part of the article, in the second installment will explain how to transfer the data just generated to SQL Server.

Regards,

Demographic data generation in SQL Server

2012-04-30T14:47:00Z

This article addresses the challenge of develop a process to create a database with demographic information, which serves as a starting point for creating a data model in PowerPivot, used in the construction of the population pyramid shown in Population pyramids with PowerPivot. Preparing the data and Population pyramids with PowerPivot. Chart development.

Unlike the article "Generate SQL Server test data from Excel" (part 1 and part 2) also published in this blog, where we used Excel to generate dummy data population, this time we will work with real data, which represent a larger volume of information to manage with respect the above-mentioned article.

The current aim is to create a database containing a population table, in which each record represents an individual, with its corresponding age, sex and healthcare area.

Before proceeding, I want to express my gratitude to Ricard Gènova Maleras, a demographer specializing in population health analysis, part of the Health Service Reports and Studies (Directorate of Health Promotion and Prevention, DG Primary Care, Health Ministry CM) for their valuable advice in those key demographics for the development of this and the all related population pyramids published in this blog; and the other members of that Service: Jenaro Astray Mochales, Felicitas Dominguez Berjón, María Dolores Esteban Vasallo and Beatriz Elvira Rodriguez, for their support and assistance in all health concepts about population necessary for the proper focus of this article.

Last but not least, thanks to Enrique Barceló, fellow sufferer in this work of BI, for the interesting times we chatting about matters relating to the development of information systems in general and data cubes in particular, and for sharing his amazing knowledge of OLAP with the rest of the team.

Data collection and preparation

There are several Web sites belonging to different agencies, where we will find demographic information needed to perform our analysis, organized by criteria such as sex, age, geographic region, etc. Of all these agencies to highlight the relevant United Nations , National Statistical Institute (Spain), Institute of Statistics of the CAM (Madrid) and CELADE (demographics of Latin America and Caribbean). In all cases, in addition to direct consultation in the website, we have the ability to download information in various formats such as Excel, CSV, etc.

For the development of the examples in this article we'll use the registered population data from the Community of Madrid for the year 2010, classified by age, sex and health zoning, effective as of that year, found in the following link (T10Z2_SALUD10) from the website of the Statistical Institute of CAM.

Data extraction strategy

The Excel file pc10t10z2_salud10.xls obtained from the above link, organizes the data of population around three worksheets containing respectively, the values of total population, men and women.

Each worksheet has in its first two columns the code and name of the health care area, while the remaining columns contain population figures classified in five-year groups, where each group has a column with the total five-year and several columns representing the detail by simple age of the ages that make up the group.

The data we need extract are, first, the codes and names of health zoning, for which we can use any of the worksheets in the Excel file. Moreover, we have to get the total five-year age group in both sheets: male and female population.

We will bring all these values to a new worksheet in which we copy each combination of health area codes, age group and population type (male / female) in order of steps similar to the following figure.

Here we see an approximation of what would be the resulting spreadsheet.

Using select, copy and paste operations by hand to transfer the needed data to a new sheet can be a cumbersome and heavy task. That is why we propose to use a macro that automates all this work, allowing us to repeat the process as many times as we want, and apply it to other populations whose data is structured the same way.

To create the macro, on the Excel Ribbon will click on the "Macros" option, belonging to the same name group, located on the "View" tab.

This option is also available on the "Developer" tab, within "Code" group.

In the event that the "Developer" tab is not visible, we will click on the "File" tab, and within this on "Options". In "Excel Options" window will click "Customize Ribbon" and displaying the list on the right select the item "Main Tabs". In the panel below the list will check "Developer", which will make this tab visible in the ribbon.

Regardless the way chosen for its creation, the "Macro" window will appear, we'll give the name "TraspasarDatosPoblacion" to our macro and click "Create", which will lead to the VBA (Visual Basic for Applications) editor, where we write the code for the macro.

We will divide the work to carry out the macro into three parts: data creation for health area code column, age range column, and finally, the columns of numbers of population by sex.

In the next block of code we can see the instructions responsible of creating a new worksheet, which assign a name and column headings. Later we'll select the area codes from a source worksheet, which will paste in the new sheet many times as there age ranges.

Option Explicit

Private Const RANGOS_COLUMNAS As Integer = 20
 
Sub TraspasarDatosPoblacion()

' create new sheet and assign name
Dim sHojaDestino As String
sHojaDestino = "DatosBasePoblacion"
 
Dim oWorksheet As Worksheet
Set oWorksheet = Sheets.Add(After:=Sheets(Sheets.Count))
oWorksheet.Name = sHojaDestino
 
' assign column titles
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"
 
' back to source data sheet
Sheets("Hombres").Select
 
' set start and end data rows
Dim sFilaInicial As String
Dim sFilaFinal As String
sFilaInicial = "14"
sFilaFinal = "299"
 
' calculate number of existing zones
Dim nCantidadZonas As Integer
nCantidadZonas = (CInt(sFilaFinal) - CInt(sFilaInicial))
 
' zone codes
' ---------------
Dim sColumna As String
sColumna = "A"
 
' select codes, copy to clipboard and paste in a new sheet
Range(sColumna & sFilaInicial & ":" & sColumna & sFilaFinal).Select
Selection.Copy
Sheets(sHojaDestino).Select
 
Dim nContador As Integer
Dim nFilaVacia As Long
 
' do paste for each age group
For nContador = 0 To RANGOS_COLUMNAS
    ' obtain next empty row number from column
    nFilaVacia = FilaVaciaEnColumna(1)
    
    ' place in cell
    Range(sColumna & CStr(nFilaVacia)).Select
    
    ' paste data
    ActiveSheet.Paste
Next
 
' assign white color background to column
Columns("A:A").Select
Selection.Interior.Pattern = xlNone
Selection.Interior.TintAndShade = 0
Selection.Interior.PatternTintAndShade = 0

Before each assignment of values in cells, we need to know the empty row from which paste the data. For this, we'll use the auxiliary function "FilaVaciaEnColumna" to iterate the collection of rows in the sheet to find the row containing an empty cell in that column on which we are working.

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

The next phase in creating the macro will be to assign values to the column age ranges. So we'll create an array containing these values, and to cross it, in each iteration, the current range value will be copied the same number of times than the amount of existing health zones. Note that before assigning values to this column we will apply the text format (property Selection.NumberFormat), since otherwise, the default format may cause some values of the age ranges are interpreted as month-year.

' age ranges
' --------------
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+"
 
' assign format of age column to text
sColumna = "B"
Columns(sColumna & ":" & sColumna).Select
Selection.NumberFormat = "@"
 
' repeat each value of aRangosEdad array
' as times as number of existing zones
Dim vRangoEdad As Variant
 
For Each vRangoEdad In aRangosEdad
    ' obtain next empty row number from column
    nFilaVacia = FilaVaciaEnColumna(2)
 
    ' assign age range value in next two cells down
    Range(sColumna & CStr(nFilaVacia)).Select
    ActiveCell.FormulaR1C1 = vRangoEdad
     
    Range(sColumna & CStr(nFilaVacia + 1)).Select
    ActiveCell.FormulaR1C1 = vRangoEdad
    
    ' set cell range and fill with value of above cells
    Range(sColumna & CStr(nFilaVacia) & ":" & sColumna & CStr(nFilaVacia + 1)).Select
    Selection.AutoFill Destination:=Range(sColumna & CStr(nFilaVacia) & ":" & sColumna & CStr(nFilaVacia + nCantidadZonas)), Type:=xlFillDefault
Next

And we end the macro with the assignment of the columns dedicated to the population figures by sex, where this time use two combined arrays containing the column headings that correspond to the five-year total population and population type. When touring both in a nested way, for a certain type of population, we'll extract their numbers of individuals, assigning them to the destination worksheet.

' population figures by sex
' ----------------------------
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
 
' for each population type
For Each vTipoPoblacionColumna In aTiposPoblacionColumnas
    ' obtain information of population type:
    ' sex, target column, numeric position in target column
    sTipoPoblacion = Split(vTipoPoblacionColumna, ",")(0)
    sColumna = Split(vTipoPoblacionColumna, ",")(1)
    nPosicionColumna = Split(vTipoPoblacionColumna, ",")(2)
 
    ' iterate the columns with population figures
    ' and paste in new sheet
    For Each vColumnaPoblacion In aColumnasPoblacion
        ' place in sheet with source population data
        Sheets(sTipoPoblacion).Select
     
        ' select and copy cells
        Range(vColumnaPoblacion & sFilaInicial & ":" & vColumnaPoblacion & sFilaFinal).Select
        Selection.Copy
         
        ' place in target sheet
        Sheets(sHojaDestino).Select
        
        ' obtain next empty row, place in cell and paste data
        nFilaVacia = FilaVaciaEnColumna(nPosicionColumna)
        Range(sColumna & CStr(nFilaVacia)).Select
        ActiveSheet.Paste
    Next

Next
 
' change column style to display cell borders
Columns("C:D").Select
Selection.Style = "Normal"
 
Range("A1").Select
 
End Sub

Then create a new macro with the name "TraspasarDatosZonificacion" whereby we'll move to a new worksheet codes and names of the healthcare areas. These data will be used to load a table in the database we'll create later.

Sub TraspasarDatosZonificacion()

' create new sheet and assign name
Dim sHojaDestino As String
sHojaDestino = "DatosZonificacion"
 
Dim oWorksheet As Worksheet
Set oWorksheet = Sheets.Add(After:=Sheets(Sheets.Count))
oWorksheet.Name = sHojaDestino
 
' set column titles
Range("A1").Select
ActiveCell.FormulaR1C1 = "Zona_ID"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Zona_DS"
 
' back to data sheet
Sheets("Hombres").Select
 
' copy zone codes and names to clipboard
Range("A14:B299").Select
Selection.Copy
 
' back to target sheet and paste data
Sheets(sHojaDestino).Select
Range("A2").Select
ActiveSheet.Paste

' remove cell background color
Columns("A:B").Select
Selection.Interior.Pattern = xlNone
Selection.Interior.TintAndShade = 0
Selection.Interior.PatternTintAndShade = 0
 
Range("A1").Select
 
End Sub

When we copy the codes and names of the health zones to new sheets within the Excel file, the color configuration of the cells corresponding to these values is copied too. To achieve white background color in these cells, we use the following lines of code.

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

But if our version of Excel is prior to 2007 these lines will fail at runtime, so we must comment them to avoid being executed.

Finally, from the macro management window, we'll select each one and click on "Run".

As a result we will obtain two new sheets with the data generated by the macros.

Database creation

Our next step is to create the database PiramidePoblacion, to accommodate the information we have just prepared in Excel. The following code block shows the Transact-SQL script that will be executed in our SQL Server instance (in this article we have used 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

The table Poblacion will contain the main data of our population, while tables Zona, Edad and Sexo contain catalog information (code / descriptor).

Surely, in the script we will have noticed the lack of foreign keys between Poblacion table and the rest. This is an intentional forgetting that we'll fix in the articles Population pyramids with PowerPivot. Preparing the data and Population pyramids with PowerPivot. Chart development, where we demonstrate how using PowerPivot we can also create relationships between tables in the model.

After running the script, the first action that will take place in the new database will be transfer data from sheet DatosBasePoblacion of file pc10t10z2_salud10.xls into the table of the same name, using the following statement.

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

Generating population records in the database

The purpose of the process that we are doing so far is to generate, for table Poblacion, a number of records that represent the population with which we work, and that will equal the sum of columns Poblacion_H and Poblacion_M from table DatosBasePoblacion.

For this volume of data, using a loop to create one by one the records is totally unfeasible due to time and performance reasons, so we must find an alternative technique that allows us to work using sets of results, to create the necessary number of records, using a small number of operations.

The technique that we will opt is shown in the book Microsoft SQL Server 2008: T-SQL Querying , by Itzik Ben-Gan and other great names in SQL Server: Lubor Kollar, Dejan Sarka and Steve Kass; and is use several "Common Table Expressions" or CTE nested, through which generate a result set of appropiate size, that we will combine with an INSERT INTO statement, to add to Poblacion table a determined number of records. All this will include in the following stored procedure.

CREATE PROCEDURE GenerarRegistrosPoblacion
@nZona_ID AS int,
@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 @nZona_ID, @nEdad_ID, @sSexo_ID 
FROM NumerosTotal
WHERE NumeroFila <= @nPoblacion;
 
END
 
GO

Then write a script which will cross the DatosBasePoblacion table, and each of their records will take the value of Poblacion_H and Poblacion_M fields, inserting in Poblacion table, by executing the stored procedure GenerarRegistrosPoblacion, a number of rows equal to value of the aforementioned fields.

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

The time spent in the execution of this script was 1 minute and 37 seconds in a virtual machine equipped with a Core 2 Duo CPU and 1.5 GB of RAM, which represents excellent performance given the large number of records added to Poblacion table.

We ended the preparing database operations with the statements used to add data in tables that serve as catalogs for the table Poblacion. As we see below, for the Zona table we also get the records from pc10t10z2_salud10.xls file.

--////
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')

Data loading completed

At this point we conclude the process of creation and loading of demographic information in our database. In the articles pointed out at the beginning, we will use this database as a starting point for the construction of population pyramids with PowerPivot.

La importancia de las relaciones en los modelos tabulares de SQL Server 2012

2012-04-15T14:15:00Z

Una característica esencial de los modelos de datos tabulares pertenecientes a BISM (Business Intelligence Semantic Model), el nuevo paradigma de desarrollo BI incorporado en SQL Server 2012, lo constituyen las relaciones entre las tablas integrantes de un determinado modelo, ya que sin ellas resultaría imposible realizar un adecuado análisis de la información contenida en el mismo.

Pongamos como ejemplo que tenemos una base de datos con el nombre AdvWksDW, creada a partir de algunas de las tablas de la base de datos AdventureWorksDW2012, pero sin las correspondientes relaciones entre las mismas. El siguiente bloque de código muestra las sentencias SQL necesarias para su creación.

CREATE DATABASE AdvWksDW

USE AdvWksDW

SELECT * INTO FactInternetSales FROM AdventureWorksDW2012.dbo.FactInternetSales

SELECT * INTO DimDate FROM AdventureWorksDW2012.dbo.DimDate

Para aquellos lectores más familiarizados con el trabajo con modelos de análisis multidimensional, si tuviéramos que desarrollar un cubo OLAP empleando esta base de datos, la tabla FactInternetSales representaría a la tabla de hechos, ya que contiene las columnas a partir de las cuales podemos obtener las medidas o resultados numéricos a analizar; mientras que la tabla DimDate representaría a una tabla de dimensión, empleada para filtrar la información por las fechas existentes en el modelo.

A continuación crearemos un proyecto de análisis de tipo tabular con SQL Server Data Tools (SSDT); el entorno de desarrollo para SQL Server 2012 basado en Visual Studio 2010, conocido en anteriores versiones como Business Intelligence Development Studio.

En el modelo de datos de este proyecto incluiremos las tablas de la base de datos que acabamos de crear, añadiendo también una medida que sume los valores de la columna SalesAmount.

Para analizar este modelo desde Excel seleccionaremos la opción de menú de SSDT "Model | Analyze in Excel", o bien haremos clic en el botón de la barra de herramientas que desempeña el mismo propósito.

Esta acción tendrá como resultado la apertura de una nueva hoja de cálculo que mostrará el modelo de datos en una tabla dinámica, dentro de la cual situaremos la medida "Sum of SalesAmount" de la tabla FactInternetSales en el bloque Valores, y el campo CalendarYear de la tabla DimDate en el bloque "Etiquetas de fila".

El resultado que necesitaríamos obtener de esta tabla dinámica sería la suma del campo SalesAmount, perteneciente a la tabla FactInternetSales, agrupada por los años de venta, es decir, por el año del campo OrderDateKey. Puesto que contamos con la tabla DimDate como catálogo de fechas, si combinamos en una consulta SQL ambas tablas por los campos de fecha adecuados, obtendríamos las cifras esperadas.

Sin embargo el resultado obtenido en la tabla dinámica es muy distinto, ya que todas sus celdas muestran el mismo valor: el total de la suma del campo SalesAmount. Esto es debido a la inexistencia de una relación entre los campos OrderDateKey y DateKey de las tablas FactInternetSales y DimDate respectivamente, lo cual produce que el motor del modelo tabular no sepa cómo aplicar la medida "Sum of SalesAmount".

Relaciones en el modelo tabular

En escenarios como el que acabamos de describir es donde nos damos cuenta de la importancia que cobra un adecuado establecimiento de relaciones entre las tablas de nuestro modelo, a fin de conseguir un óptimo análisis de la información.

Un modelo tabular obtiene, en el caso de que existan, las relaciones directamente de la fuente de datos origen durante la ejecución del asistente de importación de datos. A las relaciones obtenidas de esta forma las denominaremos relaciones automáticas.

Si el modelo carece de relaciones, o bien necesitamos relaciones adicionales a las ya existentes, podemos crearlas mediante la ventana de administración disponible a tal efecto en el entorno de desarrollo de Visual Studio 2010; llamaremos a este tipo relaciones manuales.

En nuestra situación actual, para que la información del modelo resulte coherente, necesitamos, como hemos indicado antes, crear una relación entre las tablas FactInternetSales y DimDate, por los campos OrderDateKey y DateKey; para ello seleccionaremos la opción de menú "Table | Create Relationships", que abrirá la ventana en la que realizaremos dicha operación, seleccionando las tablas y campos integrantes de la relación.

Una vez creada la relación volveremos a la tabla dinámica de Excel en la que estamos analizando el modelo, y después de hacer clic en la opción Actualizar (grupo Datos de la pestaña Opciones, contenida en la pestaña de nivel superior "Herramientas de tabla dinámica") se producirá una nueva lectura de los datos del modelo por parte de la tabla dinámica, refrescando su contenido, y haciendo que las cifras de ventas por año se muestren ya correctamente.

El estado de la relación. Relaciones activas e inactivas

En un modelo de datos puede suceder que una tabla en la que hemos definido medidas, varios de sus campos se relacionen con un mismo campo de otra tabla que vamos a utilizar para filtrar los datos de la primera. Cuando esto ocurre, ¿cómo podemos saber qué campo de la tabla de medidas se está relacionando con la tabla de filtro al analizar el modelo por ambas tablas? Para aquellos lectores con experiencia en el desarrollo de cubos OLAP, la pregunta a formular sería: ¿qué campo de la tabla de hechos se está relacionando con la tabla de dimensión?

La respuesta a esta pregunta la hallamos en el estado de la relación (activa o inactiva), y la mejor manera de ilustrar este concepto es a través de un ejemplo. Por consiguiente, vamos a crear un nuevo proyecto tabular en SSDT, cuya fuente de datos sea esta vez la base de datos AdventureWorksDW2012, importando en el modelo, al igual que en el ejemplo anterior, las tablas FactInternetSales y DimDate; y definiendo igualmente una medida con la suma de la columna SalesAmount.

Al analizar el modelo en Excel, situando el campo CalendarYear en las etiquetas de fila, los números resultantes corresponderán a las ventas realizadas por fecha de factura.

Que serán las mismas cifras que obteníamos de la consulta SQL presentada en un apartado anterior.

Llegados a este punto es donde el lector se preguntará con toda seguridad: "¿Y cómo sabe el modelo que yo quiero obtener la suma del campo SalesAmount en base al campo OrderDateKey, y no en base a DueDateKey o ShipDateKey, que también son de tipo fecha?"

Realmente, el modelo no lo sabe, sino que se limita a utilizar la relación activa del modelo. Expliquemos esto con mayor detalle:

Si observamos el diseñador del modelo en vista de diagrama, veremos que desde la tabla FactInternetSales parten tres relaciones hacia la tabla DimDate. De las flechas que indican visualmente las relaciones, la que tiene el trazo de línea continuo corresponde a la relación activa, y por lo tanto, será la que se utilizará de forma predeterminada cuando se consulte el modelo empleando el campo CalendarYear de la tabla DimDate. Al hacer clic en dicha relación, la ventana de propiedades del entorno de desarrollo de Visual Studio mostrará el detalle de la misma. Nótese que la propiedad Active tiene el valor True.

También podemos averiguar cuál es la relación activa, y por ende, las inactivas, seleccionando en Visual Studio la opción de menú "Table | Manage Relationships", que abrirá la ventana de administración de las relaciones del modelo, donde la columna Active nos informará de este aspecto en particular.

Consultando el modelo a través de una relación inactiva

La cuestión a plantear ahora sería cómo podríamos interrogar al modelo, de forma que nos proporcionara la suma del campo SalesAmount, pero utilizando alguno de los otros dos campos de fecha (DueDateKey o ShipDateKey) que también están relacionados con el campo DateKey de la tabla DimDate. Dicho con otras palabras, lo que necesitamos es alterar el contexto de filtro predeterminado actualmente activo.

La solución pasa por crear una nueva medida, que también sume el campo SalesAmount, pero indicando de alguna manera que el campo de agrupación va a ser uno distinto del predeterminado. Por ejemplo, si queremos obtener los resultados de ventas por el campo ShipDateKey, usaremos la siguiente expresión DAX:

VentasPorFechaEnvio:=CALCULATE( SUM([SalesAmount]) ; USERELATIONSHIP(FactInternetSales[ShipDateKey]; DimDate[DateKey]) )

Para añadir esta medida al modelo de datos, dentro del área reservada a las medidas haremos clic en una celda vacía debajo de la columna SalesAmount, y escribiremos la anterior expresión en el panel de introducción de fórmulas/expresiones.

Analicemos esta expresión por partes: debemos emplear la función SUM(), ya que nuestro objetivo principal es sumar el campo SalesAmount, pero como queremos que al agrupar la suma, se utilice el campo ShipDateKey, mediante la relación de éste con el campo DateKey de la tabla DimDate, tenemos que especificar explícitamente dicha relación porque no es la activa, y esto es lo que hacemos con la función USERELATIONSHIP(). Ahora bien, precisamente por el hecho de utilizar una relación que no es la activa, necesitamos alterar el contexto de filtro actual, lo que conseguimos usando la función CALCULATE().

Como hemos hecho en ejemplos anteriores, si trasladamos esta situación a una consulta SQL, para comprobar que los valores obtenidos son los mismos, la sentencia quedaría tal y como vemos a continuación.

Conclusiones

En el presente artículo hemos realizado una introducción a las relaciones en los modelos tabulares del nuevo modelo semántico para Business Intelligence incorporado a SQL Server 2012. Dicha característica representa una pieza fundamental en la construcción de sistemas de análisis empleando esta tecnología, por lo que confiamos en que este artículo sirva de ayuda al lector para iniciarse en el desarrollo de sus propios modelos de análisis utilizando BISM. Los ejemplos elaborados a lo largo del artículo están disponibles en este enlace.

Population pyramids with PowerPivot. Chart development (and 2)

2012-04-01T11:06:00Z

In the first part of this article we developed a data model in PowerPivot representing population figures by age and sex. In this second installment will shape these figures on a chart shaped population pyramid.

Pyramid chart. First approach

In its current state, the pivot table already have enough information (figures by population, age and sex) to try to create a chart that represents a population pyramid, but we advance the reader that in this first approach we will not get the desired effect.

Positioned in the pivot table, from the Excel ribbon, select the option "PivotChart" belonging to "Tools" group from "Options" tab, which in turn is contained in the top-level tab "PivotTable Tools".

This selection opens "Insert Chart" window, which contains all available chart types. Here we will realize that there is no specific template for creating a pyramid chart, therefore, of all bid at our disposal we choose, within "Bar" category, the "Clustered Bar" type, which as we shall see later, it will be the best adapted to the result we want achieve.

Accepting this window, the chart will be created from the PivotTable data, and as we anticipated, the result won't look like the image presented in the first part of the article.

However, the main difference lies in the orientation of the men bar population, which should be left. All other aspects are basically visual configuration issues, which explain how to solve in short.

Solving the trajectory of the population bars

Focusing on the male population bar, the solution for achieve to draw itself in the opposite direction to the current, is to put in negative the values of the cells in the pivot table for this segment of the population.

If we were in a simple spreadsheet with no connection to PowerPivot, the solution is as simple as editing the cells in column Hombre, changing their values to negative, but in this scenario data is being obtained from the PowerPivot data model, so that it's not possible directly edit the values in the PivotTable.

To solve this kind of problems we have to resort to the creation of columns and / or calculated measures, which through DAX expressions provide the results we need. In the transition to negative the values of the Hombre column, we will open the PowerPivot window, and placing ourselves in the first available empty column in the Poblacion table, write the following expression in the formula bar:

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

We just created a calculated column that will be evaluated for each row of the Poblacion table, checking if Sexo_ID field value is equal to the letter "M" (Mujer), if so, the column value in that row will be 1, otherwise, when the field contains "H" (Hombre), the returned value will be -1.

Then we double-click its header to assign Sexo_Codigo as the column's name it. We can also give name by right clicking on the header and choosing "Rename Column".

Turning again to the Excel window, we'll remove the chart we created in the worksheet and uncheck the RecuentoPoblacion measure, leaving empty the area of the PivotTable values.

The next step will be to create a new measure named SumaPoblacion, based on the following DAX expression:

=SUM([Sexo_Codigo])

By applying this measure to the PivotTable, the SUM function takes the sum of column values passed as parameter, so the population figures of men now appear negative. This means that when re-creating the chart in the manner explained above, the indicator bars of the population values by sex are now drawn in opposite directions. As an additional feature, on "PivotTable Tools" tab, into "Design" tab, at the "Layout" group, we'll drop "Grand Totals", selecting "On for Columns Only", that hides the row totals column, because its presence in this context is irrelevant.

Visual configuration of population bars

Although the chart bars are now shown with the effect we wanted, it would be desirable a few tweaks in its visual appearance to improve the presentation quality.

First we right click on the age ranges labels, selecting "Format axis". In the window of the same name, under "Axis Options", assign the value "Low" to "Axis Labels" property, which align the labels column to the left of the chart.

Then we right click on any of the bars in the graph, choosing "Format Data Series". In the configuration series window, into "Series Options," on property "Series Overlap" will move the position marker to the right end position (fully overlapped), while in property "Gap Width" will move the position marker to the left end (no gap at all). In this way we will accomplish the bars increase their thickness and eliminate the space between them, being completely joined to form the population pyramid.

Calculating the population percentages

So far, the data representation obtained, both in the pivot table as in the chart pyramid is based on absolute population numbers. However, the usual practice is that such representation is made as a proportion of each age group and sex on total population.

For example, in our pivot table, the women population aged between 55 and 59 is 184,888 people; to get the percentage that this population group is in relation to all individuals with whom we are working (6,458,684), we divide the group by the total, and format the result as a percentage, gaining 2.86%.

If we want the PivotTable to perform this operation for all population groups, we'll have to add additional calculations in the form of measures, but before that we'll remove the current population chart, because we will build it again from one of the new measures.

We start out the existence of a measure, SumaPoblacion, which as we know, it returns the number of people adding the field Sexo_Codigo. The next step is to create a new measure, which included in the pivot table, provide the total population in all cells.

Our first reaction might be to reuse the RecuentoPoblacion measure, created in the early stages of our example, but soon we realize that it is useless for this purpose, because although this measure counts the Poblacion table rows, the cells result are affected by the fields used in rows and columns, as well as other active filters in the PivotTable.

For a measure always count all rows in a table, regardless of the filters may be active, we'll use the CALCULATE function, which as the first parameter pass the operation to perform, in this case the count of rows in the Poblacion table using COUNTROWS function. Then pass as many parameters as filters we want to remove, using the ALL(TableName) function for each table that is, somehow, acting as a filter.

Under such assumptions create a new measure called TotalGlobalPoblacion, with the following DAX expression:

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

When applied this measure to the PivotTable, all the cells show the same value: the total population.

Now we need a third measure that makes the division between the two previous and display the result in percentage format. This new measure will have the name PorcentajePoblacion and use the following formula:

=[SumaPoblacion] / [TotalGlobalPoblacion]

As we saw in the first installment of the article, to apply the formatting to this measure will right click on one of its cells choosing "Number Format". In the format window this time select the "Custom" category, through which we introduce in the "Type" field the following format string.

0,00%;0,00%

This string will format the number as a percentage, and show the male population columns values without the negative sign, but internally, these values will remain negative.

Next deactivate all PivotTable measures except PorcentajePoblación, which will be the only one that remains visible. Then again add a clustered bar chart, using the configuration steps outlined above, and adding new format features to improve their presentation.

Apply percentage format to the horizontal axis

Firstly we will right click on the horizontal axis labels by selecting "Format axis", which will open the format window. In "Number" section, select the format category "Custom", and in the "Format Code" field, write the following format string:

0%;0%

By clicking the "Add" button, the string is added to the list of custom strings. Accepting the window, the format will apply to the horizontal axis labels.

Emphasizing the population bar edges

Then we will right click on one of the bars in the chart, choosing again "Format Data Series". This time, in "Border Color" section, we will click "Solid Line", selecting black color; while in "Border Styles" assign the value "2 pt." in property "Width". This operation will make for both groups of bars in the chart.

Then we right click on the labels of the age ranges, selecting "Format axis". In this format window assign the same values for the color and border style properties that just used to the chart bars.

As a result of these actions, the chart will show the edges with an outline clearly highlighted.

Relocating the legend position

At the time of chart creation, Excel puts the legend (Sexo_DS field) on the right side by default. However, it is possible to change the location of this element if we want to provide more space to the drawing of population bars. To do this, we right-click the legend and select "Format Legend" in the format window, under "Legend Options", we will click "Top".

As we can see, the chart has won drawing surface, but the legend indicators have been placed in reverse order with respect to the bars. To solve this problem we will click on the legend (Sexo_DS), dropping a menu of filter options, which will select "Sort Z to A".

With this action, the legend indicators shall be placed properly, but now we will find that the colors of the bars have been reversed, and lost the edge of the chart bars.

We will restore the edges of the bars in the manner explained above, whereas in terms of colors, for each side of the pyramid do a right click on one bar and select "Shape Fill", changing the current color which originally had the chart.

To complete the adjustments we are making about the legend, drag it until it is situated at the same level of the upper element of the pyramid, and will increase its width, so that the indicators are further apart.

Clear the fields and add title

Then we right click on any of the fields of the chart buttons and select the option "Hide All Field Buttons on Chart". In this way will prevent the user to apply filters on the horizontal and / or vertical pyramid axis, keeping a solid structure thus avoiding the possibility, for example, to hide age ranges either sex. However, this filter feature will still exist from the PivotTable.

Moreover, in the tab group "PivotChart Tools" we will click on "Layout", and within the group "Labels" will click on "Chart Title", which will drop several items where we will choose "Above Chart", adding a text box to the chart, we will edit to give it a title. At this point we have completed the development of our pyramid.

Energizing the data pyramid data through slicers

Although we have achieved the stated goal of creating a population pyramid, it would be interesting to enrich the information that actually offers as we do in this section.

Looking at the data model in the PowerPivot window, we shall realize that we haven't use health zoning information yet, so we can use these data to construct a filter that displays the pyramid based on the population concerning one or more of these health areas.

PowerPivot pivot tables, in addition to traditional filter, incorporating a new filter type called "slicer", in addition to the usual filtering functionality provides a more flexible user interface for handle the values to work with.

Let's create one slicer on the pyramid based on zone information. To this end, on the field list panel, drag the field Zona_DS from Zona table to "Slicers Horizontal" block, resulting in a slice located above both the pivot table and pyramid chart.

To filter data by slicer simply have to select the name of the health area we want to use as a filter. It is also possible to filter several areas simultaneously holding down the Ctrl key while clicking the areas composing the filter (as shown in the figure below).To remove all active filters will click on the icon located in the upper right corner of the slicer.

And at this point we concluded the article, in which its two parts we have shown how to construct a population pyramid in Excel 2010, using PowerPivot as a management tool for population data. However, the power of this technology goes beyond the mere treatment of demographic information, covering its scope to any environment where we have to make an analysis with large volumes of data.

Population pyramids with PowerPivot. Preparing the data (1)

2012-03-26T16:56:00Z

A population pyramid is a tool that allows to analyze the status and evolution of a population based on age and sex. This is a feature in demography and statistics, but also cover areas such as health, education, business, etc. For this reason, its integration in an computing information system belonging to one of the areas just mentioned, involves a substantial enrichment in the quality of the results obtained by the users of such systems.

This article will discuss the construction of population pyramids using PowerPivot, an add-on to Excel 2010 that enables access to large volume data sources and its subsequent management and analysis.

As we mentioned in the article Generation of demographic data from SQL Server, also published in this blog, our current goal (using the PiramidePoblacion database created in that article) is to construct a chart representing a population pyramid similar to what we see in the figure below.

The first part of the article is devoted to the preparation of PowerPivot data model: connection to a data source, data load and pivot table creation to analyze the population figures. The second part will devote to the construction of the chart that represents the population pyramid, starting from the data on which we worked on the first delivery.

As in the previously mentioned article, I want to thank again the members of the Health and Studies Reports Service (Directorate of Health Promotion and Prevention, DG Primary Care, Health Ministry CM): Jenaro Astray Mochales, María Felicitas Dominguez Berjón, María Dolores Esteban Vasallo, Beatriz Elvira Rodriguez and especially to Ricard Gènova Maleras, for the support and guidance received on demographic concepts necessary to be able to properly develop a population pyramid using Excel 2010 in combination with PowerPivot. Besides all this, Ricard has kindly agreed to do a great job of reviewing this article, so the gratitude goes twice.

I would also like to thank to Enrique Barceló for all his great tricks about OLAP development, shared with the team, making a little easier our development work on all these business intelligence tasks.

A little theory

But before to jump into its creation process, we will provide some brief theoretical notes on population pyramids that allow us to better understand their main features and the information we can collect and analyze from them.

In essence, a population pyramid is a double histogram that shows the distribution by age and sex of the individuals from a population, either in absolute figures or as percentage of the total population.

If we are interested in obtaining more detailed information about the conceptual aspects surrounding population pyramids, Epidat's 4.0 documentation can be useful. This is an software application developed by the Ministry of Health of the Xunta de Galicia and the Pan American Health Organization (OPS, Washington), from whose documentation, we quoted below some of the most important points concerning the issue at hand. First start with some ideas related to the construction of the pyramid.

"The best way to visualize the distribution by sex and age of a population is, without doubt, the population pyramid, a true icon of demography. A pyramid is a double histogram that allows, at a single glance, to draw up a clear idea not only about the general characteristics of the population (young, aged, with some imbalance by gender) but also specific features which refer to a particular event relative to the population under study.

Usually the population pyramids are constructed following several conventions and rules:

- To represent men at left and women at right of the central axis.

- Locating ages so as to be lower, the closer are the base and vice versa.

- Maintain a certain proportionality between the base and height (3 widths by 2 levels, or 4 by 3, approximately).

- Respect the same scale on both sides of the central axis (to facilitate comparison between sexes).

- Represent the weight of each age interval by the area of each histogram bar, not by its length, which is especially important when working with mixed age groups.

- Can be built with absolute values, but preferably with the proportions of each age and sex of the total population.

The last point is important and differentiates the pyramid structure indicators [proportion of young and old, aging index, etc.], which are calculated separately on the total for each respective sex. In the case of the population pyramid, using the total population of both sexes gathered, as the denominator of the proportions, not only ensures the comparability of age distribution, but also by sex."

And we will conclude with those items relating to the pyramid's interpretation.

"A pyramid by sex and age summarizes the demographic history of a population of at least a hundred years before the reference date (the time it takes roughly one generation to move from the base to the top of the pyramid). Sometimes, the indirect effect of some demographic events is visible beyond the leap of a century (for example, the impact of the sharp decline in fertility in recent decades experienced by many populations will be appreciated, no doubt, in the pyramids of the first half of the XXII). A simple ages pyramid [age by age: 0, 1, 2, 3 ... 99, 100 ...] allows a more accurate analysis than other age groups on aggregate (five-year, ten-year) but also suffers the risk of being affected by quality problems in the records, or be vulnerable to instability of the distributions in small populations.

The first perception of a pyramid allows to identify the general features of the represented population: a broad-based pyramid that narrows quickly gives an idea of a young population with a high proportion of children and adolescents, adults and elderly low, resulting of high birth and death. Conversely, a pyramid with a narrow profile at the bottom and wide at the top and the middle represents a mature or aged structure. The higher the life expectancy of a population is, most often the inequality by gender is reflected at the top of the pyramid (more full on the side of women, due to their highest level of survival).

The pyramid does not provide answers for itself, but conducive to the relevant questions arise. The explanations for their profile must be found in historical, social, political and economical events and tendencies that generate consequences in demographic future -that is, in fertility, mortality and migration, which are the phenomena that shape the contours and set the size of a population- and have an interpretation in terms of the triple time perspective: age, period and cohort."

Incorporating data to PowerPivot

Thanks to VertiPaq, the PowerPivot's data processing engine, we can achieve unprecedented power and speed in handling, filtering, creating expressions of business logic, and ultimately, in all the analytical operations that we perform on a data model created with this tool.

The following link provides access to PowerPivot's web site, where we can download it, and then install it on our machine, so that we can follow the examples from this article.

When PowerPivot installation concludes, we will start Excel 2010, creating a new worksheet with the name PiramidePoblacion.xlsx. In the ribbon will have now a new tab with the name "PowerPivot". Clicking on it will select "PowerPivot Window " belonging to the "Launch" group, which opens the aforementioned PowerPivot window, where define the table structure we will use, or "data model" as referred in PowerPivot development context.

Then in PowerPivot's window "Home" tab, we'll connect to the database using "From Database | From SQL Server" option, from "Get External Data" group.

This will start the "Table Import Wizard", where indicate the data source we want to connect.

A valid data source to develop a population pyramid must have at least information about sex and age of the population, as is the case in PiramidePoblacion database. Additionally, as a way to enrich the analysis to be performed, the database may have additional information as may be the health zoning, population nationality and so on.

After selecting the PiramidePoblacion database, the next step will provide a list of tables and views that we could import, of which will mark the following: Edad, Poblacion, Sexo y Zona, beginning the import by clicking on "Finish".

If the import process runs correctly, the wizard will display a summary of the process window.

After the data entry concludes, we will click "Close" to return to the PowerPivot window, where we find the imported tables arranged in several tabs.

Analysis using PivotTable

Before go back to the construction phase of the population pyramid, we will use a PivotTable to analyze the figures from the PowerPivot data model, so placed in its working window, we will click on "PivotTable" option, from "Reports" group.

This will position us in the Excel window, which opens a dialog where we choose the worksheet that will accomodate the PivotTable.

In our case, selecting the first option and accepting the dialog box, will create the PivotTable on a new worksheet.

From the "PowerPivot Field List" panel we have at our disposal the fields from the data model tables, which will locate in different areas of the PivotTable (row labels, columns, values, etc.) to carry out our analysis operations. We will use for this a very similar style of work to that we would employ if we were querying an OLAP cube, since the management of the PowerPivot fields here is similar to what we can do with the dimensions and measures contained in a data cube when consulted from Excel.

Let's start with a simple query, consisting in a record count of the Poblacion table, grouping the information by age group, which will place in the PivotTable rows.

We'll make the record count through a DAX expression (the PowerPivot query language) located in a calculated measure, which will create by selecting "New Measure", belonging to the "Measures" group in the "PowerPivot" tab. We'll apply this measure on the Poblacion table, so we must be previously positioned on it, within the field list panel.

Selecting this option will open up the "Measure Settings" dialog, and in the formula text box we will write the following expression:

=COUNTROWS(Poblacion)

COUNTROWS, as its name suggests, is a function that counts the rows of the table passed as a parameter. To finish creating our measure, give it the name "RecuentoPoblacion" and click "OK".

Right after creating the measure, it is automatically added to Poblacion table field list and in the "Values" block, showing the table total number of rows in the pivot table.

Then select in the field list pane, the Edad_Grupo field from Edad table, which automatically placed it in the block "Row Labels". If this is not the behavior obtained, we'll manually drag the field and drop in that block.

As a result of the previous action the pivot table will be updated, showing the Edad_Grupo field values in the rows axis. Regarding figures from RecuentoPoblacion measure, they should be distributed among the sections of each age, to reflect the number of records (population) corresponding for each of these sections. However, as shown in the figure below, this is is not happening, because the measure shows the same value for all rows, which is incorrect.

Relationships between tables in the model

Observing the field panel we see that PowerPivot already noticed the problem just discussed, because on top of the panel displays a notice, which informs us that it may be necessary relationship between the tables that are being used to compose the pivot table query.

This problem would not have occurred if there had been the necessary relationships between tables in the database, but as we said in the article on the creation of the database population, such relations were not created intentionally in order to demonstrate that it is also possible do it so from PowerPivot, as discussed below.

To let PowerPivot automatically detect the needed connection, we will click the "Create" button next to the notice displayed on the panel fields. As a result, a window will open to create the appropriate relationship, offering additional information on it through the "Details" links.

After relationship creation, it will be applied immediately on the pivot table without any user intervention, so it will properly display measure values grouped by age.

It's also possible to manually create relationships between tables in the data model using the PowerPivot working window. To do this we will click the "Create Relationship" option, that belongs to the "Relationships" group, which is located in the "Design" tab of that window.

This will open a window which will select the table and column representing the source and destination of the relationship.

This way we will establish two new relationships, which have the Poblacion table as the source and Sexo and Zona tables as a destination. Additionally, using "Manage Relationships" we can see a summary of the relationships created and manage them (create, edit, delete, etc.).

These changes just make in the PowerPivot window may affect directly or indirectly the data we are presenting in the pivot table; for that reason, when we return to the Excel window, will see a notice on the field panel, where we click the "Refresh" button to update the data we're working with.

Adding data to the column axis

Fixed the relationships problem, now is time to add new data in the pivot table columns through Sexo_DS field from Sexo table.

When selected, this field is placed by default on the "Row Labels" blocks so we'll have to manually move it to "Column Labels" block, or directly drag it to the label column block.

Additionally, we will format the numeric cells either by right-clicking any of them and selecting "Number Format" in the Format dialog box, where we'll define it without decimal and with thousands separator.

After these operations, the PivotTable will already display population data according to the requirements posed.

Now we conclude the first part of this article. In the next installment we will reach our goal of creating a chart that represents the population pyramid using the data we have just prepared with PowerPivot.

Superposed population pyramids with PowerPivot

2012-03-15T22:22:00Z

In the articles that were dedicated to the creation of a population pyramid using PowerPivot ( part1 and part2 ), we saw the power that this add-on for Excel provides to users responsible for conducting information analysis on a database of demographic content.

Continuing the line of work depicted in those articles, this time we will address the development of superposed population pyramids, one aspect of the work with population pyramids, which is to observe and analyze the differences by sex and age structure between two populations by direct comparison of their respective pyramids.

Before proceeding I want to reiterate my thanks to Ricard Gènova Maleras, a demographer specializing in population health analysis, belonging to the Reports and Studies Health Service (Directorate of Health Promotion and Prevention, DG Primary Care, Health Ministry CM) for their invaluable review work and guidance on all those concepts about creating population pyramids, which have proved so valuable in the development of this and previous articles we have published about this topic in the blog.

Brief introduction to the superposed population pyramids

If we want to graphically describe this working scenario, firstly, we take the population pyramids to compare ...

... and then place a pyramid on the other, so that we can measure and analyze population differences between them, ending the operation.

If we are managing two static populations, the task is more complex than solely fill the cells in the Excel sheet with the corresponding population figures, and prepare the pyramid-shaped chart that combines the values. However, suppose we have a population base or reference, belonging to all the individuals from a region, and we want to make comparisons between this amount and a classification of the subgroups in which this population can be divided, for example, the geographic or health care areas.

If the number of zones is very high, this will increase the amount of calculations and subgroups pyramids that we will need developing to compare with the main reference pyramid, increasing the complexity of demographic analysis to be performed.

At this point of the problem, PowerPivot enters the arena, because if we use to manage population data we must work with, greatly simplify the development of population pyramids and subsequent analysis phase.

Taking advantage of the pyramid developed in the items listed above, and reflected in the PiramidePoblacion.xlsx file, we will use that work setting as a starting point to develop, in first place, the population pyramids of different health areas, and continue with the reference pyramid creation, that depicts the Community of Madrid population.

The zoning pyramids

At first glance, this task may seem the most complicated, because its purpose is to create a pyramid for each one of the health area of the population we are dealing with.

Nothing is further from reality however, because if we followed the early commented articles about pyramids creation, we will have realized that we already have that work done in the PiramidePoblacion.xlsx file, created as an exercise of such of those articles, due to the use of a slicer based on the table Zona, from the PowerPivot data model.

However, in order to reorganize the names we use in this article, we will remove the measures and pyramid chart we had originally created for the PivotTable. Also change in the window PowertPivot, the table name Poblacion to PoblacionZonas, by right-clicking on the tab for the table, and choosing the menu option "Rename".

Next we will create again the measures for the PoblacionZonas table with the following names and expressions in DAX language.

/ / PoblacionZonasSuma

= SUM ([Sexo_Codigo])

/ / PoblacionZonasTotal

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

/ / PoblacionZonasPorcentaje

= [PoblacionZonasSuma] / [PoblacionZonasTotal]

The way to create and configure the pyramid chart for these populations will be the same as in the articles already mentioned, except the edge of the bars, which in this case be assigned the color white, with a width of 2 points between thereof. Let's recall that to format the bars will right click on any of them, selecting the menu option "Format Data Series".

In the figure below we can see the chart with the changes just discussed (including the slicer by the field Zona_DS from Zona table), showing the pyramid from one of the available areas.

The reference population pyramid

The next step is to add to the current chart the pyramid representing the total population, so we will place on the PowerPivot window, where we will repeat the process of adding the Poblacion table from source database to the PowerPivot data model, but this time giving the name PoblacionReferencia to the PowerPivot table, and also creating the calculated column Sexo_Codigo using the following DAX expression: "= IF ([Sexo_ID] =" M ", 1, -1)" cause we need to sum up the population according to sex of individuals.

After this operation we will create the necessary relationships between both population tables and the rest, obtaining the result shown in the figure below.

Then return to the Excel window, where we click the "Refresh" button in the "PowerPivot Field List" window to refresh its content, and incorporate the PoblacionReferencia table that we just added to the PowerPivot window.

With respect to the necessary measures to generate the pyramid corresponding to this reference population, the following code block shows the DAX expressions used for its creation.

/ / PoblacionReferenciaSuma

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

/ / PoblacionReferenciaTotal

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

/ / PoblacionReferenciaPorcentaje

= [PoblacionReferenciaSuma] / [PoblacionReferenciaTotal]

Comparing the measures from the tables PoblacionReferencia and PoblacionZonas, we find in those a significant difference in the operations involved in sum and calculate the total population, consisting in the use of the ALL function applied to the Zona table, which is passed as parameter. Into the measure PoblacionReferenciaSuma we will use, furthermore, the CALCULATE function, so that the addition operation on the reference population table is carried out correctly.

The ALL function, when operating in a DAX expression used to construct a calculated measure that we use, for example, in a PivotTable, cancels those filters applied to the PivotTable, which are related to table passed as a parameter to the ALL function, and in our case, as mentioned above, affects the Zona table.

To verify this behavior, that will result in the pyramid of reference at the same time, in the PowerPivot fields window, drag the PoblacionReferenciaPorcentaje field to the Values pane. This will result in new columns insertion into the pivot table for the measure, and bars into the pyramid chart to represent the new population figures.

As a next step apply the percentage format to the new columns of figures from the PivotTable, and then do right click on one of the new bars from the population chart, selecting the "Format Data Series"; this action will open the window of the same name, where we modify the following properties / values.

- Filling: No Fill.

- Border Color: solid line, color black.

- Border Styles: width of 1 point.

Repeating the same operation on the other bar chart data will complete the visual configuration of the new reference population pyramid, which will stay superposed on the zone pyramid, thus achieving our goal. This result allows us to appreciate the differences between the two types of people, facilitating their analysis.

At this point we conclude this article about construction of population pyramids superposed using PowerPivot as a tool to model our data. In a future article will discuss the development of accumulated pyramids, a variety of population pyramid in which the pyramid bars provide more information, because they allow the inclusion of an additional category of classification.

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.