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

Question

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.

EDIT

As requested, these are my source tables which are in a 3NF database schema.

Tables include but not limited to:

• quotation_line_items
• products
• quotations
• prices
• purchase_orders

The relationships are

• products is many-to-many to quotations via quotation_line_items.
• products is one-to-many prices
• purchase_orders is one-to-many to quotations

EDIT 2

After some clues and further revision, I realized that maybe the solution is to have two fact tables that have common dimensional tables.

The 2 fact tables are:

• Quotation Line Item (this is transaction)
• Quotation Processing Workflow (this is accumulating snapshot)

The dimensions are:

• Product (for Quotation Line Item)
• Customer (for both tables)
• Sales Rep (for both tables)
• Purchase Order (for Workflow)
• Quotation Header (for Workflow)

Then I do a drill across the 2 fact table. Although I am not sure how to do that using MySQL and PHP.

Attached is the drill across concept I re-read in the book.

EDIT 3

Okay I just realized that drill across basically is :

1. both fact tables need to have some common dimensions and the row headers must be from the common dimension table.
2. perform queries on the 2 fact tables separately
3. perform an outer join on the 2 result sets.

Based on condition 1, my report does NOT require an identical conformed attribute from a common dimensional table.

Because there is no Quotation Header dimension table for Quotation Line Item Fact table.

Do I simply do separate queries on both fact tables and then do an inner join on the result sets?

Or I am mistaken about my fact tables?