Data Warehousing

Data Warehousing


• What is data warehouse?
• Functions
• Data Warehouse Vs Data Mart
• Database Vs Data Warehouse
• OLAP Operations
• Data Warehouse Architecture
• Applications


Data Warehouse:
• Repository of information collected from multiple sources, stored under a
unified schema, and which usually resides at a single site.
• Subject-oriented, integrated, time-variant, and non-volatile collection of data in
support of management’s decision making process.

Functions:
• Data cleaning
• Data transformation
• Data integration
• Data loading &
• Periodic data refreshing
Multidimensional database structure

Physical structure:
relational data store / multidimensional data cube


Multidimensional Data Model:
Views data in the form of data cube.
􀀹 Fact numerical measures
􀀹 Fact table
􀀹 Cuboid, base cuboid, apex cuboid
􀀹 Star, snowflake and fact constellation schema

OLAP Operations:
• Roll-up
• Drill-down
• Slice
• Dice
• Pivot

􀂙 Data Warehouse Architecture:

Architectural Diagram

Steps for designing a warehouse :
•Choose a business process to model
(eg) orders, sales, shipments
•Choose the grain of the business process
(eg) individual transactions, individual snapshots etc.
•Choose the dimensions that will apply to each fact table record
(eg) time, item, customer, supplier
•Choose the measures that will populate each fact table record
(eg) numeric quantities like dollars-cold, units-sold


Types of OLAP Servers:
• ROLAP servers
• MOLAP servers
• HOLAP servers
• Specialized SQL servers

Metadata Repository:
Data about data
Data that define data warehouse objects..
Contents:
• Structure of data warehouse
• Operational meta data
• Algorithms used for summarization
• Mappings from operational environment to data warehouse
• Data related to system performance
• Business metadata

Applications:
• Information Processing
• Analytical Processing
• Data Mining





Comments

  1. UnknownOctober 22, 2015 at 2:06 AM

    The data warehouse concept you have furnished above was very useful to me. Thanks for sharing.
    Regards,
    Informatica training in chennai|Best Informatica Training In Chennai

    ReplyDelete

Post a Comment

Popular posts from this blog

Handling of Skew

Handling of Skew 􀂄 The distribution of tuples to disks may be skewed — that is, some disks have many tuples, while others may have fewer tuples. 􀂄 Types of skew: 􀃌 Attribute-value skew. 􀂾 Some values appear in the partitioning attributes of many tuples; all the tuples with the same value for the partitioning attribute end up in the same partition. 􀂾 Can occur with range-partitioning and hash-partitioning. 􀃌 Partition skew. 􀂾 With range-partitioning, badly chosen partition vector may assign too many tuples to some partitions and too few to others. 􀂾 Less likely with hash-partitioning if a good hash-function is chosen. Handling Skew in Range-Partitioning 􀂄 To create a balanced partitioning vector (assuming partitioning attribute forms a key of the relation): 􀃌 Sort the relation on the partitioning attribute. 􀃌 Construct the partition vector by scanning the relation in sorted order as follows. 􀂾 After every 1/nth of the relation has been read, the value...
Read more

Fragment-and-Replicate Join

Fragment-and-Replicate Join 􀂄 Partitioning not possible for some join conditions 􀃌 e.g., non-equijoin conditions, such as r.A > s.B. 􀂄 For joins were partitioning is not applicable, parallelization can be accomplished by fragment and replicate technique 􀃌 Depicted on next slide 􀂄 Special case – asymmetric fragment-and-replicate: 􀃌 One of the relations, say r, is partitioned; any partitioning technique can be used. 􀃌 The other relation, s, is replicated across all the processors. 􀃌 Processor Pi then locally computes the join of ri with all of s using any join technique. 􀂄 General case: reduces the sizes of the relations at each processor. 􀃌 r is partitioned into n partitions,r0, r1, ..., r n-1;s is partitioned into m partitions, s0, s1, ..., sm-1. 􀃌 Any partitioning technique may be used. 􀃌 There must be at least m * n processors. 􀃌 Label the processors as 􀃌 P0,0, P0,1, ..., P0,m-1, P1,0, ..., Pn-1m-1. 􀃌 Pi,j computes the join of ri with sj. In ...
Read more

Interquery Parallelism

Interquery Parallelism 􀂄 Queries/transactions execute in parallel with one another. 􀂄 Increases transaction throughput; used primarily to scale up a transaction processing system to support a larger number of transactions per second. 􀂄 Easiest form of parallelism to support, particularly in a sharedmemory parallel database, because even sequential database systems support concurrent processing. 􀂄 More complicated to implement on shared-disk or shared-nothing architectures 􀃌 Locking and logging must be coordinated by passing messages between processors. 􀃌 Data in a local buffer may have been updated at another processor. 􀃌 Cache-coherency has to be maintained — reads and writes of data in buffer must find latest version of data. Cache Coherency Protocol 􀂄 Example of a cache coherency protocol for shared disk systems: 􀃌 Before reading/writing to a page, the page must be locked in shared/exclusive mode. 􀃌 On locking a page, the page must be read from disk ...
Read more