Update versus recreate in TM1 process?
-
- Posts: 46
- Joined: Fri Jun 15, 2012 1:46 pm
- OLAP Product: TM1
- Version: 10.1
- Excel Version: Office 10
Update versus recreate in TM1 process?
I have built a TM1 process that runs nightly to update a product dimension where in the query against the database is only pulling products marked as "saleable", which of course can change for any product at any time. In my process, I have chosen "Update" as my action when the process runs, but this only seems to be adding new saleable items to the dimension instead of adding new products and deleting those products no longer flagged saleable which is of course the desired result.
The documentation is really a little unclear about what "recreate" does. Unless I'm reading it wrong, "recreate" completely rebuilds the dimension which would seem to indicated that data entered for an item that is in the dimension would "disappear" as the dimension was rebuilt every time the process run. Am I way off base here? If not, what might be an approach to accomplish what I need to do?
Thanks!
Mike
The documentation is really a little unclear about what "recreate" does. Unless I'm reading it wrong, "recreate" completely rebuilds the dimension which would seem to indicated that data entered for an item that is in the dimension would "disappear" as the dimension was rebuilt every time the process run. Am I way off base here? If not, what might be an approach to accomplish what I need to do?
Thanks!
Mike
- jim wood
- Site Admin
- Posts: 3952
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Update versus recreate in TM1 process?
From what you are saying you do need to recreate as you don't want any of the old product hanging around. If however you want to retain the data break the run in to 2 parts. Create a seperate hierarchy with a parent called "not current" or something like that. Then repopulate an attribute saying whether on sales. Then move any products with no on sale attribute in to the not current parent. Also move any out from that parent that are now on sale. Then just update and any new products will be added,
Jim.
Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- MVP
- Posts: 160
- Joined: Wed Aug 17, 2011 3:51 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: Excel 2007
Re: Update versus recreate in TM1 process?
If you look at the TI script generated from selecting "Recreate" you will see that the DimensionDeleteAllElements TI is used (by TM1 Architect anyways).
From the docs:
if you use DimensionDeleteAllElements to delete elements, then recreate those elements with the same names in the Metadata tab, any data points in a cube identified by the elements will be retained after rebuilding the dimension.
In other words, it will repopulate your dimension but you will not lose your data for elements that remain in the dimension.
From the docs:
if you use DimensionDeleteAllElements to delete elements, then recreate those elements with the same names in the Metadata tab, any data points in a cube identified by the elements will be retained after rebuilding the dimension.
In other words, it will repopulate your dimension but you will not lose your data for elements that remain in the dimension.
-
- MVP
- Posts: 3666
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Update versus recreate in TM1 process?
DimensionDeleteallElements may not delete data provided that the elements are recreated but it will clear all static subsets, which I would generally regard as a pretty bad thing. Typically my preference would be to unwind, then rebuild and remove any unwanted elements, this will preserve the subsets.dr.nybble wrote:In other words, it will repopulate your dimension but you will not lose your data for elements that remain in the dimension.
-
- MVP
- Posts: 160
- Joined: Wed Aug 17, 2011 3:51 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: Excel 2007
Re: Update versus recreate in TM1 process?
I tried a "Recreate" test with two static subsets on the dimension.
Where the static subset contained an element not in the new source file it was cleared as expected.
But where it contained elements also in the new source file it was preserved -- I don't see the issue you are describing.
Can you describe how to reproduce this behaviour?
Where the static subset contained an element not in the new source file it was cleared as expected.
But where it contained elements also in the new source file it was preserved -- I don't see the issue you are describing.
Can you describe how to reproduce this behaviour?
-
- MVP
- Posts: 3666
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Update versus recreate in TM1 process?
If everything is managed within one process then its ok but if executeprocess is used to manage separate portions of a metadata update in distinct processes (even if all called from the Prolog of a master process) then I have found this to occur.
Also frequently during development phases you may encounter a process break or quit befor the rebuild completes or even run separate portions separately in which case loss of subsets and/or data is garunteed if using deleteallelements. Therefore I find it best to always build dimension updates in such a way that there is no possibility of this happening with a 3 step process of unwind - rebuild - tidy up.
Also frequently during development phases you may encounter a process break or quit befor the rebuild completes or even run separate portions separately in which case loss of subsets and/or data is garunteed if using deleteallelements. Therefore I find it best to always build dimension updates in such a way that there is no possibility of this happening with a 3 step process of unwind - rebuild - tidy up.
-
- MVP
- Posts: 600
- Joined: Wed Aug 17, 2011 1:19 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2
- Excel Version: 2003 2007
- Location: York, UK
Re: Update versus recreate in TM1 process?
A word of warning about dimension updates and sub-processes. I may have said it before but there's no harm saying it again.
Do not change a dimension - in any way - in a master process, and then call a sub-process to modify it further in either the prolog or the metadata section of the outer process. The outer process and the sub-process will be working on different unconnected copies of the original dimension and the changes in the outer process will win.
e.g. if you DimensionDeleteAllElements in the outer process and then call a sub-process to insert some more elements your dimension will end up with none.
Do not change a dimension - in any way - in a master process, and then call a sub-process to modify it further in either the prolog or the metadata section of the outer process. The outer process and the sub-process will be working on different unconnected copies of the original dimension and the changes in the outer process will win.
e.g. if you DimensionDeleteAllElements in the outer process and then call a sub-process to insert some more elements your dimension will end up with none.
-
- Posts: 13
- Joined: Fri Aug 31, 2012 6:30 am
- OLAP Product: TM1
- Version: 10.1
- Excel Version: Excel 2007
Re: Update versus recreate in TM1 process?
hi,
I have a TI which is pretty much doing what is discussed.
I use a 3 step approach.
1. Unwind the structure
2. Create structure based on Datasource out of a SQL database
3. tidy up all not assigned Elements of the dimension.
The 3rd step is where my problems occur.
Although I'm almost using the same code in the epilog to tidy up the elements. I'm not able to assign the elements to the parent element.
Any help or hint is appreaciated
I have a TI which is pretty much doing what is discussed.
I use a 3 step approach.
1. Unwind the structure
2. Create structure based on Datasource out of a SQL database
3. tidy up all not assigned Elements of the dimension.
The 3rd step is where my problems occur.
Although I'm almost using the same code in the epilog to tidy up the elements. I'm not able to assign the elements to the parent element.
Any help or hint is appreaciated
Code: Select all
i = DimSiz(MDimension);
x = 1;
WHILE (x <= i);
vElement = DimNm(MDimension, x);
IF (ElCompn(MDimension, vElement) = 0);
IF (ElParN(MDimension, vElement) = 0);
IF (Dimix(MDimension, MWaise ) = 0);
DIMENSIONELEMENTINSERT(MDimension,'',MWaise ,'c');
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(MDimension,MWaise ,vElement,0);
ENDIF;
ENDIF;
x = x + 1;
END;
-
- MVP
- Posts: 600
- Joined: Wed Aug 17, 2011 1:19 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2
- Excel Version: 2003 2007
- Location: York, UK
Re: Update versus recreate in TM1 process?
DimensionElementComponentAddIBM Reference Documentation wrote:Note that you cannot use this function in the Epilog procedure of a TurboIntegrator process.