917.848.7284Data for Finance and Accounting

Top

Data Integration

I’m about to get geeky. If you don't have a soft spot for specs and technology, you may want to stop reading now. We spend a lot of our time building data integrations. Which means we're at the mercy of different trading partners and independent software vendors. Which makes life challenging when our customers want solid estimates. I hate saying "it depends." But in this case, it does depend. And it depends, largely, on our trading partners. Over the years, we’ve worked with many different systems with a huge variance in the quality of specs. Here's an overview, from best to worst, including what each scenario does to a typical integration (say, for example, inventory transactions from your manufacturing system to your order processing system): 1. The data integration specification works 100 percent Your partner provides you with a specification. And it works perfectly. Believe it or not, this can actually happen—and it did,...

Few companies get all their information from one system. Most companies we work with have multiple systems—from ERP and WMS systems, to web stores, EDI and other trading partners. Indeed, we spend 30-50 percent of our time integrating data between different systems. To that end, here are seven classic problems we encounter with integration projects—and clues for how to solve them. 1. You mistake one Excel macro for automated integration We can't say this often enough. Lots of cool tools (often Excel add-ins) allow end users to load data into systems. So, people think integrations happen at a push of a button. And as long as they have that button to push (and don’t have to rekey data from Excel into their system) they’re happy. And they remain "happy" even though it takes lots of time to assemble the data. And the data has errors. False satisfaction with their "integration" means they fail to...

Full disclosure: This blog post is somewhat self-interested. In it, I argue why you need to invest continuously in reporting and cleanup even when things are good enough. As a consulting firm specializing in report writing, this is perhaps not surprising. But in my defense, I can tell you we take pride in doing no unnecessary work. Read on and draw your own conclusions. This is a story of two clients. Client One is client ?good enough.? Client One has lots and lots of reports. They work with what they have and never really want anything done if there?s no immediate need. Things like report reviews, existing report cleanup, duplicate version elimination, and documentation improvements just don't happen. There?s no spending on reports unless there?s a direct ROI. Client One is generally pretty happy. Until they?re not. Client One quickly moves from happy to unhappy when the boss drills into a report...

There are a lot of "Rules of IT" which people obsess about (like the fallacy of the holy specification). I don't care about many of these. But there is one big rule about which I'm adamant: never, NEVER, test in production. While this rule is generally adhered to, I'm surprised at how often companies ignore it. But first, let's define production. In any mid- or large-size project, you need to have different copies of your data, software, and maybe even hardware, which we call "environments." All companies have a production environment - the live data where payables enter invoices, cashiers make sales and people enter expenses. Then, depending on the size of your organization, you may have other copies as well. Companies often have a development environment (for programmers to work in), a testing environment (for testing changes), and sometimes a training environment (which should be an exact copy of the...

Many of my clients have complex business rules. Most of these same clients want their systems as automated as possible so their staff doesn't have to know too much to do their jobs. Sometimes, these systems work beautifully; sometimes, they don't. This troubles me. I pride myself on developing systems where we might not hear from a client for months or years because the system just works. So, I've been trying to figure out why some systems work and others don't. I've concluded it comes down to the quality of the data. Client A has an old but reliable order entry system. We use the data for all kinds of complex calculations. We can count on it. When staff inputs data, they do it accurately and completely. As a result, every order has a customer and every customer exists. When we need something, it's there. We don't have to perform endless...

Microsoft's Access is a very powerful tool. We use it as a simple and fast way to create screens for data entry. But there are also lots of challenges with Access. The problem? Access is easy to learn but hard to use. Take this common situation we're currently fixing for a client: For years, a certain department in our client's company didn't communicate well with IT (shocking, I know). This department needed to get reports to various people but they didn't want to wait for IT to write a program. They had a few tools available to them (including Access) even though they aren't programmers. So they got to work. The process went something like this: The department would run a series of queries on their AS400 server which would create output files they could download to their PCs. They would download these files and load them into Access. They would then...

I recently had the opportunity to present to the Philadelphia chapter of the Data Management Association (DAMA), and in response to my point about transparency one audience member gave me an all-too-common story. He works for a health system that has multiple EMR (Electronic Medical Record) systems which don't communicate with one another. Since the cost to consolidate into a single system would be prohibitive, they?re building a data warehouse for a single source of truth. But while each constituency thinks it's great that they'll be able see everyone else?s data, they don't want to share their own. I have spent significant time over the years working with system access and security - not my favorite thing, but it has to be done - and I've seen this argument more times than I can count. So I've come up with five simple ways of categorizing the level of security your data...

We spend most of our days making data work for people. But before it can work, two things have to happen. One ? the data has to be there; it can't be lost because your server crashed or your cloud provider went down. Two - the data has to be secure; you can't have a laptop with the Social Security Numbers and compensation of the entire executive committee go missing. But all too often I find that our clients ask the wrong questions about security. Metaphorically speaking, they install a dozen locks and a high-tech motion detector on the front door but leave the windows wide open. So to help assess the level of security risk that threatens your data, I've developed this simple quiz. Answer the nine questions below to determine just how vulnerable your data may be. 1. I send emails to the wrong person? (a) Never ? I've modified my...

Understanding web data is one of the main challenges that all businesses face, and when I speak to other small business owners one of the first things they often ask me is what I know about Google Analytics. While Google Analytics is not something we consult on, it is something we use ourselves, and we try to follow our own advice when working with the data. The problem with the tool is that's there?s just too much data for most small businesses, so while I can (and will...

Is the project plan a basic guideline or is it an absolute set of things that you have to do, come hell or high water? Some people get extraordinarily attached to the project plan even when the circumstances change dramatically. I was thinking about this last weekend while I was driving back to Manhattan from NJ, where I grew up. Exit 9, if you were going to ask. Let's say your project goal is getting back to NY. The project plan tells you that the best way to get there is via the NJ Turnpike. But then you hear on the radio that there's a major tractor trailer accident on the turnpike. Do you stay on the Turnpike because that's what the plan says ? even if the highway looks like it's about to be shut down? Or do you take another approach ? perhaps the Parkway? Or even Route 1...