Wednesday, April 6, 2016

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:

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

No comments: