I would like to get views from others on how to bring Order transactions and Payment transactions together in a report. Obviously we have the typical conformed dimensions: Vendor, Organization, Product and Account. However, our users want to see Orders and Associated payments at the Purchase Order, Purchase Order Line level of granularity.
Note: the Date Dimension is not conformed and the users want to see payments and orders based on 'Order Line Date' which is in the Orders fact table.
Our Orders and Payments fact tables have Order and Order Line as 'degenerate dimensions' embedded within. As far as cardinality goes, an order can have 1,0 or many payments. My solution to this problem whas to create an Alias to the Orders fact table, hide the Measures and treat it as a conformed dimension between Orders and Payments (named it CONFORMED_ORDERS). I linked Orders and Payments to CONFORMED_ORDERS on Order and Order Line. So the end result allows the user to choose order, and order line and order line date from the conformed dimension when bringing together measures from Payments and Orders.
This works with fair performance (typical query time has been 40 - 60 seconds). Does anyone else dealt with this issue? If anyone has alternative solutions (Cognos or Data model) I would enjoy hearing them.
Scott
I have done this before by using two distinct report queries, one for each Fact, then using a join query to bring those results together.
I find this approach to work well, because each query is working from one fact (star schema) and that means fast. The 2 resultsets are typically small (assuming SOME filtering) and will join locally rather fast.
I have often reverted to this method in place of using a single query from 2 facts that did not perform well.