TM1 Forum

Posted: **Fri Dec 13, 2019 4:42 pm**

Hi, I'm trying to get TI to extract data based on a subset that is sorted according to project value based on criteria chosen by a user.

I used MDX to sort the project dimension based on the selected criteria and is working. However, when using AsciiOutput I noticed data is sorted based on dimension index instead of the MDX subset.

I wonder if I'm missing an operator to tell TI to output exactly the way the view is built.

Appreciate any help.

Kenneth

Posted: **Fri Dec 13, 2019 4:53 pm**

An update: now I found this thread: https://www.tm1forum.com/viewtopic.php?t=10604 .

I noticed Tom saying to "do your own walk through the subsets in the Prolog tab using CellGetN and ASCIIOutputs inside WHILE loops".

I wonder what it means.

Posted: **Fri Dec 13, 2019 5:29 pm**

First make 100% sure that you are using the subset in the way you think you are.

Create the subset
Assign the MDX.
Remove the MDX (since we don't want it potentially re-calcing when we export)
Assign it to the view used in the export.

Tom is I think suggesting doing manually what the data tab is doing. If your cube is large and or sparse it is probable that it won't be a practical approach.

HTH

Posted: **Fri Dec 13, 2019 6:36 pm**

Thanks Steve.

I tried following your instructions and added a SubsetMDXSet function to remove the MDX. It converts the subset to be static but the export still lists the elements using index order.

Basically in my Prolog I created the temp. view and subset then used a WHILE loop for all the dimensions and select the elements subject to the view. For the hybrid program project dimension, I used the ORDER MDX function to sort the order.

After selecting the element for the subset I created the MDX subset, assign the subset to the view, then remove the MDX before I looped to another dimension. Then the usual ViewExtract statements and DataSource statements.

Not sure if I'm missing anything. I'm including the Prolog for reference:

Code: Select all

vFilePath = '..\ExtractFiles\Ranking\';
sCube='rpt_by_ranking';
sSubset = 'source_ranking_subset';
sView = 'source_ranking_view';

HeaderRow = 0;

ViewDestroy(sCube,sView);
ViewCreate(sCube,sView);

vOldLogChanges = CubeGetLogChanges(sCube);
CubeSetLogChanges(sCube, 0);

##############################
vIndexDim = 1;
WHILE(TABDIM(sCube, vIndexDim) @<> '');
	vDimName = TABDIM(sCube, vIndexDim);
	SubsetDestroy(vDimName,sSubset);

	IF( vDimName @='cbm_version');
	vMDX= '{[' | vDimName | '].[' | pVersion | ']}';

	ELSEIF(vDimName @='cbm_hybrid_program_project');
	vMDX = '
	{
	ORDER	(
			{TM1FILTERBYLEVEL	(
							{TM1SUBSETALL(cbm_hybrid_program_project)}
							,0
						)
			}
			,[cbmr_rpt_by_ranking].
					(
					[cbm_version].[' | pVersion | ']
					,[cbm_account].[' | pAccount | ']
					,[cbmr_rpt_by_m].[' | pYear | ']
					), BDESC
			)
	}';

	ELSEIF(vDimName @='cbm_account');
	vMDX= '{[' | vDimName | '].[' | pAccount | ']}';

	ELSEIF(vDimName @='cbmr_rpt_by_m');
	vMDX= '{[' | vDimName | '].[' | pYear | ']}';

	ELSE;
	vMDX = '{TM1FILTERBYLEVEL( TM1SUBSETALL( [' | vDimName | ']), 0)}'; 

	ENDIF;

	SubsetCreateByMDX(sSubset, vMDX);

	ViewSubsetAssign(sCube, sView, vDimName, sSubset);

	SubsetMDXSet(vDimName, sSubset, '');

	vIndexDim = vIndexDim +1;
END;

#########################################################

ViewExtractSkipZeroesSet(sCube, sView, 1);
ViewSuppressZeroesSet(sCube, sView, 1);
ViewExtractSkipCalcsSet(sCube, sView, 0);

DatasourceNameForServer = sCube;

DatasourceCubeView = sView;

Steve Rowe wrote: ↑Fri Dec 13, 2019 5:29 pm First make 100% sure that you are using the subset in the way you think you are.

Create the subset
Assign the MDX.
Remove the MDX (since we don't want it potentially re-calcing when we export)
Assign it to the view used in the export.

Tom is I think suggesting doing manually what the data tab is doing. If your cube is large and or sparse it is probable that it won't be a practical approach.

HTH

Posted: **Fri Dec 13, 2019 7:05 pm**

Assuming it is only the dimension cbm_hybrid_program_project that you care about being ordered; you can keep the source view as you have it but with that dimension instead just having a single element in its assigned subset which is the total of all relevant elements.

Make sure consolidations are included.

On your data tab do a while loop over the ordered subset that is no longer part of your data source.

As you’ve found for a cube view data source it does not process in the order of the subsets used so you need to get creative for processes that require specific ordering. Other options are to create temp cubes and dims or export the whole thing to a SQL table and do your ordering there.

Posted: **Fri Dec 13, 2019 7:13 pm**

Kenship, is your user info up to date that you're on version 10.2 or have you upgrade to PA 2.0? ViewCreateByMDX is a function that you can use if you're using 2.0 that I believe will keep the element order as you've defined it in the subset. If it doesn't and I'm mistaken, you can add an ORDER statement to the MDX.

Posted: **Fri Dec 13, 2019 8:32 pm**

Thanks and I'm actually on PA 2.0.7.

I'm trying to research to use ViewCreateByMDX but the statements couldn't figure out the syntax yet for a MDX view.

In the mean time I'll probably having a TI for the user that they can name the view and access it then take a snap shot.

Thanks!

ascheevel wrote: ↑Fri Dec 13, 2019 7:13 pm Kenship, is your user info up to date that you're on version 10.2 or have you upgrade to PA 2.0? ViewCreateByMDX is a function that you can use if you're using 2.0 that I believe will keep the element order as you've defined it in the subset. If it doesn't and I'm mistaken, you can add an ORDER statement to the MDX.

Posted: **Fri Dec 13, 2019 8:45 pm**

kenship wrote: ↑Fri Dec 13, 2019 8:32 pm
I'm trying to research to use ViewCreateByMDX but the statements couldn't figure out the syntax yet for a MDX view.

If you'd like, post your view details (cube & dim names) including any subset names used and I or someone else on the forum can help with the MDX statement.

Posted: **Fri Dec 13, 2019 8:59 pm**

Hi, I was only testing with a test cube with test dimensions, it was like:

######
ViewCreateByMDX
(
'Cube',
'View',
' { [ Dim1 ] . [1] } , { [ DimA ] . [A] }'
);
######

Below is the MDX expression for the dimensions subject to the view that I wanna export.

Code: Select all

##############################
vIndexDim = 1;
WHILE(TABDIM(sCube, vIndexDim) @<> '');
	vDimName = TABDIM(sCube, vIndexDim);
	SubsetDestroy(vDimName,sSubset);

	IF( vDimName @='cbm_version');
	vMDX= '{[' | vDimName | '].[' | pVersion | ']}';

	ELSEIF(vDimName @='cbm_hybrid_program_project');
	vMDX = '
	{
	ORDER	(
			{TM1FILTERBYLEVEL	(
							{TM1SUBSETALL(cbm_hybrid_program_project)}
							,0
						)
			}
			,[cbmr_rpt_by_ranking].
					(
					[cbm_version].[' | pVersion | ']
					,[cbm_account].[' | pAccount | ']
					,[cbmr_rpt_by_m].[' | pYear | ']
					), BDESC
			)
	}';

	ELSEIF(vDimName @='cbm_account');
	vMDX= '{[' | vDimName | '].[' | pAccount | ']}';

	ELSEIF(vDimName @='cbmr_rpt_by_m');
	vMDX= '{[' | vDimName | '].[' | pYear | ']}';

	ELSE;
	vMDX = '{TM1FILTERBYLEVEL( TM1SUBSETALL( [' | vDimName | ']), 0)}'; 

	ENDIF;

	SubsetCreateByMDX(sSubset, vMDX);

	ViewSubsetAssign(sCube, sView, vDimName, sSubset);

	SubsetMDXSet(vDimName, sSubset, '');

	vIndexDim = vIndexDim +1;
END;

#########################################################

ascheevel wrote: ↑Fri Dec 13, 2019 8:45 pm
kenship wrote: ↑Fri Dec 13, 2019 8:32 pm
I'm trying to research to use ViewCreateByMDX but the statements couldn't figure out the syntax yet for a MDX view.

If you'd like, post your view details (cube & dim names) including any subset names used and I or someone else on the forum can help with the MDX statement.

Posted: **Fri Dec 13, 2019 9:02 pm**

For your test cube, your MDX should look something like the below. I'll try to take a look at your other posted code this weekend.

Code: Select all

ViewCreateByMDX('Cube', 'View',
	'select {[Dim1].[1]} on 0,
	{[DimA].[A]} on 1 from [Cube]'
	);

Posted: **Fri Dec 13, 2019 10:33 pm**

If your data source is a SUBSET then sure you can absolutely control the order of output but if your data source is a VIEW (or at least a native view) then no you cannot control the order of elements. For a view elements will be processed in index order and dimensions will be "rotated" from last to first in as per the "optimized" dimension order whihc has been set (and if the cube has not been reordered then in the original order).

This has been discussed many times.

Posted: **Fri Dec 13, 2019 10:34 pm**

Thank you! Will try.

ascheevel wrote: ↑Fri Dec 13, 2019 9:02 pm For your test cube, your MDX should look something like the below. I'll try to take a look at your other posted code this weekend.
Code: Select all
ViewCreateByMDX('Cube', 'View',
	'select {[Dim1].[1]} on 0,
	{[DimA].[A]} on 1 from [Cube]'
	);

Posted: **Mon Dec 16, 2019 8:53 pm**

Update:

Instead of extracting data to a file a decision was made to present data as a view for user. I ended up removing all the codes related to data extraction and data source specification.

Thanks to all.

Kenneth

TM1 Forum

AsciiOutput element order different from subset

AsciiOutput element order different from subset

Re: AsciiOutput element order different from subset

Re: AsciiOutput element order different from subset

Re: AsciiOutput element order different from subset

Re: AsciiOutput element order different from subset

Re: AsciiOutput element order different from subset

Re: AsciiOutput element order different from subset

Re: AsciiOutput element order different from subset

Re: AsciiOutput element order different from subset

Re: AsciiOutput element order different from subset

Re: AsciiOutput element order different from subset

Re: AsciiOutput element order different from subset

Re: AsciiOutput element order different from subset