Last week Microsoft organized the #BCAIHackathon initiative, a 3 days dedicated to create innovative solutions using generative AI in Dynamics 365 Business Central and (probably) to bring new ideas on how to apply AI in useful scenarios inside the ERP.
In the previous days I had some discussions with my (not AI-generated) friend Dmitry Katson about this initiative and we decided to be part of that event in a different way. Instead of simply create some nice “automations” using the Copilot toolkit in AL, why not doing something really new and (maybe) revolutionary for the Business Central platform?
One of the feature where Dynamics 365 Business Central lacks a bit is certainly searching data. Searching in Business Central is done using a full-text search, with all its related limitations. Why not having something different, also usable from AI-based automations inside Business Central?
We decided to work on implementing a semantic search, where data is retrieved by what you mean and not by what you write.
Dmitry has wrote today a post describing the high level overview of our solution, you can read it here and also see it in action.
I don’t want to go into technical details on how we implemented this solution for now, but the core was to transform Business Central into a vector database and then using some vector similarities functions we created to find relevant results. The output of this work was something extremely cool as you can see from Dmitry’s videos. This solution was entirely built by using AL language and Azure OpenAI features, but with something that it’s not actually present in the AI framework in AL.
But our real goal was just not only to do something cool to use… we decided to also demonstrate that if something will be done at the platform level too (alias in the backend), results could be scalable and impressive… also with big data!
So what have we done?
After implementing a semantic search functionality in AL, we decided to push the limits and also create a second version of the semantic search app, now working also at the SQL layer.
This second version (targeted to work with big amount of data) had two “branches”:
- A first version where we’ve done at the SQL level only the semantic search, but using vectorizations from AL.
- A second version where we’ve done semantic search at SQL level by using custom functions in SQL and also SQL native features like clustered columnstores on vectors.
Why have we used clustered columnstores?
Clustered columnstore indexes are most beneficial when your queries involve a significant portion of the overall data stored in the table. If your analytic queries consistently use 90-100% of the full table, a columnstore index can greatly improve performance. Also, if you have columns with similar data (like vectors) or data that compresses at a high ratio, clustered columnstore indexes can provide significant storage and resource (memory, CPU) savings.
Results of this experiment (or “hack”) was that:
- If all the vectorization is done in AL and only the semantic search is done in SQL, results are good but only about 20-30% faster than doing all in AL.
- If semantic search is done in SQL by using platform features like clustered columnstore indexes, the things changes a lot (also with big data).
Here is a quick video I’ve recorded showing the semantic search in Al and using the second SQL-based solution, working on a database with a big amount of data and tables indexed (more than 3 millions of vectors):
As you can see, we moved the semantic search capability from about 10 seconds to milliseconds!!
I think it’s impressive! Now you can see that we can search on every data with every word and indexing every table. And then we can create a full AI-based Sales Order entry experience like we’ve done in our final part of the demo, where we create a sales order in Dynamics 365 Business Central from a text written in Microsoft Forms like “I want to order 3 blue t’s in M and 4 in S“.
What are the messages that I personally would like to launch after our #BCAIHackathon ?
- Embeddings hold the key to ‘interpretability’ in AI. They allow models to understand semantics of inputs and generalize patterns across similar inputs. Without embeddings, models could only understand inputs at face value.
- Working at the platform level to support AI features is key in order to work with big data applications (like in an ERP). We cannot pretend and force to always do everything in AL if we want performance and scalability.
The last point is honestly my mantra always (not only for AI-based features). As I said always, there’s a big ecosystem outside the ERP box that can be used in order to create scalable solution, and AL is not often borned to create extremely scalable solutions.
You can do good things with AI and AL today. But just start using something outside the traditional ERP box and you can do really magical things just now…
As a conclusion to this post: thanks to my team for working with me on this idea, we had some funny days together and I think we’ve created something really new and “game changer”.
P.S. Yes, I wrote an extension with target = onprem to demonstrate the SQL-based features we implemented and the possible improvements if something will be done at the platform level, so I have to go and purify myself now…
Stefano Demiliani 