Walkthrough: Defining KPIs for a Cube [AX 2012]

Prerequisites

---

To complete this walkthrough, you will need:

• Microsoft Dynamics AX with sample data
• The MyCustomers cube from  Creating Cubes [AX 2012] Post.
• SQL Server Business Intelligence Development Studio (BIDS) or SQL Server Data Tools
• Configure Analysis Services by running the Microsoft Dynamics AX Setup wizard

Defining a KPI for Inventory Quantity

---

To display your KPI in a Business Overview web part on a role center, you must define value, goal, status, and trend expressions for the KPI. If you exclude one of the expressions, the KPI will not display properly.

To create a KPI for Inventory Quantity

1. In BIDS, open the Analysis Services project that contains the Sales analysis cube, and then double-click MyCustomers .cube.
2. Click the KPIs tab.
3. On the toolbar for the KPIs tab, click New KPI. A form displays that allows you to define the KPI.
4. For the Name field, type Customer sales.
5. For the Value Expression field, type the following expression.

[Measures].[Total customer sales Count]

This expression retrieves the quantity in inventory units.

Note

You can drag measures from the Metadata tab of the Calculation Toolspane to help you create expressions.

6. For the Goal Expression field, type the following expression.

7500

This expression identifies the goal for the inventory quantity.

7. For the Status indicator field, select Gauge from the drop-down list.
8. For the Status expression field, type the following expression.

Case
    When
        [Measures].[Total customer sales Count] >= 7500
    Then 1
    When
        [Measures].[Total customer sales Count] < 7500
    AND
        [Measures].[Total customer sales Count] > 6500
    Then 0
    Else-1
End

This expression provides a basis to evaluate progress toward meeting the goal. The graphic that displays for status of the KPI depends on what value this expression evaluates to. 

9. For the Trend indicator field, select Standard arrow from the drop-down list. 
10. For the Trend expression field, type the following expression.

Case
    When
        [Measures].[Total customer sales Count] >= 3000
    Then 1
    When
        [Measures].[Total customer sales Count] < 3000
    
    Then -1
End

This expression provides a basis to evaluate the status in some historical context. The graphic that displays for the trend of the KPI depends on what value this expression evaluates to. Typically, the trend expression would evaluate the current status relative to a previous status. 

To view the KPI 

1. On the Build menu, click Deploy Dynamics AX MyCustomers .

Note: The name of the database may vary.

2. On the toolbar for the KPIs tab, click Browser View. The KPI displays in the list.

Walkthrough: Defining KPIs for a Cube [AX 2012]

Prerequisites

---

To complete this walkthrough, you will need:

• Microsoft Dynamics AX with sample data
• The MyCustomers cube from  Creating Cubes [AX 2012] Post.
• SQL Server Business Intelligence Development Studio (BIDS) or SQL Server Data Tools
• Configure Analysis Services by running the Microsoft Dynamics AX Setup wizard

Defining a KPI for Inventory Quantity

---

To display your KPI in a Business Overview web part on a role center, you must define value, goal, status, and trend expressions for the KPI. If you exclude one of the expressions, the KPI will not display properly.

To create a KPI for Inventory Quantity

1. In BIDS, open the Analysis Services project that contains the Sales analysis cube, and then double-click MyCustomers .cube.
2. Click the KPIs tab.
3. On the toolbar for the KPIs tab, click New KPI. A form displays that allows you to define the KPI.
4. For the Name field, type Customer sales.
5. For the Value Expression field, type the following expression.

[Measures].[Total customer sales Count]

This expression retrieves the quantity in inventory units.

Note

You can drag measures from the Metadata tab of the Calculation Toolspane to help you create expressions.

6. For the Goal Expression field, type the following expression.

7500

This expression identifies the goal for the inventory quantity.

7. For the Status indicator field, select Gauge from the drop-down list.
8. For the Status expression field, type the following expression.

Case
    When
        [Measures].[Total customer sales Count] >= 7500
    Then 1
    When
        [Measures].[Total customer sales Count] < 7500
    AND
        [Measures].[Total customer sales Count] > 6500
    Then 0
    Else-1
End

This expression provides a basis to evaluate progress toward meeting the goal. The graphic that displays for status of the KPI depends on what value this expression evaluates to. 

