Business-Intelligence

I really love SQL Server Integration Services (SSIS). In fact, I wish Microsoft was giving it far more love than it is. So many of my clients use it, but unfortunately, not everyone uses it well, or for the right things. One really common mistake is to perform lots of row-by-row logic within the SSIS packages. Let’s take an example of loading a fact table in a data warehouse as an example:

2019-07-02

I spend a lot of time doing consulting that involves Power BI. One of the core skills for working with Power BI is getting yourself comfortable with the M language that’s used in Power Query. I was asked the other day, how to create a function that determines if a date is a weekday or a weekend. That’s actually quite easy. The Date.DayOfWeek function can be adapted to do that.

2019-06-28

One of my Kiwi buddies who specializes in Power BI is Reza Rad. I was pleased to see he had a set of eBooks now on Power BI but was especially happy to see he had a book called Pro Power BI Architecture. There are lots of books around to discuss how to use Power BI but there’s been a real lack of books on architecting solutions using Power BI. So if you want to learn to develop dashboards or reports, this isn’t the book for you.

2019-05-10

I love working with SQL Server spatial. Don’t just use boring tables and charts. Spatial data can really help to bring your reports and analytics to life. Don’t assume spatial is just about maps. (It’s not). Don’t assume you don’t have spatial data. (I’m sure you have addresses, locations, etc.) We’ve been working hard lately on getting many of our popular in-person courses, converted to being available online and on-demand. We’re really proud to now have SQL Server Spatial for Developers and DBAs available.

2019-01-25

I spend a lot of time on client sites and time and again, one of the mistakes that I see people making, is trying to start with large projects. I think one of my all time favorite quotes about IT is: Any successful large IT system used to be a successful small IT system. The next time you’re thinking about creating a project that’s going to have a big bang outcome, please remember this.

2019-01-18

Recently, I posted about the templates folder for SQL Server Reporting Services designer moving in Visual Studio 2017. You’ll find that blog post here. I’ve also now found that the Private Assemblies folder has moved too. I do wish these locations would be fixed, shared, and known rather than needing to play hide and seek when updates are made to the SSRS designer tooling. The Private Assemblies folder was used to hold a copy of any .

2018-06-18

I’m always telling students that one of the first things you need to learn to do when working with SQL Server Reporting Services (SSRS) is to create report templates. Now I really wish that SSRS had a real templating system, but what we have at present is just a folder that contains the items that we can create. For example, in an SSRS project, if I right-click Reports, and click Add, then New Item:

2018-06-12

In a recent blog post, I wrote about the ANSI_NULLS option for tables and after that I had several more questions related to NULL values. Note that I mentioned NULL values, not values of NULL, and not values equal to NULL. That’s because “being NULL” is a state that a value is in, not itself a value. That’s why our queries say IS NULL and not = NULL. Now because of that, many developers see values that are NULL as a pain in the neck as they have to be dealt with separately, unlike other values.

2018-04-10

Whenever I need to work on a new laptop or server, or whenever I change versions of SQL Server Management Studio, I kick myself for not remembering to export my settings, so I can import them again. I spend quite a bit of effort getting SSMS configured the way I want, so it only makes sense to save the settings. Saving them isn’t perfect but it’s far better than not having done it.

2018-03-01

If you’ve ever used SQL Server Reporting Services, you’ll notice that when you drag a database column into a table, it auto-magically converts the name of the column into a more English-readable name. [caption id=“attachment_2791” align=“alignnone” width=“429”] Image from MSDN[/caption] Notice how when the LineTotal column has been dragged into the table, the heading has been set to Line Total with a space. What it is doing is taking a Pascal-cased or camel-cased name and separating the words with spaces, based upon where the capital letters are.

2018-02-28