The Bit Bucket

I’ve got a number of clients in the superannuation (aka retirement fund) industry. At many of these sites, there is a need to be able to do this:

• Using a web site, members must be able to see their own data pretty much 24x7
• Some latency is ok i.e. it might be ok for a member to see data up to yesterday, or up a point in time a few hours ago
• Refreshing the website data is not incremental, truncating the tables and reloading them is required

The challenge is: what happens if a member looks at the data during the time it’s being refreshed?

2019-08-08

In my last SDU Tools post, I described a tool for calculating date dimension columns. While dates are often enough, in many data warehouses, you also need to allocate time periods across each day as well. To make that very easy, in our free SDU Tools for developers and DBAs, we added a tool called TimePeriodDimensionColumns.

This tool is a table-valued function that takes a time (actual time of the day), and the length of each time period, in minutes. In the example in the main image above, I’ve asked for 8:34PM and I’ve said that each period is 15 minutes long.

2019-08-07

Over the years, I’ve been noticing which apps users love and which ones they don’t like, or even hate. And I’ve decided that one of the biggest indicators of this is about how well they tolerate mistakes by users.

Users tend to learn to use apps in three ways:

• They watch or read some instructional material (this is actually the least common)
• Someone else shows them how to use the app (this is really common)
• They just experiment with the app (this is actually the most common)

How well do your apps allow people to experiment?

2019-08-06

Important concepts in any development are reusing code, and abstraction. Look at the first WHERE clause here:

I’ve asked SQL Server to return rows where the CreditRatingID is the one that has the maximum rating. I might not know how to find the maximum rating but if there is code that finds it for me (i.e. dbo.GetMaximumRating()), I don’t need to know that.

This is an example of a function that retrieves a value, and then I can use the value in my own query, without having to copy all the code that’s needed, and without even having to know how it works.

2019-08-05

I’ve got a number of friends who’ve been writing books over the last few years. Sadly, there seems to be no relationship between when I buy a book and when I actually read it.

And that’s the case with Blood Rush by Bob Simms.

Bob is an old SQL Server trainer friend/colleague and I bought this book back when Bob first mentioned that he’d written it. I only got to actually read it last week.

2019-08-02

In a recent post, I talked about how varchar(10) doesn’t mean up to 10 characters, particularly since the introduction of UTF-8 in SQL Server 2019.

So given I do want to limit the number of characters in strings at times, how exactly should I now do that?

A plan takes shape ?

One of my current plans is to do this:

Step 1: Start by forgetting that the number in brackets for the string data types is really relevant to a developer in any way.

2019-08-01

I don’t know if it’s an Australian “thing” but whenever I’m setting up new financial or government accounts, these organizations insist on actually sending things to our street address.

Given how rampant identity theft has become in many places, this is just not sensible.

[caption id=“attachment_5424” align=“alignnone” width=“538”] Awesome image by Mathyas Kurmann[/caption]

Take a look at letterboxes that people have on local streets, and please try to convince me how sending anything there, is in any way safer than sending it to a post office box.

2019-07-31

When you’re building a data warehouse using SQL Server, one of the first things that most people start to design is a Date dimension. To make that really, really easy, in our free SDU Tools for developers and DBAs, we added a tool called DateDimensionColumns.

This tool is a table-valued function that takes a date, and returns a set of columns that many will find enough for a good Date dimension. You can see the list of returned columns in the main image above.

2019-07-31

Look at the following query:

I’ve asked SQL Server to return orders where there are order comments. I did that by saying:

OrderComments IS NOT NULL

If I had said IS NULL, I would have returned all the orders with no comments. The interesting concept though, is NULL.

What is NULL?

The first thing to understand is that NULL isn’t a value. That’s why we can’t say:

WHERE OrderComments = NULL or WHERE OrderComments <> NULL

2019-07-29

One of the depressing things about watching TV is that producers (and in particular Hollywood-based producers) seem to have a very skewed concept of what happiness comes from.

Here’s a bit hint: it’s not from wealth or fame.

I’ve made enough trips around the Sun to know that anyone who believes what that TV is telling them, is being conned, big time.

Now I’m not saying that being poor is fun either. It’s not. In particular, anyone who’s deeply in debt would understand that they have very little control of their own lives.

2019-07-26