Database Replication – Part IV

This post is in continuation to my earlier post. Please check if you missed.

Database Replication – Part I

Database Replication – Part II

Database Replication – Part III

As committed in this post we will continue to cover practical approach, Next step from last post.

Publishing Data Using Transactional Replication

We will create a transactional publication using SQL Server Management Studio to publish a filtered subset of the Item table in the Nav 2018 sample database. We will also add the SQL Server login used by the Distribution Agent to the publication access list (PAL).

To create a publication and define articles

Connect to the Publisher in SQL Server Management Studio, and then expand the server node.

Expand the Replication folder, right-click the Local Publications folder, and click New Publication.


The Publication Configuration Wizard launches.


On the Publication Database page, select Nav 2018 database, and then click Next.


On the Publication Type page, select Transactional publication, and then click Next.


On the Articles page, expand the Tables node, select the check box for table CRONOUS International Ltd_$Item (dbo). Click Next.


On the Filter Table Rows page, click Add.

In the Add Filter dialog box, click the Replenishment System column, click the right arrow to add the column to the Filter statement WHERE clause of the filter query, and modify the WHERE clause as follows:

WHERE [Replenishment System] = 1

Click OK,


Click Next.


Select the Create a snapshot immediately and keep the snapshot available to initialize subscriptions check box, and click Next.


On the Agent Security page, clear Use the security settings from the Snapshot Agent check box.

Click Security Settings for the Snapshot Agent, enter <Machine_Name>\repl_snapshot in the Process account box, supply the password for this account, and then click OK.


Repeat the previous step to set repl_logreader as the process account for the Log Reader Agent


Click Finish.

On the Complete the Wizard page, type Nav2018ItemTrans in the Publication name box, and click Finish.


After the publication is created, click Close to complete the wizard.


To view the status of snapshot generation

  • Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.

In the Local Publications folder, right-click Nav2018ItemTrans, and then click View Snapshot Agent Status.


The current status of the Snapshot Agent job for the publication is displayed. Verify that the snapshot job has succeeded.


To add the Distribution Agent login to the PAL

  • Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
  • In the Local Publications folder, right-click Nav2018ItemTrans, and then click Properties.
  • The Publication Properties dialog box is displayed.
  • Select the Publication Access List page, and click Add.

In the Add Publication Access dialog box, select <Machine_Name>\repl_distribution and click OK. Click OK.



We will discuss Next step in our upcoming post.



Copying data to-and-fro between Excel & Navision


One of my reader has requested to show him how to export data from Nav Journal to Excel, perform correction and import back to Navision.

So let us see how can we perform this and what are the limitations.

Open the Journal in Navision.

Arrange and show all the Fields that you want to export to Excel on the page.

Fill some sample data. Say single line of Journal, way usually you do.


Now Send to Excel using options shown in below screen.


Data will get Exported to Excel.

Have you noticed something, with the data that got exported yo Excel?

Your 2 Additional Shortcut dimension was not Exported to Excel. Customer Group Code & Area Code, why?

Since these are not the actual fields in the table and it is calculated on Page level, so you will only be able to export Dim-1 & Dim-2 your Global Dimensions which is available as Field in the Table.

Make sure you enter Dimension Values in Capitals in Excel Columns.


Now perform required changes to the Journal data.

Insert New Lines, Delete existing Lines or Edit existing Lines.

Make sure you don’t keep more than 30000 to 40000 lines, until this it works fine if more than this either performance is too slow or Navision gets hang while you copy back your data to Navision. Upto 40000 works fine have tested several time. Depending upon your system performance you can decide how much data will be ok for you.


As we have seen above my 2 additional Dimensions is missing from the exported data. We need to match the columns what we have in our Excel and sequence. So we will hide/remove the additional columns from the Journal to match the sequence from Excel columns before we copy back our data from Excel to Navision.


Select the Rows in excel containing you data excluding header columns and copy.

Return to your Journal and Paste as shown in above screen.


Your modified data is imported back to the Journal in Navision.

Now perform the Journal action way you do normally.


App for Power BI REST APIs for Streaming Data

In this post we will see how to create app to use the Power BI REST APIs for Streaming Data.

Full documentation:

To run this app follow the steps discussed in my previous post: [Real-Time Dashboard Tile & Streaming Dataset– in Power BI]

Summary as below:

  1. Go to
  2. Go to streaming data management page by via new dashboard > Add tile > Custom Streaming Data > manage data
  3. Click “Add streaming dataset”
  4. Select API, then Next, and give your streaming dataset a name
  5. Add a field with name “Customer ID”, type Number
  6. Add a field with name “Customer Name”, type Text
  7. Add a field with name “Sales Value”, type Number
  8. Click “Create”
  9. Copy the “push URL” and paste it as the value of “realTimePushURL” in below app

