wimgielis.com

Random data in a cube II

• Apr. 03, 2010

Continued

In order to avoid the problem that we randomly select a consolidated level for data entry, we could create a subset in each of the dimensions of the cube. The subset will only contain elements of type n or s. The SUBNM function is ideally suited to get an element out of a subset. The SUBSIZ function gives us the number of elements in the subset.

An interesting TI process

To help you set up this alternative solution, download the files on the first page of the article. First thing to do, is creating subsets containing the elements to pick from. You could do this manually in the Subset Editor, or automate the task using a nifty TI process:

# Wim Gielis
# https://www.wimgielis.com
#####
# TI code to create subsets in the dimensions of a cube
# 03/04/10
#####
vCubeName='GLdata';
vSubsetName='Random';

# m loops over the dimensions in the cube
m=1;
WHILE(TABDIM(vCubeName,m)@<>'');
     vDim=TABDIM(vCubeName,m);

     SUBSETDESTROY(vDim,vSubsetName);

     SUBSETCREATE(vDim,vSubsetName);

     n=1;
     WHILE(n<=DIMSIZ(vDim));

          vElem=DIMNM(vDim,n);

          IF(DTYPE(vDim,vElem)@<>'C');
               SUBSETELEMENTINSERT(vDim,vSubsetName,vElem,1);
          ENDIF;

          # n loops over the elements in the dimension
          n=n+1;

     END;

     m=m+1;

END;

MDX and dynamic subsets are no good candidates in the process above. First off, sending random data probably is a one time exercise, and subsets should not really be dynamic. More importantly, MDX statements returning the level 0 elements in a dimension will also return consolidations without children. On these elements, data input is of course not possible.

Now that the subsets exist, it is easy to get random elements out of them in Excel. I added a new defined name called subset, which is used in the SUBNM functions. Next to that, there are no differences with the previous approach. The advantage over the first approach is that you can be sure to have lowest level elements and no consolidations.

For the record, deleting the subsets can be done using:

# Wim Gielis
# https://www.wimgielis.com
#####
# TI code to delete subsets in the dimensions of a cube
# 03/04/10
#####
vCubeName='GLdata';
vSubsetName='Random';

# m loops over the dimensions in the cube
m=1;
WHILE(TABDIM(vCubeName,m)@<>'');

     SUBSETDESTROY(TABDIM(vCubeName,m),vSubsetName);

     m=m+1;

END;

Extensions

For completeness, I mention that you can further stretch this approach a bit as follows. In the second sheet of an Excel file, create lists of elements from which you want to pick elements randomly. Say, you use column A for the first dimension, column B for the second, and so on. Paste only (valid) element names which you want to fill with random data. Then, on your main sheet, use the INDEX function to grab an element out of a list. The RANDBETWEEN function is again at your disposal: take random numbers between 1 and COUNTA(list on other sheet). Use the $ signs in Excel wisely and with only 1 formula you are the Excel/TM1 king of your company!

Look in the downloads section on page 1 for a custom file. Good luck with it!

One last note but important note: if you want to obfuscate the data in a TM1 cube without changing the element names, please have a look here for the (hidden) function DebugUtility.

• « Previous
• 1
• 2
• Next »