ETL SYSTEM DESIGN

ETL systems are used to Extract data from heterogeneous sources, Transform data (merge data from various systems, DE-normalize data, create conformed dimensions, compute fact tables), finally Load data into target OLAP system. In other words, they should feed data into OLAP systems such that content is ready to be designed according to business needs, query execution is optimized. Also, data cleansing, validation and data quality also should be ensured by ETL systems.

on a whole ETL systems should take care of below issues.

1. Transform data into conformed dimensions/metrics suitable to model according to business needs
2. Validate input data and cleansing data to ensure quality data input to OLAP system
3. Merge data from multiple systems alias data integration. In general ERP systems will have multiple master       data management systems. Also different user groups/departments might used multiple systems. Data from     all these sources should be consolidated.
4. ETL systems should also take care of Data Archiving.


Comments

Post a Comment

Popular posts from this blog

SAP V1 V2 V3 JOBS

Image
Before starting with V-series jobs, lets review some fundamentals. Data base LUW:  It consists of sequence of DB operations(INSERT,UPDATE,DELETE) that needs to be committed to either all or none to keep DB in consistent state. COMMIT will be executed when all DB operations were successfully executed, once COMMITTED these changes can't be ROLLED BACK. This is part of traditional ACID property of any RDBMS. If a failure happens while executing the LUW(will be mostly technical like memory crunch,integrity violation or data base crashed,power failure) and when DB is restarted, DB will be ROLLed BACK to previous state. Which is nothing but the state when last COMMIT was happened. In other words, when ROLL BACK happens DB will be safely brought to previous COMMIT state. DB maintains log of actions performed since last COMMIT and as part of recovery process, all these actions will be reversed. All RDBMS will have in-built locking/multi concurrency control such that dirty READ-WRITE ope...
Read more

Virtualization with SAP HANA

Image
Virtualization Most of Corporate Enterprise warehouse Eco system consists multiple disparate  Data warehouses. reason can be attributed to mergers and acquisitions,  departments using specialized software, databases choose to support specific data needs. All these scenarios result in building multiple data marts over multiple technologies. However, to serve the purpose of single source of truth, we end up building Enterprise Data Warehouse(EDW)  by sourcing data from various data marts across organization. One way of doing is, running a project for establishing standard ETL methods to extract data from regional Data Marts and staging data into EDW. However various departmental/ regional DW already contains highly formatted, information rich and aggregated data built readily available. This implies that we might not need to 1. Perform extensive transformations 2. Source data is meta data rich 3. Less data volumes will be retrieved from source systems Following trad...
Read more

Star Join node Vs Analytic view && Attribute View vs Calculation Dimension View

Analytic views comes with constraint that Measures should be coming from a single of Data foundation, although data foundation could contain any number of tables joined. Previously, we used model multiple Analytic views and combine them using JOIN node of an calculation view, in order to facilitate to Measures from multiple tables.  Star Join solves this by allowing measures coming from multiple tables. So we don't have to use multiple Analytic view to achieve this.  However Analytic views comes enriched with master data through Attribute views. To compensate this, We have Calculation Dimensional Models introduced. These artifacts are same as  Attribute views, but can only be used in a Star Join. So this solves out Master data enrichment issue while using Star Join. Introduction of Star Join Node and Calculation Dimensional Models  certainly introduces more uses cases for Calculation views and avoids scenario of creating multiple Analytic views for e...
Read more