In this example, both Product type and Product dimension could be identified as being ambiguously defined. However, this ambiguity is not detrimental to either the results generated or the performance of any query using one or more of these query subjects. You do not need to fix this query pattern because, using the rules for fact detection, only one fact is identified in any query that combines an item from the Product forecast or Sales query subjects. It remains a best practice to collapse hierarchies into a single regular dimension when modeling for analysis purposes.

Some queries that can be written using this example include the following:

Test this model by authoring a report on the number of orders per city, per country. Using this model returns an incorrect result. The numbers are correct for the cities but some cities are shown as being in the wrong country. This is an example of an incorrectly related result.

To find the cause of this problem, you should start by looking at the SQL.

The SQL

In this example, we see a stitched query, which makes sense if we have multiple facts in the model. A stitched query is essentially a query that attempts to stitch multiple facts together. It uses the relationships that relate the facts to each other as well as the determinants for the conformed, or common, dimensions defined in the model. A stitched query can be identified by two queries with a full outer join. The wrapper query must include a coalesce statement on the conformed dimensions.

Note the following problems in the SQL:

• The query has no coalesce statement.

• RSUM indicates an attempt to create a valid key.

selectD3.COUNTRY as COUNTRY,D2.CITY as CITY,D2.number_of_orders as number_of_orders 
from 
(select 
	SALES_BRANCH.CITY as CITY,
	XCOUNT(ORDER_HEADER.ORDER_NUMBER for SALES_BRANCH.CITY) as
	number_of_orders,
	RSUM(1 at SALES_BRANCH.CITY order by SALES_BRANCH.CITY
asc local)as scfrom  
	gosales.gosales.dbo.SALES_BRANCH SALES_BRANCH
	join 
	gosales.gosales.dbo.ORDER_HEADER ORDER_HEADER
	on (SALES_BRANCH.SALES_BRANCH_CODE = ORDER_HEADER.SALES_BRANCH_CODE)
group by 
	SALES_BRANCH.CITY
order by 
	CITY asc
	) D2
full outer join 
(select 
	COUNTRY_LOOKUP.COUNTRY as COUNTRY,
	RSUM(1 at COUNTRY_LOOKUP.COUNTRY order by
	COUNTRY_LOOKUP.COUNTRY asc local) as sc
from 
	gosales.gosales.dbo.COUNTRY_LOOKUP COUNTRY_LOOKUP
group by 
	COUNTRY_LOOKUP.COUNTRY
order by 
	COUNTRY asc
	) D3
	on (D2.sc = D3.sc)

By looking at the stitched columns in each query, we see that they are being calculated on unrelated criteria. This explains why there is no apparent relationship between the countries and cities in the report.

So why do we see a stitched query? To answer that question, we must look at the model.

In this example, the query items used in the report came from different query subjects. Country came from Country lookup, City came from Sales branch, and the Number of Orders came from a count on Order number in the Order header query subject.

The problem is that the query splits because the query engine sees this as a multiple-fact query. However, the split does not have a valid key on which to stitch because there is no item that both facts have in common.

There is more than one way to solve this problem but both require understanding the data.

Solution 1

You can add a filter to Country Multilingual that changes the cardinality of the relationship to 1-1.

Select *
from [GOSL].COUNTRY_MULTILINGUAL
Where
COUNTRY_MULTILINGUAL."LANGUAGE"=’EN’

Or you can add a filter on the relationship and change the cardinality to 1-1.

COUNTRY.COUNTRY_CODE = COUNTRY_MULTILINGUAL.COUNTRY_CODE
and COUNTRY_MULTILINGUAL.LANGUAGE = ’EN’

Either choice results in a model that has a single fact in this query.

Solution 2

Simplify the model by consolidating the related query subjects. This gives the greatest benefit by simplifying the model and reducing the opportunities for error in query generation.

With either solution, the result of the query is now correct.

The SQL is no longer a stitched query.

select 
	Country.c7 as COUNTRY,
	SALES_BRANCH.CITY as CITY,
	XCOUNT(ORDER_HEADER.ORDER_NUMBER for Country.c7,SALES_BRANCH.CITY)
	as number_of_orders
from 
(select 
	COUNTRY.COUNTRY_CODE as c1,
	COUNTRY_MULTILINGUAL.COUNTRY as c7
from 
	gosales.gosales.dbo.COUNTRY COUNTRY
	join 
	gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL
	on (COUNTRY.COUNTRY_CODE = COUNTRY_MULTILINGUAL.COUNTRY_CODE)
	where COUNTRY_MULTILINGUAL.LANGUAGE='EN'
	) Country
join 
	gosales.gosales.dbo.SALES_BRANCH SALES_BRANCH
	on (SALES_BRANCH.COUNTRY_CODE = Country.c1)
	join
	gosales.gosales.dbo.ORDER_HEADER ORDER_HEADER
	on (SALES_BRANCH.SALES_BRANCH_CODE = ORDER_HEADER.SALES_BRANCH_CODE)
group by 
	Country.c7,
	SALES_BRANCH.CITY