Advertisements

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.
Upgrade2016-29

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.
Upgrade2016-30

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)

Upgrade2016-31

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.
Upgrade2016-32

Upgrade2016-33

Upgrade2016-34

Upgrade2016-35

Ooops………….

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.

Syntax:

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

Parameters
-ContinueOnError

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

-ServerInstance<String>

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.

-Tenant<TenantId>

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.

-Force

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

-WhatIf

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
Upgrade2016-36

Start-NAVDataUpgrade -ServerInstance UpgradedDBfrom2013R2 –Force

Ooops………….

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:
Upgrade2016-37

Let us run the above process again.
Upgrade2016-38

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.

Advertisements

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.

ExcelData-37

  • 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.

ExcelData-38

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.

SharePath

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.

ExcelData-13

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.

ExcelData-14

  • 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.

ExcelData-15

  • 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.

ExcelData-16

  • 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.

ExcelData-7
Delete the column EditionID from sheet Medals & Hosts Table/Sheet.
ExcelData-8

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
ExcelData-9

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.

=CONCATENATE([Edition],[Season])

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.
ExcelData-10

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.

=CONCATENATE([Year],[Season])

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

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.
ExcelData-12

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:

ExcelData-3

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

ExcelData-4

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: http://1drv.ms/1GpZFd4

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.

ExcelData-1

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

ExcelData-2

  • 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: http://1drv.ms/1GpZFd4

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.
PowerPivot-15

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

Click on Design Tab on Top.

Now From Ribbon choose Table Properties.

PowerPivot-16

Select Query Editor from Switch to.

Add the Filter to your query.

Save your Query.

PowerPivot-17

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
PowerPivot-14
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.
PowerPivot-10

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.
PowerPivot-6

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

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.

PowerPivot-1

PowerPivot-2

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.
PowerPivot-3

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

Follow the Steps below:
PowerPivot-4

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.
PowerPivot-5

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.
PowerPivot-6

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.
PowerPivot-7

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.
PowerPivot-8
Select Close to return to Data Sheet imported after this operation.
PowerPivot-9
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.

Previous Older Entries Next Newer Entries

Important

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: