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?
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:
(http://farm7.static.flickr.com/6218/6276667178_2e4dc571a8.jpg) (http://www.flickr.com/photos/36913684@N00/6276667178/)
MultiFact (http://www.flickr.com/photos/36913684@N00/6276667178/) by Ahchay (http://www.flickr.com/people/36913684@N00/), on Flickr
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..
Quote from: blom0344 on 24 Oct 2011 09:16:39 AM
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
Quote from: blom0344 on 24 Oct 2011 09:16:39 AM
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.
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
(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)
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
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
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)
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
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.