We will start with new project in Visual Studio.


Create a new Visual C# Console Application.

Open the Program.cs File and write a code as shown below.

This app Uses the WebRequest sample code as documented here:


For your easy here is the code of Program.cs below:


using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Net;

using System.IO;


namespace RealTimeStreaming


class Program


// Paste your own push URL below as obtained from while creating Streaming Dataset and saved in step 9 above

private static string realTimePushURL = “;;

static void Main(string[] args)


while (true) { //Set Infinite Loop



// Declare values that we will be sending

Random r = new Random();

int currentValue = r.Next(0, 100);

String Name = “Dummy Name”;

// Send POST request to the push URL

WebRequest request = WebRequest.Create(realTimePushURL);

request.Method = “POST”;


//Here you will retrieve the data from the source and format as per the request.

//In this example we are sending Random Value generated by above code for testing purpose.

string postData = String.Format(“[{{ \”Customer ID\”: {0}, \”Sales Value\”:{1} }}]”, currentValue,currentValue);

Console.WriteLine(String.Format(“Making POST request with data: {0}”, postData));


// Prepare request for sending

byte[] byteArray = Encoding.UTF8.GetBytes(postData);

request.ContentLength = byteArray.Length;

// Get the request stream.

Stream dataStream = request.GetRequestStream();

// Write the data to the request stream.

dataStream.Write(byteArray, 0, byteArray.Length);

// Close the Stream object.


// Get the response.

WebResponse response = request.GetResponse();

// Display the status.

Console.WriteLine(String.Format(“Service response: {0}”, ((HttpWebResponse)response).StatusCode));

// Get the stream containing content returned by the server.

dataStream = response.GetResponseStream();

// Open the stream using a StreamReader for easy access.

StreamReader reader = new StreamReader(dataStream);

// Read the content.

string responseFromServer = reader.ReadToEnd();

// Display the content.


// Clean up the streams.





catch (Exception ex)




// Wait 5 second before sending


} //Infinite Loop ends here.




Compile and Run the Program.


Leave the Program Running and switch to Power BI dashboard. You will see your newly created Tile in previous post will be displaying the Random Value generated by this program updating every 5 seconds.

That’s all with little tweaking to this program you can fetch your data and send the updated data to your Real Time Streaming Dataset.

That’s end to this post.

I will come up with more details in my upcoming posts.

Till then keep Exploring and learning.



Financial Statements and Data Warehousing (Jet Reports)

Learn how a data warehouse makes creating financial statements from multiple systems fast and easy by storing all of your data in a single place that is optimized for end user reporting.



Assisted Setup (Data Migration) – Madeira

Login to Madeira using your credentials.

From Home page Action Tab Ribbon choose Assisted Setup & Tasks.


From Assisted Setup Window select Migrate Business Data Task to continue.


From Welcome Screen select Next.


Here you can choose source using which you want to Import Data using Assist Edit Button.


Select Import from Excel option and say OK.


Select Download Template file.


Save the File to desired path.


From Settings Select the Templates you wish to Apply to your Data for Customer, Vendor & Item and choose close to proceed.


Below is the format of Template downloaded in above step. Fill in the data to Import.


When we don’t select any template in above step for Customer, Vendor & Item will give confirmation message as below. Choose Yes to proceed.


From next screen Select choose button to specify the Excel file which we prepared in above step using downloaded Template.


This will fetch the data from the file and show you no of records in each table which is going to be imported.


Here if you are familiar with the Rapid Start you can select Advance and perform action accordingly or can skip and proceed to Next Step by selecting Next. Here I am selecting Advance to show the screen.


Confirm with Yes to proceed.


Your Package page will open as shown in above screen, choose Apply Package and handle any import issue with the data to complete the step.


Confirm with Yes to continue.


After processing it will show the status of data, review and fix the issues to continue.


You can drilldown and review the issues and take required steps to proceed with issue fixing and importing of data. Here I choose close to return to previous step of simple import without using Package option.


Select Next to continue with next step.


After processing it will show status of data and errors in data. Say OK to continue.


Here we can choose Show Errors to review the Errors.


We find that in my data most of the issues is related to Gen. Bus. Posting Group, Customer Posting Group, Vendor Posting Group, etc… We can fix these issues later, so just respond Close to continue with Next step.


Our data is now Imported with errors, we will take care of issues with data as reviewed above. Respond Finish to complete this step & Process.


Now you can see this step is marked as completed in Status.

