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.

Set Data Categories for fields in PowerPivot

In order for Power View to dynamically create reports based on underlying data, such as location, fields that contain such data must be properly categorized.

Open the Excel Workbook we created in our earlier post. Create a Power View report

For the Olympics data, let’s specify the categories for a few fields.

  • In Power Pivot, select Hosts. Select the NOC_CountryRegion field. From Advanced -> Reporting Properties -> Data Category: click the arrow and select Country/Region from the list of available data categories, as shown in the following screen.


  • Return to Excel, and select the Power View sheet. Expand the Medals table in Power View Fields, and notice that the NOC_CountryRegion field now has a small globe icon beside it. The globe indicates that NOC_CountryRegion contains a geographic location, as shown in the following screen.


We’ll use that geographic location in an upcoming post. It’s time to save your work, review what you’ve learned, and then get ready to dive into the next post.

I will come up with more details in my next post. Till then Stay Tuned, Keep Learning & Practicing.

Create a hierarchy in PowerPivot Data Model

We will be using the Excel workbook we used in our earlier posts. Open the Excel file, you can find the link for download in my earlier posts or from blog Menu.


Most Data Models include data that is inherently hierarchical. The Olympics data is also hierarchical. It’s helpful to understand the Olympics hierarchy, in terms of sports, disciplines, and events.

For each sport, there is one or more associated disciplines (sometimes there are many).

And for each discipline, there is one or more events (again, sometimes there are many events in each discipline).

The following Table illustrates the hierarchy.


In this post we will create two hierarchies within the Olympic data. Then use these hierarchies to see how hierarchies make organizing data easy in PivotTables and in Power View in upcoming posts.

Create a Sport hierarchy

In Power Pivot, switch to Diagram View. Expand the Events table so that you can more easily see all of its fields.


  • Press and hold Ctrl, and click the Sport, Discipline, and Event fields. With those three fields selected, right-click and select Create Hierarchy. A parent hierarchy node, Hierarchy 1, is created at the bottom of the table, and the selected columns are copied under the hierarchy as child nodes. Verify that Sport appears first in the hierarchy, then Discipline, then Event.


  • Double-click the title, Hierarchy1, and type SDE to rename your new hierarchy. You now have a hierarchy that includes Sport, Discipline and Event. Your Events table now looks like the above screen.
  • Still in Diagram View in Power Pivot, select the Hosts table and click the Create Hierarchy button in the table header, as shown in the following screen.


  • An empty hierarchy parent node appears at the bottom of the table.
  • Type Locations as the name for your new hierarchy.
  • There are many ways to add columns to a hierarchy. Drag the Season, City and NOC_CountryRegion fields onto the hierarchy name (in this case, Locations) until the hierarchy name is highlighted, then release to add them.
  • Right-click EditionID and select Add to Hierarchy. Choose Locations.
  • Ensure that your hierarchy child nodes are in order. From top to bottom, the order should be: Season, NOC, City, EditionID. If your child nodes are out of order, simply drag them into the appropriate ordering in the hierarchy. Your table should look like the above screen.

Your Data Model now has hierarchies that can be put to good use in reports. In the upcoming posts we will learn how these hierarchies can make our report creation faster, and more consistent.

Stay tuned for more details, will come up with usage of hierarchy in my upcoming post.

Till then keep learning & practicing.

Extend the Data Model using calculated columns

Recall from my previous post Add a relationship using Diagram View in Power Pivot

Which I have left with below note. I will be continuing from where I left my previous post.

It’s nice when the data in your Data Model has all the fields necessary to create relationships, and mash up data to visualize in Power View or PivotTables. But tables aren’t always so cooperative, so in today’s post will describe how to create a new column, using DAX that can be used to create a relationship between tables.

Open the Olympics Excel sheet which we used in our previous post.

Delete the column EditionID from sheet Medals & Hosts Table/Sheet.

After deleting the Column EditionID from Table/Sheet Select Update All in Ribbon from PowerPivot Tab. This will update the column in Data Model.

Since we are going to learn creating relationship on calculated fields.

In Hosts, we can create a unique calculated column by combining the Edition field (the year of the Olympics event) and the Season field (Summer or Winter). In the Medals table there is also an Edition field and a Season field, so if we create a calculated column in each of those tables that combines the Edition and Season fields, we can establish a relationship between Hosts and Medals. The following screen shows the Hosts table, with its Edition and Season fields selected

Select the Hosts table in Power Pivot. Adjacent to the existing columns is an empty column titled Add Column. Power Pivot provides that column as a placeholder. There are many ways to add a new column to a table in Power Pivot, one of which is to simply select the empty column that has the title Add Column.

