Please enable JavaScript.
Coggle requires JavaScript to display documents.
Transactional vs Informational Databases (different (Data Content： Current…
Transactional vs Informational Databases
Transactional Databases
used to answer operational questions
shipping
hiring
Selling a products
supplying
Focus is on supporting day to day
operations
Recording orders
• Processing claims
• Making shipments
• Generating invoices
• Receiving cash
• Reserving airline seats
Store data from every-day transactions
Optimised to write new data in as transactions happen
Highly normalised
Store data in normalized structure
Informational (Analytical) databases
used to answer strategic questions
Have a different scope & different
purpose
Show me the top products
• Show me problem regions
• Tell me why (drill down)
• View other data (drill across)
• Show the highest margins
• Alert me if calls are high
The focus is on getting information at a higher level suitable for decision making
It is not highly normalized
Differenced between informational and transactional databases/questions
DW Features 特征
Developing dimensional models
different
Data Content： Current Values；Archives, derived, summarised
Data Structure： Optimised for transactions (lots of writes)； Optimised for complex queries
Access Frequency： Very High； Medium
Access Type： Read, update, delete； Read
用法Usage： Predictable, repetitive； Ad hoc, random, heuristic
响应时间Response Time： Sub-seconds； Seconds to Minutes
Users： Many； Relatively few
Data Warehouse!
Integrates data from multiple sources
Makes data available to managers/users
A single repository of organisational data
DW Defining Features 特征
Subject Oriented Datas 面向主题的数据
Data in a DW cuts across Application requirements
Data is integrated across function
Integrated Data
Data from different systems
Need to convert to a common format
Data from various sources are validated
before storing them in a data warehouse.
Data quality is crucial to the credibility of the warehouse
Time Variant Data 时间变量数据
Data is stored as snapshots of the current values
Allows
Analysis of the past
Relation of data to the present
Forecasting for the future
In a Data Warehouse
In application systems the data is current
Non Volatile Data 非易失性数据
Store extracted data snapshots over time 随时间存储提取的数据快照
Data is periodically updated
Unlike transaction systems, the DW doesn’t get updated every time the data changes 数据库不会根据数据每次更新改变
Users have read access only
Data Granularity 数据粒度
Operational systems
Data kept at the lowest level of detail
Summary data created by adding up the numbers
It's not stored
Informational systems
start with summary data
more detailed levels of data are needed
stored at various levels for efficiency
level of detail： finer the granularity the lower the level of detail；the grain
Supports management needs
Used by end users
Data warehouses require a simple and easy to navigate the structure
Responses to queries should be “timely”
Dimensional Modelling
a fact table
actual business measures
(additive)
Also contain foreign keys for dimensions
• several dimensional tables
• hierarchies in the dimensions