We recently started working on our Data warehouse. We have Technicians, Salespersons, Date, Branch, Customer as our dimensions. We also have transactional tables in OLTP such as Sales Orders, Agreements, Which are referenced to each other in some situations. I'm planning to put sales order, Agreements info in Fact tables. So, that I would like to reference the all the above mentioned dimensions in both fact tables. But, here comes my problem. Sales orders and service agreements need to referenced with each other. In most cases, Agreements information need to be referenced in Sales orders. Can I reference two fact tables each other in fact table? The sales orders table in OLTP consists Million records, and Agreement table holds half million records(minimum). Can you let me know if I can reference these two in fact table?
Do you have something like an AgreementID which can be added to each fact table and then used to drill across? This is a degenerate dimension - a dimension key without a dimension table.