Using bookmarks in Power BI help you capture the currently configured
view of a report page, including filtering and the state of visuals, and later
let you go back to that state by simply selecting the saved bookmark.

You can also create a collection of
bookmarks, arrange them in the order you want, and subsequently step through
each bookmark in a presentation to highlight a series of insights, or the story
you want to tell with your visuals and reports.

In this post we’ll quickly go over
how to create a few bookmarks and view them as a slideshow if you will.

I’m going to use my March Madness Report I created in an earlier post. Once my report is opened in Power BI Desktop, I’m going to click on the View tab in the ribbon and select “Bookmarks Pane”

Bookmarks Pane

This should bring up a new Bookmarks pane inside PBI Desktop:

Bookmarks

Remember, bookmarks are used to capture the current view of the report so I’m going to use the default view where I’m showing all data and I’m going to name the bookmark “Home”. Make sure all filters are selected to show all data and click Add under the bookmark pane. This will create a new Bookmark, named Bookmark 1. Click the ellipsis and select rename to rename the bookmark appropriately.

Next, I like North Carolina, so I’m going to go to my Team Filter and choose North Carolina which will show me data for only this team.

Team Filtered Power BI Report

In my bookmark pane, I’m going to click Add again and rename to North Carolina.

Next, I want to view data on North Carolina from 2000 to present so I’ll change the Year Filter.

Team and Year Filtered Power BI Report

In my bookmark pane, I’m going to click Add again and rename to North Carolina 2000-present.

Now, if I click on any of bookmarks,
it will take me to the data that was saved for each. This is a great way to
present data in a meeting/conference so you don’t have to manually change the
filters during the engagement.

We can also click the View button in the Bookmark pane to view a slideshow using the arrows at the bottom to navigate:

I wrote a post a few weeks about creating an Azure Windows VM so wanted to follow up with a post about creating an AWS Windows VM to compare both platforms. I like Azure and AWS so I’m not going to throw either one under the bus. Both are great and easy to use.

Let’s create an AWS (EC2) Windows VM.

Log into the AWS portal and click on EC2 under All Services, Compute:

AWS Management Console

Next, click Launch Instance:

AWS Create Instance

Step 1 allows you to choose an Amazon Machine Image or AMI. There are tons of options here, but for this post, I’m going to use Microsoft Windows Server 2019 Base

Windows 2019 Base

Once I click Select, I’m brought to Step 2: Choose an Instance Type. Instance Types comprise varying combinations of CPU, memory, storage, and networking capacity and give you the flexibility to choose the appropriate mix of resources for your applications. Each instance type includes one or more instance sizes, allowing you to scale your resources to the requirements of your target workload. More info here: https://aws.amazon.com/ec2/instance-types/

For this post, and for cost sake, I’m going to use the free tier t2.micro type which is 1 CPU, 1GB RAM

Number of Instances – This is the number of servers you want to create. If you need 5 of the same servers, this makes it easy.

Spot Instances – A Spot Instance is an unused EC2 instance that is available for less than the On-Demand price. Because Spot Instances enable you to request unused EC2 instances at steep discounts, you can lower your Amazon EC2 costs significantly. The hourly price for a Spot Instance is called a Spot price. The Spot price of each instance type in each Availability Zone is set by Amazon EC2, and adjusted gradually based on the long-term supply of and demand for Spot Instances. Your Spot Instance runs whenever capacity is available and the maximum price per hour for your request exceeds the Spot price.

Subnet: the range of IP addresses in your VPC that can be used to isolate different EC2 resources from each other or the internet.

Auto-assign Public IP – requests a public IP address from Amazon’s public IP address pool, to make the server reachable from the internet.

Placement Group: You can launch or start instances in a placement group, which determines how instances are placed on underlying hardware. When you create a placement group, you specify one of the following strategies for the group:

Cluster – clusters instances into a low-latency group in a single Availability Zone

Partition – spreads instances across logical partitions, ensuring that instances in one partition do not share underlying hardware with instances in other partitions

Spread – spreads instances across underlying hardware

There is no charge for creating a placement group. Learn more: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/placement-groups.html

Capacity Reservations – enables you to
reserve capacity for your Amazon EC2 instances in a specific Availability Zone
for any duration. This gives you the ability to create and manage capacity
reservations independently from the billing discounts offered by Reserved
Instances (RI). By creating Capacity Reservations, you ensure that you always
have access to EC2 capacity when you need it, for as long as you need it.

Shutdown behavior – specifies what happens
when an OS level shutdown is performed.

