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:

You all know that report parameters can be populated through a query, right?  And multi-value parameters get this magical (Select All) item as first one in the dropdown list.  I used the word magical in the previous sentence because this (Select All) item is not a real one.  It’s a bit like a fata morgana: you can click it but it doesn’t really get selected.  It only appears that way in the dropdown.  But you can’t write an expression that tests if the (Select All) item is selected because it doesn’t really exist in the array.  The only thing it does is it selects all items when it gets clicked.

Little side note: as usual there’s a workaround to check if all items are selected.  I’ll mention the details on that later.

Sometimes people want to avoid using this functionality and implement their own "All" item.  Even more so in the case of a single-value parameter which doesn’t get an auto-generated (Select All) item.  And that’s what this article is all about!

Setting The Scene

Once again I’ll use the AdventureWorksDW2012 free sample database and write some product-related queries.

The Parameter Query

Let’s use the following query in a dataset called ProductCategory to populate a multi-value parameter called ProductCategory, type integer:

select ProductCategoryKey ID
    , EnglishProductCategoryName ProductCategory
from dbo.DimProductCategory

ID is used as Value field while ProductCategory serves fine as Label.  To have all values selected by default we can use that same dataset:

Selecting all parameter values by default

When rendering the report we now indeed see a dropdown list with a (Select All) item:

image

To come back to the workaround mentioned in the intro: you can compare the count of the selected items array with the count of the whole dataset.  When equal then all items are selected.  Here’s what that looks like in an expression:

=IIF(Parameters!ProductCategory.Count = COUNT(1, "ProductCategory")
    , "all selected", "not all selected")

Fetching Some Data

Like in any regular report we’d like to display some data.  Let’s keep it simple and show a list of product subcategories based on the selected categories:

select cat.EnglishProductCategoryName ProductCategory
    , sub.EnglishProductSubcategoryName ProductSubcategory
from dbo.DimProductSubcategory sub
inner join dbo.DimProductCategory cat
    on cat.ProductCategoryKey = sub.ProductCategoryKey
where cat.ProductCategoryKey in (@ProductCategory)

Adding an "<All>" item to the parameter

The first step in setting up a custom "<All>" item is adding a simple UNION ALL to the query that fetches the parameter list.  That goes like this:

select ProductCategoryKey ID
    , EnglishProductCategoryName ProductCategory
from dbo.DimProductCategory
UNION ALL
select -1, '<All>'
order by ProductCategory asc

I’m using -1 as ID for the "All" record because I know -1 will never exist in the database.  And I’ve included < > characters in the "All" label for two reasons: to nicely distinguish it from the other items as being a bit special and it ends up on top of the list when sorted alphabetically!  Neat huh?!

This also changes how you set the default parameter selection to "All": just set it to –1:

Setting the parameter default selection to the custom "All" item

Taking the "<All>" selection into account

Alright, the next step is taking the "All" parameter item into account in the dataset that retrieves the subcategories.  Here’s what the WHERE clause will need to get changed to:

where cat.ProductCategoryKey in (@ProductCategory)
    or -1 in (@ProductCategory)

In case of a single-value parameter there’s no need to use the IN operator.  For those situations the WHERE clause can be simplified to this:

where cat.ProductCategoryKey = @ProductCategory
    or @ProductCategory = -1

Detecting if "All" is selected in an expression

The expression to check if "All" is selected also changes, we no longer need to use the COUNT function.  Instead, we can use the Array.IndexOf VB function:

=IIF(Array.IndexOf(Parameters!ProductCategory.Value, -1) > -1
    , "all selected", "not all selected")

Checking if the "All" item is selected in a multi-valued parameter

How does this work?  Because ProductCategory is a multi-value parameter, its .Value property returns an array of selected items.  The second parameter of IndexOf is the value we’d like to search for.  As our report parameter type is integer, this needs to be an integer as well.

When the item is not found in the array IndexOf returns -1, otherwise it returns the zero-based index position of the item.

Of course, in case of a single-value parameter this check can be simplified to the following:

=IIF(Parameters!ProductCategory.Value = -1
    , "all selected", "not all selected")

Conclusion

This article demonstrates how to implement a custom "All" item for both single and multi-valued report parameters meant for filtering data.  It also shows how these values can be used in report expressions.

Have fun!

V.

Share

Tags: , , ,

SQL Server 2012 Reporting Services BlueprintsAs if preparing for SQL Server Days wasn’t sufficient to keep me fully occupied, I was once again involved in the process of book publication…

And no, I didn’t write it – though I had been asked for it many months ago – no, that not-to-be-underestimated task was for Marlon Ribunal (b|t) and Mickey Stuwey (b|t).

My role was Technical Reviewer so I read and commented on all chapters regarding their technical content.  But that’s not all, I also pointed out typos, my drive to perfection can’t just ignore those :)

The book which I reviewed is called SQL Server 2012 Reporting Services Blueprints and is published by Packt Publishing.

So if you’re (relatively) new to SQL Server Reporting Services this should be a good read for you.

The book is mainly aimed at report developers though I’m sure it wouldn’t hurt if administrators had a read through it too.  In fact, a couple of the chapters contain material that should be known by every SQL admin who needs to support BI deployments.

The book starts with the basics and gradually builds up to designing more complex reports.  This gives everyone a good chance to understand the basics before delving into more complex material.  By the end of chapter 6 you’ll have used all components needed for SSRS report development, which is a good thing!

So go out, get a copy and start reading about John Kirkland’s tale!

Happy reading!

Valentino.

Share

Tags:

image

Regular readers of this blog know that I was one of the speakers at our Belgian SQL Server Days event last Thursday and Friday.

I have to admit that I underestimated the amount of work a little: working full time for a customer, preparing a one-hour session, preparing the SQL Server Days (luckily we’ve got a great team at the SQLUG for this part!) and still having some kind of personal life as well.

On day two we had the pleasure of welcoming 279 attendees all ready to attend sessions in our five different tracks.  And about 70 of those people were in my room!!  I had practically no stress before or during the start of my session, except for the moment when I had to get the microphone wire attached to my head somehow (luckily the technician was still around!)…  In fact, I was more stressed for my dry run session three days earlier.

Would I do it again?  Ow yeah, absolutely!  Even though it requires quite some time in preparation, the applause at the end of the session makes up for that! Smile

And now the moment you’ve all been waiting for: my slides and demos are available for download from my Skydrive.

References

For those seeking more information, here are some references related to my session content:

The official Data Quality Services blog (a lot of useful content on DQS!!)

SSIS DQS Matching Transformation

SSIS DQS Domain Value Import

Creating a Synchronous Transformation with the Script Component

Fuzzy Lookup Transformation

DQS Cleansing Transformation

Finding Similar Strings With Fuzzy Logic Functions Built Into MDS

Have fun, and see you next time!

Valentino.

Share

Tags: , ,

SQL Server Days 2013For the first time ever am I not only participating in the organization of the SQL Server Days, I’ll be speaking as well!  Exciting times!

In my session, Cleaning up the mess with SSIS and DQS, you’ll learn a couple of tricks on dealing with dirty data.

Here’s the abstract for my session:

Are you loading data from exotic sources such as Excel and flat file?

Or are you dealing with manually-entered data through an application that allows, well, practically anything?  And do you sometimes run into trouble because the incoming data is not as expected?

Then you should really join us in this session in which I’ll demonstrate (yes, demos!) several different techniques that can be used to cleanse your dirty data!

Check out the agenda for more details!

We’ve got speakers from all over the world: South-Africa, Italy, Slovenia, UK, US and of course… Belgium!

Not registered yet?  Do it now!

Ow, and did you know we’ve introduced a special feature?  It’s called Bring a colleague for free.  If you register you can register an additional person for free, as long as there aren’t 50 of those free ones registered that is so be fast!

Bring your colleague

In the meantime: have fun and see you there!

Valentino.

Share

Tags: , , , , ,

« Older entries

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