Converting Great Plains SOP tables to fact rows

View previous topic View next topic Go down

Converting Great Plains SOP tables to fact rows

Post  DanColbert on Thu Jul 02, 2009 3:35 pm

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
avatar
DanColbert

Posts : 11
Join date : 2009-02-03
Age : 48

View user profile

Back to top Go down

Converting Great Plains SOP tables to fact rows

Post  jpayton on Fri Jul 03, 2009 4:29 pm

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?

jpayton

Posts : 10
Join date : 2009-05-08
Location : Ontario, Canada

View user profile http://www.dynamicintelligence.ca

Back to top Go down

Converting Great Plains SOP tables to fact rows

Post  DanColbert on Mon Jul 06, 2009 7:52 am

jpayton wrote: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
avatar
DanColbert

Posts : 11
Join date : 2009-02-03
Age : 48

View user profile

Back to top Go down

Re: Converting Great Plains SOP tables to fact rows

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum