British Steel paid for my education

British Steel paid for my education. I got a better deal than they did.

My parents weren’t that well off. I wangled a full grant (after year one anyway). University education was financed differently then and sometime in the 5^th  (maybe even the 4^th, I don’t remember clearly) form I noticed that there were companies who were prepared to “sponsor” you to go to college. I applied to everybody who was remotely relevant to the courses I wanted to do. It was excellent practice at filling in application forms and going to interviews (the companies always paid for the travel too. I finished up with an sponsorship from British Steel (General Steels Division, Cleveland).

It was an excellent deal from my point of view. BSC paid me the princely sum of 5 quid per month during term time, which doesn’t sound like much, but it was the maximum they could give me without affecting my grant. You have to consider that I paid 5 quid a week for my slum bedsit in my final year. Mild Ale was 22p per pint in the Students’ Union bar. I was flush!

BSC provided me with 3 months of relevant paid work (including 2 weeks paid vacation, which they insisted I took) during the long vacation 1^st and 2^nd year, and there was an almost guaranteed job offer at the end of my course. BSC called me a “Student Apprentice”. The result was a sort-of self-organised sandwich course, but in 3 years, not 4.

I loved it! BSC put me on a round-the-departments programme shadowing people. I was there in 1979 when BSC “blew in” what was then the largest blast-furnace in Europe (Redcar, since closed https://www.youtube.com/watch?v=rWgsINl8SWw ). I worked in some terrible places too. The coke-ovens at Consett were an experience which I do not regret but would not want to repeat. Everyone knew that plant was doomed.

British Steel were flexible and tolerant. They allowed me to fiddle with the computers (FORTRAN and BASIC) in the evenings. For a several weeks 2^nd Year vacation I would do my job (08:00 – 16:00) at the power station, clean up, walk to the sinter plant, where the “Coordination” computer (ICL 2900 series) was located and then spend 3 hours writing and running heat-transfer simulations. After that I would catch the bus to Middlesbrough, eat a take-away, drink several pints and repeat the following day. Only a 20-something-year-old can live like that.

When I graduated in 1979 I joined BSC and planned to spend at least several years there. “Events dear boy” intervened. In 1980 there was the Steel Strike ( https://steelvoices.wordpress.com/2015/01/02/the-1980-steel-strike-thirty-five-years-on/ obviously left-wing etc but factually correct). I worked through that (I’ve crossed more picket lines than most people). Another experience I would not particularly want to repeat. After the strike was over, my “posting” was to an obsolescent blast furnace plant, and my task was to work out how to shut down the steam distribution system safely. Obviously “the writing was on the wall” so I looked for another job, and found one with a company that designed boilers. (https://www.flickr.com/photos/32859789@N02/7074725003 I was based at the Power Station in this picture)

Ironically (unintended pun), the plant which I was supposed to help to shut down lasted much longer than anyone expected. There were 5 blast-furnaces on the site and 2 of them continued in operation making ferro-manganese alloy metal. They ran until ? and weren’t demolished until 1994 (https://www.youtube.com/watch?v=7DnXcPouT0k ).  The coke ovens on the same site continued in operation until 2015! (http://www.gazettelive.co.uk/news/teesside-news/tears-shed-final-batch-pushed-10095955 )

Playing with elephants - Hadoop?

People who know me know that I'm interested in databases and SQL. I continue to describe myself as an Analyst rather than a Developer or DBA, but I also think it is useful to have a basic understanding of the characteristics of the tools one might be using. I'm busy at the moment but I thought that it was high time I "nailed my colours to the mast".
I intend to start looking at Hadoop before the end of the year.
I know that may seem like a long way off, but it is getting closer all the time.

Here is one of the articles which grabbed my attention: http://www.sqlservercentral.com/articles/Hadoop/99135/

There is nothing like committing that you are going to do something as a bit of encouragement.

Why do I want to look at Hadoop?

• First of all, I want to do it as an excuse to "brush up my Unix" (sorry Linux)
• That makes it an excuse to buy and use some new (to me) hardware.
• It will also be a reason to collaborate with an old acquaintance of mine.
• And I find the idea of "map reduce" intriguing and trying it out is the best way to learn.

As I said, I'm busy, so that's it for this week!

Day to day plans - Having a dynamic To-Do list

I've come to regard myself as being like Winnie-the-Pooh "a bear of little brain". I like to give myself a task, settle down to it and get on with it without distractions. Sometimes the task itself can be quite complicated though.

In recent posts I've shared a view of "Strategic" and "Tactical" plans with you. I know you haven't seen what is inside them but I find having them enormously useful. That brings me to the next level down: the day to day, dynamic To-Do list.

Having a dynamic list of the tasks I have to do is enormously useful. If I'm managing other people doing things, then I need to know what they are doing too. This is what the classic project plan is all about. Even a small team needs a shared plan. I used to create them using spreadsheets but now I use a special purpose tool.

The picture above shows screenshots from today's dashboard. The tool I use is PBWorks Project Hub (http://www.pbworks.com/). I use the "Freemium" version, and I find it adequate for my needs. It does most of the things I would have been doing with a spreadsheet and it does some other things too. 

It is very good at doing the boring Project Office things like bugging people (including me) when they are due to be doing something today. It is also good for collecting reports of progress made or problems encountered. All that has to happen is that people have to get into the habit of making one-line notes on the task they are working on each day.

I have a geographically dispersed team. The two of us exchange notes and have the occasional phone call, but the project dashboard gives us something that we can go back to for status information without a lot of cumbersome bureaucracy (which would usually devolve to me!). The only downside of this approach is that we are using a cloud service which requires internet connectivity and could be vulnerable. That means that I take what I think are appropriate steps to have the critical information stored in some other form elsewhere.

What do you do when things go well? Make a new plan!

A little while ago I shared my “Tactical Plan” with you. Things have gone well! So well that some of the projects have been completed much sooner than I expected. Some of this is down to good luck and having estimates which were not much better than guesses. The question is: what to do now?

I expect we are all (painfully) familiar with the situation where a project is slipping behind the intended schedule. Sometimes the reasons are the same as the reasons for my success: luck, inaccurate estimates and sometimes “force majeure”. After the inevitable struggle to get back on track by cracking the whip, the response is usually to re-plan. That is exactly what I have done in response to my recent success.

Here is the redacted version of my latest tactical plan. In structure it is identical to its predecessor. The changes are in the bits you cannot see.

Let me tell you about what is in the updated plan (without giving any secrets away):

1. The objective was not achieved last time (but that would have been a miracle). So that has remained unchanged.
2. The “New Product” development is still (more-or-less) on track, so that remains unchanged from last time. Basically it is – “Keep working away on the new product!”
3. The “Marketing” project from last time has completed. It’s objective was to create something new. That has been done. Now I have a new project to use what was created by that project as part of a regular activity. I still call it a “project” because it certainly hasn’t become “business-as-usual” yet. I hope it will do eventually.
4.  The “Administration” project from last time has been completed. It was necessary, and it is making things work more smoothly, but it is complete and there is no need for follow-up.
5.  The completion of the “Administration” project has created an opportunity to start something new in the “Marketing”. That has already thrown up some interesting ideas, but the Tactical Plan is reminding me to focus on my current objectives. The new stuff can be considered for the next plan.   

The tactical plan is part of the project wiki (I may show you a little more of that in the future). It is visible to all the contributors and I have a printed copy above my desk, a little to my left, as I type. It is great as a means of reminding us all (especially me) what we have agreed we are concentrating on.  

Do we learn from our mistakes?

This is the time of the year when a lot of us spend a little time reviewing how well we did last year and what we plan to do this year. As you may have noticed, I’m making my plans a little more obvious this year. Before we get too committed to the planning process we should ask ourselves:

Do we learn from our mistakes?

Because if we don’t, or at least if we don’t try to learn from our mistakes, then the activity is essentially futile and we would be better doing something else instead.

I think that the IT industry is particularly bad at this. We have very little sense of history. We claim to be making progress – but are we?

Here is one person’s attempt to write down an outline of that history. I can’t say I agree with it entirely. In fact I haven’t really tested it critically. But do think that it is a worthwhile effort to present one view of that history.

http://timbryce.com/2014/02/10/a-short-history-of-systems-development/

Why do I think the IT industry is bad at learning from it's mistakes?

Why do I think the IT industry is bad at learning from its mistakes? Basically, I think this because I frequently get a feeling that “I’ve seen this before”. Now there is no doubt that a lot of progress has been made. A lot of that progress is down to “Moore’s Law” which has enabled whole industries to sprout, bloom and flourish.

The reducing cost of hardware in general and processing power, storage and communications has enabled things to happen which while they were conceivable, were hardly practical just a few years ago. This blog and the thousands like it are an example of that.

But, if you look at various internet forums as I do, you will find recurring themes which I am going to share with you here and I may pick up as topics at some time in the future. The thing is that many of these meta-topics have been running for donkey’s years!

• How do we write “requirements” so they are understood by the people who want the system and also by the people who are going to build the system?
• How do we create things so that people get what they are expecting?
• How do we estimate how long it is going to take us to do (practically anything)?
• How do we manage a project so that it comes in “to specification, on time and on budget”?
• How do we prevent “silly little bugs” creeping into the system?

Maybe you have some ideas for other topics in the same area, or maybe you have some solutions to some of these conundrums.

Wheels within wheels, plans within plans, but we do need action!

A week ago I shared the front page of the strategic plan for my business with you. Plans are all very well but what we really need is action, but of course we need controlled action which is moving us towards the objective.

With this in mind, I created what I've described as a "Tactical Plan" which I'm going to share with you. The tactical plan sets out an objective and some projects I am going to be working on for the first 3 months of 2015. I've included a redacted plan below. I first heard the word "redacted" in association with US Government documents. I think I would probably use the word "censored" instead. By-the way the Russian word for "editor" is "redactor" (редактор). I'm not sure if that is one of the little tricks laguages play on us, or whether it tells me anything about the Russian attitude to editing.

That really is all there is to the Tactical Plan. It is pinned to the wall in my office and it reminds me what I am achieving at the moment. It helps me to focus and keeps me from getting distracted. Of course, there are more detailed plans as well.

As you can see from the redacted plan, I have an objective, an end date and three projects. The three projects are addressing areas which I know need work: Product and Marketing are hardy perennials and in this case I felt it was time to do a bit of spring-cleaning in the thing I use for the detailed plans and tracking. I may show you that some time in the future.

It's always good to have a plan!

This is the time of year for reviewing what we have  done and thinking about what we are going to do. It is the time for making plans. The month of January is supposed to be named after the Roman god "Janus" (although there is some dispute about this)

Janus is depicted as having two faces and was the god of doorways and gates. He looked both inwards and outwards, forwards and back. In my opinion Janus is a good character to bear in mind when writing plans and reviews.

In the middle of 2014 I decided that it was high time that I wrote a "Business Plan" for my little business. I've done this sort of thing before for other people, but it feels a bit different when it is for yourself.

As it says at the bottom of the front page: 

"Duhallow Grey Geek started without a clear business plan. This document rectifies that. It summarizes the current situation and identifies options. It identifies how tactical plans will be created and provides an outline for the next one to two years."

Before you start writing (or even researching) any document, it is a good idea to decide who you are writing it for. In this case the answer was: for ME! That's right - for myself! Of course, I may want to present it to potential investors or business partners but I am the person making the largest investments in terms of effort, time and life. If I think I am going to be wasting my time and effort, I want to find out now, so I can do something more rewarding.

There are plenty of templates for what a business plan should contain, so I won't share the detailed table of contents. In fact I found myself adding things to the standard contents. Some of the things I included (which you may, or may not, think are "standard") are:

• Motivation - Why was I doing this? Why was I excited about it?
• The current position of the business - In terms of product and sales.
• Product - What is the product? 
• Market - Who buys the product?
• Industry - What is happening in the industry I'm involved in? Where is the growth?
• What resources and capabilities do I have access to?
• Constraints - What are the restrictions that I want to apply to the business?

While I was mapping out the contents, I made a list of the questions I wanted to answer and used them as the basis for research. In the end I produced appendix material on:

• The economics of the industry I am working in (on-line training material)
• Sales - past performance and future projections
• Marketing options
• Alternative sales channels
• Successful competitors

Predicting future sales is always difficult. In the end, I didn't try and make predictions. Instead I projected the past performance into the future and then identified the ways I could improve it. I also identified high and low levels which I could use to plan potential investments. 

The "Strategic" document I've produced, documents the facts and identifies the options. On the basis of the information available, I've picked some things I am going to do (in fact, I've started doing them already) and created what I term a "Tactical Plan" for a fixed term. I've going to "do the actions" in the Tactical Plan and monitor the results. Towards the end to the period of the plan I will review the results and decide what to do next. 

Wash, rinse, repeat....
  
Is it all going to work? I don't know. What am I going to do? That would be telling! Keep watching and you'll find out.

Mind maps and SQL

In a recent discussion on LinkedIn, I mentioned that I use Mind-mapping. I generally prefer pen and paper or pen and white-board, because I don't like to be constrained by what the tool wants to do. I do use Freemind sometimes, and I said in the discussion that I what I sometimes do is:

1. Create the mindmap freehand
2. Transfer that into Freemind - which consolidates the thinking and gives me something with is tidy and easier to maintain, and then
3. "Print" the map to pdf - which is easy to distribute and can form the basis of discussion at a distance.

I thought I would illustrate this with an example:

A recent project of mine has been creating an introductory course titled "SQL and Relational Databases for analysts".

The objective of the course was to give a basic understanding of SQL to Business and Technical Analysts.
It was intended to use MS SQL Server, but not be a course on SQL Server. The reason for this was to make the skills learned as portable as reasonably possible.

As it was intended to be an introduction, certain things I would like to have included (like UNION, HAVING and the database catalogue) didn't make the cut on grounds of keeping the size of the course down.

Anyway, the content of the course was documented in a mind-map which was then discussed with people in different places over a short period. I've attached the final version of the mind-map.
Everything in the mind-map (with the exception of the "title block" in the middle) was produced in Freemind.

The mind-map proved to be useful for agreeing what the content and structure of the course was going to be and then as a reminder of scope during the development of the course.

Here's mind-map (it was intended to be printed, if that ever happened, on A3 paper).

Practice makes…?

One of the issues with working from a home office, as I do a great deal of the time, is “education” or “training”. I need to keep up to date. I need to learn about new things. The problem is that very few opportunities come and knock on my door. Of course, the internet is a wonderful thing, but it is like a good public library. If you like reading, you can get lost or even lose yourself in there. That’s where personal recommendation comes in.

Quite recently I found a education site called Udemy. Maybe you knew about it already, I didn’t. I decided to take a couple of courses to find out if I liked the experience: I did, and I do.

One of the courses I took was called “Performance of Speaking” by a man called Tom j Dolan (he writes it like that, so I will as well). I confess, that one of the reasons I took the course was that I was curious about how effective a course in such a subject could be as distance learning. All I can say is “it worked for me”.

I consider myself a reasonable public speaker. I am comfortable addressing a room containing tens or maybe even a hundred people. I haven’t tried addressing a stadium full yet but maybe that will come. Never-the-less I felt there was room for improvement.

Tom’s credentials are excellent and his approach is quite simple: public speaking is a practical skill. It is something which can be learned. He makes an important point: many of us are too critical of ourselves. We demand “perfection” (whatever that is). That is really an unreasonable demand we are making. Instead we should aim for improvement “Kaizen” as the Japanese would have it. Continuous improvement is a better goal than perfection. We can usually improve. The best musicians practice constantly.

There are many skills like this. We can learn the facts, we can answer questions and give the “right” answers, but to become really good at them, we have to practice. We (or at the very least, I) are creatures of habit. When we first learn a new behaviour it takes a great deal of effort. As we practice we get better at the execution of the behaviour, but not only that, we also find that we have more capacity to think about how and why we are doing it. Experience is a valuable thing.

As part of something I am doing at the moment, I need to record and then edit my own voice. Tom’s course has helped me get used to the awkwardness I felt. It hasn’t changed the content at all but it has improved the delivery and also how I feel about the delivery. 

My first job (as a bottle-washer)

Just recently the great and good have been telling people on LinkedIn about their first day at work. I don't want to feel left out, so I thought I share what I remember about my first day at work. Actually, I've had several starts, all in different locations and under different circumstances. If you like, this is the "first, first"!

The job was supposed to be a fill-in while I retook an exam to improve on the grades which I needed to get into the university course I wanted . I remember that I had been given no notice of the interview - quite literally I had been asked "Can you go NOW?" and I'd gone - THEN! I had been unkempt and unshaven. The interview was on a Wednesday or Thursday and I must have said the right things, because they asked me if I could start on the following Monday.

The job was as a lab assistant in a small research laboratory. My responsibilities were to be quite varied, basically: do as you're told by your superiors (which meant almost everyone else!). I thought of it as being "bottle-washer".

On the Monday, the post arrived as I was about to set off for work. It contained an unconditional offer for the university course I wanted. They said they thought I could cope with the grades I had. So, I turned up on my first day at work and handed in my notice!

In fact, I told my new employer that if they preferred, I would "not start at all" and we could call the whole thing quits. They were really decent about it and said that I could have the job until I was due to go to university. Excellent I thought: relevant experience and two months of pay. Just the start I needed.

My job really was "washing bottles", and test-tubes and beakers and flasks and all the other paraphernalia of a chemical laboratory. I had to learn pretty quickly that we had some real nasties. I spent at least some of my time working with chromic acid which is really not good to come in contact with. One of the things we worked with was ion exchange resin which came as tiny polystyrene beads. I had to be really careful not to spill any of the wet beads on the floor because when they dried they became like little ball-bearings and on a hard lino floor the effect could be really quite dangerous.

Some pleasant memories are:

• Being told off because "I walked like the lab manager" and the sound of my footsteps made some of my colleagues uneasy (to this day, I don't know what they were up to). 
• Playing cricket in the park opposite the lab during lunch break. The wickets were old retort stands and the bat was kept in one of the equipment drawers.
• And playing cards with the other workers on the wet lunch breaks.

Less pleasant memories are:

• Doing seemingly endless titrations to get the "break-through point" on a sample ion exchange resin,
• And the smell of the Amination Room where we kept the fume cupboards and unpleasant materials.

It was a good start!

Have you considered using the cloud?

…I know I have. If you read the blurb being written by all and sundry (and now including me) you would be forgiven for imagining that the entire world either lives with its head in the cloud, or is considering doing so in the near future.

As I allow myself a limited budget of both time and money for “education” I’m careful what I spend it on.  Last Thursday (24^th October 2013) I went to the “Cloud Success Roadshow” in Limerick. It was well worth my investment in time.

http://cloudsuccessroadshow.com/

The roadshow is run by a company called Let’s Operate (http://www.letsoperate.com/) and although they show you their products, they definitely didn’t go in for the hard sell.

The roadshow reminded me of the factors I need to consider about my “IT strategy” as a whole. In a lot of cases the issue is not finding out what the characteristics of the “Cloud Option” will be, but finding out matching characteristics of the alternative. For example:

• “The Cloud will cost x” (per user/month), but how much does my server actually cost me?
• For that matter, how much is all that data worth (to me)?

Thinking properly about the Cloud will almost certainly make you think very hard about the speed and quality of your broadband connection and your dependence on it. If, like me, you live out in the country and work from home some of the time then this is important.  Never mind the quality, a year ago, I lost my broadband when some “eejit” demolished a telegraph pole just down the road! I was sent scurrying down the road to borrow a connection from an acquaintance in order to send a vital eMail reply. I’ve done something about that.  

  

There are still a couple of stops planned on the roadshow. If you live in Dublin or Belfast I suggest you consider spending an afternoon there if you have the time.

Will losing constraints set you free?

I’ve been busy with a project, I’ve finally got round to writing this a week later than I intended…

In a recent conversation, someone pointed out that people sometimes remove “constraints” from a database in order to improve performance. This made me ask myself:

Is this a good thing, or a bad thing?

I have to admit that this is a technical change that I have considered in the past. Never-the-less, I have mixed feelings about it.

After some thought, my opinion is:

• For many situations a constraint is redundant. The fundamental structure of many applications means they are unlikely to create orphan rows.
• The cost of the constraint is in the extra processing it causes during update operations. This cost is incurred every time a value in the constrained column is updated.
• The benefit of a constraint is that it absolutely protects the constrained column from rogue values. This may be particularly relevant if the system has components (such as load utilities or interfaces with other systems) which by-pass the normal business transactions.
• Other benefits of constraints are that they unequivocally state the “intention” of a relationship between tables and they allow diagramming tools which navigate the relationships to “do their thing”. Constraints provide good documentation, which is securely integrated with the database itself.

In short:

• The costs of constraints are small, but constant and in the immediate term.
• The benefits of constraints are avoiding a potentially large cost, but all in the future.

It’s the old “insurance” argument. Make the decision honestly based on a proper assessment of the real risk and your attitude to taking risks. Be lucky!

More Detailed Argument

For those who don’t just want to take my word for it. Here is a more detailed argument.

Let’s take the “business data model” of a pretty normal “selling” application.

When we perform the activities “Take Order” (maybe that should be “Take ORDER”), or “Update Order”

• we create or update the ORDER and ORDER_LINE entities, and
• in addition we refer to PRODUCT (to get availability and Price) and presumably to the CUSTOMER entity which isn’t shown on the diagram.

When I translate this into a Logical data model, I impose an additional rule “Every ORDER must contain at least 1 ORDER_LINE”. The original business model doesn’t impose this restriction.

Remember some people do allow ORDERs with no ORDER_LINES. They usually do it as part of a “reservation” or “priority process” which we are not going to try and have here.

When the transaction which creates the ORDER and ORDER_LINE makes it’s updates, then it will have read CUSTOMER and ORDER, so it is unlikely to produce orphan records, with or without constraints.

On the other hand, by having the constraints we can document the relationships in the database (so that a diagramming tool can produce the ERD diagram (really I suppose that should be “Table Relationship Diagram”)).

I am left wondering whether it would be possible or desirable to enforce my  “Every ORDER must contain at least 1 ORDER_LINE” rule. I’ll think about that further. (Note to self: Can this be represented as a constraint which does not impose unnecessary and unintended restrictions on creating an ORDER?)

If we don’t have constraints and we have something other than our transaction which is allowed to create ORDERs and/or ORDER_LINEs (As I said, typically this would be an interface with another system or some kind of bulk load), we have no way of knowing how reliably it does it’s checking, and we might be allowing things we really do not want into our system. Constraints would reject faulty records and the errors they created (or “threw”) could be trapped by the interface.

    

What if you don’t have a Data Model?

My previous post got me thinking. One thing leads to another as they say. The whole of my process really requires that you have a Data Model (aka Entity Relationship Diagram/Model and several other names). But what do you do if you don’t have one, and can’t easily create one?

What’s the problem?

Suppose you have a database which has been defined without foreign key constraints. The data modelling tools use these constraints to identify the relationships they need to draw. The result is that any modelling tool is likely to produce a data model which looks like the figure below. This is not very useful!

Faced with this, some people will despair or run away! This is not necessary. Unless the database has been constructed in a deliberately obscure and perverse way (much rarer than some developers would have you believe) then it is usually possible to make sense of what is there. Remember, you have the database itself, and that is very well documented! Steve McConnell would point out that the code is the one thing that you always have (and in the case of a database, it always matches what is actually there!).

To do what I propose you will need to use the “system tables” which document the design of the database. You will need to know (or learn) how to write some queries, or find an assistant who understands SQL and Relational Databases. I've used MS SQL Server in my examples, but the actual names vary between different database managers. For example: I seem to remember that in IBM DB/2 that’s Sysibm.systables. You will have to use the names appropriate for you.

The method

“Method” makes this sound more scientific than it is, but it still works!

1. Preparation: Collect any documentation
2. “Brainstorm:” Try to guess the tables/entities you will find in the database. 
3. List the actual Tables
4. Group the Tables: based on name
5. For each “chunk”, do the following:
1. Identify “keys” for tables: Look for Unique indexes.
2. Identify candidate relationships: Based on attribute names, and non-unique indexes.
3. Draw your relationships.
4. “Push out” any tables that don’t fit.
5. Move on to the next group.
6. When you’ve done all the groups, look for relationships from a table in one group to a table in another.
7. Now try and bring in tables that were “pushed out”, or are in the “Miscellaneous” bucket.
8. Repeat until you have accounted for all the tables.

At this point you are probably ready to apply the techniques I described in my previous post (if you haven’t been using them already). You might also consider entering what you have produced into your favourite database modelling tool.

The method stages in more detail.

Preparation: 

Collect whatever documentation you have for the system as a whole: Use Cases, Menu structures, anything! The important thing is not detail, but to get an overview of what the system is supposed to do.

“Brainstorm:” 

Based on the material above, try to guess the tables/entities you will find in the database. Concentrate on the “Things” and “Transactions” categories described in my previous post. 

Don’t spend ages doing this. Just long enough so you have an expectation of what you are looking for.

Remember that people may use different names for the same thing e.g. ORDER may be PURCHASE_ORDER, SALES_ORDER or SALE.

List the tables:

select name, object_id, type, type_desc, create_date, modify_date

from sys.tables

(try sys.Views as well)

       

Group the tables

Group the tables based on name: ORDER, ORDER_ITEM, SALES_ORDER, PURCHASE_ORDER and ORDER_ITEM would all go together.

Break the whole database into a number of “chunks”. Aim for each chunk to have say 10 members, but do what seems natural, rather than forcing a particular number. Expect to have a number of tables left over at the end. Put them in a “Miscellaneous” bucket.

Identify the candidate keys, and foreign keys from the indexes

select  tab.name, idx.index_id, idx.name , idx.is_unique

from

sys.indexes as idx

join sys.tables as tab on tab.object_id = idx.object_id

where

tab.name like '%site%';

select tab.name, col.column_id, col.name 

from

sys.columns as col

Join sys.tables as tab on col.object_id = tab.object_id

where

tab.name like '%PhoneBook%'

order by 1, 2 ;

 

From attribute names and indexes, identify relationships

• Sometimes the index names are a give-way (FK_...)
• Sometimes you have to look for similar column names (ORDER_LINE.ORDER_ID à ORDER.ID)
• Multi-part indexes sometimes indicate hierarchies.

 Push Out

Look for "Inter.group" Relationships

How to “dig into a large database”?

This posting was prompted by a question on one of the BA forums on Linked In. The original question was:

“So as a business analyst, whats your procedure to begin investigating a very large, unfamiliar database and then draw your understanding?”

I gave an answer in the forum, but here is an expansion, and some ponderings.

First of all, let’s set out the terms of reference. The question asks about a “large unfamiliar” database. I think we can assume that “unfamiliar” is “one that we haven’t encountered before”, but what is “LARGE”? To me “large” could be:  

•  Lots of tables
• Many terror-bytes ;-)
• Lots of transactions
• Lots of users
• There may be other interpretations

 I’m going to go with “Lots of tables” with the definition or “lots of” being:

“more than I can conveniently hold in my head at one time”

I've also assumed that we are working with a "transactional database" rather than a "data warehouse".

Preparation

Gilian, the questioner was given some good suggestions, which I summarised as "Collecting Information" or perhaps “Preparation”:

• Understand objectives of "The Business"
• Understand the objectives of "This Project" (Digging into the Database)
• Collect relevant Organisation charts and find out who is responsible for doing what
• Collect relevant Process Models for the business processes which use the database
• Get hold of, generate, or otherwise create a data model (Entity Relationship Diagram or similar)

Of these, the one which is specific to working with a Database is the ERD. Having a diagram is an enormous help in visualising how the bits of the database interact.

Chunking

For me, the next step is to divide the model into "chunks" containing groups of entities (or tables). This allows you to:  

• Focus - on one chunk
• Prioritise - one chunk is more important, interesting or will be done before, another
• Estimate - chunks are different sizes
• Delegate - you do that chunk, I'll do this one
• And generally "Manage" the work;  do whatever are the project objectives.

I would use several techniques to divide the database or model up into chunks. These techniques work equally well with logical and physical data models. It can be quite a lot of work if you have a large model. None of the techniques are particularly complicated, but they are a little tricky to explain in words.

Here is a list of techniques: 

• Layering
• Group around Focal Entities
• Process Impact Groups
• Realigning

Organise the Data Model

I cannot over-emphasis how important it is to have a well-laid out diagram. Some tools do it well, some do it less well. My preference is to have “independent things” at the top.

I’ve invented a business.

• We take ORDERs from CUSTOMERs. 
• Each ORDER consists of one or more ORDER_LINES and each line is for a PRODUCT.
• We Deliver what the customer wants as DELIVERY CONSIGNMENTS. 
• Each CONSIGNMENT contains one or more Batches of product (I’ve haven’t got a snappy name for that).
• We know where to take the consignment by magic, because we don’t have an Address for the Customer!
• We reconcile quantities delivered against quantities ordered, because we sometimes have to split an order across several deliveries.
• That’s it!

Layering

"Layering" involves classifying the entities or groups of entities as being about:

• Classifications
• Things
• Transactions
• Reconciliations

Things

Let’s start with “Things”. Things are can be concrete or they can be abstract. We usually record a “Thing” because it is useful in doing our business. Examples of Things are:

• People
• Organisations
• Products
• Places
• Organisation Units (within our organisation, or somebody elses)

Classifications

Every business has endless ways of classifying “Things” or organising them into hierarchies. I just think of them as fancy attributes of the “Things” unless I’m studying them in their own right.  

Note: “Transactions” can have classifications too (in fact almost anything can and does), I’ve just omitted them from the diagram!

Note: The same structure of “Classification” can apply to more than one thing. This makes sense if, for example, the classification is a hierarchy of “geographic area”. Put it in an arbitrary place, note that it belongs in other places as well, and move on! 

Transactions

Transactions  are what the business is really interested in. They are often the focus of Business Processes.

• Order
• Delivery
• Booking

Where there are parts of Transactions (eg Order_Line) keep the child with the parent.

Reconciliations

Reconciliations" (between Transactions) occur when something is “checked against something else”. In this case we are recording that “6 widgits have been ordered” and that “3 (or 6) have been delivered”.

 If you use these “layers”, arranged as in the diagram,  you will very likely find that the "One-to-manys" point from the top (one) down (many) the page.

Groups around Focal Entities

To do this, pick an entity which is a “Thing” or a “Transaction” then bring together the entities which describe it, or give more detail about it. Draw a line round it, give it a name, even if only in your head!

• "Customer and associated classifications" and
• "Order and Order_line" are candidate groups.

Process Impact Groups

To create a "Process Impact Group"

• Select a business process
• Draw lines around the entities which it: creates, updates and refers to as part of doing its work.
• You should get a sort of contour map on the data model. 

In my example the processes are: 

• Place Order
• Assemble Delivery Consignment
• Confirm Delivery (has taken place)

It is normal for there to be similarities between “Process Impact Groups” and “Focal Entity Groups”.  In fact, it would be unusual if there were not similarities!

Realigning

Try moving parts under headers (so, Order_line under Order) and reconciliations under the transaction which causes them. In the diagram, I’ve moved “Delivered Order Line” under “Delivery”, because it’s created by “Delivery related processes” rather than when the Order is created.

Finally, “Chunking”

Based on the insights you have gained from the above, draw a boundary around your "chunks".

The various techniques are mutually supportive, not mutually exclusive. The chunks are of arbitrary size. If it is useful, you can: 

• combine neighbouring chunks together or
• you can use the techniques (especially "Focal entities" and "Process Entity Groups") to break them down until you reach a single table/entity.

Tools

My preferred tools for doing this are: a quiet conference room, late at night; the largest whiteboard I can find; lots of sticky Post-its or file cards (several colours); a pack of whiteboard pens; black coffee on tap and the prospect of beer when I’ve finished. For a large database (several hundred tables) it can take several days!

Once you've done all this, then all you have to do, is do the work!  

 I hope you have as much fun doing it as I had writing about it! J

Security – Using Server Side security with MS SQL Server

There are times when I want to do something, and it gives rise to questions. Often I have to put the questions to one side in order to get on with the thing which is my immediate priority. When that happens, I put the questions to one side with the intention of coming back to them in the future. This is one of the occasions when I have had the opportunity to go back and answer some of the questions.

The questions in this case were:

• How does Server-side security work on MS SQL Server? And
• How can I control the access different users have to my database?

I’m not (have never been, and do not really intend to become) a DBA (Database Administrator). I understand a bit about “privileges” but in the past I’ve always had someone else “doing it for me”, and in any case , I’ve worked on other databases such as DB/2 or Oracle.

After a little bit of research and a bit of experimentation (fiddling around), I found the answer to my questions. Although it was hardly earth-shattering, the understanding is satisfying.

The key is to understand that in MS SQL Server the “Server Instance” and the “Database” are separate entities. It is possible to have several databases inside the same Server instance. In fact I do it all the time when I’m experimenting. This means that there are two separate things to define:

• A LOGIN, which gives access to the Server Instance, and with Server-side authentication, provides the security, and
• A USER, which belongs to the Database and is granted the Database privileges (including CONNECT).

Figure 1 Server-side security for MS SQL Server

Summary of the stages: 

1. Ensure that SQL Server is set up to allow Server Authentication
2. Create the LOGIN (in the SQL Server Instance)
3. Create a USER corresponding with the LOGIN (in the Database)
4. Grant the USER CONNECT privilege (happens by default, but can be revoked)
5. Grant the USER the appropriate privileges on the Database

If you're interested in taking this a bit further, I’ve summed all this up in a video on YouTube.

What is the point of Unit Testing?

I don't normally add two entries to my blog on the same day but something attracted my interest. Somebody asked the question: “What is the point of Unit Testing?”

Actually, they asked two questions:

• Why do we perform Unit Testing, even though we are going to do System testing?
• What are the benefits of Unit Testing?

To which my immediate thought responses were:

• Would you deliberately make something from parts you knew were broken? Or
• Would you make something from parts which you suspected were broken?

And then I thought "that might sound a little rude" and reconsidered…

Modern development methods have lots of benefits, but sometimes in flexible and rapid methods something gets lost. People forget why things are done. Or, if they’ve never been told, they wonder if they are worth bothering with.

Now, you should always question everything, but sometimes things are there for a good reason. If you plan to take something away;

• Understand why it was there in the first place,
• If it is no longer needed, explain why it is no longer needed.
• Understand (and be prepared to live with) the consequences of taking it away.

An old-fashioned view of a System Development Process

(by the way, you’ll notice that some of this material has been re-cycled from elsewhere)

If we take a rather old fashioned view of systems development using a “waterfall” model, then we will have a number of phases (an old IBM Development Process, but does n’t really matter).

• Each phase produces something, and the stage below it expands it (produces more “things”) and adds detail.
• The “Requirements” specify what things the system needs to do. They also identify the things that need to be visible on the surface of the system.
• For each of the things that need to be visible on the surface, we need an “External Design”
• The “External Design” specifies the appearance and functional behaviour of the system.
• For everything we have in the “External Design” we need a design for the “Internals”
• And finally someone needs the “Build” what we have specified.

You can view this as a waterfall, down the side of a valley. The process is one of decomposition.

I don’t especially recommend “Waterfall” as a way of running a development project, but it is a simple model which is useful as an illustration.

The Testing Process

On the other side of the valley we build things up.

• Units are tested.
• When they work they are aggregated into “Modules” or “Assemblies” or “Subsystems”, which are tested.
• These assemblies are assembled into the System which is tested as a whole.
• Finally the System is tested by representatives of the Users.

The process is one of developing “bits”, testing the bits and then assembling the bits and then testing the assembly.

The assembly process (in the sense of “putting things together”, not compiling a file written in “assembler”) costs time and effort. Parts are tested as soon as practical after they are created and are not used until they conform to their specification. The benefit is that we always working with things that we think work properly.

In a well-organised world, you would like to think that the Users are testing against the original requirements!

Development and Testing should be mutually supportive

What should happen is that at every level, each component or assembly should have some sort of specification (it may be a very rudimentary specification, but it should still exist) and it should be tested against that.

In fact, there is a thoroughly respectable development approach called “Test Driven Development”. The idea here is that the (Business) Analyst writes a “Test” which can be used to demonstrate that the system, at whatever level, is doing what it is supposed to be doing. Of course, the Analyst may need help to write an automated test, but the content should come from the Analyst.

This approach is really useful all the way through the development process. It’s a really good idea if a developer writes tests for the code s/he is writing before the code! In fact, I have known places where they insisted that a test was written for a bug before the developer attempted to fix the bug. That way demonstrating the fix was easy: Run the test without the fix – Test demonstrates the bug. Apply the fix and run the test again.

The Cost of Not Doing Unit (or other low-level) Testing

All bugs are found at the topmost level, which means that they are found after the product has been assembled or “built” and then we have to work out where the error has actually originated.

The Benefits of Unit Testing

• Bugs are found sooner, and they are found closer to the point at which they are created.
• Unit testing lends itself to automated testing which can be integrated with the build process. Ask a professional Java developer about “JUnit” or a Python developer about “UnitTest” (one word).
• Automated testing increases the chances of trapping “regression bugs” as code is enhanced and bugs are fixed.

All of the above mean that well-planned and executed Unit Testing results in:

• Reduced overall cost
• Improved product quality

Oracle and Courses

My personal development time last week was spent completing an online course "Oracle DBA for absolute beginners".

I wouldn't have described myself as an "absolute beginner", but I found plenty to enjoy in the course and came away having learned quite a bit about what is going on inside Oracle, and I assume most other database managers.

Circumstances influence what we do in life and so far I have had much more exposure to DB/2 and MS SQL Server than to Oracle. That hasn't been a decision on my part, simply the choices that had been made for the projects I was involved in.

In a similar way, I've spent much more time "dealing with users" as a Business Analyst, than I have working out how to manage the space requirements and performance of a database. It does me good to learn just a little about the things a DBA has to consider. I don't have to let those considerations govern what I consider the requirements to be, but at least I can understand where other people are coming from.

Taking the course led me to what you might consider "meta" thinking: thinking about not the content of the course, but the way it was presented and the platform Udemy on which it was presented.

I find Udemy interesting. It seems to work well. It certainly worked for me.

Udemy seem to be aiming to be a "neutral marketplace". The courses belong to the course instructors. Of course Udemy have standards for courses, but beyond the usual "fit to print" conditions, they are mostly technical standards (quality of video and sound) rather than subject matter related. In a similar spirit, Udemy promote the platform, but the promotion I have seen seems to be fairly neutral with regard to individual courses. On the other hand, instructors or course owners are completely free to advertise their wares elsewhere and direct potential customers into Udemy. It's a simple model which I think I will investigate further.