Creating Textfile for all Cubes

[appleglaze28]

Is there anyway to create a TI that would output specify the number of dimensions in a cube? Im just thinking if its possible to create a TI process that loops through all the cubes and output a text file for each cube.

Would it even be possible to create a single TI to do this for all cube?

[Alan Kirk]

Is there anyway to create a TI that would output specify the number of dimensions in a cube? Im just thinking if its possible to create a TI process that loops through all the cubes and output a text file for each cube.

You've been around a while now; I'm surprised that you can't figure this one out. It's dead easy.
1/ Your data source is the All subset of the }Cubes dimension. (Or create one which excludes the System (}Type) cubes; up to you.)
2/ In the data tab create a While loop which runs from 1 to 256 (the maximum number of dimensions in a cube). In that loop, do a TabDim function for the current cube using your While loop counter. As soon as the TabDim function an empty string, you know that the number of dimensions is one less than your current loop counter. Write the cube name and the loop counter-1 value out to your text file, exit the while loop and move on to the next cube from the data source.

[appleglaze28]

Hi Alan....

Sorry...its just this is something I have never tried out of doing and I was wondering if there is another function that would be easier than looping through a hundred 100 dimensions. Thanks for you utmost patience with me.

[Alan Kirk]

Hi Alan....

Sorry...its just this is something I have never tried out of doing and I was wondering if there is another function that would be easier than looping through a hundred 100 dimensions. Thanks for you utmost patience with me.

No. A function to return the number of dimensions in a cube is something that should be in TM1 but isn't. But considering that it's something like 10 lines of code and will probably finish running before you can even lift your finger off the mouse after clicking the Execute menu (if that's hyperbole it's not by much), I don't think it gets much easier than that. If you think that the code is too complex, give me about 5 minutes to write it to demonstrate otherwise.

[Alan Kirk]

Hi Alan....

Sorry...its just this is something I have never tried out of doing and I was wondering if there is another function that would be easier than looping through a hundred 100 dimensions. Thanks for you utmost patience with me.

No. A function to return the number of dimensions in a cube is something that should be in TM1 but isn't. But considering that it's something like 10 lines of code and will probably finish running before you can even lift your finger off the mouse after clicking the Execute menu (if that's hyperbole it's not by much), I don't think it gets much easier than that. If you think that the code is too complex, give me about 5 minutes to write it.

OK, I'll admit it took a fraction longer to run than I thought it would; about 5 seconds. The only thing I omitted from the earlier posts was that the variable (V1) has to be changed from Ignore to Other. After that this goes in the Data tab. Customise the output path as desired, skip the system cubes if you want to.

Code: Select all

l_Counter = 1;

While ( l_Counter <= 256);
    s = TabDim ( V1, l_Counter);
    If ( s @= '');
        AsciiOutput ('Y:\Temp\DimsInCubes.txt', V1, Trim ( Str ( l_Counter-1, 12, 0 ) ) );
        l_Counter = 257;
    EndIf; 
    l_Counter = l_Counter +1;
End;

[appleglaze28]

There's something wrong with how the TI function:

Code: Select all

Code 1
Prolog:
# DEFINE CUBE SOURCE*****
DatasourceNameForServer = vCubeName;
DatasourceNameForClient = vCubeName;


#DESTROY IF ALREADY AVAILABLE CUBE VIEW SOURCE
IF(ViewExists(vCubeName, vCubeName|' - '|pViewName)=1);
ViewDestroy(vCubeName, vCubeName|' - '|pViewName);
ENDIF;

#DETERMINE THE COUNT
 i = 1;
Count = 0;

WHILE (i <= 10);

IF(TABDIM(vCubeName, i)@='');
ELSE;
Count = Count + 1;
ENDIF;

i = i + 1;

END;

ViewCreate(vCubeName, vCubeName|' - '|pViewName);


#DEFINE CUBE VIEW SOURCE
DataSourceCubeView = vCubeName|' - '|pViewName;


Data:
EXECUTEPROCESS('Code 2','vCubeName',vCubeName);

Code: Select all

