Converting Great Plains SOP tables to fact rows

[DanColbert]

Has anyone had experience extracting Great Plains Sales Order Processing (SOP) data and modeling it dimensionally?

I'm struggling with finding the best way to pull it and would appreciate any input.

Thanks in advance!

Dan

[jpayton]

Hi Dan,

Working with Great Plains SOP data generally includes looking at the SOP30200 and SOP30300 tables for transaction data, in addition to many other tables (RM00101, IVC tables, other RM tables). You also need to consider the document type and the posting status - lots of things to be aware of. If you're new to the table structure, I'd suggest accessing the Tools -> Resource Descriptions -> Tables menu item to get a better feel for the content of each table. Beyond that, is there something in particular you are looking to determine?

[DanColbert]

Hi Dan,

Working with Great Plains SOP data generally includes looking at the SOP30200 and SOP30300 tables for transaction data, in addition to many other tables (RM00101, IVC tables, other RM tables). You also need to consider the document type and the posting status - lots of things to be aware of. If you're new to the table structure, I'd suggest accessing the Tools -> Resource Descriptions -> Tables menu item to get a better feel for the content of each table. Beyond that, is there something in particular you are looking to determine?

Thanks for the feedback. I am very familiar with the operational data structure (though I can see where my very general question wouldn't have indicated that...).

The challenge I am facing is flattening out the multiple occurences of a single customer order across several different SOPTYPEs. A single CSTPONBR can occur in any combination of Quote, Order, Backorder, Return or Invoice. I will represent each of those SOPTYPEs as a DateKey reference in my fact table. Then each customer order detail line will potentially have an OrderDateKey, BackorderDateKey, InvoiceDateKey, ReturnDateKey, etc.

Additionally, a single CUSTPONBR can appear in several SOPTYPEs at the same time. Here is a legit combo I've seen:

1. SOPTYPE 3 in SOP10100 - a backorder of a line (or lines)
   
2. SOPTYPE 2 in SOP30200 - historical copy of the original order
   
3. SOPTYPE 3 in SOP10100 - the invoice for the non-backordered items
   

I guess the main thing I am looking for is anyone else who has dealt with transforming the GP data into a dimensional model and how they solved the issue.

Thanks again for the input!

Dan

[Sponsored content]