Extract, transform and load (ETL)

Extract, transform and load (ETL) is a process in database usage and especially in data warehousing that involves:
Extracting data from outside sources
Transforming it to fit operational needs (which can include quality levels)
Loading it into the end target (database or data warehouse)

http://en.wikipedia.org/wiki/Extract,_transform,_load

• SSSI (SQL Server Integration Services) Microsoft

Microsoft Integration Services is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing

Integration Services includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as FTP operations, executing SQL statements, and sending e-mail messages; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management service
http://msdn.microsoft.com/en-us/library/ms169917(v=sql.105).aspx

Ad-Hoc Query

"ad hoc" reporting systems allow the users themselves to create specific, customized queries.
Typically this would be via a user-friendly GUI-based system without the need for the in-depth knowledge of SQL, or database schema that a programmer would have.
Because such reporting has the potential to severely degrade the performance of a live system, it is usually provided over a data warehouse.
Ad hoc querying/reporting is a business intelligence subtopic, along with OLAP, data warehousing, data mining and other tools.
http://en.wikipedia.org/wiki/Ad_hoc

An Ad-Hoc Query is a query that cannot be determined prior to the moment the query is issued.
It is created in order to get information when need arises and it consists of dynamically constructed SQL which is usually constructed by desktop-resident query tools.
http://www.learn.geekinterview.com/data-warehouse/dw-basics/what-is-an-ad-hoc-query.html

ERP , SCM, CRM

Information Technology (IT) departments for solutions to achieve greater efficiency and for business software to improve customer service

Business managers are always looking to compare the cost of IT solutions with the potential return on investment (ROI)

• ERP, the Complete Business Solution

The biggest decision and most risky solution is that of an Enterprise Resource Planning system, or ERP.
This solution can be a complete replacement for all of the business software and procedures within the company by a single suite of programs that are specifically designed to efficiently manage assets and resources based around a comprehensive financial system
The benefit of ERP is having a single solution provider for everything from accounts through manufacturing and warehouse management to human resources.
Every package is linked to allow extensive business intelligence.

• SCM for Efficient Management of the Supply Chain

Supply Chain Management as the "design, planning, execution, control, and monitoring of supply chain activities with the objective of creating net value, building a competitive infrastructure, leveraging worldwide logistics, synchronizing supply with demand, and measuring performance globally".

• CRM, Customer Relationship Management

A CRM software solution provides a database where every piece of communication such as telephone call; email or direct conversation can be logged to ensure that promises can be met and leads can be efficiently followed.
This is especially important where a business has many customers or those that have a team of sale staff who may need to know the details of the last conversation.

http://suite101.com/article/erp-scm-crm-business-software-solutions-a275320

• A CRM Without Boundaries

Vtiger CRM is considered the most powerful open source CRM solution
https://www.vtiger.com/open-source/

Data WareHouse Overview

-periodically updated,relatively static
-seperated from operational databases

building and maintaning datawarehouse tools

-dbms tool: oracle,teradata
-middleware tool:any ETL tool like informatica
-metadata tool:teradata metadata  repository
-warehouse administration tool:any database administration tool like oracle enterprise manager
-OLAP & Query tools:microstrategy,cognos

• data warehouse

-a storage area for processed and integrated data across different sources
-operational and external data

-allows the users to extract data for business analysis and strategic decision making
-supports management decision making process
-stand-alone repository of information which is ingtegrated from several operational databases

• DATA WAREHOUSING Basics

datawarehouse interview questions

• What is Data Warehousing?

A data warehouse is the main repository of an organization’s historical data, its corporate memory. It contains the raw material for management’s decision support system. The critical factor leading to the use of a data warehouse is that a data analyst can perform complex queries and analysis, such as data mining

A data warehouse is a electronical storage of an Organization's historical data for the purpose of analysis and reporting

• What are fundamental stages of Data Warehousing?

Offline Operational Databases – Data warehouses in this initial stage are developed by simply copying the database of an operational system to an off-line server where the processing load of reporting does not impact on the operational system’s performance.
Offline Data Warehouse – Data warehouses in this stage of evolution are updated on a regular time cycle (usually daily, weekly or monthly) from the operational systems and the data is stored in an integrated reporting-oriented data structure.
Real Time Data Warehouse – Data warehouses at this stage are updated on a transaction or event basis, every time an operational system performs a transaction (e.g. an order or a delivery or a booking etc.)
Integrated Data Warehouse – Data warehouses at this stage are used to generate activity or transactions that are passed back into the operational systems for use in the daily activity of the organization.

• What is the difference between OLTP and OLAP?

OLTP is the transaction system that collects business data. Whereas OLAP is the reporting and analysis system on that data.
OLTP systems are optimized for INSERT, UPDATE operations and therefore highly normalized. On the other hand, OLAP systems are deliberately denormalized for fast data retrieval through SELECT operations


OLTP is abbreviation of On-Line Transaction Processing. This system is an application that modifies data the instance it receives and has a large number of concurrent users
OLAP is abbreviation of Online Analytical Processing. This system is an application that collects, manages, processes and presents multidimensional data for analysis and management purposes
Data Source
OLTP: Operational data is from original data source of the data
OLAP: Consolidation data is from various source.

Process Goal
OLTP: Snapshot of business processes which does fundamental business tasks
OLAP: Multi-dimensional views of business activities of planning and decision making

Queries and Process Scripts
OLTP: Simple quick running queries run by users.
OLAP: Complex long running queries by system to update the aggregated data.

Database Design
OLTP: Normalized small database.This adopts entity relationship(ER) model and an application-oriented database design.
OLAP: De-normalized large database. This adopts star, snowflake or fact constellation mode of subject-oriented database design.

OLTP vs. OLAP
We can divide IT systems into transactional (OLTP) and analytical (OLAP).
In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it.

OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE).
number of transactions per second is an effectiveness measure

OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations
a response time is an effectiveness measure
http://datawarehouse4u.info/OLTP-vs-OLAP.html

Key Differences Between OLTP and OLAP

    The point that distinguishes OLTP and OLAP is that OLTP is an online transaction system whereas, OLAP is an online data retrieval and analysis system.
    Online transactional data becomes the source of data for OLTP. However, the different OLTPs database becomes the source of data for OLAP.
    OLTP’s main operations are insert, update and delete whereas, OLAP’s main operation is to extract multi dimensional data for analysis.
    OLTP has short but frequent transactions whereas, OLAP has long and less frequent transaction.
    Processing time for the OLAP’s transaction is more as compared to OLTP.
    OLAPs queries are more complex with respect OLTPs.
    The tables in OLTP database must be normalized (3NF) whereas, the tables in OLAP database may not be normalized.
    As OLTPs frequently executes transactions in database, in case any transaction fails in middle it may harm data’s integrity and hence it must take care of data integrity. While in OLAP the transaction is less frequent hence, it does not bother much about data integrity.


https://techdifferences.com/difference-between-oltp-and-olap.html

• What is Data Mining?

Generally, data mining (sometimes called data or knowledge discovery) is the process of analyzing data from different perspectives and summarizing it into useful information - information that can be used to increase revenue, cuts costs, or both. Data mining software is one of a number of analytical tools for analyzing data. It allows users to analyze data from many different dimensions or angles, categorize it, and summarize the relationships identified. Technically, data mining is the process of finding correlations or patterns among dozens of fields in large relational databases.
http://www.anderson.ucla.edu/faculty/jason.frand/teacher/technologies/palace/datamining.htm

1. What is data mart?

Data marts are generally designed for a single subject area. An organization may have data pertaining to different departments like Finance, HR, Marketting etc. stored in data warehouse and each department may have separate data marts. These data marts can be built on top of the data warehouse

• What is ER model?

ER model is entity-relationship model which is designed with a goal of normalizing the data.

• What is Dimensional Modeling?

Dimensional data model concept involves two types of tables and it is different from the 3rd normal form. This concepts uses Facts table which contains the measurements of the business and Dimension table which contains the context(dimension of calculation) of the measurements.

• What is Fact table?

Fact table contains measurements of business processes also fact table contains the foreign keys for the dimension tables. For example, if your business process is “paper production” then “average production of paper by one machine” or “weekly production of paper” would be considered as measurement of business process.

• What is Dimension table?

Dimensional table contains textual attributes of measurements stored in the facts tables. Dimensional table is a collection of hierarchies, categories and logic which can be used for user to traverse in hierarchy nodes

• What is the difference between etl tool and olap tools?

ETL tools are used to extract transformation and loading the data into data warehouse / data mart
OLAP tools are used to create cubes/reports for business analysis from data warehouse / data mart

• What is the difference between the terms "business intelligence" and "data warehousing"?

So data warehousing is the foundation that business intelligence is built upon.

People were performing data warehousing (DW) before it had a name.
The term "data warehousing" stemmed from the terms "decision support" and "management reporting" many years ago.
Business intelligence (BI) sought to encapsulate more processes that included data warehousing.
If you notice, many vendors use the term BI to describe their services; to show that they provide more services than just data warehousing.

http://www.information-management.com/news/7260-1.html