Creating Test Hierarchies

If you've ever wanted to test out an Essbase cube of a certain size, you might have run into the problem of having to create the necessary hierarchies. How does one quickly create a 100,000 member customer dimension to test out sizing? I wrote a simple Perl script that tackles the problem in a fairly simple manner. Just use the following code and it will create a balanced hierarchy extract in parent/child format that is x levels deep with y children under each parent.

1:  #!/usr/bin/perl  
2:  my $dim = "Product";  
3:  my $gens = 4;  
4:  my $members = 5;  
5:  my @hier=(1);  
6:  foreach (1...$gens){  
7:    $count = 0;  
8:    @copy_hier = @hier;  
9:    undef @hier;  
10:    foreach $member (@copy_hier){  
11:      foreach (1...$members){  
12:        $count++;  
13:        $child = $member."_".$count;  
14:        print $dim."_".$member.",".$dim."_".$child."\n";  
15:        push @hier, "$child";  
16:      }  
17:    }  
18:  }  

You can change lines 3 and 4 to alter the number of generations and members under each parent. Change line 2 if you desire a dimension name other than Product. This is very simple "starter" code -- there are endless possibilities of customization you could do such as randomizing the number of children.

If you want to know how many members the hierarchy will have there's an easy math trick that use. The equation for a geometric series is:

where x is the number of members under each parent and n is the number of generations. In this case it's 781 members. You can put the formula into an Excel equation and play around with the inputs until you get the dimension size you're looking for.

The file output looks like so:
Product_1,Product_1_1
Product_1,Product_1_2
Product_1,Product_1_3
Product_1,Product_1_4
Product_1,Product_1_5
Product_1_1,Product_1_1_1
Product_1_1,Product_1_1_2
Product_1_1,Product_1_1_3
Product_1_1,Product_1_1_4
Product_1_1,Product_1_1_5
Product_1_2,Product_1_2_6
Product_1_2,Product_1_2_7
Product_1_2,Product_1_2_8
Product_1_2,Product_1_2_9

Once you've got your test dimensions built, check out this tip for loading test data to the cube.