When moving is a problem

Mobility of staff within an organisation can be perceived as a good thing - a result of an efficient HR department and a company that likes to remain agile. However, this causes an often neglected flaw in many companies and how they administer the system access of their employees and the resultant authorisation.

Many organisations have processes that involve multiple people. This is often for good reason, to spread risk and introduce checks and balances to prevent fraud or human error. Imagine that an employee starts off in a role within the organisation in the front office, answering phone calls. Let's call him Bob.... 

Bob has a set of screens that allow him to request functions for the back office to complete. Perhaps one of these functions is requesting refunds for defective products for the back office to process.

Then Bob gets a promotion that allows him to work in the back office, printing the same refund cheques that he used to request while he was in the front office. Most organisations will add the new functions, but they will rarely take away the old redundant authorisation.

Bob now has access to both front and back office functions that enable him to request and print cheques without scrutiny from the rest of the organisation.

So I would ask - how many of your colleagues have been put in this position? Are they even aware that they are exposed to fraud and excessive operational risk? Conduct an audit. See for yourself. You may be surprised by the results.

How secure are your passwords?

In this non-stop, always-on digital world, it's not unusual for the general public to have a large number of user ID's and passwords for various web sites.  Many sites like Amazon, Ebay and Paypal also hold your credit card details for convenience.

Take this into the business world, and a user can have access to many technical areas on servers all over the place:

• Databases
• Platforms/servers
• Web applications
• Secure FTP areas
• Personal computers
• Mainframe applications

User authentication is extremely important to make sure the right people can conveniently access their systems and services, while preventing unauthorised exploitation.

Companies may choose to give their users a generic user ID that can be used for all of their systems. General public websites often ask users to use their email addresses as user ID's. This puts increased importance on the security of the password for each system. We can use Entropy (my favourite nerd term) to measure of the effectiveness of a password.

Entropy is the level of disorganisation within a collection of related objects or components of a system. So in a password situation, Entropy is used to measure the level of unpredictability between each character of a password. The higher the entropy, the more secure your password is.

There are a few different ways to find out the correct password:

• Stealing
• Social engineering (misleading you into divulging your password)
• Guesswork
• Brute force

A high entropy sequence of characters will make your password impervious to guesswork and more difficult to gain access through brute force.

Guesswork involves using knowledge of popular passwords, like '1234', 'admin', '9999' etc.

Brute force involves the use of a piece of software that bombards the application with multiple passwords until it finally hits the correct one. So the higher the Entropy of your password, the longer it will take for the program to discover your password.

There are many precautions we can take to secure our information from hackers, governments and thieves. This is the first in a number of articles in which I intent to raise awareness of information security for the normal user, and why we all need to be vigilant in the workplace.

Corporate laughs

Is business intelligence a contradiction in terms? How come so many smart people can often come together to mess things up so badly? Applying cold, hard logic to spontaneous communication can be hilarious. Here are some of my favourite corporate faux pas:

“As of tomorrow, employees will only be able to access the building using individual security cards. Pictures will be taken next Wednesday, and employees will receive their cards in two weeks.”
(Microsoft Corp. in Redmond WA)

“What I need is an exact list of specific unknown problems we might encounter.”
(Lykes Lines Shipping)

“E-mail is not to be used to pass on information or data. It should be used only for company business.”
(Accounting manager, Electric Boat Company)

“This project is so important we can’t let things that are more important interfere with it.”
(Advertising/Marketing manager, United Parcel Service)

“Doing it right is no excuse for not meeting the schedule.”
(Plant Manager, Delco Corporation)

“No one will believe you solved this problem in one day! We’ve been working on it for months. Now go act busy for a few weeks and I’ll let you know when it’s time to tell them.”
(R&D supervisor, Minnesota Mining and Manufacturing/3M Corp.)

Quote from the Boss: “Teamwork is a lot of people doing what I say.”
(Marketing executive, Citrix Corporation)

My sister passed away and her funeral was scheduled for Monday. When I told my Boss, he said she died on purpose so that I would have to miss work on the busiest day of the year. He then asked if we could change her burial to Friday. He said, “That would be better for me.”(Shipping executive, FTD Florists)

“We know that communication is a problem, but the company is not going to discuss it with the employees.”
(Switching supervisor, AT&T Long Lines Division)

 Have a great day.

Retention policy - a fundamental principle

There are a lot of annoying things in life you naturally expect - like taxes, bad television and crime. But I did not expect such vehemence about a recent article I wrote about the value of data and the conflict of interest it has with cloud providers.

It seems there is a ground swell of opinion in the technology area that all data should be retained, as valuing it and prioritising it is too hard to do. Furthermore, these people (often cloud providers) could not see the harm in keeping all their data indefinitely.

There are some very good reasons why you should be scrapping data on a regular basis, and here they are:

1.  It will save you money

2.  It will make you compliant with regulation

3.  It will improve the trust with your suppliers and the general public
4.  As reality changes, your data will degrade in quality over time until it is little more than useless.

But the most important reason is something a little more fundamental. People have a right to make mistakes and have them forgiven and forgotten. People also have the right to change their opinions and image. They have the right to reinvent themselves and move on. If we cannot discard what did not work, how can we move on in a healthy and positive manner?

