I googled for OLAP and found a definition, an OLAP Council (which has an informative white paper), a news/product site, and an information site. Is any of this information relevant? Can you point us to sources you are using?

Are you allowed to purchase additional software to make this happen? What kind of database are you using, or is MS Analysis Server the DB? Is this MSDN article about optimizing cube performance related to what you're doing? Is it MS Analysis Server or MS Analysis Services? MS has Analysis Services on their website, but I haven't been able to find Analysis Server.

From what I've seen with a few minutes of searching, the subject you are asking about is quite deep, and some clarifying information would help those answering you to know what you really want to do.

The data cubes are being created with Analysis Manager (MS Analysis Server). The service associated with the Analysis server is MSSQLServerOLAPService.

You could try this using Excel to chart a pivot table as an inline image (in "real time"). Three bits to assemble: get the pivot table set up in Excel, chart it, then put the chart on the web. Here's a MS KB article that demonstrates returning a chart image. (Apologies for the link to MS... google on "google://excel chart asp vbscript web" if stale.) Similar searches should find the necessary info.

This is quite a bit of overhead and probably not a practical solution for a high-traffic page, but OLAP is low-traffic by definition, right? Using the pivot table and chart in Excel should greatly simplify slicing up the cube and graphing code (show me sales >$1000 from regions 1 & 2 in Q2 for the last 3 years by week as a percentage of total sales for the week.)



--
May the Source be with you.

You said you wanted to be around when I made a mistake; well, this could be it, sweetheart.

The next major piece of the puzzle was the Extraction Translate and Load application (ETL). We wrote our own ETL tool because we needed a data driven application that non-technical people could use to map data from external applications to the DW. There are many ETL tools on the market if you need a commercial one.

The final piece of the puzzle is the reporting. We pre-built OLAP cubes that would answer the most common questions that users would ask of the DW including the drill downs. It would be a huge challenge to create dynamic OLAP cubes that could be constructed on the fly of any fields that the user desires. Their are so many requirements that you have to concern yourself with when building an OLAP cube, including what your facts and dimensions are, the size of the tables that you are extracting from, are the fields indexed, and many other concerns. We had to manage the expectations of our users, to let them know that we could build addition cubes but that allowing them to create anything they wanted was not acceptable. Runaway processes on the database server were the biggest concern. The end result was that we did not allow users to create cubes, only execute them.

The users accessed the cubes from a web page that asked for date ranges and other parameters. The application then ran the cube, and exported the results to Excel, HTML report, or email. We provided each user with training on how to manipulate the Excel pivot tables and graphs once they had the data they needed. Power users then took the Excel data and created tons of their own reports, graphs, and analysis. So it was impossible to anticipate all the requirements for Power users, so we let them run with the extracted data without further supervision.

For more mainstream users, we created a Digital Dashboard using Microsoft’s Digital Dashboard toolkit. Users could then access the cubes from the Dashboard after logging in and authenticating themselves. We used Role Based Access Control (RBAC) to control access to reports, with each role giving access to specific cubes. Our analysis also identified the most common reports that multiple users and or departments would use on a daily and weekly basis. On a scheduled basis, those cubes were run at night and the reports exported to HTML and stored into folders for canned reports. Some RBAC users only could see these canned reports and never access cubes directly. This prevented the servers from being overwhelmed by cube processing that was redundant.

We purposely did not constrain which programming tools we used, to give us the maximum flexibility. We used Perl, batch files, Excel macros, Digital Dashboard calls, HTML, SQL, stored procedures, multidimensional expressions (MDX) scripts, and any applicable Perl modules like DBD/DBI etc. Most MS programmers do not know about the hidden power of MDX. I would recommend learning every thing that you can about MDX, since it talks to the services directly and most efficiently. Maybe you could write a Perl module that controls MDX scripts :}

Richard

There are three types of people in this world, those that can count and those that cannot. Anon

OLAP is nothing but an SQL aggregate with a big "GROUP BY" that you then display in a matrix of columns and rows. I prefer having the option to query the live data. If you want to boost performance by distilling the data, do so as a nightly batch job that stores the aggregate query results into a heavily indexed "temp" table that avoids the bulk of the joins and/or aggregation. That's just a preference though.

