System Administration

No matter how you interact with the digital world you will always come across a person with the title System Administrator (SA).  Looking through many websites and definitions here are some major requirements that are mentioned again and again in every article I have reviewed:

“The responsibilities of the system administrator typically include ensuring the uptime, performance, resources, and security of the computers they manage meets the needs of the users.  This includes establishing and managing user accounts.”

Running the system is fine – anyone involved in IT has a responsibility that whatever they administer, develop or design meets the standards required of them.  But where do these standards come from?  This question leads me onto the next part of the requirements which is that all SAs should make themselves aware of – the users.  Whether or not they like it SAs are there because users interact with the systems they are meant to maintain and develop.  Too often SAs forget this and start making demands of the user that are unacceptable.  Fair enough, the user must pass stringent security criteria to gain access to sensitive data, but that is not be at the behest of the SA – it is because of business rules and because of the law.  The SA does not decide on those rules and they have no other input to the rules except to enforce them.  They are, above all, administrators of said rules but seem in many circumstances to get too big for their booties.

I have been an Administrator in many of my roles - whether that be systems, databases or otherwise.  I ensure that my systems, databases, etc. work the way they should and the users never sees the work I do.  This may sometimes backfire, insofar as they will think my job is effortlessly completed and anyone could do my job but that is rarely the case.  I have entered many organisations whereby gaining access to restricted systems is a painful and arduous pursuit.  I generally have to work with many systems where the administrators have no idea what collaboration means.  There is usually no central hub where I can send all of the applications for access to the systems I need to complete my work, meaning I can be chasing my tail for weeks.  Why organisations don’t put a procedure in place whereby a newbie can get the correct access centrally and efficiently is beyond me!

As for my experiences with SAs that should not be in that role, that is equally abhorrent.  I have requested access to systems where I will be designing and developing solutions to inherent problems and issues found within the organisation that hired me (challenges or opportunities are the words that are bandied about these days).  Not giving me access won’t stop me.  I usually build what I want on my local machine anyway so that I can guarantee uptime on my own PC.  Once built, I will document my solution and once accepted the current system will now have to reflect the changes that I put forward.  This means the administrators that refused access now have to adapt and change the existing set-up being used, meaning they have to work harder. 

That’s the access issue.  What about when your access key (password, card, etc.) is attached to someone else’s profile?  This isn’t as rare as you may think.  This would mean that I now have access to someone else’s data because the SA did not do the correct job.  The only reason that this bothers me is that someone else may be attached to my profile.  With the systems that I need to get access to it can be very dangerous for any organisation, both for sensitivity of data and the changes that could be made by someone who may have malicious intent.

SAs should get back to what their job is meant to be about.  They are there to maintain and administer a system that is both efficient and effective for the user.  All systems are put in place because of these reasons, whether IT based or not.

SQL VIEWS

A quick note on SQL views.  “In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.” – this is the definition of a SQL view.  I wish more people would realise the importance of this definition.  It essentially means that, although the view in question may seem like a table when queried through Management Services or something similar, it is just that – it is a result of a SQL query statement.

Too many times I have come across databases that are almost entirely built with views because the person building the queries does not realise this fundamentally important principle.  Don’t get me wrong – views have their place in many databases, especially smaller ones where performance is not a major concern.  The danger is when the database continues to grow and develop and the developer / perpetrator does not change the way the data is being presented.  You will then find that you have a SQL statement that takes longer and longer to run.  The reason? You develop a query that produces the resulting data that you were asked to provide and this queries a view.  In turn, this view may query more views which may also query more, ad infinitum.  Nested queries can be a ticking time bomb if not kept in check and the dangers become apparent very quickly as time goes on. 

If you want a report or query to run properly, my advice is to get rid of your views.  There should never be a complex query run on a view and there are NO exceptions.  Don’t listen to anyone who says different.  These are people who avoid and fall in the realm of Einstein’s advice – “if you can’t explain it simply, you don’t understand it well enough”.  I’ve met many people like this in my time and the only advice worth giving about them is to stay well clear.  This type of person will not help you in your endeavours.

So, my advice?  Use views sparingly and respect your users and servers. 

Learning Visual Basic

A friend of mine brought up the fact that he had previously mentioned VBA to me.  This can be confusing insofar as the available routes are VB and VBA, so I thought I’d give a definition here:  “Visual Basic makes it mandatory for a user to create the application instances to manipulate one or more of the Office application objects. In contrast, Visual Basic for Applications, that is a subset of VB, executes its instructions inside one of the office applications”.  Essentially, to use VB I had to install and run Microsoft Visual Studio, whereas VBA can be run within a macro in Excel.  Both are based on the BASIC language, which I have used extensively in a past life, so it should be straightforward.

Anyway, I thought I’d get familiar with both I’ve taken the course VB Fundamentals for Absolute Beginners with Bob Tabor on Microsoft Virtual Academy (MVA) – they have a wealth of courses online and are always worth a visit.  This is an excellent course (and it helps that Bob is very easy to listen to) and well worth anyone who is interested in learning VB looking into.  It will show all the basics to get you on your way and it will show you how to complete building an application from scratch or at least “Explore the concepts of using VB” (the latter as per course description).

The next course I took a look at is the Excel Easy VBA course.  This is very much targeted at people who want to write macros in Excel and is a great course to get anyone who wants to write macros started.  The only thing I would say about this short course is that they don’t tell you how to add the labels for the form in the last section.  I’ve used SQL Server Reporting Services (SSRS) before so I just winged it and the properties for all of the controls are similar enough.

Both of these courses are excellent, and I’m glad I took the time to complete them both, as both have a lot to offer.  VB is offered for developers who want to learn more about what .NET can offer.  The VBA course is offered for Excel superusers who want to get more out of Excel.  You don’t have to make a choice – you can do both, as they’re free.

I will be expanding on both in a future article and give examples of when and where to use either.

Learning Amazon Web Services (AWS)

I met with a friend the other day and he mentioned Amazon Web Services (AWS) so I thought I’d look into getting up to speed with it.  I have completed many courses on the Azure offering from Microsoft so I thought it would be worth getting an insight into the Amazon offering since, at the date of writing, they are the market leaders in Cloud services.

This is just a brief note to say that I will expand upon this article when I start using the service so it can be seen as a placeholder for the details needed to set up AWS.  I’ve taken the course Cloud Computing With Amazon Web Services with Udemy – they have a wealth of courses online and are always worth a visit.

Here’s the summary for the course – you learn how to:

• Create and configure virtual servers
• Configure storage and CDN (Content Delivery Network)
• Configure monitoring and notification services
• Configure databases
• Configure a highly available and scalable environment
• Configure DNS

Having completed the course in one evening, I can safely say that I have learnt how to set up the AWS service from start to finish and I’m very impressed.  It looks like something I can certainly use in the future knowing my data is securely held there.  I also now know how to host my own website, set up my databases and have a private network with AWS, so I will definitely be setting myself up with the service when I get the time.  

All in all, I know the cloud is the way to go and AWS certainly offers me what I need right now and what I will need in the future.  Now the question is whether I go down this route or the Microsoft Azure route?  But that’s a discussion for another day!

Reporting for Business

There’s a great deal of chatter that abounds about reporting, and too many sites for me to identify as to which ones may be best for you to visit.  There are hundreds of options available and the sites out there can be varied in their content.  Many are sales-oriented and should be avoided.  To get a list together have a tech-savvy person and a business person to review the major products and then look at them in more detail – this is not a small job but, if looking to invest a great deal of money in a product, don’t leave it to people who don’t understand the end-to-end process of what is involved.

There are all sorts of processes out there to decide what your path and direction should be for deciding on a reporting solution.  The bottom line is that if you let the business side of the organisation decide the best way to go, “you will end up with a solution that will never work and will have to go back to the drawing board”.  If you let the technical side of the organisation decide the best way to go, “you will end up with a solution that will never work and will have to go back to the drawing board”.  Sorry, but this is worth repeating.

Both the above approaches will involve carrying out a continuous review of the solution to get it right.  This seems to be the approach that most organisations take, thereby giving mediocre business analysts a job for life – the good business analysts resolve a problem and move on and don’t want to become embroiled in any process that would need a continuous review of the delivered product ad infinitum.

