Store Dimension with DataPerCompany = No

Yachty

Hi there, a client with several companies and similar dimensions reviews storing dimensional data with the DataPerCompany = No setting. This would even increase the effectiveness of the dimension set "recycling".
Has someone tried this before or can see problems with it? My first testing showed no issues at all.

osimren

It sounds a bit dangerous to me.

Tables with DataPerCompany set to No is something that I normally recommend to stay away from, it causes issues when moving companies between databases, renaming records, deleting records, etc..

It is better to suggest some kind of synchronization.

kriki

I concur. And with the NAV2013+ dimensions system, you really don't need to use DataPerCompany=No to make things faster or to save some space in the DB.

You're taking a risk to gain probably nothing from. Not worth it! The more standard you can stay, the better it is.

Yachty

osimren wrote: »

It sounds a bit dangerous to me.

Tables with DataPerCompany set to No is something that I normally recommend to stay away from, it causes issues when moving companies between databases, renaming records, deleting records, etc..

It is better to suggest some kind of synchronization.

I do agree that it sounds dangerous, but by looking at it... OnDelete Trigger surely needs attention as always if one switches a table to DataPerCompany = No. Rename is not a problem anymore as there is the dimension value id and dimension set entries can not be renamed nor deleted.

Yachty

kriki wrote: »

I concur. And with the NAV2013+ dimensions system, you really don't need to use DataPerCompany=No to make things faster or to save some space in the DB.

You're taking a risk to gain probably nothing from. Not worth it! The more standard you can stay, the better it is.

It is NAV 2016 and the gain would be that > 15 companies would recycle dimension sets. Fear is not the answer... ;o)

ppavuk

I'd better avoid something like this. Who cares on dimension set recycling? It is a small table anyways, not worth any attention...

Sebastien_Konsbruck

Hi Jotti,

Do you want to store used dimensions in a DataPerCompany=Yes table?

In this case, I can only repeat the warning of the others: don't do it.
Although I don't think you'll have technical issues (test it on a demo database to be sure), it most probably will cause you a lot of trouble during the next upgrade.

This article explains you how used dimensions are saved today in NAV:
(https://msdynamicsnavashwinitripathi.wordpress.com/2015/10/30/design-overview-of-dimension-handling-in-navision-2016/)
You will see that the storage is already optimized, so don't worry about space...

Or do you only want to store available dimension values in DataPerCompany=Yes table?

In this case, I also recommend you not to change the table property

Either by using hooks, events or a NAS, you can trigger a synchronization between the dimension value tables of a selected company group. There are a few methods that I've seen and used but don't consider this as an exhaustive list. You can also search for ISV that already offer out of the box solutions...

If you are interested in developping a synchronization tool for your organization, I can provide you with more details about each of the three below mentioned methods.

1) Create a job that checks for changes
2) Define a master data company
3) Synchronize in real-time between company groups

General considerations

- At one point the synchronization feature must be triggered. Implementing it into every table that one day might or might not be synchronized is a lot of work and you change a lot of objects. Consider using global events (https://nav-magno.be/2016/01/eventing-an-overview/)
- Using global triggers is okay if performance is not too much decreased so the method to identify whether something should be synchronized or not must be very precise and efficient. Don't search for text strings, or things like that.
- The CHANGECOMPANY Function can be tricky to use properly.
- CHANGECOMPANY respects the user's permission that has triggered the function
CHANGECOMPANY executes the triggers of the objects in the current company, not in the targeted one. CHANGECOMPANY is only cleared through a new CHANGECOMPANY call or the CLEAR Function not through RESET or others.

Yachty

ppavuk wrote: »

I'd better avoid something like this. Who cares on dimension set recycling? It is a small table anyways, not worth any attention...

Hi ppavuk, it's not so much about storage, but about performance and a hugh volume of intercompany transactions. With millions of records, it could make a remarkable difference...

Yachty

Sebastien_Konsbruck wrote: »

Hi Jotti,

Do you want to store used dimensions in a DataPerCompany=Yes table?

In this case, I can only repeat the warning of the others: don't do it.
Although I don't think you'll have technical issues (test it on a demo database to be sure), it most probably will cause you a lot of trouble during the next upgrade.

This article explains you how used dimensions are saved today in NAV:
(https://msdynamicsnavashwinitripathi.wordpress.com/2015/10/30/design-overview-of-dimension-handling-in-navision-2016/)
You will see that the storage is already optimized, so don't worry about space...

Or do you only want to store available dimension values in DataPerCompany=Yes table?

In this case, I also recommend you not to change the table property

Either by using hooks, events or a NAS, you can trigger a synchronization between the dimension value tables of a selected company group. There are a few methods that I've seen and used but don't consider this as an exhaustive list. You can also search for ISV that already offer out of the box solutions...

If you are interested in developping a synchronization tool for your organization, I can provide you with more details about each of the three below mentioned methods.

1) Create a job that checks for changes
2) Define a master data company
3) Synchronize in real-time between company groups

