Partitioning Ledgers Dimension table

Hi all of you,

ERP version is 2009

I have some implementations with over 200 millions or rows on Ledger Dimension tables.

Would it be suitable to set up partitioning on them?

Thanks for sharing your experience,

Hi Enric,

What do you mean by partitioning?

200 million rows that’s a lot! I’m sure the best thing would be upgrading. Microsoft redesigned the whole dimension table structure, make it much faster and easier to use, way less records needed.

Hi Erik, thanks for your reply. Usual SQL pure side partitioning. I unknown if possible to do something similar in Navision engine

Well I think there are some things possible there, it’s just outside of my expertise. Maybe [mention:e0730ca59f9d4cf7993a3a4833ac5c8e:e9ed411860ed4f2ba0265705b8793d05] has an idea?

Thanks Erik, i am gonna try to catch up him

200 million, that’s all. Work with a client that has 1.2 Billion records in that table. Almost had a heart attack a few months ago when I looked at Table Information and it said there was only a few hundred thousand records there. I realized the counter must be an integer and it had reached the max value and rolled over.

Yes, looking forward to the redesigned dimensions with an upgrade. We figure they’ll have maybe 50 dimension sets. If that.

Even with 1.2 billion records, I’ve never give partitioning a thought.

[mention:adb541dc2d854f69a01006d180dc3937:e9ed411860ed4f2ba0265705b8793d05] which version was this on? I guess one of the older…?
But yes, its a table to had the tendency to grow rapidly…

The database is NAV 5.0 SP1 and is still a very active system. Customer has talked about upgrading for some time but they always seem to find another more important project. They have been using NAV since 2001, but the transaction volume increased around 2007 when they brought manufacturing into the system.

There are also roughly 600 million records across the various ledger tables. So no, they are not over using dimensions.

Of course you can partition the table, but why? Is there a reason you want to do it?