When developing Power BI reports, using bookmarks and buttons together does more for you than just add pleasing aesthetics. It gives users the ability to switch between saved states of filters and slicers with the push of a button. Setting up multiple bookmarks/buttons makes navigation of your report much more straightforward to end users new and experienced with your data or to Power BI.

Setting Up Bookmarks

Bookmarks allow users to look at a saved state of specific filters or visuals. Bookmarks can apply to ‘all visuals’ on a report page or ‘selected visuals’ on a page.

All visuals should be used in the case when switching between bookmarks and wanting the page to return exactly how we left it. This will work for defaulting a page on a visual that uses the spotlight feature.

Selected visuals allow for different visuals to be used between each bookmarks, although the visuals may look the same each visual can contain different fields. Also, another case when using selected visuals is for persistent filters. This allows for a slicer on a page to be selected when switching between bookmarks and not defaulted and across page filtering will persist if the slicer is the same.

Updating Bookmark Using ‘All Selected’

1. Create a bookmark and rename it.
2. Set the page with filters, matrix list ordering, or spotlight.
3. Hover over the bookmark ellipse and update the bookmark.

Updating Bookmark Using ‘Selected Visuals’

1. In the selection pane move all slicer visuals to the top.
2. Select all the visuals beside the slicers.
3. Update the bookmark with all the selected visuals still being highlighted. This will allow the slicer to persist when filtering across bookmarks.

Setting Up Active/Inactive Buttons

Pairing together buttons and bookmarks allows users to actively engage in the report and buttons can be redesigned through text, sizing, and shape. We create active and inactive buttons that are in the same position, however, when one button is being used the other button is hidden.
1. Create a button by selecting the ‘buttons’ drop down and select ‘blank’ under the Insert tab in the ribbon.
2.Duplicate one button and make a button a darker color shade for the active button and reduce the transparency to 50% on the other button.
3. Assign the active button an ‘action’. Select a button and in the visualizations pane turn on the ‘action’ and for action type select ‘bookmark’ and choose the respective bookmark then stack the active and inactive button on one another.

How To Use The Selection Pane

The selection pane has the ability to ‘show’ and ‘hide’ visuals including buttons. By changing the buttons name and moving slicers and buttons at the top of the selection pane make updating bookmarks more organized. For the naming convention for each button, I can distinguish each button by their name followed by “active” or “inactive”.

The Bottom Line for Bookmarks and Buttons in Power BI

End users will find navigation of your reports much more straightforward and user friendly.   They will easily find the information they are seeking and become more skilled at utilizing Power BI to make business decisions.  You can learn more tips about using Power BI on our Power BI and Data Visualization page.

Brendan Phan is a BI Consultant at DesignMind. 

The post Advanced Bookmarks and Buttons in Power BI appeared first on DesignMind.

Understanding Summarized Data and Underlying Data

Before you try to export the underlying data, it’s important to understand the differences between summarized data and underlying data. Summarized exportation will only export aggregated data points in the visualizations; on the other hand, underlying data will export additional data that are not displayed in the visualization but are relevant to the visual.

When to export Summarized Data vs When to export Underlying Data

You should export summarized data when you want to see the relevant data in the visualization. Summarized data will only show the columns and measures that you created for the visualizations. If the visual contains aggregation, the export will contain aggregated data.

You should export underlying data in power bi when you want to see the relevant data and additional data from the data model you have created in Power BI. However, if your visual contains an aggregation, it will not be exporting the aggregated data.

Both data will be exported to an xlsx file, and your internet browser will prompt you to save the file, and then open it in Excel.

How to export Underlying data in Power BI

First, you have to make sure you have the correct settings. In Power BI Desktop, please select File > Options and Settings > Options > Current File > Report Settings, then under Export Data subtitle, please enable “Allow End Users to Export Both Summarized and Underlying Data from the Service.”

If the first step worked for you, congratulations. If the first step did not work for you, and the underlying data option is still greyed out, you need to create an aggregate function and implement it in the Power BI Visual. This can be achieved by creating a Power BI calculated measure and using any aggregate function, and then importing it inside the visualization. I like to use the SUM function, but functions like MIN, MAX, AVERAGE work too.

Remember, the function does not have to make sense; you are simply creating the function to bypass the export blockage in the Power BI Service. After you import the aggregate function inside of the data visualization, you can hide the aggregate function by minimizing the column. After it is complete, you can publish the report again.

Congratulations, you are now able to export both summarized and underlying data from the Power BI Service!  You can learn more about Power BI on our Power BI and Data Visualization page.

Chennie Teng is a BI Consultant at DesignMind. 

The post How To Export Underlying Data in Power BI appeared first on DesignMind.

In part one of this series, we showed you how to configure a Windows 2019 EC2 instance to run an OpenSSH Server to pave the way for secure, remote development. In part two, we configured the server for password-less login. Now, we can get down to the good stuff and configure the full developer tool chain.  You’ll see that setting up vs code for remote development is easy if you do it step by step.

Install VS Code on the Local Machine

VS Code and it’s extensions requires nothing to be installed on the development machine other than the OpenSSH Server. This is because the developer’s VS Code with the requisite extensions handle installing, starting and removing the “development server” into the SSH user’s home directory. Since the software is still in beta, it can be a little temperamental. Fortunately, VS Code comes with all of the tools for a user to manage it.

Install VSCode on your developer’s local machine. If you have never worked with VSCode before, you are in for a pleasant surprise. It’s a lightweight, rock-solid IDE that’s both free and powerful. It has extensions for most of your needs and has an active community. With a built-in package manager, it’s easy to configure and extend it too.

Install the Remote Development Extension

Open up VSCode’s Package Manager from the Command Palette.

Install the VSCode Remote Development extension.

On top of the remote tools, it will install three connectors. We will be using the one for SSH. It also ships with a Container connector for talking to Docker based environments and it installs a WSL connector for local WSL Installations. Don’t be fooled, that’s for connecting to WSL on the same development machine. We need to use SSH to connect to our remote development server regardless.

Configure the SSH Remote Development Extension

There’s quite a few powerful options for the Remote Server. Unfortunately, they are sparsely documented.

When you connect to an SSH Server the first time, it will create a map between the server address and the operating system of the development server. This is so it knows what commands to run.

For Windows, you’ll want to map it to windows and all commands will be done over Powershell.

For WSL Ubuntu, you’ll want to map it to linux and all commands will be run over sh.

There’s quite a few options that control how the Remote Development tools behave.

Getting to Work

With a properly configured SSH server, the rest is easy.

Open up VSCode and access the command palette Cntl+Shift+P.

> Remote-SSH: Connect to Host ...

This will bring up the connect dialog.

