8.18.2012

Sql Server Difference FAQs-10

1.Difference between OLTP and OLAP
S.No
OLTP
OLAP
1
Abbreviation:

OLTP stands for Online transactional processing .
Abbreviation:

OLAP stands for Online analytical processing .
2
Meaning:

OLTP is designed to efficiently process high volumes of transactions, instantly recording business events (such as a sales invoice payment) and reflecting changes as they occur.
Meaning:

OLAP is designed for analysis and decision support, allowing exploration of often hidden relationships in large amounts of data by providing unlimited views of multiple relationships at any cross-section of defined business dimensions.
3
Used in:

ERP, TX system, Client Server Architecture, Desktop application
Used in:

Data warehouse application - MOLAP, ROLAP, HOLAP
4
Data Provision:

Current data
Data Provision:

Current and historical data
5
Type of Database Transactions:

Short database transactions
Type of Database Transactions:

Long database transactions
6
Type of update/insert/delete:

Online update/insert/delete
Type of update/insert/delete:

Batch update/insert/delete
7
Normalization/Denomalization:

Normalization is promoted (1st normal form, second normal form and third normal form).
Normalization/Denomalization:

Denormalization is promoted (Dimension and Fact design).
8
Volume of Transactions:

High volume of transactions
Volume of Transactions:

Low volume of transactions
9
Transaction Recovery Needed:

Transaction recovery is necessary
Transaction Recovery Needed:

Transaction recovery is not necessary
10
Amount of Index Requirement:

Less Index
Amount of Index Requirement:

More Index
11
Amount of Join Requirement:
More Joins
Amount of Join Requirement:
Less Joins
12
Model:

Adopts an entity relationship(ER) model
Model:

Adopts star, snowflake or fact constellation model and a subject-oriented database design
13
Orientation:

Customer-oriented, used for data analysis and querying by clerks, clients and IT professionals
Orientation:

Market-oriented, used for data analysis by knowledge workers( managers, executives, analysis)
14
Source:

Daily transactions.
Source:

OLTP
15
Motive:

Faster insert, updates, deletes and improve data quality by reducing redundancy.
Motive:

Faster analysis and search by combining tables.
16
SQL complexity:

Simple and Medium.
SQL complexity:

Highly complex due to analysis and forecasting.
2.Difference between DTS and SSIS
S.No
DTS
SSIS
1
DTS stands for Data Transformation Services
SSIS stands for Sql Server Integration Services
2
DTS is a set of objects using an ETS tool to extract, transform, and load information to or from a database
SSIS is an ETL tool provided by Microsoft to extra data from different sources.

3
DTS was originally part of the Microsoft SQL Server 2000
SSIS is a component of the Microsoft SQL Server 2005
4
Uses Activex Script
Uses Scripting Language
5
No Deployment wizard is available
Deployment wizard is available
6
Limited Set of Transformation available
Huge of Transformations available
7
Does not support BI Functionality
Completely supports end to end process of BI
8
Single Task at a time
Multi Tasks run parallely
9
It is Unmanaged script
It is managed by CLR
10
DTS can develop through Enterprise manager
SSIS can develop through Business Intelligence Development Studio (BIDS, nothing but new version of VS IDE)
11
We can deploy only at local server
It can be deployed using multiple server using BIDS
12
Designer contains Single Pane
SSIS designer contains 4 design panes:
a) Control Flow
b) Data Flow
c) Event Handlers &
d) Package Explorer.
13
No Event Hander
Event Handler Available
14
No Solution Explorer
Solution Explorer is available, with packages, connections and Data Source Views (DSV)
15
Connection and other values are static, not controlled at runtime.
It can be controlled dynamically using configuration

No comments:

Post a Comment