Data Warehousing Concepts

I'm reading a book on Data Warehousing. This is in preparation for a project that I'm involved in. The project is to build a global reporting solution for IT using SQL Server 2005 Reporting Services. Here are some key words I want to remember about data warehousing:

This term	Means this
Attribute	Information about a specific dimension member
Data warehouse	A relational database designed to store management information
Dimension	A list of labels that can be used to cross-tabulate values from other dimensions
Fact table	The relational database table that contains values for one or more measures at the lowest level of detail for one or more dimensions
Foreign key column	A column in a database table that contains many values for each value in the primary key column of another database table
Join	The processes of linking the primary key of one table to the foreign key of another table
Measure	A summarizable numerical value used to monitor business activity
Member	A single item within a dimension
Member property	An attribute of a member that is not meaningful when grouping values for a report, but contains valuable information about a different attribute
Primary key column	A column in a database dimension table that contains values that uniquely identify each row
Snowflake design	A database arrangement in which attributes of a dimension are stored in a separate (normalized) table
Star design	A database arrangement in which multiple attributes of a dimension are redundantly stored in a single (denormalized) dimension table

Design considerations:

• Use integers for dimension members to reduce size of the fact table and to gaurantee uniqueness of each member key in a dimension table.
• Use surrogate keys so that the fact table consumes less space. The ability to create multiple instances of the same product–or the same customer–is an extremely important benefit of surrogate keys, and it is particularly important in a data warehouse where you are maintaining historical information for comparison. Surrogate keys are a critical part of most data warehouse design. The foreign key in the fact table and the primary key in the dimension table are then completely under the control of the data warehouse.
• Values in a data warehouse are not changing as dynamically as they would in a production database, so storing the values redundantly is less important than is retrieving the values as quickly as possible for a report.

Understanding OLAP and Analysis Services:

This term	Means this
Aggregation	Summarized values of a measure
Cache	Server-based storage locations both in memory (automatic) or on disk (designed) that enhance query performance
Calculated member	A mechanism for aggregating measures using formulas more complex than those stored in a cube
Cube	A collection of one or more related measure groups and their associated dimensions
Cube metadata	Instructions for creating and querying OLAP structures such as cubes and dimensions
Hierarchy	Levels of aggregation within a single dimension
Measure group	The conceptual container of detail values from a single fact table, along with all possible aggregations for one or more dimension hierarchies
Online analytical processing (OLAP)	A database system optimized to support decision-making processes
Online transaction processing (OLTP)	A database system used to manage transactions such as order processing
Unified Dimensional Model (UDM)	The measure groups and dimensions that define your organization's BI data; essentially synonymous with a cube

Benefit of an OLAP cube over a relational database:

1. Consistently fast response - prestoring calculated values.
2. Metadata-based queries
3. Spreadsheet-style formulas

Design considerations:

• Use Database model OLAP instead of Spreadsheet model OLAP. Advantages and disadvantages, the biggest benefit of OLAP stored using the database model is the ability to avoid data explosion. Because you need relatively few aggregate tables to provide fast results, you can have much larger cubes with many more dimensions and attributes than by using a spreadsheet model. Perhaps the biggest disadvantage of OLAP stored by using a database model is that there is no inherent way to physically store values that are calculated using nonassociative operators.

Progress GUI in perl using Win32::GUI

Did you ever need a progress GUI injected in your program? I did, and I still do. A progress GUI is something that is nice to have to show how a long running program is progressing. You don't want a user to think that your program is stuck. Last thing you want to happen is the user doing Ctrl+Alt+Delete then End Task. And so my solution is to display something like this:

Here's how my module is used:

use SamTools::ProgressGUI;

my $GUI = SamTools::ProgressGUI->new({
           Title       => 'Organization Name - Install Status',
           ProgressBar => 1,
       });

$GUI->show("Installing Software...");

foreach (1..10) {
    $GUI->step();
    sleep 1;
}

$GUI->hide();

Here's the module. I will probably contribute this to CPAN, just need to find time to prepare it, but here you go, enjoy...

#!/usr/bin/perl -w
#
#   This is module for displaying progress GUI
#   Written By: Sam Dela Cruz
#   Date      : 05/18/2006
#   Version   : 1.00
#

