4 Replies Latest reply: Oct 27, 2017 2:49 AM by Jonathan Dienst RSS

    Need Help - Analyzing data using non-common data fields across fact tables linked by common fields with a LINK TABLE

    Michael Venable

      Successfully loaded the various fact tables and generated link-table in load script.  The issue I am having is aggregating or summing across fact tables.  In my current data model i have the following fact tables:

       

      Vendor Master

      Vendor Address

      Invoice

      Requisition

      PO LIne Item

      PO Master

      Disbursements

      Receiving

      Employee Master

       

      Various fields are shared across the tables but no one field or fields are shared across all.  I generated a link table to account for this (see attached for link table script). 

       

      I am having issues summarizing across fact tables based on on non-common data fields. 

       

      For example:  Using Vendor Name from the Vendor Master File as the dimension calculate the total dollar values of payments, and invoices, requisition, and purchase orders.   The Vendor ID is foreign key or common field across the Vendor Master File, Invoice, Requisition, and Purchase Order tables and the PO Number is the foreign key between the PO table and the Invoice table.

       

      With the Vendor Name field in the Vendor Master Table I believe i should be able to calculate this.  However, when i do so it just says all values are null.  However, when i use VendorID it works.  Is there something i am missing or should I be using INDIRECT SET ANALYSIS.  Any help or guidance would be very much appreciated.  The data sets are on the larger side 400 GB in total.  Could this be causing the error?