You will want to provide the hostname of your server and the destination OS. This host selection tells it what language of commands to run when it deploys the development server. The plugin deploys a temporary development server using Powershell for Windows and Bash for Linux. It uploads commands and deploys a node server that handles all the file synchronization and remote coordination. All over ssh – seriously cool stuff.

If you want to have more advanced configuration, the Command Palette provides the ability to edit your local developer’s .ssh/config file directly. Here is where you can store advanced SSH configuration options.

Git Plugin on the Development Server

WSL Ubuntu Users

You have git and key-agent forwarding out of the box (after you enabled it in sshd_config).

Windows Users

You need to install git via RDP. Since you don’t have key-agent forwarding, you will need to configure the ssh keys for the user and add the public key to your repositories settings.

With  your public SSH key added to your repository, you will have git access directly in VS code.

If you don’t have git installed, you will see this message on your VS Code Remote Source Control tab.

If you install after connecting, you’ll need to reconnect VS Code Remote. You’ll know you have git capabilities when you see:

Now, you have a fully-connected remote development environment that reads the git configuration for whatever directory you’re looking at in VS Code’s file browser or open files. It provides a full user interface for git, but you can also run the commands directly on the shell.

VS Code Remote Settings and Commands

The VS Code remote development tool chain is under constant development. If you can connect to SSH, that’s a majority of the battle. The rest can be achieved through manipulating the settings and using VS Code Command Palette.

Open up command palette with Cntl+Shift+P

Run Remote-SSH Settings

Also open command palette and Preferences: Open Settings (JSON)

Remote-SSH Settings is a GUI view of many different options that change the way the Remote Development server gets deployed. If you run into issues, this is the first place to look.

If your server dies, it might because of stale ssh processes or problems with the cached version on the server.

If you open up command palette with Cntl+Shift+P.

You will see Remote-SSH: Kill VSCode Server … which attempts to kill any related thread.

Remote-SSH: Kill VSCode Server … is a good step if the server refuses to behave. The VS Code Remote Development tools installs a tiny node server and does a bunch of things based on the latest build of the extension. This command should stop everything and delete any cached files. The next time you connect, it will do a clean install.

That’s it! Getting SSH running, especially for public key authentication, can be a chore. However, Microsoft is seriously moving the needle in terms of playing nice with opensource tech. The pandemic might have us all searching for new ways to work, but at least we can do it securely like we were sitting at our development boxes back in the office.

Brian Hatchet is a Senior BI Consultant at DesignMind.

The post Setting up VS Code for Remote Development with Source Control (Part 3) appeared first on DesignMind.

In part one of this series, we showed you how to configure a Windows 2019 EC2 instance to run an OpenSSH Server to pave the way for secure, remote development. This post walks through the setup of pre-shared keys for password-less login. Part three covers installing and configured VS Code’s remote development capabilities.

Setting up Private and Public Keys on the Local Machine

On your local developer machine, you need to generate a public and private key. Linux and Mac users will have all the tools you need. Windows users, you’ll need to install the ssh client on your local development machine. The remote development server needs to have the line below in its sshd_config and to be restarted like we did in Part 1.

PubkeyAuthentication yes

On the local development machine, we need to make sure we have a client.
In an elevated Powershell, run:

# Make sure that OpenSSH is available for your Windows

Get-WindowsCapability -Online | ? Name -like 'OpenSSH*'

# Install the OpenSSH Client

Add-WindowsCapability -Online -Name OpenSSH.Client~~~~0.0.1.0

Open up your favorite command line:

ssh-keygen

We’re going to go through the defaults without a password using RSA. Best practices recommend generating a key with a password. You’ll be prompted to provide the password whenever you try to use the key unless you load it into an agent as detailed here.

Once you have your key loaded, copy the contents of the id-rsa.pub and get ready to install it on the server. It will be located in the user’s ~/.ssh/id_rsa.pub file.

The last step is to load the ssh key into the local development machine’s key agent. The steps are different for Windows, Mac and Linux. Github provides an exhaustive guide here.

Installing the Public Key

For WSL Ubuntu from a Linux or Mac, you can install the key onto the OpenSSH Server with one command. You will need the destination user created and configured on the development server. You will need to password-based authentication enabled to use this command, because you must login as that user. Windows users, unfortunately, are out of luck on both sides. There’s no ssh-copy-id for Windows local machines and the OpenSSH Server for Windows lacks the Linux utilities that copy the key once it gets to the server.

For the WSL Ubuntu, you can run:

ssh-copy-id demo@{your ec2 dns name or ip address}

When prompted, provide the password we created in part 1.

You should see output like:

demo@ec2-3-101-29-193.us-west-1.compute.amazonaws.com's password:

Number of key(s) added: 1

Now try logging into the machine, with: "ssh 'demo@ec2-3-101-29-193.us-west-1.compute.amazonaws.com'"
and check to make sure that only the key(s) you wanted were added.


What this utility is doing is SCPing the specified public key to the users /home/demo/.ssh/authorized_keys file and setting that appropriate permission. If you’re running a windows client or have a windows server, you won’t have the convenience of ssh-copy-id.

You can however either WSL or SSH into the WSL Ubuntu shell and do everything there. This is all done from the user’s perspective, so the files need to be created in their home directory and they need to own it.

#Create the directory if it doesn’t exist

mkdir -p /home/demo/.ssh

touch /home/demo/.ssh/authorized_keys

sudo chown -R demo:dmemo /home/user/.ssh

Now, copy the contents of your id_rsa.pub file into /home/demo/.ssh/authorized_keys

And you’re done.

Installing the Key by Hand (Windows Administrator)

By default, OpenSSH Server for Windows comes with a special configuration for Windows Administrators. This is where things get kind of confusing.

If your user is an administrator, you need to create a special authorized key file.

%PROGRAMDATA%\ssh\administrators_authorized_keys

Log into RDP, create and edit that file. Then save the contents of your client machine’s id_rsa.pub key to that file for administrators.

We’re not quite done yet. The Microsoft OpenSSH team has been very helpful with answers. They are going to fix a permissions gotcha that exists at the time of this writing. If you install the key and can’t connect, it’s probably a permissions issue.

Be sure:

• The user on the development server is an administrator
• You have PubKeyAuthentication set to yes in the sshd_conf
• That the permissions are correct on the authorized_administrator_keys.

To fix the file permissions

Open up Windows Explorer

Go to %PROGRAMDATA%\ssh

Open up the Properties dialog for administrators_authorized_keys

Under Security > Advanced

Disable inheritance.

Remove any Permission Entry other than the Administrators group and SYSTEM.

If you’re looking for the OpenSSH Utils powershell cmdlets to fix this problem, don’t. Those have been de-listed from the nuget package repository and powershell gallery in favor of the OpenSSH Server handling the permissions themselves.

