Creating SQL Server Integration Services (SSIS) Packages

This is continuous From My previous Post to create SQL Server Integration Services to get the updated data to MultiDimensionalAnalysisProject (MDAP) Database.
These are the following steps

1. Create SQL Server Integration Services (SSIS) projects.
2. Define Control flows and Data flows.
3. Deploy SSIS projects.
4. Schedule SSIS packages.

1. Create SQL Server Integration Services (SSIS) projects.
First we need to set up the framework for an SSIS project in our current MDAP Solution.This is done by adding a new project to the solution that is currently opened in Microsoft Visual Studio.
(Select integration services project  in the new project)
This will result in the SSIS project being created, and the new project should be immediately visible in the Solution Explorer.
2. Define Control flows and Data flows.
In the SSIS Toolbox  in the left hand side of the screen and drag the Data Flow Task item into the design area.Double click it to go to the Data Flow tab of the Design window.
To work with data flow we should define source and destination connections.For this we should create OLEDB Connections for the MDAP database, by Right clicking in the connection Manager which is the lower part of the Designer window.After completion of Connection setting Drag and drop the OLEDB source and Destination Controls.Then Define the Source table(from Live Database ) for Source and Destination Table(Database what we Create in initial step) in Destination Control.( if You get any time stamp error after mapping edit mapping relation ship and delete the time stamp mapping link).
Start Debugging we can see successful package run.
But each time if run it will create a new set of records in the destination tables, adding to the already existing records.
So, before we transfer the latest set of data to the destination tables, we must ensure that they are emptied first.
Let us move back to the Control Flow page of the SSIS package, where we will add an Execute SQL Task item.it to define it. The connection must point to the Database what we Create in initial step because it is the tables in there we would like to empty.Next, click the button in the SQL Statement field to enter the SQL Query that we would like to execute. The truncate statement removes the content of the table without removing the table itself.
Connect the two Control Flow items so that the Execute SQL Task  item is executed before the Transfer Data Flow item.
3. Deploy SSIS projects.
We need to process the Multidimensional database every single time the data have been updated.
For that  go to the toolbox and select an Analysis Service Processing Task item.
This Processing Task also requires a Connection Manager to be able to know which multidimensional database needs to be processed. So we will need to add an Analysis Services Connection to our list of
Connection Managers.
Debug it again for successful creation.
4. Schedule SSIS packages.
Assigning this job for SQL Agent. Already we know how to create a job and schedule it .
Open the SQL server Management studio:
Right click the Jobs folder to add a new job.Give name and other details,Go to the Steps page and click the New button. At the New Job Step page we must enter information about Name, Type and Source.
• Step name: Run the name of your SSIS Package.
• Type: SQL Server Integration Services Package.
• Run as: SQL Server Agent Service Account.
• Package Source: File system.
in next step shedule in when to run. ok
Finally right click the job and select "Start job at step" option.Make sure that the job can execute and complete without errors.(close all window related to this task before start).

That's it we completed the SQL Server Integration Services process ready to use in any BI Clients.

Business Intelligence with Dynamics NAV 2013

HI,

Actually from NAV 2013 we don't have any Business Intelligence setup. So we have to develop all Dimensions and Measures from Dot net Environment.
If we use SQL Server 2012 then we have to select "SQL Server Data Tools"  which is SQL Server 2012 folder from all programs.
If we use SQL Server 2008 R2 then we have to select "SQL Server Business Intelligence Development Studio"  which is SQL Server 2008 R2 folder from all programs.

****** IMP ****
(Before starting  we should create a Database in Database engine and import the required tables from live database for Analysis. This database information will update dynamically from live databse by creating SSIS Package because we should not disturb the Live database)

Steps to Create MultiDimesional Project for SQL Analysis:
After Opening and select path for the Project,

Step :1
The first thing we need to do is to tell upon which Data Source this project should be defined.
Right-click the Data Sources folder to start defining a Data Source for the Multidimensional project.
Clicking the New button will open the Connection Manager dialogue box.
The new connection must point to the your database on our SQL server. To access databases on our SQL server, you should select Native OLE DB\SQL Server Native Client 11.0.

Step : 2
The Data Source View is where we define the relationship between the tables that we want to access via the Data Source.
We will also be setting logical keys for the tables as we define their relationship.
Start out by right-clicking the Data Source Views folder to create a new Data Source View.

In the next step we will select from a list of available objects (tables in our case) to be included in the Data Source View.

