SSIS - Dynamically get name of worksheet to import

SSIS - Dynamically get name of worksheet to import

No-one's posted on this either - maybe it's a postgreSQL thing!  Will be posting soon

osm and PowerOutage connection

I'm looking at posting open street map details on retrieving town/city/borough, county and state information that will work with PowerOutage in the US - anyone interested?

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.