S.NoOLTPOLAP1Abbreviation:OLTP stands for Online transactional processing .Abbreviation:OLAP stands for Online analytical processing . 2Meaning: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. 3Used in:ERP, TX system, Client Server Architecture, Desktop applicationUsed in:Data warehouse application - MOLAP, ROLAP, HOLAP 4Data Provision:Current dataData Provision:Current and historical data 5Type of Database Transactions:Short database transactionsType of Database Transactions:Long database transactions 6Type of update/insert/delete:Online update/insert/deleteType of update/insert/delete:Batch update/insert/delete 7Normalization/Denomalization:Normalization is promoted (1st normal form, second normal form and third normal form).Normalization/Denomalization:Denormalization is promoted (Dimension and Fact design). 8Volume of Transactions:High volume of transactionsVolume of Transactions:Low volume of transactions 9Transaction Recovery Needed:Transaction recovery is necessaryTransaction Recovery Needed:Transaction recovery is not necessary 10Amount of Index Requirement:Less IndexAmount of Index Requirement:More Index 11Amount of Join Requirement:More JoinsAmount of Join Requirement:Less Joins 12Model:Adopts an entity relationship(ER) modelModel:Adopts star, snowflake or fact constellation model and a subject-oriented database design 13Orientation:Customer-oriented, used for data analysis and querying by clerks, clients and IT professionalsOrientation: 14Source:Daily transactions.Source:OLTP 15Motive:Faster insert, updates, deletes and improve data quality by reducing redundancy.Motive:Faster analysis and search by combining tables. 16SQL complexity:Simple and Medium.SQL complexity:Highly complex due to analysis and forecasting.
S.NoDTSSSIS1DTS stands for Data Transformation ServicesSSIS stands for Sql Server Integration Services 2DTS is a set of objects using an ETS tool to extract, transform, and load information to or from a databaseSSIS is an ETL tool provided by Microsoft to extra data from different sources. 3DTS was originally part of the Microsoft SQL Server 2000SSIS is a component of the Microsoft SQL Server 2005 4Uses Activex ScriptUses Scripting Language 5No Deployment wizard is availableDeployment wizard is available 6Limited Set of Transformation availableHuge of Transformations available 7Does not support BI FunctionalityCompletely supports end to end process of BI 8Single Task at a timeMulti Tasks run parallely 9It is Unmanaged scriptIt is managed by CLR 10DTS can develop through Enterprise managerSSIS can develop through Business Intelligence Development Studio (BIDS, nothing but new version of VS IDE) 11We can deploy only at local serverIt can be deployed using multiple server using BIDS 12Designer contains Single PaneSSIS designer contains 4 design panes:a) Control Flowb) Data Flowc) Event Handlers &d) Package Explorer. 13No Event HanderEvent Handler Available 14No Solution ExplorerSolution Explorer is available, with packages, connections and Data Source Views (DSV) 15Connection and other values are static, not controlled at runtime.It can be controlled dynamically using configuration