The good news is that Power Query in Power BI is flexible enough for us to take advantage of the Bing Maps API so that we can lookup the missing pieces of information we need. In this blog post, I’m going to show you how you can use the Bing Maps API to look up an address based on a latitude and longitude or use a street address to find a latitude and longitude for the location.
I love how map visualizations nowadays are so easy.
In SQL Server 2016 there are a lot of goodies under Compatibility Level 130, such as performance improvements with Columnstore and In-Memory OLTP and all the Query Optimizer enhancements done over the years under Trace Flag 4199. So we are faced with a situation where there are workloads that can benefit from some enhancements that come with Compatibility Level 130, but that cannot use the new CE.
I’ve had a very positive experience with the new cardinality estimator, but I certainly appreciate the option being there just in case.
While it was understandable that initially Power BI wanted to focus on visualizations first and foremost, the display for pure tabular data in Power BI was lackluster. There was no control over any of the following:
Colors –of any element in a table, font, background color, title, totals. Nada
I am simultaneously impressed that the Power BI team can so consistently push out crazy numbers of updates and glad that I don’t personally need to keep up on all of the changes…
Apparently, in PBI the domain is used, not the email. Good to know if row level security is implemented in PBI someday (note: currently it’s only available in the Power BI Service).
OK, now let’s upload this report to the Power BI Service so we can implement the RLS. When the report is opened, it seems that my carefully crafted measures are ignored. Maybe Power BI ignores the USERNAME() function as long as there is no RLS defined.
I like the fact that you can test by role, making it a lot easier to see what others would see under those circumstances and finding bugs with security implementation. If you have any non-public information and you use Power BI, this is a must-read.
There are three tokens within the T-SQL (highlighted in yellow above): A-MSG, DATE, and TIME. SQL server replaces these three tokens as follows:
- A-MSG: Message text. If the job is run by an alert, the message text value automatically replaces this token in the job step.
- DATE: Current date (in YYYYMMDD format).
- TIME: Current time (in HHMMSS format).
See the MSDN documentation for a list of tokens and their descriptions.
This is a great way of being smarter with alerts. Your SQL Server instance has a lot of information at the ready, so get familiar with what’s up for offer.
If “shrink that database” makes you cringe, that’s a good sign. Nevertheless, sometimes it has to happen. On that rare occasion in which it needs to happen, Andy Mallon gives you a how-to guide:
Perhaps you just implemented data compression, and have 60% free space in your database.
Perhaps you just deleted a bunch of old data, and have significant free space in your database.
There are a handful of scenarios where you have free space in the database, you’ll never use it, and you need to shrink. Just don’t make a habit of it.
Shrink should not be part of your normal routine, but it does come in handy sometimes. Auto-shrink, though? Burn that to the ground.
Install the cumulative update into a non-production environment (prod test or staging) and run application activity against it.
Ideally, you want this to be representative activity for your real, production applications, run from multiple application servers. You also want the hardware and storage to be as similar as possible.
If that’s not possible for you to set up, you need some sort of tool to generate concurrent activity. There are a lot of options — Michael Swart gives a great run down here.
This is some very good advice, and boils down to “test everything that you do in production.” It’s easy to forget some of these things, though, so I’m glad Kendra enumerated them.