Questions to save you from data quality meltdown

In a large, busy organisation, inevitably, there are a lot of problems. Many issues can be assigned to data quality. But one of the biggest pitfalls for a data quality team, is taking on too many assignments.

Colleagues with over-simplistic viewpoints may use the data quality department as 'long grass' to conveniently kick their problems into. Here are some questions to ask yourself to prevent a data quality team meltdown.

Is it really a data quality problem?

A popular mistake is to assume that operational or functional problems are 'data quality'. For example - if a telecommunications company keeps debiting a customer's monthly charges, even though they cancelled their mobile phone connection, it is not a data quality problem. It is an operational problem. The data correctly reflects what money the customer has paid. Spot these kinds of problems early and remove them from your inbox.

Is there a conflict of purpose?

Systems, databases and data marts get built for specific purposes. It can be tempting for analysts to try to use them for other purposes. When the data doesn't work as expected, they may declare that the data needs 'fixing' so they can use it. These are not data quality issues. They are issues for developers to solve.

Is it a nomenclature issue?

Naming terms can be a problem. Departments may have different names for the same things - or even worse - use the same name for two completely different things. Push the problem back to them until they can articulate their technical terms in plain English. Don't be afraid of sounding stupid to ask for this. It can uncover a lot of underlying problems.

Is there a migration issue?

When data gets migrated from one system to another, not all of the data for the new system will be contained in the old one. Very often these missing data items will either be blank or have default values. Although they are data quality issues, they cannot be fixed because the data was never collected in the first place. Get to know the start dates of each system, and the limitations of any migrated data.  It could save you a great deal of running around.

Are you trying to boil the ocean?

There are some issues that require large-scale intervention. Be honest about your capabilities, and get the correct resources assigned. If this means rejecting issues that are too large, reject them until the right resources become available.

These are all common-sense questions to ask yourself before accepting data quality issues. if you have any others, please use the comments below.

3 sure signs you need a data warehouse

In these challenging financial times, it is easy for those controlling the purse strings to ask, "Why do we need a data warehouse?"; Particularly among small to medium sized enterprises, where they may believe they are running quite nicely without one.

So what should a manager be observing that gives them clues they need to think about a data warehouse?

1. Your legacy data is expensive to access

You may acquire segments of your business through mergers or acquisitions. You could have historic databases containing old products or services. If it's taking a lot of time (and therefore costing a lot of money) to access this data and make sense of it, then it could be time to migrate it into a data warehouse.

2. Management information is not joined up

You have to get reports from different parts of your business, and they don't seem to agree - even though you operate from the same customer base. The reason for this is that your organisation is not integrating the data correctly and consistently. The best way to integrate data for a single version of the truth is to build a data warehouse.

3.  Adhoc reporting is slow 

All you wanted was one extra data item adding to an existing report - how come it's taking so long to do? Quite simply your measures are 'point and click' solutions that have their own ETL functions. So whenever a change is required, the whole solution needs to change and be re-ran. A data warehouse will already have the data in it's most useable form. All of the processing will have been done over night, and your analyst just needs to add the new field, query it and present the results.

Data warehouses are a practical and common-sense way of consolidating important information from all over the enterprise. They are expensive to set up, but once implemented, they will give your organisation a high level of reliability and stability.

5 things to consider when joining data

Warning - we are in major geek territory here. This assumes basic understanding of SQL. You have been warned! 

OK, you have been asked to get some data together for a project. The data is scattered across different tables and needs to be joined together before you can get any insight from it. Here are 5 things to help you avoid some of the pitfalls that can happen.

1.  Consider location carefully

Your analysis software is very powerful. Many business intelligence packages can allow you to join a table in one server to a table in another server, without ever considering whether it is a good idea to do so. I once heard of a user in Cheshire who tried to join a couple of million records on a server in London to half a million records in Hong Kong, and was surprised when he brought his entire network down. Don't do it! Extract your data from one server, place it in a temporary area on the other server, and join the data there. Or even better, import both tables to a local server before joining them. It may be a little more inconvenient to code, but don't cross your network administrators else ye will pay a terrible price!!

2. Keep your joins simple

Yes, it's flash and kind of impressive if you join 5 tables together within one SQL statement. But one day, someone else is going to have to examine your data when it is challenged. They are going to get to your large SQL query and realise that the problem is somewhere in the tenuous joining you have done. It's much better to join one table to anther; then use the product of that join to another table etc. It takes longer to code, but means that gaps in referential integrity are much easier to spot.

3.  De-duplicate the keys before joining

Just do it. Make it a habit. Even when the data looks right. One day it might be wrong.

4.  Use indexed fields

Ideally, you should be using indexed fields for the joins and also any other selection criteria. Indexes vastly improve processing times. If the fields are not indexed, you can add an index when you import the data. 

5.  Outer join and coalesce

When joining tables, consider what you want to happen to the records that don't match. It is a lot simpler to 'left outer join' the tables so that the missing records still appear in the query results. You can then use the coalesce function to add in an identifier for your missing data. It makes your reports more transparent if bad data can be categorised. It keeps your organisation honest and makes your results easier to check.