Installing the Key by Hand (Windows Regular User)

Regular users use the standard .ssh file in their user directory at HOMEPATH\.ssh\authorized_keys

Log into RDP, create and edit that file. Then save the contents of your client machine’s id_rsa.pub key to that file for administrators.

Troubleshooting Windows OpenSSH Server

If you’re reading this, welcome to the “SSH Server Administrator Bootcamp.” SSH is rock solid, but it likes a very specific kind of setup and tends to silently deny you access.

If the keys continue to fail, it’s time to get some diagnostic logging. You can turn on the SysLogFacility as detailed here. There’s an easier and more temporary way too.

Stop your OpenSSH server in services.

Open a terminal and go to %PROGRAMDATA%\ssh

Here, you can run the OpenSSH Server from the command line and see all of its output with sshd -ddd
The -d flag specifies levels of diagnostic output. You need three to get to “debug3”.

There will be a lot of chatter as it tries to authenticate.

If you see:

debug3: Bad permissions. Try removing permissions for user: S-1-5-11 on file C:/ProgramData/ssh/administrators_authorized_keys.

Authentication refused.

That’s SSH complaining about permissions on that file. It’s very strict about who can access it.

Once you have the OpenSSH Server accepting your key, kill the command line and restart the service. There’s many ways your key can fail. Some common pitfalls are permissions, character encoding and trying to install an administrator key in their regular .ssh authorized keys file. Fortunately, it’s a very chatty log file once you get used to its unique brand of diagnostic gibberish.

Another good technique is the -v “verbose” flag on the ssh client itself. On the local development machine, you can log verbose connection information with:

ssh -vvv {your ec2 dns name or ip address}

Turn off Password Based Authentication

SSH best practice recommends turning of password-based authentication. It’s a balancing act based on your security needs. If you have your SSH server configured to only allow certain connections through AWS’ Inbound Security rules, you won’t really need to worry about brute force attacks and the ease of ssh-copy-id will make it easy for you to upload keys if you know that user’s username and password.

If you do want to turn them off, then edit your sshd_config on the OpenSSH Server and restart it.

Agent Forwarding for WSL Ubuntu

One major advantage of running OpenSSH Server through WSL Ubuntu is that you get key-agent forwarding. By enabling it on the server, loading it into the developer’s local machine key agent and registering the public key with the repository, you will have one private / public key to access all the resources.

Setting up another key for Windows Development Servers

Until OpenSSH Server for Windows supports key agent forwarding, you are going to have to manage two sets of SSH keys. The first key we setup will handle the connection from the developer’s local machine to the development server. If you create a second set of keys on the development server and install that second public key on the repository, you will be able to have password-less authentication on the server.

Adding the Public Key to your Repository

With the public key of the client configured, it’s time to add it to the repository. We’re using Bitbucket in this example, but the process is the same for Github or Gitlab. Copy the public key for your user and paste it into the repository settings. Then, the user will be able to pull and push without ever having to worry about user keys.

Important: Be sure to clone your repository using the ssh url and not the user-specific https one.

You can confirm your remote url with:

git remote -v

And change it to the ssh version with:

git remote set-url --push origin git@github.com/demo/master


You will want to make sure you are using the correct url for logging into your repository. If you can clone and pull without providing a username and password, you can move on to part three and have a repository-aware remote development environment.

Brian Hatchet is a Senior BI Consultant at DesignMind. 

The post Setting up VS Code for Remote Development with Source Control (Part 2) appeared first on DesignMind.

(sshd or daemon for you Linux Folks)

COVID has revolutionized the way we think about work. Setting up a remote development environment is suddenly high on your list. While the sudden uptick in remote work is new, the tools necessary to setup a secure remote workflow have been getting easier and more powerful over the years. Now, there are many free tools available that will free your developers to work as they wish and Microsoft playing along with features in Windows and new IDE tools.

Gone are the days of clunky terminals and inelegant RDP. Visual Studio Code’s new remote code extensions and reliable old SSH allows the big three operating systems to play nicely together over a secure tunnel. The result can a blended environment where not even a single line of code needs to live on your local machine, regardless if you prefer Mac, Windows or Linux.

If you have a project that prohibits running things locally, you can setup a powerful stack. Using the power of SSH’s tunneling and key sharing, you’ll have a remote development environment with single sign on and only one key to worry about. The beauty of this setup is that you can run it on any server running SSH. We’re demonstrating a mix of Mac, Linux and Windows to highlight the portability of this setup and some of the hurdles that you might encounter. As long as your server can run SSH, you can use this setup. Windows Open SSH Server doesn’t support everything, but it’s very usable.

In this three part series, we’ll show you the ins and outs of setting up a remote development environment, and point out some caveats and gotchas along the way. We will walk you through the steps necessary to get things running from a local machine, connecting to a development server and access a git repository – all without a line of code running on the developer machine. Add in SSH’s local proxy, and you can access server resources without running them locally or even worrying about ports. Windows-based development servers don’t get key sharing because of a limitation in SSH, but there is a slightly inelegant workaround for that too.

Setting up your Server

By the end of this guide, you will have a fully configured Windows Server 2019 instance running an SSH Daemon through Windows or WSL Ubuntu. In part two of this series we walk through the setup of pre-shared keys for password-less login, and in part three you will learn how to connect VS Code to the server, code like you were sitting at the machine, and interact with the repository.

This guide assumes you know how to:

• Create EC2 Instances on AWS
• Administer Windows 2019
• Basic Ubuntu terminal knowledge

To show how we can have Mac, Windows and Linux playing nicely together. We’ll be using:

• A Windows 2019 Server running WSL (Windows Subsystem for Linux) and the Ubuntu Terminal environment running on an AWS free tier EC2 instance (Development Server)
• VS Code on Mac with the Remote Development and SSH extension (Local machine)
• A Git Repository

Create and start your new Free AWS Instance

VSCode’s remote development tools provide SSH access along with Containers and WSL access. While we can run it on a Linux server directly, folks using IIS and other Windows software are no longer out in the cold. We’ll demonstrate setting up a Windows server from the ground up to show how easy it is to get to work. We’ll be using RDP to configure the server, but it won’t be necessary by the time we’re done.

From your AWS console, set up your development server:

• Create a default instance in the free tier running Windows Server 2019 using the official AWS AMIs.
• Attach a security group to the EC2 instance that allows communication over RDP (TCP 3389) and SSH (TCP 22)
• Select a key pair you have access to so you can retrieve the administrator password.

Connect to your instance over RDP

With your instance create and ports opened, you should be able to find your EC2 Instance and connect.

Now, you can download the RDP connection file and retrieve the password by uploading the PEM key you chose when you created the instance.

