Accounting Summaries – The Report!

Spaceballs - The Flamethrower!

Spaceballs - The Flamethrower!

The best dog I’ve ever owned also had an interesting name, Peeve. She was my mom’s pet Peeve. Anyway, I’d like to share a pet peeve I have in regards to report names. I cannot tell you how many people I’ve met that actually put the word “Report” into the title of the Report.

Vendor Summary Report
Sales Report for Part Number 123
Weekly Repair Metrics Report

Ugh. We know it’s a report. It’s redundant and a waste of time to include the word “Report” in your report. This is something else I learned through the pain of red ink from Mr. Girard.

After all, you don’t see best selling authors creating book titles that end with “The Book.”

Under the Bus

monster_school_busRecently we had an M2M outage at work, which caused annoyance and downtime for many of my users. It took the better part of a day to investigate and solve. As you can imagine it was a stressful time, executives were angry, etc; which was particularly difficult for me because I am a perfectionist when it comes to my work.

I must admit that I was especially irritated when I learned it had nothing to do with myself or M2M. When my counterpart, found his error and we started bringing users back online, an executive cornered me for an update.

What did I tell him? “Bob did _____ and it caused _____?”

No. I said, “Bob found it. It was a problem with M2M VBA and he managed to ferret out the answer by checking…. “

I never mentioned that Bob caused the problem with his new image distribution method. I focused on the troubleshooting and recovery effort rather than play the blame game.

Why am I telling you this? Well, my executives don’t even know I blog so they are very unlikely to read this. I just wanted to urge my readers (both of you) to never throw someone under the bus. It isn’t productive and is in fact disastrous when you work on a team.

Later, when my technical supervisor was back in the office he asked me about it. I gave him the facts, focusing on the solution and the good troubleshooting by Bob. He grinned and said, “He found the problem… because he caused it.”

I returned the grin and responded with, “Well, yeah.”

Can You Farm?

mitch_hedberg_edited”As a comedian, I always get into situations where I’m auditioning for movies and sitcoms, you know? As a comedian, they want you to do other things besides comedy. They say “alright you’re a comedian, can you write? Write us a script. Act in this sitcom.” They want me to do shit that’s related to comedy, but it’s not comedy, man. It’s not fair, you know? It’s as though if I was a cook, and I worked my ass off to become a really good cook, and they said “alright you’re a cook… can you farm?” – Mitch Hedberg

The other day one of my good friends asked me, “Dave, how much would you charge to create a simple website for my buddy so people could order his stuff from the web?”

I tried to explain that although I do have some web development experience, I haven’t really touched that in two years and I wouldn’t be able to do it. Her friend could not afford to pay me while I re-learned to do it, and he should seek out a professional.

My friend then continued to argue with me that since I’m a geek, a super-geek in her eyes, that I should be able to do it. She just kept asking me how much. I guess this whole notion that computer professionals can do anything with a computer stems from the belief that what we do is magic. Since they don’t have any idea how we do our jobs, they think that we can just cross over into jobs that seem related to them.

As computer professionals, we may laugh privately at such requests, but are we not guilty of similar thinking? In the past year I’ve read articles regarding The Great DBA Schism and problems with Microsoft’s BI Certifications that further suggest more separate DBA roles.

I’m not suggesting that I have the solution to these issues, but I agree that more attention should be brought to them and further delineation is necessary to accurately describe our database roles. If SQL Server DBAs continue to refer to ourselves simply as DBAs, is that not the equivalent of asking a great chef if he can also farm?

BI Ethics

The ethics of business intelligence is a touchy subject that many people don’t want to talk about. However, more attention in the past several years has been given to this subject mostly due to ethics scandals at huge companies such as Enron, Tyco, and WorldCom. The Sarbanes-Oxley Act came about because of such scandals and the billions of dollars they cost investors as well as the decrease in investor confidence in the stock markets, but it only applies to publicly held companies.

This is all well documented, and related information can be found all over the web. Recently a Code of Conduct for DBAs was proposed by SQL DBA, Brad McGehee. This is an excellent, albeit broad set of guidelines for DBA behavior.

My last article discusses a relatively benign situation where a decision was made how to calculate and provide statistics. In this article, I’d like to focus on business intelligence. What do you as a Business Intelligence Expert do when asked to provide questionable information? I’ll give the following example to facilitate discussion.

The Accounting Manager of your organization requests a report which details Accounts Receivable Aging. You code the SQL for the report creating groups of invoices that are 30 days, 60 days, 90 days, and more past due. You include an attractive chart and send the report to the Manager for approval. He reviews it and confides in you that his new bonus structure (or perhaps even his job) is dependent upon these numbers. He requests that since ACME Rockets is always late, Wile E. Coyote is always out in the desert chasing the Road Runner instead of paying bills, that they should be removed from the report. His reasoning is that no matter how well the manager performs his job, ACME will never pay in less than 90 days and this drastically lowers his rating.

Think about that for a second…. I’ll wait.

Now let’s suggest that the Accounting Manager is your direct supervisor. You may actually lose your job if you refuse. Look deep within yourself, did the answer change?

Anyway, the following poll is anonymous. What would you do? Feel free to explain why you answered the way you did in the comments.

What would you do?

View Results

Loading ... Loading ...

When Being Right May be Wrong.

Recently I was tasked with automating a reporting process which collects repair metrics. The current method of tracking repairs in my company was tedious and time consuming. As is typical, the method used to track this was Excel Hell. Every week someone would run the Received Goods (RPREGO), Inventory On-Hand by Location (RPINOH), and the Monthly Shipment Analysis (RPMSHA) reports. They would export them to Excel, pivot table the information, and then type all of that information into a master spreadsheet which created multiple graphs for executive review.

Why was Monthly Shipment Analysis used rather than Invoiced Sales (RPIVSL)? Well, they want their information by product class and RPIVSL doesn’t pull that information.

Anyway, when I substituted an invoicing script instead, I matched their overall totals for repair revenue to the penny. However, the average repair cost on the invoicing script was quite a bit higher than they received from the Monthly Shipment Analysis. Upon investigation of our business processes I determined the cause. Often when a part is returned to us for repair, there is nothing wrong with it. Sometimes we will test the units gratis, and if no fault is found, return them to the company free of charge. These units are shipped, but no invoice is created because the dollar value is zero. When figuring averages their method has a greater quantity of repairs than mine (because it is derived from shippers, not invoices), so their average is less because those repairs are for $0. Make sense?

Luckily the manager acknowledged that by their own definition these units were not repaired, and so they went with the invoicing numbers with a footnote as to why the numbers were not consistent with statistics. However, the manager risked giving the executives the impression that his reports could not be trusted.

What do you think? Is consistency more important than accuracy?

My next article will discuss the ethics problems facing Business Intelligence Experts.

“What is your Biggest Weakness?” The Classic Interview Question.

The Bobs

This is the point where we all lie and list a strength and tell the interviewer that our biggest weakness is that we just work too darn hard or some other such tripe. To be honest, I don’t know why they ask this question and what they really expect for an answer.

However, I will freely admit one of mine here. I love to code. When given a project such as a difficult report, I will often start writing code. This is especially true if I anticipate the code will be difficult or tricky.

How is a love of coding a weakness? Well, it’s because when I do this I often end up coding the same project several times. Why does this happen? One of the reasons it happens is that I often put the logic into my SQL script when that should be done in the presentation layer.

Always begin a report project with the delivery medium in mind.

When a user asks me for year to date incoming sales for customers in Dallas, TX my brain spits out the following:

SELECT SOM.fcustno AS [Customer Number]
     , sum(SOR.forderqty * SOR.funetprice) AS [Net Amount Ordered]
FROM M2MDATA01.dbo.soitem SOI
      INNER JOIN M2MDATA01.dbo.sorels SOR
      ON (SOI.fsono = SOR.fsono)
AND      (SOI.finumber = SOR.finumber)
     INNER JOIN M2MDATA01.dbo.somast SOM
     ON (SOM.fsono = SOI.fsono)
WHERE ltrim(rtrim(SOM.FCITY)) = 'Dallas'
AND   ltrim(rtrim(SOM.FSTATE)) = 'TX'
AND   year(SOM.FORDERDATE) = year(getdate())
GROUP BY SOM.fcustno

However, this is not what I need. If you review the above code, you will notice that I only pulled totals per customer number. What if the user wants to be able to drill down into particular customers and see which products they are purchasing? I need to list all the sales for these companies and then group in the presentation layer, in this case Crystal Reports. Therefore the user will be able to drill down into the data, while at the same time seeing their subtotals. Re-writing this short script is trivial, but many of them are not.

Just for reference the script I would use for this would be something like:

     , SOM.FCUSTNO as [Cust No]
     , SOM.FCOMPANY as [Company Name]
     , SOM.FCUSTPONO as [Cust PO No]
     , SOR.FENUMBER AS [Item No]
     , SOR.FPARTNO AS [Part No]
     , SOR.FORDERQTY AS [Qty Ord]
     , SOR.FUNETPRICE AS [Net Price]
     , (SOR.FORDERQTY * SOR.funetprice) AS [Net Amount Ordered]
FROM M2MDATA01.dbo.soitem SOI
      INNER JOIN M2MDATA01.dbo.sorels SOR
      ON (SOI.fsono = SOR.fsono)
AND      (SOI.finumber = SOR.finumber)
     INNER JOIN M2MDATA01.dbo.somast SOM
     ON (SOM.fsono = SOI.fsono)
WHERE ltrim(rtrim(SOM.FCITY)) = 'Dallas'
AND   ltrim(rtrim(SOM.FSTATE)) = 'TX'
AND   year(SOM.FORDERDATE) = year(getdate())

Anyway, coding before planning is one of my biggest weaknesses. What is yours?

Just for fun, I am going to tag Brent Ozar and Jorge Segarra. I hope they’ll answer this questino as well as tag others in the SQL Blogosphere.

Not Quite 2000 Words

I owe a lot to Mr. Frank Girard. This blog is a product of his work as well as mine. Mr. Girard taught me how to write in high school, but I wasn’t a big fan of his at the time.

I remember getting detention for “cross pollination.” Mr. Girard would regularly split us up into small groups to brainstorm topics on which we would write, and we never seemed to get grouped with our friends. If he caught you talking to someone in another group, he would call that “cross pollination” which was an “abomination of nature” and you’d spend time in detention. He was quite a character and often referred to himself as “God’s older brother.” After having been labeled a “social butterfly” by my 7th grade art teacher, it shouldn’t be any surprise that I would get caught “cross pollinating” on a regular basis. However, this was not why I didn’t like Mr. Girard.

I didn’t like him because he was ridiculously hard to please. I’ll never forget the first paper I turned in because he forced me to re-write it. Remember kids, this was back in the day when we our homework was hand written by candle light. Alright, well I made up the candlelight part. Very few of us had computers at home, and those that did often had no way to print their work. Spell check? Are you serious? Back in the day you actually had to know how to spell. There were no punctuation suggestions. Re-writes really were re-writes, not just edits. This…. was writing.

He would assign 2000 word essays, oh my god 2000 words (which is nothing for me now). I remember counting words to make sure I hit the minimum, and writing them in such a way as to take up more pages than necessary. Afterward, being forced to re-write a 2000 word paper was a demoralizing experience.

So, I re-wrote it with the corrections he suggested. I turned it in and received it back with a C. Even the second draft was covered with so much red ink that I thought he had opened a vein and hemorrhaged all over it.

This was shocking because I had sailed through every previous writing class. At first I thought this taskmaster simply had it in for me, until I looked at other’s papers. It was obvious that a significant portion of the school’s budget must’ve gone into red pens for Mr. Girard. So, I buckled down because I had to complete this class for graduation and eventually my writing improved. He was so strict in his grading and missed nothing. When I learned to avoid most of my mistakes, he found more advanced problems to correct. Eventually, my writing improved and so did my grades, but I wasn’t very appreciative until much later in life.

When I went to college I aced my English classes, because of Mr. Girard. I wrote much better than most of my peers. In fact, it became obvious to me that the professor who taught Bio Ethics (primarily a writing class) stopped correcting my papers after the first one and simply scrawled a grade on them seemingly at random. There were some comments in the margins of the first and last pages, but nothing else. This was offensive to me because I spent hours writing them to Girard’s standards. However, when the prof gave me a B on one of them, without so much as reading it, I took action. I turned in a few papers in a row with real content on the front and final pages and then copied and pasted (we had computers by this time) several pages worth of text from the Bible for the middle portion of my essays. He never noticed. When I brought this to the attention to the Dean, my grades miraculously improved.

When I became a Science Teacher, a job I still miss some twelve years later, my appreciation for Mr. Girard grew. I took up the torch to promote good writing, something my district was pushing, and gave my students meaningful writing assignments in my Biology classes. I didn’t realize the hell I had unleashed on myself. The kids couldn’t write properly, even in the age of computers. They had no sense of sentence structure, proper punctuation, etc. Imagine my shock when I got 150+ assignments every two weeks or so from my students, and I could barely read them. This is no exaggeration. I literally spent my weekends with… you guessed it… red pens. The time required to read all of these papers was ridiculous, and I still can’t imagine how Mr. Girard managed to do it; especially since most of the papers he read were hand written as opposed to typed. It’s no wonder the man wore thick glasses. Eventually, I was asked by the administration to stop the writing assignments because the kids complained to the English department, and parents were complaining that their kids were being taught English in Biology class. It was obvious to me that they needed to be taught to write somewhere, since most of these kids were acing English with such poor writing skills, but I digress.

I respect Mr. Girard for his dedication to his craft, his unfailing demand for perfection from his students, and his unparalleled work ethic. Sadly, he died several years ago so he’ll never get a chance to read this.

I suppose that’s a blessing in a way because if he were still alive, I’m sure he’d print out this page, correct it, and send a copy back to me covered in red ink.

Oh I never did stop “cross pollinating” either.

Profuse Perpetual Pointless Pernicious Permission Problems

I’ve been working with M2M for nearly 12 years, and they’ve made very little progress with permissions. I’ve placed several change requests regarding permissions, which have gone unheeded. Smaller companies with only a few M2M users will probably not relate to some of these issues, but they become major problems when you administer 40+ users and multiple companies, like I do.

What are the Problems?

  1. M2M does not provide a way to tie your Active Directory structure to your M2M account. Yes, if you use the same usernames in M2M as AD (assuming none of your AD names exceed 10 characters), you can automatically log in. However, the admin must manually set permissions for each person. There are no groups in M2M with regards to rights, so you have to assign each person individually. M2M suggests that you create a sample user for every job in your facility and copy permissions from those accounts. For example, you might have user accounts such as “SalesAdmin”, “SalesMngr”, etc. Those accounts would act as your base permissions account for those job positions. However, when the Sales Administrators’ permission requirements change, this process is even more tedious. You must add the permission to the base account “SaleAdmin” and then manually copy the rights to each Sales Administrator afterward. Exactly how does this accomplish anything?
  2. Each new report must be assigned manually per employee as well. Every time you create a Sales report for your Sales Administrators, you must add that permission to each one.
  3. Setting up a practice company is tedious because rights have to be given manually per person. Why isn’t that an option when creating a new company? More on this later as I think a friend of mine has created an application to accomplish that very task, and I’ll share it with my readers in a future post.
  4. The permissions tables are encrypted (username) so you can’t write custom reports nor create a program to automatically manage permissions.
  5. New users have root control of M2M by default and this is a terrible security policy. I can’t tell you how many times I’ve run the Permission Report and seen that the new guy could ruin us because another admin (I would never make such a mistake right?) forgot to limit his rights.
  6. Speaking of the Permission Report, it is awful as well. It takes forever to run, and it’s very inflexible. For example, I want to be able to run the permissions report per screen only for those people who have deletion rights. The standard report doesn’t allow this. Therefore, I export them and import them into an access database and use a Crystal Report to summarize them per screen.
  7. ”Hidden” screens can be a problem as well. For example, let’s assume your Sales Administrator role is to add new sales orders to the system. So, you give them Add and Edit permissions to the Sales Order (SO) screen. The user re-starts M2M for the permissions to take effect and then come to you with the following error:
    SO Add
    Not only do you have to provide permissions to the SO screen, but you have to provide permissions to screens such as SOADD and SOCHNG as well.
  8. Speaking of Sales Orders, they are like many other documents in that you cannot give users permission to delete line items, but not entire master document. This is a hassle because I’d rather inconvenience the user as little as possible. A disgruntled employee can really hurt you with deletion permissions.
  9. Why can’t users change their own password? This is a huge security problem that all passwords are assigned by the M2M Administrator. This problem has actually been addressed in Version 6.0.
  10. Finally, and this is a small issue, but if you try to remove rights to a certain screen and you start with View, you will receive the following:
    This goes back to blog post about M2M Errors. If M2M knows what I need to do, why doesn’t it simply do it?

What do you folks think? Have I been too harsh? What problems do you have with the current permission structure?

Interesting VFP Date Issue

I performed a test migration to M2M Version 6.0 the other day on a test server. After your test server is set up, one of the first steps is to run DBSCAN on your Util database. My scan found a few records with data corruption. One sample record is below:

11/21/09 03:17:39 PM:dbScan: Data Corruption Detected!
11/21/09 03:17:39 PM:>>Table…..: L:\M2MDATA\\UTIL\utrpsess.dbf
11/21/09 03:17:39 PM:>>Record Number…….: 3995
11/21/09 03:17:39 PM:>>Field Name……: DATE_FROM
11/21/09 03:17:39 PM:>>Error……: Date fields must have values > 01/01/1900
11/21/09 03:17:39 PM:>>——————————————————————————–

To clean this up, I opened VFP and entered the following commands:

set exclusive on
open data M:\m2mdata\util\util.dbc
use utrpsess
goto 3995

When I hit browse, I didn’t understand the problem. Look for yourself:

Looks like a good date to me.

Looks like a good date to me.

However, a Consona Support Tech suggested I click inside that field and when I did, I saw the following:

See it?

See it?

Score one for support.

Happy Thanksgiving

Last year about this time I referenced the famous WKRP Thanksgiving Day Episode and listed those things for which I am thankful. Well, I’ve decided to update my list for this year as a lot has changed since then.

  • I’m thankful to have a secure position in this awful economy and that my career is moving in a positive direction.
  • I’m incredibly thankful to the SQL community and their willingness to freely share their knowledge. The following offer excellent blogs and/or recorded videos with amazing free content. In particular I’d like to thank Brent Ozar, Grant Fritchey, Andy Warren, Steve Jones, Tim Mitchel, Brian Knight.
  • I’m thankful to live near two great SQL Server User Groups, especially for the excellent classes given by Mike Hotek (which he does free) at the Fort Worth User Group.
  • Once again I’m thankful that Made2Manage has not outsourced their support, and I hope they never do.

What are you thankful for?