F1 is connected to multiple D1,D2,D3…D7-------Current, there is self restricting join in the fact tables, group_id = 2
F2 is connected to multiple D1,D2,D3…D7-------history, group_id = 4
there are few dimension in Fact tables, when queried it creates 2 query and data from dimension are mapped correctly. But dimension from both fact table can be queried but can’t be added to the report structure.
I am missing something in the design.
Mark, it may be things like Order Line Number in an Order Fact table. Makes no sense to have them anywhere else. Makes more sense outside of banking where there is physical movement of items and more immediate attention to detail lines rather than looking at big numbers first then investigating the detail. I’ve done a lot more reactive reporting on row items where there is a transaction row identifier like order number or phone number called or such like.
To the OP - you won’t be able to add dimension items from Fact 1 into a table that contains Fact 2 information as there is no way to relate it - order line number from fact 1 would make no sense to items from Fact 2. It’s a logical restriction that preventions erroneous reporting.
You could present two separate tables side by side with data from the two fact tables in though.
I was including Order Line as a natural “key” of sorts, although I probably should have said source system generated item of data.
I would say that this depends.
I, too, have worked woth an Order > Service > Invoice type set up.
Granted, not all order lines became servces and invoice lines. However both orders and lines did persist consistantly as each ordere and seperate line had a status, with even cancelled, rejected etc still persisting from fact to fact.
MarkP your correct it like orderno, invoicenumber information. Both fact table are identical, one have one day old data other have the current data. so it is need to compare and see the status of the ordernumber from previous day and current day.
Fair enough - I was thinking of anything denormalised. Great minds…
I think the main caveat is business rules - if you know that a join from fact to fact is a one to one then okay, but I’d always tread carefully.
Ah. That’s a bit different. Have you considered another column in your current day table that holds previous day status? Sounds like a bit of ETL work to save a lot of BO pain.
Add a column to the table, add it to the ETL script, add the object to the universe, publish. Or have I oversimplified it?
(BOB member since 2005-01-06)