How to Clean the data for particular element of dimension

[k2g]

Hi Friends,

I am new to TM1 and stuck in middle of something. I need your help to resolve it. The scenario is :

I have a cube having 5 dimension Month, Year, Regioncode, Price and Items(in order).

Month, Year and Regioncode act as filter in cube and value can be:
Element in Month dimension can be JAN, FEB, MAR, ........., DEC.
Element in Year dimension can be 1980, 1981,1982,..........,2011.
Element in Regioncode dimension can be 01, 02, 03,.........,100.

Price and item forms the row and column in cube.

Now i want to creat another TI process which when I run, shoulds promts user to enter value of month(eg FEB), year(eg 2010), regioncode(eg 01) and after execution it should clear the value(i mean set 0) in cube for this month FEB, year 2010, regioncode 01. I mean after running this TI, when a choose month as FEB, year as 2010 , and regioncode as 01 in cube it should show price for each item in cube as zero.

i want to use a single view and function ViewZeroOut but unable to implement it.

Thanks in advance.

[qml]

i want to use a single view and function ViewZeroOut but unable to implement it.

Show us what you have come up with so far. Basically, you need to have the following steps in the Prolog of your process:

a) destroy existing view
b) destroy existing subsets
c) create subsets based on your parameter variables
d) create a view
e) assign the subsets to the view
f) execute viewzeroout

After that it's good to again delete the now unnecessary objects.

Using the search option on this forum would give you quite a few hits, too.

[k2g]

Hi qml,

Thanks for your help.

Here i need some clarification on your suggestion.

Do i have to create 3 subset for each of Month, Year, Regioncode and then i have to assign each subset to a common view and use ViewZeroOut to clear data?

Thanks in advance.

[qml]

Do i have to create 3 subset for each of Month, Year, Regioncode and then i have to assign each subset to a common view and use ViewZeroOut to clear data?

Yes, precisely. Use SubsetCreate(), SubsetElementInsert(), and ViewSubsetAssign(), each one 3 times for the 3 separate dimensions you want to put a filter on in your view.

[catalinciumag]

Hi,

As the others mentioned, you have to create a view you want to zero out. That view is based on subsets that you defined before.
Now, it is not a good practice to destroy a view/subset and neither create it on demand. This will lock the elements that are associated with that view (cube, dimensions...) for the period of creation and there can be serious contention problems. Instead you should empty those subsets and enter the new elements (month, year, region code in your case). Is good to have a subset for each user that can perform such action to avoid concurrency. Same thing for Views.
Use static views and subsets.

Functions to use:
SubsetDeleteAllElements(DimName, SubsetName) - deletes all elements of a subset
SubsetElementInsert(DimName, SubName, ElName, Position) - inserts an element

If you have more questions or want to see some code I can post for you.

Hope this helps,
Catalin

[qml]

Now, it is not a good practice to destroy a view/subset and neither create it on demand.

I would be careful with this statement, because many developers, do, in fact, consider it a good practice. Performance is only one of many considerations, and there are many others, like usability and tidiness, for example (e.g. not leaving behind any temporary objects that would clutter users' views).

This will lock the elements that are associated with that view (cube, dimensions...) for the period of creation and there can be serious contention problems.

How long does this lock last? Typically, it's not even measurable. I have never seen real, practical examples of performance issues caused by locking when creating temporary subsets and views. There are many things that can impact performance due to object locking, but creating/destroying static temporary objects is very, very low on this list.

[catalinciumag]

Now, it is not a good practice to destroy a view/subset and neither create it on demand.

I would be careful with this statement, because many developers, do, in fact, consider it a good practice. Performance is only one of many considerations, and there are many others, like usability and tidiness, for example (e.g. not leaving behind any temporary objects that would clutter users' views).

This will lock the elements that are associated with that view (cube, dimensions...) for the period of creation and there can be serious contention problems.

How long does this lock last? Typically, it's not even measurable. I have never seen real, practical examples of performance issues caused by locking when creating temporary subsets and views. There are many things that can impact performance due to object locking, but creating/destroying static temporary objects is very, very low on this list.

I agree that there are many considerations when we are talking about performance, but using static vs dynamic objects is something that we should always consider when building a model. This is what I follow and I was trained to consider.

In an enterprise large scale solution where these objects are changed based on an user action you will defiantly see such locks.

[qml]

using static vs dynamic objects is something that we should always consider when building a model

I'm not even sure what you mean here by "static" and "dynamic". However, there is a grain of truth in this - dynamic subsets (MDX-based) can cause contention issues much bigger than static subsets. This should also be considered, not just the fact if a subset is persistent (doesn't get deleted) or temporary (created and deleted when needed), which has a minuscule impact on performance.

In an enterprise large scale solution where these objects are changed based on an user action you will defiantly see such locks.

It's lucky I've only worked on very small models with very few users and wasn't able to detect the serious issues you speak of.

[Gregor Koch]

I am really curious who would train you not to recreate and clean up views and subsets?
On which version were you trained?

You are basically saying that you (or the person who trained you) have built a 'enterprise large scale solution' in which users constantly need to run TIs and you have solved performance problems by not creating views and (static) subsets on the fly.
I agree with what was said about dynamic subsets, the lock around those is well known, but I am not sure what you mean with the dynamic views.

I'd vote it is good practice to recreate the views (delete all elements on the subsets and redo) but am happy to be convinced otherwise.
Do you actually create a lock (on what) by changing static subset? How long is that lock, longer than entering a number into a cube?

[Gregor Koch]

To answer my own question:
I guess it was IBM training as apparently it is indeed recommended by IBM not to re create Views and Subsets (even static ones). This is different to information I previously received.

So apparently the locks are issued but I would like somebody to confirm that and how big that impact really is.

Still I don't agree with the generalisation to do it that way always and the relevance of this will surely be limited to models (of any size) that need to run a lot of TIs during the day and/or are executed by users ad hoc .
Obviously if a process runs overnight you should have enough time to apply proper house keeping and recreate the lot and keep the list of views and subsets to a minimum.

[Steve Rowe]

Hi,
Do they offer any explanation as to why this should be so?
Is your comment on the locks inferred from the training statements or do IBM explicitly state this anywhere?

Can't say I've seen any evidence of creating data processing objects on the fly having been an issue.

I suspect that the downside of possible locking while these objects are being created and deleted is massively outweighed by the downside of cluttering your server up with data objects that are v.large for your users to open up and kill performance with......
Cheers

[lotsaram]

I suspect that the downside of possible locking while these objects are being created and deleted is massively outweighed by the downside of cluttering your server up with data objects that are v.large for your users to open up and kill performance with......

Agree. Our default approach is to create and destroy, except for processes used during forecast submission with quite heavy use and high concurrency where it is critical to avoid any locking and therefore performance issue.