Converting Great Plains SOP tables to fact rows
2 posters
Page 1 of 1
Converting Great Plains SOP tables to fact rows
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
I'm struggling with finding the best way to pull it and would appreciate any input.
Thanks in advance!
Dan
DanColbert- Posts : 11
Join date : 2009-02-03
Age : 54
Converting Great Plains SOP tables to fact rows
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?
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?
Converting Great Plains SOP tables to fact rows
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:
- SOPTYPE 3 in SOP10100 - a backorder of a line (or lines)
- SOPTYPE 2 in SOP30200 - historical copy of the original order
- 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
DanColbert- Posts : 11
Join date : 2009-02-03
Age : 54
Similar topics
» Reducing number of rows in fact tables
» Fact and dimension tables - avoiding same number of rows in both
» Versioning Fact rows?
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Having manually inserted 'Default' rows in DIM tables
» Fact and dimension tables - avoiding same number of rows in both
» Versioning Fact rows?
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Having manually inserted 'Default' rows in DIM tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum