Is there a way to for TI to write to multiple intersection in one line?

[Ashleigh W]

Hello experts,

Is there a way to for TI to write to multiple intersection in one line without looping through each element in Dimension subset?
Any alternative ways or options are most welcome.

Below code works find which to create a dynamic subset and tag all the elements by looping through elements however with 1000 of data points it take time to process.

thanks.

Code: Select all

myMDX = '{TM1DRILLDOWNMEMBER({[' | var_dim | '].[' | var_ele | ']},ALL,RECURSIVE)}';
SubsetCreatebyMDX(var_subsetName, mdx );

xCount = 1;
eleCount = SubsetGetSize( var_dim, var_subsetName);
WHILE( xCount <= eleCount );
	var_mdx_ele = SubsetGetElementName( var_dim, var_subsetName,  index );
	CellputS(dyna_var, xCube,vCatg, var_mdx_ele, 'xyz');
	index = index + 1;
END;

[Emixam]

Hi,

Is there a way to for TI to write to multiple intersection in one line without looping through each element in Dimension subset?

Short answer, no.

EDIT: I didn't saw it was CellPutS, therefore CellPutProportionalSpread will never work.

However, there is the CellPutProportionalSpread function but as you probably know, this will distributes a specified value to the leaves of a consolidation proportional to existing cell values. I don't think this is what you want to achieve.

[Ashleigh W]

thanks for quick response. This might actually work. I need to flag 0,1 or 2

[burnstripe]

How long does it take to process? Is the loop through the subset in a prolog or data tab. If its in the data tab, is the source another cube view? If so lock checking may be slowing it down. It's quick and dirty but you could try outputting to a csv instead of inserting directly from the process. And then having a separate process load this file into the cube. You'd be surprised how much of an impact this can have.

Also you could disable cube logging for xCube during the process load if it's not already disabled

[Ashleigh W]

Thanks burnstripe. I will check the approach of writing to csv and loading back to target cube.
Currently I am checking for best solution, and it is taking about 30mins which is not expectable

Process 1: Stage all relevant data. Few seconds to process
Process 2: Process above data using below code in Prolog. About 30mins.

Code: Select all

myMDX = '{TM1DRILLDOWNMEMBER({[' | var_dim | '].[' | var_ele | ']},ALL,RECURSIVE)}';
SubsetCreatebyMDX(var_subsetName, mdx );

xCount = 1;
eleCount = SubsetGetSize( var_dim, var_subsetName);
WHILE( xCount <= eleCount );
	var_mdx_ele = SubsetGetElementName( var_dim, var_subsetName,  index );
	CellputS(dyna_var, xCube,vCatg, var_mdx_ele, 'xyz');
	index = index + 1;
END;

[Emixam]

Is there heavy rules in xCube ? if yes, I'd suggest to take a look at this bedrock process to load/unload rules.

Also, whenever I'm creating a temporary subset to use in a loop, I always convert them into static

Code: Select all

myMDX = '{TM1DRILLDOWNMEMBER({[' | var_dim | '].[' | var_ele | ']},ALL,RECURSIVE)}';
SubsetCreatebyMDX(var_subsetName, mdx );
SubsetMDXSet( var_dim, var_subsetName, '' );

If there is only 1000 elements in your subset, it shouldn't take 30 min !

[Wim Gielis]

If you want to write String data to the cube (CellPutS), CellPutProportionalSpread is not going to help you.

But most likely, yes, the fact that the dynamic subsets recaculates too many times could be the culprit.

[Ashleigh W]

Appreciate all the inputs. Thank you as always.

[PavoGa]

There is an outlier on this that can significantly impact performance writing to a cube as well and that is dimension order, although with what appears to be three dimensions in the cube, this may be an unlikely culprit.

The first thing that needs to be corrected is the subset needs to be static as Emixam said, as well as looking into his comment on the rules being a potential issue. Writing to a cell that is applying feeders can take as long as a month of Sundays.

Otherwise a loop through 1000 elements should be a blink of eye. Also the code snippet does not look quite right. SubsetGetElementName is using a variable index, but that is not assigned an initial value in this snippet, although xCount is. I assume xCount and Index are mixed here?