Business-Intelligence

When designing databases, one question that comes up all the time is how large columns should be.

For numbers, the answer is always “big enough but not too big”. This week I’ve been working at a site where the client numbers were stored in int columns. Given the clients are Australians and the Australian Bureau of Statistics Population Clock says there are just under 25 million of us, an integer seems a pretty safe bet, given it can store positive numbers up over two billion. It’s hard to imagine that number being exceeded, but I’ve seen people deciding that it needs to be a bigint. I doubt that. Even if we count all our furry friends, we aren’t going to get to that requirement.

2017-11-27

I had the great pleasure to record another SQL Down Under podcast last week with the Power BI general manager Kamal Hathi.

In the show, Kamal and I discuss the current state and potential futures for Power BI, its relationship to SQL Server Reporting Services, and its development and extensibility models.

You’ll find the show here: https://podcast.sqldownunder.com

I hope you enjoy it.

Note: We had a few unexpected audio issues with the recording. Sorry about that. We’ll do better next time :-) It’s still pretty good and I’ll still think you’ll find it interesting.

2017-11-24

I work with data. I understand that for most organizations, that the data they own is the most valuable asset the company owns.

One thing I’ve learned from working with data is that unlike application software, data generally outlives generations of applications, is often used by many different applications, and typically just morphs from shape to shape over time. It almost never gets totally refreshed.

This is a good thing.

2017-11-21

Many people know that I have an issue with ORMs (object relational mappers). It’s worth spending a blog post to describe why.

Unfortunately, I spend my life on the back end of trying to deal with the messes involved. The following are the key issues that I see:

Potentially horrid performance

I’ve been on the back end of this all the time. There are several reasons. One is that the frameworks generate horrid code to start with, the second is that they are typically quite resistant to improvement, the third is that they tend to encourage processing with far too much data movement.

2017-11-07

I’m not a great fan of linked servers in SQL Server but they are often necessary. I really wish the on-premises product supported External Data Sources and External Tables. But in the meantime, what I see all the time, is people hardcoding server names like this:

SDUPROD2016.WWIDB.Payroll.Employees

That makes your code really hard to manage. One option to get around that is to use synonyms.

Instead of sprinkling references to that table all through the code, you can create a synonym for it like this:

2017-11-06

I spend most of my life in a variety of customer sites. Most of these nowadays tend to be large financial organizations. When I talk to developers in those organizations, and ask them about how they interact with their DBAs, I can’t tell you how often I hear that they try to avoid ever making schema changes, etc. as it takes too long and the process is painful.

When I talk to the DBAs at the same organizations, and I ask them why they resist making schema changes, I hear this:

2017-10-27