package SamTools::ProgressGUI;

use strict;
use Win32::GUI;
use Win32::GUI::BitmapInline;

#   Constructor
sub new {
    my $proto  = shift;
    my $class  = ref($proto) || $proto;

    my $self = {};
    bless($self, $class);

    #initialize object
    return $self->_init(@_);
}

#   Initialize object
sub _init {
    my $self = shift;

    # default values
    $self->{'title'}        = 'Install Progress';
    $self->{'progressbar'}  = 0;

    # Have we been passed anything
    if (@_ != 0){
        # We are expecting our configuration to come as an anonymous hash
        if (ref $_[0] eq 'HASH'){
            my $hash=$_[0];
            foreach my $key (keys %$hash){
                $self->{lc($key)}=$hash->{$key};
            }
        }
        else {
            ( $self->{'title'}, $self->{'progressbar'} ) = @_;
        }
    }
    #create the GUI
    return $self->_createGUI;
}

#   Create the GUI
sub _createGUI {
    my $self = shift;

    # Program Icon
    $self->{'icon'} = newIcon Win32::GUI::BitmapInline( q(
    AAABAAEAICAAAAAAAACoCAAAFgAAACgAAAAgAAAAQAAAAAEACAAAAAAAgAQAAAAAAAAAAAAAAAEA
    AAAAAAAAAAAAAACAAACAAAAAgIAAgAAAAIAAgACAgAAAwMDAAMDcwADwyqYA8PDwAJn//wCZ1JkA
    /9SZAP/M/wCZmf8AMCIiABEAAAAiAAAARAAAAFUAAAB3AAAAiAAAAKoAAADdAAAA7gAAAAARAAAA
    IgAAAEQAAABVAAAAdwAAAIgAAACqAAAA3QAAAO4AAAAAEQAAACIAAABEAAAAVQAAAHcAAACQAAAA
    qgAAAN0AAADuADMAAABmAAAAmQAAAMwAAAAAMwAAMzMAAGYzAAChMwAAzDMAAP8zAAAAZgAAM2YA
    AGZmAACZZgAAzGYAAP9mAAAAmQAAM5kAAGaZAACZmQAAzJkAAP+ZAAAAzAAAM8wAAGbMAACZzAAA
    zMwAAP/MAAAz/wAAZv8AAJn/AADM/wAAAAAzADMAMwBmADMAmQAzAMwAMwD/ADMAADMzADszMwBm
    MzMAmTMzAMwzMwD/MzMAAGYzADNuMwBmZjMAmWYzAMxmMwD/ZjMAAJkzADOZMwBmmTMAmZkzAMyZ
    MwD/mTMAAMwzADPMMwBmzDMAmcwzAMzMMwD/zDMAAP8zADP/MwBm/zMAmf8zAMz/MwD//zMAAABm
    ADMAZgBmAGYAmQBmAMwAZgD/AGYAADNmADMzZgBmM2YAmTNmAMwzZgD/M2YAAGZmADNmZgBmZmYA
    mWZmAMxmZgD/ZmYAAJlmADOZZgBmmWYAmZlmAMyZZgD/mWYAAMxmADPMZgBmzGYAmcxmAMzMZgD/
    zGYAAP9mADP/ZgBm/2YAmf9mAMz/ZgD//2YAAACZADMAmQBmAJkAmQCZAMwAmQD/AJkAADOZADMz
    mQBmM5kAmTOZAMwzmQD/M5kAAGahADNmmQBmZpkAmWaZAMxmmQD/ZpkAAJmZADOZmQBmmZkAmZmZ
    AMyZmQD/mZkAAMyZADPMmQBmzJkAmcyZAMzMmQD/zJkAAP+ZADP/mQBm/5kAmf+ZAMz/mQD//5kA
    AADMADMAzABmAMwAmQDMAMwAzAD/CNQAADPMADMzzABmM8wAmTPMAMwzzAD/M8wAAGbMADNmzABm
    ZswAmWbMAMxmzAD/ZswAAJnMADOZzABmmcwAmZnMAMyZzAD/mcwAAMzMADPMzABmzMwAmczMAMzM
    zAD/zMwAAP/MADP/zABm/8wAmf/MAMz/zAD//8wAMwD/AGYA/wCZAP8AzAD/AAAz/wAzM/8AZjP/
    AJkz/wDMM/8A/zP/AABm/wAzZv8AZmb/AJlm/wDMZv8A/2b/AACZ/wDd3d0AzJn/AGbM/wAAAIgA
    /wDMAJkzAAAzZjMAAGaZADMzMwDw+/8ApKCgAICAgAAAAP8AAP8AAAD//wD/AAAA/wD/AP//AAD/
    //8A//////////8KgVdXXVdXXXtXXXtXXXsJ///////////////2//b/9lcZ/Bn8GRkZGRkZGRkZ
    GVHV9v/29v/2//v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7
    +/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7+/v7++0LCu2AXFZW
    elZWelZWVlZWVlZWVVZWVlZWVlYHCwoL///VGRn8/Pz8/Pz8/Pz8/Pz8/Pz8/Pz8/Pz8/Pyr///2
    1Rn8/Pz8/Pz8Gfz8Gfz8Gfz8/Pz8/Pz8/Pz8/Pyr/85zUPz8/PwFUBkZ/HvOD8/VD87IGfz8UAUF
    BQVQGfyrK/m5/Pz8T/mWGRmBD/n5D//5+fnP/AX5+fn5+fm5UPzc+fn8/Py6+fkZgf/h+fkK9vn5
    +f/Pc/n5K9zc+fkqGZX5+VD8Gfn5+Z3//+H5+f//+fn5//8OuXMZGRm6+flzBfn5c/xz+fn54Qr/
    +fnh///5+fn///8OGRkZ/HT5+bpQ+fkFGbn5+fn5Cg/5+ej///n5+f////97Gfz8lvn5BRj5+bkY
    +fm5+fkPD/n5D///+fn5/////3sZ/AUr+flQ/Nz5+QX5+VDc+dzo+fkKCv/5+fn///8Ke1AqK/n5
    lvz8lfn5+fkq/Mb5+Ssr+f////n5+f///wrd+fn5KwX8/PwF+fn5+QX8zvkrKyvi////+fkr//8K
    4fn5+blRGfz8/FD5+fn5UPzVKysrK+j///8rKyv//w/5+flP/Pz8/Pz8Gfn5+Sv8/IHoKyv5D///
    /ysrK///6Pn5Bfz8/Pz8/Pz83Pn5lvz8/J0rK/kK////Kysr///i+fl0/Pz8GRn8/PyW+flz/Pz8
    /Csr3P////8rKyv/q3P5+blzUJcqc/wZGQUr3Pz8/Pz8livh/wr///n5K8/8/Lr5+fn5+fm5/NUZ
    dCuW/Pz8/PxzK8HVCc/V3PkrGfz8GZcrK/kr3HOr/9UZGfz8/Pz8/Pz8/Pz8/Pz8/Pz8/Pz8/PwZ
    Ufz8q////9X8/Pz8/Pz8/Pz8/Pz8/Pz8/Pz8/Pz8/Pz8GQn//////wpX/Pz8GRkZGRkZGRkZGRkZ
    GRkZGRkZGfyr////a2trkGtIRENIQ0NJQ0NJQ0NJQ0NJQ0NJQ0NJa2uQa2v6+vr6IiIiIiIiIiIi
    IiIiIiIiIiIiIiIiIiIiIiIiIvr6IiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIiIia2tra2tr
    a2tEQ0NDQ0NDQ0NDQ0NDQ0NDa2tra2tra2v//////////wpXV1dXV1dXV1dXV1dXV9X/////////
    /wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    AAAAAAAAAAAAAAAAAAAA
    ) );

    # Start Pop-Up Window GUI
    $self->{'screen_width'}  = Win32::GUI::GetSystemMetrics(0);
    $self->{'screen_height'} = Win32::GUI::GetSystemMetrics(1);
    $self->{'font1'}         = new GUI::Font(-name=>"Tahoma",
                                             -height=>8,
                                             );
    $self->{'font2'}         = new GUI::Font(-name=>"Tahoma",
                                             -height=>14,
                                             -bold=>1,
                                             );
    $self->{'font3'}         = new GUI::Font(-name=>"Tahoma",
                                             -height=>9,
                                             -bold=>1,
                                             );
    $self->{'font4'}         = new GUI::Font(-name=>"Tahoma",
                                             -height=>11,
                                             -bold=>1,
                                             );

    $self->{'Popup'} = new GUI::DialogBox(
                       -name   => "Popup",
                       -text   => $self->{'title'},
                       -size   => [400, 150],
                       -pos    => [(($self->{'screen_width'}/2)-200),
                                   (($self->{'screen_height'}/2)-125)],
                       -font   => $self->{'font1'},
                       -helpbutton => 0,
                       );

    my $wc = $self->{'Popup'}->Width();

    $self->{'Popup'}->SetIcon($self->{'icon'});

    $self->{'Popup'}->AddLabel( -name    => "PopupLabel",
                                -text    => "",
                                -top     => 20,
                                -left    => 20,
                                -height  => 80,
                                -width   => $wc-40,
                                -font    => $self->{'font1'},
                                );

    #display progress bar if specified in object creation
    if ($self->{'progressbar'}) {
        $self->{'Popup'}->AddProgressBar( -name   => "ProgressBar",
                                          -width  => $wc-40,
                                          -height => 10,
                                          -top    => 80,
                                          -left   => 20,
                                          -smooth => 1,
                                          );

        $self->{'Popup'}->ProgressBar->SetPos (0);
        $self->{'Popup'}->ProgressBar->SetRange(0,50);
        $self->{'Popup'}->ProgressBar->SetStep (1);
    }

    $self->{'Popup'}->SetForegroundWindow();

    return $self;
}