Enabled termination protection – You can
protect instances from being accidentally terminated. Once enabled, you won’t
be able to terminate the instance until this option has been disabled.

Monitoring – Monitor the instance with
Amazon CloudWatch.

Tenancy – You can select to run your server
on a shared server or a dedicated server.

Elastic Graphics – Enables graphic
acceleration.

For this post I’ll use defaults and click Next.

Step 4 is Add Storage.

I’m not going to go over each Storage option, but you can get more info here: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/Storage.html?icmpid=docs_ec2_console

AWS Storage

Selecting default and clicking next.

Step 5: Add Tags.

Like Azure, A tag consists of a case-sensitive key-value
pair. For example, you could define a tag with key = Name and value =
Webserver. A copy of a tag can be applied to volumes, instances or both. Tags
will be applied to all instances and volumes

Click Next.

Step 6 is Configure Security Group

A security group is a set of firewall rules that control the traffic for your instance. On this page, you can add rules to allow specific traffic to reach your instance. For example, if you want to set up a web server and allow Internet traffic to reach your instance, add rules that allow unrestricted access to the HTTP and HTTPS ports. By default, the RDP port is added, but it allows all IP addresses to connect. Changing the Source column will allow you to filter what IP’s are able to RDP into the server. For this post, I’m going to change the Source column to allow “My IP”

AWS Security Groups

Next…and last page is a summary of the options selected. To finish configuring the instance, click Launch.

AWS EC2 Summary

After clicking launch, you will see a popup where you can create or use an existing key pair. A key pair consists of a public key that AWS stores, and a private key file that you store. Together, they allow you to connect to your instance securely. For Windows AMIs, the private key file is required to obtain the password used to log into your instance. For Linux AMIs, the private key file allows you to securely SSH into your instance.

Once the new VM is created, you can go back to the EC2 dashboard and click on Instances to see the new VM:

The first line (Order Date Title = “Sales For “ &) is basically naming the measure and adding the beginning text for the title. The second line (MIN ( FactInternetSales[OrderDate] ) & “ to “) is finding the minimum order date from FactInternetSales.OrderDate and then adding the “to” text.The last line (MAX ( FactInternetSales[OrderDate] ) is finding the maximum order date from FactInternetSales.OrderDate.

This one was pretty easy. Once I’ve typed my DAX, hit the checkmark to make sure there are no errors and the click off screen.

DAX

Our measure has been created! Let’s go back and find it under the FactInternetSales fields pane.

Power BI Fields

Next, let’s click on the Card Visualization and move and size it appropriately to fit in our title space.

Card Visualization

While the card is highlighted, click on the new measure from the Fields pane and it will populate the card with the measure we created.

The only thing left to do is format the title and we’re all set! If we change the Order Date Slicer, you’ll notice the title changes with the date. See live example at the beginning of this post.

At the beginning of the year I set a goal to learn something
new. I’ve always loved business intelligence and bringing data to life in the
form of dashboards and charts so for the 1st half of the year I wanted
to focus on Microsoft’s Power BI. I’m not going to explain what Power BI is,
but if you want to read up on it go here: https://powerbi.microsoft.com/en-us/

This post is just going to show off my dashboard. 😊 See live example above.

I’m a huge sports fan and the best time of the year happens to fall in March. Besides my birthday being in March, it’s also March Madness. Hours and hours of basketball. I could of used AdventureWorks for my dataset, but I wanted to use something I’m interested in. I found some data containing every NCAA tournament game result since 1985 (when the tournament was expanded to the 64 team bracket). The dataset contains the year, round (1-6), seed of the teams (1-16), region (1-4) and the scores. Perfect. Let’s use this to create a dashboard.

There’s not a ton of data, but I used what I could and tried
to answer some questions around wins and upsets. Here’s a screenshot of the
final product:

March Madness Power BI Dashboard

You can see Wins By Team (Duke with 93, North Carolina with
78, etc), Wins by Seed, National Championships, and Upsets vs Wins by Year. You
can also see that a total of 2142 games have been played with 199 different
teams in the tournament.

This was really fun and answers a lot of the questions I was thinking in my head while designing. The top left corner also has slicers which help filter the data. For example, if I wanted to see only the data for 2015 I could change the Year slicer to 2015 and it would update all my visualizations:

March Madness Power BI Dashboard filtered by year 2015

You can see that Duke won the National Championship from the National Championships visualization. If you hover over the Wins and Upsets visualization, you’ll see there were 30 upsets out of 63 games.

Let’s say I want to view data for a certain Team. Let’s choose Alabama Crimson Tide. If I change the Team slicer to Alabama I can see some data based around this team.

March Madness Power BI Dashboard filtered by team Alabama Crimson Tide

Alabama has won 19 NCAA tournament games, 0 national
championships, has been a 5 or 7 seed 21% of the time and they’ve had a few
upsets along the way. Not bad for a football school.

What about data for the National Championship game? I can change the Round slicer to 6, which is the National Championship round and view the data this way.

March Madness Power BI Dashboard filtered by Championship game

I can see out of 34 games, there has only been 16 different
teams make the National Championship. Duke leads the way with 6, followed by
North Carolina and Connecticut with 4. The 1 seed has played in this game 59%
of the time, and there were upsets in 1988, 1990, 1997, 2003, 2006, and 2016.

We can also click on the visualizations themselves to view data. For example, if we reset our slicers to show all data and click on the #1 seed in the Wins By Seed Donut Chart we see the following:

March Madness Power BI Dashboard filtered by #1 seed

We can see that the #1 seed has played in 419 games with a
total of 41 different teams. Duke has won 51 games as the #1 seed while North
Carolina has won 46. Duke has also won the National Championship 4 times as the
#1 seed and in 1999 the #1 seed won 17 games which is the highest.

Really cool stuff. I loved working on this project and
working with this data.

I ran into an issue where SQL Server was installed with
the wrong collation and a lot of user databases were already attached. I could
easily backup the databases, uninstall, reinstall, and restore the databases
back, but this could take literally all day. There is a better and much faster
way to make this change. This post will go over it….

First, backup all databases (duh)

Next, we’ll verify the current collation. On this server it’s set to Latin1_General_CI_AS and I want to change it to SQL_Latin1_General_CP1_CI_AS.

Next, we’ll double check and make sure we have backups
of all databases 😊

Open SQL Configuration Manager and turn off all SQL Services:

Open Command Prompt (as administrator) and browse to the
BINN directory and type the following command.

Creating an Azure Windows VM seems pretty easy, but there are a lot of settings to be careful of before hitting the Create button. In this post, we’ll go over some of those so the next (or first) time you create an Azure VM, you’ll be ready!

Here we go….

Log into the Azure portal and click Virtual Machines and Create Virtual Machine:

Note: I’m using a free trial.

Under the Basics tab, there are a few options. Let’s start at the top.

Subscription: An Azure subscription is the agreement with Microsoft to use Azure, for which charges accrue. You can create multiple subscriptions to separate billing or management.

Resource Group: A container that holds related resources for an Azure solution. The resource group includes those resources that you want to manage as a group. You decide how to allocate resources to resource groups based on what makes the most sense for your organization.

Availability Zone: VM’s are in a different physical location within an Azure region. This offers 99.99% SLA.

Availability Set: A group with two or more virtual machines in the same Data Center is called Availability Set, this ensures that at least one of the virtual machines hosted on Azure will be available if something happens. This configuration offers 99.95% SLA.

Size: This is the size of the VM. Here’s a good chart to use when sizing. Remember, pricing! Another cool thing with Azure is that you can resize pretty easy by shutting down the VM, resizing it, and restarting. Of course, this depends on your region and availability options. https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sizes

Public inbound ports: Ports that are accessible from the public internet.

Already have a Windows license?: You can save money if you already have a Software Assurance license or an Active Windows server subscription.

In my example, I’m using a Free Trial, so that’s the only option for Subscription. For Resource Group, let’s create a new group (by clicking Create New), named SQLFreelancerRG.

I’ll name my VM SQLVM, put it in the East Region, with no redundancy and a fresh copy of Windows Server 2016 DataCenter.

Next, I’ll size this VM with the Standard DS1 v2, which is going to give me 1vCPU and 3.5GB RAM. I’ll create an Administrator account, allow no ports from the public internet and choose No for the Azure Hybid Benefit.

Virtual Network: A virtual network enables VM’s to communicate privately with each other, and with the internet.

Subnet: The subnet is the range of IP addresses in the Virtual Network.

Public IP: Public IP addresses allow Internet resources to communicate inbound to Azure resources. Public IP addresses also enable Azure resources to communicate outbound to Internet and public-facing Azure services with an IP address assigned to the resource. The address is dedicated to the resource, until it is unassigned by you. If a public IP address is not assigned to a resource, the resource can still communicate outbound to the Internet, but Azure dynamically assigns an available IP address that is not dedicated to the resource.

NIC network security group: Security rules in network security groups enable you to filter the type of network traffic that can flow in and out of virtual network subnets and network interfaces.

Public inbound ports: Ports that are accessible from the public internet.

Accelerated Networking: Enables low latency and high throughput on the network interface.

The next page is Guest Config where you can add additional configuration, scripts, agents, or applications via VM extensions. Click Next.

Tags are name values pairs that enable you to categorize resources and view consolidated billing. Think of this as Twitter hashtag.

Last page is Review and Create. This is the page that displays a summary of our selections including price per hour of uptime. You’ll also notice at the bottom there is a link that will allow you to download a template for automation. This will allow you to use the same specs that you created for this VM on other VM’s. Click Create to build your new VM.

If you click on the Notification Bell at the top of the portal, you’ll see that Deployment is in progress.

Once the VM is created, you can click Virtual Machines from the Favorite Bar and view your newly created VM:

If you click on the new VM it will bring up the Overview page (along with tons of other pages to choose from).

Click on the Networking tab and Add inbound port rule. Under Destination Port Range type 3389 and under Name type RDP inbound. This will allow RDP access.

SQL Server 2019 preview builds on previous releases to grow SQL Server as a platform that gives you choices of development languages, data types, on-premises or cloud, and operating systems. This article summarizes what is new for SQL Server 2019. For more information and known issues, see the SQL Server 2019 Release Notes.

In the New Column Master Key box you can choose from several key stores. The most popular stores are Current User or Current Machine. See below for description.

Local machine certificate store
This type of certificate store is local to the computer and is global to all users on the computer. This certificate store is located in the registry under the HKEY_LOCAL_MACHINE root.

Current user certificate store
This type of certificate store is local to a user account on the computer. This certificate store is located in the registry under the HKEY_CURRENT_USER root.

Be aware that all current user certificate stores inherit the contents of the local machine certificate stores. For example, if a certificate is added to the local machine Trusted Root Certification Authorities certificate store, all current user Trusted Root Certification Authorities certificate stores also contain the certificate.

In this example, we’ll use Local Machine. Type a name for the Master Key, choose Key Store and click Generate Certificate.

Click OK and a new Column Master Key will appear:

Do the same for any other master keys you would like to create. For example, Developer:

To create a Column Encryption Key, right click on Column Encryption Key and choose New Column Encryption key:

Give the key a name and select the Column Master Key that you would like to associate it with:

Do the same for the Developer keys. You should have two column master keys and two column encryption keys:

To view Local Machine certificates:

Open Certlm.msc

Browse to Personal, Certificates:

Double click a certificate and go to the Detail tab where you will see the thumbprint:

Match this thumbprint with the Column Master Key in SSMS:

Right click the certificate in Certlm.msc and choose Properties. Enter a friendly name to identify which certificate belongs with the Column Master Key.

With SQL Server 2012 we introduced Always On Availability Groups, and the Always On Availability Group Dashboard in SQL Server Management Studio (SSMS). This dashboard can be utilized by database administrators to view the current health of an Availability Group and its availability replicas and databases. While the dashboard can be configured to provide information regarding the latency between Primary and Secondary Replicas (can be calculated using the Commit LSN, Sent LSN and harden LSN values), it does not provide insights into the reason for the latency. To understand the reason for latency, requires capture and analysis of Extended Events and Performance Monitor counters. This activity can be time consuming and requires extensive knowledge of the Extended Events associated with Always On.

With the new SSMS 17.4 release, we are introducing the Availability Group Latency data collection and reporting built into the Availability Group dashboard. This feature masks the capture and analysis of the Extended Events from the end user and provides an easy to understand report detailing the time spent during the various phases of the Log Transport process.

What to use it for?

The Latency data collection functionality and the associated reports allows a database administrator to quickly discern the bottleneck in the log transport flow between the Primary and the Secondary replicas of an Availability Group. This feature does NOT answer the question “Is there latency in the Availability Group deployment?” but rather provides a way to understand why there is latency in the Availability Group Deployment. This functionality provides a way to narrow down the potential cause of latency in an Availability Group deployment.

How does it Work?

As seen below, the Availability Group Latency Data Collection functionality can be accessed from the Availability Group Dashboard.

Microsoft is releasing the SQL Server Diagnostics(Preview) extension within SQL Server Management Studio and Developer APIs to empower SQL Server customers to achieve more through a variety of offerings to self-resolve SQL Server issues.

What this offers to our customers?

Analyze Dumps – Customers using this extension will be able to debug and self-resolve memory dump issues from their SQL Server instances and receive recommended Knowledge Base (KB) article(s) from Microsoft, which may be applicable for the fix. The memory dumps are stored in a secured and compliant manner as governed by the Microsoft Privacy Policy.