Don’t get me wrong - any business process needs to include an on-going process review but, when you’re set a task to be completed and it’s nowhere near the needs of the organisation, cut and run.  Otherwise, you are throwing money at a bad solution that should be dropped but hasn’t been, because the lead(s) couldn’t find their nose (replaced by me – see reference) with both hands.

There is a simple solution.  Get someone who knows a bit about the technical side from the business and get someone who knows a bit about the business from the technical side and let them start talking – the most important thing is communication between teams within your organisation.  All you do is get two people who want to do their best for the organisation and get them talking.  It’s really not that difficult – read as many papers as you want and it’s just a lot of turgid words around this.  I came from the other side of the desk – Finance- and have (mostly) had great relationships with people like that, but that’s another story for another day.  It’s always tough at the start because you are both coming from completely different directions to try and achieve the same solution.  It’s “us and them”, then “can’t you do better”, then “I’m sure we can do better” and, finally, “I think we got it”.  Then it works – and it is then that you realise why you love your job.

Look at the last paragraph – you don’t find the job specifications for that.  It is not inherent, it is not innate - it has to be learnt with experience.  I like the expression that “We have two ears and one mouth so that we can listen twice as much as we speak”.  It’s by Epictetus and I had to look it up but it is sage advice.  If we listen, we learn.  Even running a little team like one designing a report, we can do it by listening and talking with each other - communication again!

Reports are what the leaders of an organisation want.  They don’t have time to analyse and digest all the data so give them what they want.  Clear, concise reporting – I would also add correct (or exact) data, and that is where my expertise lies.

Big Data

Big Data

