A while ago I wrote a blog post about Azure SQL Data Warehouse and Power BI which I explained how to install a new instance of Azure SQL Data Warehouse and how to visualise your Azure SQL DW data in Power BI. In this post I explain how to query an Azure SQL DW in SSMS and Visual Studio.
An existing instance of an Azure SQL Data Warehouse. If you don’t have an existing one check this out
Querying Azure SQL Data Warehouse from Visual Studio
Prior the latest release of SQL Server Management Studio (SSMS) 2016, the only available tool for querying an Azure SQL Data Warehouse was SQL Server Data Tools (SSDT) for Visual Studio 2013 or 2015. Here is how you can use SSDT 2015 to query an Azure SQL Data Warehouse:
Open SQL Server Data Tools 2015
Click “SQL Object Explorer” from View menu
Click “Add SQL Server”
Enter “Server Name”
If you don’t recall server name then open a web browser and log into Azure portal
Click “SQL databases”
Click any desired Azure SQL Data Warehouse you created before. Make sure the database is “Online”
One of the most powerful features in Power BI and Excel is supporting geospatial visualisations. In Excel we can use Map visualisation in Power View, or use Power Map directly. In Power BI, as you know, there are two built-in visualisations supporting geographic coordinate data, Map and Filled map. They work beautifully if you have enough data supported by Bing Maps. But, there are some issues with Map visualisations in both Power BI and Excel. In this post I address some of the issues I faced myself and I’ll provide the solutions for the issues. As “Filled Map” and “Map” visualisations in Power BI are very similar my focus in this post would be on “Map” visualisation. My intention is not explaining Power View and Power Map that much so my focus in this article would be on Power BI more than the other two.
Requirements
To experiment everything I explain in this post you need to have:
The new SQL Server sample, WideWorldImportersDW (WWI). You can download it here
The latest version on Power BI Desktop (current version is 2.35.4399.381 64-bit (May 2016))
Select Fact.Sales and Dimension.City then load data
Map Issues In Power BI
Wrong Cities in Power BI
Expand the “Dimension City” table
Select “City” column then change its Data Category to City (Data Category is on “Modeling” tab from the ribbon)
Put a Map visual into the page
Put “City” on Location
Put “Total Excluding Tax” on Size
As you see sales distributed across different countries, but, this is not quiet right.
Put a slicer on the page then put “Country” on the slicer
Click “United States” to filter the Map
Oops! This is not quiet right. What happened is that Bing Map Engine gets confused with the city names so that it shows a city with the same name outside of the US, just like New Plymouth which a city in New Zealand, but, the New Plymouth we have in our data source is the New Plymouth from Idaho in the US.
Update1: As per April 2018, the process has slightly changes since I wrote this blog post. Now you can find it under “Downloads” tab, under “Servers” section. (Thanks to “David Shannon” for pointing this out.)
Update2: Click here to utilise the SQL Server installer. By clicking the link you’ll download an executable file “SQLServer2016-SSEI-Dev.EXE” produced by Microsoft that gives you three options:
“Basic”: To quickly install basic features like SQL Server Database Engine
“Custom”: You go through SQL Server installation wizard to choose what you want to install
“Download Media”: To download SQL Server setup files and install them later
A while ago Microsoft decided to provide Developer edition of SQL Server 2014 and 2016 for free. So we should be able to download SQL Server 2016 Developer Edition for free legitimately. If you search for it on the Internet you’ll quickly find out that it is NOT that straightforward. In this post you see how to download SQL Server 2016 Developer Edition for free legally.
But, what is the difference between SQL Server Developer Edition and any other editions? Well, generally speaking, the Developer edition has all features of an Enterprise edition, but, it’s not for commercial use. To learn more about different editions of SQL Server 2016 have a look at here. I encourage you to see this datasheet as well.
To be able to download SQL Server 2016 Developer Edition for free you need to have MSDN Subscription or you can join Visual Studio Dev Essentials.
Download SQL Server 2016 Developer Edition using MSDN Subscription:
If you do have an MSDN subscription you can easily download SQL Server 2016 Developer Edition for free. Just click here and download.
Join Visual Studio Dev Essentials and Download SQL Server 2016 Developer Edition:
Joining Visual Studio Dev Essentials is free and simple. While you join Visual Studio Dev Essentials lots of benefits will get unlocked including a free SQL Server Developer Edition.
As I promised in my earlier post, in this article I show you how to leverage your Power BI Desktop model using Query Parameters on top of SQL Server 2016 Dynamic Data Masking (DDM). I also explain very briefly how to enable DDM on DimCustomer table from AdventureWorksDW2016CTP3 database. We will then create a Power BI Desktop model with Query Parameters on top of DimCustomer table. You will also learn how to create a Power BI Template so that you can use it in the future for deployment.
In the previous post I explained how to create dynamic data sources using Query Parameters. You also learnt how to use Query Parameters in Filter Rows. In this post you learn :
Using Query Parameters on top of SQL Server Dynamic Data Masking (DDM)
I’m not going to provide much details about DDM as you can find lots of information here. But, to make you a bit familiar with Dynamic Data Masking I explain it very briefly.
Dynamic Data Masking (DDM)
Dynamic Data Masking (DDM) is a new feature available in SQL Server 2016 and also Azure SQL Database. DDM is basically a way to prevent sensitive data to be exposed to non-privileged users. It is a data protection feature which hides sensitive data in the result set of a query. You can easily enable DDM on an existing table or enable it on a new table you’re creating. Suppose you have two groups of users in your retail database. Sales Persons and Sales Managers. You have a table of customers which in this post it is DimCustomer from AdventureWorksDW2016CTP3. This table contains sensitive data like customers’ email addresses, phone numbers and their residential adders. Based on your company policy, the members of Sales Persons group should NOT be able to see sensitive data, but, they should be able to all other data. On the other hand the members of Sales Managers group can see all customers’ data. To prevent Sales Persons to see sensitive data you can enable Dynamic Data Masking on the sensitive columns on DimCustomer table. In that case when a sales person queries the table he/she will see masked data. For instance he see uXXX@XXX.com rather than user@domain.com.
Create a table with DDM on some columns
It’s easy, just put “MASKED WITH (FUNCTION = ‘Mask_Function’)” in column definition. So it should look like this:
CREATE TABLE Table_Name (ID int IDENTITY PRIMARY KEY, Masked_Column1 varchar(100) MASKED WITH (FUNCTION = ‘Mask_Function’), Masked_Column2 varchar(100) MASKED WITH (FUNCTION = ‘Mask_Function’),
…
)
GO
Alter an existing table and enable DDM on desired columns
As you guessed you have to use “ALTER TABLE” then “ALTER COLUMN”. Your T-SQL should look like:
ALTER TABLE Table_Name ALTER COLUMN Column_Name1 ADD MASKED WITH (FUNCTION = ‘Mask_Function’);
GO
ALTER TABLE Table_Name
ALTER COLUMN Column_Name2 ADD MASKED WITH (FUNCTION = ‘Mask_Function’);
A template is basically a Power BI file that represents an instance of a predefined Power BI Desktop which includes all definitions of the Data Model, Reports, Queries and parameters, but, not includes any data. Creating Power BI Templates is a great way to ease the deployment of existing models. Creating templates is very easy, you just click File –> Export –> Power BI Template. We will look at this more in details through this article.
Scenario
You are asked to implement a new level of security on customers’ data (DimCustomer on AdventureWorksDW2016CTP3 database) so that just privileged users can see the customers’ email, phone numbers and residential address. Privileged users are all members of “SalesManager” database role. You are also asked to prevent “SalesPerson” database role to see sensitive data. But, all members of both “SalesManager” and “SalesPerson” database roles can query DimCustomer table. The users should NOT have SQL Server logins.
