The servers, storage, client/server models and parallel and distributed systems a warehouse runs on.
5.1 Hardware and operating systems for data warehousing
The hardware for a data warehouse must meet several requirements. It must be scalable so that it can grow with the data, it must read and write data from disk at a high rate, it must have a large amount of memory, and it must be reliable and available whenever users need it. Warehouses typically run on symmetric multiprocessing (SMP) servers, massively parallel processing (MPP) systems, clusters or mainframes. The operating system, which is usually a variant of UNIX, Linux or Windows Server, must support very large files, many processors and parallel input and output. For storage, warehouses use RAID disk arrays for speed and fault tolerance, and storage area networks (SANs) to hold very large volumes of data.
ExampleShopMart's 30-terabyte warehouse runs on two Linux servers. Each server has 32 processor cores and 512 GB of memory, and both are attached to a storage area network that uses RAID 10.
ExplanationThe 64 cores allow large scans to run in parallel, and the 512 GB of memory on each server holds the dimension tables and summary tables so they can be read quickly. RAID 10 spreads data across several disks for speed and keeps a mirror copy, so the failure of one disk loses no data. Linux supports the large files and parallel input and output that this setup needs.
Different RAID levels balance speed, protection and cost in different ways. The table below shows how each common level works, how much usable space it gives from eight disks of 4 TB each (32 TB of raw space), and how it is used in a warehouse.
| RAID level | How it works | Usable space from 8 × 4 TB | Use in a warehouse |
|---|---|---|---|
| RAID 0 | Striping only | 8 × 4 = 32 TB | Fast temporary space with no protection |
| RAID 1 | Mirroring | 32 ÷ 2 = 16 TB | Logs and metadata |
| RAID 5 | Striping with parity | (8 − 1) × 4 = 28 TB | Read-heavy fact data at a lower cost |
| RAID 10 | Mirroring and striping | 32 ÷ 2 = 16 TB | Fast, protected fact data |
ExplanationRAID 0 gives all 32 TB but loses data if any disk fails. RAID 5 gives up one disk's worth of space (4 TB) to store parity, so it survives one disk failure while keeping 28 TB. RAID 1 and RAID 10 keep a full mirror copy, so they give only half the raw space, 16 TB, but they are the safest and, in the case of RAID 10, also the fastest. To hold ShopMart's 30 TB on RAID 10, it therefore needs at least 60 TB of raw disk, or fifteen 4 TB disks rounded up to sixteen so they pair evenly.
5.2 Client/server computing model and data warehousing
Users reach a warehouse through a client/server arrangement, and the number of tiers in that arrangement affects how many users it can serve. The table below compares the three common models.
| Model | Layout | Example |
|---|---|---|
| Two-tier | The client tool talks directly to the database | An analyst's Excel workbook connects to the warehouse through ODBC |
| Three-tier | Client → application or OLAP server → database | Dashboard → OLAP server → warehouse, as in Figure 2.1 |
| Multi-tier (web) | Browser → web server → application server → database | 200 store managers open their reports in a web browser |
ExplanationThe two-tier model is simple, but each client holds its own logic and opens its own connection to the database, so it breaks down when there are hundreds of users. With 200 store managers in a two-tier model, the database must hold 200 open connections. In a three-tier model, the middle tier can share, say, 20 pooled connections among all 200 users, which is a tenth of the load on the database. A middle tier brings the logic, caching and security together in one place. As a result, serving more users only requires adding more middle-tier servers.
5.3 Parallel processors and cluster systems
The three panels in the figure differ only in what the processors share. In the first panel they share everything, in the second they share only the disks, and in the third they share nothing.
Symmetric multiprocessing (SMP). In an SMP system, many processors share a single memory and a single operating system.
ExampleOne server with 32 processors runs the whole of a company's warehouse.
ExplanationAn SMP system is easy to manage, but all the processors compete for the same memory bus. Beyond a certain point, therefore, adding more processors brings very little extra speed.
Clusters (shared-disk). A cluster is a group of independent computers, called nodes, that share the same disks over a fast network connection.
ExampleA bank runs its warehouse on a four-node Oracle RAC cluster. When node 2 fails, nodes 1, 3 and 4 continue to answer queries.
ExplanationA cluster provides high availability, because the failure of one node does not stop the system. However, the nodes must coordinate their locks on the shared data, which adds overhead.
Massively parallel processing (MPP, shared-nothing). In an MPP system, each node has its own processor, memory and disk.
ExampleA 100-node Teradata system holds a 30 TB Sales fact table, so each node stores 30 TB ÷ 100 = 300 GB, which is one per cent of the table. If one node can scan its 300 GB in 10 minutes, all 100 nodes together scan the full 30 TB in about 10 minutes. A single machine with the same speed would need 100 × 10 = 1,000 minutes, or nearly 17 hours.
ExplanationA full scan of the table is split 100 ways, and the nodes do not compete with each other for any resource. Capacity grows simply by adding more nodes: with 200 nodes, each would hold only 150 GB, and the same scan would take about 5 minutes.
Non-uniform memory access (NUMA). In a NUMA system, memory is shared, but each processor reaches its own local memory faster than the memory attached to other processors.
ExampleA server with four processor sockets lets each socket read its own memory faster than the memory belonging to another socket.
ExplanationNUMA is a middle ground between SMP and MPP. It is programmed like an SMP system, but it scales further, provided that data is placed close to the processor that uses it.
5.4 Distributed DBMS implementations
In a distributed database management system (DDBMS), data is stored at several sites, but it appears to users as a single database.
ExampleA bank stores its customer accounts at two sites, one in Mumbai for the West region and one in Delhi for the North region. It uses horizontal fragmentation, so Mumbai holds the rows where the region is West and Delhi holds the rows where the region is North. It also uses vertical fragmentation: the know-your-customer (KYC) columns, such as address and PAN, are kept at a secure site, while the balances are kept elsewhere, and both are linked by the account number. The small Branch table is replicated, meaning it is copied to both sites, so local queries do not need to cross the network. Finally, a transfer from a Mumbai account to a Delhi account uses two-phase commit. Suppose ₹10,000 is moved from a Mumbai account holding ₹50,000 to a Delhi account holding ₹20,000. In the first phase, the coordinator asks both sites to prepare: Mumbai prepares to change ₹50,000 to ₹40,000, and Delhi prepares to change ₹20,000 to ₹30,000, and both vote "ready". In the second phase, the coordinator tells both sites to commit, so the balances become ₹40,000 and ₹30,000, and the combined total stays at 40,000 + 30,000 = ₹70,000, exactly as before. If Delhi had voted "abort" instead, both sites would have rolled back, leaving ₹50,000 and ₹20,000, and money could never vanish or be created by half a transfer.
ExplanationUsers write a single query, and the DDBMS decides which fragments to read; this hiding of fragmentation and location from the user is called transparency. In data warehousing, the same ideas support federated warehouses and regional data marts. The cost is extra network traffic and the greater difficulty of keeping all sites consistent.
Exam points
- RAID 10 combines mirroring and striping for fast, protected data; RAID 0 gives speed with no protection.
- A three-tier model puts an application or OLAP server between clients and the database, so it scales to many users.
- In SMP all processors share memory and disks; in shared-disk clusters each node has its own memory but shares disks; in MPP nothing is shared.
- Clusters give high availability: if one node fails, the others keep serving.
- Horizontal fragmentation splits rows across sites; vertical fragmentation splits columns.
- Two-phase commit makes a distributed transaction commit at all sites or at none.
Independent study notes for learning purposes. Not affiliated with or endorsed by any university, publisher or course provider. Product and company names are trademarks of their respective owners.