OLTP Benefits
------------------
Online Transaction Processing has two key benefits: simplicity and efficiency.
Reduced paper trails and the faster, more accurate forecasts for revenues and expenses are both examples of how OLTP makes things simpler for businesses. It also provides a concrete foundation for a stable organization because of the timely updating. Another simplicity factor is that of allowing consumers the choice of how they want to pay, making it that much more enticing to make transactions.
OLTP is proven efficient because it vastly broadens the consumer base for an organization, the individual processes are faster, and it’s available 24/7.
Dis adv
----------
B2B transactions, businesses must go offline to complete certain steps of an individual process, causing buyers and suppliers to miss out on some of the efficiency benefits that the system provides.As simple as OLTP is, the simplest disruption in the system has the potential to cause a great deal of problems, causing a waste of both time and money. Another economic cost is the potential for server failures. This can cause delays or even wipe out an immeasurable amount of data.
OLAP
--------
Online Analytical Processing, or OLAP (IPA: /ˈoʊlæp/), is an approach to quickly provide answers to analytical queries that are multi-dimensional in nature.[1] OLAP is part of the broader category business intelligence, which also encompasses relational reporting and data mining.[2] The typical applications of OLAP are in business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting and similar areas. The term OLAP was created as a slight modification of the traditional database term OLTP (Online Transaction Processing).[3]
Data warehouse
-----------------------
Data warehouses are designed to facilitate reporting and analysis[1].
This classic definition of the data warehouse focuses on data storage. However, the means to retrieve and analyze data, to extract, transform and load data, and to manage the data dictionary are also considered essential components of a data warehousing system. Many references to data warehousing use this broader context. Thus, an expanded definition for data warehousing includes business intelligence tools, tools to extract, transform, and load data into the repository, and tools to manage and retrieve metadata.
benefit:
-----------
A data warehouse provides a common data model for all data of interest regardless of the data's source. This makes it easier to report and analyze information than it would be if multiple data models were used to retrieve information such as sales invoices, order receipts, general ledger charges, etc.
Architechture
---------------------
- Operational database layer
- ---------------------------------------
- The source data for the data warehouse - An organization's ERP systems fall into this layer.
- Informational access layer
- --------------------------------------
- The data accessed for reporting and analyzing and the tools for reporting and analyzing data - Business intelligence tools fall into this layer. And the Inmon-Kimball differences about design methodology, discussed later in this article, have to do with this layer.
- Data access layer
- --------------------------
- The interface between the operational and informational access layer - Tools to extract, transform, load data into the warehouse fall into this layer.
- Metadata layer
- ---------------------
- The data directory - This is often usually more detailed than an operational system data directory. There are dictionaries for the entire warehouse and sometimes dictionaries for the data that can be accessed by a particular reporting and analysis tool.
Data mining
------------------
Data mining is the process of sorting out and analyzing data in a data warehouse or data mart.
Diff bw dwh and dm
-----------------------------
data mart tends to start from an analysis of user needs and that a data warehouse tends to start from an analysis of what data already exists and how it can be collected in such a way that the data can later be used.
data mart
---------------
Data marts are often derived from subsets of data in a data warehouse, though in the bottom-up data warehouse design methodology the data warehouse is created from the union of organizational data marts.
the data
warehouse is created from the union of organizational data marts.Operational data store
----------------------------------
An operational data store (or "ODS") is a database designed to integrate data from multiple sources to make analysis and reporting easier.
Normalization
--------------------
Normalization splits up data to avoid redundancy (duplication) by moving commonly repeating groups of data into a new table. Normalization therefore tends to increase the number of tables that need to be joined in order to perform a given query, but reduces the space required to hold the data and the number of places where it needs to be updated if the data changes.
star and snowflake schema
----------------------------------------
The star and snowflake schema are most commonly found in dimensional data warehouses and data marts where speed of data retrieval is more important than the efficiency of data manipulations. As such, the tables in these schema are not normalized much, and are frequently designed at a level of normalization short of third normal form.
star schema
------------------
a fact table consists of the measurements, metrics or facts of a business process. It is often located at the centre of a star schema, surrounded by dimension tables.
Entity model
------------------
An entity-relationship model (ERM) is an abstract conceptual representation of structured data. Entity-relationship modeling is a relational schema database modeling method, used in software engineering to produce a type of conceptual data model (or semantic data model) of a system, often a relational database, and its requirements in a top-down fashion. Diagrams created using this process are called entity-relationship diagrams,
benefits:
-------------
Entity modelling can aid the understanding of an organisation's data, both computerised and non-computerised, for the strategic benefit of the organisation and as an aid to communications within and across its boundaries.
entity model as an aid to information management within an organisation.
The benefits of entity model clustering to the organisation, for end-user computing, to the information systems department, and to the entity modelling process are discussed.
sql
----
SQL (Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.[2][3]
Dql
------
DQL (Documentum Query Language) is a query language which allows you to do very complex queries involving:
1. Property searches
2. Searches for words and phrases within documents
3. Other specialized searching capabilities added for document and content management
GROUP BY
---------------
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
ORDER BY
---------------
The ORDER BY keyword is used to sort the result-set by a specified column.
First Normal Form (1NF)
---------------------------------
First normal form (1NF) sets the very basic rules for an organized database:
* Eliminate duplicative columns from the same table.
* Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
Second Normal Form (2NF)
--------------------------------------
Second normal form (2NF) further addresses the concept of removing duplicative data:
* Meet all the requirements of the first normal form.
* Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
* Create relationships between these new tables and their predecessors through the use of foreign keys.
Third Normal Form (3NF)
-------------------------------------
Third normal form (3NF) goes one large step further:
* Meet all the requirements of the second normal form.
* Remove columns that are not dependent upon the primary key.
Fourth Normal Form (4NF)
--------------------------------------
Finally, fourth normal form (4NF) has one additional requirement:
* Meet all the requirements of the third normal form.
* A relation is in 4NF if it has no multi-valued dependencies.
Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database.
If you'd like to ensure your database is normalized, explore our other articles in this series:
* Database Normalization Basics
* Putting your Database in First Normal Form
* Putting your Database in Second Normal Form
* Putting your Database in Third Normal Form
If you want to receive notifications of new database articles posted on this site, Subscribe to our newsletter
Index
--------
A database index is a data structure that improves the speed of operations on a database table. Indices can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.
Random access file:
----------------------------
Random access files are useful for many different applications. One specific example of a good use of random access files are zip files. Zip files contain many other files and are compressed to conserve space. Zip files contain a directory at the end that indicates where the various contained files begin:
An efficient way to extract a particular file from within a zip file is with a random access file:
- open the file
- find and read the directory locating the entry for the desired file
- seek to the position of the desired file
- read it
Random access is sometimes called direct access.
No comments:
Post a Comment