And click next after that finish it.
Now we want to establish the relationship between the tables.
Already we know about Fact tables and Dimension tables
Some tables contain the actual data or the facts that we would like to analyze such as Amount, Quantity etc. These tables are often referred to as Fact tables.
Each post in the fact tables also contains information about e.g. which item has been sold, but the item information in the fact table may be kept short, often limited to the item number only.
Further information about an item number such as name and description may then be found in another table, e.g. the Item table. Such a table is then called a Dimension table.
Just Right click on the table and establish relation ship by selecting the tables and the fields which are primary and Foreign keys.

Step :3 
Creating Dimensions for our Analysis:
Dimensions to categorize and to filter your data.
we can go to the Solution Explorer on the right hand side to add a New Dimension to the Dimensions folder.
Select appropriate Data source view and select Main table(from where you want to select the dimension fields) and Key column.
Next select the remaining field which you want to use as dimensions. and finish it.
All you can see in Dimension Structure tab.
Finally Process the Dimension.(If you get any impersonation problem,
To correct the Impersonation setting, you will have to go back to the Data Source definition of your project in the Solution Explorer on the right hand side. Double click the  DBName.ds Data Source or right click to open the Data Source settings dialogue box.Select the Service Account.)

Step : 4
Creating Cubes for our Analysis :
To create a new cube you should right click the Cubes folder in the Solution Explorer on the right hand side of Visual Studio.
the Cube Wizard is actually just showing all the fields from the table that has a numeric data type.So Select the Tables and in the next select the required measures for Analysis.Next you will get the Dimension sets what you created, select as per requirement.
Finally Process The Cube.(If you get any impersonation problem,
Before clicking the Run button above there is one button change settings select it   Dimension Key Errors
Select "Use Custom error configuration"  and select "Ignore errors count")

Its Completed now you can check data by selecting dimensions and measures in Browser tab in visual studio.
But Database day by day increse so we should create SSIS package to get data Dynamically.
We will see how to create SSIS package in my next post.

Reading Webservice Response

While we are integrating our NAV with any other Frontend , we should read all the response from the webservice response file for our required information.
For simple testing i am just storing the response file in 'C:\TEMP\testing.xml' location

Name                              DataType       Subtype    Length
XMLNodelist                  Automation    'Microsoft XML, v6.0'.IXMLDOMNodeList
XMLDomElement           Automation    'Microsoft XML, v6.0'.IXMLDOMElement
SourceXMLDocument    Automation    'Microsoft XML, v6.0'.DOMDocument    


CLEAR(XMLNodelist);
CLEAR(XMLDomElement);
CREATE(SourceXMLDocument,TRUE,TRUE);

IF SourceXMLDocument.load('C:\TEMP\testing.xml') THEN BEGIN
    XMLNodelist :=SourceXMLDocument.getElementsByTagName('ns1:multiCallResponse');
    XMLDomElement :=  XMLNodelist.item(0);
    IF XMLDomElement.hasChildNodes() THEN BEGIN
       XMLNodelist1 := XMLDomElement.childNodes();
       XMLDomElement1 := XMLNodelist1.item(0);      //item
       MESSAGE (XMLDomElement1.nodeName);  -------------> To get the node name
       MESSAGE (XMLDomElement1.text); ----------------------> To get the node value
     End;
End;

Import from Excel with Automation variables

Actulally this i tried and get success in NAV 2009 i hope this will work in future version also

Write this code in Report with empty DataItem because is processing only purpose

Import from Excel

Variables :

Name          DataType      Subtype        Length

SheetName   Text                250

FileName      Text                250

XlWrkSht     Automation Unknown Automation Server.Worksheet     

XlRange       Automation Unknown Automation Server.Range  

xa                Automation Unknown Automation Server.Application    

XlWrkb        Automation Unknown Automation Server.Workbook     

XlWrkshts    Automation Unknown Automation Server._Worksheet  

Maxi            Integer                     

Maxj            Integer                     

i                   Integer                     

Window       Dialog                       

CellValue      Text                250

j                   Integer                     

CommonDialogMgt           Codeunit       Common Dialog Management  

ExcelBuf      Record           Excel Buffer 

Ct                Integer                     

ExportToExcel         Boolean 

                   

Step-1 :  Create a Method OpenBook()

  IF FileName = '' THEN

    ERROR(Text001);

IF SheetName = '' THEN

  ERROR(Text002);

IF NOT EXISTS(FileName) THEN

  ERROR(Text003,FileName);

