Sometimes you receive data from other sources that are suppose to be dates but are not entered correctly. The roman classic I was born in ’12/15/0010′, or the time traveler ’03/23/99999′. Sometimes its just junk like ‘tbd’ etc. This is where try_cast or try_convert is essential.
Try_Cast or Try_Convert has been available since SQL 2008. These will prevent an error to stop your query.
select TRY_CAST('1/1/2016' as date)
These will both successfully return a date of 1/1/2016. But if you try to process the following:
This will return a null instead of an error. You can wrap the try with a coalesce and return a default value
This will return 1/1/1900 and your query completes.
Often I find myself wanting to choose dynamically between different steps in a package or being able to turn off a step without having to actually modify the procedure.
This can be accomplished dynamically by disabling a step via a variable or parameter. In this example I have a loop that processes a set of college clubs. The query loads a list of clubs. I would like to have a different process for The Physics and Astronomy club. Maybe eventually each club will have a different process of its own. One way to make this happen is by controlling what is enabled. Below is the package.
The SQL Load List of Files loads a table of the clubs to a source object that is used in the for each loop. The Name of the club is assigned to a variable called ClubName.
I then modify the step called Physics and Astronomy. Open the properties of the Physics and Astronomy step and add an expression for the Disable feature.
I test to see if the ClubName is not equal to Physics and Astronomy Club. If it isn’t then Disable is set to true and this step will not run. If the ClubName is set to Physics and Astronomy Club then the step will continue. Since I only have one other step for all other clubs. I set the Disable for this step when ClubName is equal to Physics and Astronomy Club.
Now as I loop through the clubs, the Physics and Astronomy Club step will only run for that club and All Other Clubs step will run for everything else.
The YYYYMMDD format is very useful in organizing data for filenames, sorting, etc.
Here is one way to generate that format.
SELECT CONVERT(VARCHAR(10), getdate, 112)
This is the method that works well in sql. If you want to do the equivalent in an expression in SSIS then you can use the Year,Month,Day functions.
Year(getdate()) *10000 + MONTH(getdate())*100+ DAY(getdate())
This will result in a integer value in the form of YYYYMMDD. You can then convert to a varchar.
Power BI Slicer is a great tool to customize reports, but it can be difficult to use with long lists. One way to fix this is to use two slicers as a hierarchy to speed selection.
Here is an example. The product list in AdventureWorks is long. Here is a report that show the Inventory and the pricing for a product selected from the slicer.
The lit is long and its difficult to scroll up and down to find the product one is looking for. One way to speed the search is to add a hierarchy. I chose breaking things up by the first letter in the product name.
To do this, I edited the product table by copying the Name table. Then i split the copy of Product Name into to two columns, with the first letter as the first column. Then I renamed the column Name A-Z.
Now add a second slicer using the Name A-Z. Now you can select the first letter of the product you want, then select the product from the filter product list! Much easier than scrolling away!
Sometimes you want to query to see if an item exists but if it doesn’t you usually get an empty result in SQL. It would be more useful if it would return a 0.
I often want to ask the question: Does x exist? and get back a yes or no. Yes or No often translates to boolean as a 1 or 0. Let say I query to see if a Date is a Holiday. I have a table of Holidays.
I want to see if today is a holiday so i enter the following:
Select case when count(HolidayKey) > 0 then 1 else 0 end as IsHoliday
where date = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
The case statement returns the results I am looking for. If the date is Holiday then the count of Holidaykey is greater than or equal to 1, which returns a 1. If the date doesn’t exist in the table the count function returns a zero.
Without the count, the case statement fails to return the proper result. See below.
The census contains a wealth of demographic data down to a very small portion of a county called a census block. It also contains data at the zip code level as well. Power Map can map zip codes. Unfortunately zip codes cross county boundaries, so data can’t easily be rolled up to the county level. It would be nice if Power Map could recognize census block data geographically.
Power Map does support Latitude and Longitude. If we could translate the block level data to a latitude and longitude than we could map the data. After much searching of the Census website I was able to find a source for the latitude and longitudes of the blocks based on centers of population.
If you go to the Geography Reference page on the census.gov web site you will find listed Centers of Population.
Click on Data Files and select Centers of Population by Block group. You have to select a state. I picked Florida.
This will open up a text file that contains comma delimited data in the format shown below.
We can now use this data to map the census blocks. Save the data to a file on your hard drive. Use the From File option in Power Query and
select either CSV or text file and load the data. It will load without headers.
Rename the columns.
Add a new Column for GeoCode. It is basically a combination of the state, county, tract and Block codes.
This identifier will match the identifier used in the census data files. Convert all columns to numbers.
Close and Load this data. Now go to insert a map.
Launch Power Map. The Data will load and recognize the latitude and longitude.
Select next at the bottom right and then check population. Tilt down to see the population.
The data is now available to tie to other census data. Below is an example where I tied in the level of education for the Duval County, Clay County, and St John County. The map is showing the number of people with college educations, bachelor degree or higher.