#   Methods
sub show {
    my $self = shift;
    my $message = shift;
    $self->{'Popup'}->PopupLabel->Text($message);
    $self->{'Popup'}->Show();
    Win32::GUI::DoEvents();
}

sub step {
    my $self = shift;
    $self->{'Popup'}->ProgressBar->StepIt() if ($self->{'progressbar'});
    Win32::GUI::DoEvents();
}

sub hide {
    my $self = shift;
    if ($self->{'progressbar'}){
        $self->{'Popup'}->ProgressBar->SetPos(50);
        sleep 1; # give chance to show completion of progress bar
    }
    $self->{'Popup'}->Hide();
    Win32::GUI::DoEvents();
}

1;

__END__


=head1 NAME

 SamTools::ProgressGUI - Sam's Simple Progress GUI

=head1 SYNOPSIS

 use SamTools::ProgressGUI;

 # Pretty format, all the parameters
 my $GUI = SamTools::ProgressGUI->new({
            Title       => 'Organization Name - Install Status',   # define the window title
            ProgressBar => 1,                                      # display progress bar
        });

 # Typical usage
 my $GUI = SamTools::ProgressGUI->new({
            Title       => 'Organization Name - Install Status',
            ProgressBar => 1,
        });

 # Display status window with the message
 $GUI->show("Installing Software...");

 # Move the progress bar
 $GUI->step();

 # Hide the status GUI
 $GUI->hide();