In the formula bar, type the following DAX formula. The CONCATENATE function combines two or more fields into one. As you type, AutoComplete helps you type the fully qualified names of columns and tables, and lists the functions that are available. Use tab to select AutoComplete suggestions. You can also just click the column while typing your formula, and Power Pivot inserts the column name into your formula.


When you finish building the formula, press Enter to accept it.

Values are populated for all the rows in the calculated column. If you scroll down through the table, you see that each row is unique – so we’ve successfully created a field that uniquely identifies each row in the Hosts table. Such fields are called a primary key.

Let’s rename the calculated column to EditionID. You can rename any column by double-clicking it, or by right-clicking the column and choosing Rename Column. When completed, the Hosts table in Power Pivot looks like the following screen.

Next let’s create a calculated column in Medals that matches the format of the EditionID column we created in Hosts, so we can create a relationship between them.

When you created a new column, Power Pivot added another placeholder column called Add Column. Next we want to create the EditionID calculated column, so select Add Column. In the formula bar, type the following DAX formula and press Enter.


Rename the column by double-clicking CalculatedColumn1 and typing EditionID.

Next let’s use the calculated columns we created to establish a relationship between Hosts and Medals.

In the Power Pivot window, select Home > View > Diagram View from the ribbon.

Drag the EditionID column in Medals to the EditionID column in Hosts. Power Pivot creates a relationship between the tables based on the EditionID column, and draws a line between the two columns, indicating the relationship.

Now you can see the relationship between Host & Medal table is established on calculated field EditionID in both the tables.

Stay tuned for more details on this topic. I will come up with next step in this series in my upcoming post.

Till then keep learning & practicing.

Add Excel Sheet/Table to the PowerPivot Data Model

Download the Excel file from the link provided in previous post.

Import data using copy and paste from Excel sheet or other source for PowerPivot Data Model.

Open the Workbook:


Select sheet one by one and Add to Data model of PowerPivot.


Make sure all Table/Sheet is added to Data Model of PowerPivot.

I have create a sample Excel File for you which you can down load from the Shared Path using link:

This file have couple of sheets having data related to Olympics which I have compiled from MS Office support site.

You can find the link on the Menu of my blogsite too to access the shared files.

I will be using this file for couple of walkthrough in my upcoming posts.

Import data using copy and paste from Excel sheet or other source for PowerPivot Data Model.

You can copy & Paste data from an Excel workbook, or import data from a table on a web page, or any other source from which we can copy and paste into Excel. In the following steps, you add the data from a table.

  • Insert a new Excel worksheet, and give it the name desired any meaningful name that describes the data.
  • Select and copy the data, including the table headers.
  • In Excel, place your cursor in cell A1 of the above created worksheet and paste the data.
  • Format the data as a table. You press Ctrl + T to format the data as a table, or from HOME > Format as Table. Since the data has headers, select My table has headers in the Create Table window that appears.


  • Name the table. In TABLE TOOLS > DESIGN > Properties locate the Table Name field, and type desired name.


  • Format your data using Excel std. formatting tool.
  • Save the workbook.This file have couple of sheets having data related to Olympics which I have compiled from MS Office support site.
  • You can find the link on the Menu of my blogsite too to access the shared files.
  • I have create a sample Excel File for you which you can down load from the Shared Path using link:

I will be using this file for couple of walkthrough in my upcoming posts.

How to add Filter for data retrieval in PowerPivot Data model.

Recall from previous posts we have added Item Ledger Entry Table to our Data Model of PowerPivot.

Adding more tables to the Data Model using Existing Connection – In PowerPivot

Add relationships to Data Model in PowerPivot

My requirement is to retrieve only records related to Sales.

We will apply filter on Entry Type field to value [1]-[Sales] this way Item Leger Entry Sheet will have only Sales Data.

To do this we will add this filter to the query used for data retrieval.

Select the Sheet for table – Item Ledger Entry from bottom.

Click on Design Tab on Top.

Now From Ribbon choose Table Properties.


Select Query Editor from Switch to.

Add the Filter to your query.

Save your Query.


From Home Tab, Select Refresh from Ribbon.

On Success completion Close the window.

Now your Item Ledger Entry is populated with Fresh data as per the modified Query.

I.e: All Entries which have Entry Type = 1 [Sales].

I will come up with more details in my upcoming post, till then keep practicing and stay tuned for future updates & posts on this topic.

Add relationships to Data Model in PowerPivot

