What is the difference between a database and a data warehouse

Question

What is the difference between a database and a data warehouse   Aren t they the same thing  or at least written in the same thing  ie  Oracle RDBMS

User · Answer

A Data Warehouse is a type of Data Structure usually housed on a Database. The Data Warehouse refers the the data model and what type of data is stored there - data that is modeled (data model) to server an analytical purpose.

A Database can be classified as any structure that houses data. Traditionally that would be an RDBMS like Oracle, SQL Server, or MySQL. However a Database can also be a NoSQL Database like Apache Cassandra, or an columnar MPP like AWS RedShift.

You see a database is simply a place to store data; a data warehouse is a specific way to store data and serves a specific purpose, which is to serve analytical queries.

OLTP vs OLAP does not tell you the difference between a DW and a Database, both OLTP and OLAP reside on databases. They just store data in a different fashion (different data model methodologies) and serve different purposes (OLTP - record transactions, optimized for updates; OLAP - analyze information, optimized for reads).

User · Answer

Example  A house is worth  100 000  and it is appreciating at  1000 per year    To keep track of the current house value  you would use a database as the value would change every year    Three years later  you would be able to see the value of the house which is  103 000    To keep track of the historical house value  you would use a data warehouse as the value of the house should be    100 000 on year 0    101 000 on year 1    102 000 on year 2    103 000 on year 3

User · Answer

A data warehouse is a TYPE of database   In addition to what folks have already said  data warehouses tend to be OLAP  with indexes  etc  tuned for reading  not writing  and the data is de-normalized   transformed into forms that are easier to read  amp  analyze   Some folks have said  databases  are the same as OLTP -- this isn t true   OLTP  again  is a TYPE of database   Other types of  databases   Text files  XML  Excel  CSV     Flat Files   -

User · Answer

Any data storage for application generally uses the database  It could be relational database or no sql databases which are currently trending    Data warehouse is also database  We can call data warehouse database as specialized data storage for the analytical reporting purposes for the company   This data used for key business decision    The organized data helps is reporting and taking business decision effectively

User · Answer

Source for the Data warehouse can be cluster of Databases  because databases are used for Online Transaction process like keeping the current records  but in Data warehouse it stores historical data which are for Online analytical process

User · Answer

The simplest way to explain it would be to say that a data warehouse consists of more than just a database   A database is an collection of data organized in some way  but a data warehouse is organized specifically to  facilitate reporting and analysis    This however is not the entire story as data warehousing also contains  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    Data Warehouse

User · Answer

Check out this for more information   From a previous link   Database   Used for Online Transactional Processing  OLTP  but can be used for other purposes such as Data Warehousing  This records the data from the user for history  The tables and joins are complex since they are normalized  for RDMS   This is done to reduce redundant data and to save storage space  Entity     Relational modeling techniques are used for RDMS database design  Optimized for write operation  Performance is low for analysis queries     Data Warehouse   Used for Online Analytical Processing  OLAP   This reads the historical data for the Users  for business decisions   The Tables and joins are simple since they are de-normalized  This is done to reduce the response time for analytical queries   Data     Modeling techniques are used for the Data Warehouse design  Optimized for read operations   High performance for analytical queries  Is usually a Database    It s important to note as well that Data Warehouses could be sourced from zero to many databases

User · Answer

From a Non-Technical View  A database is constrained to a particular applications or set of applications   A data warehouse is an enterprise level data repository  It s going to contain data from all many segments of the business  It s going to share this information to provide a global picture of the business  It is also critical to integration between the different segments of the business   From a Technical view  The word  Data Warehouse  has been given no recognized definition  Personally  I define a data warehouse as a collection of data-marts  Where each data-mart consists of one or more databases where the database is specific to a specific problem set  application  data-set or process    Simply put a database is a component of a data-warehouse  There are many places to explore this concept  but because there is no  definition   you will find challenges with any answer you give

User · Answer

DataBase  -  OLTP online transaction process    It is current data  up-to-date detailed data  flat relational isolated data  Entity relationship is used to design the database  DB size  100MB-GB simple transaction or quires   Datawarehouse   OLAP Online Analytical process   It is about Historical data Star schema snow flexed schema and galaxy     schema is used to design the data warehouse  DB size 100GB-TB Improved query performance foundation for DATA MINING DATA VISUALIZATION Enables users to gain a deeper understanding and knowledge about various  aspects of their corporate data through fast  consistent  interactive access  to a wide variety of possible views of the data

User · Answer

A Data Warehousing  DW  is process for collecting and managing data from varied sources to provide meaningful business insights  A Data warehouse is typically used to connect and analyze business data from heterogeneous sources  The data warehouse is the core of the BI system which is built for data analysis and reporting

User · Answer

Data Warehouse vs Database  A data warehouse is specially designed for data analytics  which involves reading large amounts of data to understand relationships and trends across the data  A database is used to capture and store data  such as recording details of a transaction   Data Warehouse  Suitable workloads - Analytics  reporting  big data   Data source - Data collected and normalized from many sources   Data capture - Bulk write operations typically on a predetermined batch schedule   Data normalization - Denormalized schemas  such as the Star schema or Snowflake schema   Data storage - Optimized for simplicity of access and high-speed query  performance using columnar storage   Data access - Optimized to minimize I O and maximize data throughput   Transactional Database   Suitable workloads - Transaction processing   Data source - Data captured as-is from a single source  such as a transactional system   Data capture - Optimized for continuous write operations as new data is available to maximize transaction throughput   Data normalization - Highly normalized  static schemas   Data storage - Optimized for high throughout write operations to a single row-oriented physical block   Data access - High volumes of small read operations

User · Answer

Database   Used for Online Transactional Processing  OLTP     Transaction-oriented  Application oriented  Current data  Detailed data  Scalable data  Many Users  Administrators   Operational  Execution time  short    Data Warehouse   Used for Online Analytical Processing  OLAP     Oriented analysis  Subject oriented  Historical data  Aggregated data  Static data  Not many users  manager  Execution time  long

User · Answer

See in simple words    Dataware --  Huge data using for Analytical storage  copy and Analysis   Database --  CRUD operation with Frequently used data    Dataware house is Kind of storage which u are not using on daily basis  amp  Database is something which your dealing frequently    Eg  If we are asking statement of bank then it gives us for last 3 4 6 more  months bcoz it is in database  If you want more than that it stores on Dataware house

[database] What is the difference between a database and a data warehouse?

Examples related to database

Examples related to data-warehouse