What follows is some proof of concept stuff I did. I pulled it from the larger project, so you will need to fill in some bits and pieces like setting up $dbh via DBI, HTML head/body codes, and spitting out HTML headers.

It was written to use PostgreSQL, but the SQL is generic enough.

Change @rowhead, @colheads, @datacols, $cubetable to match your system.

NOTE: There is much room for improvement, but the basic concept is there. Much convolution is due to my need to generate the HTML table in proper order. In retrospect there is probably a much more sane way to code it. . .

use strict;
use DBI;

my $dbh; #SET THIS TO A VALID DBI CONNECTION HANDLE.

my @rowheads = qw/fiscal_year sale_in_territory/;
my @colheads = qw/class product_id/;
my @datacols = qw/units_sold/;
my $cubetable = 'transactions';

my @pivots = @rowheads;
push @pivots, @colheads;

my (%values,%names,%ordinals,%filtered,%totals,%columnheaders);

my $sth = $dbh->prepare('select 
'.join(',',@rowheads).','.join(',',@colheads).','.join(',',@datacols).
+'
from '.$cubetable.' ORDER BY '.join(',',@pivots)) || die
$dbh->errstr;
$sth->execute || die $dbh->errstr;

my @row;
while (@row = $sth->fetchrow_array) {
    #put the data values in a safe place
    my @data;
    for (my $i = 0; $i < @datacols; $i++) {
        my $d = pop @row;
        push @data,$d;
    }
    my $continue = 0;
    foreach (@data) {
        $continue = 1 if defined $_;
    }
    next unless $continue;
    my @key = @{GetKey(@row)};
    my $key = join(' ',@key);
    if (defined $values{$key}) {
        my $count = -1;
        foreach (@{$values{$key}}) {
            $_ = $_ + $data[$count++];
        }
    } else {
        $values{$key} = \@data;    
    }
    #now set up the column header hash
    splice(@key,0,(@rowheads));
    $key = join(' ',@key);
    if (defined $columnheaders{$key}) {
        my $count = -1;
        foreach(@{$columnheaders{$key}}) {
            $_ = $_ + $data[$count++];
        } 
    } else {
        $columnheaders{$key} = \@data;
    }
    
}


#build the column headers in a 2D array


#my $colcount = 1;
#foreach (@colheads) {
#        $colcount = $colcount * (@{$names{$_}});
#}
#$colcount = $colcount * (@datacols);

my $stop;
my @colkey;
my $count = 0;
foreach (@colheads) {
    $colkey[$count] = 0;
    $stop = $stop.$#{$names{$colheads[$count]}}.' ';
    $count++;
}
chop $stop;
my @cols;
my $count = 0;
while ($stop ne join(' ',@colkey)) {
    CalcCol($#colheads);
    my $key = join(' ',@colkey);
#    next if !defined $columnheaders{$key};
    $cols[$count] = $key;
    $count++;
}


#print '<pre>',main::Dumper(@cols),'</pre>';

#display
print '<table border = 1>';
my $datacount;
#print the column headers
my $count = 0;
my @colkeys;
foreach my $h (@colheads) {
    $datacount = 0;
    print '<tr>';
    print '<td colspan="'.(@rowheads-1).'"></td>';
    print "<td>$h</td>";
    foreach my $key (@cols) {
        next if !defined $columnheaders{$key};
        $datacount++;
        my @tmp = split(' ', $key);
        foreach (@datacols) {
            print '<td>'.${$names{$h}}[$tmp[$count]].'</td>';
        }
    }
    print '</tr>';
    $count++;
}

#print the row heads & data col heads
print '<tr>';
print '<td>',join('</td><td>',@rowheads),'</td>';
for (my $i = 0; $i < $datacount;$i++) {
    foreach my $h (@datacols) {
        print "<td>$h</td>";
    }
}

print '</tr>';
#print the row headers and data;
my @rowheader;
my @rowkey;
my $rowcount;
my $count = 0;
my $stop = '';
foreach (@rowheads) {
    $rowkey[$count] = 0;
    $stop = $stop.$#{$names{$rowheads[$count]}}.' ';
    $count++;
    
}
chop $stop;
my $first = 1;
while (join(' ',@rowkey) ne $stop) {
    CalcRow($#rowheads); 
    my $rowOK = 0;
    my $count = 0;
    foreach my $h (@rowheads) {
        $rowheader[$count] = ${$names{$h}}[$rowkey[$count]]; 
        $count++;
    } 
    my $rowstr = '<tr><td>'.join('</td><td>',@rowheader).'</td>';
    foreach my $k (@cols) {
        next if !defined $columnheaders{$k};        
        my $data = $values{join(' ',@rowkey).' '.$k};
        my @data;
        if (defined($data)) {
            @data = @{$data};
            $rowOK = 1;
        } else {
            foreach (@datacols) {
                push @data, undef;
            }
        }
        $rowstr = $rowstr.'<td>'.join('</td><td>',@data).'</td>'; 

    }
    if ($rowOK) {
        print $rowstr,'</tr>';
    }
}

sub CalcRow {
    my $pos = shift;
    if ($pos == 0) {
        $rowkey[0]++ if $rowkey[0] <= $#{$names{$rowheads[0]}};
        return 0;
    } else {
        if ($rowkey[$pos] >= $#{$names{$rowheads[$pos]}}) {
            #PRINT TOTAL LINE HERE
            $rowkey[$pos] = 0;
            CalcRow($pos-1);
        } else {
            $rowkey[$pos]++;
            return 0;
        }
    }
    return 1;
}

sub CalcCol {
    my $pos = shift;
    if ($pos == 0) {
        $colkey[0]++ if $colkey[0] <= $#{$names{$colheads[0]}};
        return 0;
    } else {
        if ($colkey[$pos] >= $#{$names{$colheads[$pos]}}) {
            #PRINT TOTAL LINE HERE
            $colkey[$pos] = 0;
            CalcCol($pos-1);
        } else {
            $colkey[$pos]++;
            return 0;
        }
    }
    return 1;
}



print '</table>';


#print '<p>columnheaders:<br><pre>',main::Dumper(%columnheaders),'</pr
+e><p>';
#print '<p>stop: ',main::Dumper($stop);
#print '<p>stop: ',main::Dumper(join(' ',@rowkey));
#print '<p>NAMES: ',main::Dumper(%names),'<p>';
#print 'VALUES: ',main::Dumper(%values),'<p>';
#print 'ORDINALS: ',main::Dumper(%ordinals),'<p>';



sub GetKey {
    my $count = 0;
    my @key;
    foreach my $val (@_) {

        if (defined $ordinals{$pivots[$count].$val}) {
            push @key, $ordinals{$pivots[$count].$val};
        } else {
            my $o = push @{$names{$pivots[$count]}},$val;
            $o--;
            $ordinals{$pivots[$count].$val} = $o;
            push @key, $o;
        }
        $count++;
    }
    return \@key;
}



1;
[download]

Questions, comments to rob AHT cabrion DHOT com.

I really would like to know if someone decides to hack on this, or builds a module from it.

Can any of you shine some light on the path I should take?

I think your question is too general. Are you asking for help with XML, MS Analysis Server, Win32::OLE, or Perl?

If it were me, I'd find a path to a new job. That's because I'm an OS bigot though and I have this fantasy that if no one develops for MS platforms they will go away. :-)

-sauoq
"My two cents aren't worth a dime.";

Well this is disappointing. I approved/frontpaged the node with the thought that there was a very interesting question there. I don't use MS Analysis server but I was thinking that the it would be useful to know of any general modules or external packages for doing generalized reporting, data extraction, and graphing. It doesn't look like this node is going to go that direction but at least that's what I was thinking and hoping for. Maybe next time.

__SIG__
use B;
printf "You are here %08x\n", unpack "L!", unpack "P4", pack
  "L!", B::svref_2object(sub{})->OUTSIDE;
[download]

1. here
2. here
3. and here

Unfort none of its particularly meaty.. :-(