Accessing Dynamics Business Central (NAV) Cloud Data, Part 1: Terminology and Tools

When databases became a common part of ERP systems over 20 years ago, developers would say that no matter what else happened, they would always have SQL. “Give me a connection string and I’ll get you anything you want,” was their moto.

Today, that’s not the case. With the advent of SaaS in general (and cloud-based Business Central in particular), you now need to know things like “ODATA and “APIs” — especially if you want to combine data from multiple systems in a single report or data warehouse.

I joke that the definition of SaaS is “software where companies save lots of money on infrastructure and upgrades, which they end up spending on data and reporting.” (Which is good for me, I suppose. I never liked infrastructure and always liked data.)

But seriously, unlike many SaaS systems that I’ve worked with, Business Central has a good set of tools that can get you where you need to go, even if you can’t do it with a SQL query.

In this somewhat long series of posts, I’m going to discuss how it all works — how you can get access to data in NAV, how you can work with that data in Excel and Power BI, and even how you can get that data back into SQL Server.

I’m preparing this series of posts in conjunction with a presentation I’m giving on the subject at the NAVUG Focus in Denver in May (should it go forward). Please sign up so you can attend and ask questions in real time.

In this first post, I’m going to go over some relevant terminology and tools. 

Let me be absolutely clear: I would much rather spend my time developing in SQL then becoming a web developer. I’ve programmed in many languages in my career (including VBA, Python, COBOL and RPG), but these days I spend most of my time in SQL. So, I’m going to keep this as straightforward as possible.

In the brave new world of no databases, you’ll run into a bunch of new terms. I’m going to go over a few of them — mostly to let you know that they’re not all that important. 

What is important, as you go down the list, is to observe that almost every term is not exclusive to Business Central/NAV. Unlike in the old on-premises world of C/AL and XML ports, all the tools below work with standards that are common to a far broader universe. Which is good. Because while you may be the data guy, maybe you have a web developer on staff who can lend a hand.  Further, everything you learn here can be applied to the three other SaaS offerings your company just purchased.

API – Application Program Interface

I’m guessing most of you know what an API is. After all, APIs existed long before the web. 

In short, APIs are a way that programs allow other programs to do things that a user could do manually. In our case, we care about the four key activities of data manipulation: CRUD (Create, Read, Update and Delete).   

I write this here not so much for the Business Central/NAV folks but as a reminder to everyone to please, please, please, educate your management that every SaaS vendor you choose must have APIs so you can get data in and data out without hiring an army of temps. 

Also, make sure those APIs use standard conventions.

Web service 

Web service Is essentially an API that works across the web or with web-based technologies. You can learn more about it here: https://en.wikipedia.org/wiki/Web_service

REST – Representational State Transfer

When you dig into APIs in the world of web applications, you’ll hear a lot about REST APIs.  

You can check out this Wikipedia article if you’re really keen:  https://en.wikipedia.org/wiki/Representational_state_transfer

But those us of who just want to work with APIs don’t need to know the details. The main idea to grasp is that a REST API is stateless. Which means that the server you make requests from has no idea what you’ve done before. It just answers your current question.

Let’s use vending machines and bartenders as an analogy. When you put money into a vending machine, it spits out your selected chocolate bar. It won’t remember how many chocolate bars you’ve eaten today, and it won’t care. If you can pay, you get the chocolate.   

In contrast, if you go to a restaurant, the bartender will (hopefully) remember that you’ve been there before, remember what you like to drink, and maybe suggest that you slow down after that second bottle of wine.

So, basically, in REST, each transaction stands on its own.

I explain this here so you’ll have some idea of what’s going on when a web developer starts talking. Also, I will introduce some easy scripts in PowerShell later in this series, and REST will pop up there.

ODATA — Open Data Protocol

ODATA is a standard for building RESTful APIs. You can read more here: https://www.odata.org/.   

The key thing to know is that there are different releases of the protocol (and Business Central supports two different versions). This is important because when you use ODATA APIs, you’re often looking up ODATA documentation and not Business Central documentation — and how you specify data (for example) in an ODATA API will change depending on the version.

JSON — Java Script Object Notation

For our purposes, JSON is a just a simple way that data is formatted when it’s passed from the APIs we are using (kind of like XML – but simpler). JSON passes data in key value pairs.

Here’s an example of what JSON can look like for the NAV chart of accounts:

You’ll see that each record is separated by curly braces. And every column in the database (and its value) is repeated in what is called a key value pair. I’ll talk about the details later in this series of posts.

To summarize what we’ve covered in terminology:

• We need an API to do something by computer that a user could do manually.
• A RESTful API is a certain type of API.
• ODATA is a standard for RESTful APIs.
• JSON describes the format of the data that’s returned when we perform a READ operation. (Just like XML or a database table is a way of representing data.)

In these posts, I will make some references to PowerBI and Excel. But you wouldn’t be reading this post if you were happy with PowerBI and Excel solutions. So, I’ll mostly rely on two other tools that you either already have or can get for free: Postman and Visual Studio Code.

Postman

When you start working with APIs, things can get complicated pretty fast. And if you’ve seen a URL with lots of percent signs, you know you don’t want to type that string of crazy characters every time you need to test. 

Postman is a tool designed to make that kind of testing easier. I’ve used the free version of the tool for these blog posts and that seems to be all you need if you’re testing another API.

PowerShell

PowerShell is the force that binds the Microsoft universe.  (whether I think it represents the dark side of the force or light side of the force depends on the day). Think of it as the good old DOS prompt multiplied in power by orders of magnitude. I said above, I’d prefer to use SQL. But, even as a SQL guy, I’ve learned that PowerShell is incredibly helpful in many, many tasks that I encounter every day. 

PowerShell is part of every Windows system. We’ll get to it later in this series when I talk about getting data back into SQL server.

Visual Studio Code

Visual Studio Code is Microsoft’s free development tool. We’ll use it to develop PowerShell scripts and view the JSON files we create. It’s also the tool of choice for AL Development.

Now, let’s run an API.