TI Speed in Dimension creation

gaohua5201

All,

Using 9.5.2 FP2 I have come across something a little odd.

I have a TI process that is, from a flat file building, six broadly similar copies of the same dimension.

It??s fairly complicated several while loops and many attributes being sprayed around.

I??ve been experiencing some differences in performance depending on how I was running the code.

If I run the code with a DimensionDeleteAllElements against each of the dimensions I??m building then the code runs in about 55 seconds.

If I run the code without a DimensionDeleteAllElements then the code takes 3.5 minutes to run.

All other code is identical.

Even stranger it is the attribute update that is where the performance hit is rather than on the meta data tab. (Logging is forced off on the attribute cubes in the TI.)

I??m at a loss to explain this and can??t figure away to spoof my way round. Anyone have a clue what might be going on under the hood? It all seems rather counter intuitive to me...

  

I refuse to use DimensionDeleteAllElements in TIs that are going to get released into the wild so I guess I??ll have to take the performance hit??br />
Cheers,
Steve

yseen

When you run the TI without the DimensionDeleteAllElements have you manually deleted the elements before hand?

If you are running the process to update dims that already contain the elements are you presumably running some sort of reference to see if they are already there (e.g. a Dimix)?

cttjs

Hi,

No I  don't delete the elements manually.

Yes I do a Dimix before I do an element insert but.
   1. I would expect the TI to run slower on the MD tab since my Dimix fails more often and I'm doing more element inserts, I've not tested explcitly but
   2.  It's the attribute adding that runs slowly on the D tab if I don't do a dimension delete all elements.  Again there is a Dimix here, whicever way I run it.  So with the delete in place I'm running the same number of Dimix request but writing more attributes and getting much faster performance.... hence my confusion...

Cheers,

pakyarms

Steve Rowe wrote:Hi,

No I  don't delete the elements manually.

Yes I do a Dimix before I do an element insert but.

I wouldn't bother with that unless your objective is to flag duplicates; otherwise it's a waste of processor cycles. If the element exists then DimensionElementInsert just ignores it.

Steve Rowe wrote:   1. I would expect the TI to run slower on the MD tab since my Dimix fails more often and I'm doing more element inserts, I've not tested explcitly but
   2.  It's the attribute adding that runs slowly on the D tab if I don't do a dimension delete all elements.  Again there is a Dimix here, whicever way I run it.  So with the delete in place I'm running the same number of Dimix request but writing more attributes and getting much faster performance.... hence my confusion...

This is just a WAG[1], but are those attributes aliases? If so then somewhere down in the bowels of the functions that the TI calls there would need to be some sort of routine which validates uniqueness (across all aliases as well as the principal name). In all probability this involves a scan of the dimension and its aliases. If you blow away all of the elements and recreate in metadata then the only things it needs to check are the principal names and the aliases which had already been added by the data tab of that process; all of the other values will be known to be null. If you don't, then there are probably preexisting aliases that it needs to check for every element. That would take substantially longer since there's the potential for a shedload of string comparisons to need to be done. (Probably even longer than normal since TM1 is mostly case and space insensitive, so the comparison wouldn't be a simple binary one.)

Of course if they're plain ol' text or numeric attributes, that won't account for it.

[1] Wild A$$ed Guess

chenbaolei

Another WAG: perhaps it has something to do with validating the rules as it goes.  I have seen an occasional error where a change to the dimension fails because the rule doesn't like it so obviously TI does check with the rules as it goes.  It may be that starting from a clean slate enables this to be done faster.

Perhaps try removing the rules in the cubes that this dimension uses and see if that makes any difference.

赏狗网

I ran into something similar recently updating a dimension from an ODBC source.

Alan Kirk wrote:This is just a WAG[1], but are those attributes aliases? If so then somewhere down in the bowels of the functions that the TI calls there would need to be some sort of routine which validates uniqueness (across all aliases as well as the principal name). In all probability this involves a scan of the dimension and its aliases.

This is roughly what I concluded. Updating just a single alias caused the data tab to run approximately 100 times slower on a dimension of ~50000 elements. Interestingly, I managed to work around it by setting a string attribute via TI which was referenced in a rule to populate the alias. I was a little bit hesitant about this approach but haven't had any problems.

Martin Ryan wrote:Perhaps try removing the rules in the cubes that this dimension uses and see if that makes any difference.

I did try this actually but it had no effect. I'd considered whether it might be that I was somehow triggering a load of feeders too but this didn't seem to be the case.

碧·染

Good idea on the alias I'll try and narrow it down a bit further.

asutcliffe wrote
This is roughly what I concluded. Updating just a single alias caused the data tab to run approximately 100 times slower on a dimension of ~50000 elements. Interestingly, I managed to work around it by setting a string attribute via TI which was referenced in a rule to populate the alias. I was a little bit hesitant about this approach but haven't had any problems.

Thanks for taking the time out to confirm Alan's WAG(!), not sure I like the ruled approach since strings aren't cached and I'd be concerned that I'm swapping a dimension build performance issue for a small lag in alias referencing.  If I have the time to figure an alternative approach I'll let you know.

Deleting and reinserting the alias is a non-starter as it will break an subsets that reference the alias.  I guess the other thing to try is to VZO the alias before I start writing to them.  This should mean that the testing process is "like" it is when the dimension has jsut been created.

In tracking down that it was the dimension delete that was causing the issue I'd already deleted all the cubes on the server so there are no rules.

Alan Kirk wrote:
[quote]Steve Rowe wrote:
Hi,

No I don't delete the elements manually.

Yes I do a Dimix before I do an element insert but.

I wouldn't bother with that unless your objective is to flag duplicates; otherwise it's a waste of processor cycles. If the element exists then DimensionElementInsert just ignores it.


[/quote]

I've done some testing on this and doing a Dimix is faster than doing DimensionElementInsert, even if the element already exists.  At a guess I would say that a DimensionElementInsert is not ignored for elements that already exist its just that as a user you can't tell the difference between re-insertion and ignore for an element that exists.  Looking at a dimension via Dimix vs updating a dimension has a significant time difference.

In the special case where you build a dimension from scratch then the Dimix is wasted I agree, in the more general case where you are applying small incremental updates there is a performance benefit from doing a Dimix before your insert.

Anyway, thanks to the forum as ever, sounds like I have an answer.  I'll post my findings.

Cheers,

玩美123

Hi Steve,

The downside of using DIMIX in the Meta Data is, it won't update the hierarchy if elements are moving between different consolidations, thereby chances are you will end up either:
1) Leaving the existing element orpahaned, assuming you have unwinded the consolidation where it rolls up to
2) Having the same element rolling in two different places in the absence of unwind

Unfortunately I won't be able to provide any solution for your problem other than perhaps trying loading the attrs through and seperate TI, if not wanted to just share my passing thoughts on the issue.