Lesson Learned: Don’t Abandon the Acumatica Data Access Classes (DAC)

Ok, I give up. Exactly one month ago (click here) I attempted to abandon the Acumatica Data Access Classes. Now, at the time I wasn’t sure if it would work out, but I thought I’d at least try it temporarily. One month later and I’ve given up. I feel like I need to go to a chalkboard and write “Don’t abandon the Acumatica Data Access Classes” over and over again.

Old ERP Technology versus New ERP Technology

I was trying to apply old ERP technology principles where they don’t apply anymore. With Acumatica, we aren’t in the old ERP technology world anymore and we have to change some of the things that we were used to doing. Lots of things are changing in the world of ERP technology: where our data is stored, how it is secured, how it can be accessed, etc.

It’s not easy to change. We all want to fall back on the things that we are comfortable with, and that’s why I was trying to go around DAC by going directly to the Acumatica database. However, eventually I came to the conclusion that it’s better to take the time to learn something new and adjust my thinking to the way new ERP technology works. I am a PAL after all (click here)! It’s a bigger up-front investment, but it will pay off in the long run.

Adding fields exercise helped me gain confidence in DAC

The exercise that I went through to add new fields to an existing Acumatica screen (click here) helped me to get more comfortable with DAC. I was able to see first-hand that Acumatica can take care of adding the fields automatically to the database (which is pretty cool). It’s kind of like if you were used to washing your clothes by hand and then one day you get a washing machine. At first you might not trust the machine, but eventually you get used to it and it saves you tons of time. I’m getting more confident in Acumatica’s ability to take care of the database while I focus on DAC. It’s not just a fairy tale idea anymore. Acumatica really can manage the database for you!

Some benefits of using Data Access Classes instead of going directly to the database

1. Acumatica security gets applied. If you go directly to the database, then your reports don’t know what stuff you should have access to. This is especially important in Acumatica because of the ability to setup security at the entity level using the Row-Level Security (RS000000) module. So, for example, maybe someone is only allowed to see certain warehouses or customers. If they use a report that utilizes DAC, they can only see the warehouses or customers that they should have access to. However, if they use a report written directly against the database, the report has no idea what they should see so it shows them everything. This can be very dangerous!
2. DAC makes more sense to a business user than the technical database stuff. I don’t have too much experience with this yet, but so far it seems like it’s more intuitive to create relationships in DAC. It’s a lot more complicated in the database as I’ve discovered (click here).
3. New fields automatically get added to a DAC. If you add fields in the future, they automatically get picked up by the DAC and become available to you when you’re designing reports. There’s no need to reinvent the wheel over and over again.

Crystal Reports and SSRS

Going directly against the database is typically what you would do in the old days using Crystal Reports or Microsoft SSRS (SQL Server Reporting Services).

However, I have found Microsoft’s Power Pivot in Excel to be a better reporting tool in most situations and definitely more popular with report users.

I’m hoping for OData in the future since I still like Excel

OData is a way to subscribe to data that you care about. Think of it like your Facebook newsfeed, except it’s for data, specifically ERP data.

Microsoft Excel Power Query and Power Pivot are already setup to connect to OData feeds:

If you build something in Microsoft Excel (the world’s most popular business intelligence tool) that utilizes OData, all you have to do is click Refresh and your report will have up to the minute information without you having to spend hours rebuilding it. This is really powerful.

There are already a number of different applications that currently make their data available as OData feeds (click here). Some of the ones that are more specific to the ERP world:

1. SAP
2. Microsoft Dynamics
3. Microsoft SharePoint
4. SSRS (SQL Server Reporting Services)

I’ve actually used the Microsoft SharePoint feature before. All you do is click the OData icon in SharePoint and it instantly opens the data that you were looking at in Excel. So you can refresh it, combine it with other data sources, apply some dynamic filters, etc. Using OData is a piece of cake because Excel already knows how to handle it.

So what about getting OData feeds in Acumatica? It’s not supported yet, but Gabriel Michaud already thought of it. All we have to do is vote on it. Cast your vote by clicking the image below!

BI360 if you want Excel reports right now

BI360 really deserves its own post. This is a product that takes Microsoft Excel reporting to a whole new level. BI360 is very powerful and it’s available from a company called Solver (click here). I’ve been looking at it for Sage 500 ERP since that’s what I use in my current job. But BI360 also works with Acumatica. The more I think about it, I really should just make a post devoted to BI360. Ok, I just added it to my future post list. Just know that BI360 can get you the refreshable Excel report functionality, plus a whole bunch of additional features, and it’s available right now, today, without any votes needed.

Conclusion

So I’m going to put my fascination with Excel on the back burner for now and focus on the current Acumatica reporting tools which utilize DAC. This means that I’ll be picking up where I left off over nine months ago (click here) with the S130 Reports Dashboards and Inquiries course in Acumatica University. Here we go…