Efficient Cross Company Data Sharing in D365

With greater number of transactions piling up in the system the inevitable challenge that come are a. speed of data fetching, b. calculations that are based on tables that have enormous data in them c. challenges get all the more serious when you plan to send the end result as an oData payload result.

Imagine a condition when you are creating a report of payment buckets of customers (customer ageing) and you have to link CUSTTABLE with CUSTTRANS. Conditions worsens when you have for each customer itself a huge number of transactions. And then you need to pull customer results from just not from one Legal entity, but from multiple companies.

Your query ideally looks like this:

Select CrossCompany sum(AmountMST) from custTrans exists join custTable where custTable.AccountNum ==custTrans.AccountNum && custTrans.TransDate >= <_some_Date_Range>

Also you can specify from which companies you want to query the data:

Container companyRange = [‘LE1’, ‘LE2’, ‘LE3’]; Select CrossCompany:companyRange sum(AmountMST) from custTrans exists join custTable where custTable.AccountNum ==custTrans.AccountNum && custTrans.TransDate >= <_some_Date_Range>

In either case when the number of transactions rise very high, inevitably the compiler is sure to fall under the clasps of performance issues.

Luckily there are efficient ways to handle such kind of situations.

Using views

Views can offer you a pre-calculated tool of data fetching/mining that involves a high degree of SQL performance and concepts. Essentially when you build your solution( and provided if you have kept your project area of VS → Synchronize database on build → true), the view gets reflected/created in SQL too.

Steps

Step 1:

• Right click on your VS project → Add → New item → Under the Data Model tab → Query
• Give a name. Eg. TestQuery
• Set the ‘Allow Cross-Company’ property as ‘True’
• Add a new data source → select CustTrans as table
• Select Dynamics Fields as ‘no’
• Select Fields → right click → add new field → AccountNum
• Select Fields → right click → add new field → TransDate
• Select fields → right click → add new Sum field → select AmountMST from properties
• Go back to CustTrans node → add data source → select CustTable as datasource
• Join mode ‘Exists join’

Save the Project. Do a build.

Making the ‘All Cross-company’ makes it to fetch the data from across all the companies.

Step 2:

• Right-click on your VS project → Add →New Item → under the Data Model → Views
• Give a proper name: e.g. TestView1
• Step 2: drag and drop the TestQuery under the metadata node.
• Come to ‘Fields’ node.
• Drag and drop the fields from your query into your fields’ section.
• Come to the view metadata → ‘Allow cross company’ → set to yes
• Save and build the project.

This will create the view at the backend. Refresh your SQL Server → the SQL_DB (ideally it’s located as AxDB) → open the views node → you can see the view being created as:

Create or alter view TestView1 As Select T1.AccountNum as Accountnum, T1.DataAreaId as LegalEntity, SUM(T1.AmountMST) as Balance from CustTrans T1 where exists(select T2. AccountNum from CustTable T2 where T2.AccountNum == T1.AccountNum) group by T1.AccountNum, Order by T1.DataAreaId

Please note that:

• a) We didn’t include the ‘DataAreaId’ column in the fields’ list, even then it has been added in the view
• b) The ‘group by’ clause has been automatically been added owing to the fact that we are using a sum(AmountMST)
• c) When you run this view – automatically, the data is fetched regardless of the data entities.

• AccountNum	Balance	LegalEntity
  C1	300	LE1
  C2	400	LE1
  C3	250	LE1
  C4	600	LE1
  C99	820	LE1
  C56	700	LE2
  C66	300	LE2
  C68	230	LE2

• d) Also try to provide as many indexes as possible so as to make the performance considerations.

Issues:

Views do have certain drawback too while considering the cross company share

• The root element table in the underlying query must be a shared table so as to make the records be available cross company
• By definition, views are never cross company. While viewing the view from table browser, it always filters records as per the logged on company
• Taking any other field other than dataAreaId for cross company, won’t be considered (eg. RecVersion, etc.)
• These apart, normal SQL views are different than X++ views – creating views in SQL won’t be considered/imported in Ax.