Install OpenSSH for Windows (Option 1)

If you don’t want WSL, you can install OpenSSH directly on the server. This setup has a few things to consider:

1. No SSH Key Agent forwarding. The windows version of OpenSSH server does not support the forwarding of pre-shared keys from the local machine to the dev server to the downstream repository. It’s a very subtle limitation. The only indication of the problem is a line in the server logs. This can be hard to figure out considering how picky and prone to failing the OpenSSH server is regarding key security. You can, however, create and load an ssh key on the development server and configure the SSH agent to use that. We’ll be covering all of this in part 3. Now, you just need to decide if you want to be purely windows or if you want the power of Windows and WSL with a fully functional SSH Server.
2. Administrator Keys are weird. Administrator users have their public keys stored in a different file that the standard authorized_keys file in the user’s ssh directory.
3. Native Windows Shell. One major advantage of using the Windows version of the OpenSSH server is that it’s all Windows. Your SSH terminals will be in CMD. While this is easy to arrange in WSL, it’s out of the box functionality for the purists.

In an elevated Powershell, run:

# Make sure that OpenSSH is available for your Windows

Get-WindowsCapability -Online | ? Name -like 'OpenSSH*'

# Install the OpenSSH Client

Add-WindowsCapability -Online -Name OpenSSH.Client~~~~0.0.1.0

# Install the OpenSSH Server

Add-WindowsCapability -Online -Name OpenSSH.Server~~~~0.0.1.

# Enable OpenSSH Server to always start

Start-Service sshd  

Set-Service -Name sshd -StartupType 'Automatic'

You can read about the finer points of configuring the OpenSSH Server on windows here.

Install OpenSSH for WSL Ubuntu (Option 2)

If you do  all of the OpenSSH features and the power of Linux running along Windows on the server, you can WSL. This setup also has a few things to consider:

1. SSH Key Agent forwarding. We still must configure it, but it’s supported.
2. WSL needs to be run as a user. There’s no way to run WSL Linux services directly. Some scripts or a Scheduled Task configured to run as a Windows user at boot are required.
3. You’ll need to create users in Ubuntu as well as windows. SSH requires users to connect to on the development server. Windows OpenSSH server can just use Windows users, but you will need to create users inside Ubuntu for them to connect.
4. All windows files are available under /mnt/{drive letter}
5. You can write from Linux to Windows, but not the other way around.
6. You can run a mix of windows and bash commands, but Powershell takes precedence.
7. Windows executables require the full name including the extension.
8. If you start a WSL session from the desktop, you get access to the windows path variables. When you SSH in, you don’t unless you expressly set them up.
9. The WSL application is specific to that user. Fortunately, it can be exported and shared with other users.

In an elevated Powershell, run:

dism.exe /online /enable-feature /featurename:Microsoft-Windows-Subsystem-Linux /all /norestart

dism.exe /online /enable-feature /featurename:VirtualMachinePlatform /all /norestart

wsl --set-default-version 2

Now, you can install the Linux distribution of your choice. You can install multiple flavors at the same time too. This guide will be using Ubuntu 20.04.  The official Microsoft guide is here. Downloading and installing will take a few minutes.

In an elevated Powershell, run:

Invoke-WebRequest -Uri https://aka.ms/wslubuntu2004 -OutFile Ubuntu.appx -UseBasicParsing

Rename-Item .\Ubuntu.appx .\Ubuntu.zip

Expand-Archive .\Ubuntu.zip .\Ubuntu

cd Ubuntu

ubuntu2004.exe

$userenv = [System.Environment]::GetEnvironmentVariable("Path", "User")

# Make sure this concatenated path matches the ubuntu2004.exe location
[System.Environment]::SetEnvironmentVariable("PATH", $userenv + ";C:\Users\Administrator\Ubuntu", "User")

This will download and extract Ubuntu2004 as a exe in the administrator’s home directory.  When you run it for the first time, you will configure a user and password. It will also become the default distribution when you run wsl.exe. We’ll be creating and using a user called demo with a password demo.

Install OpenSSH Server in WSL Ubuntu

Log into wsl as the user you created.

ubuntu2004.exe

# Inside Ubuntu Shell

sudo apt install ssh

[sudo] password for demo: {the password you created}

sudo ssh-keygen -A # Generate the ssh keys for the server

sudo service ssh start

Run OpenSSH Server in WSL Ubuntu when Windows starts

One downside of this setup is that there is no way to run WSL as a service. We’ll need to configure Windows to login and start WSL as a user via a Scheduled Task.

First, we need to allow the OpenSSH Server to start without authentication inside WSL Ubuntu

Log into wsl as the user you created.

# Enable starting sshd without password

sudo visudo

[sudo] password for demo: {the password you entered}

#Add this line below %sudo ALL=(ALL:ALL) ALL

%sudo ALL=NOPASSWD: /etc/init.d/ssh

# Exit and save

On the development server, open Windows Task Scheduler and add our command as a startup task.

Click on Create Basic Task

Name it Start WSL sshd

Move to Next > Trigger

Select When the Computer Starts

Move to Next > Action

Select Start a program

Move to Next > Program.script

Enter C:\Windows\System32\wsl.exe

Add arguments (optional): -u demo sh -c “sudo /etc/init.d/ssh start”

Run the task and refresh it. You want it returning 0x0 as a status code.

Select the task that just got created and enable it to always run. You will need to enter the credentials of the windows user, else it just won’t run.

Restart the server. The SSH server should be listening by default and you can move on to getting SSH access.

Allow OpenSSH Server traffic through Windows Firewall

The AWS Security group with the traffic for 22 takes care of our AWS firewall access, but Windows Server 2019 will block the traffic by default.

Whether you installed OpenSSH Servber via Windows or WSL Ubuntu, you need to allow traffic on port 22.

Add a Windows Firewall Rule to allow SSH by running these commands in an elevated Powershell. Note, this will open up 22 for all TCP traffic at the Windows Firewall level.

In an elevated Powershell, run:

Set-NetFirewallProfile -All -Enabled True

New-NetFirewallRule -DisplayName 'WSL SSH Inbound' -Direction Inbound -Action Allow -Protocol TCP -LocalPort @('22')


Let’s Get Connected

You should now be able to open an ssh connection to the server. You will not be able to login by default because we must setup our authentication. By default, OpenSSH Server ships with password-based login disabled because it’s insecure. You’ll need to configure an SSH client on your local machine. On mac and linux, you should be able to open up terminal and have ssh dump its command line arguments.

Windows users, you must have an ssh client installed. If you’re running Windows 2019 or Windows 10, you can install the windows client version. While you can use Putty for now, the SSH keys pageant generates need to be converted to work with OpenSSH Serbver.