General considerations

- At one point the synchronization feature must be triggered. Implementing it into every table that one day might or might not be synchronized is a lot of work and you change a lot of objects. Consider using global events (https://nav-magno.be/2016/01/eventing-an-overview/)
- Using global triggers is okay if performance is not too much decreased so the method to identify whether something should be synchronized or not must be very precise and efficient. Don't search for text strings, or things like that.
- The CHANGECOMPANY Function can be tricky to use properly.
- CHANGECOMPANY respects the user's permission that has triggered the function
CHANGECOMPANY executes the triggers of the objects in the current company, not in the targeted one. CHANGECOMPANY is only cleared through a new CHANGECOMPANY call or the CLEAR Function not through RESET or others.

Hi Sebastien,
thanks for your very much appreciated feed back and information, which I will go through today. You mentioned expected issues with upcoming updates. What exactly do you have in mind there?

Sebastien_Konsbruck

Hi,

In two previous migrations I had trouble with DataPerCompany=No tables. I had to change them to DataPerCompany=Yes which led to some changes in business logic for the client. I think the problem were the lots of table relations.

There are more reasons why this change can cause more problems than it solves.

1) You need to exactly know the table relations between the table you want to set to DataPerCompany=No and the other related tables and consider which ones must be DataPerCompany=No as a indirect consequence of the property change of the first table.

Example: You set the Contact table to No but leave the territory code DataPerCompany=Yes. Your lookup won't work - if you have implemented business logic based on the content of the field "Territory Code", it might fail.

2) There are possible issues resulting from business logic. How can you be sure that all steps are executed in the correct order in all companies? (Example: https://community.dynamics.com/nav/f/34/t/150587 (I hope the guy made a backup before applying his 20 table property change... ).

3) Whenever you change such basic behaviour of NAV you have to keep in mind that your system considerably changes from MS standard. Not sure whether MS will help you if you have a ticket related to this matter.

Edit: 4) Imagine your company acquires a new business next year January but this business will work completely different from the one you are doing today... You'd have to change everything back to DataPerCompany=Yes whereas the synchronization tool would allow you with a simple change of setup to stop synchronizing for one company but continue for the rest.

Conclusion: I've not tried setting the Dimension Set Tree Node and Dimension Set Entry table to DataPerCompany=No but my guess is sooner or later it will cause trouble.

Good luck in any case

Best regards,
Sebastien

ppavuk

Jotti_ wrote: »

ppavuk wrote: »

I'd better avoid something like this. Who cares on dimension set recycling? It is a small table anyways, not worth any attention...

Hi ppavuk, it's not so much about storage, but about performance and a hugh volume of intercompany transactions. With millions of records, it could make a remarkable difference...

it won't. it only stores set id, so for thousands of ledger entries there could be same set...

Sebastien_Konsbruck

Generally speaking there is no reasonable factor to my knowledge to do such a change apart from having the same values available in each company which leads back to the initial suggestion of synchronizing values only.

Yachty

I really appreciate yout input, Thx! We also considered the risk to be higher than the performance gain and dropped the idea...