Multiple Fact modelling - Best approach

[Ahchay]

Hello all, hoping someone here can help with this as I'm tearing what little hair I have left out trying to get this to work.

One liner - how can I get Cognos 8.4 to correctly model multiple fact schemas?

More details. I have two fact tables, at different levels of granularity for our booking and itinerary information (we're a holiday company, but these are roughly analogous to an invoice and invoice line tables). The general relationship here is that one booking contains many invoice lines.

We have modelled these into two fact tables with associated dimensions - as follows:
DIM_BOOKING

i_SK_Bkg	BookingRef	LeadName
1	1000	C SMITH
2	1001	C JONES
3	1002	S BOYCE

DIM_ITIN

i_SK_Bkg	i_SK_Itin	ItinCode	ItinDesc
1	1	FLT001	Flight
1	2	ACC001	Accommodation
1	3	EXT001	Ski Hire
1	4	EXT002	Boot Hire
1	5	EXT003	Lift Pass
2	6	FLT001	Flight
2	7	ACC001	Accom
3	8	FLT001	Flight
3	9	ACC001	Accom
3	10	EXT001	Ski Hire
3	11	EXT003	Lift Pass
3	12	EXT003	Lift Pass
3	13	EXT003	Lift Pass

FACT_BOOKING

i_SK_Bkg	i_SK_ConfirmDate	i_SK_DepartDate	f_Pax	f_Revenue
1	20111001	20111218	4	1800
2	20111005	20111225	2	750
3	20111017	20120104	3	1300

FACT_ITIN

i_SK_Bkg	i_SK_Itin	i_SK_ItinDate	f_Pax	f_Revenue
1	1	20111218	4	300
1	2	20111218	4	500
1	3	20111218	3	300
1	4	20111220	2	700
2	5	20111225	2	250
2	6	20111225	2	500
3	7	20120104	4	300
3	8	20120104	4	250
3	9	20120104	1	100
3	10	20120104	2	200
3	11	20120106	3	400
3	12	20120109	1	100

I have modelled this as two seperate star schemas, joined by the common dimension DIM_BOOKING. I understand that I should then use determinants to force aggregation to occur at different levels, but no combination of these that I have tried seems to be quite right. The best I have managed to get is to set a Uniquely Identified determinant on the FACT_BOOKING table which gives me correct results in list mode, but not in cross-tabs (or, at least, not in all cross tabs)

Using a determinant at booking level gives me the following results on a per-booking cross tab:

	Booking Pax	Itin Pax
1000	3	13
1001	2	4
1002	3	15
Totals	9	32

Which is correct, however, if we try and run a per-itin description breakdown we get the following:

	Booking Pax	Itin Pax
Flight	9	10
Accommodation	9	4
Ski Hire	9	5
Boot Hire	9	2
Lift Pass	9	6
Accom	9	5
Totals	54	32

Whereas I would expect that to be as follows:

	Booking Pax	Itin Pax
Flight (all bookings)	9	10
Accommodation (Booking 1 only)	4	4
Ski Hire (Bookings 1 and 3)	7	5
Boot Hire (Booking 1 only)	4	2
Lift Pass (Booking 1 and 3)	7	6
Accom (Bookings 2 and 3)	5	5
Totals	9	32

I think that's all the information I can provide on this, but feel free to ask for clarification - does anybody have any ideas on this?

[Ahchay]

Double post, but trying to get attachments to post. Attachment shows the logical join layer that I am currently using in FM.

Okay, uploaded to Flickr:


MultiFact by Ahchay, on Flickr

[blom0344]

Quick hint look at cardinality Booking Dimension to Booking Fact  (1:1 -- 1:1). A real multifact would be  1:1 -- 1:n..
I personally do not see the different grain by the way..

[Ahchay]

Quick hint look at cardinality Booking Dimension to Booking Fact  (1:1 -- 1:1). A real multifact would be  1:1 -- 1:n..

I've tried that as both, but in effect the dimension to fact relationship (in this example) is actually 1:1 - I get the same results though with this as 0:n

I personally do not see the different grain by the way..

A single booking can have multiple itiinerary items with the same code/description. For instance, 4 people might be on the booking but they may, between them, have booked several tuition days. So the booking pax (or passengers) would always be 4, but the Itin pax for Tuition could be as many as 12.

[blom0344]

I would expect DIM_ITIN to be the shared dimension as it stores the repective keys  i_SK_Bkg  and i_SK_Itin. You can lookup the documented multifact example where the time dimension is the shared one. The following link points to the official pdf:


http://www.google.nl/url?sa=t&rct=j&q=Modeling%2Bin%2BFramework%2BManager%2Bfor%2BPredictable%2BQueries%2Band%2BResults&source=web&cd=1&ved=0CCAQFjAA&url=http%3A%2F%2Fapps.carleton.edu%2Fcampus%2Fits%2Fcognos%2Fassets%2FModeling_in_Framework_Manager_Best_Practices.pdf&ei=9K2lTsPUL4Kc-wactK2LBQ&usg=AFQjCNEJisIdfwIdwm_wKyyl983040X5sA

[Ahchay]

Thanks for the link blom0344 - am trying to work through that now. Notice that the date on that document is 2005 - is this still valid for 8.x?

Oh, and I added in i_SK_Bkg in a previous attempt to do just as you described - I get the same results with that as by setting the determinant on FACT_BOOKING

[blom0344]

The modelling core does not depend on the exact 8 version. I think 2005 was the year of the 8.1 MR2 release, though in this case ReportNet (strictly a pre 8 version) is mentioned.
The shared dimension would have a unique determinant on the unique key for the dimension and a 'group by' determinant for the non-unique one

[Ahchay]

Thanks for your patience with this, but still not working for me :(

I've tried remodelling the tables into a single fact table, as we've had some success doing things this way (but still not quite right!)

FACT_ITIN

i_SK_Bkg	Key to Booking determinant
i_SK_Itin	Key to Itin Determinant
i_SK_ItinDate	Key to Itin Determinant
f_ItinPax	Itinerary Pax - Member of Itin Determinant
f_BookingPax	Booking Pax - member of Booking Determinant

i.e. I've combined both facts into a single table, with a Group By determinant on the booking and a Unique determinant for the itinerary line.

This *almost* works - as shown:

	ItinPax	BookingPax
Accom	5	3
Accommodation	4	4
Boot Hire	2	4
Flight	10	9
Lift Pass	6	5
Ski Hire	5	7
Total(ItinDesc)	32	32

So, each of the per itinerary rows are correct (i.e. there are seven passengers on bookings which contain ski hire, although only 5 of those have actually bought it) but the Total line is still summing up all of the rows above it. What this needs to say is that, overall, we have sold to 9 passengers, but we have sold 32 individual things to them - as follows:

	ItinPax	BookingPax
Accom	5	3
Accommodation	4	4
Boot Hire	2	4
Flight	10	9
Lift Pass	6	5
Ski Hire	5	7
Total(ItinDesc)	32	9

Is this something that I can affect in the report?

(Edited for formatting)

[blom0344]

It IS right in the context of your initial issue, but you now want a a customized total in the report, which is okay, but has little to do with the modelling part. It basically means that you need to adjust the definition of the totalization to suit your need, like:

total(distinct [customerid] for report) if your query also fetches the customerid's involved

[Ahchay]

Okay, I'll try that (or, more exactly, I'll ask our report writing team to have a go - we're through the looking glass on this one with regards to my Cognos report writing skills) and let you know how we get on.