BUY v/s BUILD:

When it comes to ETL tool selection, it is not always necessary to purchase a third-party tool. This determination largely depends on three things:

Complexity of the data transformation: The more complex the data transformation is, the more suitable it is to purchase an ETL tool.

Data cleansing needs: Does the data need to go through a thorough cleansing exercise before it is suitable to be stored in the data warehouse? If so, it is best to purchase a tool with strong data cleansing functionalities. Otherwise, it may be sufficient to simply build the ETL routine from scratch.

Data volume: Available commercial tools typically have features that can speed up data movement. Therefore, buying a commercial product is a better approach if the volume of data transferred is large.

Important Data WareHousing Terms

Aggregation: One way of speeding up query performance. Facts are summed up for selected dimensions from the original fact table. The resulting aggregate table will have fewer rows, thus making queries that can use them go faster.

Attribute: Attributes represent a single type of information in a dimension. For example, year is an attribute in the Time dimension.

Conformed Dimension: A dimension that has exactly the same meaning and content when being referred from different fact tables.

Data Mart: Data marts have the same definition as the data warehouse (see below), but data marts have a more limited audience and/or data content.

Data Warehouse: A warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process (as defined by Bill Inmon).

Data Warehousing: The process of designing, building, and maintaining a data warehouse system.

Dimension: The same category of information. For example, year, month, day, and week are all part of the Time Dimension.

Dimensional Model: A type of data modeling suited for data warehousing. In a dimensional model, there are two types of tables: dimensional tables and fact tables. Dimensional table records information on each dimension, and fact table records all the "fact", or measures.

Dimensional Table: Dimension tables store records related to this particular dimension. No facts are stored in a dimensional table.

Drill Across: Data analysis across dimensions.

Drill Down: Data analysis to a child attribute.

Drill Through: Data analysis that goes from an OLAP cube into the relational database.

Drill Up: Data analysis to a parent attribute.

ETL: Stands for Extraction, Transformation, and Loading. The movement of data from one area to another.

Fact Table: A type of table in the dimensional model. A fact table typically includes two types of columns: fact columns and foreign keys to the dimensions.

Hierarchy: A hierarchy defines the navigating path for drilling up and drilling down. All attributes in a hierarchy belong to the same dimension.

Metadata: Data about data. For example, the number of tables in the database is a type of metadata.

Metric: A measured value. For example, ‘total sales’ is a metric.

MOLAP: Multidimensional OLAP. MOLAP systems store data in the multidimensional cubes.

OLAP: On-Line Analytical Processing. OLAP should be designed to provide end users a quick way of slicing and dicing the data.

ROLAP: Relational OLAP. ROLAP systems store data in the relational database.

Snowflake Schema: A common form of dimensional model. In a snowflake schema, different hierarchies in a dimension can be extended into their own dimensional tables. Therefore, a dimension can have more than a single dimension table.

Star Schema: A common form of dimensional model. In a star schema, each dimension is represented by a single dimension table.

Reading & Writing Files - Stage Properties

Reading & Writing Files - Stages
Stage Name	Input Link	Output Link	Reject Link	Executes in
Data Set Stage	1	1	0	Can be configured to execute in parallel or sequential mode
Sequential File Stage	1	1	1	The stage executes in parallel mode if reading multiple files but executes sequentially if it is only reading one file. The Stage executes in parallel if writing to multiple files, but executes sequentially if writing to a single file.
File Set Stage	1	1	1	It only executes in parallel mode.
Look up File Set Stage	1	1 (must be reference link)	0	The stage can be configured to execute in parallel or sequential mode when used with an input link.
External Source Stage	0 (Takes input from 1 or more source programs)	1	1	It can be configured to execute in parallel or sequential mode.
External Target Stage	1	0 (Allows you to write data to one or more source programs)	1	It can be configured to execute in parallel or sequential mode.
Complex Flat File Stage (used as source)	0 (Reads from one or more complex flat files)	n	0	CFF source stages run in parallel mode when they are used to read multiple files, but you can configure the stage to run sequentially if it is reading only one file with a single reader.
Complex Flat File Stage (used as target)	1	0 (You can write data to one or more	1	Can write to one or more complex flat files. But cannot write to MVS data sets or to files that contain multiple record types.