Top 10 issues discovered from Dynamics AX Health Check
A very informative blog post by Bertrand Caillet covering important points for Dynamics AX best practices for health check, and it contains a sub links under main points. I am glade to share it over my page.
1. Max Degree of Parallelism is not set to 1
2. No Pre-Allocation for Non Continuous Number Sequences with high consumption
3. Insufficient Auto Growth for Data and Log files
4. Processor bottleneck for Dynamics AX batch processing
5. Heavy Application database logging
6. Missing Clustered indexes
7. Wrong Index maintenance
8. Enabled debug in Production
9. Power Management set to Balanced
10. Kernel and Application are outdated and should be patched
The Premier Field Engineer team has been running several hundred of Dynamics AX Health Check worldwide and I thought it would be interested to share the most common issues discovered during onsite. Some of the following issues might look trivial but the reality is that despite all the literature that exists today on the solution Dynamics AX, we can still see same repeated patterns, especially when default settings are used in production. Please note there is no logical order in this list.
1. Max Degree of Parallelism is not set to 1
The default setting from SQL Server installation is zero. If OLAP applications are running on the same SQL Server instance, the risk is that all CPUs are consumed (% Processor Time) and therefore impact performance of the OLTP application. To prevent this, we recommend to set MAXDOP to 1. Please note that in some situation when SQL Server batches or queries are run, using some degree of query parallelism can be beneficial. In this case setting Max degree of parallelism to 2 or 4 is probably a better value to take into account both types of queries.
2. No Pre-Allocation for Non Continuous Number Sequences with high consumption
This is a crucial setting in the Dynamics AX application that needs to be reviewed every few months on production to match the usage of the number sequences. Basically, number sequences can be Continuous or Non Continuous. When they are non-continuous, you can allow pre allocation per ID and therefore reduce the database calls and improve performance. When consumption is high, like several ID per seconds, we have noticed Lock Escalation events on the table NumberSequenceTable. This is especially true when Dynamics AX batch are running and generate thousands of records for Journals Lines creation or Sales Order Invoicing. Please read this blog post to better estimate this consumption.
3. Insufficient Auto Growth for Data and Log files
The default Auto growth is By 1 MB for Data and By 10% for Log files. If the initial size is too small, you will notice frequent auto growth events, resulting in negative performance impact. In the SQL trace, you can monitor such event with ID and see the duration of each auto growth event. The goal is to set an appropriate Auto Growth value on the Dynamics AX and TempDB Data and Log files to prevent frequent auto growth events during daytime. You can monitor such events and their duration in milliseconds in the SQL Trace with ID 92 and 93. One recommendation is to set the Auto Growth with a greater value, from 200MB to 500MB.
4. Processor bottleneck for Dynamics AX batch processing
Most of the customer running Dynamics AX are enabling only one AOS instance for the batch processing and several AOS dedicated for rich clients load which therefore limit the number of batch thread to the number of logical processor available on that single AOS instance. Also the setting on the AOS batch is often the default one with 8 maximum batch thread and 24 hours a day schedule . A good ratio to calculate the number of thread is to multiply the number of cores by 2, but this depends on the processes running and should be validated in testing. To overcome this limitation, you can set different batch thread for different time of the day and enable more AOS for Batch processing at night. For example, you can dedicate one AOS for Rich client load from 8 am to 6 pm and leverage it for batch processing at night. You can read this blog post to learn how to tune the number of thread required for faster batch execution.
5. Heavy Application database logging
This is another important setting in the Dynamics AX application that often has negative impact when over utilized. This feature allows to track all CRUD operations (including Rename primary key) on any field and any table of the database. This information will be stored in the table SYSDATABASELOG. You should not use this feature to track automated transactions run in batch jobs. You can monitor the table growth of SYSDATABASELOG and estimate the most expensive table set up with database logging. Please also notice that enabling the logging on field like CreatedDateTime or ModifiedDateTime will change the default behaviour of InsertRecordSet from a single round trip to the database to a record-by-record operation. The three recommendations are:
- Define a retention policy to only keep the latest 3 months for example so that size of table SYSDATABASELOG remains small. You can find the clean-up form under Administration | Inquiries | Database log | Clean up log.
- Avoid heavy logging for transactional tables processed during the batch and prioritize activity that is absolutely necessary either for legal compliance or to meet other business requirements.
- Avoid logging the columns CreatedDateTime or ModifiedDateTime of any table.
6. Missing Clustered indexes
This might not be the most common issues but it is definitively one of the most impacting one. All tables in an OLTP database should have clustered indexes, and you should investigate them by looking at the highly active tables, tables frequently involved in blocking or deadlock situations, tables that frequently incur the overhead of forwarded rows. Because the database model is changing during the lifecycle of the application with new customization and because indexes are managed from the Application Object Tree, it is important to periodically check the missing indexes from SQL Server Statistics. You can use the query “4-Analyze_SQL_Indexes.sql” from Dynamicsperf version 1.1.6 to find all missing indexes.
7. Wrong Index maintenance
Following the Missing clustered indexes issue mentioned above, the index maintenance is clearly one of the unhealthiest issue for Dynamics AX. Having a bad index maintenance or no index maintenance at all will have the same consequences: when statistics are outdated, Query Plan will use SCAN instead of SEEK operations and their performance will be badly impacted. The level of fragmentation is not that critical as such for performance, but it should be part of the weekly maintenance. The following is a recommended example:
- Reorganize indexes that are larger than 1000 pages and are between 10% and 30% fragmented.
- Rebuild indexes that are larger than 1000 pages and more than 30% fragmented using a fill factor between 85% and 95% depending on the frequency of the job execution.
It is also strongly recommended to run Update Statistics regularly with FULL SCAN, or with at least a 50% sample, as well as having Auto_Create_Stats and Auto_Update_Stats enabled. If you are running SQL 2008 R2 SP1 or greater, you can also enable Trace Flag 2371.
8. Enabled debug in Production
In the Dynamics AX Server Configuration Utility, you should always disable the two settings that allow user breakpoint and global breakpoint to debug X++ code. Even though you delete all breakpoints in the Application Object Tree, you will still suffer from a clear performance degradation around 10%.
9. Power Management set to Balanced
This is an easy one but almost never implemented in production since the default power management is set to balance. Changing it to High performance is highly recommended for all Windows Server 2008 used in Production. You can see this setting from Control Panel – hardware – Power Options or with the following command: “powercfg –getactivescheme”.
10. Kernel and Application are outdated and should be patched
Last but not least, maintain your Dynamics AX solution as much updated as possible. For example, you can notice that latest Rollup for Dynamics AX 2009 SP1 is RU8 and is already one year old. Several hundred of Hot Fixes have been released since and it is a best practice to apply those to leverage the latest fixes from the Product Group. The two recommendations are:
- Patch the Binaries with the latest Kernel available. You can see the latest KB on the AX Support Blog. As of today, the latest one for Dynamics AX 2009 SP1 is build 5.0.1600.1824 from June 2013.
- For the Application, you should upgrade to latest RU available, but you can also proactively check the list of individual Hot Fixes relevant to your business logic.
As you can see this list is not exhaustive and we could elaborate hundred of issues discovered from existing Dynamics AX live instance, but I do hope it can help you remediate some of the current issues you may experience. I will also encourage you to read the great article written by Arvind Shyamsundar on MSPFE blog regarding the 10 top SQL Server Issues uncovered by the SQL Server Risk Assessment Program.
Finally, please contact your Microsoft Premier Technical Account Manager if you will like to receive guidance from the Premier Field Engineering.
Source: Dynamics AX in the Filed
Re-index Dynamics AX database
Our database is incremented by 1 GB daily, and we apply periodic clean up and re-index from dynamics ax client but it is not affect size so much, so we tried using SQL re-index through SQL visual studio but we noticed that the re-index is increased not decreased plus data size of table is increased also although number of records are the same before and after the re-index, any body faced this problem before?
Hint: we are using SQL Cluster (2 Nodes)
Create display Method in Dynamics AX Table
Let is say you have value you want to display in Ax report or Form, and this value not available in your table for example
We have salesTable in AX have salesStatus field and you want to display sales order status in your report or form, to achieve that you want to create display method in SalesLine Table to display sales order status in required form and report.
Display salesStatus getSalesStatus()
{
SalesTable _salesTable;
;
select salesStatus from _salesTable where _salesTable.salesId == this.salesId;
return _salesTable.salesStatus ;
}
Dynamics AX Performance Optimization Guide
Imparted from Here
Book Review - Dynamics AX Performance Optimization Guide
"Dynamics AX Performance Optimization Guide" - This book lives up to it's name and I would recommend it as an excellent resource and a handy guide to troubleshoot performance related issues to Dynamics AX and SQL. This book is targeted towards the technical audience.
Along with Dynamics AX, this book covers advanced troubleshooting tips with SQL Server which many of us might have not known before.
This book spans approx. 200 pages and it's divided into 8 chapters:
1. Understanding Dynamics AX - covering Dynamics AX Architecture, Sessions, Concurrency Control
2. Monitoring Hardware, Database and Dynamics AX - Touches on the key areas like Hardware Monitoring, Database Monitoring, Database I/O, Monitoring Dynamics AX
3. Setup and Configuration - This chapter extensively covers from a SQL Server perspective like Recovery models, Files and File Groups, SQL Server configuration, Database options
4. Common Dynamics AX performance problem - Disk I/O Bottlenecks, Memory Bottlenecks, Extensive Logging, Deadlocking
5. Optimizing Dynamics AX SQL Statement - Index Hints, Trace flags, Best Development Practices, Buffer and Caching mechanisms are covered in this chapter
6. SQL Server Performance and Compilation Optimizations for Dynamics AX - Resource Management, Auditing and Compliance, Performance Monitoring , Query Optimizations and Hardware Optimizations
7. Database Maintenance - Managing Indexes and Locking
8. Infrastructure and Hyper - V virtualization - Hardware Sizing, Sizing guidelines for Dynamics AX 2012 and 2009, Dynamics AX Server Virtualization benefits, Hyper-V best practices
As you might have already seen by now, this book covers extensively SQL Troubleshooting which i think is very critical to understand while dealing with performance issues. I think every technical person developing in Dynamics AX should have a good understanding of SQL Server from an administrative point of view and this book exactly does that. Whether you are a newbie in AX Development or an experienced developer, there's a lot to learn from this book.
I really appreciate Martin and Daniel for the effort they have put behind this book and I would recommend every technical person to grab a copy of the book if they could. Good job guys!
If you like to order a copy of this book, kindly go to this link http://www.amazon.com/Dynamics-Performance-Optimization-Guide-Microsoft/product-reviews/1481100750
Temporary Tables In Dynamics AX
In Microsoft Dynamics AX, a table typically maps to a corresponding table in the database. Temporary tables enable to you define table objects that are not persisted to the database. Define a table as a temporary table by doing one of the following:
-
Set the table's Temporary property to Yes at design time.
–or–
-
Call the setTmp method in X++ code.
A temporary table is held in memory until its size reaches 128 KB. The dataset is then written to a disk file. The disk file for a temporary table has the naming convention $tmp<nnnnnnnn>.$$$.
A temporary table is located in the Application Object Tree (AOT) under the Data Dictionary\Tables node, just like a persisted table. If a table is defined as temporary, you can use it just as you would a static table. All X++ DML statements can be run against a temporary table.
Note: It is a best practice to infix temporary table names with Tmp. This improves readability in code.
Scope A temporary table exists only while a record buffer variable that references the table exists. No memory is allocated to the temporary table until the first record is inserted. At that point, memory is allocated. Disk space is allocated, if it is needed. As soon as the record buffer goes out of scope, the memory is de-allocated and the disk file is deleted.
A temporary table resides on the tier where the first record is inserted. If a record is inserted on the server tier, memory for the temporary table is allocated on the server tier. If the temporary table exceeds 128 KB, a disk file is created on the server.
Adding data to temp table
To add data to a temporary table, you must define the record buffer and call the insert method. The following is a code example that uses the TmpCustLedger table.
static void TableTmpInsertRecord(Args _args)
{
TmpCustLedger custTmpLedger;
;
custTmpLedger.AccountNum = '1000';
custTmpLedger.Name = 'NameValue';
custTmpLedger.Balance01 = 2345000;
custTmpLedger.insert();
}
To free the memory and delete the file for the temporary table, set the record buffer variable to null, as follows.
custTmpLedger = null;
To populate a temporary table with data from a persisted table, use the setTmp method. The following code example copies all customers in Toronto to the temporary table.
static void CopyPersistedTableToTemp(Args _args)
{
CustTable custTable;
CustTable custTmpLedger;
;
custTmpLedger.setTmp();
custTable.recordLevelSecurity(true);
while select custTable where custTable.City == 'Toronto'
{
custTmpLedger.data(custTable.data());
custTmpLedger.doInsert();
}
}
Indexes
Indexes can be defined on a temporary table just as you would a persisted table. If a temporary table is created by copying a persisted table, the indexes are also copied to the temporary table. Indexes are very useful for retrieving data in temporary tables especially if the temporary table data is in a disk file.
Temporary table vs. Container
Microsoft Dynamics AX supports a special data type called a container. This data type can be used just as you would use a temporary table.
- Data in containers are stored and retrieved sequentially, but a temporary table enables you to define indexes to speed up data retrieval.
- Containers provide slower data access if you are working with many records. However, if you are working with only a few records, use a container.
- Another important difference between temporary tables and containers is how they are used in method calls. When you pass a temporary table into a method call, it is passed by reference. Containers are passed by value. When a variable is passed by reference, only a pointer to the object is passed into the method. When a variable is passed by value, a new copy of the variable is passed into the method. If the computer has a limited amount of memory, it might start swapping memory to disk, slowing down application execution. When you pass a variable into a method, a temporary table may provide better performance than a container.
Integrate Microsoft Dynamics AX analytic components with external data sources
Data warehouses are a popular solution for providing analytic capabilities to users. Until recently, data warehouses were the only reasonable solution for building robust analytic capability. However, as applications become easily interoperable and as technologies such as in-memory databases and OLAP become cost-effective and simpler to use, building a data warehouse is not the only solution to meet analytic requirements.
Table below presents several architecture options for integrating external data with the prebuilt
analytic solution; a data warehouse is just one of the options. The columns represent architecture options, whereas the rows represent the benefits and cost implications of each option.
When most data is in Microsoft Dynamics AX (assuming that Microsoft Dynamics AX is the
predominant source of data in the organization), you have two options.
The data mash-up option is best suited to an environment where capable users author and publish analyses for the use of others. This option relies on client tools such as Excel PowerPivot. Microsoft Dynamics AX 2012 enables Microsoft Dynamics AX queries to be published to data mash-up tools through OData feeds, or as data exports to Excel.
You can bring external data into Microsoft Dynamics AX either through services (data services consumed by means of inbound ports) or as batch jobs that are executed periodically to import data into tables. With this approach, external data is represented as read-only data within Microsoft Dynamics AX. The benefit to this approach is that external data appears as native Microsoft Dynamics AX data to Microsoft Dynamics AX tools. You can create analytics, reports, and inquiry forms that use the combined data.
A more complex approach involves integrating external data directly into the prebuilt BI solution.
With this option, a BI developer adds another data source to the prebuilt BI solution by using Business Intelligence Development Studio. Additional data tables are brought into the DSV by using the new data connection. It is possible to create dimensions and measures by using the new tables in the DSV.
The traditional ETL-based data warehouse option is suited to scenarios that require complex
transformations or large volumes of data. Although this option is more flexible in terms of capabilities, it is also the most expensive to implement and manage.
You might want to build a data warehouse to implement the following scenarios:
■ Integrate external data sources with Microsoft Dynamics AX data In this approach, the Microsoft Dynamics AX implementation serves as one of many corporate applications.
Although Microsoft Dynamics AX contains some of the corporate data, other systems contain
a considerable portion of the data. To make decisions, data must be combined across systems, and the data warehouse serves that need.
■ Incorporate legacy data into Microsoft Dynamics AX analytics Most organizations
migrate recent data when implementing Microsoft Dynamics AX. Legacy data is still
maintained in read-only instances of legacy applications. Although legacy data is no longer
used for operational purposes, it is required for historical trend analysis. A data warehouse
serves as the repository where legacy data is combined with current data.
Although Microsoft Dynamics AX 2012 does not directly support the creation of a data warehouse schema, the following artifacts generated in Microsoft Dynamics AX 2012 can be used to build a data warehouse:
■ The DSV generated as part of the prebuilt analytic solution can be used within SQL Server
Integration Services when an ETL package is developed to extract data from Microsoft
Dynamics AX.
■ Microsoft Dynamics AX document services can be consumed as data sources based on Simple Object Access Protocol (SOAP).
■ Microsoft Dynamics AX queries can be exposed as OData feeds.
Extend cubes [AX 2012]
you can customize the prebuilt analysis project relatively easily by using the SQL Server Analysis Services Project Wizard. But in some cases, you may want to make
deeper customizations. For example, you might want to:
■ Create a rich hierarchy, such as a parent/child hierarchy to model organizational units.
■ Add new KPIs.
■ Bring external data into the analysis project and create a custom dimension.
You can use Business Intelligence Development Studio to make these types of changes.
Because the prebuilt BI components are included in the AOT as an SSAS project, you can modify the project. To modify the prebuilt Analysis Services project, do the following:
1. In the AOT, expand the Visual Studio\Analysis Services Projects node.
2. Right-click the project that you want to modify, and then click Edit.
An Infolog message appears, stating that a copy of the SSAS project has been created and saved, as shown in Figure below
If SQL Server Business Intelligence Studio is installed, it will start and open the copy of the project.
Changes that you make to the project are not automatically saved to the AOT. You need to save the project and import it back into the AOT.
DSV
The DSV contains the table and view definitions that are used by analytic artifacts. Notice that the OLAP framework has implemented several query definition patterns in the DSV:
■ Financial dimensions that the wizard has added appear as custom query definitions in the DSV.
■ The OLAP framework has created query definitions corresponding to Microsoft Dynamics AX views.
■ The OLAP framework has added a reference relationship to resolve virtual companies, if your Microsoft Dynamics AX installation has virtual company definitions.
■ The OLAP framework has created views that make Microsoft Dynamics AX enumerations accessible
in all of the languages that have been added to the project.
Avoid modifying any of the framework-generated objects in the DSV. Any changes that you make to these objects are overwritten without warning the next time you update the project.
You may add your own objects to the DSV (for example, new query definitions. The Project Update option will preserve these objects.
In Microsoft Dynamics AX 2012 R2, do not implement any partition-specific logic in any of the query definitions. Otherwise, when the project is deployed to multiple partitions, the system may
generate processing errors. (Because the framework adds partition-specific logic to the DSV at deployment time, it may not apply the changes accurately to your query definitions.)
Data source
A data source has been created that points to the Microsoft Dynamics AX OLTP database.
Dimensions, measures, and measure groups
In Figure below , notice the dimensions that are included with the Microsoft Dynamics AX 2012 prebuilt BI solution, as well as the measures and measure groups.
For a list of measures and dimensions, see “Cube and KPI reference for Microsoft Dynamics AX 2012” at http://msdn.microsoft.com/en-us/library/hh781074.aspx.
KPIs and calculations
The SSAS project contains prebuilt KPIs and calculations. Microsoft Dynamics AX 2012 does not provide the capability to model KPIs and calculations in the AOT. You can modify these definitions or
add new ones directly in Business Intelligence Development Studio.
Customize cubes [AX 2012]
This post related to this post [Customizing the prebuilt BI solution]
Figure below shows the process for updating a cube. This post walk through each step in detail.
Choose the project to update
The first step is selecting the project to modify. You can select an SSAS project in the AOT or a project maintained on disk. The wizard performs basic validation of the selected project before you can proceed.
The update process is designed to ensure that you end up with a project that you can
deploy and process without any errors. If the selected project does not build (the most basic measure of validity), the wizard will not let you proceed to the next step.
Figure below shows the process for updating a cube. This post walk through each step in detail.
Choose the project to update
The first step is selecting the project to modify. You can select an SSAS project in the AOT or a project maintained on disk. The wizard performs basic validation of the selected project before you can proceed.
The update process is designed to ensure that you end up with a project that you can
deploy and process without any errors. If the selected project does not build (the most basic measure of validity), the wizard will not let you proceed to the next step.
Customizing the prebuilt BI solution
As you have seen in the previous post, it’s relatively easy to implement the prebuilt BI solution in Microsoft Dynamics AX 2012. But regardless of how good the prebuilt BI solution is, you may want to change the functionality to suit your needs.
These changes can be divided into three broad categories:■ Configuration Although the prebuilt BI solution is designed to cover all of the functionality in Microsoft Dynamics AX 2012, you may have implemented only certain modules. Even within those modules, you may have chosen to disable certain functionality. In Microsoft Dynamics AX, license codes and configuration keys govern the availability of modules and functionality, respectively. (For more information, see Chapter 11, “Security, licensing, and configuration.”) Configuration keys correspond to functionality within modules. They can be enabled or
disabled.
If you do not activate certain license codes or if you disable certain configuration keys, the Microsoft Dynamics AX user interface configures itself by removing content that is associated with those elements. In this case, you may need to remove the corresponding analytic content. (However, because the prebuilt BI solution draws data from across Microsoft Dynamics AX, this content will not be hydrated with data in any case.) You can use the SQL
Server Analysis Services Project Wizard to remove the corresponding content from the prebuilt cubes, so that you do not have to remove the irrelevant content manually yourself.
■ Customization You might want to add additional calendars and financial dimensions, and also new attributes and measures, to the prebuilt cubes. The SQL Server Analysis Services Project Wizard lets you perform the most frequent customizations with a step-by-step approach, without requiring BI development skills.
■ Extension At some point, you may want to develop extensions to prebuilt cubes by using the SQL Server BI development tools. Table below lists categories of customizations, summarizes the types of changes that you can make, and lists the skill level, time, and tools required to make those types of changes.
Configure analytic contentAs previously explained, you can configure the predefined analytic content to reflect configuration changes in Microsoft Dynamics AX in a matter of minutes by using the SQL Server Analysis Services Project Wizard. In Microsoft Dynamics AX 2009, this process had to be performed manually. This process required BI development skills and a day or two of spare time. Microsoft Dynamics AX 2012 dramatically simplifies this process by introducing the following three improvements:
■ Static schema Historically, Microsoft Dynamics AX has had a schema whose shape changed depending on licenses and configuration keys. That is, when a configuration key was turned off, the database synchronization process dropped tables and data that were deemed invalid.
This caused prebuilt cubes (that rely on a static schema in the underlying database) to break at processing time. Unlike its predecessor, Microsoft Dynamics AX 2012 has a static schema.
So, when configuration keys are disabled, the database schema no longer changes. This means that prebuilt cubes can continue to be processed without generating errors. (They will, for example, contain empty measures, because the corresponding tables have no data).
■ Improved modeling capabilities in the AOT The Microsoft Dynamics AX 2009 OLAP framework did not allow advanced modeling of constructs in the AOT. As a result, developers had to implement any functionality that was lacking directly in an SSAS project. In Microsoft Dynamics AX 2012, a larger portion of analytic content is modeled in the AOT. Therefore, configuring the content can be done much more easily by the framework.
■ Wizard-driven user interface The six different forms that were necessary in Microsoft Dynamics
AX 2009 have been replaced by a single step-by-step wizard that guides you through various activities.
To configure the prebuilt BI project, you must have developer privileges in Microsoft Dynamics AX.
This step modifies the project so that irrelevant measures, dimensions, and entire cubes are removed
after the process is completed. The modified project will be saved in the AOT in your own layer.
To configure the project, start the SQL Server Analysis Services Project Wizard, and then select the Configure option. You then need to select the project to configure. Select the Dynamics AX project to configure the prebuilt project, and step through the wizard. For step-by-step instructions, see the “How to: Configure an Existing SQL Server Analysis Services Project” at http://msdn.microsoft.com/en-us/library/gg724140.aspx.
If you also deploy and process the project, you should notice the following changes:
■ Cube content (such as measures and dimension attributes that source data from tables that are affected by disabled configuration keys) is deleted from the project. You may see that entire cubes have been removed if the corresponding content has become invalid.
■ KPIs and calculated measures have been removed in cubes that depend on disabled measures and dimension attributes.
■ OLAP reports in Role Centers that source data from cubes that have been removed no longer appear on the Role Center page. If a user intentionally adds such a report to the Role Center, the report displays a warning message and will execute.
■ KPIs and measures that were removed no longer appear in the Business Overview web part.
These changes can be divided into three broad categories:■ Configuration Although the prebuilt BI solution is designed to cover all of the functionality in Microsoft Dynamics AX 2012, you may have implemented only certain modules. Even within those modules, you may have chosen to disable certain functionality. In Microsoft Dynamics AX, license codes and configuration keys govern the availability of modules and functionality, respectively. (For more information, see Chapter 11, “Security, licensing, and configuration.”) Configuration keys correspond to functionality within modules. They can be enabled or
disabled.
If you do not activate certain license codes or if you disable certain configuration keys, the Microsoft Dynamics AX user interface configures itself by removing content that is associated with those elements. In this case, you may need to remove the corresponding analytic content. (However, because the prebuilt BI solution draws data from across Microsoft Dynamics AX, this content will not be hydrated with data in any case.) You can use the SQL
Server Analysis Services Project Wizard to remove the corresponding content from the prebuilt cubes, so that you do not have to remove the irrelevant content manually yourself.
■ Customization You might want to add additional calendars and financial dimensions, and also new attributes and measures, to the prebuilt cubes. The SQL Server Analysis Services Project Wizard lets you perform the most frequent customizations with a step-by-step approach, without requiring BI development skills.
■ Extension At some point, you may want to develop extensions to prebuilt cubes by using the SQL Server BI development tools. Table below lists categories of customizations, summarizes the types of changes that you can make, and lists the skill level, time, and tools required to make those types of changes.
Configure analytic contentAs previously explained, you can configure the predefined analytic content to reflect configuration changes in Microsoft Dynamics AX in a matter of minutes by using the SQL Server Analysis Services Project Wizard. In Microsoft Dynamics AX 2009, this process had to be performed manually. This process required BI development skills and a day or two of spare time. Microsoft Dynamics AX 2012 dramatically simplifies this process by introducing the following three improvements:
■ Static schema Historically, Microsoft Dynamics AX has had a schema whose shape changed depending on licenses and configuration keys. That is, when a configuration key was turned off, the database synchronization process dropped tables and data that were deemed invalid.
This caused prebuilt cubes (that rely on a static schema in the underlying database) to break at processing time. Unlike its predecessor, Microsoft Dynamics AX 2012 has a static schema.
So, when configuration keys are disabled, the database schema no longer changes. This means that prebuilt cubes can continue to be processed without generating errors. (They will, for example, contain empty measures, because the corresponding tables have no data).
■ Improved modeling capabilities in the AOT The Microsoft Dynamics AX 2009 OLAP framework did not allow advanced modeling of constructs in the AOT. As a result, developers had to implement any functionality that was lacking directly in an SSAS project. In Microsoft Dynamics AX 2012, a larger portion of analytic content is modeled in the AOT. Therefore, configuring the content can be done much more easily by the framework.
■ Wizard-driven user interface The six different forms that were necessary in Microsoft Dynamics
AX 2009 have been replaced by a single step-by-step wizard that guides you through various activities.
To configure the prebuilt BI project, you must have developer privileges in Microsoft Dynamics AX.
This step modifies the project so that irrelevant measures, dimensions, and entire cubes are removed
after the process is completed. The modified project will be saved in the AOT in your own layer.
To configure the project, start the SQL Server Analysis Services Project Wizard, and then select the Configure option. You then need to select the project to configure. Select the Dynamics AX project to configure the prebuilt project, and step through the wizard. For step-by-step instructions, see the “How to: Configure an Existing SQL Server Analysis Services Project” at http://msdn.microsoft.com/en-us/library/gg724140.aspx.
If you also deploy and process the project, you should notice the following changes:
■ Cube content (such as measures and dimension attributes that source data from tables that are affected by disabled configuration keys) is deleted from the project. You may see that entire cubes have been removed if the corresponding content has become invalid.
■ KPIs and calculated measures have been removed in cubes that depend on disabled measures and dimension attributes.
■ OLAP reports in Role Centers that source data from cubes that have been removed no longer appear on the Role Center page. If a user intentionally adds such a report to the Role Center, the report displays a warning message and will execute.
■ KPIs and measures that were removed no longer appear in the Business Overview web part.
Implementing the prebuilt BI solution
Traditionally, BI solutions are implemented during the second or third phase of an Enterprise Resource Planning (ERP) implementation project. Needless to say, project fatigue sets in (and the budget gets exhausted), and subsequent phases are postponed or delayed. BI implementation is complex and involves the integration of many components. Also, the skill set required to implement a BI solution is distinctly different from the skill set required to implement an ERP system. Often, implementation of the BI solution involves engaging a different partner or consultants. All of these factors contribute to postponing the BI implementation.
Microsoft Dynamics AX 2012 simplifies the implementation of a BI solution, so that all Microsoft Dynamics AX 2012 partners and customers (regardless of whether they have access to BI specialists) can implement the prebuilt BI solution when they implement the ERP functionality.
In Microsoft Dynamics AX 2012, the default SQL Server Analysis Services (SSAS) project is a
first- class citizen of the Application Object Tree (AOT), as are other SSAS projects that you create in the AOT. This means that SSAS projects derive all of the benefits of being residents of AOT.
■ SSAS projects respect the layering concept. This means that an independent software vendor (ISV) or partner can distribute a customized version of an SSAS project that adds additional analytic components to the solution that is included in the SYS layer.
■ You can import and export SSAS projects to and from different environments as part of a model (by using models or .xpo files).
■ SSAS projects respect the version control capabilities offered by AOT-based artifacts.
When you deploy a project by using the SQL Server Analysis Services Project Wizard, which is new in Microsoft Dynamics AX 2012, the wizard selects the project in the highest layer for deployment.
If you examine the Visual Studio Projects node in the AOT, will see the default SSAS project that is included with Microsoft Dynamics AX 2012, as shown in Figure below. If you have any customizations at higher levels, they are also displayed.
Implementing the prebuilt BI solution consists of the following steps:
1. Implement the prerequisites.
2. Configure an SSAS server.
3. Deploy the cubes.
4. Process the cubes.
5. Provision users so that they can access the analytic data.
The following sections describe each step in further detail.
1- Implement the prerequisites
Before you implement the analytic components in the prebuilt BI solution, the following Microsoft Dynamics AX core components should be in place:
■ At least one AOS instance must be implemented.
■ The Microsoft Dynamics AX Windows client must be implemented, and the initialization checklist must be completed.
■ The Enterprise Portal web client must be configured.
If you are implementing the analytic components on a development or test instance, you might not implement a scale-out architecture. However, if you are implementing these components in a
production system, you may want to implement a redundancy or load balancing infrastructure. You need to configure the clustering or Network Load Balancing (NLB) solution before you implement the
analytic components.
2- Configure an SSAS server
This step configures a given SSAS server for the Microsoft Dynamics AX 2012 analytic components.
To do so, run the Configure Analysis Extensions step in the Microsoft Dynamics AX Setup wizard on the SSAS server that hosts Microsoft Dynamics AX 2012 cubes.
Running the configuration step should take you a few minutes. This function does the following:
■ Ensures that the SSAS server has all of the necessary prerequisites to host Microsoft Dynamics AX 2012 cubes.
■ Adds the Business Connector (BC) proxy user as an administrator of the SSAS server. This step is required to enable AXADOMD data extensions to operate without the use of Kerberos
constrained delegation.
■ Allows you to add a read-only user account to the Microsoft Dynamics AX 2012 database for processing cubes (you should specify a domain account whose password does not expire).
3- Deploy cubes
When you deploy cubes, Microsoft Dynamics AX generates and processes an OLAP database by using the metadata definition contained within the Analysis Services
project that is included with Microsoft Dynamics AX 2012. The result is an OLAP database that contains
Microsoft Dynamics AX cubes that are referenced by analytic reports and Role Centers.
In a Microsoft Dynamics AX 2012 R2 environment where there is only a single partition, the deployment step generates a single OLAP database that sources data from the Microsoft Dynamics
AX OLTP database. In a multiple-partition environment, the deployment step generates multiple OLAP databases that correspond to each partition. Figure below shows the deployment process both
in a single-partition and multiple-partition environment.
You use the SQL Server Analysis Services Project Wizard in the Microsoft Dynamics AX 2012 client to deploy, process, and in some instances, update cubes. To deploy the cubes, you must have the
right to deploy projects to the SSAS server. If you are also processing the cubes, you must have the right to read the Microsoft Dynamics AX 2012 OLTP database.
To start the SQL Server Analysis Services Project Wizard and deploy cubes, do the following:
1. In the Development Workspace, on the Tools menu, click Business Intelligence (BI) Tools > SQL Server Analysis Services Project Wizard.
2. On the Welcome page, click Next, and then select the Deploy option on the next page, as shown in Figure below
3. On the next page, you select an SSAS project to deploy—in this case the Dynamics AX project.
You can select a project in the AOT, as shown in Figure below, or you can select a project that is saved on a disk.
4. Next, you specify the SSAS server to deploy the project to, the SSAS database you want to use, and whether you want the project to be processed after deployment .
By default, the wizard uses the SSAS server that you configured earlier, but you can select any server to deploy the project to.
Deploy cubes in an environment with multiple partitions
As mentioned earlier, in a Microsoft Dynamics AX 2012 R2 environment with multiple partitions, the SQL Server Analysis Services Project Wizard generates an OLAP database for each partition. You can
use the wizard to select the partitions for which OLAP databases are created, as shown in Figure below
In this case, the SQL Server Analysis Services Project Wizard deploys the SSAS project to multiple OLAP databases. In each database, <partitionkey> is added as a suffix to the name of the OLAP
database.
Also, within each OLAP database, the data source view (DSV) is modified so that a partition filter is applied to all queries. Figure 10-8 shows the architecture of an environment with multiple partitions.
In all cases, the SSAS project in the AOT is partition-unaware, whereas the OLAP databases that are deployed are partition-specific. The SQL Server Analysis Services Project Wizard handles the step of
making sure that each OLAP database is wired to read data only from the corresponding partition in Microsoft Dynamics AX. This is a departure from the behavior of Microsoft Dynamics AX 2012. You
need to be aware of the following implications:
■ If you deploy Microsoft Dynamics AX SSAS projects by using Analysis Services tools, such as the Deployment Wizard or Business Intelligence Development Studio, the resulting OLAP
database is not partition-aware. In other words, cubes will aggregate data across partitions.
■ If you want to extend an SSAS project, always check out and modify the project in the AOT.
Do not customize a project associated with a specific partition by importing the project directly in Business Intelligence Development Studio. The Deploy function in the wizard will overwrite any partition-specific customizations that you have made directly on the server.
■ If you add custom query definitions in the DSV, the wizard adds where clauses to each select statement that restrict rows from other partitions.
4- Process cubes
The SQL Server Analysis Services Project Wizard lets you process deployed cubes directly. However, before processing, the wizard also runs through several prerequisite checks to ensure that cube
processing will not fail later. If you are using demo data, you can ignore these preprocessing warnings and have the wizard process the cubes.
While the project is being processed, the wizard displays a progress page. When processing is complete, click Next, and the wizard will show the completion screen.
5- Provision users in Microsoft Dynamics AX
After you deploy and process Microsoft Dynamics AX cubes, you must grant users permissions to access them. Provisioning users involves two activities:
■ Associate an appropriate user profile with each Microsoft Dynamics AX user.
■ Provide Microsoft Dynamics AX users with access to the OLAP database.
Components of the Microsoft Dynamics AX 2012 BI solution
Figure below shows a simplified architecture diagram of the BI solution that is included with Microsoft Dynamics AX 2012. In the figure, the Microsoft Dynamics AX 2012 logical architecture has been
simplified to highlight only the components that are relevant to the BI solution.
The solution is divided into three tiers:
■ Data tier Contains sources of data, such as the Microsoft Dynamics AX 2012 operational database, often referred to as the online transaction processing (OLTP) database.
■ Integration tier Contains the Application Object Server (AOS), programming interfaces, and staged data, such as Microsoft Dynamics AX 2012 cubes, that serve as the database for
analytical reporting. (This tier is called also middle tier in from “Architectural overview.” perspective.
■ Presentation tier Contains tools and user interface elements that users can use to interact with data.
Imparted from Inside Dynamics AX 2012 Book
Reporting service deployment error for Dynamics ax 2009
Error: “THE FOLLOWING COMPONENTS HAVE NOT BEEN INSTALLED OR ARE NOT CONFIGURED CORRECTLY: AL.EXE, MICROSOFT DOMAIN – SPECIFIC LANGUAGE TOOLS”
This error message indicates that the following software components are not installed:
• Windows SDK for Windows Server 2008 and .NET Framework 3.5
• Microsoft Visual Studio 2008 Shell (isolated mode)
Error: “THE MICROSOFT.DYNAMICS.CLRBRIDGE.DLL FILE IS NOT LOADED”
1. Close the Microsoft Dynamics AX Reporting Project Deployment form.
2. click Start Menu ---> Dynamics ax folder ---> right click on “Dynamics Ax 2009 reports Deployment then “ then run as Administrator
Troubleshooting blocked SPIDS in AOS
In this blog post I will provide some details on how the AOS server manages it SQL connections and some tips on troubleshooting blocked connections in the database. All of my description is based on the SQL Server backend but similar techniques are applicable for Oracle also, only the DB tools are different. Let’s first start with quick primer on SQL connection management inside Ax.
When connecting to SQL Server, Dynamics uses ODBC APIs to connect to the database. There is a cost in establishing the connection and logging in the user , hence the AOS uses a connection caching mechanism that allows recycling of connections when not in use. Internally, the AOS keeps track of 3 types of connections which are:
· Regular application connection – All application code use this connection.
· RECID connection – There is a dedicated connection to the SystemSequences table. This is used by the RECID allocator inside the AOS.
· Read Only/SysLastValue connection – This is a shared connection that is used for SysLastValue and read only queries.
Note, that the AOS internally distinguishes between these connection types but externally when looking in the database or in the application it is not possible to differentiate between them easily. In fact if a particular connection is cached and then reused again it might end up being reused as a different type of connection than its original type although there are some restrictions on the types when reusing the connections
The connection cache is implemented as a FIFO queue where the connection is stored while it is not being used. By not being in use I mean that there is no active SQL statement from the AOS to the database backend using that connection. The AOS also keeps track of the state of the connections before putting them in the cache for reuse. If the connection is bad due to connectivity errors to the database or any other errors that would cause errors in the future, then that connection will not be reused and it will deleted which will close the connection to the database.
Now let’s see how to look at some of this information from outside the AOS. There are a variety of ways to look at the active connections in a database server. For our discussion we will consider two alternatives, one using the tools provided by SQL Server and the other from within Ax itself. Let’s first look at the option or using SQL Server. My favorite tool for a quick overview of connections is the “Activity Monitor” tool that is available in the SQL Server Management Studio. This tool can be found under the Management folder in the object explorer. There are lots of columns for each active connection but the Process ID (SPID) is the one of interest to us. When you first start an AOS server you will see 2 SPIDS which have “Microsoft Dynamics Ax” under the Application column. These are the connections which are currently opened by the AOS and they could be either active or an inactive connection in the cache.
SQL Server 2005
SQL Server 2008 R2
From inside Ax, you can see some of the above information using the “Online Users Form”. The Client Sessions tab has a column called SPIDs that shows the connections that are currently active on a particular session. I should make you aware that you don’t get to see the whole picture when using this form. To start with you only see the active connections that are assigned to a session. This explains why sometimes you will not see any value in the SPIDs columns for some of the users. Basically it means that the user does not have any active connection to the database at that instant of time. Another limitation is that the SPID will be populated only if the client where you are viewing the form is connected to the same AOS as the other session. So if you have multiple AOS’ in a cluster you will not see all the active SPIDs in the system by opening a single instance of the online users form. You will have to open the form in separate clients each connected to a different AOS.
Now let’s look at how we can use some of the above information to troubleshoot scenarios where sessions are blocked in the database and how to get rectify the situation. You can again start with the Activity Monitor to look lookup the SPID of the blocked session. The activity monitor also gives you the SPID that it blocking any other SPID and also the DB resource that is causing the contention. You can then use the online users form to determine the owner of the session in AX for the blocked and blocking SPIDs. The form provides an ability to terminate an existing session in Ax. But you have to be careful in terminating the correct session. If you try to terminate the blocked session you will see that the online users form changes the status to “Ending – Blocked”. This state indicates that the AOS tried to terminate the session but it was not successful since this session has some open resources and it cannot be safely terminated. One option to solve this situation is to terminate the session that is blocking the other session(s). If the termination is successful, the database connection is closed and this will free up the DB resources for the blocked session(s).
In the online users form when you try to terminate a session sometimes you might see the status change to “Ending – Waiting for AOS”. This happens when you terminate a session that is not in the same AOS as the client that sent the terminate request. In this case the request is placed to the other AOS and it monitors for terminated session in the background and will it terminate it eventually when it processes the request.
In addition to the above techniques there are alternate techniques to troubleshoot blocking in the SQL Server database. You can query the database provided Dynamics Management Views (DMVs) to get more details on the resources that are being consumed or blocked.
I hope you found the above information useful and interesting. Let us know if you would like more information in related areas on how the AOS works and options to manage it. We would also like to hear any suggestions for improvements in any of the areas covered in this topic.
Subscribe to:
Posts (Atom)