Data Integration and Analytics Blog for UKG

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.

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.)

I recently presented “NAV SQL from Friends to Foes” at the recent NAV User Group Summit.

Here’s a copy of the presentation:

Here’s the code I reference (in GitHuB).

My presentation is a whirlwind tour of the NAV database and SQL. But still, it’s a good starting point for folks who want to get comfortable with them both.

As I continue to build on this topic, I’m going to write about it here. 

And I’m going to start by explaining how NAV is (and isn’t) a relational database. 

And the first related topic is duplication of data — because this is a common source of confusion that all begins with NAV reports.

In this article, I’m going to delve into the topics of MAXDOP and cost threshold for parallelism. 

I’m writing it for two reasons:

1. There was a recent thread on SQL Server settings on the NAVUG list. Some of the information was good. But much of it was expressed in terms of “Well, I heard…,” which makes me nervous. You should have a basic understanding of what various settings do before you start taking anyone’s advice. (This isn’t to make me look superior. I’ve had plenty of times in my life where I (or my team) have been caught in the “let’s give this setting a try” loop — and it’s not pretty.)

2. I’m preparing a presentation on SQL performance for NAV. While I’ll be focusing mostly on improving BI query performance (which is where I spend a lot of my time), I do get asked about server settings, so I think it’s a worthwhile topic.

A client recently asked me: “When I run a report, how can I know which server/company I’m using?”

It’s a good question — and one that others may have.

Below, I’ll walk you through the answer.

And because pretty colors impress most folks, I’ve even made it match the colors that show up in NAV.

Last fall, my attendance at the SQL Summit was entirely justified by a presentation by Aaron Nelson, a SQL Server MVP on Excel, PowerShell and SQL Server.

Unfortunately, I regularly suffer from having Excel as a data source. In an ideal world, this wouldn’t happen.

Logically, no one would buy a SaaS solution without first figuring out how the data in that system would be accessed and combined with all the other data your organization needs.

Unfortunately, things aren’t always logical.

Read more…

I was talking to a friend/prospective client recently about the accounting knowledge that is a key part of my work.

We agreed that in too many non-profits, the board’s finance committee doesn’t perform its necessary function. The committee members look at a few reports when they meet, but they don’t really dig in. They may have a soft connection to “numbers,” but they aren’t always great at picking apart financial statements.

So, the question came up: if you want to evaluate financial risk in your non-profit, where would you start?

Read more…

Many, if not most, of the SSRS reports I write end up being exported to Excel. But unfortunately, many reports don’t export cleanly. More often than not, you wind up with merged cells and blank spaces you don’t want.

In this blog post, I’ll show you how to build your report correctly for a cleaner export to Excel.

Read more…