Power BI: 5 Minutes to "Wow" and for enterprises, what's next?

Power BI is amazing. And it's starting to appear all over the place. Many enterprises don't know what to make of it though. Some are scared that it'll be the "next Access" where stores of uncontrolled data end up all over the organization. Power BI's mantra of "5 minutes to Wow" is spot on. It's easy to be impressed. But enterprises are often struggling with "what comes next after that 5 minutes?"

We've been implementing many enterprise-level projects that use Power BI, and I'd love to spend time showing you what we do.

Amit Bansal and the DPS team in India are hosting a virtual summit later this year. There are three days of data-related conference presentations delivered 24 x 7. If you can't find something of real interest to you, and in your time zone, you aren't looking. And the price? At present it's $89 USD. Yes you read that right. And it includes a year of access to the recordings of all the normal conference sessions.

As part of their Data Platform Virtual Summit  event, there are also pre-cons and post-cons. Again they are amazing value.

I'm presenting a pre-con over two half days. At the current discount, it's $129 USD and if you use the discount code GREG@DPS, it's even cheaper. I'd love to see many of you attend. Let's talk about Power BI in the enterprise.

Now, this isn't a session about how to make great visuals, etc. I'll leave that to someone else. But if you want to really get into what makes a good underlying data model, how to integrate properly with security, how to structure projects, how we use Azure Analysis Services and/or Power BI Premium and more, this should be the right session for you.

You can book here.

T-SQL 101: #78 Custom formatting dates and times in SQL Server using FORMAT

In some upcoming posts, I'll discuss how you can change between data types, but I wanted to show you first, how you can use the FORMAT() function to convert dates and times to strings.

This query asks for the current date and time value formatted as day then month, then (four digit) year. It also includes the desired culture (en-US) which would be used if the format required any culture-specific items, such as the names of months.

You might wonder where that format string ddMMyyyy comes from. That's actually a .NET standard format, and so this is actually a wrapper function that calls the .NET Framework under the covers.

For this reason, it's also not a very fast function. If you can use CAST and/or CONVERT to do the formatting, always use that instead.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

T-SQL 101: #77 Switching timezone offsets in SQL Server T-SQL with SWITCHOFFSET

I mentioned in the last T-SQL post how you could create a datetimeoffset value by combining a datetime2 with an offset. But sometimes you need to switch to a different offset. And that's what the SWITCHOFFSET() function does:

In this query, I was taking a local time (that had a timezone offset of 11 hours), and switching it to the current time in Seattle (with a -7 hours offset):

So this function allows us to take a datetimeoffset value and convert it to a different offset.

Keep in mind that even though we have handling of timezone offsets, SQL Server isn't timezone aware to any great deal. However, there is a function sys.time_zone_info that will tell you the current offsets and daylight savings settings for different timezones.

What it doesn't do is provide you with future or historical timezone info.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

T-SQL 101: #76 Creating datetimeoffset values in SQL Server T-SQL with TODATETIMEOFFSET

It's great to have SQL Server data types now that handle time zone offsets. Sometimes though, you need to combine a datetime value and an offset to produce one of these new values. That's what the TODATETIMEOFFSET() function does:

In this example, I've taken 28th February 2019 and added a timezone offset of 10 hours to it:

the output data type is datetimeoffset.

The data type of the first value is actually datetime2.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SQL: Previous SSMS Tips and Tricks Session

One of the more popular things I've ever written is my (free) SQL Server Management Studio Tips and Trips eBook.

I've had a few people asking why I haven't done a video on it lately. I'm planning to do a series on it very soon.

In the meantime, here's a session that I did with Amit and the people from Data Platform Geeks a while back: Tips and Tricks Session

It covers a number of tips and tricks, but if you want all of them, you'll find the eBook here: https://ssmsbook.sqldownunder.com/

 

T-SQL 101: #75 Constructing dates and times in SQL Server using DATEFROMPARTS

I mentioned in previous T-SQL posts that one of the challenges is that there's no standard way to write dates, so we end up having to write them as strings. Now that was a real problem in earlier versions where people would get that wrong. The DATEFROMPARTS() function added in SQL Server 2012, though, can help with this:

This query takes the year 2019, the month 2, and the day 28 and returns a date:

All of the date related data types have options with FROMPARTS. So for example there's a DATETIMEFROMPARTS(), a DATETIMEOFFSETFROMPARTS(), etc.

Sadly, though, at the time of writing, the Intellisense for these in SSMS is very poor. Currently it doesn't tell you what each parameter is. That's not good.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

T-SQL 101: #74 Find the date for the end of a month in SQL Server T-SQL with EOMONTH

When writing T-SQL, it's surprising how often you need to calculate the end of month for a given date, and so you want the last date in the month for whatever date you supply. That's the main thing that the EOMONTH() function does.

So in this case, 16th of February 2020, we might wonder if that is a leap year. So would it be the 28th or the 29th? And so if we execute that, we find the 29th is the date, so this is actually a leap year:

Now this function can also take an optional second parameter. If you put the value 1, it will give you the end of the month after the date you supply. A value of 2 will give you the end of two months later. And so on. You can even give it negative values. That's actually quite useful.

There are two other comments I'd make on it though:

There's no equivalent BOMONTH (beginning of month). I think that's really a little strange.

The other thing is I actually don't like the name of the function at all. I'd really prefer it was ENDOFMONTH (or even END_OF_MONTH -> that's another thing the SQL Server team have never been consistent on. Some functions and object names have underscores between words, some don't).

There's no rational argument for saving those three characters, and for me, it's just an unnecessary abbreviation. I'm told the reason they did that is that's the name of the function in Excel. That doesn't sound a great reason to me and look, I'd encourage you, whenever you're writing code yourself, spell out names in full unless there's a really strong reason to abbreviate them.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

T-SQL 101: #73 Get string names of date and time components in SQL Server T-SQL with DATENAME

I've shown in recent posts how to extract components out of dates and times. You might also want the name of a month or a day, rather than just its number. The DATENAME() function does that.

In this query, I've asked for the name of the month that's part of the date 28th February 2019. So it returns:

An important concept here though is that this is based upon my regional settings. Mine currently are set to US English, to it returned February. But if I had different regional settings, it would display the name in whatever region I was configured to use. So if I had a French configuration that would come back with Février instead.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

SDU Tools: Finding the current session decimal separator in SQL Server T-SQL

I often say that I love writing code that writes code. I use T-SQL to generate T-SQL scripts all the time. (I use Excel to do it too but that's a story for another day). An issue I ran into a while back though, was how to output decimal numbers. I wanted to make sure I was using the correct decimal separator for the user who was running the script.

If you're not aware, it's common in European countries to write numbers like 234232,45 where in other countries we'd write 234232.45 instead. You can see that in the main image above where I've output a value with German culture.

The CurrentSessionDecimalSeparator function in our free SDU Tools for developers and DBAs, does this. It provides the separator that's currently in use.

It takes no parameters.

And of course, it's also important to get the thousands separator if one of those is needed. So we've provided CurrentSessionThousandsSeparator as well.

Find out more

You can see them in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

 

T-SQL 101: #72 Extracting date and time components in SQL Server T-SQL using DATEPART

In previous posts, I mentioned that you can easily extract the year, month, and day from dates and times, but you might want other components. That's what the DATEPART() function does.

You can see the output from the query above here:

To use this function , we put the interval that we're after and then the value we want to find it out from. So if you look at this one, I said I wanted the day from 28th of February 2019. The day is 28.

If we look at second example, I had a date there, but I had no time shown at all, so the second comes up as zero.

In the third example, I did have seconds shown, so that was 11 hours, 59 minutes and 46 seconds. It's then extracted the 46 seconds out.

It's a useful function.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.