Full text indexes in AX 2012Continue reading
Dynamics AX 2012 uses full-text indexes maintained by Full-Text Engine for SQL Server.
Full-text indexes allow for quick searches over large volume of text data (Dynamics AX Memo fields) or even documents. You can build indexes over columns that store up to 2GB of data (nvarchar(max)).
Full-text index stores information about significant words and their location within one or more columns of a table. It is possible to define an SQL thesaurus in order to enable searches of words of similar meaning, e.g. you can search against word ‘vehicle’ and SQL will return records containing ‘cars’ and ‘bike’.
In AOT set TableGroup property to Main.
Under TableName > Full Text Indexes create new Full Text Index. Give it appropriate name and drop a field to this index. In the presented example field Notes of string size (Memo) is used.
Optional: please observe the full text catalogue created in AX Business Database.
Please edit a thesaurus for appropriate language, you will find them in
[SQL installation dir]\MSSQL11.MSSQLSERVER\MSSQL\FTData. For EN-US (1033) file name is tsenu.xml.
Find the contents used in this example below
<XML ID="Microsoft Search Thesaurus">
Load thesaurus to SQL by executing following T-SQL script
EXEC sys.sp_fulltext_load_thesaurus_file 1033;
Usage in X++ source code
To use full text indexes in X++ source code you need to create QueryBuildRange object with type FullText:
static void DevI_Ch2_FullTextIndex_Demo(Args _args)
query = new Query();
queryBuildDataSource = query.addDataSource(tableNum(DevI_Ch2_FullTextIndexed));
queryBuildRange = queryBuildDataSource.addRange(fieldNum(DevI_Ch2_FullTextIndexed, Notes),
queryRun = new QueryRun(query);
devI_Ch2_FullTextIndexed = queryRun.getNo(1);
Please observe that I search by ‘vehicle’ phrase which does not exist in the table. But thanks to thesaurus relevant records are found. If no thesaurus is employed full text searches will work but no similar words will be found.
Find data used in the example:
The outcome from script DevI_Ch2_FullTextIndex_Demo:
Please note that full text index rebuilding process takes place after transactional part of a T-SQL command. This means that when you query database immediately after inserting or updating, the engine will use ‘old’ full text index (will not find records or will find records that are not relevant anymore). Full text index is synchronised with database in asynchronous mode.
Implementing a new ERP system in an organisation is always a challenge. Users question whether they will be able to cope with analysing the data flow in an integrated system without external support. The accountant wonders whether all the set-up accounts are correct. The chief technologist cannot get rid of troublesome thoughts about whether the routings and boms are set up optimally. This is why it is so important to have a reliable service team to help the company control the system in the post-implementation stage. In this article, we take a look at the issues surrounding the implementation of an ERP system and the benefits of maintaining proactive service support.
328.77 million terabytes of data are generated every day - this is the estimate for the first quarter of 2023 (1). These are massive amounts of data on a global scale. On a smaller scale, such as an enterprise, it's difficult to estimate because it depends on the organisation in question, but one thing is certain - collecting, processing and analysing this data is the key to business success today. Why is the Data Lake service so important for data analysis and reporting in Microsoft Dynamics 365 Finance and Operations? How can you tame the data by integrating Microsoft Power BI analytics with Data Lake? We have drawn up some tips.
The retail industry is still dealing with the effects of the pandemic that lasted almost three years. In addition to this, further global events are causing disruption to the supply chain or financial stability of many retailers globally. As the industry confronts new challenges shaped by economic and geopolitical factors, it also faces trends influenced by changing customer expectations and needs. Here are 5 developments and trends to watch.
An interactive, AI-powered support for sales, customer service, marketing, and supply chain - Microsoft Dynamics 365 Copilot leverages generative AI and natural language processing technology to perform simple, yet time-consuming daily tasks that workload employees but can be automated.