Introducción
En este artículo vamos a explorar como se comporta Power BI cuando en un modelo de datos existen violaciones de la integridad referencial, y como podemos identificar y solventar este problema, a la vez que agrupamos los miembros desconocidos dotándolos de significado y garantizando la integridad de nuestro modelo.
La integridad referencial es un conjunto de reglas que utilizan las bases de datos relacionales para asegurarse de que no existen valores en una clave foránea que no estén en la clave primaria de la tabla relacionada. Veámoslo con una imagen:
Tenemos una dimensión Cliente y una tabla de hechos de Ventas. La integridad referencial lo que comprueba es que todos los valores de la clave foránea estén presentes en la clave primaria de la tabla relacionada, en este caso la dimensión Cliente. Basicamente, se trata de garantizar la consistencia de los datos; que los datos que referencian a otros datos sean correctos.
Si nos fijamos en la imagen anterior veremos que el valor 3 del campo IdCliente
se encuentra en la tabla de hechos, pero no en la dimensión. Por lo tanto, existe una violación de la integridad referencial entre ambas tablas, y se dice que dicho valor pertenece a un «miembro desconocido».
Y este es el propósito de las relaciones en un sistema transaccional, garantizar la integridad referencial, que no puedas crear una factura a un cliente que no existe en el maestro de clientes.
Por el contrario, en un modelo analítico como el de Power BI las relaciones se establecen con la finalidad de propagar filtros de una tabla a otra, por lo que en un modelo de datos podemos tener errores de integridad referencial, y que los datos se sigan actualizando sin incidentes.
Vamos a ver como maneja estas situaciones Power BI y como podemos nosotros, proactivamente, adelantarnos al problema.
Creando el problema
Vamos a partir del siguiente modelo de datos:
Como podemos advertir, se trata de un esquema en estrella con una única tabla de hechos de Ventas y varias dimensiones. En este momento no existe ninguna violación de la integridad referencial.
Si creamos una visualización de las Ventas por Categoría de producto obtenemos este resultado:
A continuación, vamos a introducir una violación de la integridad referencial entre las tablas Ventas y Producto. Para ello eliminamos unos pocos registros de la dimensión y nos fijamos en lo que ocurre en la anterior visualización:
Como vemos, aparece un valor en blanco en la columna Categoría que agrupa las Ventas correspondientes a todos los registros en los que existe un problema de integridad referencial. A todos los efectos, es como si se hubiera añadido una nueva fila en nuestra dimensión, con todos los campos en blanco, para agrupar dichos registros.
Antes de continuar, es muy importante entender que esto solo ocurre si hablamos de una relación regular. En las relaciones físicas en Power BI tenemos dos tipos: 1) las regulares, que son las que el motor del modelo puede comprobar que en el lado uno de la relación los valores son únicos, y 2) las limitadas, que es cuando no se puede garantizar esta unicidad, circunstancia que se puede dar en uno de los siguientes casos:
- Siempre que tengamos una relación varios a varios.
- En relaciones «inter isla», que son aquellas que se establecen cuando una de las tablas se encuentra importada en Vertipaq y la en modo Direct Query, o cuando ambas tablas se encuentran en modo Direct Query pero provienen de orígenes distintos. Podemos visualizar ambas circunstancias en la siguiente imagen:
Estas relaciones limitadas se representan en la vista de modelo con separaciones en la línea que simboliza la relación entre dos tablas:
En este artículo nos centramos en las relaciones regulares, por lo que, una vez aclarado este punto, seguimos con el ejemplo.
Solucionando el problema
Vamos a distinguir dos tipos de problemas a detectar y solucionar, uno se refiere al caso en que cual, en una clave foránea tenemos un valor nulo, y la otra cuando tenemos un valor que no existe en la clave primaria, que es la violación de la integridad referencial propiamente dicha.
En primer lugar, vamos a lidiar con los valores nulos en una clave foránea, situación que no tiene que representar a priori ningún problema en la calidad de los datos, y de hecho se da con cierta frecuencia en la vida real. Por ejemplo, en contabilidad analítica es muy común que tengamos ingresos o gastos para los que no aplica una determinada dimensión, pero siempre es preferible sustituirlos por otro valor, siguiendo la buena práctica de modelado de no tener ningún valor null
en una tabla de hechos. Para replicar este problema voy a sustituir un valor de la clave foránea IdProducto
por null
en la tabla de hechos.
Como vemos en la siguiente imagen, Power BI trata estos valores nulos de la misma forma que las violaciones de la integridad referencial, el informe sigue siendo el mismo pero con mayor importe agrupado en la fila en blanco:
Para solucionar los valores nulos en una clave foránea vamos a sustituirlos por un valor arbitrario, por ejemplo -1:
ReemplazarNulos= Table.ReplaceValue(
#"Valor reemplazado",
null,
-1,
Replacer.ReplaceValue,{"ProductKey"}
)
Power QueryY a continuación vamos a crear un registro adicional en nuestra dimensión de Producto con el valor -1 en la clave primaria:
Si seguidamente volvemos a la visualización, advertiremos que los registros que originalmente tenían un valor null
en la clave foránea IdProducto
se han agrupado bajo la Categoría N/A:
Como podemos ver, todavía tenemos el problema de los «miembros desconocidos» producido por la violación de la integridad referencial entre las tablas de Ventas y Producto. Con estos registros, lo que necesitamos, y no nos queda otra, es cruzar las dimensiones con la tabla de hechos. Cabe resaltar que este proceso puede llegar a ser muy caro en términos de rendimiento, razón por la cual estas operaciones se suelen realizar antes de que los datos lleguen a Power BI, en la construcción del Datawarehouse o Datamart analítico que servirá como origen del modelo de datos. Normalmente, este proceso forma parte de la creación de las claves subrogadas, que jugarán un papel crucial en los casos en los que necesitemos trabajar con dimensiones lentamente cambiantes.
En este modelo, esas claves subrogadas, que no dejan de ser un identificador único que se asigna a cada registro de una tabla de dimensión y cuyos valores son totalmente independientes de los datos del origen transaccional, ya están creadas, por lo que vamos a realizar los cruces por ellas directamente.
Para ello, combinamos las tablas Producto y Ventas:
Y expandimos la clave primaria de la dimensión. Lo siguiente, es reemplazar los valores null
, que corresponden a los registros que producen la violación de la integridad referencial, por otro valor arbitrario, por ejemplo -2:
ReemplazarNulos1= Table.ReplaceValue(#"Se expandió Product",
null,
-2,
Replacer.ReplaceValue,{"ProductKey.1"})
Power QueryPara continuar, crearemos otro registro en la dimensión Producto para agrupar estos registros:
Y tras cambiar el campo de la tabla de hechos con el que relacionamos con la dimensión Producto, volvemos a nuestra visualización y veremos que los registros que producen el problema de integridad referencial se han agrupado sobre este último registro creado en la dimensión:
Identificar los problemas de integridad referencial
Para terminar, me gustaría comentar la mejor forma, en mi opinión, de buscar e identificar estos problemas fácilmente en cada actualización de los datos. Se trata de usar un script para Tabular Editor desarrollado por el compañero de la comunidad Bernat Agulló. Todos los detalles y el código del mismo pueden encontrarse en este artículo de su blog: https://www.esbrina-ba.com/easy-management-of-referential-integrity/
Deja un comentario