Blogging

You are currently browsing the archive for the Blogging category.

TSQL-Tuesday#050: The automation storyAutomation, whoah, what a subject for our monthly T-SQL Tuesday party!  This month’s event is hosted by SqlChow.  Head over to his post for more details on this month’s topic and to find out what T-SQL Tuesday party actually is, if needed:

T-SQL Tuesday #050: Automation, how much of it is the same?

Automation, what would the world be without it?  I have no idea but it would definitely not be the same!  And what would my job be without it?  Again I don’t know but it would definitely not be the same!

As a Business Intelligence consultant automation is a big part of the task list!  How else would we be able to build a data warehouse (DWH), if it wasn’t through automation?  Our ETL processes are all about automation: retrieving data (Extract), converting and remodeling it into something useable (Transform) and pushing it into the DWH (Load).  Without automation we would never be able to go home at 5PM and still couldn’t get it all done!

And it doesn’t end there, even for report delivery we use automation now and then: reports can get exported to a shared folder or emailed to a bunch of people through subscriptions.  You just set it up once and user-friendly output gets generated as scheduled!

Before I switched to the BI world – regular readers know that I used to be a software developer – automation was a big part of my job too: automating the import of flat files into an accounting application, ensuring money transactions got approved (or rejected) automatically, …  Automation is a big part of a software developer’s life too!

So as you can see it’s not only DBAs who would/could benefit from automation, practically everyone active in our IT domain can use it here and there!

Let’s see if I can share something more about automation, something which you might find useful in your daily job.  Ow yeah, do you see that Must-Have Downloads title in the right bar on this site, right underneath my tag cloud?  It mentions two of my favorite tools: SSMS Tools Pack and BIDS Helper!

SSMS Tools Pack

This great SSMS add-on, developed by Mladen Prajdic, should contain something useful for everyone using SQL Server.  My most favorite functionality is the Script Grid Results item when right-clicking the results of a query:

Script Grid Results

Using that functionality you can generate a script that inserts data into a table, real handy if you quickly want to move some data over into another table or obviously if you just want to script out your data!  Here’s your typing automated for you!

BIDS Helper

This is a must-have add-on for Visual Studio (BIDS) that adds a lot of additional functionality for SSIS and SSAS (both multidimensional and tabular) and even a bit for SSRS projects.

My favorite of them all: Smart Diff!  Using the regular Compare function on an SSIS package or SSRS report doesn’t produce the most readable result.  Since these are XML files they are a bit hard to read to humans.  And they contain item positioning info which obfuscates it all even more.  Smart Diff is smart enough to only highlight the parts in which you as developer are actually interested.  Have a look at the following documentation page and the screenshots will immediately clarify what I mean: Smart Diff

Other really useful additions are:

Sort Project Files: how annoying if your SSIS packages are not displayed alphabetically, right?  With this BIDS Helper functionality you can just right-click the SSIS Packages node and choose Sort by name!

Expression and Configuration Highlighter (SSIS): adds colored triangles on connection manager icons to indicate that they are configured through package configuration.  Same for tasks and connection manager which have an expression on them in a property somewhere.  Very useful while debugging packages you’re not familiar with!

And there’s much more, have a look at the main documentation page for the full list.

I hope the above info helps you in getting a couple of your daily tasks improved, or perhaps even some of your daily annoyances removed.  In my case it surely did!

Have fun!

V.

Share

Tags:

T-SQL Tuesday #46 Rube Goldberg MachineHere’s my participation in this month’s T-SQL Tuesday.  T-what you say?  T-SQL Tuesday, the monthly blog party started by Adam Machanic!  Head over here to this month’s invitation by Rick Krueger: T-SQL Tuesday #46 Rube Goldberg Machine for the longer story.

So, a story about SQL Server-related contraptions, let’s see…  Yeah, I can do that!

Some years ago I was asked to help out in finding a solution to a certain problem, the life of a consultant, right?  The problem was performance related.  No, this is not a story about a database without any indexes.  Instead, it involves a certain .NET web application.  The end user would fill out a page with some metrics and the application would then perform certain calculations.  And that’s where we get to the issue: the calculations would take way too many minutes (yes, not seconds, minutes!) to finish.  That was the challenge.

The first step was to examine the .NET code that was performing those calculations.  But there were so many lines of code, complicated code, that the final conclusion was: hmm, rewriting this contraption is not an option right now…

After some discussion with the project lead it became clear that the end user would not necessarily need to wait for the calculation to finish.  It didn’t need to get calculated online.  Aha!  So if we would be able to schedule these calculations and set up a batch process then that means problem solved, right?  Now, how would we schedule this?  How about our favorite scheduler, the SQL Server Agent?  Yes, that’s it!