According to SAS (http://www.sas.com/) “Big data is a term that describes the large volume of data – both structured and unstructured – that inundates a business on a day-to-day basis¹. But it’s not the amount of data that’s important². It’s what organizations do with the data that matters³. Big data can be analyzed for insights that lead to better decisions and strategic business moves⁴.”

Let’s analyse what exactly is being said first:

1. Big Data is unstructured – end - no discussion here because people get this wrong more often than not -any other data is called large volumes of data.  Large volumes of data in a structured environment can be addressed by tools that have been around for a long time - there are many different ways to optimize and address said volume with regular databases.  Because of the increase in quantity, the downward variance in quality and cheaper storage options, more data is being retained.  This is the reason we have all sorts of unstructured data being held in storage for a lower cost and for much longer.
2. “But it’s not the amount of data that’s important” – who wrote this?  Whether it is structured or unstructured data this data needs to be analysed.  The more data there is the more thought has to go in to how that data is analysed, or sliced and diced, so that the business can make use of this information.  The more data in play the more storage is needed.  The more storage that is needed must give a return on investment so there needs to be a different approach to this newer, larger volume of data for that reason alone.  New methods have come into play – the front-runner being the statistical language R, the next in class being Python (two great programming languages that, in my view, shouldn’t be compared.  They both have their advantages and will be discussed at a later date here).  SQL just doesn’t cut it when it comes to unstructured data, although this may change.
3. “It’s what organizations do with the data that matters” – again – who wrote this?  Of course it’s what they do that matters!  Why would an organisation collect and collate this data unless it was of use to them in both current and future analyses.  There are a many number of analyses out there – check my shortened view here.
4. The final statement could be construed as true, but many organisations collect this data for the wrong reasons, as in “Do I want to ensure that employees are not going outside their job parameters and checking a recipe for dinner later?”  I think not, but this seems to be one of the prevalent ways in which this data is being used.  Heavy investment, poor return.  Big Brother does not a happy employee make.

Data Analysis Steps

Once you have enough data, you start to see patterns:

Data Analysis

The process of systematically applying statistical and logical techniques to describe, illustrate, condense and evaluate data. “What is happening?”

Descriptive Analytics

High-level data analysis - summarise data into smaller, more useful information – “What has happened?”

Predictive Analytics

Forecast techniques about what might happen in the future, “What could happen?”

Prescriptive Analytics

Prescribe an action so the business decision-maker can take this information and act, “What could happen?”

SQL – Joins

SQL joins are used to combine rows from more than one table.  I will be setting up a code page and will provide a link when this has been completed.

The different JOINs are as follows:

LEFT (or LEFT OUTER) JOIN: returns all rows from the first or left hand side table, even if there are no matches in the right table.  This is the main JOIN used when querying SQL.

INNER JOIN: returns rows when there is a match in both tables - produces a result set that is limited to the rows where there is a match in both tables for what you are looking for.

RIGHT (or RIGHT OUTER) JOIN: returns all rows from the right table, even if there are no matches in the left table.

FULL (or FULL OUTER) JOIN: returns rows when there is a match in one of the tables.  All rows from both tables are returned.

CROSS JOIN: returns a table with a potentially very large number of rows.  The row count of the result is equal to the number of rows in the first table times the number of rows in the second table.

SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

CARTESIAN JOIN: no-one uses this – see CROSS JOIN.  Returns the Cartesian product of the sets of records from the two or more joined tables.

This is the best visual I’ve seen from www.codeproject.com (http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins)

SQL - Indexes

Indexes are special lookup tables that the database search engine can use to speed up searches and queries. An index in a database is very similar to an index in the back of a book.  It is used to efficiently find all rows matching a column in your query and then walk through that subset of the table for exact matches.  If you don’t have indexes then every row in the table will be checked.

An index will speed up your SELECT queries but will slow down data input, i.e. when you use UPDATE or INSERT statements.

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order.

Whatever index you create, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions.

Single-Column

A single-column index is one that is created based on only one table column. 

Unique Index

The index can be unique, whereby you cannot have duplicate values in that column, or a Primary Key which in some storage engines defines where in the database file the value is stored.  Creating a unique index “provides additional information for the query optimizer that can produce more efficient execution plans”, as per SQL Server Index Design Guide.

Composite Index

A composite index is an index on two or more columns of a table.

Implicit Index

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

Hash Table Index

Hash tables are another data structure that can be used as indexes - known as hash indexes. Hash indexes are used because hash tables are extremely efficient when it comes to just looking up values. The only thing you must keep in mind is that hash table is only good for looking up key value pairs, i.e. equality rather than a range of values. 

 A table that does not have a clustered index is referred to as a HEAP and a table that has a clustered index is referred to as a clustered table (although a HEAP table can have a non-clustered index – but that’s for another time).

What (or who) is Kaggle?

Introduction

I met a good friend for lunch today who is well-informed on most anything (I really mean abso-bloomin’-lutely everything) in the IT arena.  I brought up the subject of Kaggle in conversation and he looked completely blank.  I tried to explain what it is to him but I don’t think I did a very good job so I thought I’d put that to rights here.

Kaggle, as per their website (https://www.kaggle.com/), is a “vibrant community [which] comprises experts from many quantitative fields and industries (science, statistics, econometrics, math, physics). They come from over 100 countries and 200 universities. In addition to prize money & data, they use Kaggle to learn, network, and collaborate with experts from related fields.”  In other words it brings the opportunity of working with real-life data to a vast audience which may not have access to the kind of projects that are out there and that they may be exceptionally good at.

Also on the website is the why? – “Many organizations don't have access to the advanced machine learning that provides the maximum predictive power from their data. Meanwhile, data scientists and statisticians crave real-world data to develop their techniques. Kaggle offers companies a cost-effective way to harness this 'cognitive surplus' of the world's best data scientists.”

I have to say, at this stage, that I was only recently introduced to Kaggle myself and think that it’s a great idea.  It’s a great way to learn data analytics and also receive feedback and the recognition deserved to anyone who takes part in the many competitions.  The main languages used are R and Python, although SQLite and Julia scripts are also shown in the scripts section of the site.

History

Anthony Goldbloom founded Kaggle in 2010.  Early in 2014, according to Inc.com, Kaggle had over 140,000 members (the article calls the members data scientists but the list includes many leading lights in the data analytics field, IT people who want to know more (like me) , students and a whole plethora of other interested parties).  The idea came to Goldbloom that he could create a way of solving problems through data science. Companies could post their problems on the website, and then any statistician who was interested could submit a solution that would be scored against any other entries.  He came by the name by writing an algorithm to see what one-word names he could get a URL for.

The site

Most, if not all, of the competitions cannot be solved by one area of expertise alone.  The member or team must have a unique blend of skills and several different factors available to them.  The aim of the competition is to find the solution that is the best fit, as there can be many ways to solve the proposed challenge.  On many websites and blogs the same words appears over and over again – perseverance and persistence.  Both are required as there is a lot of trial and error involved in getting to the finishing line.

Most competitors are not motivated by money but by challenge of the competition itself.  Many more members use their own algorithms and develop them on a continuous basis, so there is no such thing as resting on your laurels.  The competitions seem to be pictured as an ongoing development process rather than one-off challenges to the members.

That’s a very high-level overview and doesn’t get anywhere near the depth of knowledge about the site whatsoever, but is only a taster.  The competitions are free to enter and the site is well worth a visit.

Service-oriented Websites

This is only an introduction and is not meant to be complete in any way.  I am producing my own site at the moment and know that the views expressed here will change going forward.  There are many sites out there to help anyone start, but this is just a “toe in the water” article.The key to website design is to get the person looking for your service to look at your page and be knocked out by what they see.

Here are a few pointers for all websites:

Get your message across on the front page and don’t make the site too.  Don’t try and give your life story here so only put information on the page that serves a purpose.  Don’t have flashing gizmos or other memory-consuming frills as the visitor may have a poor connection to the net.  Don’t let the glitz overshadow your site’s content and annoy visitors.

Be organised and use the same models / themes for each of the pages on your site.  Use the same headings and fonts throughout.

Get to know who will visit your page – when a website stops changing it’s on life support.  Check your competition and make sure you can compete with them on a web-level.

And some for service-oriented websites:

If you have a company van with your logo on it put it on the page – it doesn’t have to be front and centre but it shows that you are not a fly-by-night but a reputable company that wants to be recognised.

List the major work that you carry out – four or five main-level items should do the trick.  Link to a separate page on your site to give more detail to the list.

Show some examples of your work – as many different types of work as you can offer.  Show some on the front page and have another linked page set up with said examples.

Do you have any qualifications or are you just trying to earn an income before returning to your main source of income.  Let visitors know that this is what you do and how long you have been doing it.  It’s like a mini-résumé or CV – this is how people will see what you can do.

Try to get as many testimonials from previous customers posted to your site.  If the customer is delighted with your work but not on the web ask if you can post their comments on your site.

The technical bits:

SEO (Search Engine Optimization) refers to the titles, descriptions & keywords you add to your website.  When a web user is searching for a service this is what they type into their search engine.  Make sure you list everything that you offer on your site.

Put in your main keywords.

COBOL: Everywhere and Nowhere

Here’s and excerpt from an excellent and disquieting article by Coding Horror about COBOL that may have your hair standing on end – the full article is at http://blog.codinghorror.com/cobol-everywhere-and-nowhere/:

COBOL is everywhere, yet is largely unheard of among the millions of people who interact with it on a daily basis.  Its reach is so pervasive that it is almost unthinkable that the average person could go a day without it.  Whether using an ATM, stopping at traffic lights or purchasing a product online, the vast majority of us will use COBOL in one form or another as part of our daily existence.

The statistics that surround COBOL attest to its huge influence upon the business world. There are over 220 billion lines of COBOL in existence, a figure which equates to around 80% of the world's actively used code. There are estimated to be over a million COBOL programmers in the world today. Most impressive perhaps, is that 200 times as many COBOL transactions take place each day than Google searches - a figure which puts the influence of Web 2.0 into stark perspective.  

Hit the link to continue reading…..

Web Design (HTML and CSS)

This past weekend I completed the Front-end Foundations course at Code School (https://www.codeschool.com/) so I must be an expert in HTML and CSS.  I’ll be a web designer – hooray!  No, not really, but this is a great course and well worth giving a go if you want to see if web design will float your boat.  Jon Friskics is the teacher and is excellent -   the link will go to his Twitter account.  I plan on completing all the course levels in due time but other work precludes me from doing the whole shebang.