=head1 DESCRIPTION

I is a class providing methods to display a progress GUI.  There are a number
of parameters that can be passed to allow configuration of the GUI.

=head1 REQUIRED MODULES

Win32::GUI;
Win32::GUI::BitmapInline;

=head1 METHODS

There are no class methods, the object methods are described below.
Private class method start with the underscore character '_' and
should be treated as I.

=head2 new

Called to create a I object.  The following optional named parameters can be
passed to the constructor via an anonymous hash:

=over 4

=item Title

Defines the window title.  If not defined in the
constructor then it will use the default "Philips NA IT - Install Progress".

=item ProgressBar

Used to determine if a progress bar is needed.
Default is none.

=back

=head2 _init & Private methods

I method to initialise the object on construction.  Called by C.
All I methods start with B<_> and should be treated as PRIVATE.  No other
private methods are documented (since they are private).

=head2 show

The C method is used to show the Progress GUI with message specified.

eg.  $GUI->show("Installing Software...");

=head2 step

C is used to step the progress bar if progress bar is turned on.

=head2 hide

C is used to hide the GUI.

=head1 KNOWN ISSUES

none

=head1 AUTHOR

Sam Dela Cruz, sammydc at gmail dot com

=head1 LICENSE

Copyright (c) 2006 - Sam Dela Cruz. All rights reserved. This
program is free software; you can redistribute it and/or modify it under
the same terms as Perl itself.

