PraxIS Dec. 2007

07-12 Contents: Data loss, privacy, Process Improvement, Excel tips, Spreadsheet Management

ISSN 1649-2374 This issue online at http://www.sysmod.com/praxis/prax0712.htm   [Previous] [Index]  [Next]

_______________________________________________________

Welcome to PraxIS

As we approach the festive season, may you all have a safe and happy Christmas, and achieve your goals in 2008!

Patrick O'Beirne

_______________________________________________________ _______________________________________________________

1)  IT Risk & Security

The biggest data loss in history ... so far

In mid-October, a junior official in the Revenue & Customs service of the UK (HMRC) placed two CDs into unrecorded outsourced delivery to the National Audit Office containing unencrypted full personal data about  recipients of child benefits. The story is a catalog of short cuts taken to save bother which bypassed all accepted security control measures. 

Why by post on CDs, when there is (allegedly) a secure government intranet ?

Why unencrypted, given that the new head David Hartnett (Paul Gray moved sideways to the Cabinet Office)  told the House of Commons Treasury Select Committee that "We set out in 2006 to learn lessons in relation to security and to tighten things up."

Why was all the data there when the NAO specifically asked for a subset? Cost was initially offerered as a reason but if they believe that they are being ripped off by the outsourced services.

And when it was known not to have arrived, why was it sent AGAIN (although received the second time) the same way?

We don't know yet whether the data is only mislaid internally, or if it is already in the hands of fraudsters waiting to make their move.

Naturally, this questions their ability to manage a secure National ID Card database.

That is merely the biggest of a continuous stream of admissions of failure to maintain security and privacy

http://www.digitalrights.ie/

The.Data Protection Commissioner in Ireland, Billy Hawkes, has said he has serious concerns about the levels of data security in some public bodies in Ireland that handle large amounts of information about citizens. “We’ve been warning for years about the danger of information about us previously held in silos in the public sector being brought together in centralised databases and accessible to large numbers of public servants,” Mr Hawkes told RTÉ Radio’s News at One programme.    

http://www.electricnews.net/article/10123485.html 

AIB confirmed Thursday evening that a computer error caused 15,000 payment advice slips to be sent to the wrong addresses.  The bank apologised for the mistake and said that it is writing to customers affected. AIB also stated it had informed the Office of the Data Protection Commissioner.

http://u.tv/newsroom/indepth.asp?id=86401&pt=n 

Bank account numbers, National Insurance numbers, names, addresses and dates of birthof up to 60,000 people were on a laptop stolen from a staff member's car in Belfast earlier this week. Derek Alcorn, chief executive of the Citizen's Advice Bureau of Northern Ireland, apologised and added "We can say that the data on the computer is protected by three levels of security including a high level of encryption."

http://technology.timesonline.co.uk/tol/news/tech_and_web/article3007298.ece

Hundreds of websites have been shut down temporarily by one of the largest web hosting companies in Britain after the personal details of customers were stolen by computer hackers.

The hackers managed to access the “master database” of Fasthosts for information, including addresses, bank details, e-mails and passwords.

____________________________________________________________   

2) Software Testing Quality

SoftTest Ireland Test Process Improvement seminar - free

____________________________________________________________
____________________________________________________________

3) Spreadsheets

UK Excel User Conference,Cambridge

If you weren't there, you missed a great chance to learn from the experts and MVPs about pivot tables, advanced charting, data access techniques, and optimising performance. Congratulations to Simon Murphy for organising it. My own presentation on auditing spreadsheets has been supplied to the delegates. It was a one-hour condensation of a half day course. If you'd like to have that in-company, contact me.

Total of records in a table with multiple criteria

http://www.xldynamic.com/source/xld.SUMPRODUCT.html  Multiple Conditional Tests
Bob Phillips provides a detailed discussion on the SUMPRODUCT worksheet function.  It can be used to solve the problem of, for example, getting a sum between two dates, or with extra selection conditions. Also, it can function with closed workbooks, and the handling of text values can be tailored to the requirements.

How to UNpivot an Excel table

Have you ever wanted to straighten out a table of non-normalised data into something that you can pass to a pivottable? John Walkenbach describes a startlingly simple technique to create a simple table where the columns of the original are repeated as rows.

http://j-walk.com/ss/excel/usertips/tip068.htm

How to create a normalised database table from a simple two-variable summary table.

Excel Formula Reference Auditing Utility

Jan Karel Pieterse has  launched the first version of his ExcelRefTool ( the Excel Formula Reference Auditing Utility.)

http://www.jkp-ads.com/ExcelRefTool.asp

JKP is a founding member of: the Professional Office Developers Association
http://www.ProOfficeDev.com

Manager and Auditor perspectives