9. For the Trend indicator field, select Standard arrow from the drop-down list. 
10. For the Trend expression field, type the following expression.

Case
    When
        [Measures].[Total customer sales Count] >= 3000
    Then 1
    When
        [Measures].[Total customer sales Count] < 3000
    
    Then -1
End

This expression provides a basis to evaluate the status in some historical context. The graphic that displays for the trend of the KPI depends on what value this expression evaluates to. Typically, the trend expression would evaluate the current status relative to a previous status. 

To view the KPI 

1. On the Build menu, click Deploy Dynamics AX MyCustomers .

Note: The name of the database may vary.

2. On the toolbar for the KPIs tab, click Browser View. The KPI displays in the list.

Batch processing in Microsoft Dynamics AX 2012

Batch processing is a non interactive task-processing technique where users create batch jobs to organize appropriate types of tasks to be processed as a unit. Batch processing has some important advantages: it lets users schedule batch tasks and define the conditions under which they execute, add the tasks to a queue, and set them to run automatically on a batch server.

After execution is complete, the batch server logs any errors and sends alerts.

A batch job might involve printing reports, closing inventory, or performing periodic maintenance. By scheduling a batch job to process these types of resource-intensive tasks in off-peak hours, users can avoid slowing down the system during working hours.
Table below describes how standard batch processing concepts are represented in Microsoft Dynamics AX.

Concept	Description
Batch task	The smallest unit of work that can be executed using the batch framework. It is a batch-executable class that contains business logic to perform a certain action. The Microsoft Dynamics AX classes that are used for batch tasks are designated to run on the server. These tasks can run automatically as part of a batch job on the AOS. This version of the product has limited support for client batch jobs; it is recommended that you use server-side batch jobs to take full advantage of the new features in Microsoft Dynamics AX 2012.
Batch job	A complete process that achieves a goal, such as printing a report or performing the inventory closing process. A batch job is made up of one or more batch tasks.
Batch group	A logical categorization for batch tasks that lets administrators specify which AOS instance runs a particular task. Tasks that are not explicitly assigned to a batch group are, by default, assigned to an empty (default) group.
Batch server	An AOS instance that processes batch jobs.

Common uses of the batch framework
Organizations can use the batch framework to perform asynchronous operations in a variety of scenarios. Typically, organizations create batch jobs to address the following kinds of needs:
■ Enable scheduling flexibility The batch framework can perform periodic tasks on a regular schedule, such as data cleanup or invoice processing. For example, to run invoice processing at the end of every month, you can set up a recurring batch job that runs at midnight on the last working day of each month. The batch framework automatically picks up the job and processes pending invoices according to the specified schedule.
■ Control the order in which tasks execute With the batch framework, you can develop a workflow or perform a complex data upgrade in a sequence that you specify. You can also set up dependencies between the tasks and create a dependency tree that ensures that certain tasks run in sequence while others run in parallel.
■ Enable conditional processing Decision trees can help you implement a reliable way of processing data. Developers or system administrators can set up dependencies between tasks in such a way that different tasks are executed, depending on whether a particular task succeeds or fails. System administrators can also set up alerts so that they are notified if a job fails.

■ Improve performance by using parallelization The batch framework lets you take advantage of multithreading, which ensures that your processor’s capabilities are used fully. This is particularly important for long-running processes, such as inventory closing. You can improve performance further by breaking a process into tasks and executing them against different AOS instances, thus increasing the throughput and reducing overall execution time.
■ Implement advanced logging and profiling The batch framework lets you see what errors or exceptions were thrown the last time the batch ran, and it also shows you how long a process takes to execute. Advanced logging and the new profiling capabilities are also useful for performance benchmarking and security auditing.

Performance
The new capability to run larger and more complex batch jobs has required performance enhancements to the batch framework. In Microsoft Dynamics AX 2012, the batch framework is designed to be a server-side component. This lets you design multithreaded server processes in a controlled manner. By configuring the number of parallel execution threads and servers, defining the set and order of tasks for processing, and setting the execution schedule, you can achieve greater scalability across your hardware.

As mentioned earlier, the batch framework is now designed to run X++ that has been compiled as .NET CIL code for batch jobs. This significantly improves performance compared to Microsoft Dynamics
AX 2009, which ran interpreted X++ code. Compared to the interpreted code, garbage collection is much better, and because of session pooling, scheduling new batch jobs is less resource intensive. You can also profile the performance of jobs by using Microsoft Visual Studio Performance Profiler.

Exporting data to Excel by Code x++

static void DASCreateExcelDocument(Args _args)
{
   SysExcelApplication  xlsApplication;
   SysExcelWorkBooks    xlsWorkBookCollection;
   SysExcelWorkBook     xlsWorkBook;
   SysExcelWorkSheets   xlsWorkSheetCollection;
   SysExcelWorkSheet    xlsWorkSheet;
   SysExcelRange        xlsRange;
   CustTable            custTable;
   int                  row = 1;
   str                  fileName;
   ;
   //Filename
   fileName = "C:\\Test.xlsx";
   //Initialize Excel instance
   xlsApplication           = SysExcelApplication::construct();
   //Open Excel document
   //xlsApplication.visible(true);
   //Create Excel WorkBook and WorkSheet
   xlsWorkBookCollection    = xlsApplication.workbooks();
   xlsWorkBook              = xlsWorkBookCollection.add();
   xlsWorkSheetCollection   = xlsWorkBook.worksheets();
   xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
   //Excel columns captions
   xlsWorkSheet.cells().item(row,1).value("Account Num");
   xlsWorkSheet.cells().item(row,2).value("Name");
   row++;
   //Fill Excel with CustTable AccountNum and Name fields (only 10 records)
   while select custTable
   {
      if(row == 10)
        break;
      xlsWorkSheet.cells().item(row,1).value(custTable.AccountNum);
      xlsWorkSheet.cells().item(row,2).value(custTable.Name);
      row++;
   }
   //Check whether the document already exists
   if(WinApi::fileExists(fileName))
      WinApi::deleteFile(fileName);
   //Save Excel document
   xlsWorkbook.saveAs(fileName);
   //Open Excel document
   xlsApplication.visible(true);
   //Close Excel
   //xlsApplication.quit();
   //xlsApplication.finalize();
}

Exporting data to Excel by Code x++

static void DASCreateExcelDocument(Args _args)
{
   SysExcelApplication  xlsApplication;
   SysExcelWorkBooks    xlsWorkBookCollection;
   SysExcelWorkBook     xlsWorkBook;
   SysExcelWorkSheets   xlsWorkSheetCollection;
   SysExcelWorkSheet    xlsWorkSheet;
   SysExcelRange        xlsRange;
   CustTable            custTable;
   int                  row = 1;
   str                  fileName;
   ;
   //Filename
   fileName = "C:\\Test.xlsx";
   //Initialize Excel instance
   xlsApplication           = SysExcelApplication::construct();
   //Open Excel document
   //xlsApplication.visible(true);
   //Create Excel WorkBook and WorkSheet
   xlsWorkBookCollection    = xlsApplication.workbooks();
   xlsWorkBook              = xlsWorkBookCollection.add();
   xlsWorkSheetCollection   = xlsWorkBook.worksheets();
   xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
   //Excel columns captions
   xlsWorkSheet.cells().item(row,1).value("Account Num");
   xlsWorkSheet.cells().item(row,2).value("Name");
   row++;
   //Fill Excel with CustTable AccountNum and Name fields (only 10 records)
   while select custTable
   {
      if(row == 10)
        break;
      xlsWorkSheet.cells().item(row,1).value(custTable.AccountNum);
      xlsWorkSheet.cells().item(row,2).value(custTable.Name);
      row++;
   }
   //Check whether the document already exists
   if(WinApi::fileExists(fileName))
      WinApi::deleteFile(fileName);
   //Save Excel document
   xlsWorkbook.saveAs(fileName);
   //Open Excel document
   xlsApplication.visible(true);
   //Close Excel
   //xlsApplication.quit();
   //xlsApplication.finalize();
}

Batch processing in Microsoft Dynamics AX 2012

Batch processing is a non interactive task-processing technique where users create batch jobs to organize appropriate types of tasks to be processed as a unit. Batch processing has some important advantages: it lets users schedule batch tasks and define the conditions under which they execute, add the tasks to a queue, and set them to run automatically on a batch server.

After execution is complete, the batch server logs any errors and sends alerts.

A batch job might involve printing reports, closing inventory, or performing periodic maintenance. By scheduling a batch job to process these types of resource-intensive tasks in off-peak hours, users can avoid slowing down the system during working hours.
Table below describes how standard batch processing concepts are represented in Microsoft Dynamics AX.

Concept	Description
Batch task	The smallest unit of work that can be executed using the batch framework. It is a batch-executable class that contains business logic to perform a certain action. The Microsoft Dynamics AX classes that are used for batch tasks are designated to run on the server. These tasks can run automatically as part of a batch job on the AOS. This version of the product has limited support for client batch jobs; it is recommended that you use server-side batch jobs to take full advantage of the new features in Microsoft Dynamics AX 2012.
Batch job	A complete process that achieves a goal, such as printing a report or performing the inventory closing process. A batch job is made up of one or more batch tasks.
Batch group	A logical categorization for batch tasks that lets administrators specify which AOS instance runs a particular task. Tasks that are not explicitly assigned to a batch group are, by default, assigned to an empty (default) group.
Batch server	An AOS instance that processes batch jobs.

Common uses of the batch framework
Organizations can use the batch framework to perform asynchronous operations in a variety of scenarios. Typically, organizations create batch jobs to address the following kinds of needs:
■ Enable scheduling flexibility The batch framework can perform periodic tasks on a regular schedule, such as data cleanup or invoice processing. For example, to run invoice processing at the end of every month, you can set up a recurring batch job that runs at midnight on the last working day of each month. The batch framework automatically picks up the job and processes pending invoices according to the specified schedule.
■ Control the order in which tasks execute With the batch framework, you can develop a workflow or perform a complex data upgrade in a sequence that you specify. You can also set up dependencies between the tasks and create a dependency tree that ensures that certain tasks run in sequence while others run in parallel.
■ Enable conditional processing Decision trees can help you implement a reliable way of processing data. Developers or system administrators can set up dependencies between tasks in such a way that different tasks are executed, depending on whether a particular task succeeds or fails. System administrators can also set up alerts so that they are notified if a job fails.

■ Improve performance by using parallelization The batch framework lets you take advantage of multithreading, which ensures that your processor’s capabilities are used fully. This is particularly important for long-running processes, such as inventory closing. You can improve performance further by breaking a process into tasks and executing them against different AOS instances, thus increasing the throughput and reducing overall execution time.
■ Implement advanced logging and profiling The batch framework lets you see what errors or exceptions were thrown the last time the batch ran, and it also shows you how long a process takes to execute. Advanced logging and the new profiling capabilities are also useful for performance benchmarking and security auditing.

Performance
The new capability to run larger and more complex batch jobs has required performance enhancements to the batch framework. In Microsoft Dynamics AX 2012, the batch framework is designed to be a server-side component. This lets you design multithreaded server processes in a controlled manner. By configuring the number of parallel execution threads and servers, defining the set and order of tasks for processing, and setting the execution schedule, you can achieve greater scalability across your hardware.

As mentioned earlier, the batch framework is now designed to run X++ that has been compiled as .NET CIL code for batch jobs. This significantly improves performance compared to Microsoft Dynamics
AX 2009, which ran interpreted X++ code. Compared to the interpreted code, garbage collection is much better, and because of session pooling, scheduling new batch jobs is less resource intensive. You can also profile the performance of jobs by using Microsoft Visual Studio Performance Profiler.

Set the .NET Business Connector proxy account

Some components require that the .NET Business Connector be configured to connect to Microsoft Dynamics AX with a proxy account. The use of a proxy enables the .NET Business Connector to connect on behalf of Microsoft Dynamics AX users when authenticating with an AOS instance.
The Business Connector proxy is a Microsoft Windows domain account that is configured from the initialization checklist, or in the Administration > Setup > Security > System accounts form.
Work with a system administrator to create a new account for the Business Connector before you install it. We recommend that the account be set up as follows:

• Must be a Windows domain account
• Must be a dedicated account (used only by Business Connector)
• Must have a password that does not expire
• Must not have interactive logon rights
• Must not be a Microsoft Dynamics AX user.

Important
If a malicious user learns the Business Connector proxy credentials (name and password), that user could gain unauthorized access to sensitive information, and potentially damage the Microsoft Dynamics AX application. For this reason, only Microsoft Dynamics AX administrators should know the proxy credentials.
To set up and configure the Business Connector proxy, you must perform the following steps.

