Business Intelligence

Restarting the Red Three Blog and Newsletter

Over the course of the last seven years, I created over 300 posts for my blog. These posts were written sporadically. Some years, I wrote over 100. This year, I’ve written maybe two.

But as I focus my business on “helping social service agencies maximize revenue and results with data,” I find myself answering the same set of questions. Some questions are strategic, (“How do we become more data driven?”). Some relate to data architecture, (“Do we need a datamart or a star schema?”). Others are specific to the tools we use, such as SQL Server Reporting Services, (“How do I stop cells from merging when I export to Excel?”).

Restarting the Red Three blog and newsletter

Read more…

Business Intelligence

The Health Home Program and Its (Relatable) Data Problems

An article in Politico caught my attention recently. It was about the Health Homes program and some of its problems.

For those of you not familiar with this program, here’s a brief explanation from the article:

Health homes are not brick-and-mortar buildings. They are a concept based on the idea that if several providers work together to coordinate care for the most expensive Medicaid patients, they can provide better care at a lower cost.

These patients, the so-called super-utilizers, have behavioral and mental health issues, substance abuse problems, multiple chronic conditions, sometimes all of the above. Health homes, which can be a hospital or health and human services agency, assemble a network of providers that together manage care for the patient.

Read more…

Dynamics NAV

SOX Audits and Dynamics NAV, Part 1: “The Auditors are Here and They Have a Few Questions”

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…

Business Intelligence

Using a Datamart to Solve NY Medicaid Billing Issues

Several of my clients are New York-based social service agencies. As such, much of their income depends on Medicaid reimbursement. The problems they face are two fold: First, they need to make sure staff members document client work in a way it can be billed. That’s important, but it’s not something I deal with.

Second, they need to figure out how much they’re billing and collecting—and where the differences lie. This problem meshes well with my background in accounting and databases. The goal is to know how well (or poorly) billing/collecting is going—so they they can work with client-facing staff members to make sure all revenue is being captured.

Medicaid Billing Issues

Read more…

Business Intelligence

How to Use Your GL to Prepare Your CFR Reports and other NYS Reports

Many nonprofit agencies have reporting requirements that go beyond GAAP. For example, in New York State, social service agencies depend on state funding to serve their clients. To get this funding, they must submit various CFRs (Consolidated Fiscal Reports) to various NYS departments, including the OPWDD, OMH, OASAS, and OCFS (i.e. Office for Persons with Developmental Disabilities, Office of Mental Health, Office of Alcohol and Substance Abuse Service, and Office of Children and Family Services).

Faced with these different requirements, finance folks often download their financial statements into Excel and start making adjustments. This is not a good idea for several reasons:

CFR reports and other NYS reports

Read more…

SSRS

SSRS Consultant Tip: Conditional Formatting

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.

 

SQL Server

Explaining SQL Server Security, Part 3: Permissions

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

 

SQL Server

Explaining SQL Server Security, Part 2: People

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.

 

SQL Server

Explaining SQL Server Security, Part 1: Pieces

I get calls from clients to answer auditor questions on a somewhat regular basis. These questions usually start with the accounting software and then drill down to the database level. Then, I often get this entirely reasonable question: Who can change data on SQL Server?

This is, as I said, an entirely reasonable question. Unfortunately, sometimes the person asking the question knows next to nothing about SQL Server. Which makes it difficult to answer this question without launching into a lengthy explanation of SQL Server Security 101.

(It’s disheartening when this happens. You would think that a person working for a large national firm sent to audit a system would have some knowledge of the database. It’s not like we’re using dbase or internally described files on the AS400. Yet, in talking to other folks, this is far from an uncommon problem.)

So, to save you the trouble, I’m going to walk through SQL Server Security 101 (or, more accurately, 000, or maybe -101). You can use it to explain to non-IT people how SQL Server security works.

To capture all this in a short series of posts is going to be a challenge—and I’m sure I’ll have to oversimplify.

The situation reminds me of one of my college professors. When I argued that a question on a western civilization test was way too big for a test, she stated that every question has a five-minute answer, an hour answer, a term paper answer and a thesis answer. And you have to learn to give the appropriate answer for the time given.

Here, I’ll be giving you the five-minute answer.

You’ll note that I’ve divided this series into three posts:

  1. Pieces—The pieces that make up SQL Server
  2. People—The people or groups that get access to SQL Server
  3. Permissions—What the people can do to the pieces.

I’m purposely going to start by using language that’s not special to SQL Server but can be understood by anybody.

Again, the goal here isn’t to describe how to do each thing. It’s to describe how to explain each thing to an auditor who will probably never use SQL Server.

Part I: The Pieces

SQL Server is complex software. Sadly, it doesn’t have a simple switch you can flick on and off to denote “keep end users from doing anything that’s going to cause a problem.” Instead, it’s made of up a variety of pieces that combine to make more precise distinctions.

So, to understand SQL Server security, you need to understand the pieces.

To start, you need to know four things:

  • Instance
  • Database
  • Schema

Below is a visual representation of SQL Server. (In this case, I’m using Dynamics NAV, but it could be any software.) In it, I’ve circled examples of these four things:

Let’s start at the top.

The Instance

In the example, the first line is the name of the instance. (I’ve greyed out the actual company name for privacy.)

Essentially, an instance is one installation of SQL Server, meaning that someone took the software and ran the install program. Mostly (but not always), you’ll find one instance of SQL Server running on one physical server or virtual machine.

The instance is important because every user who has access to the system will need to be set up at the instance level. (We’ll get there later in this series.)

The Database

The database in this example is Cronus.

A database is really just a collection of data about a given topic. In the world of accounting software, you may have separate databases for production and testing of a given software. You may also need, as in Dynamics GP, a separate database for each company that is setup.

From an audit perspective, you want to focus initially on the databases which hold production data.

Databases are important because you can grant users permission to do things at the database level and these permissions will give them rights to the next lower levels.

The Schema

In this example, the schema is dbo.

A schema is a way to organize the various tables and other objects that make up the database that store the data.

In most accounting systems I’ve worked with, only one schema exists per database, and it’s called dbo. However, you can have multiple schemas. Again, this is important because you can grant access to a schema and that access will apply to lower levels.

The Tables

Tables are where the actual data is stored. For example, in Dynamics NAV General Ledger, transactions for a given company are stored in a table called Company$G_L Entries. In Dynamics GP, General Ledger Transactions are stored in GL20000 and GL30000.

(Of course, databases contain other objects besides tables (e.g. views and stored procedures) but today, I’m going to focus on tables.)

Tables are the lowest piece to which you can assign security in SQL Server. (At least, in all accounting software I’ve seen.)*

So, to review, we have four pieces in SQL server:

Instance->Database->Schema->Table

And security settings at higher levels affect security settings at lower levels.

In the next post in this series, I’ll discuss the second part of the security puzzle: People.

*Theoretically, there’s a feature in SQL Server called row-based security that allows you to assign security to parts of a given table. But I know of no accounting software that uses this feature.

 

 

SSRS

SSRS Consultant Tip: Multiple and Shared Datasets

As I’ve said before, the cardinal rule of well-performing reports is to use SQL Server for your processing, and not SSRS. Instead, save SSRS for presenting and distributing data. (And here’s why.)

In addition, you should create and store these procedures as views first, and then as a stored procedure, if necessary.

Continuing our discussion, let’s talk about datasets.

First, what is a dataset?

You can think of a dataset in two pieces:

  • Some kind of SQL code (a SELECT statement or a stored procedure)
  • Additional logic performed to the results of that SQL Code once the code is returned to SSRS.

Now let’s look at specific types of datasets and some scenarios.

Multiple SSRS Datasets for Parameters

Many reports require more than one query. To illustrate, let’s take a simple example I’ve built over AdventureWorks. This simple report gives us sales and allows us to select those sales based on customer and/or item.

Now, it would be theoretically possible to just have the user key in the appropriate item number. But that’s not going to make anyone happy (especially if they’ll eventually to want to select multiple items or customers). So, this report has two additional data sets: one to provide values for the customer and one to provide values for the item.

Let’s take a look at the report:

We have five datasets, but they’re not all the same.

The first dataset (called “DataSet1”), is used in the table and provides the bulk of the report output.

The other four datasets—“Territories,” “Categories,” “FiscalYears,” “FiscalPeriods,” and “Quarters”—are only used for parameter selection.

Shared Datasets vs. Embedded Datasets

Continuing with this example, only DataSet1 is unique to this report.

You can see this by right clicking on the dataset and choosing properties:

Now, we can see that the DataSet1 is “embedded in the report.” This means that this particular set of “query plus extras” can only be used by this report.

The other four datasets use the shared dataset option. That means that the query was defined BEFORE we created the report.

If we look on the right side of Visual Studio, we can see all the shared datasets that I’ve created for this particular project. I’m using most, but not all of them in this report.

When to Share Datasets

When I work in development, I almost only use shared datasets for parameters. If I have complicated logic that might power multiple reports, I’ll create a view, stored procedure or even simple datamart rather than create a complex dataset (as discussed in my post “SSRS Consultant Tip: Stored Procedures vs. Views for SSRS Reports“).

To keep things clear, I generally name datasets as I have here: “DataSet1” for the dataset unique the report and then real names (e.g. “Products,” “Territories,” “FiscalYears”) for shared datasets.

Multiple Datasets in One Report Beyond Parameters

In the example above, I only have one main dataset. But sometimes I’ll use other datasets in one report.

For example, for a NAV customer, I created a report to show all invoices that have been selected for payment. The user then asked to see all vendors with a credit balance who were not receiving checks at that time. In other words, every time the user asks for step one they also want to see step two.

Therefore, I created another dataset and another table in the report. But essentially, they’re two reports in one because they’re always requested together.

A No-No: Combining Data from Multiple Datasets With the Look Up Function

SSRS has the ability to combine data from multiple data sets. Going back to my cardinal rule about using SSRS only for presenting data and reporting, I don’t think this is a good idea.

Yes, it’s possible to link each dataset in the report to a different data source and therefore a different database (which I once did, combining Unix data and AS400 data in one report. Oh, the horror!). But the performance suffers.

I intend to write more posts on this topic as this is a fairly common scenario for users using multiple software packages.

 

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult