Choosing the Data Source for your Report

Source ERP

Pros

• Familiar application database structure
• Real-time availability of data
• Contains all data

Cons

• Data is unsorted and separated
• Can have unclear naming
• Reports may run slower due to data being split across multiple tables
• Can be difficult to include multi-company data in the same report

• Real-time data is needed
• Non-complex reports:
  • Limited number of tables; the more tables you need, the slower it becomes.
  • No need to use multiple fields to calculate a single outcome.
• Small data throughput
  • ERP systems are designed for input
  • Give priority to input vs output
• Non-commonly used tables or fields are needed

Data Warehouse

Pros

• Built from source data
• Data is sorted and consolidated
• Tables and fields from source data are consolidated into new tables
• Multiple companies or data sources pulled into one source
• Better named tables and fields
• Reports can run faster due to fewer tables and smaller data set
• Can build your own indexes for performance tuning

Cons

• Not real-time data
  • Typically a 1-day delay
• Only contains data selected from source data

• Real-time data not required
• Need for detailed reports
• Faster execution times
• Consolidated reporting
  • Multiple companies
  • Multiple data sources

Cubes

Pros

• Built from data warehouse
• Data is pre-calculated and aggregated
• Clearly named
• Reports with calculations run more efficiently due to pre-calculated data
• Easily build Pivot Tables and dashboards

 Cons

• Not real-time data
  • Typically 1-day delay
• Generally not detail-level information

• Fastest execution times
• Consolidated reporting
  • Multi-company
  • Multi-data source
• Aggregated data reporting
  • Summarized data
  • Complex calculations
• Real-time data not needed
• PivotTable needed