1. Create the proxy account in Active Directory.
2. Add the proxy account to the IIS local Windows group.
3. Configure the IIS application pool.
4. Install the .NET Business Connector.
5. Specify the Business Connector proxy user in Microsoft Dynamics AX.

Create the proxy account in Active Directory

---

1. Create a unique user in Active Directory in the form domain\username, for example, domain\bcproxy. This user must not have the same name as an existing Microsoft Dynamics AX user. For the procedure to add a new user, see the Active Directory documentation.
2. Assign a password to the user.
3. Select the Password does not expire option.
4. Select the No interactive logon rights option.
5. Close Active Directory.

Add the proxy account to the IIS local Windows group

---

For Web applications, you must add the Business Connector proxy account to the IIS local Windows group. If you are using Windows SharePoint Services, you must also add the account to the Windows SharePoint Services local Windows group.

1. Open the Computer Management application (Start > Administrative Tools > Computer Management).
2. Expand the Groups folder under Local Users and Groups.
3. Add the Business Connector proxy account to the following groups:
   
   • IIS_WPG (IIS Worker Process Group)
   • STS_WPG (STS Worker Process Group), if running Windows SharePoint Services

Specify the Business Connector proxy user in Microsoft Dynamics AX

---

1. Start Microsoft Dynamics AX (Start > All Programs > Microsoft Dynamics > Microsoft Dynamics AX).
2. Open the System service accounts form: Administration > Setup > Security > System service accounts.
3. In the Business Connector Proxy section of the form, enter the alias and the domain of the user.
4. Click OK.

Set the .NET Business Connector proxy account

Some components require that the .NET Business Connector be configured to connect to Microsoft Dynamics AX with a proxy account. The use of a proxy enables the .NET Business Connector to connect on behalf of Microsoft Dynamics AX users when authenticating with an AOS instance.
The Business Connector proxy is a Microsoft Windows domain account that is configured from the initialization checklist, or in the Administration > Setup > Security > System accounts form.
Work with a system administrator to create a new account for the Business Connector before you install it. We recommend that the account be set up as follows:

• Must be a Windows domain account
• Must be a dedicated account (used only by Business Connector)
• Must have a password that does not expire
• Must not have interactive logon rights
• Must not be a Microsoft Dynamics AX user.

Important
If a malicious user learns the Business Connector proxy credentials (name and password), that user could gain unauthorized access to sensitive information, and potentially damage the Microsoft Dynamics AX application. For this reason, only Microsoft Dynamics AX administrators should know the proxy credentials.
To set up and configure the Business Connector proxy, you must perform the following steps.

1. Create the proxy account in Active Directory.
2. Add the proxy account to the IIS local Windows group.
3. Configure the IIS application pool.
4. Install the .NET Business Connector.
5. Specify the Business Connector proxy user in Microsoft Dynamics AX.

Create the proxy account in Active Directory

---

1. Create a unique user in Active Directory in the form domain\username, for example, domain\bcproxy. This user must not have the same name as an existing Microsoft Dynamics AX user. For the procedure to add a new user, see the Active Directory documentation.
2. Assign a password to the user.
3. Select the Password does not expire option.
4. Select the No interactive logon rights option.
5. Close Active Directory.

Add the proxy account to the IIS local Windows group

---

For Web applications, you must add the Business Connector proxy account to the IIS local Windows group. If you are using Windows SharePoint Services, you must also add the account to the Windows SharePoint Services local Windows group.

1. Open the Computer Management application (Start > Administrative Tools > Computer Management).
2. Expand the Groups folder under Local Users and Groups.
3. Add the Business Connector proxy account to the following groups:
   
   • IIS_WPG (IIS Worker Process Group)
   • STS_WPG (STS Worker Process Group), if running Windows SharePoint Services

Specify the Business Connector proxy user in Microsoft Dynamics AX

---

1. Start Microsoft Dynamics AX (Start > All Programs > Microsoft Dynamics > Microsoft Dynamics AX).
2. Open the System service accounts form: Administration > Setup > Security > System service accounts.
3. In the Business Connector Proxy section of the form, enter the alias and the domain of the user.
4. Click OK.

Sample to manipulate string Function in Dynamics AX

1 – str2Capital () : Convert first character in to Capitalstatic void Jobtest(Args _args)
        {
            str source,desti;
            ;
            source  = 'dynamics ax';
            desti   = str2Capital(source);
            info(strfmt("%1 : %2",source,desti));
        }
    Using str2CapitalWord() method convert the first character of the first word in Sentence to Capital.

2- str2Con () : Concatenate the word or sentence from specified characterstatic void Jobtest(Args _args)
        {
            str         source,desti;
            container   Con;
            int         i;
            ;
            source  = 'dyna,mics, ax';
            Con   = str2Con(source,',');
            for (i = 1; i <= conlen(Con); i++)
            {
                info(strfmt("%1",conpeek(Con,i)));
            }
        }

3-  str2Date() : Date in string type & return it as Date type static void Jobtest(Args _args)
        {
            str         input;
            Date        output
            ;
            input  = '05/30/2010';
            output  = str2Date(input,213);
            info(strfmt("%1",output));
        }
        123 : DMY
        213 : MDY

4- strIntOk () : Check a string which contain integer value static void Jobtest(Args _args)
        {
            str         input;
            boolean     output;
            ;
            input   = '1294';
            output  = str2IntOk(input);
            if(output)
                info(strfmt("Integer Value"));
            else
                info(strfmt("Not a Integer Value"));
        }

5- strEndsWith () : Check whether end characters are matching or not

static void Jobtest(Args _args)
        {
            str         input1,input2;
            boolean     output;
            ;
            input1  = 'dyanmics ax';
            input2  = 'ax';
            output  = strEndsWith(input1,input2);
            if(output)
                info(strfmt("OK"));
            else
                info(strfmt("Not OK"));
        }

6- strLFix  :  strLfix(str _str, int _lenght [, char _char])

Add character _char after string _str to obtain a string long _leng.

Default filler chararcer is ' '.

strLfix('aaa', 5, '_') //returns the text string 'aaa__'.

If _str is longer than _leght function return a substring of _str.

strLfix('dynamics ax',5);

//returns the text string ‘dynam'.

Sample to manipulate string Function in Dynamics AX

1 – str2Capital () : Convert first character in to Capitalstatic void Jobtest(Args _args)
        {
            str source,desti;
            ;
            source  = 'dynamics ax';
            desti   = str2Capital(source);
            info(strfmt("%1 : %2",source,desti));
        }
    Using str2CapitalWord() method convert the first character of the first word in Sentence to Capital.

2- str2Con () : Concatenate the word or sentence from specified characterstatic void Jobtest(Args _args)
        {
            str         source,desti;
            container   Con;
            int         i;
            ;
            source  = 'dyna,mics, ax';
            Con   = str2Con(source,',');
            for (i = 1; i <= conlen(Con); i++)
            {
                info(strfmt("%1",conpeek(Con,i)));
            }
        }

3-  str2Date() : Date in string type & return it as Date type static void Jobtest(Args _args)
        {
            str         input;
            Date        output
            ;
            input  = '05/30/2010';
            output  = str2Date(input,213);
            info(strfmt("%1",output));
        }
        123 : DMY
        213 : MDY

4- strIntOk () : Check a string which contain integer value static void Jobtest(Args _args)
        {
            str         input;
            boolean     output;
            ;
            input   = '1294';
            output  = str2IntOk(input);
            if(output)
                info(strfmt("Integer Value"));
            else
                info(strfmt("Not a Integer Value"));
        }

5- strEndsWith () : Check whether end characters are matching or not

static void Jobtest(Args _args)
        {
            str         input1,input2;
            boolean     output;
            ;
            input1  = 'dyanmics ax';
            input2  = 'ax';
            output  = strEndsWith(input1,input2);
            if(output)
                info(strfmt("OK"));
            else
                info(strfmt("Not OK"));
        }

6- strLFix  :  strLfix(str _str, int _lenght [, char _char])

Add character _char after string _str to obtain a string long _leng.

Default filler chararcer is ' '.

strLfix('aaa', 5, '_') //returns the text string 'aaa__'.

If _str is longer than _leght function return a substring of _str.

strLfix('dynamics ax',5);

//returns the text string ‘dynam'.