1.Difference
between OLTP and OLAP
-
S.No OLTPOLAP1Abbreviation: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 applicationUsed in:Data warehouse application - MOLAP, ROLAP, HOLAP4 Data Provision:Current dataData Provision:Current and historical data5 Type of Database Transactions:Short database transactionsType of Database Transactions:Long database transactions6 Type of update/insert/delete:Online update/insert/deleteType of update/insert/delete:Batch update/insert/delete7 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 transactionsVolume of Transactions:Low volume of transactions9 Transaction Recovery Needed:Transaction recovery is necessaryTransaction Recovery Needed:Transaction recovery is not necessary10 Amount of Index Requirement:Less IndexAmount of Index Requirement:More Index11 Amount of Join Requirement:More JoinsAmount of Join Requirement:Less Joins12 Model:Adopts an entity relationship(ER) modelModel:Adopts star, snowflake or fact constellation model and a subject-oriented database design13 Orientation:Customer-oriented, used for data analysis and querying by clerks, clients and IT professionalsOrientation:14 Source:Daily transactions.Source:OLTP15 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.
-
S.No DTSSSIS1DTS stands for Data Transformation ServicesSSIS stands for Sql Server Integration Services2 DTS 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.3 DTS was originally part of the Microsoft SQL Server 2000SSIS is a component of the Microsoft SQL Server 20054 Uses Activex ScriptUses Scripting Language5 No Deployment wizard is availableDeployment wizard is available6 Limited Set of Transformation availableHuge of Transformations available7 Does not support BI FunctionalityCompletely supports end to end process of BI8 Single Task at a timeMulti Tasks run parallely9 It is Unmanaged scriptIt is managed by CLR10 DTS can develop through Enterprise managerSSIS can develop through Business Intelligence Development Studio (BIDS, nothing but new version of VS IDE)11 We can deploy only at local serverIt can be deployed using multiple server using BIDS12 Designer contains Single PaneSSIS designer contains 4 design panes:a) Control Flowb) Data Flowc) Event Handlers &d) Package Explorer.13 No Event HanderEvent Handler Available14 No Solution ExplorerSolution 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