Press "Enter" to skip to content

Day: April 29, 2016

Power BI With Mapping

Published 2016-04-29 by Kevin Feasel

Dustin Ryan shows how to use the Bing Maps API in Power BI visualizations:

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.

Comments closed

Compatibility Level 130 With Legacy Cardinality

Published 2016-04-29 by Kevin Feasel

Jos de Bruijn notes that there is a way to use the legacy cardinality estimator even when you are in Compatibility Level 130 (new in SQL Server 2016):

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.

The solution is to use one of the new database-scoped configuration options, namely the Legacy Cardinality Estimation option. You can enable this using the following ALTER DATABASE command:

I’ve had a very positive experience with the new cardinality estimator, but I certainly appreciate the option being there just in case.

Comments closed

Power BI Updates

Published 2016-04-29 by Kevin Feasel

Julie Smith points out new Power BI updates and she is excited about them:

Today, Microsoft announced the April Power BI Desktop updates and they include something I and my colleagues at Innovative Architects have been long awaiting: formatting for table reports!

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:

  1. Font size

  2. Colors –of any element in a table, font, background color, title, totals. Nada

  3. Borders

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…

Comments closed

Row-Level Security With Power BI

Published 2016-04-29 by Kevin Feasel

Koen Verbeeck discusses dynamic, row-level security using Power BI:

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.

Comments closed

Conditional Alerting

Published 2016-04-29 by Kevin Feasel

Dave Mason revs up SQL Server alerts using tokens and conditional responses:

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.

Comments closed

How To Shrink A Database

Published 2016-04-29 by Kevin Feasel

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.

Comments closed

Testing Cumulative Updates

Published 2016-04-29 by Kevin Feasel

Kendra Little discusses how to test cumulative updates:

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.

Comments closed