Forcing a join path among multi-fact relationships with conformed dimensions

[cog8user]

Consider I have...
Two fact tables: Sales_Fact, Returns_Fact
Two dimension tables: Time, Product

The dimensions are conformed so all tables are joined together with 1..1 and 1..n cardinality (dimension to fact), so each dimension would have two different paths but Cognos will wisely choose based on the cardinality set.

These are joined in database layer only once and in the presentation layer, aliases are in separate namespaces for each business type.
Namespace 1 (Sales):
Product
Time
Sales

Namespace 2 (Returns):
Product
Time
Returns

When you use these in the studios, the join path will be chosen based on which fact table is added to the query. And if both facts are chosen, it will write a stitched query. Everything works as expected until here.

What if a user wants to see Products that were "SOLD" within certain time period without worrying much about the number of products. Meaning, no field from fact table(s) is added to the query?
Cognos by default chooses Returns fact as it comes first in the alphabetical order of the relationship names. For what the user wants, it is incorrect!
For the end user from business, he does not need to know technical dependency of the tool. When you define the Sales namespace, whatever he chooses should give him Sales data.

Can anyone suggest what are different ways of forcing the join paths to dedicate them to each namespace created for each business purpose?

I am sure many would have experienced similar situations.

[Kiran P]

Hi,

One potential solution could be to add a condition related to sales in product query item in BL, say for ex. case when sales_fact.productID = product.productID then product.Name end. This will force cognos to include sales_fact in the query path. Note that by doing this, you will not be able to see any products that has zero sales.

Thanks,
Kiran

[AnalyticsWithJay]

How are you querying "products that were sold within a time period"? Generally, I would place Product in a data container, and filter on "Sales.Product IS NOT NULL", and "Time.Period = February", all from the Sales namespace.

In other words, how are you querying which products were sold WITHOUT going to the Sales fact query as you mentioned? If you don't use a fact in your query and you use conformed dimensions, Cognos will go through the fact as you stated. But the nature of your query typically involves going to the Sales fact.

You could build standalone filters for common queries that would force it in the background. But before suggesting this, I am curious how you are building that query without a fact table. That seems to be the issue at first glance.

[cog8user]

Kiran & Jay, Thanks for your replies.

I am not suggesting to build a query without the fact table. What I say is the end user does not need to do this in the report. They should be least bothered about the technical dependencies.
So, technically it should be done in FM and all I am trying to find is different ways to achieve this as each may have some or other impact on the usage.

If I understand correctly, I believe you meant an embedded filter added to a dimension query subject definition and would use a field from the fact table. This enforces usage of the fact table behind the scene. Please correct me if I am wrong.

If I am correct, this requires separate model query subjects for each namespace as the embedded filters would use the fact tables one at a time.

[AnalyticsWithJay]

Hi cog8user,

You're correct, but I'm talking about standalone filters -- not embedded filters. Many of my clients have a folder, with sub-folders (ex: Sales, HR, Time), that contain a list of commonly used filters. They are standalone, meaning the user can expand the folder and drag/drop the filter onto the report should they wish.

More importantly though, what I am trying to say is that whomever tries to answer "products that were sold within a time period", has to somehow reference the fact table (in most designs). And in this case the fact table is sales. It is very rare to have queries that don't touch a fact table (even if it doesn't display it). And for those rare cases, you can use standalone filters, self-service reports/packages that are targeted for this specific purpose, etc.

Hope this answers your question :)

[cog8user]

I got what you are saying. Thank you! Now this becomes a question of how I will need to train my users. :)

[MFGF]

I got what you are saying. Thank you! Now this becomes a question of how I will need to train my users. :)

Of course the other obvious point is that (as you rightly said) the query engine defaults to using the first alphabetical relationship (based on the relationship name). The users don't ever see relationship names - they are defined in FM and visible to the modeller in FM, but not visible in a published package. One kludgy solution is to rename the relationships between the dimensions and the Sales fact table so the relationships have names alphabetically higher than the relationship names between the dimensions and the Returns fact table. You can call the relationships anything you like, without affecting the names of the query subjects.

Having said all that, I'm completely with Jay and Kiran here - especially Jay's advice above about standalone filters.

Cheers!

MF.