Multi-source ETL integration streamlines monthly financial consolidation

[drewwizard]

Our finance team recently completed a major project to automate our monthly financial consolidation process using SSRS 2017 and SSIS. Before this implementation, our financial close process took 8-10 business days each month, with finance analysts manually consolidating data from five different ERP systems across our global subsidiaries. The manual consolidation involved copying data into Excel, applying currency conversions, mapping accounts to our corporate chart of accounts, and reconciling intercompany transactions. Beyond the time burden, we had significant accuracy issues - every month we’d find errors that required restatements.

We built a multi-source ETL solution with SSIS that pulls financial data from all five ERPs, applies standardized data mapping and transformation rules, and loads consolidated results into a financial data warehouse. SSRS enterprise reports now generate our monthly financial package automatically with full audit trail. The results have been transformative - we’ve cut close time to 3 days, eliminated manual consolidation errors, and given our CFO real-time visibility into financial performance.

[miguel_lead]

From an audit perspective, this sounds great but I need to understand the controls and audit trail. Can you trace every number in your consolidated financial statements back to the source transaction in the subsidiary ERP? How do you handle adjustments and corrections after the initial consolidation? And what about segregation of duties - who can modify the account mappings or currency rates? We need to ensure the automation doesn’t bypass proper financial controls.

[adit7566]

This is impressive! Multi-source ERP integration is notoriously difficult because each system has different account structures, transaction types, and data formats. How did you handle the account mapping across five different charts of accounts? And what about handling different fiscal calendars if your subsidiaries operate in different countries? We’re facing similar challenges with three ERPs and struggling with the mapping complexity.

[william_sage]

Account mapping was definitely our biggest challenge. We created a master mapping table in SQL Server that maps each subsidiary’s local account codes to our corporate chart of accounts. The table has about 2,500 mappings and includes validation rules - for example, all revenue accounts must map to 4000-series corporate accounts. We maintain this mapping table through a simple SSRS report where finance managers can propose changes that go through approval workflow. For fiscal calendars, we standardized everything to calendar months for consolidation, but we preserve the original fiscal period in the data warehouse so subsidiaries can still report using their local fiscal calendar.

[thomas_arch]

Here’s a comprehensive overview of our multi-source ETL integration for financial consolidation, covering the three key focus areas.

1. Multi-Source ETL with SSIS

We built a master SSIS package that orchestrates extraction from five subsidiary ERPs:

Architecture Overview:

• Source Systems: Oracle EBS (US subsidiary), SAP ECC (European subsidiary), Microsoft Dynamics (Asia subsidiary), NetSuite (Latin America), QuickBooks Enterprise (Small Canadian operation)
• ETL Frequency: Daily incremental loads for transaction data, monthly full refresh for master data
• Target: SQL Server 2017 financial data warehouse with star schema

SSIS Package Structure:

Master Package: FinancialConsolidation_Master

• Executes child packages sequentially for each subsidiary
• Manages error handling and logging
• Sends notifications on success/failure
• Generates ETL metrics report

Child Package Pattern (one per subsidiary):

Step 1: Pre-Extraction Validation

• Verify source system connectivity
• Check that source data is posted and closed for the period
• Validate that prior period loaded successfully
• Log extraction start time and row counts

Step 2: Data Extraction

• Pull general ledger transactions for the period
• Extract account master data
• Pull cost center and department hierarchies
• Extract intercompany transaction details
• Use incremental extraction based on last_modified_date

Step 3: Data Transformation

• Apply account code mapping using lookup tables
• Standardize transaction types across systems
• Convert local currency to USD using appropriate rates
• Calculate derived fields: fiscal period, quarter, year
• Flag intercompany transactions for elimination
• Apply data quality rules and flag exceptions

Step 4: Data Loading

• Load to staging tables with full lineage tracking
• Execute validation stored procedures
• Merge into dimension tables (SCD Type 2 for accounts)
• Load fact tables with surrogate keys
• Update control tables with load statistics

Error Handling:

• If any subsidiary extraction fails, others continue but consolidation doesn’t run
• All errors logged to ETL_ErrorLog table with source system, error message, and affected records
• Email alerts sent to ETL support team and subsidiary finance contacts
• Failed records quarantined in ErrorRecords table for investigation

2. Data Mapping and Transformation

The core of our solution is a sophisticated mapping framework:

Account Mapping Structure:

We maintain a central mapping table:

CREATE TABLE AccountMapping (
  SubsidiaryCode VARCHAR(10),
  LocalAccountCode VARCHAR(50),
  CorporateAccountCode VARCHAR(20),
  AccountDescription VARCHAR(200),
  AccountType VARCHAR(50),
  FinancialStatement VARCHAR(50),
  IsActive BIT,
  ValidFrom DATE,
  ValidTo DATE,
  CreatedBy VARCHAR(100),
  ApprovedBy VARCHAR(100)
);

Mapping Categories:

• Direct Mappings: 60% of accounts map 1:1 to corporate accounts
• Aggregated Mappings: 25% of local accounts roll up to single corporate account (e.g., multiple expense accounts → Corporate Travel Expense)
• Split Mappings: 10% require splitting (e.g., one local account contains both COGS and Operating Expenses → split to two corporate accounts)
• Conditional Mappings: 5% depend on other attributes (e.g., same account code maps differently based on cost center)

Transformation Rules:

Currency Conversion:

