Información general de las relaciones
En este tema se presentan las relaciones que puede definir entre las tablas en PowerPivot para Excel. Se incluyen las secciones siguientes:
¿Qué es una relación?
Requisitos para las relaciones
Detección automática e inferencia de las relaciones
Después de leer este tema, debería entender lo que es una relación, cuáles son los requisitos para definir una y cómo puede PowerPivot para Excel detectar automáticamente las relaciones. Además, obtendrá información sobre parte de la terminología que los profesionales de bases de datos utilizan para describir las relaciones.
¿Qué es una relación?
Una relación es una conexión entre dos tablas de datos, basada en una o más columnas de cada tabla (exactamente una columna de cada tabla para PowerPivot). Para ver por qué son útiles las relaciones, imagine que realiza el seguimiento de los datos de los pedidos de los clientes de su negocio. Podría realizar el seguimiento de todos los datos en una sola tabla que tiene una estructura como la siguiente:
CustomerID |
Nombre |
DiscountRate |
OrderID |
OrderDate |
Product |
Quantity |
|
|---|---|---|---|---|---|---|---|
1 |
Ashton |
chris.ashton@contoso.com |
.05 |
256 |
2010-01-07 |
Compact Digital |
11 |
1 |
Ashton |
chris.ashton@contoso.com |
.05 |
255 |
2010-01-03 |
SLR Camera |
15 |
2 |
Jaworski |
michal.jaworski@contoso.com |
.10 |
254 |
2010-01-03 |
Budget Movie-Maker |
27 |
Este enfoque puede funcionar, pero implica almacenar muchos datos redundantes, como la dirección de correo electrónico del cliente para cada pedido. El almacenamiento es barato, pero tiene que asegurarse de que actualiza cada fila para ese cliente si la dirección de correo electrónico cambia. Una solución a este problema es dividir los datos en varias tablas y definir relaciones entre esas tablas. Este es el enfoque utilizado en las bases de datos relacionales como SQL Server. Por ejemplo, una base de datos que importe en PowerPivot para Excel podría representar los datos de los pedidos usando tres tablas relacionadas:
Customers
[CustomerID] |
Nombre |
|
|---|---|---|
1 |
Ashton |
chris.ashton@contoso.com |
2 |
Jaworski |
michal.jaworski@contoso.com |
CustomerDiscounts
[CustomerID] |
DiscountRate |
|---|---|
1 |
.05 |
2 |
.10 |
Orders
[CustomerID] |
OrderID |
OrderDate |
Product |
Quantity |
|---|---|---|---|---|
1 |
256 |
2010-01-07 |
Compact Digital |
11 |
1 |
255 |
2010-01-03 |
SLR Camera |
15 |
2 |
254 |
2010-01-03 |
Budget Movie-Maker |
27 |
Si importa estas tablas de la misma base de datos, PowerPivot puede detectar las relaciones entre las tablas en función de las columnas que están entre [corchetes] y puede reproducirlas en la ventana de PowerPivot. Para obtener más información, vea Detección automática e inferencia de relaciones en este tema. Si importa las tablas de varios orígenes, puede crear las relaciones manualmente según se describe en Crear una relación entre dos tablas.
Claves y columnas
Las relaciones se basan en las columnas de cada tabla que contienen los mismos datos. Por ejemplo, las tablas Customers y Orders pueden estar relacionadas entre sí porque ambas contienen una columna que almacena un identificador de cliente. En el ejemplo, los nombres de columna son los mismos, pero no es obligatorio. Uno puede ser CustomerID y otro puede ser CustomerNumber, en tanto en cuanto todas las filas de la tabla Orders contengan un identificador que también esté almacenado en la tabla Customers.
En una base de datos relacional, hay varios tipos de claves, que normalmente son solo columnas con propiedades especiales. Los siguientes cuatro tipos de claves son los más interesantes para nuestros propósitos:
Clave principal: identifica en exclusividad una fila de una tabla, como CustomerID en la tabla Customers.
Clave alternativa (o clave candidata): una columna distinta de la clave principal que es única. Por ejemplo, una tabla Employees podría almacenar un identificador de empleado y un número de la seguridad social, ambos exclusivos.
Clave externa: una columna que hace referencia a una columna única de otra tabla, como CustomerID de la tabla Orders, que hace referencia a CustomerID en la tabla Customers.
Clave compuesta: una clave compuesta de más de una columna. Las claves compuestas no se admiten en PowerPivot para Excel . Para obtener más información, vea "Claves compuestas y columnas de búsqueda" en este tema.
En PowerPivot para Excel, la clave principal o la tecla alternativa se conocen como la columna de búsqueda relacionada, o simplemente columna de búsqueda. Si una tabla tiene una clave principal y una alternativa, puede utilizar cualquiera de las dos como columna de búsqueda. La clave externa se denomina columna de origen o simplemente columna. En nuestro ejemplo, se definiría una relación entre CustomerID de la tabla Orders (la columna) y CustomerID (la columna de búsqueda) en la tabla Customers. Si importa datos de una base de datos relacional, PowerPivot para Excel elige de forma predeterminada la clave externa de una tabla y la clave principal correspondiente de la otra. Sin embargo, puede utilizar cualquier columna que tenga valores únicos como columna de búsqueda.
Tipos de relaciones
La relación entre Customers y Orders es una relación uno a varios. Cada cliente puede tener varios pedidos, pero un pedido no puede tener varios clientes. Los otros tipos de relaciones son de uno a uno y varios a varios. La tabla CustomerDiscounts, que define una tarifa reducida única para cada cliente, tiene una relación de uno a uno con la tabla Customers. Un ejemplo de relación de varios a varios es una relación directa entre Products y Customers, en la que un cliente puede comprar varios productos y el mismo producto lo pueden comprar varios clientes. PowerPivot para Excel no admite relaciones de varios a varios en la interfaz de usuario. Para obtener más información, vea “Relaciones de varios a varios” en este tema.
En la siguiente tabla se muestran las relaciones entre las tres tablas:
Relación |
Tipo |
Columna de búsqueda |
Columna |
|---|---|---|---|
Customers-CustomerDiscounts |
uno a uno |
Customers.CustomerID |
CustomerDiscounts.CustomerID |
Customers-Orders |
uno a varios |
Customers.CustomerID |
Orders.CustomerID |
Relaciones y rendimiento
Una vez creada una relación, la base de datos de PowerPivot para Excel normalmente debe recalcular las fórmulas en que se usen columnas de las tablas de la relación recién creada. El proceso puede tardar algún tiempo, en función de la cantidad de datos y la complejidad de las relaciones. Para obtener más información, vea Recalcular fórmulas.
Requisitos para las relaciones
PowerPivot para Excel tiene varios requisitos que se deben seguir al crear relaciones:
Relación única entre tablas
Varias relaciones podrían producir dependencias ambiguas entre las tablas. Para crear cálculos precisos, se necesita una única ruta de una tabla a la tabla siguiente. Por lo tanto, puede haber solo una relación entre cada par de tablas. Por ejemplo, en AdventureWorksDW2012 , la tabla, DimDate contiene una columna DateKey que está relacionada con tres columnas diferentes de la tabla FactInternetSales: OrderDate, DueDate y ShipDate. Si intenta importar estas tablas, la primera relación se creará correctamente, pero recibirá el error siguiente en las relaciones sucesivas en las que participe la misma columna:
* Relación: tabla[columna 1] -> tabla[columna 2] - Estado: error - Motivo: no se puede crear una relación entre las tablas <tabla 1> y <tabla 2>. Entre dos tablas solo puede existir una relación directa o indirecta.
Si tiene dos tablas y varias relaciones entre ellas, entonces deberá importar varias copias de la tabla que contenga la columna de búsqueda y crear una relación entre cada par de tablas.
Una relación para cada columna de origen
Una columna de origen no puede participar en varias relaciones. Si ya ha usado una columna como columna de origen en una relación, pero desea usar esa columna para conectar con otra columna de búsqueda relacionada en una tabla diferente, puede crear una copia de la columna y emplearla para la nueva relación.
Es fácil crear una copia de una columna que tiene los mismos valores exactos usando una fórmula de DAX en una columna calculada. Para obtener más información, vea Columnas calculadas.
Identificador único para cada tabla
Cada tabla debe tener una única columna que identifique de forma única cada fila de esa tabla. A menudo se hace referencia a esta columna como la clave principal.
Columnas de búsqueda única
Los valores de datos de la columna de búsqueda deben ser únicos. En otras palabras, la columna no puede contener duplicados. En PowerPivot para Excel, las cadenas nulas y vacías equivalen a un valor en blanco, que es un valor de datos distinto. Esto significa que no puede tener varios valores nulos en la columna de búsqueda.
Tipos de datos compatibles
Los tipos de datos de la columna de origen y de la columna de búsqueda deben ser compatibles. Para obtener más información acerca de los tipos de datos, vea Tipos de datos admitidos en libros PowerPivot.
Claves compuestas y columnas de búsqueda
Las claves compuestas no se pueden utilizar en un libro de PowerPivot; siempre debe tener exactamente una columna que identifique de forma única cada fila de la tabla. Si intenta importar tablas que tienen una relación existente basada en una clave compuesta, el Asistente para la importación de tablas omitirá esa relación porque no se puede crear en PowerPivot.
Si desea crear una relación entre dos tablas en PowerPivot, y hay varias columnas que definen las claves principales y las claves externas, debe combinar los valores para crear una columna de clave única antes de crear la relación. Puede hacerlo antes de importar los datos, o hacerlo en PowerPivot creando una columna calculada.
Relaciones varios a varios
PowerPivot para Excel no admite relaciones de varios a varios y no puede agregar simplemente tablas de unión en PowerPivot. Sin embargo, puede usar funciones de DAX para modelar las relaciones de varios a varios.
Autocombinaciones y bucles
Las autocombinaciones no se permiten en las tablas de PowerPivot. Una autocombinación es una relación recursiva entre una tabla y ella misma. Las autocombinaciones se utilizan a menudo para definir las jerarquías de elementos primarios y secundarios. Por ejemplo, podría unir una tabla Employees a sí misma para generar una jerarquía que muestre la cadena de dirección en un negocio.
PowerPivot para Excel no permite crear bucles entre relaciones en un libro. En otras palabras, se prohíbe el conjunto siguiente de relaciones.
Tabla 1, columna a a Tabla 2, columna f
Tabla 2, columna f a Tabla 3, columna n
Tabla 3, columna n a Tabla 1, columna a
Si intenta crear una relación que crearía un bucle, se generará un error.
Detección automática e inferencia de las relaciones
Al importar los datos en la ventana de PowerPivot, el Asistente para la importación de tablas detecta automáticamente las relaciones existentes entre las tablas. Además, al crear una tabla dinámica, PowerPivot para Excel analiza los datos de las tablas. Detecta posibles relaciones que no se han definido y sugiere columnas adecuadas para incluirlas en esas relaciones.
El algoritmo de detección usa datos estadísticos de los valores y metadatos de las columnas para deducir la probabilidad de las relaciones.
Los tipos de datos de todas las columnas relacionadas deberían ser compatibles. Para la detección automática, solo se admiten los tipos de datos de texto y números enteros. Para obtener más información acerca de los tipos de datos, vea Tipos de datos admitidos en libros PowerPivot.
Para que la relación se detecte correctamente, el número de claves únicas de la columna de búsqueda debe ser mayor que los valores de la tabla del lado de "varios". Dicho de otro modo, la columna de clave del lado de "varios" de la relación no debe contener ningún valor que no esté en la columna de clave de la tabla de búsqueda. Por ejemplo, suponga que tiene una tabla de productos con sus identificadores (la tabla de búsqueda) y una tabla de ventas con las ventas de cada producto (el lado de "varios" de la relación). Si los registros de ventas contienen el identificador de un producto que no tiene un identificador correspondiente en la tabla de productos, la relación no se puede crear automáticamente, pero quizás pueda crearla manualmente. Para que PowerPivot para Excel detecte la relación, primero debe actualizar la tabla de búsqueda, la tabla de productos, con los identificadores de producto que falten.
Asegúrese de que el nombre de la columna de clave del lado de "varios" es parecido al nombre de la columna de clave de la tabla de búsqueda. No es necesario que nombres sean exactamente iguales. Por ejemplo, en las empresas, suele haber variaciones de los nombres de columnas que contienen prácticamente los mismos datos: Emp ID, EmployeeID, Employee ID, EMP_ID, etcétera. El algoritmo detecta los nombres parecidos y asigna una probabilidad más alta a las columnas con nombres parecidos o exactamente iguales. Por consiguiente, para aumentar la probabilidad de crear una relación, se puede cambiar el nombre de las columnas de los datos que se importen por nombres parecidos a los de las columnas de las tablas existentes. Si PowerPivot para Excel detecta varias relaciones posibles, no crea ninguna.
Esta información podría ayudar a entender por qué no se detectan todas las relaciones, o cómo los cambios realizados en los metadatos (por ejemplo, el nombre de campo y los tipos de datos) podrían mejorar los resultados de la detección automática de relaciones. Para obtener más información, vea Solucionar problemas de relaciones y Fundamentos de la detección automática de relaciones de PowerPivot.
Detección automática para los conjuntos con nombre
Las relaciones no se detectan automáticamente entre los campos relacionados y conjuntos con nombre en una tabla dinámica. Puede crear estas relaciones manualmente. Si desea usar la detección automática de relaciones, quite cada conjunto con nombre y agregue directamente los campos individuales del conjunto con nombre a la tabla dinámica.
Inferencia de relaciones
En algunos casos, las relaciones entre las tablas se encadenan automáticamente. Por ejemplo, si crea una relación entre los dos primeros conjuntos de tablas del ejemplo siguiente, se deduce que existe una relación entre las otras dos tablas y se establece una relación automáticamente.
Products and Category -- creado manualmente
Category and SubCategory -- creado manualmente
Products y SubCategory -- la relación se deduce
Para que las relaciones se encadenen automáticamente, las relaciones deben ir en una dirección, como se mostró antes. Si las relaciones iniciales fueran entre, por ejemplo, Sales y Products, y Sales y Customers, no se deduciría una relación. Esto se debe a que la relación entre Products y Customers es una relación de varios a varios.