Project - Importing Financial Information Into The Global Data Warehouse

The Global Data Warehouse provides financial, sales, and customer information at the branch and corporate level.  Many reports could be generated such as: item profitability per market by territory, sales per product class by region, etc..  In our division two different ERP systems were used across the branches - I was the only person familiar with the local ERP, and we were missing data from the local branch.

Some complications:

• stock codes, product class, and G/L codes and coding were not uniform across branches
• the ERP Company contained multiple legal entities
• each legal entity had different and specific G/L codes
• one ERP used posted dates and the other used date entered
• branches had different year end dates (different fiscal month identifiers)
• the ERP's had different fields (counts and lengths) leading to long customer names continuing into the first customer address field, and the use of comma's, symbols, and acronym's, etc.
• due to a restriction in the number of characters allowed, customer address information was not properly formatted in the ERP
• sales information had been partially misinterpreted during the import procedure
• the team was not clear as to how the local database stored information, making it difficult to extract data from the correct tables

Challenges:

• the branch ERP had not been maintained, requiring constant adjustments to my export scripts as I corrected stock codes, Bills of Materials, Product Classes, G/L coding, and G/L setup
• per financial reporting requirements, when provided with a legal entity and date range my scripts needed to present totaled month / year amounts per each of the G/L report entries
• the financial year was changed to a calendar year in the local branch
• some companies were amalgamated in the ERP, forcing the creation of a new Company in the ERP

I worked with both Accounting and the Data Warehouse Team to import the local branch financials, customer information, and sales information into the Data Warehouse.  After rebuilding the local branch financial reports, I referred to the GL codes used in the reports when creating the SQL Functions that massaged the extracted values into the required reporting format.  My function considered the various legal entities set up in the ERP, that the financial year had recently been changed, and that there was legacy / historical data also stored in the ERP.  My scripts considered multiple legal entities, across two ERP Companies, each having different Year Ends and posting months, and different G/L codes used. I developed a replacement Structured G/L that, if implemented in the branch ERP, would eliminate the need to remap G/L entries during import into the GDW and that would standardize GL coding throughout the branches.  My SQL Functions worked as expected, matching the Income, Balance, and Cash Flow reports to within one cent.  I later developed a replacement Structured GL that, if implemented in the local branch ERP, would allow GL entries to be directly translated into the Global Data Warehouse while considering the financial reporting requirements of the local branch - unfortunately this was not realized prior to my leave.

I also developed an SQL Function to extract and condition customer contact information for import into a Global Customer Master database.  Due to ERP field size restrictions, often companies having longer names will have the last part of their name found in the first address field.  My script analyzed these entries, adjusting the customer name and address information as needed to ensure proper presentation to the Global Data Warehouse.

In consultation with the Data Warehouse Team, I also helped diagnose issues relating to the importing of sales information from the local branch ERP database.  I helped solidify their understanding of which fields needed to be imported, how to interpret their contents, and the mathematical steps needed to calculate line and order totals depending on the customer configuration (pricing contracts, selected pricing levels, and discount options).