You can install the OpenSSH client for Windows on your local machine by opening an elevated powershell and running:
# Make sure that OpenSSH is available for

Get-WindowsCapability -Online | ? Name -like 'OpenSSH*'

# Install the OpenSSH Client

Add-WindowsCapability -Online -Name OpenSSH.Client~~~~0.0.1.0

Configuring SSH keys will be covered in Part Two. For now, let’s try it with our username and password before we go locking things down. Preshared keys are finicky and will fail unless they are in the right place with the right permissions. By validating with a username and password first, we can make sure everything else works.

Configure OpenSSH Server for Windows

The first thing we need to do for Windows is copy the default config file to the run time location.

In an elevated Powershell, run:
cp C:\Windows\System32\OpenSSH\sshd_config_default 'C:\ProgramData\ssh\sshd_config'

Edit C:\ProgramData\ssh\sshd_config in your favorite text editor.

While we are in there, let’s turn on PublicKey Authentication for later by uncommenting and changing this line to yes.

PubkeyAuthentication yes

And for temporary testing purposes, enable password based authentication by uncommenting and changing this line to yes.

PasswordAuthentication yes

Restart the OpenSSH Server. You should be able to login to your box using your RDP credentials.

On your local machine, run ssh -vvv administrator@{your ec2 dns name or ip address}

And login with the same password retrieved via the pem key. The -vvv flag puts the Open SSH Client on your local machine into verbose mode and shows all the authentication steps it is trying. That kind of verbosity will aid you in troubleshooting connections and understanding what SSH is trying to do.

You should now be sitting at a remote terminal and can interact with CMD.

When you’re satisfied, be sure to set PasswordAuthentication to no in the copied C:\ProgramData\ssh\sshd_config.

Restart your server and get ready to configure some OpenSSH keys.

Configure OpenSSH Server for WSL Ubuntu

Login to WSL on the Development Server as demo.

wsl -u demo

In the WSL shell:

edit /etc/ssh/sshd_config

While we are in there, let’s turn on PublicKey Authentication for later by uncommenting and changing this line to yes.

PubkeyAuthentication yes

And for temporary testing purposes, enable password based authentication by uncommenting and changing this line to yes.

PasswordAuthentication yes

Find and uncomment the line that allows Agent Forwarding

AllowAgentForwarding yes


Exit and save your changes then restart the OpenSSH Server with sudo service ssh restart

On your local machine, run ssh -vvv demo@{your ec2 dns name or ip address}

And login with the same password you created in WSL. The -vvv flag puts the Open SSH Client on your local machine into verbose mode and shows all the authentication steps it is trying. That kind of verbosity will aid you in troubleshooting connections and understanding what SSH is trying to do.

You should now be sitting at a remote terminal and can interact with Bash. While you are in Ubuntu, you can access your windows environment at /mnt/c.

That’s it! You should be SSHing into your box which the main thing required to setup remote code. Before we get to the really good stuff, Part two will talk about tweaking your installation to enable Public Key Authentication and other goodies. Part three will go over Visual Studio Code remote development and draw back the curtain on the power of this setup.

Brian Hatchet is a Senior BI Consultant at DesignMind.

The post Setting up VS Code for Remote Development with Source Control (Part 1) appeared first on DesignMind.

Why would you even consider right sizing your Power BI Gateway on AWS? While Microsoft provides a Redshift Datasource, that requires pulling things across the wire from AWS to Azure or to your Power BI Desktop. Treating AWS’ data resources like an “on-premise” data lake, allows us to configure Power BI Gateways onto EC2 Instances in the same VPC as the database. We can offload and localize Refreshes and Direct Queries. Now, we just need to right-size our Gateway clusters so we have the capacity we need without breaking the bank.

Installing the Power BI Gateway using PowerShell cmdlets

Let’s talk about Power BI Gateway clusters first. Power BI Gateway already provides some basic load balancing, but they require big expensive machines that must be pre-configured and connected.

When you install the gateway, you get a choice after you authenticate. You can either register the gateway as a new cluster or you can connect it to the existing cluster. Here’s the rub. That part cannot be automated as of yet.

The Power BI Gateway installer doesn’t accept any options. We can’t select the gateway. We can’t provide the recovery key. We can’t connect to an existing cluster.

If we could automate these settings, we could connect a new instance to an existing gateway cluster.

The Power BI team provides us with some PowerShell cmdlets that lets us do almost everything. With the modules outlined here, we can do almost everything. We can install the gateway, login and create a new gateway. Microsoft’s Power BI team points out this limitation in the public preview of their new cmdlets to automate it.

Right sizing your Power BI Gateway on AWS

That’s it in a nutshell. We can automate installation. We can create NEW gateways. We cannot register a new gateway to a new installer in an automated manner with either the installer or PowerShell.

There goes a pre-cooked AMI with everything installed and a user-data script to configure a gateway and add it to the cluster. Since each node needs to be manually configured, we need to resize pre-existing machines in AWS.

Enter Ops Automator

There’s no way to resize and existing EC2 with the tools that AWS provides out of the box. ELB and Autoscaler groups are designed to work with ephemeral instances that auto-configure themselves. Fortunately, AWS provides an enhanced framework
that makes such things possible. They provide a CloudFormation template that installs this impressive stack.

The core of it is Lambda functions that do powerful things not available to us on the Console. Once you install that framework, our AWS configuration is relatively straightforward. We can use the VerticalResize task provided by the framework, attach it to our EC2 instances to RESIZE the installation.

Bringing it all together

In order to make this work and ensure maximum uptime while reduced cost, we must configure the Power BI gateway itself to use their own internal load balancing and then use the Vertical Resize task to setup the pre-configured EC2 instances under load and step them down when the load cools off.

Microsoft provides some out of the box load balancing with some tweaks to the configuration files on the primary gateway here. By default, load balancing is random. By adjusting the metrics in the config file, we get greater control to start routing traffic to child nodes. If we didn’t care about cost, we’d just have a bunch of machines running and they’d balance the queries and we would be done.

This is AWS we’re talking about here. The recommended configurations for an On-premises gateway get expensive real fast. So, why not have one main gateway running at the specifications and then have child nodes running on Free Micro instances that scale up under load?

That’s where the ops automator comes in. By using the Ops Automator framework, we can tag our child nodes and have them scale up as they come under load. The Power BI Gateway routes the traffic and the child nodes go offline, scale up and then come back online. Getting the responsive, always on cluster is a balancing act of scaling up in time, but this is just the basic configuration.

With CloudWatch and better monitoring, we can get those child nodes scaling up once the Power BI Gateway goes underload. Once the Power BI team delivers the PowerCmd modules planned on their road map, then we should be able to use auto scaling and throw away ephemeral gateways when we are done. This solution for right sizing your Power BI gateway on AWS is a handy trick for those who need to resize EC2 containers that must be configured by hand.