Code 2
Prolog:
# DEFINE CUBE SOURCE*****
DatasourceNameForServer = vCubeName;
DatasourceNameForClient = vCubeName;


#DESTROY IF ALREADY AVAILABLE CUBE VIEW SOURCE
IF(ViewExists(vCubeName, vCubeName|' - '|pViewName)=1);
ViewDestroy(vCubeName, vCubeName|' - '|pViewName);
ENDIF;

#DETERMINE THE COUNT
 i = 1;
Count = 0;

WHILE (i <= 10);

IF(TABDIM(vCubeName, i)@='');
ELSE;
Count = Count + 1;
ENDIF;

i = i + 1;

END;

ViewCreate(vCubeName, vCubeName|' - '|pViewName);


#DEFINE CUBE VIEW SOURCE
DataSourceCubeView = vCubeName|' - '|pViewName;


Data:
IF(Count=2);
TextOutput(pLocation|vCubeName|'.csv', vCubeName,TABDIM(vCubeName,1),TABDIM(vCubeName,2),Value);
ELSEIF (Count=3);
TextOutput(pLocation|vCubeName|'.csv', vCubeName,TABDIM(vCubeName,1),TABDIM(vCubeName,2),TABDIM(vCubeName,3),Value);
ELSEIF (Count=4);
TextOutput(pLocation|vCubeName|'.csv', vCubeName,TABDIM(vCubeName,1),TABDIM(vCubeName,2),TABDIM(vCubeName,3),TABDIM(vCubeName,4),Value);
ELSEIF (Count=5);
TextOutput(pLocation|vCubeName|'.csv', vCubeName,TABDIM(vCubeName,1),TABDIM(vCubeName,2),TABDIM(vCubeName,3),TABDIM(vCubeName,4),TABDIM(vCubeName,5),V
alue);
ELSEIF (Count=6);
TextOutput(pLocation|vCubeName|'.csv', vCubeName,TABDIM(vCubeName,1),TABDIM(vCubeName,2),TABDIM(vCubeName,3),TABDIM(vCubeName,4),TABDIM(vCubeName,5),T
ABDIM(vCubeName,6),Value);
ELSEIF (Count=7);
TextOutput(pLocation|vCubeName|'.csv', vCubeName,TABDIM(vCubeName,1),TABDIM(vCubeName,2),TABDIM(vCubeName,3),TABDIM(vCubeName,4),TABDIM(vCubeName,5),T
ABDIM(vCubeName,6),TABDIM(vCubeName,7),Value);
ELSEIF (Count=8);
TextOutput(pLocation|vCubeName|'.csv', vCubeName,TABDIM(vCubeName,1),TABDIM(vCubeName,2),TABDIM(vCubeName,3),TABDIM(vCubeName,4),TABDIM(vCubeName,5),T
ABDIM(vCubeName,6),TABDIM(vCubeName,7),TABDIM(vCubeName,8),Value);
ELSEIF (Count=9);
TextOutput(pLocation|vCubeName|'.csv', vCubeName,TABDIM(vCubeName,1),TABDIM(vCubeName,2),TABDIM(vCubeName,3),TABDIM(vCubeName,4),TABDIM(vCubeName,5),T
ABDIM(vCubeName,6),TABDIM(vCubeName,7),TABDIM(vCubeName,8),TABDIM(vCubeName,9),Value);
ELSEIF (Count=10);
TextOutput(pLocation|vCubeName|'.csv', vCubeName,TABDIM(vCubeName,1),TABDIM(vCubeName,2),TABDIM(vCubeName,3),TABDIM(vCubeName,4),TABDIM(vCubeName,5),T
ABDIM(vCubeName,6),TABDIM(vCubeName,7),TABDIM(vCubeName,8),TABDIM(vCubeName,9),TABDIM(vCubeName,10),Value);
ENDIF;

Output
Cube Name Dimension1 Dimension 2 Value
product_sales month products 10
product_sales month products 20
product_sales month products 10
product_sales month products 30
product_sales month products 20
product_sales month products 10
product_sales month products 40

[Steve Rowe]

