Analytics, BI, and reporting in Business Central from 2020 until now - part 3 (2022 release wave 1+2)

This is part 3 in a series of blog posts about Analytics, BI, and reporting in Dynamics 365 Business Central from 2020 until now. In this post, I will tell stories from the 2022 release waves 1+2.

If you missed it, maybe start from the beginning. Open part 1 in a new browser tab

Analytics, BI, and reporting in Business Central from 2020 until now - part 1 (2020 release wave 1+2)

and then get back here.

Focus areas in 2022

Let's rewind time to 2022. The focus on making the product working the cloud, making the web client good to great, and moving the developer experience from C/AL to AL, was getting there. So we were finally able to direct some development resources towards more (end) user-oriented features: financial reporting and a new kid on the block: Excel layouts.

Cloud scale (continued)

The final piece in the scale puzzle (for reporting) was to move the rendering part of the server to be able to run as a separate service. We had seen examples of reports that could take up a full CPU core and a lot of memory when running and while this happened, other users running UI sessions would experience degraded performance. When ripping out a piece of code from the server, we also had to find a way to run the reporting service on-premises (here it just runs as a separate windows service), an example of a new design principle that we have to deal with from now on: for every feature we do, we need to ask ourselves "how will this work on-premises?"

Partially fixing the "mysterious slowness problem"

The work to move report rendering to a separate service had a build-in positive surprise: partially fixing the "mysterious slowness problem". What is that? Well, for some time, we had struggled with a performance problem that was very difficult to reproduce for our developers: sometimes users experienced sluggishness in the UI. It was not related to specific scenarios, not related to peak hours or specific regions, and it happened when the servers had plenty of CPU resources to use. We decided to setup some massive test suites that would mimic tons of user requests, report renderings, and web service requests, run them in cloud infrastructure, and collect lots of performance data from Windows counters. And suddenly we saw a pattern: when the Business Central server (the AL runtime) had consumed a lot of memory and when Windows needed to do garbage collection (to clean up memory no longer in use) of a certain type of memory allocations, then Windows would pause the server process for up to 15 seconds to perform this task. Then we noticed that report rendering used a lot of memory of the type of memory that caused process pauses. By moving report rendering to a separate (Windows) service, these pauses would not affect the AL runtime, it would only pause report rendering workloads. There was much celebration in the shiproom when we presented this finding. We are still not done fixing the "mysterious slowness problem", but moving report rendering workloads out of the AL runtime solved a big chunk of this issue.

Excel layouts for reports

Ever since we shipped the Word layouts feature (meant to make document reporting easier) in 2013, the team had a desire to move on to do the same with Excel (meant for analytical reporting). Well, better late than never... In 2022, we finally got the funding in place to do it. The idea behind Excel layouts was that for analytical reports, an end user (likely a power users with mad Excel skills) would be able to do the layout part of the report directly in Excel. This would reduce the development skills needed for analytical reports to being able to write AL code to express the report dataset and how the request page should be designed. As a PM, I was thrilled to finally be able to ship a reporting that looked towards the future of our reporting story and not "only" having to patch the past. I also wrongfully assumed that this feature would be 100% uptaken by customers and partners instantly. Boy was I wrong there (more about this in a later post). In the last few years prior to shipping Excel layouts, I had experimented with the use of code samples and guidance on our BCTech GitHub repo for our telemetry feature, and I thought that by doing the same with Excel layouts (see aka.ms/bcexcelsamples for examples). But code samples alone wouldn't drive uptake, we had to do a LOT more readiness work for the feature to be widely used (here in 2024 we are finally getting to some decent MAU/MAT numbers).

For more information about Excel layouts, see Creating an Excel layout report and Working with Excel layouts.

Reporting features for developers

With our work on Excel layouts, we needed to add support for them in AL report objects, so while we were working on that codebase, we decided to enhance the report object with a new rendering section that also gave the ability to ship multiple layouts of the same type (Word, Excel, or RDLC) in the same report. For more information, see Defining Multiple Report Layouts. As with most of the features in the Business Central platform, they come with an AL runtime part and an AL language (developer and compiler) part, so of course we needed to add a developer experience for working with Excel layouts from within Visual Studio Code.

