Random data in a cube I
- Apr. 03, 2010
|Example files with this article:|
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.
- Start MS Excel and load the TM1 Perspectives add-in.
- Connect to a TM1 server holding the cube to be filled.
- Create 4 defined names (hit Control + F3),
specify server, cube name and bounds for the random numbers:
Defined name Example value server TM1server cube GLdata lowerbound 0 upperbound 1000
- 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))
- Copy cells:
Source cells Destination cells B1:B3 C1:E3 A3:E3 A4:Ex, where x is a sufficiently large number
- 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.
- Recalculate your sheet any number of times by pressing F9. Look in the cube for the result!
- 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: