How to resolve BMT-MD-2016

[kris123]

Hello,
I wanted to create a model in framework manager and chose
the tables SLS_PRODUCT_DIM, SLS_RTL_DIM, DIST_RETURNED_ITEMS_FACT, DITS_PRODUCT_FORECAST_FACT, SLS_SALES_FACT, GO_TIME_DIM

when i looked at the diagram view, 3 relationships are connecting go_time_dim and sls_sales_fact

I created determinants for go_time_dim (for day, year, quarted, month)

When i verify model, i am getting the below warning

Code Select Expand

GO_TIME_DIM 
BMT-MD-2016 This 'Query Subject' and 'Query Subject' '[go_data_warehouse].[SLS_SALES_FACT]' have the following multiple relationships, or relationship shortcuts, defined between them: 'Relationship' '[go_data_warehouse].[GO_TIME_DIM <--> SLS_SALES_FACT]' and 'Relationship' '[go_data_warehouse].[GO_TIME_DIM <--> SLS_SALES_FACT1]'. Based on the relationship cardinalities, this creates redundancies. 


The 3 relationships are
Day_key  pointing to 1)order_day_key 2)close_day_key 3)ship_day_key

How to resolve this warning? please help

[MFGF]

Hi,

Using determinants is not a way of fixing multiple ambiguous relationships between two query subjects. When your model is used to generate SQL queries by reports, if items are included from both query subjects in the report, the two query subjects will be joined by one (and only one) relationship. If you have more than one (as in your case), the query engine will use the first alphabetical choice, based on the names given to each of the three relationships. This may or may not be the correct one to use, depending upon which specific date you wanted to report on.

The solution is to break the multiple relationships apart. To do this, create other versions of the Time Dimension query subject (called role playing dimensions) - so you end up with three Time Dimension query subjects, one for each date column in the SLS_SALES_FACT query subject. From memory, one is Order Date, one is Ship Date and one is Closed Date. So you will end up with a Time Dimension query subject linking via Order Date, a Ship Time Dimension query subject linking via Ship Date and a Closed Time Dimension linking via Closed Date.

The relevant guidelines in the FM User guide for this process are here:

http://publib.boulder.ibm.com/infocenter/cbi/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.ug_fm.10.1.0.doc%2Fug_fm_id20561bp-modeling_relational_data_sources.html

Cheers!

MF.

[wyconian]

Hi

Try adding aliases for the different dates e.g. one alias for order day, one for close day and one for ship day.

You can have multiple joins between a fact and a dimension (e.g. when the join is based on multuple columns***) but in your case this is going to cause issues becuase you have multiple columns in the fact joined to a single column in the date dimension. 

The determinants are a good idea but I don't think they do what you think they do.  Determinants on the time dim will allow you to roll up records to the various levels (day, year quarter etc) so if you had fact records joining to the dim on quarter (e.g. quarterly sales targets) and another joining on day (e.g. daily actual sales) you could report against both facts without double counting.

Anyway the point is alias your time dimension and have one join from the fact to each of the aliases.

Hope that helps :-)

*** you can have multiple joins between a dimension and the fact but ti is prossibly better to have a derived composite key as the basis for the join.

[kris123]

Thank you MF and wyconian

MF: the link is perfect, but I do not know how to create Order Day query subject (like wise ship day and close day)

Are the steps as below:
1. delete GO_TIME_DIM
2. right click and create new query subject ...select "Model"
3. and then  select available model objects (which ones are to be selected?)...Looks like duplicate GO_TIME_DIM 3 times but give 3 different names
4. draw relationship....

Please advice

[wyconian]

Hi

You could have 3 separate query subjects based on the time dimension just with different names, create these as you would the regular time dimension or you could have aliases based on a single time dimension query subject.  Aliases are easier to maintain as they pick up any changes to the parent query subject but you can't rename any columns in an alias.

[kris123]

Please can you help with steps.

Sorry, I am a Cognos starter.

[kris123]

Is create Alias shortcut same as creating an alias?

[MFGF]

If it was me, I would avoid using shortcuts to the time dimension query subject, because I would want to rename the query items within each to reflect their role. Assuming you are starting from a position of having a single time dimension query subject with three relationships to the fact query subject, do the following:

1. Right-click on the time dimension query subject, and choose "Merge in new query subject" - when prompted whether to re-create the relationships that exist forthe original query subject, answer No. Rename the new query subject to Ship Time Dimension, and rename each query item within it to reflect it's role (eg Year to Ship Year, Month to Ship Month etc.) Delete the relationship between Time DImension and your Fact query subject based on Ship Date, and create a relationship between your new Ship Time Dimension and your fact query subject, linking the Ship Date from Ship Time Dimension to Ship Date in your fact. Set the cardinality as 1..1 at the Ship Time Dimension end and 1..n at the fact end.
2. Repeat the same process to create a Close Time Dimension (obviously renaming as appropriate and linking to Close Date)

Regards,

MF.

[kris123]

great help!
I have renamed the first 1 as Order day
and used merge for ship and close day.