专栏首页有关SQL数据仓库,就不是数据库了吗?

数据仓库,就不是数据库了吗?

来源:https://www.guru99.com/database-vs-data-warehouse.html

本来摘自guru99, 粗略过了下,非常适合入门的读者阅读。对数据仓库好有更清晰 的了解。如果大家有需求翻译成中文的,可阅后留言。

What is Database?

A database is a collection of related data which represents some elements of the real world. It is designed to be built and populated with data for a specific task. It is also a building block of your data solution.

In this tutorial, you will learn

  • What is Database?
  • What is a Data Warehouse?
  • Why use a Database?
  • Why Use Data Warehouse?
  • Characteristics of Database
  • Characteristics of Data Warehouse
  • Difference between Database and Data Warehouse
  • Applications of Database
  • Applications of Data Warehousing
  • Disadvantages of Database
  • Disadvantages of Data Warehouse

What is a Data Warehouse?

A data warehouse is an information system which stores historical and commutative data from single or multiple sources. It is designed to analyze, report, integrate transaction data from different sources.

Data Warehouse eases the analysis and reporting process of an organization. It is also a single version of truth for the organization for decision making and forecasting process.

Why use a Database?

Here, are prime reasons for using Database system:

  • It offers the security of data and its access
  • A database offers a variety of techniques to store and retrieve data.
  • Database act as an efficient handler to balance the requirement of multiple applications using the same data
  • A DBMS offers integrity constraints to get a high level of protection to prevent access to prohibited data.
  • A database allows you to access concurrent data in such a way that only a single user can access the same data at a time.

Why Use Data Warehouse?

Here, are Important reasons for using Data Warehouse:

  • Data warehouse helps business users to access critical data from some sources all in one place.
  • It provides consistent information on various cross-functional activities
  • Helps you to integrate many sources of data to reduce stress on the production system.
  • Data warehouse helps you to reduce TAT (total turnaround time) for analysis and reporting.
  • Data warehouse helps users to access critical data from different sources in a single place so, it saves user's time of retrieving data information from multiple sources. You can also access data from the cloud easily.
  • Data warehouse allows you to stores a large amount of historical data to analyze different periods and trends to make future predictions.
  • Enhances the value of operational business applications and customer relationship management systems
  • Separates analytics processing from transactional databases, improving the performance of both systems
  • Stakeholders and users may be overestimating the quality of data in the source systems. Data warehouse provides more accurate reports.

Characteristics of Database

  • Offers security and removes redundancy
  • Allow multiple views of the data
  • Database system follows the ACID compliance ( Atomicity, Consistency, Isolation, and Durability).
  • Allows insulation between programs and data
  • Sharing of data and multiuser transaction processing
  • Relational Database support multi-user environment

Characteristics of Data Warehouse

  • A data warehouse is subject oriented as it offers information related to theme instead of companies' ongoing operations.
  • The data also needs to be stored in the Datawarehouse in common and unanimously acceptable manner.
  • The time horizon for the data warehouse is relatively extensive compared with other operational systems.
  • A data warehouse is non-volatile which means the previous data is not erased when new information is entered in it.

Difference between Database and Data Warehouse

Parameter

Database

Data Warehouse

Purpose

Is designed to record

Is designed to analyze

Processing Method

The database uses the Online Transactional Processing (OLTP)

Data warehouse uses Online Analytical Processing (OLAP).

Usage

The database helps to perform fundamental operations for your business

Data warehouse allows you to analyze your business.

Tables and Joins

Tables and joins of a database are complex as they are normalized.

Table and joins are simple in a data warehouse because they are denormalized.

Orientation

Is an application-oriented collection of data

It is a subject-oriented collection of data

Storage limit

Generally limited to a single application

Stores data from any number of applications

Availability

Data is available real-time

Data is refreshed from source systems as and when needed

Usage

ER modeling techniques are used for designing.

Data modeling techniques are used for designing.

Technique

Capture data

Analyze data

Data Type

Data stored in the Database is up to date.

Current and Historical Data is stored in Data Warehouse. May not be up to date.

Storage of data

Flat Relational Approach method is used for data storage.

Data Ware House uses dimensional and normalized approach for the data structure. Example: Star and snowflake schema.

Query Type

Simple transaction queries are used.

Complex queries are used for analysis purpose.

Data Summary

Detailed Data is stored in a database.

It stores highly summarized data.

Applications of Database

Sector

Usage

Banking

Use in the banking sector for customer information, account-related activities, payments, deposits, loans, credit cards, etc.

Airlines

Use for reservations and schedule information.

Universities