You've not said or need to make much clearer exactly what is going wrong with your code and what you are expecting it to do....It wasn't clear to me...

[lotsaram]

Code: Select all

l_Counter = 1;

While ( l_Counter <= 256);
    s = TabDim ( V1, l_Counter);
    If ( s @= '');
        AsciiOutput ('Y:\Temp\DimsInCubes.txt', V1, Trim ( Str ( l_Counter-1, 12, 0 ) ) );
        l_Counter = 257;
    EndIf; 
    l_Counter = l_Counter +1;
End;

That's a curious approach, when doing this I normally just set the While loop to exit once the dimension count is exceeded and TabDim returns a blank string.

Code: Select all

iDim = 1;
sDim = TabDim(vCube, iDim);
sCubeAndDims = vCube | ',' | sDim;
While( sDim @<> '' );
  iDim = iDim + 1;
  sDim = TabDim(vCube, iDim);
  sCubeAndDims = sCubeAndDims | ',' | sDim;
End;
AsciiOutput(sFile, sCubeAndDims);

[appleglaze28]

Was expecting the elements of the datapoint of those values to appear and not the dimension name.

[lotsaram]

There's something wrong with how the TI function:
.......

Considering the example Alan gave you why have you gone with this approach with such a large amount of unnecessary coding?

[Steve Rowe]

TextOutput(pLocation|vCubeName|'.csv', vCubeName,TABDIM(vCubeName,1),TABDIM(vCubeName,2),Value);

err but thats what your code says...
tabdim gives the name of the dimension, you want to use the variables from the variables tab of the TI process instead, probably V1 to Vx, make sure you set them all to string so that you dont have to worry about figuring which field is the value and converting it

[appleglaze28]

Thanks Steve for pointing that out....Thanks everyone for the help.

[Alan Kirk]

Thanks Steve for pointing that out....Thanks everyone for the help.

I'm sure you're most welcome, but next time you ask a question could you please take a moment to re-read it before hitting the [Submit] button to ensure that it's asking what you actually want to ask?

The original question asked for

the number of dimensions in a cube

Number: A numeric value. A single all-numeric mathematical output, designating how many of a given quantity of objects.

You did not, at any time, state that you wanted a list of the dimensions until that curious piece of code that you posted in the 6th post of the thread.

"The number of" and "a list of" are two rather dissimilar concepts. And that:

Was expecting the elements of the datapoint of those values to appear and not the dimension name.

bears absolutely no relation to either of them.

[Alan Kirk]

Code: Select all

l_Counter = 1;

While ( l_Counter <= 256);
    s = TabDim ( V1, l_Counter);
    If ( s @= '');
        AsciiOutput ('Y:\Temp\DimsInCubes.txt', V1, Trim ( Str ( l_Counter-1, 12, 0 ) ) );
        l_Counter = 257;
    EndIf; 
    l_Counter = l_Counter +1;
End;

That's a curious approach, when doing this I normally just set the While loop to exit once the dimension count is exceeded and TabDim returns a blank string.

Code: Select all

iDim = 1;
sDim = TabDim(vCube, iDim);
sCubeAndDims = vCube | ',' | sDim;
While( sDim @<> '' );
  iDim = iDim + 1;
  sDim = TabDim(vCube, iDim);
  sCubeAndDims = sCubeAndDims | ',' | sDim;
End;
AsciiOutput(sFile, sCubeAndDims);

Which is, essentially, what I'm doing although yours is gathering a list of dims rather than just the number. (The latter was what appeared to have been asked in the initial post, though as noted above the nature of the question underwent some rather curious mutations after that time.) In VBA I'd use Exit For or Exit Do or similar. I don't trust undocumented functions so I'm not using Break, and instead exit the loop by kicking the counter above the max.

The principal difference between yours and mine is that mine has a safety break; my loop can never exceed 256 iterations should the something go screwy on the server side and TabDim start returning some kind of garbage value. No, that should never happen. But I've seen too many things that should never happen, happen (not just in TM1) so I tend to whack airbags as well as seatbelts into my code if there's no cost involved in doing so.