How to join multiple fact tables and aggregate based on date dimension

[robblob]

I have a situation where I have two fact tables that have an established relationship, but I have facts from the second table that I need to aggregate on a common date.  I've attached an example of the situation I'm up against to help paint a picture.  It seems like this should be easy to model, but so far I haven't had any luck in getting the expected results. 

In my example, I'm joining my fact table to my date table where Day = Create_Date.  Everything in this join works as expected.  Once I join Employee_ID to Employee_ID from my Calls table to Fact Table, as soon as I try to pull in Call_Count it pulls bizarre numbers.  I've tried bringing my Date Table in again and joining off the Calls table which didn't work.  I attempted creating a separate query in report studio to aggregate Call_Count by month and then joining to the Fact Table, but I ended up with a cross join error.  I'm just not really sure what the best approach is to do this in Cognos.  If anyone has any suggestions, please let me know.

[MFGF]

I have a situation where I have two fact tables that have an established relationship, but I have facts from the second table that I need to aggregate on a common date.  I've attached an example of the situation I'm up against to help paint a picture.  It seems like this should be easy to model, but so far I haven't had any luck in getting the expected results. 

In my example, I'm joining my fact table to my date table where Day = Create_Date.  Everything in this join works as expected.  Once I join Employee_ID to Employee_ID from my Calls table to Fact Table, as soon as I try to pull in Call_Count it pulls bizarre numbers.  I've tried bringing my Date Table in again and joining off the Calls table which didn't work.  I attempted creating a separate query in report studio to aggregate Call_Count by month and then joining to the Fact Table, but I ended up with a cross join error.  I'm just not really sure what the best approach is to do this in Cognos.  If anyone has any suggestions, please let me know.

Hi,

Does the attached image reflect how you have modelled your query subjects in FM? If so, I can see the problem. In a true Star Schema paradigm, Fact tables never join directly to other Fact tables - only indirectly via Conformed Dimension tables.

First, remove the relationship between Calls and Fact Table. Then add a relationship between calls and Date Table, with Calls at the n end and Date Table at the one end. If you also need a breakdown by Employee, you're going to need an Employee dimension query subject too. This can be based on Calls (a shortcut is the easiest), and it would link 1-to-n to Fact Table and 1-to-n to Calls.

Cheers!

MF.

[robblob]

Hi,

Does the attached image reflect how you have modelled your query subjects in FM?

Cheers!

MF.

MF,

I attached an image base on the original model that I created when I first started down this path.  I've tried several iterations, but nothing has worked.  I wanted to provide as simple of an example as possible to show the dilemma.

I think I get the idea of what you're saying to do.  I will give it a shot today and see what I can work out.  As always, I appreciate the help!

-Rob