Conexión a cubos dinámicos de la DGIS de la Secretaría de Salud (México)
Pasos para conectarse a los cubos dinámicos de la DGIS de la Secretaría de Salud
Introducción
La Secretaría de Salud (SSA) de México publica muchos de sus datos abiertos a través de cubos dinámicos. Si bien los cubos dinámicos tienen la ventaja de que permiten a los usuarios construir consultas complejas, lo cierto es que la tecnologia que usa la SSA es muy antigua y hace prácticamente inaccesibles los datos. Según la guía de instalación, es necesario usar el navegador Internet Explorer (que ya fue descontinuado por Microsoft) y además instalar al menos 4 programas y complementos en el navegador. El proceso es simplemente anacrónico.
Para facilitar el acceso a los cubos y poder obtener un acceso a los datos mucho más detallado, me dí a la tarea de buscar una forma de conectarme directamente a los cubos sin necesidad de usar el tedioso proceso de instalación. A continuación detallo los pasos de cómo lo logré usando Python.
Primero que todo, es necesario instalar adobapi
pip install adodbapiimport pandas as pd
import adodbapi
def rows_to_df(rows) -> pd.DataFrame:
"""
Convierte los resultados de una consulta de adobdapi a un DataFrame de Pandas.
rows: resultado de la consulta
"""
df = pd.DataFrame(data=dict(zip(rows.columnNames.keys(), rows.ado_results)))\
.assign(_id=lambda x: range(len(x)))
return df
- Se necesita descargar el driver de MSOLAP (amd64): https://docs.microsoft.com/en-us/analysis-services/client-libraries?view=asallproducts-allversions&viewFallbackFrom=sql-server-2017. No he probado en otros sitemas operativos, pero me parece que solo se puede con Windows.
- Es posible que también haya que instalar los drivers que especifica la páginas de DGIS en las instrucciones de instalación (a mí me funcionó sin estos).
- Usamos
adodbapipara establecer una conexión con el servidor - Es necesario especificar la base de datos que se va a consultar, por ejemplo, este es la de muertes maternas.
cubo = 'MATERNAS_2020'
conn = adodbapi.connect('Provider=MSOLAP.8;Password=Temp123!;Persist Security Info=True;User ID=SALUD\DGIS15;'
f'Data Source=pwidgis03.salud.gob.mx;Update Isolation Level=2;Initial Catalog={cubo}')
cursor = conn.cursor()
Una vez establecida la conexión, se pueden hacer queries usando el lenguaje MDX, que usa algunos comando básicos de SQL aunque es mucho más difícil.
La siguiente query permite conocer todas las bases de datos disponibles:
cursor.execute("""SELECT [catalog_name] FROM $system.DBSCHEMA_CATALOGS""")
rows = cursor.fetchall()
list(rows)
En nuestro caso, ya escogimos la base de datos "MATERNAS_2020" con los datos de mortalidad materna del año 2022.
La siguiente query sirve para conocer los cubos y dimensiones de esta base de datos:
cursor.execute("""
SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE], DIMENSION_CAPTION AS [DIMENSION]
FROM $system.MDSchema_Dimensions
WHERE DIMENSION_CAPTION='Measures'
""")
rows = cursor.fetchall()
df_cubos_maternas = rows_to_df(rows)
df_cubos_maternas
- de estos cubos, casi todos empiezan con el símbolo $ \$ $, excepto uno. Lo que he notado es que este cubo es el que tenemos que usar para hacer las consultas de los datos de interés. En este caso, por ejemplo, se trata del cubo "MUERTES MATERNAS"
df_cubos_maternas['cube'].loc[lambda x: ~ x.str.contains(r'\$')]
MDX permite hacer consultas con gran nivel de detalle, pero a mí me parece complicado. Considero más fácil descargar toda la información y luego hacer las agregaciones usando Python:
cursor.execute("""
SELECT *
FROM [MUERTES MATERNAS].[Measures]
""")
rows = cursor.fetchall()
df = rows_to_df(rows)
df
Los datos que obtuvimos son los 21124 registros individuales de mortalidad materna en 2020, para cada uno de ellos tenemos datos como el lugar de ocurrencia, causa, escolaridad, etc.
revisamos los nombres de columnas del DataFrame
df.columns
- Podemos hacer cruces entre variables o graficxar la distribución de las variables:
pd.crosstab(df.iloc[:, 0], df.iloc[:, 4])
df['[totales].[$afiliacion.afiliación derechohabiencia]'].value_counts().plot.barh()
df['[totales].[$escolaridad.escolaridad]'].value_counts().plot.barh()
df['[totales].[$edad.edad quinquenal]'].value_counts().plot.barh()
- Con MDX podemos hacer consultas seleccionando filtrando algunas columnas con SELECT y o también filtrando filas con WHERE:
cursor.execute("""
SELECT [$mes registro.mes de registro], [$afiliacion.afiliación derechohabiencia]
FROM [MUERTES MATERNAS].[MEASURES]
WHERE [$mes registro.mes de registro]="MAYO"
""")
rows = cursor.fetchall()
rows_to_df(rows)
- De la lista de cubos disponibles, seleccionamos el de NACIMIENTOS_2022
cubo2 = 'NACIMIENTOS_2022'
conn2 = adodbapi.connect('Provider=MSOLAP.8;Password=Temp123!;Persist Security Info=True;User ID=SALUD\DGIS15;'
f'Data Source=pwidgis03.salud.gob.mx;Update Isolation Level=2;Initial Catalog={cubo2}')
cursor2 = conn2.cursor()
- revisamos las dimensiones que tiene este cubo:
cursor2.execute("""
SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE], DIMENSION_CAPTION AS [DIMENSION]
FROM $system.MDSchema_Dimensions
""")
rows = cursor2.fetchall()
df_cubos_nacimiento = rows_to_df(rows)
df_cubos_nacimiento
- el cubo de interés es "NACIMIENTOS_2022"
df_cubos_nacimiento.query('cube=="NACIMIENTOS_2022"')
- Si intentamos seleccionar todos los datos del cubo, nos aparece un error porque parece que la carga es mucha y el tiempo de consulta se agota.
cursor2.execute("""
SELECT *
FROM [NACIMIENTOS_2022].[Measures]
""")
rows = cursor2.fetchall()
rows_to_df(rows)
- Para poder hacer la consulta, la opción es hacer varias consultas más pequeñas, por ejemplo, una para cada entidad federativa. Revisemos las entidades federativas disponibles
cursor2.execute("""
SELECT entidadresidenciad
FROM [NACIMIENTOS_2022].[$ENTIDAD_RESIDENCIA]
""")
rows = cursor2.fetchall()
rows_to_df(rows)
- entonces ahora solo seleccionamos la de un estado en particular, por ejemplo, Chiapas
cursor2.execute("""
SELECT *
FROM [NACIMIENTOS_2022].[Measures]
WHERE [$ENTIDAD_RESIDENCIA.entidadresidenciad]="07 CHIAPAS"
""")
rows = cursor2.fetchall()
df_nacimientos_chis = rows_to_df(rows)
df_nacimientos_chis
df_nacimientos_chis.columns
En este último caso quizá convenga aprender mejor MDX para hacer consultas más detalladas y con menos resultados. La verdad a mí me pareció un lenguaje complicado, pero es necesario entenderlo un poco para poder usar los cubos dinámicos. Yo tuve muchísimos errores antes de poder dar con las consultas correctas que hice arriba.