Realistically Using Large Dimensions

[Steve Vincent]

OK so we've heard the stories of people using dimensions with 1m or more elements in them, but realistically how friendly are they to use in a production environment?

I’m testing a model right now that uses an 11 dim cube and I’m expanding one dim from around 26,000 elements to near 300,000. It’s a requirement to test if we can use our existing models as a template for various areas to gather the data we require but at a much finer level of detail. If successful it will replace various bespoke, non-supported systems using Excel or Access with a single, does the same for everyone TM1 system, and reduces their workload significantly.

My initial test failed on my 32bit dev server (as I knew it would) when I tried to load some dummy data and it broke the memory limit. My next test is to break up the models in to programmes and load less data, so this time I’m trying to cut down the large dim so it only has data for one of the programmes rather than all.

I cannot edit the dim via XDI because Excel is unable to handle something that big. So I turned off the properties pane and went to edit the dim via server explorer.

That was nearly and hour ago and it’s still thinking about it. It can’t be possible to run a production system with a dim this kind of size without getting totally naffed off waiting for it to do something. I know I can edit the dim with TI but having seen how badly this is behaving right now, I’ve grave concerns about its usability even if we moved to 64bit.

Does anyone seriously use a model this kind of size with any level of success?

[kpk]

I cannot edit the dim via XDI because Excel is unable to handle something that big. So I turned off the properties pane and went to edit the dim via server explorer.

That was nearly and hour ago and it’s still thinking about it. It can’t be possible to run a production system with a dim this kind of size without getting totally naffed off waiting for it to do something. I know I can edit the dim with TI but having seen how badly this is behaving right now, I’ve grave concerns about its usability even if we moved to 64bit.

Hello!

What kind of TI problem you are referring in connection with dimension maintenance?

Regards,

Peter

[Steve Vincent]

Sorry, badly worded. I don't have an issue with a TI, i have an issue with the normal way i'd maintain a dimension (via the server explorer). In fact i could strip out all the stuff i didn't want in my test using TI and it ran in about 1 sec, so why the blazes does it take over an hour to manually select some elements then press the "delete" key? I know John has expressed the same concerns with populating the GUI, especially around attributes and the properties pane, but i ensured i had that turned off before i even started.

I get the impression that anything done via the GUI is executed in the background one item at a time. So if i selected 75,000 elements and hit delete, it would do them one at a time, recalculating any cubes as it went. I can't think of any other reason as to why the GUI would take so long and TI would be so quick, as TI would lock the whole model until it'd finished, thus only calculating once rather than 1000's of times.

Its only a guess, but this alone is enough for me to suggest we keep the models as small as we can, despite the issues with trying to maintain so many models and keeping their dims in sync.

[Steve Rowe]

Hi Steve,
I don't personally work with the dimension editors or xdi at all when looking after dimensions. I don't have any experience of working with such a large dimension either.

Nearly all the dimensions in the systems I look after are maintained in an Excel table, that is then processed with TI into the system. I've found it much easier than the tools provided with TM1.

For a dimension the size yout talking about you'd need to move to having the standing metadata structure in SQL and read that into TM1 via TI. I would have thought it would be pretty straight forward...

The real problem (again no direct experience) is not how you would maintain the dimension but how the users would be able to use it, especially if you are looking to produce detailed xl worksheets.

Cheers,

[Harvey]

Hey Steve,

I don't want to be presumptuous, but I doubt you'd ever edit a dimension of 300,000 elements manually, either via XDI or Server Explorer.

The company I work with now was part of a project that includes a dimension with millions of elements (prepaid mobile phone numbers) and it is still working in production now, processing hundreds of thousands of transactions a day. I believe it is still considered one of the, if not THE, largest TM1 model in production.

I didn't work on it myself, but I could get some stats for you if you're interested in the details.

Cheers,
Harvey.

[jim wood]

Steve,

We have a 11 dimension cube with one dimension that has around 489k elements within it. It is used as a sandbox cube for consolidating data only. The memory usage is quite high. It currently has roughly 2 years worth of data within it and it takes 10gb. I have pulled data out of it and the respone time when pulling data at a hig level is really slow. Also when expanding the said large dimension there can be quite a bit of lag. If you keep the data sets small the performance isn't bad. (The fact that our Solaris box has really slow CPU's may have something to do with it.)

One other point. We have no rules on the cube to avoid us blowing our memory limit. (Our server memory usage is capped at 40gb)

I hope this helps,

Jim.

[Steve Vincent]

Thanks for the comments, all very helpful. I'm only going on my gut feeling of what people will want in this type of model (trying to pre-empt questions for when they do arise) but i am worried about response times. Editing the dim will mainly be done with TI (i hope) but this particular dim is made up from about 7 different csv files currently. The expanded version is likely to require about 10 seperate sources to build the total hierarchy. The problem that may arise is that some of this data *may* need to be manually added, due to the way the business will define its requirements. It might not, i could possibly work around it but either way it's all stuff to consider.

SQL is sadly not an option. The people who will ultimately be left to look after these models will not be IT literate to that level. Also, we don't have any SQL data sources they would be allowed to administer, apart from the overly complex Oracle Apex which i wish to avoid like the plague. Jim, does your large dimension have any hierarchy or is it fairly flat? One thing i might consider is ditching all the levels and try to keep it as flat as possible, i will need to write rules on this beast so the fewer # of datapoints it has the better.

The data should be sparse enough for it to work, but i won't know until i try. Speed really will be the key tho - if its massively slow then they'll get turned off by it and we'll end up with a nasty big failure on our hands. Thats an avoid-at-all-costs issue, even if it means not doing it at all...

[ScottW]

Hi Steve,

Sorry if this is a bit late just haven't had time to post anything lately.

The largest dims we have in production would be a couple of SKU dims used in sales cubes for some retail clients with somewhere between 100K - 200K elements. Once consolidations are accounted for probably around double this in terms of the total elements. No real problems maintaining these or working with these. Recently I have done a couple of POCs replacing Powerplay with TM1 because Powerplay couldn't handle the dimension and cube size (very slow performance) - these were for dims with about 400K - 600K elements. TM1 handled it pretty well

The only way to maintain or edit dimensions of this size is with TI. It would be folly to ever attempt to manually edit a large dimension. IMO dimension editor struggles even with moderately sized dimensions of more than 1000 elements. The other big watch out is that with large dimensions the user base needs to be better educated as you have to strictly enforce a rule of "the properties pane must be off" when browsing a large dim.

Performance when calculating views generally good, but watch out for rules if you have a lot of dimensionality. Summary cubes for most reporting and drilling back to the mega cube when required helps a lot, as does splitting out the large dim into smaller summary dims (if possible depending on the model.) Optimising dimension order in cubes using large dims can also have a huge impact on performance.