Our data is imported we will access the data and fix the issues accordingly.

I will come up with more details in my upcoming posts.

Till then keep exploring and Learning.



Assisted Setup – Madeira

When you want to explore Project “Madeira” on your own, you can always go back to the Getting Started tour. See in cubes area marked.


At Home page you will find the Assisted Setup & Tasks action. See in Ribbon marked in above screen.

Here you see a list of assisted setup that can help you getting started.


You can migrate data such as vendors, customers, and items from your existing financial system using the Migrate Business Data assisted setup. Once you are done with this piece you are good to go ahead exploring more.

Depending on your need, check if the other assisted setup can help you in any manner.

In the Assisted Setup window, you will find the followings:



Set Up Company: – Creates a new trial company for you to enter data and try out Project “Madeira”. If you went through the Getting Started tour this step is probably already completed.

Migrate Business Data: – Lets you import your existing company data such as vendors, customers, and items from Excel or QuickBooks.

Set up Sales Tax: – Gets you started with default tax groups and assigning tax area codes that you can assign to customers and vendors in order to automatically calculate sales tax in sales or purchase documents.

Set up Email: – Gets you ready for sending email messages directly from sales orders or contacts in Project “Madeira”.

Set up Office Add-Ins: – Sets up the ability to use and launch Project “Madeira” from Outlook.

Set up Email Logging: – Sets up the capability to log email correspondence in Project “Madeira” to follow up on interactions.


Will come up with more details in my upcoming posts.

Till then keep exploring and learning.




Microsoft Power BI – Part X

Continuing from my previous post. Today we will downloading the dataset into Excel from Power BI Online for analysis.

In case you have missed my previous posts here I present the link to all previous posts below.


Microsoft Power BI – Part – I

Introduction to Power BI and Creating Report from Excel Data, Local Files.

Microsoft Power BI – Part – II

Introduction to few Features of Power BI

Microsoft Power BI – Part – III

Power BI Desktop, Creating Dataset & Reports from In Premise Database installation

Microsoft Power BI – Part – IV

Power BI Gateway usage

Microsoft Power BI – Part – V

Scheduling Refresh of Dataset & Report created using In Premise Database

Microsoft Power BI – Part – VI

Power BI Content Pack

Microsoft Power BI – Part – VII

Power BI Mobile App

Microsoft Power BI – Part – VIII

Power BI Content Pack

Microsoft Power BI – Part – IX

Power BI Publisher for Excel


Login to Power BI using your credentials.


Select the Dataset which you wish to analyse, click the three dots on right and from appearing menu choose ANALYZE IN EXCEL.


You will be prompted for Analyse in Excel (preview). If you are running first time please install it.

At the same time you will be prompted for (.odc) MS Office Data Connection file to save/open.

Save and then open the File in Excel.


On opening the file you will be prompted for security concern Enable to allow it.


You will be able to see Pivot Table Fields, containing all of the Tables available in the Dataset.


Now you can play with your data to analyse and create Pivot, Charts and share with others or you can Pin back your result to Power BI Dashboards using concept we used in our previous post.


That’s all for today, I will come up with more features in my future posts.

Till then keep practicing & Learning.


Working with Data Encryption in Navision 2016

You can encrypt data on the Microsoft Dynamics NAV Server by generating new or importing existing encryption keys.

How to: Enable Encryption Keys

You can enable data encryption on the Microsoft Dynamics NAV Server instance that connects to the database.

  • Open the Data Encryption Management Card, as path suggested in below Image.


  • Click on Enable Encryption in Process of Home tab.


  • On Confirmation Dialog box choose Yes.
  • On further Confirmation of Password choose Yes.


  • Enter the Password as defined in Rule as shown in below Image.


  • Upon entering Password choose OK to continue.
  • Key is created with password protected and offered to Export.


  • Choose Save, and save it as Safe Location as you will require it again whenever you restore the database or Import Export the data. Please make sure you also store the password for further reference whenever required.


  • Once Key is generated, you can see that the Encryption Enabled & Encryption Key Exists is checked TRUE.
  • Also in Ribbon you will find the Change Encryption Key, Export Encryption Key & Disable Encryption is now enabled.


  • If Microsoft Dynamics NAV is configured with multiple service tiers (Microsoft Dynamics NAV Server instances), then you must first enable encryption on one server instance, and then export the key and import to other server instances where you enable encryption.

If you export companies and other data that is secured by data encryption, then remember to also export the encryption key so that you can access the data after you import it into another database, for example when you restore a backup. Creating a backup of encrypted data involves the following high-level steps.

  • Export the data from one database.
  • Export the data encryption key.
  • Import the data into another database.
  • Import the data encryption key.

