Data Integration and Analytics Blog for UKG

As there are many specific tools for data integration (Boomi, SSIS, informatica), you we think you should choose PowerShell.  Whether your data source is an Excel spreadsheet, a text file, a Rest APIs, or a software specific reporting tool, PowerShell should be the glue that binds them all together.  There are two reasons we think you should choose this direction.

A date table is essential to data work, whether you’re working in SQL, Tabular Modeling for Power Pivot, Power BI or SSAS. 

Because if you work with any kind of data, and particularly accounting data, you’ll always have to ask for the appropriate time period.

You can find lots of good examples of how to build a data table, both in SQL and in Power BI, if you operate on a calendar year or your fiscal months are calendar months. 

Google is your friend here, so if the links above don’t help, you can find others.

But, if you’re a retailer or another business that operates on some variety of a 4-5-4 calendar (with a 53^rd week showing up every couple of years), that’s not as easy to find.  

In my recent post on the Change Entry Log, I referenced several reports I’d written. 

Thinking about it later, it occurred to me that everyone has different formatting option preferences. 

So, in this post, I’m going to show you how you can create a SQL Query to get the information you need to build your report.

When writing SQL queries for NAV, you can run into a problem.   

While column names are generally pretty self-explanatory, sometimes the data within those columns requires decoding.  

Those are: 1) Table Numbers, 2) Fields Numbers and 3) Option Numbers. 

I’ve already covered this in my posts (and presentation) on Understanding the NAV Database.

Here I’m going to repeat myself — and then give you the code you need to get these numbers into SQL.  

In part one of this series, I looked at NAV security from an audit perspective (see SOX Audits and Dynamics NAV, Part 1: “The Auditors are Here and They Have a Few Questions.”)

Here, in part two, I’ll cover two areas:

1. Setting up the change log
2. Looking at change log entries.

So far in this series, I’ve spent a lot of time showing how we can work with various ODATA web service APIs to get different sets of data from Business Central. 

And as you may recall, the purpose of this series is to describe how you can combine data from Business Central with other applications that may not be in the cloud and may not be using Power BI.  

In this post, we’re ready to talk about this specifically.

So far, we’ve seen how we can extract an entire table from the cloud, in this case the Chart of Accounts. However, we often don’t want to do that (especially on tables like G/L Entries). 

Thus, we need to filter.

The complete reference for ODATA Filtering for Business Central/NAV can be found here: 

https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/webservices/use-filter-expressions-in-odata-uris

In Part 2 of this post, I reviewed the basics of setting up a web service access key, finding your first API, and then executing the API in your browser. 

But the output we had wasn’t all that user friendly. Further, we asked for EVERY account in the system. In this case, there wasn’t a huge amount of data so it wasn’t a problem. But it’s not something you’d want to do with G/L Entries or other larger tables.

Fortunately, you can do a lot more with these ODATA Web Services than just select everything. Theoretically, you could create ever more complex strings and test them in your browser. But that’s not really efficient.  

Which brings us to a tool that’s called Postman.

In my last post, I promised that we’d get to Postman and see how we can work with the queries.  

But I changed my mind and decided that before we get to that, I want to show you how to load data directly into Excel with the ODATA URL that we used before. 

In Part 1, I went over some of the terminology and tools involved with getting data from Business Central (NAV). 

In Part 2, we’re going to go into Business Central and run an API.