=cut

Perl Best Practices

I always wanted to tidy up my perl code. But most of the time, because of the nature of work and tasks, I don't need to worry about this because my programs are mostly throw-away scripts, write and use once. But through the years that I'm programming in perl, I have accumulated a bunch of snippets here and there that are really worthy of reuse. Problem is when I look at them again, I need to re-read and re-understand my code again. Something I left out before is naming and layout convention. So with the purpose of rewriting and improving readability, consistency and maintainability of my codes, I set to follow some standards. I found this wonderful book called Perl Best Practices. It outlines coding layouts, naming conventions and well, best practices, perl style. Below are my notes for Naming Conventions taken from thisbook by Damian Conway.

#Identifiers

# Packages and Classes
# Noun::Adjective::Adjective
package Disk;
package Disk::DVD;
use base qw( Disk );
package Disk::DVD::Rewritable;
use base qw( Disk::DVD );

# Variables
# noun, preceded by zero or more adjectives
my $next_client;
my $prev_appointment;
my $estimated_net_worth;
my $final_total;
my $cumulative_total;

# Hashes and Arrays for look-up tables
# noun, preceded by zero or more adjectives, then preposition
my %title_of;
my %ISBN_for;
my @sales_from;
# and later...
while (my $month = prompt -menu => $MONTH_NAMES) {
    for my $book ( @catalog ) {
        print "$ISBN_for{$book} $title_of{$book}: $sales_from[$month]\n";
    }
}

# Subroutines and Methods

# imperative_verb noun
sub get_record;                      

# imperative_verb noun preposition
sub get_record_for;                  

# imperative_verb noun
sub eat_cookie;                      

# imperative_verb adjective noun
sub eat_previous_cookie;              

# imperative_verb noun
sub build_profile;

# imperative_verb adjective noun
sub build_execution_profile;

# imperative_verb adjective noun participle
sub build_execution_profile_using;

# Notice how clear this is:
@config_options = get_record_for($next_client);
for my $option (@config_options) {
    build_execution_profile_using($next_client, $option);
}


# Booleans
# begin with is_ or has_, but not always

sub is_valid;
sub metadata_available_for;
sub has_end_tag;

my $loading_finished;
my $has_found_bad_record;

# and later...

if (is_valid($next_record) && !$loading_finished) {
    METADATA:
    while (metadata_available_for($next_record)) {
        push @metadata, get_metadata_for($next_record);
        last METADATA if has_end_tag($next_record);
    }
}
else {
    $has_found_bad_record = 1;
}

# Reference Variables
# suffix _ref to any variable that is supposed to store a reference
sub pad_str {
    my ($text, $opts_ref) = @_;

    my $gap   = $opts_ref{cols} - length $text;
    my $left  = $opts_ref{centred} ? int($gap/2) : 0;
    my $right = $gap - $left;

    return $SPACE x $left . $text . $SPACE x $right;
}

# Arrays and Hashes
# Name arrays in the plural and hashes in the singular

my %option;
my %title_of;
my %count_for;
my %is_available;

# and later...
if ($option{'count_all'} && $title_of{$next_book} =~ m/$target/xms) {
    $count_for{$next_book}++;
    $is_available{$next_book} = 1;
}

my @events;
my @handlers;
my @unknowns;

# and later...

for my $event (@events) {
    push @unknowns, grep { ! $_->handle($event) } @handlers;
}

print map { $_->err_msg } @unknowns;

# but arrays as random-access look-up table, should be singular

my @factorial = (1);
for my $n (1..$MAX_FACT) {
    $factorial[$n] = $n * $factorial[$n-1];
}

sub factorial {
    my ($n) = @_;

    croak "Can't compute factorial($n)"
        if $n < 0 || $n > $MAX_FACT;

    return $factorial[$n];
}