-- Pseudocode for currency transformation:
1. Identify transaction currency and target currency (USD)
2. Determine rate type: Average (P&L) or Period-End (Balance Sheet)
3. Lookup rate from CurrencyRates table for transaction month
4. Calculate converted amount: LocalAmount * ExchangeRate
5. Store both original and converted amounts for audit
6. Flag transactions where rate is missing or stale

Intercompany Elimination:

-- Pseudocode for IC elimination:
1. Identify intercompany transactions using IC account flags
2. Match debits and credits between subsidiaries
3. Calculate elimination entries (equal and opposite)
4. Generate elimination journal entries
5. Flag unmatched IC transactions for investigation
6. Produce IC reconciliation report

Data Quality Rules:

• All transactions must have valid account mapping (no unmapped accounts)
• Debits must equal credits within each subsidiary
• Material transactions (>$100K) flagged for review
• Negative revenue or positive expense flagged as exceptions
• Accounts marked as inactive in source system trigger alerts

3. Automated Financial Consolidation

Once data is loaded and validated, automated consolidation runs:

Consolidation Process:

Step 1: Pre-Consolidation Validation

• Verify all five subsidiaries have loaded data for the period
• Confirm total debits = total credits for each subsidiary
• Check that intercompany transactions are balanced
• Validate that control totals match subsidiary reports
• Ensure currency rates are available for all currencies

Step 2: Trial Balance Generation

• Aggregate transactions by corporate account code
• Separate by subsidiary for segment reporting
• Calculate beginning balance + activity = ending balance
• Generate subsidiary-level trial balances
• Produce consolidated trial balance

Step 3: Intercompany Eliminations

• Execute IC elimination logic
• Post elimination entries to consolidation tables
• Verify IC balances net to zero
• Generate IC elimination report for audit

Step 4: Financial Statement Preparation

• Map accounts to financial statement line items
• Calculate subtotals and totals
• Apply presentation rules (rounding, grouping)
• Generate comparative periods (current vs prior year)
• Calculate key financial ratios

Step 5: SSRS Report Generation

Enterprise Reports Produced:

1. Consolidated Income Statement

   • Current month, quarter, and year-to-date
   • Comparison to prior year and budget
   • Variance analysis with commentary fields
   • Drill-down to subsidiary detail

2. Consolidated Balance Sheet

   • Current period and prior period comparison
   • By subsidiary with consolidation columns
   • Drill-down to account-level detail

3. Cash Flow Statement

   • Operating, investing, and financing activities
   • Reconciliation to balance sheet changes

4. Segment Reporting

   • Revenue and profit by geographic region
   • By product line and customer segment

5. Intercompany Reconciliation

   • IC payables vs receivables by subsidiary pair
   • Unmatched IC transactions requiring investigation

6. Consolidation Audit Trail

   • Every adjustment and elimination with full documentation
   • Comparison of subsidiary reports to consolidated results
   • Currency translation gain/loss analysis

Report Delivery:

• All reports generated automatically on day 3 of close process
• Delivered via SSRS subscription to CFO, Controller, and subsidiary finance directors
• Published to secure SharePoint site with role-based access
• Excel export available for further analysis
• Drill-through enabled to view source transactions

Controls and Audit Trail:

To address Patricia’s audit concerns:

1. Lineage Tracking: Every consolidated amount can be traced back to source ERP transaction using LineageID field that links through all transformation stages

2. Segregation of Duties:

   • Only treasury team can update currency rates
   • Account mapping changes require finance manager proposal + controller approval
   • ETL execution is automated (no manual intervention possible)
   • Adjustment entries require dual authorization

3. Change Management: All mapping changes logged in AuditLog table with user, timestamp, old value, new value, and approval status

4. Validation Reports: Daily reconciliation report compares ETL-loaded amounts to subsidiary-reported totals, highlighting any discrepancies

5. Version Control: Each month’s consolidation is versioned. If corrections needed, we create new version while preserving original for audit trail

Results After 12 Months:

• Financial close reduced from 8-10 days to 3 days consistently
• Zero restatements due to consolidation errors (previously 2-3 per year)
• Eliminated 320 hours/month of manual Excel consolidation work
• Real-time visibility: CFO can view preliminary results by day 1 of close
• Audit efficiency: External auditors reduced testing time by 40% due to automated controls
• Cost savings: $180K annually in reduced overtime and consulting fees
• Enabled monthly forecasting process that wasn’t possible before due to time constraints

Key Success Factors:

1. Executive Sponsorship: CFO championed the project and secured buy-in from subsidiary finance teams

2. Cross-Functional Team: Finance, IT, and subsidiary representatives collaborated throughout design and testing

3. Phased Rollout: Started with two subsidiaries, proved the concept, then added remaining three

4. Extensive Testing: Ran parallel processes for 3 months before going live to validate accuracy

5. Training: Invested in training for subsidiary teams on new processes and exception handling

6. Continuous Improvement: Monthly review meetings to identify issues and enhancement opportunities

The automated consolidation has transformed our financial close from a manual, error-prone marathon into a streamlined, reliable process that gives leadership confidence in our financial reporting.

[arun_pro]

What’s your approach to handling currency conversion? Are you using fixed monthly rates or daily rates? And how do you handle the timing - convert at the subsidiary level before consolidation or after? We’ve been debating this design decision. Also curious about your data validation process - how do you ensure the ETL captured all transactions before running consolidation? Missing even one day of data could throw off the entire financial close.