Dimension Element Length Impact on Performance

[ardi]

Hi Guys,

I have a question regarding best practice on dimension element length. We have a large dimension ( with around 2 million elements ) and in order to get a unique element, we have to concatenate a few database columns. That would make the dimension element length with around 30 characters.
In the database, also there is a single column ( type of surrogate key column ), which is unique and had a length of 10 characters. The problem is that thus surrogate key, has no business meaning and we cannot expose it to any user interface, so we would have to use aliases instead, and then the option we have it to concatenate the business fields to create unique aliases. I was trying to look if there is any documentation or best practice recommendation regarding this but i cannot find any.

What do you think would be the best solution?

Thank you

[Alan Kirk]

Hi Guys,

I have a question regarding best practice on dimension element length. We have a large dimension ( with around 2 million elements ) and in order to get a unique element, we have to concatenate a few database columns. That would make the dimension element length with around 30 characters.
In the database, also there is a single column ( type of surrogate key column ), which is unique and had a length of 10 characters. The problem is that thus surrogate key, has no business meaning and we cannot expose it to any user interface, so we would have to use aliases instead, and then the option we have it to concatenate the business fields to create unique aliases. I was trying to look if there is any documentation or best practice recommendation regarding this but i cannot find any.

What do you think would be the best solution?

There are pluses and minuses whichever way you go here.

To be honest I wouldn't be averse to using the surrogate key. I know all of the reasons for not exposing a surrogate key to an end user, but a lot of GL-based systems use account combination segment codes that have no "real world" meaning either; not even the meaning of falling neatly within a sequential structure. (That's probably more true with profit centres than with accounts, and with profit centre hierarchies which have been around for a while.) So the use of a kinda-sorta surrogate key, which most people won't know the meaning of off the top of their head without checking the description, isn't without precedent. I'd probably think twice about it if it was a GUID simply because those things are seriously ugly and user-unappealing, but if it's 10 characters I'm assuming that it's not.

I don't think that the 30 character element name length would be a performance killer, but the thing is, is that group of values really going to be more meaningful than the surrogate key would be anyway? If not it's going to simplify (and improve the speed of) your metadata maintenance if you can pull it from a single column instead of multiple columns. (Though you could of course define the data source as a view on the database side and do the concatenation within that view, again giving you a single column to pull from, so that's not a strong argument either way.) The users need rarely if ever see the surrogate key if you have the default subset for the dimension set with the descriptive alias on in any case.

Either way will work; I'd be inclined to go for the simplest and fastest. Others may have different opinions but I don't think any of us could say that one way is the unequivocal right way, which is probably why you didn't find anything on this subject.

[lotsaram]

A dimension with 2 million elements is possible but will eat its fair share of resources. I wouldn't think that the difference between element names with 10 characters and 30 would be that significant both in terms of memory and performance but one thing to consider would be the resources required for the alias and whether these could be saved if the 30 character name was used as opposed to the 10 character surrogate key.

Also more than worth considering is how exactly and for what exactly you need such a dimension. Is it for lookup purposes only or for use in data cubes?

[ardi]

Hi Guys,

Thank you for your answers. In the User Interfaces we need to show the 30-character key, which is the only key that business users would recognize. The other key is created from the ETL process, and is completely meaningless for users. The issue is that we have implemented a HyperLink function on each row of the weebsheet to give users the ability to select a row and open in it in another websheet to make adjustment. And we need to pass many of the dimension keys to the hyperlink URL, which has a limit of 256 characters. So if we use the 30-character key as dimension element name, then we exceed that 256 length of the HyperLink URL.

The dimension with 2 million elements is being used in the main cube and unfortunately we need this dimension.

Thanks again
Ardi

[paulsimon]

Hi Ardi

Rather than having the 2million element dimension, have you considered drill thru or at least have separate detail and summary cubes?

Regards

Paul Simon

[ardi]

Hi Paul,

We do have summary cubes and detailed cubes, but the big dimension is part of a cube that is used in data entry websheets, unfortunately this is the requirement, users want to be able to adjust data at the lowest level in order to reconcile to GL. Also there are requirements to allocate some $ amounts from a very aggegated level to the lowest level, and one thing we did is we developed TI processes to perform these allocations rather than through rules. Also most of calculations ( expect some C: Calculations, mainly Rates ) are implemented through TIs.

Any other recommendation on how to improve performance would be much appreciated

Thanks again
Ardi