Data Integration and Analytics Blog for UKG

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…

If you work at the intersection of systems and accounting, as I do, the title of this post may put you on edge—because you know how the story goes. Your system is working fine. You may even have time to get to some of the “nice to haves” as the “must haves” are going pretty well. But then the auditors show up. And you end up spending a LOT of time with them—without much to show for it.

Read more…

Anyone who’s in software development, and works directly with business users, knows that no matter how much things change, one rule remains:

You never get credit for what’s hard. You may, however, get credit for what’s pretty.

In that spirit, I’m going to write a few posts on making things prettier in SSRS. And today, I’m going to discuss conditional formatting.

By conditional formatting, I basically mean making things turn red when there’s a problem. I’m not talking about fancy charts. I’m talking about basic operational reports where you want users to know where there’s a problem.

For example, one of my clients uses Dynamics NAV. The “joy” of NAV is that it lets you enter data incompletely—and then only warns you when you post. That’s sub-optimal.

Instead, we’ve built messages in our reports to alert users before they post, such as when they miss a value or a transaction is not in balance.

Using Conditional Formatting to Highlight Errors

Let me illustrate with an example. Here’s a snip of a report in Visual Studio. It shows a simple text box with an expression:

Now, let’s look at the expression:

In this case, Invoice_Amount is the amount entered at the header level (which was a modification we needed in NAV). Sum is the sum of the expense distributions for the invoice.   When they don’t equal, the user gets an error message (in red font).

Which was acceptable for the first couple of months. But users wanted more. They wanted it to be OBVIOUS. So, we decided to format the box itself.

We started with Text Box Properties:

From Text Box Properties, we chose Fill:

On the Fill page, we choose expression by clicking the function symbol:

If we look at the function, we see pretty much the same logic. If the amounts differ we want the box to be red. (Instead of changing the background color, we could have chosen font and created a similar expression.) But the client wanted the error message to be super obvious.

As for the report itself, we can see that—depending on whether the report balances or not—we get an error message on a red background.

Here’s the data, one invoice is in balance and one is out of balance.

And here’s the output:

As you can see, the first invoice is balanced (with no error message) and the second invoice is NOT balanced (with an error message).

A final word: Like many things, a little formatting goes a long way. Use this kind of formatting sparingly. If the report starts to look like a Christmas tree, the colors lose their impact.

Those of you guilty of this know who you are.

In an earlier couple of posts, I had described three critical elements of SQL Server security: pieces, people and permissions.

For a non-technical person (such as, perhaps, an auditor) to understand SQL Server security, they need to have a grasp of all three elements.

You can read more about the four pieces of SQL Server as well as the different types of people (i.e. logins).

In this post, I’m going to cover the third essential element: Permissions.

Part III: Permissions

Now we have the pieces and the people. But how can they do anything?

In other words, what can the people (the logins/users) do to the pieces (the databases/schemas and views)? For our purposes, we care about their abilities to add or change data in the database.

We can grant that ability in two ways:

1. Through a direct permission

In a database, someone has a permission when they can something to something. For example, they can insert (i.e. add a new) record to the General Ledger Transaction table. Or they can update (i.e. change) records in that table or delete them.

Given the above definitions, we can grant permissions to people to Insert, Update and Delete data in a couple of ways:

• We can grant a user or group permission on an individual table. For example, we can give NH\ajacobson the ability to update the general ledger transaction table.
• We can grant a given user or group permission to an entire schema. Let’s say we want to give an entire group access to a given application that uses a certain schema. Rather than go table by table, we can give rights at the schema level. For example, let’s say that in addition to dbo, we had another schema called custom. We could let users in the NH\r3grp update the custom schema.

2. Through server or database roles

Of course, just as it would be tedious to handle permission for every user, it would be equally tedious to handle every permission on a table-by-table (or other object) basis.

Therefore, SQL Server has what’s called roles. A role gives certain overarching rights (such as read data on an entire database). Roles exist at both the database level and instance level.

Roles can be assigned at the database level and at the server level.

Roles that we care about for our immediate purpose at the database level include:

• db_owner —can do anything to the database, (including update data)
• db-datawriter —can write data to any table on the database.

At the instance level, we care most about sysadmin, which gives rights to anyone to do anything. It essentially makes them the equivalent of sa. And we don’t want to give this permission to many people.

In Summary

In these three posts, I’ve tried to give an overview of SQL Server security for folks with minimal understanding of it, which can include auditors.

Let me recap by highlighting the key terms:

• Instance
• Database
• Schema
• Table
• Login
• User
• Role

If you know these terms, you’ve got a place to start.

Of course, tracking all these permissions can be tedious. The good news is that all of this is stored in SQL Server meta data. And I have queries (which I’m working on cleaning up) that report on all this.

If you’d like the queries, please let me know.

In an earlier post, I explained three essential elements of SQL Server security: pieces, people and permissions. Your auditor (or any other non-technical person) will need to have an understanding of all three parts to understand how security works in SQL Server.

In this post, I’m going to explain the second element: People.

Part II: The People

While a database with no access would be secure, it wouldn’t be all that useful. So how do we tell SQL Server who gets access to the system?

We start at the instance level and look at logins:

Logins:

A login is a way for a person or program to gain access to SQL Server.

Let’s analyze a few of the logins I’ve set up here.

Single Windows User Login

In the above example, NH\ajacobson is a single windows user login.

When you see a \ within a domain, you’re seeing a login that first existed in Windows.  Almost everyone reading this post uses a Windows login when they come to work each day and starts up their PC.

Generally, the single user (in this example NH\ajacobson) also has login rights to Windows. We know it’s a single user because of the icon which represents one person…

as opposed to the icon for a group,  which kind of looks like multiple people (discussed below):

It’s almost always preferable to use this kind of login. With it, you don’t have to worry about passwords expiring within the scope of the accounting system and database because all that’s taken care of by your Windows admin.

Windows Group Login

NH\R3Grp is an example of a Windows group login.

As you can imagine, it wouldn’t be efficient to set up EVERY user as a login to your SQL Server. Generally, (especially for accounting applications), if a single user needs access to the database, many other users in the organization will need similar access. Therefore, Windows allows you to create a group.

So, let’s say you have 20 folks in accounting that will all be running reports over the accounting database. You can have your Windows administrator set up a group (in this case R3Grp) and include all the accounting users in that group. This way you can maintain security for the group and not for the individual. (Of course, your administrator will need to produce a list of everyone in a given group for your auditors.)

SQL Server Login

fransales is an example of a SQL Server login.

When you don’t see the backslash character in a login, the login hasn’t been set up in Windows. That means no one is going to login to their PC as fransales. However, sometimes people will use an application that uses SQL Server, and they’ll be asked for this user name and password.

In general, we want to avoid using these kinds of logins. Using them means maintaining password security in SQL Server. Also, these kinds of logins (and their passwords!) wind up getting hardcoded into spreadsheets. Not that I would ever do something like that.

A Special SQL Server Login

Before I finish with logins, I want to talk about one special login that exists on every SQL Server: sa. Which stands for system administrator.

The sa account can do ANYTHING to EVERYTHING on the server. How to secure this login requires another blog post. But the point is, whatever else happens on your server, if someone has access to this account they can do anything.

Database Users

Logins alone aren’t enough. You need to “attach” the logins to individual databases. In effect, you have to say not only can this guy use this server, but he can also use this database.

For example, I have a custom database that I want the NH\R3Grp to use. So, I’ve added them as a user to that database:

If we hadn’t added R3Grp to this database, users in this group would have no access to it.

So far, we’ve discussed the pieces of SQL Server and the people with access to SQL Server. In my next post, I’ll talk about permissions—allowing people to do stuff with the pieces.