Small and medium business | Business Central, N...

Large number of dimension values

(0) Share

Report

Posted on by Luis Lopes

We have a customer (BC14) with 8 dimensions being 3 of them with more than 500k values each (one has more than a 1.000.000). We are concerned that, at some point, we find performance or capacity issues (or any kind of issues). Is there any recommendation for dealing with such a large number of dimension values? Are there any foreseen issues?

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Matthew Perren 33 on at

Like (0)

Report
Copy link

Link copied!

That is a great question.

Can I ask some in response?

How are they generated? I’m assuming there’s some code that’s generating them automatically.

What’s the event that triggers their creation.

Are they all used? How many dimension sets are there?

Was this reply helpful? Yes No
Kine_ 2,053 on at

Like (0)

Report
Copy link

Link copied!

The question is, if these "Dimensions" are really "dimensions" or some additional attributes. For me, dimensions are "slowly changing values", not values changing with every document or so... (like Business Case No., Project No., Order No. etc.). I will rather add these "fast changing" values as new attributes to needed tables. I cannot imagine how the system will filter for the dimension, because it will need to create list of all matching Dimension Sets. This will kill the system.

Was this reply helpful? Yes No
Luis Lopes on at

Like (0)

Report
Copy link

Link copied!

Hi, sorry for the late reply.

Some of them are generated automatically, others. They are triggered by an action on a custom entity, and they have a life span; that is, they will be used while the status of the entity is active (imagine a loan and the posts regarding payments from that loan, the dimension used in the posts refers to the loan).

Was this reply helpful? Yes No
Luis Lopes on at

Like (0)

Report
Copy link

Link copied!

I believe that when the design was first made, some 10 years ago, they would not expect that there would be such a large numbers of dimensions. The thing is that it is working, and to change it it would have a enormous impact. But we want to take some preventive steps if an issue is foreseen.

Was this reply helpful? Yes No
Guy McKenzie 1,340 on at

Like (0)

Report
Copy link

Link copied!

The dimensions processing is actually very data efficient. It was changed from how it used to work which did impact performance.

Each time a unique combination of dimensions is seen on a transaction, a new dimension set is created. It is the dimension set code that is added to the transaction line.

Any individual transaction line only has the one dimension set id which is a just a link to another table. Whilst you have a huge theoretical potential for combinations of dimensions, these are only sitting in a table when created and pareto analysis suggests you will only ever see a small percentage of those potential combinations ever used.

Whether or not they should be dimensions is a valid question. I am always keen to see people use dimensions more than they typically do as it improves granularity of reporting. However, if those values aren't used in reporting, they possibly shouldn't be dimensions.

Was this reply helpful? Yes No
Suggested answer

IB-29041624-0 1,177 Moderator on at

Like (0)

Report
Copy link

Link copied!

The current design of the dimension handling in BC is very efficient now that we got the datasets.

So from a performance view i would not be worried as long as you keep the implementation in a general good shape when it comes to middeltire servers and SQL server.

But it can anyway be a good thing to check if dimensions is the right thing to use in this case. But I don't think the data you have there now will cause you any performance issues.

Was this reply helpful? Yes No
Luis Lopes on at

Like (0)

Report
Copy link

Link copied!

Thanks Inge. Yes, it is much more efficient than it was, I believe that's why we still didn't get any issues on this. Still the dimension set entry table is getting big (20.5 Million records), and growing...

Was this reply helpful? Yes No