If you report on Sales target and Quantity grouped by Product and Sales staff, the result set is incorrect. Sales target is too large.

To check the result, query each fact separately.

This confirms that the first report is giving a much larger result for Sales target than it should.

The SQL

Note the following problems when you look at the SQL:

• Only one of the dimension columns has a coalesce statement. This indicates that the query has been improperly split.

• Grouping is correct for the Quantity side of the query. This explains why Quantity is accurate.

• The quantity side of the stitched query is grouped only by Product, which explains why the Sales targets are too large.

select 
	coalesce(D2.PRODUCT_NAME,D3.PRODUCT_NAME) as PRODUCT, 
	D2.LAST_NAME as LAST_NAME,
	D2.SALES_TARGET as SALES_TARGET,
	D3.Quantity as Quantity
from 
(select 
	Product.PRODUCT_NAME as PRODUCT,
	SALES_STAFF.LAST_NAME as LAST,
	XSUM(SALES_TARGET.SALES_TARGET for 
	Product.PRODUCT_NAME,SALES_STAFF.LAST_NAME) as SALES_TARGET
from 
	(select PRODUCT_MULTILINGUAL.PRODUCT_NAME,PRODUCT.PRODUCT_NUMBER
from
	gosales.gosales.dbo.PRODUCT PRODUCT,
	gosales.gosales.dbo.PRODUCT_MULTILINGUAL PRODUCT_MULTILINGUAL where
	PRODUCT.PRODUCT_NUMBER = PRODUCT_MULTILINGUAL.PRODUCT_NUMBER) Product
	join 
	gosales.gosales.dbo.SALES_TARGET SALES_TARGET
	on (Product.PRODUCT_NUMBER = SALES_TARGET.PRODUCT_NUMBER)
	join 
	gosales.gosales.dbo.SALES_STAFF SALES_STAFF
	on (SALES_STAFF.SALES_STAFF_CODE = SALES_TARGET.SALES_STAFF_CODE) 
 group byProduct.PRODUCT_NAME,SALES_STAFF.LAST_NAME) D2 
full outer join 
	(select 
	Product.PRODUCT_NAME as PRODUCT_NAME,
	XSUM(XSUM((ORDER_DETAILS.QUANTITY * ORDER_DETAILS.UNIT_SALE_PRICE)
	for Product.PRODUCT_NAME) at Product.PRODUCT_NAME 
	for Product.PRODUCT_NAME)as Actual_sales
from 
	(select PRODUCT_MULTILINGUAL.PRODUCT_NAME,PRODUCT.PRODUCT_NUMBER
from
	gosales.gosales.dbo.PRODUCT PRODUCT,
	gosales.gosales.dbo.PRODUCT_MULTILINGUAL
	PRODUCT_MULTILINGUAL where PRODUCT.PRODUCT_NUMBER =
	PRODUCT_MULTILINGUAL.PRODUCT_NUMBER) Product
	join 
	gosales.gosales.dbo.ORDER_DETAILS ORDER_DETAILS
	on (Product.PRODUCT_NUMBER = ORDER_DETAILS.PRODUCT_NUMBER) 
 group byProduct.PRODUCT_NAMED3on (D2.PRODUCT_NAME = D3.PRODUCT_NAME)

These problems exist because nothing was selected from Order header. It was not included in the query so there was no way to link Sales staff to Order details. Linking Sales staff to Order details was not necessary to connect all the tables because there was already a relationship between Sales staff and Sales target.

Although you can resolve this by including an item from Order header in the report, your users may not know to do that. We recommend that you fix it in the model. This is the recommended solution from both a dimensional and business modeling standpoint. In addition, a simpler model makes it harder to write a report that generates bad SQL.

Consolidate query subjects where logical groupings are possible. In this example, combine the Order details and Order header query subjects. The new group of query subjects can now be used for dimensional modeling.

When you use this new group of query subjects, the report looks like this.

When you look at the SQL, you see this:

select 
	coalesce(D2.PRODUCT_NAME,D3.PRODUCT_NAME) as PRODUCT,
	coalesce(D2.LAST_NAME,D3.LAST_NAME) as LAST_NAME,
	D2.SALES_TARGET as SALES_TARGET,
	D3.Quantity as Quantity
from 
	(select 
	Product.PRODUCT_NAME as PRODUCT,
	SALES_STAFF.LAST_NAME as LAST_NAME,
	XSUM(SALES_TARGET.SALES_TARGET for
	Product.PRODUCT_NAME,SALES_STAFF.LAST_NAME) as SALES_TARGET
from 
	(select PRODUCT_MULTILINGUAL.PRODUCT_NAME,PRODUCT.PRODUCT_NUMBER
from
	gosales.gosales.dbo.PRODUCT PRODUCT, 
	gosales.gosales.dbo.PRODUCT_MULTILINGUAL
	PRODUCT_MULTILINGUAL where PRODUCT.PRODUCT_NUMBER = 
	PRODUCT_MULTILINGUAL.PRODUCT_NUMBER) Product
	join 
	gosales.gosales.dbo.SALES_TARGET SALES_TARGET
	on (SALES_TARGET.PRODUCT_NUMBER = Product.PRODUCT_NUMBER)
	join 
	gosales.gosales.dbo.SALES_STAFF SALES_STAFF
	on (SALES_STAFF.SALES_STAFF_CODE = SALES_TARGET.SALES_STAFF_CODE)
group by 
	Product.PRODUCT_NAME,
	SALES_STAFF.LAST_NAME
	) D2
	full outer join 
	(select 
	Product.PRODUCT_NAME as PRODUCT,
	SALES_STAFF.LAST_NAME as LAST_NAME,
	XSUM((ORDER_DETAILS_ORDER_HEADER.c5 * ORDER_DETAILS_ORDER_HEADER.c8)
	for Product.PRODUCT_NAME,SALES_STAFF.LAST_NAME ) as Actual_sales
from 
	(select PRODUCT_MULTILINGUAL.PRODUCT_NAME,PRODUCT.PRODUCT_NUMBER
from
	gosales.gosales.dbo.PRODUCT PRODUCT,
	gosales.gosales.dbo.PRODUCT_MULTILINGUAL
	PRODUCT_MULTILINGUAL where PRODUCT.PRODUCT_NUMBER = 
	PRODUCT_MULTILINGUAL.PRODUCT_NUMBER) Product
	join 
	(select 
	ORDER_DETAILS.PRODUCT_NUMBER as c4,
	ORDER_DETAILS.QUANTITY as c5,
	ORDER_DETAILS.UNIT_SALE_PRICE as c8,
	ORDER_HEADER.SALES_STAFF_CODE as c14
from 
	gosales.gosales.dbo.ORDER_DETAILS ORDER_DETAILS
	join 
	gosales.gosales.dbo.ORDER_HEADER ORDER_HEADER
	on (ORDER_HEADER.ORDER_NUMBER = ORDER_DETAILS.ORDER_NUMBER)
	) ORDER_DETAILS_ORDER_HEADER
	on (ORDER_DETAILS_ORDER_HEADER.c4 = Product.PRODUCT_NUMBER)
	join 
	gosales.gosales.dbo.SALES_STAFF SALES_STAFF
	on (SALES_STAFF.SALES_STAFF_CODE = ORDER_DETAILS_ORDER_HEADER.c14)
group by 
	Product.PRODUCT_NAME,
	SALES_STAFF.LAST_NAME
	) D3
	on ((D2.PRODUCT_NAME = D3.PRODUCT_NAME) and (D2.LAST_NAME = D3.LAST_NAME)))