If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Mark preferred path to resolve dimensions

Started by RudiHendrix, 13 Jan 2010 09:38:15 AM

Previous topic - Next topic

RudiHendrix

In our framework we have several dimensions. For example: Contract and Network Point (NWP for now).
Obviously we also have some facts: Contract Facts and Billing Facts.

The dimensions are conformed dimensions. Thus they apply to both facts.

Now, it is possible that we have some data for Contract Facts but no Billing Facts yet.

Now if the user selects only Contract and NWP in Query Studio no data is displayed. If he adds a fact from the Contract Facts data IS displayed.
We narrowed things down by opening the query in Report Studio. By extracting the SQL query we can see that the both dimensions are selected by including the Billing Facts table.

Can we enforce that the Contract Facts will be used if no Billing Facts are available?
The only way that I can think of now is by creating an alias for the Contract dimension and thus making the Contract dimension to a more or less non-conformed dimension. Any other ideas? (Because this solution means more to maintain.)

MFGF

Hi,

I think the issue here is that the Query Engine is being presented with multiple ambiguous join paths (relationships) between the Contract dimension and the NWP dimension when no fact items are included in the query.  In this situation, the Query Engine has to use one of the sets of relationships, so its default behaviour is to use the first alphabetical (based on the relationship names, and I'm willing to bet the relationship names between dimensions -> Billings Facts come first alphabetically).

If you rename the relationships between your dimensions -> Contract Facts query subject in FM so that their relationship names are alphabetically before the dimensions -> Billings Facts relationship names, these will be used in preference.

Regards,

MF.
Meep!

RudiHendrix

That's probably it!

But actually with multiple conformend dimensions these ambiguous join paths will always be there, right?

MFGF

Potentially, yes, but does it make sense to report off just two dimensions with no correlating facts?  I guess it also depends whether they are real dimension tables or virtual dimensions defined as model query subjects in FM.  If the latter, you obviously have more control over the structures and relationships.

MF.
Meep!

RudiHendrix

In general a report without facts is pretty useless. However in this case the NWP dimension is a real dimension when it comes to the contract facts. But when it comes to the contract dimension it is more like a fact.

A contract can have some NWP's associated to it without any real facts. And a question of the business CAN be: Which networkpoints of the contract have no facts associated to it?

(In order to fix that there is something more to be done than only change the name of the relationship, because in that case we need to add a fact that only consists of the keys indicating there is a relationship between that specific NWP and that specific contract.)