How to: Export and Import Encryption Keys

Note: You cannot generate different keys within one multiple-server instance environment.

Exporting an Encryption Key

You export an encryption key to make a copy of the key or so that it can be imported on another server instance.

Exporting an encryption key stores the encryption key that is used by the current server instance to a file on your computer or network.

To export an encryption key

  • On the message about saving the encryption key, choose Yes.
  • In the Set Password window, enter the password that will protect the exported key file, and then choose OK.


  • In the Export File window, choose Save, choose a safe location where the key file is stored, and then choose Save.



Importing an Encryption Key

You can import an encryption key to a server instance from an encryption key file that was exported from another server instance or saved as a copy when the encryption was enabled.

You cannot import an encryption key on a server instance that already includes an encryption key. In this case, you must change the encryption key instead.

To import an encryption key

  • On the Home tab, in the Process group, choose Import Encryption Key.
  • In the Select a key file to import window, choose the encryption key file, and then choose Open.


  • In the Password window, enter the password that protects the key file, and then choose OK.

Changing an Encryption Key

If a server instance already has an encryption key, then you can replace the current encryption key with an encryption key that is stored in an encryption key file that was exported from another server.

To change an encryption key

  • On the Home tab, in the Process group, choose Change Encryption Key.


  • In the Select a key file to import window, choose the encryption key file, and then choose Open. Same as in above process.
  • In the Password window, enter the password that protects the key file, and then choose OK. Same as in above process.

Upgrading the Data in Navision 2016

Continuing from my earlier post Upgrading the Application Code in Microsoft Dynamics NAV 2016

At end of previous post we have imported the objects and compiled also resolved any conflicts and compilation error. In the same process we have got the list of objects which were having destructive table schema, means the objects which will be having the changes due to which we could lose some data. Scan the objects and if we wish to save those data then we will be requiring Data Upgrade codeunits to handle any such situation.

How to create you can refer to my earlier post Data Upgrade – in Navision 2015 this it still valid for 2016.

Now we have all the upgraded application objects and Data Upgrade codeunits if any required for the upgrade. So we are good to go with Upgrading Data for Old database.

We will follow below steps to continue, make sure you have followed the process for preparation/ converting of database as discussed in my earlier post Upgrade in Microsoft Dynamics NAV 2016

Import the application objects to the converted database

In the development environment, import all the application objects that you extracted in previous step as in my earlier post discussed above in the Microsoft Dynamics NAV 2016 database. This includes the FOB file that contains all the Microsoft Dynamics NAV 2016 objects from the application code upgrade and upgrade toolkit objects if any.

When you import the FOB file, if you experience metadata conflicts, use the Import Worksheet to handle these conflicts.

Finally, on the dialog box for selecting the schema synchronization, set the Synchronize Schema option to Later.

If the upgrade toolkit objects are stored in a separate FOB file, then import the upgrade toolkit FOB file after the application objects are imported.


Run the schema synchronization to synchronize the new tables

To publish the data schema changes of the newly imported tables to the SQL tables, run the Sync. Schema For All Tables – With Validation option from the development environment.

If you are confident that any loss of data you are ok with same you can choose Force option which will drop the data for deleted fields, else use Upgrade Codeunit prepared for same as discussed above.

Or alternatively run the Sync-NavTenant cmdlet from the Microsoft Dynamics NAV 2016 Administration Shell.

Sync-NAVTenant –ServerInstance UpgradedDBfrom2013R2 (My Server Instance Name)


Note this command runs in Administration Shell not in Development Shell as we used in our previous post.

Run the data upgrade process

A data upgrade runs the upgrade toolkit objects, such as upgrade codeunits and upgrade tables, to migrate business data from the old table structure to the new table structure. You can start the data upgrade from the Microsoft Dynamics NAV Development Environment.





Or Alternatively Microsoft Dynamics NAV 2016 Administration Shell.

In the last phase of data upgrade, all companies will be initialized by running codeunit 2 Company Initialization. This is done automatically.

If you want to skip company initialization, then use the Start- NavDataUpgrade cmdlet and set the SkipCompanyIntitialization parameter.


Start-NAVDataUpgrade [-ServerInstance] <String> [[-Tenant] <TenantId> ] [[-FunctionExecutionMode] <FunctionExecutionModeValue> ] [[-ContinueOnError]] [-Force] [-Confirm] [-WhatIf] [ <CommonParameters>]


Specifies whether the Microsoft Dynamics NAV Server instance continues to execute other upgrade functions when an error occurs while executing an upgrade function.

