1

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.

Drill Across description

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?

  • added. I am not going to list all the other tables. Only the crucial ones so far. – Kim Stacks Feb 3 '15 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.