IF NOT CREATE(xa,TRUE,TRUE) THEN

  ERROR(Text000);

xa.Workbooks.Open(FileName);

XlWrkb := xa.ActiveWorkbook;

i := 1;

EndOfLoop := XlWrkb.Worksheets.Count;

WHILE (i <= EndOfLoop) AND (NOT Found) DO BEGIN

  XlWrkshts := XlWrkb.Worksheets.Item(i);

  IF SheetName = XlWrkshts.Name THEN

    Found := TRUE;

  i := i + 1;

END;

IF Found THEN

  XlWrkSht := XlWrkb.Worksheets.Item(SheetName)

ELSE BEGIN

  XlWrkb.Close(FALSE);

  xa.Quit;

  CLEAR(xa);

  ERROR(Text004,SheetName);

END;

Step-2 :

Write the following code in ON PreDataItem Section :

OpenBook(FileName,SheetName);

Window.OPEN(

  Text005 +

  '@1@@@@@@@@@@@@@@@@@@@@@@@@\');

Window.UPDATE(1,0);

XlRange := XlWrkshts.Range('c1').SpecialCells(11);

Maxi := XlRange.Row();

Maxj := XlRange.Column();

i := 1;

REPEAT

  CLEAR(CellValue);

  INIT;

  CellValue :=  FORMAT(XlWrkshts.Range('A'+FORMAT(i)).Value);

  CellValue :=  DELCHR(CellValue, '<=>','');

  EVALUATE("Weborder No.",FORMAT(CellValue));

  CLEAR(CellValue);

  CellValue :=  FORMAT(XlWrkSht.Range('B'+FORMAT(i)).Value);

  CellValue :=  DELCHR(CellValue, '<=>','');

  "NAVoredr No." := CellValue;

  CLEAR(CellValue);

  CellValue :=  FORMAT(XlWrkSht.Range('C'+FORMAT(i)).Value);

  CellValue :=  DELCHR(CellValue, '<=>','');

  EVALUATE("Shipping Cost",CellValue);

  CLEAR(CellValue);

  CellValue :=  FORMAT(XlWrkSht.Range('D'+FORMAT(i)).Value);

  CellValue :=  DELCHR(CellValue, '<=>','');

  EVALUATE("Shipping Charge",CellValue);

  INSERT;

  i := i+1;

  Window.UPDATE(1,ROUND(i / Maxi * 10000,1));

UNTIL i > Maxi;

XlWrkb.Close(FALSE);

xa.Quit;

CLEAR(xa);

Window.CLOSE;

Integration Tools

Microsoft Dynamics NAV 2009 provides several integration tools that can be used inside or outside of Microsoft Dynamics NAV to integrate to other applications.

Web Services

Web services are a standardized way for independent software systems to

communicate with one another over standard Internet protocols. Web services architecture is designed for dynamic program-to-program interaction.

Microsoft Dynamics NAV 2009 supports Web services, which makes it easy to integrate Microsoft Dynamics NAV with other systems. This is possible with the introduction of Microsoft Dynamics NAV Server.

There are several types of Web service in Microsoft Dynamics NAV 2009:

•The simplest Web service type is developed by using the page

object. By using the page object, Microsoft Dynamics NAV

constructs a default Web service that has a fixed set of methods.

Generally, it corresponds to general data access, such as get and set

individual values or retrieves and updates lists of values.

•Another Web service type involves including codeunits and the

functions from those codeunits in the Web service.

•The last type of Web service includes the ability to pass complex

data types by using an XMLport object as a parameter in a codeunit

function.

ODBC

Open DataBase Connectivity, or ODBC, is a standard database access method developed by Microsoft. The goal of ODBC is to make it possible to access any data from any application regardless of which database management system (DBMS) is handling the data. ODBC manages the access of data by inserting a middle layer called a database driver between an application and the DBMS.

The purpose of the middle layer is to translate the application’s data queries into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant; that is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them.

C/FRONT

C/FRONT is an application programming interface that can be used to access a Microsoft Dynamics NAV database. C/FRONT facilitates high-level interaction with the Microsoft Dynamics NAV database and enables developers to manipulate any Microsoft Dynamics NAV database.

The central component of C/FRONT is a library of C functions. These functions provide access to every aspect of data storage and maintenance, and enable the integration of both standard and custom applications with a Microsoft Dynamics

NAV database.

C/FRONT comes as a DLL for direct linkage with a program and also as an OCX that a program can use.

OCX

OLE Custom control, or OCX, is an independent program module that can beaccessed by other programs in a Microsoft® Windows® environment. OCXcontrols end with an .ocx extension. OCX controls represent Microsoft’s secondgeneration of control architecture, the first being VBX controls written in VisualBasic.

Both VBX and OCX controls have now been superseded by Microsoft ActiveX®controls. However, ActiveX is backward compatible with OCX controls whichmeans that ActiveX containers such as Microsoft Internet Explorer can execute

OCX components.

There is a vast array of commercially available OCXs that perform a variety of

tasks and developers can produce their own. They can use tools such as

Microsoft Visual C++ or Visual Basic to create OCXs that can be called by

Microsoft Dynamics NAV.

** NAV Terminology

The following shows descriptions of several terms in the third column:

Properties:

Properties control the appearance and behavior of application objects and all sub-objects. Properties are used to control the appearance of data, specify

default values, specify colors, and define relationships.

C/AL:

C/AL is the language used for writing functions in C/SIDE. In the

previous table, C/AL refers to functions written in this language.

Triggers:

When specific things happen to the application objects, the system

automatically activates a trigger. Inside a trigger, developers can add C/AL code

if they want to modify the default behavior of the application object or extend its

functionality.

Fields:

A field is the smallest unit of information in the database. A field

typically stores information such as a name or a number.

Keys:

A key defines the order in which data is stored in the tables. Speed up

searches in tables by defining several keys to sort information in different ways.

Controls:

Controls are objects on a form or report that display data, perform

actions or decorate the form. Typical examples are command buttons and text labels.

Request Form:

A request form is a form used in a report or a dataport. Before a

report or a dataport is run, a request form appears to let the user specify filters and options for the report or the dataport.

Request Page

: A request page is the request form equivalent in the RoleTailored client.

Data Items:

A data item is a building block used for defining a model of data

when creating a report or a dataport. By using a hierarchy of data items,

developers define which data to include in the report. A data item represents a table and when a report is run, the system cycles through the records in the associated table. In a report, a data item can have one or more sections.

Sections:

A section is a substructure of a data item. A section is where controls are placed to display information. Generally, sections are used to define the body, header, and footer in the report.

NodeName:

NodeNames are used to specify the name of a node in an XML

document. The name specified is inserted in theNodeName field of the

XMLport Designer of the element or attribute in question.

NodeTypes:

This property is used to specify whether an XML object is an

element or an attribute.

Menu Node:

A Menu Node can be either a Menu Group or a Menu Item.

Menu Group:

A Menu Group is a collection of Menu Nodes.

Menu Item:

A Menu Item is the lowest level of the menu tree. It is associated

with a specific application object.

Is Microsoft Dynamics NAV Object Oriented ?

Microsoft Dynamics NAV is not object-oriented but object-based. This is an important distinction. In an object-oriented language or environment, developers can create new types of objects based on the ones that are already in the system.
In C/SIDE, developers have eight types of application objects and that is all.Developers can create and use Tables, Forms, Pages, Reports, Dataports,
XMLports, Codeunits, and MenuSuites, but nothing else.
Limiting developers to use these eight objects makes their work faster and more efficient. The biggest benefit from this limitation is stability. It is fairly difficult to create a severe bug in C/SIDE.

PAGE Object

Page and Action Triggers
Microsoft Dynamics NAV 2013

Page triggers allow you to use C/AL code to control the behavior of the system as a result of an event on the page, such as a page opening or a field changing its value. You typically use page triggers for advanced validation and logic.
Page triggers can be divided into three categories:

• General page triggers that apply to the entire page
• Field page triggers that apply to a field control on a page
• Action triggers that apply to an action on a page.

General Triggers
The following table lists triggers that apply to the entire page.

Page trigger name	Executed
OnInit Trigger	When the page is   loaded, but before the controls are available.
OnOpenPage Trigger	When the page is   initialized and the controls are available.
OnClosePage Trigger	When the page about to   close and after OnQueryClosePage Trigger trigger.
OnFindRecord Trigger	When the page is   opened and a record is retrieved from a table.
OnNextRecord Trigger	When the page changes   from displaying one record to another record in a table. For example, on a   Customer card page, this happens when a user selects Next (Ctrl+Page   Down) or Previous (Ctrl+Page Up).
OnAfterGetRecord Trigger	When a record has been   retrieved but not yet displayed.
OnNewRecord Trigger	When a new record has   been initialized but not yet displayed.
OnInsertRecord Trigger	When a new record is   about to be inserted in the table.
OnModifyRecord Trigger	When a record is about   to be modified in the table.
OnDeleteRecord Trigger	When a record is about   to be deleted from the table.
OnQueryClosePage Trigger	When the page is about   to close, but before the OnClosePage Trigger

Field Triggers
The following table describes the triggers that are available on field controls.

Control trigger	Executed
OnValidate (Page fields) Trigger	When the user changes   the value in a field and then selects away from the field so that the field   loses focus.
OnLookup (Page fields) Trigger	When the user requests   a lookup by clicking a field’s lookup button or pressing F4.
OnDrillDown Trigger	When the user requests   a drill-down by choosing the field’s drill-down button or pressing Shift+F8.
OnAssistEdit Trigger	When the user requests   assist-edit by choosing an AssistEdit button or by pressing Shift+F4.
OnControlAddin Trigger	When a control add-in   is initiated on a page.

Action Triggers
The following table lists triggers that apply to actions on a page.

Triggers	Executed
OnAction Trigger	When an action is   initiated on a page.

REPORT object

Report Triggers
The following table lists triggers that apply to the report itself.

Trigger	Executed
OnInitReport Trigger	When the report is   loaded.
OnPreReport Trigger	Before the report is   run, but after the RequestPage has been run.
OnPostReport Trigger	After the report has   run, but not if the report was stopped manually or by the QUIT Function (Report, XMLport).

In Report we define the Data Items and its triggers are as follows
Data Item Triggers
The following table lists triggers that apply to each data item on the report.
The Trigger executing style for Report already i published in my blog .

Trigger	Executed
OnPreDataItem Trigger	Before the data item   is processed, but after the associated variable has been initialized.
OnAfterGetRecord (Data Items) Trigger	When a record has been   retrieved from the table.
OnPostDataItem Trigger	When the data item has   been iterated for the last time.

XML PORT Object

Microsoft Dynamics NAV 2013
From NAV 2013 we can run XML port direclty.
The following triggers apply to XMLports.

XMLport trigger	Executed
OnAfterAssignField Trigger	Executed after a field has been assigned a value and before it   is validated and imported.This   trigger is only used to import data.
OnAfterAssignVariable Trigger	Executed after the value defined in the XML document is assigned   to the text variable.This   trigger is only used to import data.
OnAfterGetField Trigger	Executed after a field is passed to the XML document.This   trigger is only used to export data.
OnAfterGetRecord (XMLports) Trigger	Executed after a record is retrieved from a table and before it   is exported to the XML document.This   trigger is only used to export data.
OnAfterInitRecord Trigger	Executed after a record is loaded.This   trigger is only used to import data.
OnAfterInsertRecord Trigger	Executed after a record has been inserted into a database table.This   trigger is only used to import data.
OnBeforeInsertRecord Trigger	Executed after a record has been loaded and before it is   inserted into a database table.This   trigger is only used to import data.
OnBeforePassField Trigger	Executed before a field is passed to the XML document.This   trigger is only used to export data.
OnBeforePassVariable Trigger	Executed after the source expression has been formatted into a   text variable and before the text variable is passed to the XML document.This   trigger is only used to export data.
OnInitXMLport Trigger	Executes   when the XMLport is loaded and before any table views and filters are set.
OnPreXMLport Trigger	Executed   after the table views and filters are set and before the XMLport is run.
OnPostXMLport Trigger	Executes   after the XMLport is run.
OnPreXMLItem Trigger	Executed after the table is initialized and before you start   exporting data to an XML object. This trigger only applies to XMLport   elements that have a source type of Table.This   trigger is only used to export data.

Query Object

Query Triggers
 It is new Object introduced from Microsoft Dynamics NAV 2013

This topic describes the C/AL triggers that are available for queries. Triggers are typically used to perform calculations and verification. Triggers allow you to control how data is selected and retrieved in a more complex and effective way than you can achieve by using properties.

Query Object Triggers

The following table lists the triggers that apply to the query object.

Trigger	Executes
OnBeforeOpen	Before the   query object is run and the dataset is generated. For example, you can use   the OnBEforeOpen trigger to apply filters using the SETFILTER function.

Code Units

Codeunits  Used to organize and group code which is written by the developers

Codeunit Triggers
Microsoft Dynamics NAV 2013

The following triggers apply to codeunits.

Codeunit trigger name	Executed
OnBeforeTestRun Trigger	Before a   test function of a test codeunit is run.
OnAfterTestRun Trigger	After a   test function of a test codeunit is run.