Why is Ad Hoc Reporting so difficult within an ERP system?
When you are running a modern, expensive ERP system, it is perhaps surprising that ad hoc reporting is such a chore. Why do these swish, shiny systems not provide flexible, ad hoc reporting and analysis out of the box?
The answer to this question is multifaceted…
The reality is that most modern ERP systems, such as MS Dynamics NAV, do provide lots of standardised reports out of the box. If you need to produce a cashflow report or a VAT return, for instance, then you can be fairly sure NAV is going to do that, really well, as standard.
The challenge part is when it comes to ad hoc reporting – creating reports that the software vendors did not predict in advance that you would need. These tend to be a) specific to your business b) consequently, of high business value and c) frequent.
An example would be a report on ‘The total margin generated by sales of Product Group A to customers in Poland in Calendar Q1, broken down by sales rep and compared to last year.’
The majority of ERP systems do come with the technology and tools required to build reports. The problem, however, is that business users often lack the skills to use these complex tools, making ad hoc reporting almost impossible.
If you’re a tech whizz who knows what to do with these tools, who knows the ERP really well and who understands your business too, then you can use these tools to create new reports.
The downside is that most people are not. If you have these skills in-house then bottlenecks will form around the one or two people who can create new reports. If you do not, you will need to employ or contract someone in. In either case, new reports are slow to create and then need to be maintained.
Normalisation is the way in which system architects and programmers design the databases that underpin your ERP system. They don’t do it for fun, it is done for good reasons, but it does make ad hoc reporting extremely difficult. With a normalised database, information is spread across lots of tables.
This means that when you need a very specific report you have to join multiple tables together to make this happen, which is what turns something seemingly simple into a complex, IT person’s job.
Modern ERP systems are made up of literally hundreds of individual tables. To get almost anything meaningful out of them, you have to join the tables together (using a language called SQL – ‘structured query language’) and even relatively simple or common business reports may require multiple tables to be joined.
Because of the technical nature of some reporting tools and the underlying normalised nature of your ERP system’s database, the business user is never going to be able to self-serve or conduct their own ad hoc reporting, with the tools that come out of the box.
Running complex queries over databases slows them down. We now know why reporting over an ERP system database is complex. – because of normalisation. So when we want to get a meaningful report, we have to build an SQL query that joins multiple tables together.
The bad news is that this kind of query negatively affects performance of the database. The best-case scenario is that your report is going to run slowly (the ‘kick-off the report then go for a coffee’ scenario).
The next best case is that in order to serve your reports (and keep your system running at the same time), you have to upgrade your hardware (the ‘throw more horsepower at it’ scenario).
The worst-but-not-that-uncommon situation is that when you run certain reports at certain times of day, the performance of the whole system can be affected. This is pretty scary when you consider that your ERP is also running your sales order entry processes, warehouse, and maybe your production.
Not all the information that you need in your reports is in your ERP. No matter how clever or nifty the ERP system is, it can’t really be expected to tell you something it doesn’t know. So if you have information that isn’t held in the ERP but which you do want to include on a report then you have a problem.
Many businesses have ancillary systems that hold valuable data, such as CRM, e-commerce or HR systems. Many also have information only held on spreadsheets. Good examples include budgets, targets, rebates and ancillary costs.
Ad Hoc reporting solution
Delivering a successful self-service business intelligence implementation over an ERP system can be transformational.
Giving business users improved ad hoc reporting capabilities empowers faster, more accurate decision-making. Well-designed dashboards and KPIs lead to improved situational awareness and the ability to steer the business with agility. A single version of the truth – usually stemming from a well-designed data warehouse – reduces conflict, errors, and the ‘invention of management information’.
To find out how you can improve ad hoc reporting over ERP systems such as MS Dynamics NAV, download our E-Book below. You’ll find everything you need to formulate a successful Self-Service Reporting and Business Intelligence strategy for use in your MS Dynamics NAV environment.