If you do not set this parameter, then when an error occurs, the Microsoft Dynamics NAV Server instance will suspend the data upgrade process. It will cancel the execution of upgrade functions currently in progress and roll back any changes that were applied. Completed functions will not be rolled back.

The process remains in suspended state until you take one of the following actions:

– Fix the problems in the upgrade functions that failed, and then resume the process by using the Resume-NAVDataUpgrade cmdlet. You should not add new upgrade functions at this time because they will be ignored when you resume the process.

– Stop the data upgrade process by using the Stop-NAVDataUpgrade cmdlet. Stopping the process will not roll back changes made by upgrade functions that have already been executed.

If you set this parameter, then when an error occurs, the Microsoft Dynamics NAV Server instance will continue executing other upgrade functions. At the end of the process, you can use the Get-NAVDataUpgrade cmdlet to see the list of failed upgrade functions. Changes that were applied by completed functions will not be rolled back.

When upgrading a large database, you should increase the SQL Command Timeout setting for the Microsoft Dynamics NAV Server instance that connects to the database to avoid timeouts during schema synchronization. The default setting is 30 minutes


Specifies the Microsoft Dynamics NAV Server instance that the application database and the tenant database are mounted against, such as DynamicsNAV90.

You must include the name within single quotation marks.


Specifies the ID of the tenant that you want to synchronize with the application, such as Tenant1.

This parameter is required unless the specified service instance is not configured to run multiple tenants.


Forces the command to run without asking for user confirmation.
Specifies whether the Microsoft Dynamics NAV Server instance executes upgrade functions in series or parallel.
Prompts you for confirmation before running the cmdlet.


Shows what would happen if the cmdlet runs. The cmdlet is not run.

Few Examples of Usage:

[1] PS C:\> Start-NAVDataUpgrade -ServerInstance DynamicsNAV90 –Force

[2] PS C:\> Start-NAVDataUpgrade -ServerInstance DynamicsNAV90 -ContinueOnError –Force

[3] PS C:\> Start-NAVDataUpgrade -ServerInstance DynamicsNAV90 -FunctionExecutionMode Serial –Force

Start-NAVDataUpgrade -ServerInstance UpgradedDBfrom2013R2 –Force


Now what to do?

To learn how to Create Data Upgrade Codeunits you can see my earlier posts:

Data Upgrade Codeunit in Navision 2015 – Part -1

Data Upgrade Codeunit in Navision 2015 – Part -2

These posts are still valid for 2016, you can follow to get you task done at this step resolution to above error.

To resolve above issue I have followed the instruction as suggested in error message, but in real scenario you will definitely will be having such codeunits. Since I have taken the 2013-R2 Std. Database I am having this issue. I have created on Upgrade codeunit with an empty upgrade function as below:

Let us run the above process again.

This time I was able to complete the process successfully.

Delete the upgrade objects

At this point, you have upgraded the database to Microsoft Dynamics NAV 2016. Now, you can delete the upgrade codeunits and upgrade table objects that you imported in above step.

When you delete tables, on the Delete dialog box, set the Synchronize Schema option to Force.

Import upgraded permission sets and permissions by using the Roles and Permissions XMLports

You import the permission sets and permissions XML files according to the following procedure.

To import the permission sets and permissions

  • Delete all permission sets in the database except the SUPER permission set.

In Object Designer, run page 9802 Permission Sets, and then delete the permission sets.

  • Run XMLport 9171 Import/Export Permission Sets to import the permission sets XML file,

In the request page for the XMLport, in the Direction field, choose Import, choose the OK button, and then specify the permission sets XML file.

  • Run XMLport 9172 Import/Export Permissions to import the permission XML file.

In the request page for the XMLport, in the Direction field, choose Import, choose the OK button, and then specify the permissions XML file.

Set the language of the customer database

In the development environment, choose Tools, choose Language, and then select the language of the original customer database.

Add new control add-ins

The database is now fully upgraded and is ready for use. However, you may want to add the new client control add-ins that are included in Microsoft Dynamics NAV 2016. These are not added by the upgrade process. The following client control add-ins are available from the Microsoft Dynamics NAV product media:

  • Microsoft.Dynamics.Nav.Client.BusinessChart
  • Microsoft.Dynamics.Nav.Client.PageReady
  • Microsoft.Dynamics.Nav.Client.PingPong
  • Microsoft.Dynamics.Nav.Client.VideoPlayer
  • Microsoft.Dynamics.Nav.Client.SocialListening

You can add control add-ins in the Control Add-ins window in the Microsoft Dynamics NAV Windows client.

I will come up with more details on this topic in my upcoming posts.

Previous Older Entries


Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: