Create TM1 View & Subsets with TI Process & Extract To File

[CapAmerica]

Now I need some help as this is my very first TI process that I have created. Now within this TI process I am trying to create some subsets then a view in which I also want to export the data from the view into a flat file so that another TM1 instance can use this file. Here is a high level screen shot of the of the file extract that I would like to have in a view.



Now I think I’m on the right track but would love for someone to tell me if I am correct or wrong and if so what should I do to correct the issue. Also, with my TI process below, I need some help with getting my subsets created. Now I know how to create them manually but I want to have them created within the TI process. Now I’m trying to create 4 subset, 3 of the subsets would be static and one subset would need a rule, I believe. Or some type of logic.

Here is what the cube looks like:

Code: Select all

############################
 ## Define Static Variables ##
 ############################
 
 	## Define Cube names ##
 		sCubSAPCosts = 'SAP_COSTS';
 
 	## Define Dimension Names ##
 		sDimCostElement = 'COSTELEMENT';
 		sDimCostCenter = 'COSTCENTER';
 		sDimOrder = 'ORDER';
 		sDimCurrency = 'CURRENCY';
 		sDimIntercompany = 'INTERCOMPANY';
 		sDimTimeline = 'TIMELINE';
 		sDimEntityBS = 'ENTITY_BS';
 		sDimStatus = 'PDD_Status';
 		sDimScenarioShort = 'SCENARIO_SHORT';
 		sDimSource = 'SOURCE';
 
 	## Define View Names ##
 		sViewSource = 'Ascend_Actuals_IC';
 
 	## Define Subset Names ##
 		sSubsetSource = sViewSource;
 		sSubsetDestination = sViewDestination;
 
 
 ############################
 ## Set up Actuals View from the SAP_COSTS Cube ##
 ############################	
 
 	## Remove the Source view if it exist already. 
 		IF(ViewExists(sAscendActualsIC,sViewSource) > 0);
 			ViewDestroy(sAscendActualsIC,sViewSource);
 		EndIf;
 
 	## Remove the subsets if they exist already. They will be dynamically recreated below.
 		IF(SubsetExists(sDimTimeline,sSubsetSource) > 0);
 			SubsetDestroy(sDimTimeline,sSubsetSource);
 		EndIf;
 
 		IF(SubsetExists(sDimIntercompany,sSubsetSource) > 0);
 			SubsetDestroy(sDimIntercompany,sSubsetSource);
 		EndIf;
 
 		IF(SubsetExists(sDimCurrency,sSubsetSource) > 0);
 			SubsetDestroy(sDimCurrency,sSubsetSource);
 		EndIf;
 
 		IF(SubsetExists(sDimScenarioShort,sSubsetSource) > 0);
 			SubsetDestroy(sDimScenarioShort,sSubsetSource);
 		EndIf;
 	
 	## Create Source View
 		ViewCreate(sAscendActualsNonIC,sViewSource);
 
 	## Create Subsets  ---- NEED HELP
 
 
 	## Assigned the subset to view  --- NEED HELP
 
 
 
 ############################
 ## END TI PROCESS ##
 ############################

Dimensions: for Subset
SCENARIO_SHORT - STATIC


INTERCOMPANY - STATIC


CURRENCY - STATIC


TIMELINE
Now the TIMELINE subset will be different as this subset should not be static and I’m not sure how to do this or where to start. I'm guessing I need a rule. What I would like to do is grab the current month and year and pull data 3 years back.

Here’s how I create this subset manually.

First I go to Timeline Dimension


Second I then open Timeline Dimension


Third I close all hierarchies


Fourth step I expand and open the “Timeline Fiscal Year” hierarchy


Fifth step I select the current FY year and 2 years prior. This is where I think I need to add a rule or sometype of logic.


Once selected I then click on the “Expand” button


After clicking the “Expand” button the lower level elements will be shown. Next, I click on “Filter by Level” button, in which I Select 0 then click OK.

Then I save the following subset as 3_Years_Monthly.

Now how do I do this in a TI process?

Now these are the only subsets I need for my view. Once the view is created with this set of data the next step I wanted to do is export this into a flat file. Can anyone provide me any example on how to do this when the view is created? Thanks!

[Wim Gielis]

Hello,

Like this:

Code: Select all

SubsetCreate('SCENARIO_SHORT', sSubsetSource);
SubsetElementInsert('SCENARIO_SHORT', sSubsetSource, 'Act@Act', 1);

# and:

SubsetCreateByMDX(sSubsetSource, '{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [ TIMELINE ] )}, 0)}, ASC)}');

The first one is easy I guess.

The second one is a dynamic MDX-based subset. Inside is the expression that generates a sorted list of all lowest level elements in the TIMELINE dimension.
That is of course too much data for your view, you should adapt the MDX to grab only the elements (months and years) that you need.
Or, make a WHILE... END loop over the elements of the dimension and use SubsetElementInsert when the element (in the loop) fits your criteria.
This kind of loop isn't costly at all, it's very fast in TM1 and is on par with a (difficult) MDX expression.
If the expression is rather easy or standard practice in TM1-land, I would go for a shorthand MDX expression instead of looping over elements.

Good luck ! Know that there exists good example code on the internet if you know the websites

[CapAmerica]

Thanks Wim Gielis I was able to finally figure it out with the help of you post. Thanks again!