Cross Joins with Multiple Fact Tables 1

[angi]

We have a hierarchical database containing the following:

Res_Summary
------------------------
ReservationKey (Key)
TourOperator
SBU
Company
ReservationCarrier
ReservationOrigin
ReservationDestination
ReservationDepartureDate
ContactName
AirSales
LandSales


Res_Air_Detail
------------------------
ReservationKey (Key)
ReservationSequence (Key)
FlightSequence (Key)
PackageOrigin
PackageDestination
PackageCarrier
PackageDate
FlightClass
NumberOfSeats
Sales


Res_Hotel_Detail
------------------------
ReservationKey (Key)
ReservationSequence (Key)
ItemSequence (Key)
PackageOrigin
PackageDestination
PackageCarrier
PackageDate
HotelCode
LengthOfStay
Sales


Essentially, the "Detail" tables contain the individual components of a reservation. The "Detail" tables link to the "Summary" table via the ReservationKey column. The "Summary" table essentially rolls up the values from the "Detail" tables for a given ReservationKey.

We seem to be in a catch-22 with how we set up the schema. If I create a report that contains the following columns:
Res_Summary.ContactName
Res_Hotel_Detail.HotelCode
Res_Hotel_Detail.LengthOfStay

I receive a cross-join between Res_Summary and Res_Hotel_Detail even though I have an attribute set up that joins Res_Summary.ReservationKey and Res_Hotel_Detail.ReservationKey. In addition, the user's answer to the ReservationDepartureDate prompt is ignored. Note that my report does contain an attribute from Res_Summary (ContactName).

I can get rid of the cross join if I set up all of the parent-child relationships in the tables. For example:
ReservationCarrier is a child of ReservationKey
ContactName is a child of ReservationKey
PackageCarrier is a child of ReservationKey + ReservationSequence + ItemSequence
etc.

With these added relationships, the cross join disappears; however, these parent-child relationships introduce another problem. Security filters are applied to users based on TourOperator, SBU, and Company. In the case where a user has access to a finite set of SBU's and the report that the user is running contains a prompt for PackageCarrier, MicroStrategy will read through the entire "Detail" table trying to gather a distinct list of PackageCarriers that apply to that user's TourOperator, SBU, and Company permissions. Here, it uses the ReservationKey to join Res_Summary and Res_Hotel_Detail to find the distinct PackageCarriers for the TourOperator, SBU, and Company that the user has access to. This causes a timeout given that the "Detail" table contains millions of rows. In reality, I don't even want the user's list of PackageCarriers to be limited to their SBU permissions; I just want to display all of the PackageCarrier values from a lookup table, but the lookup table is ignored.

Any suggestions on how to set up the schema for this. Do we need to set up parent-child relationships between all of the columns of the fact tables to the table's key? If so, how can we stop MicroStrategy from its attempt to find all of the distinct PackageCarriers, PackageOrigins, PackageDestinations, etc? If we don't need the parent-child relationships, how do we link multiple fact tables in one query? Are multiple fact tables not allowed in the same query?

Note that our database is not a star schema. It is hierarchical in nature with multiple "Detail" tables rolling up to one "Summary" table. We do have occasion though where we want to grab attributes from multiple fact tables. We typically wouldn't grab metrics from multiple fact tables though, just attributes.

Thanks for your help.
IGA

 

[reisw]

I'm not sure if I understand everything you described perfectly but give this a try.

1. I would create a lookup table or view that contains all of the distinct reservation key values from all three tables(summary and details). If there is a description that goes along with the key I would include it in that table too.

2. Create another table or view that contains all of the contacts fromthe summary table.

3. Now create a "reservation" attribute using the new table or view and make sure it joins to the correct columns on the summary and detail tables.

4. Create a contact attribute that uses the new lookup table and makes sure it joins to the summary table.

It looks like the summart table would contain a many to many between contact and reservation. Is that correct?

Let me know if this helps.

 

[reisw]

One thing i forgot to mention is that you might also be able to use a joint child relationship with the contact and reservation attributes. Give that a try too.