Brian Hatchet is a Principal Software Engineer at DesignMind, and specializes in .NET, open source, and team software development processes.

The post Right Sizing Your Power BI Gateway on AWS appeared first on DesignMind.

Power BI Custom Visuals provide a way to extend OOB functionality with other open source visualization tools like D3, jQuery, and R. This gives you a great deal of flexibility in terms of formatting, visual elements, and capabilities. Microsoft even provides the source code for some of their OOB visualizations for you to make tweaks to if you want to try to self-teach yourself how to do this.

Unfortunately, custom visuals can be a lot of effort. You’ve got to know Java, D3, CSS, HTML, and possibly other languages to complete a custom viz package (PBIVIZ). You’ve also got to be intimately familiar with all the different aspects of a custom visual project to be effective. Field well combinations, Formatting options, drill-through, and tooltips all have different areas of the custom visual project you need to know where they are, how to code, and how to tie it all together.

At DesignMind, we help clients break these Power BI custom visuals down so that we can get you a minimum viable product that you can take over and make subtle tweaks to. Here’s some pointers on how to get one of these projects underway.

Power BI Custom Visuals Step by Step

1. Break down the Design elements

As with any project, you need to break down the elements of what you want to build. What may seem simple at a moment’s glance can quickly turn complex when you factor in all the variables. Let’s look at an example.

Below is a circle card visual that Microsoft includes in their tutorial on how to build custom visuals. Let’s break it down to its visual elements:

1. Circle with a border
2. White background color in the circle
3. Value text
4. Label text

Okay, not too bad. So let’s say you install this and get everything working on your laptop. Once you start playing with the visual, you start to ask questions: How does resizing the visual work? Does it auto-scale? Should the data labels be fixed position or allow the user formatting options? How do change the background of the circle based on the data? How can I add more data to this visual? What about tooltips? Drill-though?

So after giving it some thought and talking with your team, now your breakdown starts to grow:

1. Data wells
   1. Value
   2. Label
   3. Color
2. Formatting Options:
   1. Border/No Border
   2. Font
   3. Font Size
   4. Font Color
   5. Value Positioning
   6. Label Positioning
3. Tooltips
4. Drill-through

Now this is just the beginning. After you and your team have the night to sleep about it, you come back with all kinds of ideas: icons, arrows, sparklines, etc.

Like with all projects, narrowing down the scope to get a minimum viable product (MVP) is critical to getting acceptance, feedback, and championing.

2. Get the Visual Studio Code editor

Visual Studio Code is probably the hottest code editor out there on the market today. (At least for Microsoft development). It’s fast to develop, fast to extend, and continually updated every month just like Power BI! If you don’t already have this tool in your development toolbelt, try it out. It’s great for text editing, but you will ultimately need to learn it if you plan to do anything with custom visuals.

3. Check in your work

If you don’t already have source control, get some. There are plenty of providers out there that you can use for free like GitHub, Bitbucket, or Azure DevOps. Probably the fastest if you don’t already have a source control provider (SCP) is GitHub. To integrate GitHub with Visual Studio Code, all you need is to download and install Git. Checking your code into GitHub is easy and there’s no excuse not to do it this way.

4. Learn

Probably the easiest way to learn how to build Power BI custom visuals is to start with a basic visualization package like D3. There are many examples out there on the web that look fantastic! Try them out and impress your friends.

5. Test live or test in Power BI Desktop

If you go through the Microsoft tutorial online, they walk you through how to setup a development environment which is very helpful if you’re making small incremental changes to your code. In the end though, be sure you can package your custom visual with the pbiviz package command. That produces the magical pbiviz file that you can then import into Power BI Desktop, tie to your data, and publish to the Power BI Service and see your work in all its grandeur.

We’d love to hear from you.

If this task seems daunting, talk to us! We’d love to hear about what custom visual you have in mind and how we might be able to help. Our team at DesignMind has years of experience designing, developing, and architecting Power BI solutions for organizations, large and small. You can see more tips from our Power BI experts here.

Gnanaprakash Rajamanickam is a BI Consultant at DesignMind. 

The post Creating Power BI Custom Visuals appeared first on DesignMind.

When your Power Report is too slow

Throughout my time working with various clients, developing and architecting Power BI reports, our clients continue to ask why some reports seem to render very slowly. Sometimes those Power BI Reports don’t exhibit performance issues until they are fully deployed to the Power BI Service. In this post, I will share the methodology I follow when optimizing Power BI report performance.

Why should you care that your report is running so slowly?

When a Power BI report runs slowly, valuable resources are being consumed, and are not available to other reports and users. There can be many causes of performance issues (eg. model design, calculated columns, calculated measures, visuals and/or the amount of data being displayed on the report). One side effect of a slow running Power BI report is the stress on memory consumption (See my earlier post covering Power BI Report Memory Issues). In addition, the more users consuming the reports, the more non-optimized performance will affect your users and your organization.

How to discover and speed up report performance

Prior to the introduction of the Power BI Performance Analyzer, the task to assessing performance issues were squarely in the hands of the Power BI developer.
Here is the list of tools that are part of my arsenal when working on any Power BI performance assessment project, along with a few others I will write about in a future post.

A. Power BI Performance Analyzer
This tool allows the Power BI developer to review the performance of each report element, such as visuals and DAX formulas.
B. DAX Studio
This is a third-party tool from SQLBI developed to write, execute, and analyze DAX queries against Power BI reports, Excel Power Pivot, and Analysis Service Tabular models.
C. VertiPaq Analyzer
This is another SQLBI tool that allows you to analyze VertiPaq storage structures for a data model in Analysis Services Tabular, and also for Power BI Documents
D. Memory consumption Footprint learn more in “Are Your Power BI Performance Issues due to High Memory Consumption?”

Performance tuning cycle

Sometimes Power BI Report performance issues do not come to light during development; instead, it becomes a performance issue when users start consuming the Power BI report on the Power BI Service. The performance issue might come up in a conversation in which a user mentions a report has been running too slow or a visual is taking too long to render.
During a performance tuning project, I like to break down the computing effort, as shown here.

The flow of the task is not always linear or left to right, since there might be times where we have to review the analytic model due to calculation or report requirements.

Performance Scenario

Our client has informed us that there is a Power BI Report that contains ten pages with a set of visuals per page and a compressed size of 300 MB. The report is suffering from performance issues.

Let’s try to resolve this problem

To work with slow performance, we need to download a copy of the report to our local computer and open it using Power BI Desktop. Next we need to get some insight into the report model without having to go through each table and inventory everything by hand. VertiPaq Analyzer provides a schema plus inventory of a Power BI or tabular model with other information such as cardinality information, compression, hierarchy, calculated columns, calculated measures and more in one Excel document.

