My client wants a report that looks something like this on a daily basis.
+---------------+-------------------------+-----------------+---------------------+-------------+
| ProductNumber | QuotationLineItemNumber | QuotationNumber | PurchaseOrderNumber | Status |
+---------------+-------------------------+-----------------+---------------------+-------------+
| PRD-1 | 1 | Q1 | PO1 | PO received |
| PRD-2 | 2 | Q1 | PO1 | PO received |
| PRD-3 | 1 | Q2 | NA | Awaiting PO |
+---------------+-------------------------+-----------------+---------------------+-------------+
My system is able to do the daily processing of QuotationLineItem and PurchaseOrderLineItem fairly okay.
The issue is with reporting which leads me to do many joins which is crazy.
I have looked at the Kimball book 3rd Ed of Data Warehouse Toolkit: Dimensional Modeling.
I am convinced that I need to have a separate database instance which is meant for OLAP situations to satisfy the reporting requirements.
Because of this, I need to design dimensional and fact tables.
My question is, it appears that I have at least 2 fact tables. QuotationLineItem
and PurchaseOrderLineItem
.
How do I generate a report like the above? Because from what I understand fact tables are not supposed to have foreign keys to each other else I will get back a snowflake schema.
Aucun commentaire:
Enregistrer un commentaire