So I ended up building an assembly that was to be called from SQL Server: a CLR-enabled assembly!  The web application used web services so the new assembly would also call a couple of (reworked) "calculate" methods.  This meant we had to install the assembly with increased permissions: EXTERNAL_ACCESS to be precise.

Furthermore I didn’t want to hardcode the path to the web service.  There were different environments, you know, DEV, UAT, PRD, so it had to be configurable.  Now, as the assembly is getting called from SQL Server, I needed to provide the configuration through sqlservr.exe.config which gets loaded when SQL Server starts.

As you can probably guess, this little project required a lot of investigation, trial and error, and so on.  But I learned a lot from it and that’s what I like!  And it also made me write the following article: Calling A Web Service From SQL Server 2005.

This article turned out to be quite popular, even today, almost five years later, it’s still in my top 10 most viewed pages of last month!

Most popular pages over one month

Was this a clean solution?  Well no, otherwise I wouldn’t have selected it for this month’s T-SQL Tuesday party, right?

Did it solve the issue?  It sure did!

‘till next time and in the meantime: have fun!

Valentino.

Share

Tags:

Look at that, it’s T-SQL Tuesday #42, how can I miss that?!

This month’s party is hosted by Wendy Pastrick and the topic is about change in the work life, possibly related to technology.  Well yeah, in my career of approximately fifteen years now I can write a couple of words on that subject.  Here’s the story of my career.

Introduction

For those who don’t know me yet I have something to confess: I used to be a software developer!  And I’m not ashamed to admit this.  Perhaps not that surprising, fifteen years ago you didn’t finish your studies to conclude: "And now I’m going to be a Business Intelligence consultant!".  Oh no, not at all.

During my studies, out of all possibilities, I already knew what I liked most: to develop software.  And I also quickly learned that my favorite language would not be COBOL, too outdated.  It wouldn’t be Visual Basic either, feels like you’re writing a book.  I prefer the shorter C/C++ syntax style.  I also knew that I didn’t want a consultancy position.  I considered a consultant to be an expert, and how could I be an expert after just having finished my studies??

Change 1.1

The first three years of my career I was an in-house C++ programmer.  Then I got involved with a software package that was written in VB6.  They were lacking manpower to implement new functionality and I was asked to help out.  So I did some programming in VB6 because there was no other option.

Then the first version of .NET arrived and guess what I did?  Part of the package’s functionality was the import of data out of flat files.  My job was to automate this process.  SSIS would have been perfect for the job but that didn’t exist yet.  I did use DTS for a couple of things, but not here.  The existing code consisted of about 15,000 lines of VB6 code which means redesigning was not an option anyway.  So I wrote a Windows service in C# that called the VB6 code!  Process automated!

Change 1.2

I’d also been using C# to write a couple of tools to help me in my day-to-day job.  Then the opportunity arose to be part of a new project which would be written completely in C#.  I was happy to be part of this!  Compared with VB6 and VC++6, the new Visual Studio for .NET was really a joy to work with!

Change 1.3

After a good year or so, upper management decided to go on the free tour: instead of .NET we were required to use Java for any new development projects.  Sure, why not give that a try then?!  One of the subprojects on which I worked was an activity monitoring tool for our server application.  I ended up developing some stored procedures to extract statistics out of logging tables.  At that time I wasn’t familiar with the ETL acronym yet, but that’s what I was doing.  Nowadays I would use SSIS for such a task.  There was also a reporting part to the project so I coded a website, in Java, to display those numbers, with drill through functionality and all that fancy stuff.  Nowadays I would definitely use SSRS!

Several months and lots of code later I realized that I didn’t enjoy Java (and all involved tools, libraries, …) as much as I enjoyed C#.  Sure, I could get stuff done.  But it wasn’t always as straightforward as I would have hoped, and online info was not as good – in my opinion.

After ten years of being an in-house developer at three different companies, I decided the time had come for a bigger change!  What I haven’t mentioned so far is that practically all projects in which I was involved used SQL Server as database engine.  And I always enjoyed playing around with that.

Change 2.0

In my new job I would no longer be an in-house employee and my main activity wouldn’t be writing code: I became a SQL Server Business Intelligence Consultant!

My employer, Ordina, gave me some time to cope with the change.  I was allowed to spend some weeks studying books and even going for a week of training (SSAS).

I was also encouraged to start blogging.  Initially it was a challenge to find topics to blog about.  But only initially, nowadays I’ve got too many topics and not enough time.  I discovered it was actually interesting to write about things I’d encountered for real.

Here’s such an example.  In one of my shorter project interventions, I ended up calling a web service through SQL CLR.  This was not an easy task.  Without my developer background I would probably never have succeeded here!  When I turned this into an article on my blog it became my first real hit!  The article, Calling a Web Service From SQL Server 2005, was posted Nov 11, 2008 and has gotten over 26,000 page views to date.  Even today it is still one of the more popular pages I’m hosting.

Besides teaching others through my blog I also discovered that forums can be a really interesting way to improve skills.  So in August 2008 I created a free account at Experts Exchange.  I also found out that helping out on forums combined with blogging is a real win-win situation: it helps me to find topics to write about and I can help people out by referring to an already-written article!

In February 2012 I was delighted to read that EE had found my forum activity worth an extra credit so I was given the MVE – Most Valuable Expert – award! Since then I have managed to reach the number one position in the SSRS zone with over one million points in total and it looks like I’ll be staying there for some time.

Lately I have started presenting.  So far I have found that to be a stressing yet rewarding experience.  My next presentation will be at the Community Day where both I and my colleague Koen Verbeeck will each talk for about half an hour about SSRS visualizations.  The target audience are mainly developers, that’s going to be interesting!  And it will be my first time on a stage in a cinema room, fearing the spotlights already!

Ow, and registration is open so see you there?  Come over to say hello if you are planning to attend the conference.  We are scheduled in the first slot so once that’s over I’ll be relieved of my stress!

Conclusion

Did a change in technology influence my career path?  Ow yeah, I do believe it did!  SQL Server was not the only tech that influenced it but I do consider it the most significant one.  And I’m glad it did too, keeps things challenging! 

Now I’m off to install Oracle.  So long, and thanks for all the fish!

PS: one of my statements in this post is a lie.

Valentino.

Share

Tags: ,

Ever since I started using @ValentinoV on Twitter a bit more actively, which is now more than a year ago, I have also been exploring different methods of announcing my new blog posts to the world.  One of the methods I use is sending out a couple of tweets to my followers.  So far I had been adding the prefix [Blog] as a standardized way of working and I also add relevant hashtags, such as #SSRS when the post is about Reporting Services.

Here’s an example:

Tweet using the [Blog] prefix

Then Jamie Thomson, aka SSIS Junkie, made me aware of a blog post he had posted earlier this week.

The power of Twitter: being able to quickly contact someone without the need of email

And in that post, he introduced an interesting alternative to what I (and a lot of other SQL Server bloggers) was doing!  Instead of using that [Blog] prefix it’s better to use a hashtag!  Why is that better?  Well, because searches and filters on hashtags work better than searches on just plain text. In fact, he proposes several hashtags.  Have a look at his post for the details: Bootstrapping SQL Server bloggers and blog readers with Twitter!

And here’s my earlier tweet reworked as per Jamie’s proposed standards:

Tweet using Jamie Thomson standards

However, the #blogged hashtag is not the one I will be using.  Several other bloggers commented on his post that they’re more in favor of the concatenation of #blogged and #sqlserver into #sqlblog.  I like that proposal because it’s closer to my earlier prefix and it uses less characters.  Given the nature of Twitter, if we can deliver the same message using less characters then that would be the preferred method, right? So that’s the one I will be using from now on!

I’ll even demonstrate it by updating this post once I’ve tweeted about it.  Yeah, a recursive blog post!

Tweet to demonstrate usage of the adapted standard

Have fun, and spread the word!

Valentino.

Share

Tags: ,

Dear Reader,

If you have read my previous post, you know that one of my articles was in the running for the first ever SQLServerPedia Awards.  And guess what?  I’ve won in the Business Intelligence category!

At first I wasn’t convinced that I’d be able to get enough votes.  Jen Stirrup had been scoring quite a bit as well.  But after a couple of days I noticed that my votes kept coming in too.  So I decided to contact some more people, such as ex-colleagues, the experts at Experts Exchange, and even my neighbours!

The result: 181 votes, which is 53 more than the next contestant in the category.  If you’re one of those 181: thank you!!  In the overall score list, my article ended up at the third spot!  Not too bad for a first-time nominee I suppose :-)

I can now add an extra logo to my blog:

SQLServerPediaAwards_badge-winner-bi

And my statue is on its way too.  Plus every nominee will receive a T-shirt.  Hopefully it’s one for regular use and not another one in the “suitable for household chores and bike cleaning only”-category…

Ow, congrats to all other winners and nominees!  To be honest, the nomination means more than the actual voting.  To get nominated, the editors (Iain Kick, Kevin Kline and Jeremiah Peschka) need to select your article out of all those present at SQLServerPedia.  Which means that it needs to be of a certain quality.  While the voting only shows how many people you know…  But the winner badge sure looks nice of course :-)

Have fun!

Valentino.

Share

Tags: , , ,

« Older entries

© 2008-2017 BI: Beer Intelligence? All Rights Reserved