Here are the steps needed to connect to the Vertipaq Analyzer document.

A. Install DAX Studio

B. Download VertiPaq Analyzer
The ZIP file includes various version of the Vertipaq Analyzer excel document. Please review the readme file found on the zip file to determine which excel document. Once you determine which excel document to use, make sure that you enable Power Pivot for excel.

C. Open the Power BI Report using Power BI Desktop

D. Open DAX Studio

• Click on Connect, in order to connect to open Power BI Report from step C and press Connect button
• You should be able to see the Power BI Data Model on your screen as shown below

• In order to connect the Vertipaq Analyzer you will use the connection string to the Power BI Report
• In DAX Studio, make a note of the localhost:port found on the botton far right, as shown in the figure below

• Open the Vertipaq Analyzer version according to the readme describe in step B
• Once the excel document is open, click on Power Pivot and Select as shown below

• This will open the Power BI Manage Screen, where you will click on the Existing Connections in order to change the current Vertipaq Engine connection to your current Power BI Report

• When you click on Existing Connections, select SSAS and press the edit button to change the connection information

• In the connection screen you will select Build, which will open the Data Link Properties window

• In the Data Link Properties window under the Data Source, enter the information you retrieve from the DAX Studio i.e Localhost:XXXXX
• Once you enter the Data Source, click the dropdown for “Enter the initial Catalog to use” in order to select the actual Power BI Model database
• Press the “Test Connection” to make sure you can connect to the Power BI Model
• Press OK, then SAVE, and finally Close
• If everything went well, click on the Save button and then on the Excel button to return to the Excel report

• Once in the Excel report, Click under the Data Menu -> Refresh All

This shows a sanitized version of the report we are going to be working with:

The image above shows one of the many information tabs that the Vertipaq Analyzer tool creates related to the number of tables, compression before, compression after, and other information.
One of the key pieces of information from this tool is column cardinality, the count of unique values in a column. This information is important when looking at how well a column compresses. If it is an identity column with unique values for each row, the Vertipaq engine will not compress. When compression is not optimal, the memory usage can get very large.

Data Ingestion

I recommend that data ingestion be carefully reviewed with respect to how much memory is allocated during the data refresh. You may be depriving your Power BI environment of much-needed resources. Since this data ingestion performance is not the focus of this blog post, we will leave this topic for a future post.

Model Design

Although Power BI is a self-service model that allows the data to be modeled with few restrictions, careful design consideration should be implemented to optimize the Vertipaq engine. A model can consist of a single table, or multiple tables with relationships (Many to One, One to Many, Many to Many, etc.). In this blog, we will use the model shown here:

The model above shows the usage of bi-directional relationships, support parameter tables, and hidden tables.

DAX Measures

As the client or consumer reported, the Power BI Report in question performs poorly. Using Power BI Performance Analyzer, we can get a first-hand look into each page(tab) performance.
Steps to get baseline performance on each report tab:
A. Open the report in question using Power BI Desktop
B. Create a blank page for this report and save it.
C. While on the blank page, close the report.
Close the Power Bi Report while in the blank page. This will allow the report to open directly on the blank page and not run any other report page prior to running the Performance Analyzer.
D. If possible, close any other open applications
E. Open Power BI Desktop
F. Open the Power BI Report in question, making sure that it opens on the blank page

G. While on the blank page, click on Menu->View->Performance Analyzer to enable performance capture

H. With the Performance Analyzer enabled

Click on “Start Recording”
Click on the desired report page to record the performance log

I. Since we are interested in the query time, click on the arrow pointing down and select Descending and Sort by DAX Query to display the worst performing queries, according to the business predefined time to render threshold (3-5 second max)

J. Here are some of the worst performing DAX queries, expressed in milliseconds

K. Once the performance capture is completed, click on Stop and select Export to save the performance log file for later review as a JSON file

L. Once you have saved the performance file for the current page, click on Clear, then click on Start recording and move to the next page, and iterate from step H to K as needed

Performance Analyzer log review

Once the Performance Analyzer logs have been created, the process to review and fix poorly performing queries begins. In our case, we will take a single poor performance log and try to find a solution. The DAX Studio tool allows us to not only write a DAX formula, but also to check what is happening in the Formula Engine and Storage Engine.
To investigate a Power BI report using DAX Studio, we will first need to open the Power BI Report using Power BI Desktop. Once the report has been opened in Power BI Desktop, we will start the DAX Studio tool and connect to the Power BI Report. Once connected, we will then click on “Load Perf Data” to load any of the captured Performance Analyzer log files, as shown in Fig 5.

Once the Performance Analyzer log has been open, we should be able to see all of the query’s issues against the model for the Power BI Report page in question, as shown in Fig 6. This figure shows a query that is taking 190,894 milliseconds(3.18 minutes) to complete (WOOO).

While in DAX Studio, we will click on the query in question to display the code in the empty window, as shown in figure 7.

It’s great that we can see the performance issue, but how can we see what is happening behind the scenes? We want to see the actual performance and the break-down of the query execution. On this screen we will click on the “Query Plan” and “Server Timing” to provide the query time profile and query plan.

To get a baseline for the poor performance visual query, we will run this query as follows:
A. Click on the clear cache
B. Click on the Run
C. Once the run has completed, make a note of the following items:
a. Server Timing
i. Formula Engine
ii. Storage Engine
iii. CPU

b. Review the Query Plan
i. Number of records that it is iterating against
ii. Number of lines

Once we have completed our baseline, we need to review the actual DAX query to determine what is making our query perform so poorly. Below is a copy of the original poorly performing DAX Query:

The DAX query in question contains seven calculated measures, so we executed each measure individually by commenting the other six one by one to get their performance metrics. The table below shows each DAX measure time to complete performance and Server Timing metrics.

As we can see from our performance inventory, query number 2 is the one that is taking the longest amount of time to complete, so we are going to review the actual DAX code and see how it can be improved.

While still in DAX Studio, we will use the search capability to look for the DAX measure plus its dependents, and see what is making this DAX formula run so slowly. When searching for the DAX measure in question, we want to select the ‘Define Dependent Measure” to retrieve all of the in-between DAX measures used by the final DAX measure shown below.

The table below shows the before DAX query and the modified version:

The query performance issues in our case were related to the usage of a Table Filter instead of a column filter. The original formula was iterating through the 4 million-plus records by using a Filter, which once replaced with a column filter, the actual size of the number of records we needed to iterate is reduced.

The table shows the performance profile before and after modifications:

Resources when your Power BI Report is too slow

