Optimize asciioutput

[benoit6369]

Hi everyone,

I'm currently working on TM1 9.4 and will migrate to 10.1.1
As I've an export process that take time (lot of time) I would like to reduce it.

I use asciioutput function base on a cube view. I would like to know if it exist best practise in using this function in version 9.4 or in version 10. Also does it exist in version 10 another way to do it like a native export function.

Any help is appreciate.

Regards.
Benoît.

[qml]

ASCIIOUTPTUT function itself is not your problem. Can you please tell us more about your set-up and results? How many rows of data are you exporting and how long is it taking? Can you let us know if your source view includes consolidations, rule-calculated values and/or zero/empty cells? Where is the process writing to - a local drive or a network drive?

[tomok]

Also does it exist in version 10 another way to do it like a native export function.

Of course there is a built-in function for exporting cube data. Right click on cube in Server Explorer and choose "Export as Text Data". Then follow the dialog box to choose what to export and whether or not you want to include rule-calculated data, consolidated nodes, etc., and whether to cherry pick certain elements of dimension or everything.

[benoit6369]

Thanks for your answer.
Excuse me to gave you so few detail but I would have a general idea.

I'm not able to give the number of row but the size of the exported file is 2.3 Go
It take around 1 hour to do the export
The setup for the view is the following one (some data come from other cube through rules):
ViewExtractSkipRuleValuesSet(Cube_To_Export,'z_Export_Entry',0);
ViewExtractSkipCalcsSet(Cube_To_Export,'z_Export_Entry',0);
ViewExtractSkipZeroesSet(Cube_To_Export,'z_Export_Entry',1);
The process write on a local drive.

To complete, idea of the process is to export data entry in a cube and hightlight, after dimension update, data that have been lost by browsing txt file. If possible, I would not modify the way of doing it by creating a second cube with duplicated dimension.

Also, the idea is to do it by process as it should be done during the night refreshing process. I know that the right click exist.

Thanks for your help again.
Benoît.

[tomok]

It looks like you are dumping the entire contents of the cube, including all rule-calculated values and consolidated nodes, to the file. The only way you'll be able to reduce the amount of time it takes is going to be by reducing the amount of data you are exporting. given that you have enough RAM on your server and you are not running out and hitting a paging file (doubt you are but you haven't mentioned the state of your memory after the export so I can't tell). Do you really need to export the consolidated nodes in order to accomplish the business purpose? I'm pretty sure I could accomplish the task without the rollups, don't know about you.

[qml]

the size of the exported file is 2.3 Go

Can you convert it to something more widely used like, oh I don't know, mega/gigabytes, mebi/gibibytes or dynes per square furlong?

ViewExtractSkipRuleValuesSet(Cube_To_Export,'z_Export_Entry',0);
ViewExtractSkipCalcsSet(Cube_To_Export,'z_Export_Entry',0);

There you go - these two lines are the reason of the 'slow' export. You are basically telling TM1 to calculate all possible combinations of all consolidations in your cube, as well as including all rule-calculated values. You are not just exporting data, you are exporting a gazillion of calculated values that have to be calulated first and then written to the file.

[benoit6369]

Thanks tomok, I will check the state of RAM after the process in order to see if the paging file is use.

To justify the 2 settings which export consolidated and rules i would say that the view is base on "total year" which is consolidation of month. Then in the data tab, I export on one row jan, feb, mar, ...

For example:
Client1 Product1 DataJanuary DataFebruary ...

And, it appears that skipping calulated value and not consolidate one is not working.

Also, sorry for the figures I gave you as size (it is french unit).
Let say 2.3 gigabytes

Rgds.
Benoît.

[tomok]

To justify the 2 settings which export consolidated and rules i would say that the view is base on "total year" which is consolidation of month. Then in the data tab, I export on one row jan, feb, mar, ...
And, it appears that skipping calulated value and not consolidate one is not working.

Of course it's not going to work because your view is based on a consolidated node. What you need to do is change the configuration of your view so that all the other dimensions except for the time dimension (assuming you don't need the rollups in these) are based on subsets that only have the leaf level elements.

[benoit6369]

tomok,

all other dimensions subset contain leaf level elements. Only time dimension selection contain consolidated elements.

I've another question. Do you think that divide view in 2 (one for actual data and one for budget data) then create process with a cmd function that agregate the 2 generated file will be faster as views will be smaller?

What do you think about it?

Regards.
Benoît.

[benoit6369]

To add detail on how the view is created, I have for all other dimension than time dimension the following code

Code: Select all

SubsetDestroy('ACCOUNT', 'z_Export_Entry');
SubsetCreate('ACCOUNT', 'z_Export_Entry');
Nb_ACCOUNT = DimSiz('ACCOUNT');
i=1;
While (i <= Nb_ACCOUNT);
   ACCOUNT = dimnm('ACCOUNT',i);
      if (ellev('ACCOUNT',ACCOUNT)=0);
         SubsetElementInsert('ACCOUNT', 'z_Export_Entry',ACCOUNT,1);
      endif;
   i=i+1;
end;
ViewSubsetAssign(Cube_To_Export,'z_Export_Entry','ACCOUNT', 'z_Export_Entry');

According to a TM1 expert that came in my company, it is more efficient than MDX subset as MDX subset will be re-evaluate for each row in the data tab.

Regards.
Benoît.

[David Usherwood]

What I think you think you want to do is to put each of your monthly values in a column....
to do that you need to test for a full year value in your zero suppressed view....
so you try to build a C level view....
which blows up.
But what you said you are doing is to migrate content from 9.4 to 10.1 .
To do this a simple one column view is much better - you can export the data zero suppressed at N level and read it back in easily.
But....
Why don't you just copy the whole server folder over? TM1 is amazingly tolerant with version up- and down-grades. The only real break is just before 9.4, when Unicode came in. We had a couple of clients upgrade from version 6 last year. What about doing that?

[benoit6369]

Hi David,

thank for your help.
The idea is not to export data from one 9.4 cube to integrate it in the 10.1.1 cube.

The idea is to export data in txt file then , after updating dimension, hightlight data that have been lost.

I speak about version 10.1.1 (has we are currently migrating from 9.4 to 10) in order to know if some new functionnalities have been implemented to optimize/replace asciioutput function.

Regards.
Benoît.

[David Usherwood]

And the answer is....
No
Since the existing function, if used properly, works very well.

[tomok]

I speak about version 10.1.1 (has we are currently migrating from 9.4 to 10) in order to know if some new functionnalities have been implemented to optimize/replace asciioutput function.

Did you remember to enable super-lickity-split mode?

[lotsaram]

The idea is to export data in txt file then , after updating dimension, hightlight data that have been lost.

Well that's an "interesting" approach. TM1 cubes only hold data in leaf cells so I would advise the same as what you have already been told; you only need to export simple leaf cells that contain data, in other words skip consolidations and skip rule values. This will still export all actual data and allow you to do whatever comparison against snapshots to see if any records (that actually matter) are no longer present. (it will also be 100s of times faster and no need to worry about some enhanced, mythical more efficient ASCIIOutput).

However the approach makes me gasp a bit. Does this mean the dimension update processes contain DimensionDeleteAllElements? In which case maybe this isn't the brightest idea. An alternative is to unwind the dimensions and then rebuild hierarchies. This way no dimension elements can be deleted and therefore also no data can be lost. As the last step in the dimension update any leaf elements which no longer have parents (and are therefore deleted or absent in the source) can be added to an "unmapped" or "removed" consolidation. Then all you need to do is check whether there is any data rolling into the unmapped consolidations. There's also the benefit that if tests do show some elements were "removed" that shouldn't have been then the data isn't actually deleted, all you need to do is restore the hierarchies and no harm done. This lets the database do the work for you (in both the data quality check and any "restore" action), sounds like at the moment the design is making the system (and you) work a lot harder than you need to.

[benoit6369]

Did you remember to enable super-lickity-split mode?

Tomok, What do you means by super-lickity-split mode? I did not found anything about that. Maybe my english is not so good to understand subtlety.

lotsaram, thank for your suggestion. The way you indicate was the way we had before with the last Hyperion Essbase system. The problem with it is that it can exist master data difference between TM1 and our Datawarehouse in which we export Budget data from TM1. By example if a product is in TM1 and no more in the datawarehouse (which is alos the source of our TM1 cube (dimension and data)), export can not be upload. As it take one day (or more) to update datawarehouse from local system, it has been decide to do like this. So each time we rebuild a TM1 cube, we use function deleteAllElement.
Before update data we export TM1 data into txt file (the problem of time i mentionned), we created temporary dimension and compare the normal and the temporary dimension to hightlight difference. If some difference are detected, we search in the txt file if it exist some budget data related to. If yes data are duplicate in an other txt file (smaller than the initial one) with a format that allow user to load through a bulk load method. Of course user should have first correct dimension.

Also, we have imagine to create a view base on all month (means to get a txt file with month in row (one row for one month)) and not the total year but we thought that the export file will be enourmous as it already have 2.3 GigaBytes size with the month in column. Also recreate a file with month in column to allow user to relaod lost data could be complex.

Thanks again for your help.

[David Usherwood]

You are exporting a file for a data warehouse.
Why not read your dimension(s) and query the DW for mismatches before you export the file?

[benoit6369]

David, you right. Doing this way you avoid to lost some data.
But we do not want user to face export fail because some product/client/... does not exist anymore in the datawarehouse. especially the day when they had to export budget for the group consolidation. We prefer to anticipate and force them to have correct dimension even if they may lose some data.

We do this because it can take 1 or 2 day to update master data of the central datawarehouse. And as you may know user are not very patient during budget period.

Hope i've been clear enough.

[David Usherwood]

Yes....
That's why I suggested a pre-validation check which will tell them what's missing before they export....

We prefer to anticipate and force them to have correct dimension even if they may lose some data.

So, if your users really want to leave out content which should be in the DW but can't go in because the sluggards who own it can't update it quick enough, then run the query and exclude those elements which won't match on the load. You can even run a report to deliver the value of the excluded content.
Of course, if you used TM1 directly for variance reporting and brought the actuals back from the DW, you'd have a much more usable system. But I imagine the DW police wouldn't like that much

[benoit6369]

Got a new idea how to create the view and want to know your opinion.
Imagine that via a rule, i create a total year by adding Jan + Feb + Mar +...
Then in the view I skip consolidation and not rule (of course).

Do you think it can be faster?

I've few hope in this solution but maybe, some of you have experience it.

I've to say that in the time dimension Month are consolidated in quarter and quarter are consolidated in total year. The idea is to bypass quarter level.