To store student information, course registrations, colleges, and results.

Telecommunication

It helps to store call records, monthly bills, balance maintenance, etc.

Finance

Helps you to store information related stock, sales, and purchases of stocks and bonds.

Sales & Production

Use for storing customer, product and sales details.

Manufacturing

It is used for the data management of the supply chain and for tracking production of items, inventories status.

HR Management

Detail about employee's salaries, deduction, generation of paychecks, etc.

Applications of Data Warehousing

Sector

Usage

Airline

It is used for airline system management operations like crew assignment, analyzes of route, frequent flyer program discount schemes for passenger, etc.

Banking

It is used in the banking sector to manage the resources available on the desk effectively.

Healthcare sector

Data warehouse used to strategize and predict outcomes, create patient's treatment reports, etc. Advanced machine learning, big data enable datawarehouse systems can predict ailments.

Insurance sector

Data warehouses are widely used to analyze data patterns, customer trends, and to track market movements quickly.

Retain chain

It helps you to track items, identify the buying pattern of the customer, promotions and also used for determining pricing policy.

Telecommunication

In this sector, data warehouse used for product promotions, sales decisions and to make distribution decisions.

Disadvantages of Database

  • Cost of Hardware and Software of an implementing Database system is high which can increase the budget of your organization.
  • Many DBMS systems are often complex systems, so the training for users to use the DBMS is required.
  • DBMS can't perform sophisticated calculations
  • Issues regarding compatibility with systems which is already in place
  • Data owners may lose control over their data, raising security, ownership, and privacy issues.

Disadvantages of Data Warehouse

  • Adding new data sources takes time, and it is associated with high cost.
  • Sometimes problems associated with the data warehouse may be undetected for many years.
  • Data warehouses are high maintenance systems. Extracting, loading, and cleaning data could be time-consuming.
  • The data warehouse may look simple, but actually, it is too complicated for the average users. You need to provide training to end-users, who end up not using the data mining and warehouse.
  • Despite best efforts at project management, the scope of data warehousing will always increase.

What Works Best for You?

To sum up, we can say that the database helps to perform the fundamental operation of business while the data warehouse helps you to analyze your business. You choose either one of them based on your business goals.

本文分享自微信公众号 - 有关SQL(SQLHub),作者:Lenis

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-09-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Linux 搭建 SQL Server 高可用完结篇

    默认情况下,这三个软件都在自带的 repository 库中配置好了,只要执行安装即可:

    Lenis
  • SQL经典面试题 - 连续3天登录用户,终于会了!

    【SQL经典面试题系列】前段时间六师妹去某团面试,回来后一直郁郁寡欢。好奇心下得知,原来是面试官要求小六用sql实现手写“连续3天登录用户”代码。这个问题虽然说...

    Lenis
  • Hive 的入门级 Group By 全案例

    这两天把自己的 Hadoop/Hive/Spark 集群环境搭好了,准备正式的做点试验,写点文章。

    Lenis
  • 2017年大数据的十大发展趋势

    研究人员称,会有越来越多的公司加速采用大数据技术。互联网数据中心(IDC)预测,到2020年大数据和分析技术市场,将从今年的1301亿美元增加至2030亿美元。...

    华章科技
  • C++核心准则CP.2:避免数据竞争​

    Unless you do, nothing is guaranteed to work and subtle errors will persist.

    面向对象思考
  • 银行业的大数据:银行如何从客户数据中获得更大的价值?

    36大数据专稿,原文作者:Vaishnavi Agrawal 本文由36大数据翻译组-欧显东翻译。

    华章科技
  • 原文|21世纪地理大数据

    By Jeff de La Beaujardière 25 November 2019

    气象学家
  • 大数据企业想要成为行业巨头的5个要素

    Navin Chaddha是早期阶段风险投资公司Mayfield的总经理。这家公司目前正在投资的一些公司包括Gigya、Elastica、Lyft、MapR和P...

    小莹莹
  • 传感器数据和城市:城市可视化和健康数据的聚合(CS HC)

    移动传感器技术的发展使市议会有可能了解人们在城市空间中的行为,这有助于减轻城市周围的压力。我们提出了一种定量方法来传达对城市场所的集体意识。这些数据是在高粒度级...

    用户7495559
  • 【论文推荐】最新六篇机器翻译相关论文—跨语言推理、单语数据、可扩展工具包、不确定性、合成

    【导读】专知内容组整理了最近六篇机器翻译(Machine Translation)相关文章,为大家进行介绍,欢迎查看! 1. Baselines and tes...

    WZEARW

扫码关注云+社区

领取腾讯云代金券