By going through the performance review cycle of VertiPaq Model, Data Ingestion, Model Design, DAX, and the various tools, you should be able to understand and fix almost any Power BI Report performance issues. Our Power BI team at DesignMind can help you as you implement Power BI in your organization.

Here are resources that might be of help when development or troubleshooting Power BI reports.

• SQLBI Tools such as “DAX Studio,” VertiPaq Analyzer,” “DAX Editor,”BISM Normalizer” and many more
• The Importance of Star Schemas in Power BI
• Comparing DAX calculated columns with Power Query computed columns
• The Definitive Guide to DAX book Second Edition

Tomas Santandreu Polanco is a Senior BI Consultant at DesignMind.

The post My Power BI report is too slow. What tools and techniques should I use? appeared first on DesignMind.

Power BI performance is one of the key factors affecting satisfaction by business users.  We’ll examine memory consumption by Power BI Reports, how it can be measured, and how you can use this information to improve report performance. We’ll look at a report memory footprint, and how that affects report performance. We’ll also look at which Power BI-related Windows processes are running on your machine, and how measuring memory consumption can provide a path to fixing poorly performing reports.

In my time working on Power BI projects for our largest clients, I’ve often been asked why a Power BI report is consuming so much memory as our users navigate through their reports. I also get asked about the need to move to a more expensive tier when using Premium Capacity, even when there are just a few reports.

Why should you care about your Power BI report memory footprint?

For Power BI Premium capacity, each tier has predefined computing resources assigned to it. These resources relate to how much memory and CPU are available on that Premium node. Having the ability to assess a Power BI report’s memory requirements allows you to ensure good performance for all reports, and adequate memory capacity is available when you need it. This allows you to be at the lowest cost Premium Capacity tier and still maintain healthy Power BI performance.

Measuring Power BI document memory consumption

While using Power BI desktop to create a Power BI report, you can use the Windows Task Manager to review the current memory footprint of the report. To do this:

A. Open the report in Power BI Desktop

B. Insert a blank page

C. While in the blank page, save and close the report

D. If possible, close all open applications

E. Launch Power BI Desktop again, and open the previously saved report

F. Once the report is open, you should be in the blank page that you had added (the report should open to the blank page added in Step B, above)

G. Open Windows Task Manager by pressing Ctrl-Alt-Del, and selecting Task Manager from the list provided. Expand the Power BI Desktop Group under the process tab
(Detailed information about these processes can be found at the end of this post.)

H. Wait for the report CPU % to approach zero, which means that it has completed loading it into memory. See this sample report on the blank page.

I. Next, navigate to one of the pages where memory consumption is very high due to an underperforming DAX formula.

J. Once you’ve optimized DAX formulas that were causing the memory spike, you can do a side by side memory comparison between the initial version and the optimized version.

Repeating the process as you build new reports or rework slow reports will allow you to minimize memory usage and get the most out of your premium capacity.

The associated processes are defined below:

CefSharp.BrowserSubprocess

• This process allows Power BI Desktop to simulate the Power BI Service

Console Windows Host

• Microsoft internal process

Microsoft Mashup Evaluation Container

• The Power Query Engine
• It is responsible for processing all the steps in the Power Query Editor, which gets data from my sources, transforms it and then loads it into my data model

Microsoft Power BI Desktop

• This is the starting point executable and container for all the processes run within Power BI Desktop

Microsoft SQL Server Analysis Services

• The analytical data engine
• This is where all the data gets loaded from Power Query into the data model.
• This process can have the highest memory usage
• An expensive DAX measure that must get most of its data from the storage engine will see an increase in memory utilization and CPU during the evaluation and running of the DAX measure

Power BI performance improvements are attainable

Now you know how to gain insight into Power BI Report memory consumption to avoid problems once the reports are run within the Power BI Service. Our team at DesignMind has years of experience designing, developing, and architecting Power BI solutions for organizations, both large and small. You can see more tips from our Power BI experts here.

Tomas Santandreu Polanco is a Senior BI Consultant at DesignMind.

The post Are Your Power BI Performance Issues Due To High Memory Consumption? appeared first on DesignMind.

Dynamic reports are important to both report developers and users, as we can greatly reduce the number of reports required. By using Power BI parameters, a single report can be used for multiple purposes. For instance, Power BI provides the following capabilities in the backend data modelling through the use of parameters.

• Changing the data source
• Modifying query filters/values

Changing the data source:

You can create a multivalued parameter per source system, so the same report can be used with Dev/QA/Prod server names or SharePoint locations. All the M queries can then be modified by pointing the “Source” property to respective server parameter.

The developer develops the reports on the Dev server. After publishing the reports to workspace in the Power BI service the reports can be pointed to QA or Production via the parameter. This way developers do not need to access production data during reports development and thus reduce the workload on production servers.

Modifying query filters/values:

This can be implemented when there is a use case of limiting the data set to certain date or value. Start and end date parameters can be created in the model. A custom filter can then be created in the table for the field for which the dates need to be filtered.

Example: Connection string parameters can be created and modified in Power BI as follows:
1. Connect to a data source and import the required table from the source system.
2. Create a parameter, name it “ConnectStr”, and provide multiple server names as per your environment.
3. Modify the M query of the table to point the source name to the parameter created.

4. Publish the report to the Power BI service from the Power BI desktop.
5. Once you publish the report to the service, go to dataset and click on the ellipsis to see the properties of the dataset.

6. In the dataset properties, click on parameters and type in the source server that the report should point to and hit apply. 

a. Please note that the list of values defined in Power BI Desktop will not be listed here. The person publishing the report needs to enter the connection string manually.
b. This is a Power BI Service limitation.

Conclusion: the report can be pointed to the appropriate source system without the need to re-publish for each environment!

Limitation of parameters in PBI:
• Reports can only be published from Power BI desktop, other publishing processes, such as SharePoint/One Drive will not work.
• If you are using the RestAPI to publish your reports, you can manage parameters in the Power BI Service.
• Even though the parameters are multivalued in Power BI desktop, once you publish to the Power BI service, the value of the parameter needs to be typed in by the developer.
• Power BI Parameters will only work in import mode, irrespective of data source type.
• To modify the parameters in the service, the developer will need admin rights to the gateway on which the source system resides.

Power BI Parameters save time for report developers and users. And the above example shows how easy it can be to develop the dynamic reports that will get your users excited about their improved ability to understand their data.

Sarada Karlaputi is a Senior BI Consultant at DesignMind in San Francisco. She has been delivering powerful, complex Power BI, Analysis Services, and other database solutions to many of DesignMind’s key clients. You can learn more about editing parameter settings in the Power BI service here.  And you can read more articles by the DesignMind Power BI team here.

The post Power BI Parameters – How to Use & Update Parameters in the Power BI Service appeared first on DesignMind.