VDM Public

Public information; No registration required

Typical DW Assessment Schedule

The assessment is performed on a time-boxed basis, and entails 20 – 30 days of consulting effort. The assessment schedule is dependent on the availability of the key people involved. InfoKarta strives to structure the assessments so that they can be completed within a four to six week timeframe. A five-week engagement typically consists of the following schedule:

VDM Access: 
VDM Public

Balanced Configuration Unit (BCU)

BCU Overview

The BCU provides a scalable performance ratio of disk I/O to memory to CPU to network. A balanced configuration minimizes bottlenecks that limit the overall performance of a data warehouse. Of course, it is important to remember that bottlenecks exist in all computer systems. For example, if a server can drive a peak I/O capability of 3 GB per second yet it is attached to a storage subsystem that is only capable of half that throughput, the server might be up to 50% under-utilized when the workload on the system require table scans.

VDM Access: 
VDM Public

Contiguous Sequences

Introduction

Let’s say we have a table with sales by product, location and week. We want to enumerate contiguous weeks of sales for each item/location. The table below shows sales for SKU 123 at store A for a range of weeks and includes a sequential enumeration of contiguous weeks.

VDM Access: 
VDM Public

Logically divide a table into approximately equal parts using value ranges

We have a large table LT(C1,C2,C3...) with 2.5 billion rows, and we want to process it, update it or otherwise manipulate it in ranges of one of the columns that divide the table in roughly equal chunks. We don't have enough log space, can't risk using "not logged initially". The problem is that the rows associated with each value of C1 is uneven. We seek the boundary values of C1 that will roughly divide the table into... say 25000000 row increments which would roughly be about 100 such intervals. Here is the SQL we chose to use to generate the FROM-TO list of C1 values:


 
VDM Access: 
VDM Public

Handy SQL

This is our SQL corner with handy solutions to interesting problems...

VDM Access: 
VDM Public

Metric Integration through Union-Pivot Views

When code generators, such as MicroStragegy, build reports that integrate metrics from different fact tables they typically produce small contained queries or table expressions against each fact, and then they use a pivoting step that align the metrics into rows by their common key. Depending on the code generation settings a report may follow one of two strategies:

    VDM Access: 
    VDM Public

    Table Space Standards and Naming

    1. Devote separate tablespace for each large base history fact and optionally by time horizon (e.g. year) for range partitioned tables. This will facilitate backup/restore and archival migration to different levels of storage.
    2. Use separate tablespaces for current base facts.
    3. Separate MQTs by fact family. Share tablespaces among small MQTs of the same family with the same MDC and partitioning requirements.
    4. Share dimension and reference spaces, separating in groups, e.g. Product, Location, Time.
    5. Use a separate common space for all "distributed by replication" reference and dimension tables.
    VDM Access: 
    VDM Public

    Date and Time

    http://www.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yi...

    VDM Access: 
    VDM Public

    Implementation Notes

    This section contains useful references and links for various topics. Check it out.

    VDM Access: 
    VDM Public

    Access and Query Patterns

    VDM Access: 
    VDM Public

    Pages