To make it easier to troubleshoot RDLC reports, we also added the ability for developers to get the raw XML report dataset used by RDL directly from the report request page (in case you wondered what to do with that part of the menu).

The beginning of a new reporting strategy

Something happened in 2021 that build the foundation for the reporting work in the years to come and when you know about this, the feature roadmap (past, present, and future) might make more sense than if you just see features in each release wave as isolated islands. As mentioned above, we could see an end to fixing the painful (but very important) things that were needed to still support reporting and printing at scale in the cloud. We knew from customer and partner feedback that our reporting experience needed a lot of love, and when moving from the Windows client to the web client, we even degraded that experience further because we needed to remove the RDL rendering and viewing component (as this was only supported in a format that we could use in the Windows client.) So as a PM, how do you go about telling the story internally that we need to invest in such a huge area? I decided to spend time collecting screenshots of how reporting and analytics looked in our competitors products. I then compiled a deck and presented the story of reporting in ERP to the product group where I quickly showed our experience (run a report, get a PDF) and then showcased how our competitors did it. The presentation had the effect that I was hoping for: me and a colleague got the task assigned to develop a new product strategy for reporting and analytics. Now we just needed to write that and execute on it. Easy-peasy/lemon WHAT? It turned out to not be as easy as that (surprise!). Together, we spent countless hours discussing what was the main pain points and how we should prioritize the work. We got some of it right from the start (Excel layouts and financial reporting), whereas other parts took a few more iterations to get right. In the next posts, I will tell more about this story, so stay tuned...

Much more focus on User Experience (UX) improvements...

When developing the new strategy for analytics, we had already work in progress on improving the Financial Reporting feature that back in 2022 was called Account Schedules. That name had been there ever since the feature name was originally (mis) translated from Danish Kontoskemaer 20-20 years ago. The more correct translation would have been something like "Account schemas". Anyway, the name and confusing elements in the UI were identified as some key root causes for why we got feedback that "Business Central does not have a feature to do financial reporting." Kinda sad considering that the Account Schedules feature was all about that. So, we decided to give the feature some love, including a rename to Financial Reporting.

The work that happened on Excel layouts in the AL runtime and in the compiler also had spinoffs other places:

1. we decided to renovate the layout admin experience in the app, uptaking all the new language and runtime features around layouts. The old solution had different pages for layouts supplied by ISVs compared to layouts supplied by users, so we decided to just have one page (called Report Layouts).
2. with the ability for a report to have multiple layouts of the same type, we added ability in the request page for users to choose which layout they wanted to use when running the report.
3. one of our sister teams in the client were working on integration to Onedrive and decided to make the Excel layout feature even better by integrating the report inbox to Onedrive and also to make sure that Excel reports would open in Excel online if integration to Onedrive was enabled. I love this. These small things just make a feature like Excel layouts shine.

Now you know about the background story of the new reporting strategy that was forming this year (2022) and why it took some time for end User Experience (UX) improvements to surface in this space. In the next parts of this series, you will hopefully see how we ramped up in this space, leading to the analytics experience in the current version of Business Central.

Signup to the newsletter (and/or spread the word)

That's it for now. Thanks for reading along. Do comment on things that resonated with you when reading the article. Next post will be about reporting improvements in the 2023 release waves 1+2.

If you are present at the Days of Knowledge Americas 2024 conference this September in Atlanta, then you can hear the full story in one of my sessions there.

Want to read earlier posts in the series. See them here:

• Analytics, BI, and reporting in Business Central from 2020 until now - part 1 (2020 release wave 1+2)
• Analytics, BI, and reporting in Business Central from 2020 until now - part 2 (2021 release wave 1+2)

If you liked the newsletter and think that others might benefit from it as well, please send them the signup link here:

https://www.dhirubhai.net/newsletters/6981923051273658368