917.848.7284Data for Finance and Accounting

Top

FINANCIAL REPORTING AND ACCOUNTING BLOG

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: Through a direct permission In a database, someone has a permission when they can something to something. For example, they can insert (i.e....

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

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

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

I hope I’ve convinced you in my previous post to develop and store your SQL on the database and my rationale for that argument. The next question is how to create and keep this code on the server. You have lots of options, such as views, stored procedures, functions, simple data marts and even SSAS. In this analysis, I’m going to focus on views and stored procedures. That’s where I spend most of my time and is most relevant for “next level” super users. Generally, I like to start with views before going to stored procedures. I do this for two reasons: 1. Views get along with most report tools Almost every reporting tool I’ve ever used makes it easy to use views. After all, views look just like tables to a reporting tool. The same can’t be said for stored procedures. For example, if you connect to SQL Server from Excel, you’ll see views and tables, but...

As I started this series, I emphasized the importance of using SSRS for what it does best, not for everything it can do. I ended the post by noting the importance of having someone on your team with knowledge of T-SQL and access to the database. Whenever I make this point, whether in presentations or with colleagues, they ask me to back up my argument. So let me lay it out for you here: 1. You can’t assume that SSRS will be your only reporting tool Over the years, many IT shops have tried to control the number of ways folks see data. And this control makes sense, in theory. There’s value in having at least some of your staff up to speed on your preferred tool. But in reality, end users like the tools they like, regardless of anything you have to say about it. Because of this human proclivity to use whatever...

I recently presented on SSRS at the NAV Focus Conference in St. Louis. As often happens at these events, I wasn’t exactly sure who was going to show up and what they would already know. (Especially at an event like this, which had a development track but with no specified levels.) In any case, it became clear to me that many folks in the audience could benefit from a series of posts aimed at a “SSRS 102” level. They’ve mastered “SSRS 101” material—they can create a basic report and know what data source, datasets and reports are—but now, they’re ready to move up. The Cardinal Rule of SSRS Performance Happiness To move from a SSRS 101 level of understanding to a SSRS 102 level, you have to understand what SSRS does well—and what it doesn’t. Like most software tools, SSRS can do many things. Some of those things it does really well. Other things,...

In May, I’m presenting at the NAVUG Focus conference on the topic of “BFFs with SSRS.” (Not a title I chose. Please.) In preparation, I’ve been doing more work on the database, and I thought it worth writing up some of my findings. In this post, I’m going to review three fields that are in the NAV table but aren’t (or aren’t obviously) in SQL Server: Option fields Flow filter fields (FlowFilter) Sum index fields (SumIndexFields). If you’re new to NAV, you should start with my Understanding the NAV Database post from last summer. Option Fields In my previous post, I wrote about how to find the table name from a NAV page. Now, let’s look at another example, using the Vendors page and underlying table. Here, I’ve filtered to show three vendors and the value for “Blocked” (which I’ve set): If we look at the table information, we can see that the underlying table is...

I’m working on my first paid project using Azure. Previously, I’d set up test databases in Azure but nothing focuses the mind like having to get something working for a real client. I am hardly an Azure expert, but most of my clients are interested in or using some sort of cloud application, of which Azure SQL database is one. So, part of my motivation for writing this post is to get my thoughts clear. Why Azure SQL Database Should Work for Me and My Clients In the SQL server universe, I spend most of my time writing queries and doing integration development on SQL Server. I’m not a production DBA. I only learned about the back end to help various clients survive SOX and other financially related orders. So things like backups and redundancy are areas where neither my team nor I have in depth experience. Most of our clients have...

As a consultant, I’ve spent many hours on the technical aspects of budgeting. I’ve designed models, developed spreadsheets, downloaded and uploaded data, and of course, written lots of financial statements. I’ve also budgeted for my own business as well as other companies where I’ve worked. So, I know something about budgets. But, recently, in my volunteer position as treasurer of my local Y, I’ve been thinking about how a nonprofit board should approve a budget. The challenge of any nonprofit is that board members mostly don’t know the ins and outs of the business. And running a community center is particularly difficult as you have at least five to six different businesses contained therein, with very different revenue and cost structures. In my day job, I never have to model a health club and nursery school in the same project—not to mention a teen musical production. The other challenge is that, unlike in my day...