Page Comparison

With Replica 365 you can replicate Business Central (“BC”) tables to Azure Table Storage (“ATS”). In ATS you can store large amounts of data. You can use ATS to query on this set of data using applications like Excel and PowerBI. ATS scales it’s tables automatically as your demand increases.

Replicating your BC tables to ATS will lower the impact on your BC environment, so that your BC environment is not experiencing a significant load when a lot of data is requested for reports. Because the BC data is already available in ATS, reports will also be loaded faster. From ATS you can also use the data as a source for your BI environment, for example in Azure SQL or in your Data Warehouse.

Use the Replica 365 page to configure the tables that you want to be replicated.

Voorwaarden

1. Replica 365 is succesfully configured in Business Central, using the “Replica 365 Setup page”

2. Azure Table Storage (“ATS”) is configured (more information can be found in the article describing how to setup Replica 365.

Business Central Setup

1. Sign in to Business Central

2. Search for the page “Replica 365” and open this page to start entering the tables you want to replicate.

3. Start by entering the first Table you want to replicate. You can either search by name or table number.

4. Select the appropiate table. Once the table is selected, a couple of informative fields is shown and options to configure the behaviour of the replicate action

	Field	Explanation
a	No. of Records	Number of records (rows) in the table
b	Fields	Number of selected fields in the table. Click on the field to open the Field Selector. Replica supports up to 250 fields for each destination table, due to limitations of ATS. ATS supports a maximum of 255 fields, 5 are needed for system properties. When your table contains more than 250 fields you need to make a subselection within the existing columns or replicate the table in multiple parts.
c	Exclude Flowfields	When flow fields ("calculated fields") are excluded, a replication action will be faster. For large tables, you can choose to replicate the table in multiple parts, each with part of the flow fields.
d	Replication Type	Choose between [Full] and [Changes]. [Full] will replicate the table in full for each replication round. [Changes] will only replicate the changed records compared to the previous replication. This is determined using the attribute "Last Modified Date Time" of the selected table. Warning: when you change the Replication Type from [Changes] to [Full], this will result in an entire new copy of the table “as is” at that moment. This results in losing history in ATS, handle this with care
e	Full Sync Required	When you change the selected fields, for example (de)select Fields, this will trigger a Full Sync the next replication round. This is necessary to keep the number of fields per row in ATS consistent. Warning: This results in losing history in ATS. All records that do not longer exist in BC, will no longer be available in ATS. Handle this with care.
f	Track Deletes	Check this field when you want Records that are deleted in BC to be marked “as deleted” in ATS. If this option is not checked, deleted Records in BC The field “DeletedAt” in ATS will be updated with the DateTime of the actual Deletion in BC (not the DateTIme of updating the record in ATS). Remark: When [Track Deletes] is combined with [Replication Type] = “Full”, this will result in retaining deleted Records in ATS. So even though the Record has been deleted in BC, it will continue to exist in ATS. However, the attribute "DeletedAt" is updated in ATS to mark the Record as being deleted.
g	No. of Deleted Records	Shows the No. of Deleted Records that need to be processed. For each Table that is configured in Replica 365, a log is kept with deleted records in BC. So that these deletes can be implemented in ATS.
h	Status	Shows the Status (“Finished”, “Processing”, “Error”) when the manual Export is triggered.
i	Start	Date/Time of Start of the last Export
j	Duration	Duration of the last Export.
k	Unprocessed Messages	Exports are being processed to ATS in batches. This shows the number of Unprocessed batches.
l	No. of Errors	Number of Errors that occured during the last Export. Click on the number to show more details.

Select fields

For each Table you can select the desired fields you want to Replicate.

1. Use the “Select All” button to select Fields. Replica supports up to 250 fields for each destination table, due to limitations of ATS. ATS supports a maximum of 255 fields, 5 are needed for system properties. When your table contains more than 250 fields you need to make a subselection within the existing columns or replicate the table in multiple parts. Warning: selecting more than 250 fields will result in an Error during Processing to ATS.

2. Use the “Deselect All” button, to unselect all Fields.

3. Use the “Update Fields” button to reload the available Fields. This is necessary when fields have been added to a table, for example after installing a new Extension. New fields are not included by default.

Schedule Exports

To automatically schedule Exports from BC to ATS, you can use BC’s Job Queue.

1. Search for the page “Job Queue Entries”

2. Create a new entry:

   1. Object Type to Run: “Codeunit”

   2. Object ID To Run: 11295563 (bbs_ReplicaSyncTables)

   3. Choose the earliest Start Date/Time, when you want Replica to start exporting to ATS

   4. Choose the Recurrence days

   5. Choose the “No. of Minutes between each Recurrence” (1440 minutes / 60 = every 24 hours)

3. Click on “Set Status to Ready” to schedule the Job Queue for exporting

Image Added

Best practices

PowerBI Reports - Querying on Azure Table Storage

It is possible to connect directly to Azure Table Storage using Microsoft PowerBI.
This is an excellent solution for less complex reports for which a Data Warehouse is unnecessary

1. Open PowerBI and click on “Get Data”

2. Choose “More”

Image Added

3. Search for “Azure Table Storage” and select the option

Image Added

4. Enter the “Base URL” (simular to the Base URL as earlier configured in BC)

Image Added

5. Sign in to the Azure Portal and find your account key, which is needed to connect. More information can be found here. Copy your Account key.

6. Paste the Account key in PowerBI:

Image Added

7. Select the Tables which you would like to include in your report and click “Load”

Image Added

8. Finally, you need to expand the actual “Data columns”. This is necesarry because of the connection method between PowerBI and Azure Table Storage. Click on the “Table view” icon.

Image Added

9. Click “Transform Data”

Image Added

10. The Power Query Editor opens. Click on the icon in the “Content” column.

Image Added

11. Uncheck “Use original column name as prefix”. You can leave this check if needed, but this will result in long(er) column names in PowerBI. Click “OK” to close this screen.

Image Added

12. All columns are shown now. Click on “Close & Apply” to close Power Query Editor.

Image Added

13. You are ready to start building your report!

Image Added