lundi 2 février 2015

how many fact tables do I need given I want to build an OLAP for Quotation Line Item and Purchase Order Line Item report?


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