Removing some data from a cube

[ATD]

Hi,

Is there a way to remove data from a cube based on the values in two dimensions? For example, suppose I have a cube with year and month as two of the dimensions and now need to replace that data, I want to ensure that the cube only contains the new data for that year/month and that any old cells for the year/month that are no longer valid no longer exist and I don't want to remove data relating to any other year/month.

I can see how to clear the entire cube's data but not how to remove all cells based on specific dimension values.

Any ideas?

Thanks

Andy

[tomok]

Use the function ViewZeroOut.

[ATD]

Thanks Tomok,

So, I create a view using the dimension values I need as a filter and then zero out the data in that view. Something like:

VIEWCREATE (‘MyCube’, ‘SinceStartDate’);
SUBSETCREATEBYMDX(‘Last2Months’, ’{ LastPeriods(-9999999, [MyTimeDim].[‘ | MyStartDate | ‘]) }’);
VIEWSUBSETASSIGN(‘MyCube’, ‘SinceStartDate’, ‘MyTimeDim’, ‘Last2Months’);
VIEWZEROOUT(‘MyCube’, ’SinceStartDate’);

(just copied from the web, so I've not updated it for my stuff yet!)

Is that correct?

Thanks

Andy

[David Usherwood]

You'd be better off building the view by hand via the 'Export to Text File' dialog. The subsets you need to change at runtime should be attached to the view by name, then you can adjust the subset contents in your code and the view will change accordingly. Much less code and also much less locking, compared to building and tearing down the view each time.

[Alan Kirk]

You'd be better off building the view by hand via the 'Export to Text File' dialog. The subsets you need to change at runtime should be attached to the view by name, then you can adjust the subset contents in your code and the view will change accordingly. Much less code and also much less locking, compared to building and tearing down the view each time.

I take your point but I'm afraid I'm still inclined to go the "build and tear down" route. The way you've described leaves a public view on the server which:
(a) I don't trust users not to try to open, which could result in a massive hit to the server depending on how it's set up;
(b) Clutters the list of genuinely user-needed public views; and
(c) There's also the risk of handing off the system to someone else who doesn't realise that the view is needed for a TI process and deletes it as part of a "cleanup" process.

Now if TM1 had been enhanced to allow {gritted teeth} Data. Sources. To. Be. Separated. From. Views. The. Way. IBM. Should. Have. Done. At. Least. Five. Frapping. Years. Ago. instead of wasting their time developing poorly performing but superficially appealing Java wizards for their sales demos...{/gritted teeth}{Deep breath, one-two-three-four-five-six-seven-eight-nine-ten...}

Then I would be OK with leaving Views (or, as we would be calling them if IBM would focus on useful enhancements, "queries" or "data sources" or something) in place and just updating them dynamically as needed. Of course I see this improvement in our future, not. Until then I feel safer doing the strip-down rebuild route and making the process / chore as self-contained and as little dependent on non-standard objects as possible.

[lotsaram]

Likewise I tend to disagree with the manual view and subsets approach because there is a better way. Use the bedrock clear data process. All that's needed is one parameter for the cube name and another for the dimension/element filter combination. The process does all the view creation, zero out and removal. While we wait for IBM to get around to creating special data source views (which I agree would be nice to have) then this is good enough for me.

[Alan Kirk]

Likewise I tend to disagree with the manual view and subsets approach because there is a better way. Use the bedrock clear data process. All that's needed is one parameter for the cube name and another for the dimension/element filter combination. The process does all the view creation, zero out and removal. While we wait for IBM to get around to creating special data source views (which I agree would be nice to have) then this is good enough for me.

I thought of suggesting Bedrock.Cube.Data.Clear, but I don't use it for one main reason; it lacks a parameter to turn off cube logging during the zero out process. The lack of an ability to disable logging becomes a problem with regular "blow away and refresh" cycles since you end up with massively bloated log files. This can be added by the end user obviously, but they need to understand how to code TI to do that and of course as soon as they do then they've moved away from the standard Bedrock code and need to be careful when doing upgrades. (I can't put the details of how to modify the process here given the terms of Bedrock's licencing.)

I actually raised the addition of such a parameter as a suggestion to Bedrock but either it didn't appeal to them or it just got buried somewhere. I have a generic process of my own that does something similar, and it's the kind of process that should really be in every admin's library.

[ATD]

Hi All,

Thanks for the replies. I'm inclined to create and then destroy a view etc as well as it would only be used for this purpose and I don't like leaving behind what could become a small collection of views that are only there for admin purposes.

Thanks

Andy

[Gabor]

For a one-time action, using "Data Spread\Clear" with a right mouse in a Perspectives cube cell is a fast way to do it.
It's slower than ViewZeroOut, but does not require a TI & public view.

[karkea]

I've used Bedrock's ZeroOut process, but the problem is as said earlier cube logging.

In addition I have had to schedule periodic cleanup of temporary views and subsets created by these ZeroOut processes.

BR,
Kimmo

[BariAbdul]

I have StockReportingCube which has Time_Dimension in the form of 2013-01-02 and I have tried to zeroOut data on this particular date ,I have created a view out of StockReportingCue and tried to ZeroOut on 2013-01-02,The Process run fine but it throws an error "Unable to Register a View'.Could gurus help me what I am doing wrong here,Thanks a lot in Advance



#****Begin: Generated Statements***
#****End: Generated Statements****

Cube=' StockReportingCube ';
MyTimeDim= 'Time_Dimension';
MyStartDate='2013-01-02';
VIEWCREATE ( 'StockReportingCube ' ,'SinceStartDate');
SUBSETCREATEBYMDX('Last2Months', '{ LastPeriods(-9999999, [Time_Dimension].[' | MyStartDate | ']) }');
VIEWSUBSETASSIGN( 'StockReportingCube ', 'SinceStartDate', 'Time_Dimension', 'Last2Months');
VIEWZEROOUT(' StockReportingCube ', 'SinceStartDate');

[bplaia]

The Process run fine but it throws an error "Unable to Register a View'.

My advice would be to check and see if there is a view already created with that name ('SinceStartDate'), since that's the error that gets thrown when it tries to create a view that already exists.

Maybe add some conditional logic to check for existence and if it exists, blow it away then recreate it after your IF statement

[BariAbdul]

#****Begin: Generated Statements***
#****End: Generated Statements****





#****Begin: Generated Statements***

Thanks heaps,You were spot on.The Process runs fine now without error,Below is the code:



#****End: Generated Statements****


Cube=' StockReportingCube ';
MyTimeDim= 'Time_Dimension';
MyStartDate='2013-01-02';
IF( ViewExists('StockReportingCube ' ,'SinceStartDate')=1);
ViewDestroy('StockReportingCube' , 'SinceStartDate');
EndIf;

VIEWCREATE ( 'StockReportingCube ' ,'SinceStartDate');
SUBSETCREATEBYMDX('Last2Months', '{ LastPeriods(-9999999, [Time_Dimension].[' | MyStartDate | ']) }');
VIEWSUBSETASSIGN( 'StockReportingCube ', 'SinceStartDate', 'Time_Dimension', 'Last2Months');
VIEWZEROOUT(' StockReportingCube ', 'SinceStartDate');

Code: Select all

[ATD]

Hi,

Aarrrgghh! Trying to find anything anywhere that explains the syntax of the MDX Expression needed is really p*****g me off!

Given the following:

Cube: ATD_VIEW_TEST
Dimension: ATD_MONTH - need to filter to "12"
Dimension: ATD_YEAR - need to filter to "2013"

can someone help with the expression that I need in the SUBSETCREATEBYMDX function so that I can create a view on the cube that as the above filters

I can create the view manually very easily, but every time I try to create the view by code, I get syntax errors (with no explanation of what the error is) or "Unable to register subset" errors etc etc etc.

Thanks

Andy

[declanr]

Hi,

Aarrrgghh! Trying to find anything anywhere that explains the syntax of the MDX Expression needed is really p*****g me off!

Given the following:

Cube: ATD_VIEW_TEST
Dimension: ATD_MONTH - need to filter to "12"
Dimension: ATD_YEAR - need to filter to "2013"

can someone help with the expression that I need in the SUBSETCREATEBYMDX function so that I can create a view on the cube that as the above filters

I can create the view manually very easily, but every time I try to create the view by code, I get syntax errors (with no explanation of what the error is) or "Unable to register subset" errors etc etc etc.


Thanks

Andy

If you mean that you just want 1 element in each subset don't bother with MDX, just use SubsetCreate and SubsetElementInsert.

If you are looking for multiple Months that contain 12 etc, open the subset editor, press "record expression", turn on the expression window, expand your dimension, filter by wildcard.... and finally see what MDX code it creates for you.

[ATD]

Thanks, Declan.

Took a while to work out, but I found an example and ended up with:

Code: Select all

#****Begin: Generated Statements***
#****End: Generated Statements****

c = 'ATD_VIEW_TEST';
v = 'VIEW_2013_12';
y = '2013';
m = '12';
zM = 'ZeroOutMonth';
zY = 'ZeroOutYear';

# 1. Remove view and subsets if they already exist
IF(ViewExists(c, v)=1);
 ViewDestroy(c, v);
ENDIF;

IF(SubsetExists('ATD_MONTH', zM)=1);
 SubsetDestroy('ATD_MONTH', zM);
ENDIF;

IF(SubsetExists('ATD_YEAR', zY)=1);
 SubsetDestroy('ATD_YEAR', zY);
ENDIF;


# 2. Create new subsets
SUBSETCREATE('ATD_MONTH',zM);
SUBSETELEMENTINSERT('ATD_MONTH',zM,'12',1);

SUBSETCREATE('ATD_YEAR',zY);
SUBSETELEMENTINSERT('ATD_YEAR',zY,'2013',1);


# 3. Create view & assign subsets
VIEWCREATE(c,v);
VIEWSUBSETASSIGN(c,v,'ATD_MONTH',zM);
VIEWSUBSETASSIGN(c,v,'ATD_YEAR',zY);


# 4. Zero out
VIEWZEROOUT(c,v);


# 5. Tidy up
VIEWDESTROY(c,v);
SUBSETDESTROY('ATD_YEAR',zY);
SUBSETDESTROY('ATD_MONTH',zM);

The subset names for each dimension had to be different but, other than it generated a large log file, that seems to have done the trick - ie, the data is now zero for 12/2013

Andy

[Wim Gielis]

Hi,

Aarrrgghh! Trying to find anything anywhere that explains the syntax of the MDX Expression needed is really p*****g me off!

Did you mean:

http://www.bihints.com/book/export/html/68 (the famous one)
http://users.skynet.be/fa436118/wim/tm1 ... nts_EN.htm (the somewhat less famous one, but it's from my own website)

Wim

[ATD]

Hi Wim,

Yes - I went to both of those sites.

The first page was confusing but I tried everything from there that seemed to apply to my situation and couldn't get anything other than errors

Following Declan's advice, I then found another page on the same site as your second page: http://users.skynet.be/fa436118/wim/tm1 ... iew_EN.htm - and that gave me what I needed. The last bit of my code was to make the month/year dynamic and I've sorted that bit out.

I've now bookmarked your site as it seems a lot easier to follow than others I've been too (and I've been to a lot in the past few days!) - so, thanks


Andy

[tomok]

Yes - I went to both of those sites.

The first page was confusing but I tried everything from there that seemed to apply to my situation and couldn't get anything other than errors

The MDX Primer is not necessarily for beginners but I think it is very well written and is an invaluable resource for TM1 developers. MDX is not an easy language and not very widely known so there isn't much information about it available on the internet. However, it is well worth the investment in time to learn it if you plan on doing much of value, especially in reporting, with TM1.

The last bit of my code was to make the month/year dynamic and I've sorted that bit out.

Using MDX code for a temporary subset for deleting data is a complete waste of time in my opinion except for the geek factor. In order to do that you have to know the parameter(s) values with which to filter/select the elements and if you know those values then it is just as easy to add the elements to a static subset.

[ATD]

Hi Tom,

I'm sure the MDX functionality is invaluable - I am a novice at TM1, so the pages I looked at were mind-boggling to say the least! When I get the chance, I'll look further into it but, for now, I have something working that suits my needs.

The only change I've made to the code I posted earlier was to make the month/year values dynamic - fortunately, these will always relate to the prior month, so I've used:

Code: Select all

t = TODAY(1);
m = MONTH(t) - 1;
y = YEAR(t);

IF (m = 0);
 m = 12;
 y = y - 1;
ENDIF;

any my element to insert into a subset is defined as, eg:

Code: Select all

SUBSETELEMENTINSERT('ATD_MONTH', 'TestMonth', TRIM(STR(m,2,0)), 1);

and that works ok as well.

Thanks

Andy