wimgielis.com

Random data in a cube I

• Apr. 03, 2010

	Example files with this article:
		TM1 random data_1
		TM1 random data_2

Introduction

Have you ever needed to fill a cube with random data? In fact, you can do this without entering or pasting data yourself, without writing any TI process or VBA code, and with only 3 Excel formulas!

I will now show you how to do it. You will want to follow along and reproduce the steps in an empty Excel sheet. The example below is worked out for a cube with 4 dimensions.

Alternatively, download the first file on top of the page.

Guiding steps

1. Start MS Excel and load the TM1 Perspectives add-in.
2. Connect to a TM1 server holding the cube to be filled.
3. Create 4 defined names (hit Ctrl + F3), specify server, cube name and bounds for the random numbers:

   Defined name	Example value
   server	TM1server
   cube	GLdata
   lowerbound	0
   upperbound	1000

   You could have less or no defined names, but using them makes the sheet more generic and easier to maintain.
4. Add 3 formulas:

   Cell	Formula
   B1	=server & ":" & TABDIM(server & ":" & cube;COLUMN()-1)
   B3	=IF(B$2<>"";B$2;DIMNM(B$1;RANDBETWEEN(1;DIMSIZ(B$1))))
   A3	=IF(DTYPE(E$1;$E3)="N";DBS(RANDBETWEEN(lowerbound;upperbound);server & ":" & cube;B3;C3;D3;E3);DBSS(CHAR(RANDBETWEEN(65;90));server & ":" & cube;B3;C3;D3;E3))

5. Copy cells:

   Source cells	Destination cells
   B1:B3	C1:E3
   A3:E3	A4:Ex, where x is a sufficiently large number

6. In the cells B2:E2, you can add the name of an element for each dimension. The element for that dimension will not be random in that case, but hard-coded. Leave the cell empty if you want randomness.
7. Recalculate your sheet any number of times by pressing F9. Look in the cube for the result!

Important remarks:

• The formula in column A is able to generate random string data for string cells. The letters A to Z are used for string cells, numeric cells will be between the bounds you set in the defined names. Change if needed.
• As the example above is suited for a cube with 4 dimensions, you need to change the formulas accordingly if your cube has fewer or more dimensions. Just use less or more columns, and change the cell references in the DBS and DBSS functions.
• If a consolidation is returned by the random index number and the cell is not a string cell, the DBS formula cannot evaluate. Hit F9 successively to generate other random combinations of elements. You could slightly modify the formula in column A to return the first componant of a consolidation (ELCOMP(server:dimension,element,1)). But if that element is a consolidation, you are stuck again.
• The same applies to cells calculated by a rule, or elements/cells to which the logged in user does not have the necessary rights.
• Please note that the formulas will overwrite any existing data in the cube without notifications.
• A zero out procedure might be useful prior to sending the random data. Or you might want to Data Spread > Clear… cells in a view. Of course, the benefit of this is limited since you will be sending data to random combinations anyway.
• The separator for arguments in Excel formulas might be different for you (a , instead of a ;)

To be continued…

Continue reading if you want to learn how to get past the few drawbacks:

• « Previous
• 1
• 2
• Next »

• « Previous TM1 article
• Next TM1 article »