My ScanXLS product creates such an inventory with a list of errors and unusual attributes of thousands of spreadsheets. The biggest scan I have heard about so far was of a company with 45,000 finance spreadsheets in Australia. At the Audit Technology conference that I spoke at in November, I was asked how one knows what is a risky spreadsheet given just such an inventory with various attributes. In fact, there is no single rule that tells you. Consider the TransAlta spreadsheet mistake that cost them $24M, as reported here:

http://www.theregister.co.uk/2003/06/19/excel_snafu_costs_firm_24m/

I don't know, but that could have been a very simple spreadsheet with just a SUM in it. The problem was the data was pasted in off by one row.  While audit automation software like ScanXLS is going to identify files where some sheet has formulas that refer to empty cells, or inconsistent formulas,  they cannot tell whether this is for someone's coffee pool budget, or a mission critical spreadsheet. That's where sampling and domain knowledge play their part.  Another auditor told me of their experience in asking for an really important spreadsheet and receiving a very simple model with just a few tables of numbers, which passed inspection as clear. Well, of course, if you ask a manager to select something for you to audit them on, what do you think you'll get?

Peter de Jager, a change management consultant who achieved prominence during Y2K, speaks on the nature of organisational change:

http://www.technobility.com/

I remember one of his presentations included a physical metaphor of how to turn pushback into progress; kind of judo meets ballroom dancing!  A big effort in end-user computing management is just that problem of culture change. People with a hero culture like the reputation of being a firefighter, only slightly diminished by the fact that few people notice that it was the same person who started the fire. We've been over this ground a long time ago with software development and testing, so it's time to apply the same lessons to spreadsheet developers, maintainers, and users. To get a handle on your own company's assets of informal software, give me a call.

Spreadsheet Check and Control: 47 best practices to detect and prevent errors

http://www.sysmod.com/az.php?a=190540400X&b=Spreadsheet+Check+Control Available worldwide from Amazon.

http://sysmod.buy.ie/catalog/product_info.php?products_id=188  Our offer - free shipping to EU .

_______________________________________________________
_______________________________________________________

FEEDBACK

Simply send your comments to FEEDBACK (at) SYSMOD (dot) COM

Thank you! Patrick O'Beirne, Editor

_______________________________________________________ _______________________________________________________

4) Off Topic

Roderick Jones RIP

I shared many a platform with Roderick during the Y2K and Euro project years. I received a letter with sad news from his sister Debbie beginning
"It is with great sadness that I have to tell you of the sudden and unexpected death of Roderick on 2nd September in Spain." Megan & I visited Spain last year and had tried to meet up with him but we could not coincide.  We remember him fondly, ever since a picnic near the Albert Hall many years ago.

_______________________________________________________
_______________________________________________________

Copyright (c) Systems Modelling Limited, http://www.sysmod.com . Reproduction allowed provided the newsletter is copied in its entirety and with this copyright notice.

We appreciate any feedback or suggestions for improvement. If you have received this newsletter from anybody else, we urge you to sign up for your personal copy by sending a blank email to   EuroIS-subscribe (at) yahoogroups (dot) com

For those who would like to do more than receive the monthly newsletter, the EuroIS list makes it easy for you to discuss issues raised, to share experiences with the rest of the group, and to contribute files to a common user community pool independent of the sysmod.com web site. I moderate posts to the EuroIS list, to screen out inappropriate material.

Patrick O'Beirne, Editor
_______________________________________________________
ABOUT THIS NEWSLETTER
"Praxis" means model or example, from the Greek verb "to do". The name is chosen to reflect our focus on practical solutions to IS problems, avoiding hype. If you like acronyms, think of it as "Patrick's reports and analysis across Information Systems".
Please tell a friend about this newsletter.
We especially appreciate a link to www.sysmod.com from your web site!
______________________________________________________
ARCHIVES
To read previous issues of this newsletter please visit our web site at http://www.sysmod.com/praxis.htm

DISCLAIMER
This newsletter is prepared in good faith and the information has been taken from observation and other sources believed to be reliable. Systems Modelling Ltd. (SML) does not represent expressly or by implication the accuracy, truthfulness or reliability of any information provided. It is a condition of use that users accept that SML has no liability for any errors, inaccuracies or omissions. The information is not intended to constitute legal or professional advice. You should consult a professional at Systems Modelling Ltd. directly for advice that is specifically tailored to your particular circumstances.
_______________________________________________________
PRIVACY POLICY:
We guarantee not to sell, trade or give your e-mail address to anyone.
To subscribe to this Newsletter send an email to
EuroIS-subscribe (at) yahoogroups (dot) com
To unsubscribe from this Newsletter send an email to
EuroIS-unsubscribe (at) yahoogroups (dot) com
EuroIS is the distribution list server of the PraxIS newsletter. It also offers a moderated discussion list for readers and a free shared storage area for user-contributed files. The archives of this group are on YahooGroups website http://finance.groups.yahoo.com/group/EuroIS/
_______________________________________________________