# Underscores
# Use underscores to separate words in multiword identifiers

FORM:
for my $tax_form (@tax_form_sequence) {
    my $notional_tax_paid
        = $tax_form->{reported_income} * $tax_form->{effective_tax_rate};

    next FORM if $notional_tax_paid  < $MIN_ASSESSABLE;

    $total_paid
        += $notional_tax_paid - $tax_form->{allowed_deductions};
}

# Capitalization
# Distinguish different program components by case
# Use lowercase only for the names of subroutines,
# methods, variables, and labeled arguments
# $controller, new( ), src=>$fh

# Use mixed-case for package and class names
# IO::Controller

# Use uppercase for constants
# $SRC, $NODE

my $controller
        = IO::Controller->new(src=>$fh,  mode=>$SRC|$NODE);

# Abbreviations
# retain start of each word
# This example is easily comprehended:

use List::Util qw( max );

DESC:
for my $desc (@orig_strs) {
    my $len = length $desc;
    next DESC if $len > $UPPER_LIM;
    $max_len = max($max_len, $len);
}

# Utility Subroutines
# Prefix "for internal use only" subroutines with an underscore
sub _find_fib {
    my ($n) = @_;

# Walk up cache from last known value, applying Fn = Fn-1 + Fn-2...

    for my $i (@fib_for..$n) {
        $fib_for[$i] = $fib_for[$i-1] + $fib_for[$i-2];
    }

    return;
}

Kid's Surprise

Kid's Surprise

This is a great idea from the 3 moms. They arranged this get-together to surprise our kids with presents. It was amazing to see how they really appreciated simple gifts. You can see from their eyes the moment they opened the gifts the sparkle. This is nice, we got to do this at least once a year.

Online Exercise Journal

I spent a few minutes on the web trying to find an online exercise journal so I can track my progress. I found FitDay. Not only that it tracks daily exercise activities but also tracks diet. Then based on the journal entry, answers fitness questions, including:

• How many calories am I eating?
• What's my fat/carb/protein calorie breakdown?
• How many calories does each food contribute?
• Am I getting my recommended requirements of vitamins and minerals?
• How many calories do I burn?
• Where am I burning my calories (exercise, BMR, lifestyle)?
• Am I achieving a caloric balance?
• How many miles did I run (swim, walk, bike) over the past few weeks?
• How is my weight changing?
• Is my weight within a healthy range for my height?
• Am I achieving my personal weight goal?
• Am I achieving my personal nutrition goals?

The chart above shows how healthy(?) I am at the moment. There is a way to enter fitness goals. My goal is to be 150 lbs. by the end of the year. Let's track my progress!

Let's Take A Walk!

After years of no exercise whatsoever, this week I started walking just 30 minutes a day. Oh what a feeling! (from a song, if I may borrow). A difference it makes indeed.

It's Friday

Another week is over. What have I accomplished this week? A lot I suppose, but not too much. This week in a hurry I did the following:

1. Composed and delivered a talk.
2. Took my 2 kids to the doctor due to common cough and cold.
3. Rescheduled a public talk.
4. Learned about dotnetCharting for work.

I worried to much about my 2 kids being sick this week, everytime this happens I would remember how sick my youngest had been a few years back. It was my first time to have experienced putting a baby in a tub full of water. Her temperature had reached 103 degrees at about 11:30 that night. While I was talking to her pediatrician on the phone, I turned on the tub faucet and filled it with water, then 2 minutes later, soaked my baby while she was crying and screaming. Quite an experience. I'm sure most parents have their share of these experiences. This week it was my son who brought this viral sickness in my house. He must have gotten it from school. Their pedia suggested he bring hand sanitizers to school and wash hands often specially after touching things like doorknobs and keyboards in school. My wife also was slightly sick, problem sometimes is that, she has trouble of knowing if she had cold or an allergy attack. Allergy attack indeed, just a night after taking her medications, she is back as a hyperactive mom. I too got the cold bug, but as usual I'm not allowed to get sick, so while taking in 2 pills in 2 nights, still did my daily routine. Well, so much for that, hopefully after taking the prescribed medications, my kids would be their usual selves, hyper and makulit (stubborn).