Tag: Dataviz

Power BI, Power BI Desktop, SQL Server

Power BI Desktop Query Parameters, Part 1, Introduction

Power BI Query Parameters

One of the coolest features added to the April 2016 release of Power BI Desktop is “Query Parameters”. With Query Parameters we can now create parameters in Power BI Desktop and use them in various cases. For instance, we can now define a query referencing a parameter to retrieve different datasets. Or we can reference parameters via Filter Rows. Generally speaking we can reference parameters via:

  • Data Source
  • Filter Rows
  • Keep Rows
  • Remove Rows
  • Replace Rows

In addition, parameters can be loaded to the Data Model so that we can reference them from measures, calculated columns, calculated tables and report elements.

In “Power BI Desktop Query Parameters” series of articles I show you how to use Query Parameters in different scenarios.

Scenarios

In this article I’ll show you some use cases of Query Parameters based on some scenarios as below:

  1. Parameterising a Data Source
  2. Using Query Parameters in Filter Rows

You’ll learn more about Query Parameters in the next articles “Power BI Desktop Query Parameters, Part 2, SQL Server Dynamic Data Masking Use Case” and “Power BI Query Parameters, Part 3, List Output“

Requirements

You’ll require to meet the following requirements to be able to follow this post:

  1. The latest version of Power BI Desktop (Version: 2.34.4372.322 64-bit (April 2016) or later)

Note: As Dynamic Data Masking (DDM) is a new feature of SQL Server 2016 and it is not available in the previous versions of SQL Server you need to install the latest version of SQL Server 2016. So you will need SQL Server 2016 and Adventure Works CTP3 only if you want to use Query Parameters on top of Dynamic Data Masking (DDM).

Scenario 1: Parameterising a Data Source

Parameterising a Data Source could be used in many different use cases. From connecting to different data sources defined in Query Parameters to load different combinations of columns. To make it more clear I break down the scenario to some more specific use cases.

Use Case 1: Parameterising Data Source to Connect to Different Servers and Different Databases

Suppose you have different customers using the same database schema. But, the databases hosted in different instances of SQL Server and also the database names are different. With Query Parameters we can easily switch between different data sources then publish the reports to each customers’ Power BI Service.

  • Open Power BI Desktop
  • Click Get Data
  • Select “Blank Query” from “Other” then click “Connect”Power BI Desktop Create Blank Query
  • In Query Editor window click “Manage Parameters” from the ribbon

Continue reading “Power BI Desktop Query Parameters, Part 1, Introduction” →

Power BI, Power BI Desktop, Power Query, Uncategorized

Power BI and Google Maps API (Address Lookup)

In this post I explain how to use Google Maps APIs to retrieve useful information out of Google Maps. The use case scenario could be getting address, postal code, etc. from existing latitude and longitude values. The data could be generated by any sort of GPS tracking device like your Garmin cycling GPS computer, your Fitbit watch etc. I know you can load your GPS tracking data into athletic social networks to analyse your activities. But, if you want to do some more specific data analytics like in which area of the city you created more power during your cycling activities then those websites might not give you what you want for free.

For instance, you can export your device data to CSV then import and append all CSV files into a Power BI model and create amazing analytical reports. How to import your CSV files into a Power BI model is out of scope of this article so I leave it to you for any further investigations.

GPS tracking devices are creating lots of data including geographic coordinates which can be easily used in Power BI. You can simply put latitude and longitude on a Map visualisation and you’re good to go.

Power BI Map using Coordinates

You can also concatenate the latitude and longitude data and use it as Location in your Map visualisation.

Power BI Map using Location

This can be done from Query Editor in M language.

Creating Location from Latitude and Longitude in Power BI

But, in some cases you need some more geo-information like Country, City, Post Code and Street Address in a table as well. Or you might want to use postal code in a slicer. In this article I show you how to get all of these information out of Google Maps by passing existing coordinates to Google Maps geocoding API.

Continue reading “Power BI and Google Maps API (Address Lookup)” →

DAX, Power BI, Power BI Desktop

Role Playing Dimensions in Power BI

In this post I want to explain how to handle role playing dimensions in Power BI. I wrote an article awhile ago regarding role playing dimensions in SSAS Tabular which is valid for Power BI Desktop.

To recap, in the role playing dimensions in SSAS Tabular article I explained three different solutions:

  1. Importing role playing dimensions several times into the model
  2. Creating database views in the source side (in case your source is a from of RDBMS like SQL Server, Oracle etc…) then import the data into the model
  3. Keep the inactive relationships in the model and create several measures to take care of different roles using USERELATIONSHIP functions in DAX

In this post I show you alternative ways for the first two solutions to handle role playing dimensions without importing data several times into the Power BI model. You also don’t have to create database views on your source database. I show you how to manage this in both DirectQuery and Import modes when connecting Power BI Desktop to a SQL Server database. I explain the third option in another post.

I used AdventureWorksDW, but, you can use any other versions of AdventureWorksDW database or you can mimic the process to your own model.

Note: If you are designing a star schema for your data warehouse you can easily create a Date dimension as explained here.

The idea is to manage role playing dimensions in Power BI Desktop itself in the easiest way possible.

Role Playing Dimensions in Import Mode

  • Open Power BI Desktop
  • Get data
  • Select “SQL Server”
  • Enter the server and database names then click OK

Power BI SQL Server Connection

  • Select DimDate and FactInternetSales from the list then click “Load”
  • “Import” mode is selected by default. Click OK

Power BI Connection Settings

Continue reading “Role Playing Dimensions in Power BI” →

Azure, Azure SQL Data Warehouse, Pass Business Intelligence Virtual Chapter, Power BI, Power BI Desktop, Power BI Service, Presentations

Webinar Materials: Visualising Azure SQL DW with Power BI

Power BI Azure SQL DW PassIn the previous post I announced that I will speak in “Visualising Your Azure SQL Data Warehouse with Power BI” webinar on 23 Jan 2016. The webinar host was Pass Business Intelligence Virtual Chapter. It was such an amazing experience for me to speak in the webinar and I would like to thank all 105 attendees. The attendees showed their enthusiasm by asking lots of questions during the webinar.

In this webinar I demonstrated:

  • How to install Azure SQL DW in Azure Portal
  • How to configure firewall settings from Azure Portal and SQL Server Management Studio (SSMS) 2016
  • How to connect directly from Azure SQL DW to Power BI Service and the other way around
  • How to visualise you Azure SQL DW data warehouse data with Power BI Desktop (both Data Import and DirectQuery scenarios)
  • Comparing the features of different scenarios that helps you finding the best for your use cases

and much more…

You can see and download the session materials as follows.

Session Materials

Watch Visualising Your Azure SQL Data Warehouse with Power BI on YouTube

Continue reading “Webinar Materials: Visualising Azure SQL DW with Power BI” →