You can systematically create table relationships for all new tables that you import. If you’re sharing the workbook with colleagues, having predefined relationships will be appreciated if they don’t know the data as well as you do.

When creating relationships manually, you will work with two tables at a time.

For each table, you’ll choose columns that tell Excel how to look up/ join related rows in another table.

Recall from my previous posts I will be continuing the example from using same Data Model

PowerPivot Creating a Data Model in Excel 2013

Adding more tables to the Data Model using Existing Connection – In PowerPivot

With your Data model sheet opened
Click on Diagram View in Upper Right Side as highlighted in above screenshot.

Window will switch to Diagram View as shown in above screenshot, showing all the tables available in the Data model.

Click No_ in Customer Table and Drag to Item Ledger Entry Table and release the mouse button pointing to Source No_ field, you will see the Link is Established between these two tables showing the Link via Arrow line.

Repeat above step again between Item->No_ and Item Ledger Entry -> Item No_ fields.

Now we have Established connection between these three tables Customer, Item & Item Ledger Entry.

I will come up with more option on this topic in my next post, till then start practicing and stay tuned.

Adding more tables to the Data Model using Existing Connection – In PowerPivot

For Introduction please see PowerPivot for Excel

Recall from my earlier post we have created a connection to SQL Navision Database in my previous post.

PowerPivot Creating a Data Model in Excel 2013

I will be modifying same connection to include more tables to the Data model.

Click on PowerPivot-> Manage.

From the PowerPivot Sheet click on Existing Connections.

This will open Connection Window Listing all available connection to PowerPivot for this Sheet.

Double Click the Connection in my case Nav2015DB_SQL_Connection.

Select first option as we used in previous post and then Next for next Step.
Select Item & Item Ledger Entry Tables and then Finish to import Data to the Sheet.
On Success Click Close to return to Data Model Sheets.

You will find two new sheets have been added to PowerPivot Sheet.

I will come up with more option on this topic in my next post, till then start practicing and stay tuned for more details & Updates on this topic.

PowerPivot Creating a Data Model in Excel 2013

Before you can create a PivotTable, you’ll need some data. Let’s get some data from the Navision database.

In Excel, open a blank workbook.



You have several options for getting data from no of sources.

Recall from my earlier post where we accessed data using web service in Excel.

Viewing Page Data in Excel Using PowerPivot (OData)

Let us see what other options are available to us.

In today’s Example I will be connecting with SQL but will access Navision 2015 Database.

Follow the Steps below:

Give meaningful name to your connection in my case I have used Nav2015DB_SQL_Connection.

Select the SQL Server to which you wish to connect in my case I have used INDEL-AXT5283N1, basically this is the SQL Server installed on my Laptop.

Specify the Login method, I am using Windows Authentication, if required you can use SQL Server Authentication. In later case you will have to specify your User Name & Password.

Select the Database to Connect, I have selected Demo Database NAV (8-0) Std. Database for Navision 2015.

Click the Test Connection button, If everything is ok you will get Connection Succeed Message, as shown in above screen shot.

Press Next for next step.

In this screen you have option to either import data from Tables and Views or you can Write Query to fetch data for import.

In my case for this example I am importing data from tables.

Select option – Select from a list of tables and views to choose the data to import and Press Next for next step.

I have Selected two Tables Cust. Ledger Entry & Customer.

Select Related Tables button ensure to select if any other tables related to this Table.

In my case not applicable. Click Finish to add data to the Data Model/ Import the data of these two tables.
Select Close to return to Data Sheet imported after this operation.
Here you see your Data for these two tables have been imported in two sheets.

What happened?

You might not have realized it yet, but you’ve just created a data model. It’s created automatically when you import or work with multiple tables simultaneously in the same PivotTable report. The model is mostly transparent in Excel, but you can view and modify it directly using the Power Pivot add-in. In Excel, the presence of a data model is evident when you see a collection of tables in the PivotTable Fields list. There are several ways to create a model.

I will come up with more details in my next post.

Configuring a Data Source in Jet Express

Select Data Source Settings from the Jet Ribbon

Use the Data Source Version drop down button and select “Dynamics NAV 2013 and later”.


Select the Web Service Tab

  • Enter the Server, SOAP Services Port and Instance.
  • If your Web Service is configured to use SSL encryption then check the “Use SSL encryption” box.
  • Pick the Jet_Data_Source codeunit.
  • Pick your default Company.
  • Click Test Connection to ensure connectivity.
  • Click OK.

Checkout Upcoming posts for more information.

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: