Reconciliations: From raw to report

It’s 2am and you’re reconciling data.  Something’s off, and it’s hard to tell where.  Regions are slightly off, product amounts are off.  You slice the data another way and sales by salespersons are off.  It’s not isolated to just one sales order or one type of order.  Sound painfully familiar?

I wrote about how to cut data reconciliations by 90% before, but here’s a different spin on reconciliations: look at it from creation to display.  There a series of data manipulations that take the data from raw form (creation) to report (display).  Your data process or ETL may be different, but you get the general idea:

  • Transaction is created
  • Transaction is stored in tables
  • Tables are moved into views or logical joins
  • Data is summarized
  • Data is filtered
  • Date is put into report format.

What you need to do is jump from the end of the problem to the beginning of the problem.  Here’s my suggestion on how to do this:

  1. Confirm the numbers don’t match at the display level (i.e. report, cube, or whatever the end customer is looking at)
  2. Jump down to the most detailed level possible (sales order number, tables, or raw files) and compare there.

If your data is accurate in item number 2, then it’s an issue of filters or criteria (usually is), but if it’s off in the source files, then you have a different type of reconciliation to do.  This can save you hours of time trying to find the “needle in a haystack” by skipping past the display issues.

What other secrets can you share with the community?

Photo by: philandpam

Share
Posted in Best Practices, Excel | Leave a comment

Avoiding Change in Your Job?

picture of flowing water

There’s a proverb that says “without a vision, people perish.”  And every successful person

I’ve talked to has had the same mindset, “manage your career.” But what do you do when you’re happy with what you do?

I don’t like everything about it, but there’s more things I like than dislike.  So, I’m in the awkward position of not wanting things to change.  However, biology teaches repeatedly that things change.

  • A barren piece of ground will start with weeds and eventually grow something. (That’s why deserts freak me out.  Never going to Burning Man.)
  • We get over colds, but they always change enough to get us sick the following year. (If everyone would just stop licking doorknobs for one year we’d be done with this thing finally)
  • Everything on earth is either growing or decaying.  (Can you think of anything where I’m wrong?)

barren desertNow that’s something you can resonate with, “I don’t want things to change.”  Many of us feel that way about relationships, jobs, family relations.  So the questions I have are these:

1. How much effort do I put into trying to keep things the same?

2. Do I have to come up with a better vision that gives me a new direction to sail towards?

3. Do I just need more Zen in my life?

The quote from Kung-Fu Panda sticks out, “Yesterday is history, Tomorrow is a mystery but today is a gift that is why it is called a present.”  I’m leaning towards number 3.

What do you think?  Share your thoughts/reactions in the comments below

Photo Credit: Irargerich and Mahatma4711

Share
Posted in Purpose | Leave a comment

7 Steps That Save You Hours of Troubleshooting

I was contacted by a client asking for help with Excel.  His challenge was that the default on the grid lines in his spreadsheets was white, therefore making it hard to see where each cell was.

I told him where the settings were and we adjusted them.  It didn’t fix the problem.  He sent me the sheet and we looked for other solutions.  Nothing was fixing the problem.  Had I used the OSI model to troubleshoot we could have saved that client several hours of frustration.

The Open Systems Interconnection (OSI) model is used all the time, we just don’t think about it in these terms.  While there are 7 layers to the model we don’t always need to use all of them.  Using the process is what is most important.

Here’s the key…start from the physical layer and move to the application layer.  Literally, start with the stuff you touch and then progress towards software options.  For those who want to know more, here’s how the model would break down in the scenario from above.

  1. Physical (are cords connected, is there power, etc…)
  2. Data (are the cords working, are there blinking lights and sounds, etc…)
  3. Network (are they connected correctly)
  4. Transport (is it consistent or sporadic)
  5. Session (has the Operating System come up)
  6. Presentation (Do we have a separation between the system running and how it is displaying)
  7. Application (default settings, check boxes or buttons selected, etc…)

It turns out his issue was that his monitor needed to be degaussed.  Here’s a how to article.  A simple solution, but one we didn’t try first.  Like most Business Intelligence professionals and non-technical support people, we spent a good chunk of time in step 7.  Had we considered the OSI model, there were several spots worth checking (steps 1,2, 4, and 6.) All showed signs of needing to be checked.

Send this to someone you know, so you can save them from this mistake.

Share
Posted in Best Practices, Excel | Leave a comment

Certified Bull

Here’s an image that can be used to certify a chart or graph.  Have fun with it.

Share
Posted in Best Practices, Communication, Presentations | Leave a comment

What Major Company Online Privacy Changes Mean For You

Big brother watching through binocularsYou’re being tracked…

It’s okay.

Google’s privacy policy changes have caused a lot of concern.  Facebook has been doing dubious activities with our online activity since inception.  Apple has been going around security settings to track us.  All of this talk about privacy makes a person want to shrink back into anonymity, like a cockroach when the kitchen light comes on.

While you may want to scurry away from the issue, the reality is you are being tracked online.  And unless you want to stop using the internet, this will increase.  The Onion had a great video about Google providing an “opt out village.”

Seth Godin wrote an interesting post on privacy.  He said that we gave up privacy a long time ago.  We just don’t like getting surprised about how companies are using our information.  While this is true, I think our part in this “agreement” is simpler.

Our currency in an online environment is our behavior tracking.  Where we go online, what we buy, who we buy from, who we are connected with, and what we think about brands/companies/issues are what we pay with in exchange for free searches, social networking, and entertainment.

When I watch something on Hulu, I am glad they give me a chance to tell them if an advertisement is relevant.  I am also aware that I’ve just “paid” to watch that “free” episode of a TV show.  That’s what I am “agreeing to” when going online.

Photo Credit: Andy Hay

Share
Posted in Best Practices, Communication | Leave a comment

2011 Was About Changing Your Mind

Girl looking at shoes and trying to decide2011 was about changing your mind. Radical decisions were made… and then changed.  Big decisions don’t just affect you, they affect everyone around you.  In the past these decisions were kept quiet until you were “sure.”

But not last year…

Why the change? What happened that caused this change?  Is it because:

  1. Companies are itching to “make something happen” in a down economy?
  2. Do we have management that’s lost its ability to make a decision?
  3. Are we getting to a point in business where we’re being transparent with our customers, and as a result we see what’s normally been discussed behind closed doors?

Here are a few examples to think about:

Netflix example:

In 2011 we saw Netflix change their mind.  First Netflix was going to split the business.  An announcement went out that saying they were going to take the “mail in DVD” and separate it from the “instant streaming to your device” business.  They planned on charging separately and having totally different websites to go to and charge your credit card twice.  http://blog.netflix.com/2011/09/explanation-and-some-reflections.html

This caused a huge uproar, including 5,000 comments on their blog.  One classic comment said something to the effect that, “you shouldn’t separate bread from a peanut butter and jelly sandwich because it’s a separate product… the whole is worth more than the sum of its parts.”  Netflix was smart enough to rethink the decision and they changed their minds and kept the business together.

http://blog.netflix.com/2011/10/dvds-will-be-staying-at-netflixcom.html

HP example:

HP announced it would not sell its computer business.  The new CEO had to make a decision quickly.  After looking at “the numbers” and other research, the computer business looked profitable.  So a decision was made.

http://www.zdnet.com/blog/btl/hp-were-keeping-the-pc-unit/62081?tag=nl.e539

Then HP changed its mind. The computer business was for sale.  Whether this was good decision or not, time will tell.  What is amazing is the quick change of mind, especially with all the impact a kind of decision like this would have.

http://online.wsj.com/article/SB10001424052970203687504577002144041676680.html

Conclusion:

Part of the reason things changed and then changed back is that we’re seeing everyone respond faster to new information.  In the past updates / polls / customer feedback took longer to get and analyze.  Now it can happen with a surveymonkey.com survey sent to Facebook.

Think of how quickly you can change a reporting to management. It’s a quick modification to a spreadsheet and an e-mail saying, “things have changed.”  Or if you’re thinking ahead a little more you’re doing things like:

  • Sending hyper links to the report, allowing you to change the report without sending an update
  • Sending people to a webpage that has the changes or disclaimers of the report

Now that I think about it, maybe 2011 wasn’t about changing your mind. It was about responding to information faster.

Do you have more examples?  Think there was a different reason?  Share your thoughts in the comments below.

Photo Credit: inafrenzy

Share
Posted in Best Practices, Purpose | Tagged , , , , , | Leave a comment

How to learn faster and get up to speed

We don’t focus on it.

Sometimes because we’re afraid, other times because we don’t know where to start.

If you come from the Information Systems (IS) side, it’s common.

If you came from the Business side, the idea of writing code sounds either daunting or frustrating.  Learning new programs and languages are common for developers, but for the Business Intelligence professional it’s something we may only dabble in.

We want to highlight an amazing tool for learning languages and programs and show you a specific example of how to use it.

The Amazing Tool

You’ve been checking out YouTube for years to see things like the Lassi Hurskainen Angry Birds or Jedi Kittens, but YouTube has another amazing side (one of many really) and that is that a bunch of education and learning videos are out there.

Real Life Example

I had to get up to speed on Microsoft’s Sql Server Integration Services (SSIS) and knew nothing about it. So I started searching out videos and YouTube and found Steve Fox making amazing videos.  After 2 hours of watching and doing, I was able to build my first package.  Thank you Steve!

Here’s a video that saved me hours of reading and researching:

How to build a simple SSIS package. Part 1 of 3.

Share
Posted in Best Practices, Video | Leave a comment

The First Rule When Networking With People

Picture of two people talkingMany times, people begin a project or a task without considering the purpose or desired outcome. From time to time, I will go to a fast-food restaurant. I realize that the food is generally not 100% organic. The menu does not have steak and lobster or fine wine. When I go to a fast food restaurant, I am not there for the excellent cuisine. I have one desired outcome – a full belly. More often than not, that is exactly what I get.

What happens when you work with someone who desires an outcome that opposes your own? At the very least, it can lead to frustration. If you go to a fast- food restaurant and you encounter an employee who desires only a paycheck, it is quite likely that the service you receive will be substandard. I am not suggesting for even a moment that employees should not seek a paycheck. In fact, my first job in high school was in the fast-food industry and I learned many valuable lessons. However, if an employee is focused only on a paycheck, then the only time he or she feels “successful” is payday. In the 1980′s when the minimum wage was only $3.50, the happiness of a restaurant paycheck was fleeting at best. Even though the wages have increased, the measure of happiness from a fast-food paycheck is just as temporary.

My experience working in a fast-food restaurant was mostly pleasant. As a high school student, I had three goals that kept me engaged. The paycheck was only one goal. This goal directly benefited two entities – first FICA and then me. Secondly, I wanted to help the business. My diligence helped the company to be more productive and profitable. They liked me so much that I quickly became the only one allowed to take out the trash – at least it seemed that way. Finally, I had a goal to serve the customers. I truly believed in the golden rule. If my friends stopped by to eat, I would gladly give them more food than they purchased. After all, that’s what I would want them to do for me.

Guilt Insurance

Even though I was slightly misguided, I was truly involved in mutually beneficial transactions. I have learned since the days of high school to grow beyond mutually beneficial deals. It was mutually beneficial to my friends and me to give away free food. I got the recognition I was seeking and they often returned the favor when I visited the restaurant on their shift. From the employer’s perspective, however, it was just plain stealing. I have adopted the philosophy taught by Napoleon Hill in his famous book, “Think and Grow Rich.” It says, “I will engage in no transaction that does not benefit all whom it affects.”

I call this Guilt Insurance. As long as I live by this philosophy, I sleep well at night. I never worry about remembering who heard my comments because I make an extra effort to exchange words that will benefit all whom they affect. In any business endeavor, there are three ways to weigh the benefits:

The Con

All for you and none for others. If you are short sighted, you may consider this to be the makings of a good deal. While I am highly in favor of getting a good deal, this scenario does not fit my description of one. In fact, this is the essence of what I call a genuine scam. One reason Ponzi schemes and pyramid schemes are harmful is that some of the unsuspecting “investors” are guaranteed a loss by definition. The money people receive comes from “investors” who are being cheated instead of from real profits. The hidden hook is greed that induces someone to throw away money in one of these scams. People who live this way are con artists.

The Victim

All for others and none for you. Some people are in the habit of contributing without expecting a benefit. Giving is an important part of life. The reality is, often times the donor does realize some benefit, even if it is not immediate or tangible. One example is a tax deduction for any monetary gifts. Other benefits may be emotional, such as a feeling of fulfillment or altruism. When you realize none of these benefits and the “benefits” go to others, you are commonly known as a victim. Some are victims by circumstance; others are victims by choice.

Win Win

Benefits for all involved. This is what Mr. Hill refers to in “Think and Grow Rich.” In this book, I refer to this as rule number one in networking.

If you are truly a Networker (not a con artist looking for victims) you will live by this creed; I will engage in no transaction that does not benefit all whom it affects. Seeking to benefit all parties involved allows you to network with confidence. You never think twice about asking for anything. You never hesitate to offer assistance. Deep down, you realize that if the person says yes to your proposed business connection, they will benefit as much as you do, if not more. I highly recommend this “Guilt Protection” to insure your network before you build it. This policy virtually guarantees your success.

Kenny Mitchell is our first guest post and is the co-founder of Real Skill Builders, a company that helps individuals and organizations to harness greater productivity through the understanding and application of fundamental success principles applicable in business and in life. Real Skill Builders delivers informative training workshops and lively keynote addresses for businesses and organizations. Learn more at http://www.realskillbuilders.com, call 866-389-0579 or email info@realskillbuilders.com
Photo Credit: The US National Archives
Share
Posted in Best Practices, Communication, Presentations | Leave a comment

The most frustrating question you have to answer all the time

Picture of a Business Intelligence Professional shaking handsWe hear this question all the time. We ask this question. And depending on the scenario it can be really hard to answer. What do you do?

No.  That IS the question, “What do you do?”

For the Business Intelligence professional, you know what’s coming next, “What is that?” it’s either mixed with a smirk because it sounded like you were being evasive, or a doubtful look, because they think it’s some fancier way to say your job, like a custodial engineer or domestic manager of a small labor force. Either way, you analyze how long you have with this person and their level of interest before you respond. Here are three canned responses we suggest.

1. Interest level low and time is short

For this scenario you can say,

“It’s a combination of Marketing, Sales and Information Systems work… slightly geeky, but fun.”

Many of the terms are recognizable and while they may not “get it” they can at least put you into a box and walk away.

2. Interest level high and time is short

For this scenario you want to pontificate and tell them all about your projects and interactions with management and your ownership of processes, but there just isn’t time. We recommend something to this effect,

“It’s a mixture of working with other managers and people in the business to get information and help the company know what it’s doing and where it’s doing it. Think of it like a Special Ops team and fortune teller mixed.”

The last part will stick with them and it puts you in that position of smart and action oriented.

3. Interest level high and there’s some time

Obviously, this is our favorite scenario because we get to talk about ourselves. At this point, we will remind you that all conversations about work and your job are potential interviews either as a customer or possible employer, so don’t bore them. Lately we’ve noticed if you mention too many IS items, then they will quickly put you into a box “Oh, he does reporting and is a computer guy”. This may be okay for how you want to be portrayed, but Business Intelligence is about making solutions and helping the company get smarter. So in this scenario we usually will start with,

“it’s a good industry to be in, the real gist of this type of job is to make solutions for those in the company, for example…”

At this point list two of your favorite or recent projects. Then say,

“…there is an Information Systems (IS) bend to the role, but a lot of it is also making the company smarter, i.e. helping sales call on the right people, helping Marketing know what they are doing is affective and helping management make quick and informed decisions.”

If you go any further you’re monopolizing the conversation. Either turn the question to them, or ask what challenges they’ve been having in their business. If you have some solutions then go that route and make sure to get their business card. Always be networking…
Have you found a better phrasing for a Business Intelligence professional? We’d love to hear it, and will try it out at the next office party or afterhours event.

Photo Credit: buddawiggi

Share
Posted in Best Practices, Communication, Presentations | Leave a comment

For my birthday I want…

Children's Birthday

For my birthday I want an ERP license!!!

What if your boss asked you what you wanted for your birthday? And just like when your wife or parents ask, you know you’ll get something like it or the exact thing. In my family it’s been something I’ve worked to make an efficient process. I keep a list of items I’d like, but don’t plan on purchasing right away (nice to have but not needs) on my Evernote account. Then a week or so before I browse the list send an e-mail to my wife and parents.

But back to the question: what if your boss asked you? (Catch: it has to be something business related.)

  • Better support from Business Intelligence?
  • Software that isn’t buggy?
  • Workshifting?
  • Three monitors (everyone has two now…)?

It might be time to keep a list of business wishes, and work them into some goal setting process. What would be on your wish list?

Share
Posted in Best Practices, Communication, Purpose | 1 Comment