As a Dynamics 365 business central Navision expert, you know that getting actionable insights from your data is critical.
While Business Central provides some great embedded analytics options like charts, Power BI, and more, sometimes you need to go deeper.
This is where custom Kusto queries come in.
Kusto query language allows you to write advanced queries to slice and dice your BC data in nearly limitless ways.
As someone who relies on data to make business decisions, it has become an indispensable tool in my analytics toolkit.
In this post, I’ll walk you through real-world examples of how custom Kusto queries have supercharged my data analysis in Dynamics 365 Business Central.
I’ll also provide step-by-step instructions on how you can start writing your own Kusto queries today. Let’s get started!
My Journey with Kusto Queries for D365BC Data Analysis
I first learned about Kusto queries a few years ago when I was trying to analyze sales data by region and product category over time.
The built-in reporting options in BC gave me a high-level view, but I wanted to slice the data in different ways to uncover deeper insights.
After some googling, I discovered Kusto query language, and it was a total game-changer!
I was able to write complex queries to aggregate and filter my BC data just how I needed it.
Since then, I now used Kusto queries for things like:
- Comparing budget vs. actuals by department
- Analyzing profit margins by customer segment
- Identifying seasonal sales trends by product
- And more!
With some SQL background, I found Kusto query syntax straightforward to learn. The ability to join data from multiple BC tables has proven invaluable.
I can now do in minutes advanced analytics that would’ve taken hours in Excel.
Step-By-Step Instructions for Writing Kusto Queries for BC
Ready to unlock your BC data analytics? Here is a step-by-step guide to start writing your own Kusto queries:
1. Enable Kusto Query Log Data in BC
First, you need to turn on the logging of your BC data to Azure Data Explorer. This provides the raw data for your Kusto queries.
In BC, go to Administration > Application Settings > Enable Kusto Query Log Data
2. Install the Kusto Query Tool
Go to Azure Data Explorer and install the Kusto query tool. This gives you an interface to write and test your queries.
3. Connect to Your BC Data Cluster
In the query tool, you’ll need to connect to your BC data cluster. You can find your cluster URI in BC under Administration > Application Settings > Kusto Query Log Data.
4. Start Writing & Testing Queries
Now the fun begins! Refer to Kusto query language documentation as you write your first queries. Test them out on your real BC data to ensure you get the results you expect.
Some queries to get started:
- Show all sales order lines for 2022:
<!—->
BCLog_SalesHeaderOrderLines
| where Timestamp > datetime(2022-01-01) and Timestamp < datetime(2023-01-01)
- Show total revenue by customer country:
<!—->
BCLog_SalesHeaderOrderLines
| summarize Revenue=sum(LineAmount) by BillToCountryRegionCode
| Order by Revenue desc
The possibilities are endless! With some practice, you’ll be slicing and dicing your BC data like a pro in no time.
Take Your BC Analytics to the Next Level with Kusto
In today’s highly competitive business landscape, unlocking insights from your ERP data through reporting and analytics is more important than ever. As you’ve seen, custom Kusto queries give you the power to analyze your Dynamics 365 Business Central-Navision data in nearly unlimited ways.