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:
Also you can specify from which companies you want to query the data:
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.
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.
- 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.
- 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:
Please note that:
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
- 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.
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.
- 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.
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:
Data to share as cross company:
In your underlying query, you need insert a new field: DataAreaId
Add the same in the view. Rename the field as ‘LegalEntity’. Build the visual studio project → in order to reproduce the view in SQL.
In your entity, remove the ‘PrimaryCompanyContext’. Add the newly created view in your entity. Build/synch the project now.
Next time when you call the entity from the browser it will get:
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.