Consulta de varios hechos y consultas de granularidad múltiple en dimensiones compatibles

Una consulta sobre hechos múltiples y dimensiones compatibles respeta la cardinalidad entre cada tabla de hechos y sus dimensiones, y escribe SQL para devolver todas las filas de cada tabla de hechos.

Por ejemplo, tanto Sales como Product Forecast son hechos.

Hechos múltiples y cardinalidad en dimensiones compatibles

Tenga en cuenta que se trata de una representación simplificada y no de un ejemplo de cómo debería aparecer en un modelo creado mediante las recomendaciones de modelado de IBM® Cognos.

Resultado

Las distintas consultas sobre Sales y Product Forecast por Month y Product arrojan los siguientes resultados. Los datos de Sales se almacenan realmente en el nivel de día.

Una consulta sobre Sales y Product Forecast respeta la cardinalidad entre cada tabla de hechos y sus dimensiones, y escribe SQL para devolver todas las filas de cada tabla de hechos. Las tablas de hechos se correlacionan con sus claves comunes (de mes y producto) y allí donde es posible, se agregan al nivel común más bajo de granularidad. En este caso, los días se acumulan a los meses. Este tipo de consulta suele devolver valores nulos ya que una combinación de elementos dimensionales en una tabla de hechos puede no existir en la otra tabla.

Tenga en cuenta que en febrero de 2004, el producto Course Pro Umbrellas formaba parte de la previsión pero no hubo ventas reales. Los datos de Sales y Product Forecast existen en niveles diferentes de granularidad. Los datos de Sales están en el nivel de día y Product Forecast está en el nivel de mes.

SQL

A menudo, el SQL que genera el software IBM Cognos, denominado consulta enlazada, se presta a confusión. Una consulta enlazada utiliza varias subconsultas (una para cada estrella), unidas por una unión externa completa de las claves comunes. El objetivo es conservar todos los miembros dimensionales que se generan a cualquier lado de la consulta.

El siguiente ejemplo se ha editado en cuanto a longitud, y se utiliza para entender las características principales de las consultas enlazadas.

select
	coalesce(D2.MONTH_NAME,D3.MONTH_NAME) as MONTH_NAME, 
	coalesce(D2.PRODUCT_NAME,D3.PRODUCT_NAME) as PRODUCT_NAME, 
	D2.EXPECTED_VOLUME as EXPECTED_VOLUME,
	D3.QUANTITY as QUANTITY
from (select TIME.MONTH_NAME as MONTH_NAME,
	PRODUCT_LOOKUP.PRODUCT_NAME as PRODUCT_NAME,
	XSUM(PRODUCT_FORECAST_FACT.EXPECTED_VOLUME for  
	TIME.CURRENT_YEAR,TIME.QUARTER_KEY,TIME.MONTH_KEY,
	PRODUCT.PRODUCT_LINE_CODE, PRODUCT.PRODUCT_TYPE_CODE,
	PRODUCT.PRODUCT_KEY) as EXPECTED_VOLUME
from 
	(select TIME.CURRENT_YEAR as CURRENT_YEAR,
	TIME.QUARTER_KEY as QUARTER_KEY,
	TIME.MONTH_KEY as MONTH_KEY,
	XMIN(TIME.MONTH_NAME for TIME.CURRENT_YEAR,
	TIME.QUARTER_KEY,TIME.MONTH_KEY) as MONTH_NAME
	from TIME_DIMENSION TIME
	group by TIME.MONTH_KEY) TIME
	join PRODUCT_FORECAST_FACT PRODUCT_FORECAST_FACT
	on (TIME.MONTH_KEY = PRODUCT_FORECAST_FACT.MONTH_KEY)
	join PRODUCT PRODUCT on (PRODUCT.PRODUCT_KEY =
	PRODUCT_FORECAST_FACT.PRODUCT_KEY)
where
	(PRODUCT.PRODUCT_NAME in ('Aloe Relief','Course Pro
Umbrella')) and 
	(TIME.MONTH_NAME in ('April 2004','February 2004','February
2006')) 
group by 
	TIME.MONTH_NAME,
	PRODUCT_LOOKUP.PRODUCT_NAME
) D2
 full outer join 
(select TIME.MONTH_NAME as MONTH_NAME,
	PRODUCT_LOOKUP.PRODUCT_NAME as PRODUCT_NAME,
	XSUM(SALES_FACT.QUANTITY for TIME.CURRENT_YEAR,
	TIME.QUARTER_KEY, TIME.MONTH_KEY, 
	PRODUCT.PRODUCT_LINE_CODE, PRODUCT.PRODUCT_TYPE_CODE, 
	PRODUCT.PRODUCT_KEY ) as QUANTITY
from 
select TIME.DAY_KEY,TIME.MONTH_KEY,TIME.QUARTER_KEY,
	TIME.CURRENT_YEAR,TIME.MONTH_EN as MONTH_NAME 
	from TIME_DIMENSION TIME) TIME
	join SALES_FACT SALES_FACT
	on (TIME.DAY_KEY = SALES_FACT.ORDER_DAY_KEY)
	join PRODUCT PRODUCT on (PRODUCT.PRODUCT_KEY = SALES_FACT.PRODUCT_KEY)
where 
	PRODUCT.PRODUCT_NAME in ('Aloe Relief','Course Pro Umbrella'))
	and (TIME.MONTH_NAME in ('April 2004','February 2004','February
2006'))
 group by 
	TIME.MONTH_NAME, 
	PRODUCT.PRODUCT_NAME 
) D3
 on ((D2.MONTH_NAME = D3.MONTH_NAME) and 
 (D2.PRODUCT_NAME = D3.PRODUCT_NAME))

Significado de la sentencia Coalesce

Una sentencia coalesce es simplemente una forma eficaz de gestionar los elementos de consulta de las dimensiones compatibles. Se utiliza para aceptar el primer valor no nulo devuelto de cualquiera de los asuntos de consulta. Esta sentencia permite obtener una lista completa de claves sin repeticiones cuando se realiza una unión externa completa.

¿Por qué existe una unión externa completa?

Una unión externa completa es necesaria para garantizar que se recuperen todos los datos de cada tabla de hechos. Una unión interna genera resultados sólo si se vendió un artículo del inventario. Una unión externa derecha permite obtener todas las ventas cuyos elementos estaban en el inventario. Una unión externa izquierda permite obtener todos los elementos del inventario de los que haya habido ventas. Una unión externa completa es la única forma de saber el contenido del inventario y qué elementos se han vendido.