Using entities as an alternate procedure

A data entity is a tantamount of what it used to be as the DIXF in Ax 2012, though with much larger and far reaching offerings:

• Exposing data as JSON payloads
• Using Open Data(oData) protocol to handle data through oData based system architecture/ classes
• Faster processing of data that are minimal to load, exchange and read/write/update/delete – based on RESTful commands: GET, POST, DELETE, PUT, etc.
• Security implemented by various protocols(oAuth2.0, etc.)
• It can now be used for voluminous data uploads using entity framework – a very useful way of various data import for any implementation.
• It supports various kinds of data exchanges: excel, CSV, XML, logic app based calls, MS flows, etc.
• Capable of importing single entity based or aggregate or multiple data source based operations
• Automatic ‘set-based operation’ making a high volume import to be split up into a number of parallel processing threads (based on MAX Degree of Parallelism of the system).

Entities to support cross company data

Step 1

• On your VS → right click on your project node → add → new item → select entity from Data Models → give a name: TestEntity1
• A wizard will start which will let you select the underlying table
• Do the necessary mappings for tables →
  • Enter/select the primary data source
  • Enter/select category → this will have a functional parametric result.
  • Enter staging table name
  • Enter associate view that will be created
  • Enter privileges that will be created
  • Give a proper collection name: TestEntity1
  • Check on as ‘Public’ API → external sources can call your entity simply as: https://yourD365Client.com/data/TestEntity1
  • Click next to continue.

Step 2

Select/Enter/review your table → staging table → target entity mapping. You can specify the various labels of the fields you will be using. You can also tick on/off any field in order to enable/disable its availability. You can also select if a particular field is mandatory/not.

Also this step helps to select/enter your own custom help texts.

When you Click finish: it will create the entity.

A lot many things you need to change/alter after this.

Step 3

• Select set-based operation as true: if required
• Alter public collection name – if on build, it failed saying ‘The public collection name **** already exists’
• Provide additional keys if required
• Turn off ‘Data Management’ if your intention to create the entity was just data API exposure.

Note the ‘Primary company context’. By default it is set as: DataAreaId. When you say this, it by default restricts the data to be exposed as per company only.

TestEntity 1 ent1; While select * from ent1 { info(ent1.AccountNum); } This returns data from the logged on company only. While select: crossCompany from ent1 { info(ent1.AccountNum); }

This returns data from all the companies.

If I am at ‘USMF’ company, and I order this entity from the browser: https://yourD365Client.com/data/TestEntity1, I will get the following JSON response:

“Values”: [ { “DataAreaId”: “USMF”, “AccountNum”: “Cust1”, “Balance”: 122 } , { “DataAreaId”: “USMF”, “AccountNum”: “Cust2”, “Balance”: 150 } , { “DataAreaId”: “USMF”, “AccountNum”: “Cust3”, “Balance”: 180 } ]

Data to share as cross company:

Step 1:

In your underlying query, you need insert a new field: DataAreaId

Step 2:

Add the same in the view. Rename the field as ‘LegalEntity’. Build the visual studio project → in order to reproduce the view in SQL.

Step 3:

In your entity, remove the ‘PrimaryCompanyContext’. Add the newly created view in your entity. Build/synch the project now.

Step :

Next time when you call the entity from the browser it will get:

“Values” : [ { “DataAreaId” : “USMF”, “AccountNum”: “Cust1”, “Balance”: 122 }, { “DataAreaId” : “USMF”, “AccountNum”: “Cust2”, “Balance”: 150 }, { “DataAreaId” : “USMF”, “AccountNum”: “Cust3”, “Balance”: 180 }, { “DataAreaId” : “INMF”, “AccountNum”: “Cust4”, “Balance”: 200 }, { “DataAreaId” : “DMF”, “AccountNum”: “Cust5”, “Balance”: 600 } ]

Conclusion

As we mention above discussed about cross company data sharing between companies in Dynamics 365 for finance and operations. These data sharing platform helps increase the skill levels for users and get the most of their business application, opening the door to development and automation for every user.