Creating an SQL Azure Database for Metadata

Some of the steps in this post will require ADALSQL.DLL this can be found in SQL Management Studio 2016, or Data Tools for Visual 2015; and .NET framework 4.6 or later.

Setup the Database Server and Metadata Database

On your Azure Portal (portal.azure.com) you’ll need to create a new Database, and if you do not yet have a Server a new Sever as well.  This can all be completed from the SQL Database Blade.

New - > Data + Storage -> SQL Database

For several steps you’ll need to sure ensure that it is a V12 Server, and you will want to allow azure services to access the server.

Add Client IP Address to Firewall

You will need to at least add your client IP Address, as well as any other well known IP Address ranges into the Firewall Settings.

Add Client IP to SQL Firewall

Configure Azure Active Directory Authentication

For a database that is designed to be shared across many users, the best solution will be to integrate this with your Azure Active Directory, using Azure Active Directory Groups. I will assume you already have an Azure Active Directory Instance setup and running.

Setup the Required Security Groups

Using Security groups is the preferred method for controlling access, this is especially true in Enterprise Scenarios; with Azure Active Directory Premium we can use Dynamic groups for this as well which can keep maintenance very low once the business rules are agreed on.

Create SQL Administrator Group

First, you will want to create an Azure Active Directory Security Group to hold the SQL Server Admins for the server; I am going to name it “a-ca-sql-admins”.

Add the users who will require administrator rights to the SQL Servers to this group, most likely your admin account.

Create an SQL Users Group for the Database

Secondly, you will need a group for the users of the metadata.  This will hold all of the users who are approved to access this database. I will name it “a-ca-sql-metadata-users”.

Creating user groups that have functions for each database will make it easier to maintain in the future, if someone outside the sql admin team was going maintain the dimensions on this database you could also create “a-ca-sql-metadata-readers” and “a-ca-sql-metadata-writers”.

Assign the Azure Active Directory Administrator

Open the SQL Server Blade from the new Azure Portal and on the settings menu you will find an option to assign the Active Directory admin.  In our case this will be a security group.  Note that MSN and other non-integrated domains cannot be the assigned admin.

Add Azure SQL Admin.PNG

Create the user in the metadata Database

Open a connection to the Server using one of the accounts in the Azure Active Directory Administrator group defined above.  In this case, because I am not using Federation I am using Active Directory Password Authentication.

SSMS - Connect to Azure Active Directory

When connected Open a New Query run the following command.

CREATE USER [a-ca-sql-metadata-users] FROM EXTERNAL PROVIDER
GO
EXEC sp_addrolemember 'db_datareader', [a-ca-sql-metadata-users]

This will create the new user into the metadata database and then assign the db_datareader role, in this case, due to the nature of the information we want everyone to be able to read everything.

You would not do this for a database with internal data and instead, would create a unique role set for the tables required.

Summary

The steps above are a straightforward way to create a central database that can use various utility tables to use across various reporting platforms, this will reduce the duplication of functions and allow for improvements in data models to be quickly shared.

Additional Information and Resources

If you’re looking for in the in-depth steps you can find them here:

https://azure.microsoft.com/en-us/documentation/articles/sql-database-aad-authentication/

Additionally, there is a helpful video on Channel9 https://channel9.msdn.com/Shows/Data-Exposed/Azure-Active-Directory-Authentication-for-SQL-Database-V12

Building a Date Table for Power BI

A very common requirement in data analysis is working with Dates, a key tool for handling this is a Date table as this centralizes the concept of dates for your organization and allows for some complex filtering and display within your reports.

There are a couple of steps for building a date table and once completed we will have a highly dynamic range of dates for PowerBI.

Outline

  • Identify the Date Ranges
    • Pick a Fixed Starting Date
    • Build a Dynamic End Date
  • Creating the Date Table
    • Add a New Table
    • Add the First Column
    • Basic Date Columns
    • Date Labels
    • Current Date Options

Identify The Date Ranges

Before we start up PowerBI we need to work out the range of dates that make sense for our report, ideally, we want to create a range that is as large as we need to capture every date, but still as small as possible to keep performance high.

Adding all the dates for a hundred years sounds good at first but the performance quickly goes down hill as we add calculated columns on the table.

Pick a Fixed Starting Date

There are two main parts to figure out, the first is the date of the year that you want to start your date table on.  Most commonly this is the start of the Fiscal Year for your organization; if you’re not sure what this is check with the Accounting Department they should be able to tell you, most often this will be Jan 1.

The second part is to figure out what year to start the date table on, this will very a lot with what the report is trying to accomplish, and normally will be back at least 1 year if not a few more.

For this demonstration, I’ll be using 1 Jan 2012.

DATE(2012,1,1)

Using the DATE formula is the best way to go as this will avoid a number of issues that can occur with local date formats that can come up from using a date string instead and changing computers.

Building a Dynamic End Date

We can create a Dynamic End Date that will grow as we advance in time, this is because we can pass a function to the End Date Parameter of the CALANDER function.

In nearly all cases just adding to the end of the next year will be enough for most reporting needs, if there are cases where you need more then the current and next year you can easily just add a +2 (or more) instead of a +1.

In most cases, you will want to use the Day before the Start of the Fiscal Year or the Fiscal Year End, again your Accounting Department can help you with this if you are not sure, in most cases, this will be December 31.

In this case, I will use December 31 as the year end

DATE(YEAR(TODAY())+1,12,31)

If your Fiscal Year end is February you can run into some challenges with leap years and hard-coding of dates; we can use the EOMONTH function to handle this.

DATE(YEAR(TODAY())+1,12,EOMONTH(DATE(YEAR(TODAY())+1,12,1),0))

Create the Date Table

Now that we have established the parameters of the how long the date table will be created for we can start PowerBI and add it to our model.

Add a New Table

Open a New Power BI Model, and switch to the model editor and select New Table

New Table

In the Formula, Bar adds in the details for the dates we identified above.

Date = CALENDAR(DATE(2012,1,1),DATE(YEAR(TODAY())+1,12,31))

The CALENDAR function is specifically designed to create a table like this and takes the Start and End date we outlined above.  Adding the Dynamic Values will allow it to expand dynamically.

Create Table

This will populate the Date table which we will use for all the rest of the future calculations and because of the Dynamic End Date. You should also switch the Data Type to Date instead of the default Date/Time, and the Format to one you like (I prefer yyyy-MM-DD).

Now we can build up a range of helpful date formulas, many of them are based on simple formatting and make reporting dates a snap.

Adding a new Column

New Column

The Syntax for a formula in DAX is “Column Name = Expression to Evaluate” this makes adding columns in PowerBI quick and painless.  The part after the = sign will work if you are using Excel to build the Date table as well and you’ll need to rename each column.

Basic Date Functions

These are the basic functions for formatting dates, and ordering information.

Year = FORMAT([Date],"YYYY")
Month = FORMAT([Date],"MM")
Day = FORMAT([Date],"DD")
DayOrder = FORMAT([Date],"W - DDD")
Weekday = FORMAT([Date],"W")
Week = FORMAT([Date],"WW")
Quarter = FORMAT([Date],"Q")
YearMonth = FORMAT([Date],"YYYY-MM")
MonthSort = FORMAT([Date],"mm-MMM")
DateKey = FORMAT([Date],"yyyymmdd")

Date Name Functions

It is helpful for a number of report labels to have access to the long and short versions of the various date aspects.

WeekdayNameShort = FORMAT([Date],"DDD")
WeekdayNameFull = FORMAT([Date],"DDDD")
QuarterName = "Q" & FORMAT([Date],"Q")
MonthNameShort = FORMAT([Date],"MMM")
MonthNameFull = FORMAT([Date],"MMMM")

Current Date Functions

These are very helpful for a wide range of functions of current reporting and other dynamic scenarios. These are returned as boolean (true/false) values, so once you create them you’ll want to change the data type to true/false.

IsCurrentDay = IF([Date]=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),1,0)
IsCurrentWeek = IF(FORMAT([Date],"YYYY-WW")=FORMAT(TODAY(),"YYYY-WW"),1,0)
IsCurrentMonth = IF(DATE(YEAR([Date]),MONTH([Date]),1)=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(1)),1,0)
IsCurrentYear = IF(YEAR([Date])=YEAR(TODAY()),1,0)

There are a number of areas to extend and add more functions if they are required for your reports such as IsYesterday, IsTomorrow, IsLastWeek, IsNextWeek.

Summary

The steps above will create a simple but powerful date table that will last the test of time and not need to be updated in the future, which is very important once a model gets out in the wild or handed off to someone else.

There is a lot more we can do with the date table which I’ll cover in future posts, such as adding Hierarchies for reporting, adding more custom functions.

I hope this helps your reporting efforts, let me know if there is anything I missed in the comments!

Cloud Network Diagrams

As more and more objects move into the cloud the ability to build clear network diagrams becomes more important, today I found the Visio stencils and general use PNG for the cloud objects to build Azure (and other cloud) network diagrams.

Microsoft Azure, Cloud and Enterprise Symbol / Icon Set – Visio stencil, PowerPoint, PNG:

https://www.microsoft.com/en-ca/download/details.aspx?id=41937

To use them in Visio you will want to open the Zip that you download and then copy the Visio Folder into the My Shapes Directory on your PC, they will then show up under the My Shapes Folder in Visio.

Detailed Instructions are here:

https://support.office.com/en-us/article/Import-downloaded-stencils-74bbdce1-4872-4d5b-af4c-e93fa23f7008?ui=en-US&rs=en-US&ad=US&fromAR=1

To use the images in PowerPoint you can place them anywhere on you need them and then drag and drop the images into your presentation.

 

 

Storing Credentials for PowerShell

When working locally on PowerShell scripts I am quite frequently re-running them; and rather then let my self hard-code my credentials into my script which would be the easy (and less secure) thing to do, I did some digging to figure out now to securely store them.

$credpath = "v:\crypt\token.xml"
Get-Credential |  Export-CliXml $credpath

This will prompt for an interactive dialog box where you enter your password, and it is then written as a secure string to an xml file to the path identified.

Now this can easily be used in a number of scripts on the local machine without the need to enter credentials interactively which is helpful for repeated runs and automation scenarios as well where there might not always be an interactive UI.

$credpath = "v:\crypt\token.xml"
$cred = import-clixml -path $credpath

This file can only be used by the user and the machine where it was created; you should keep the file somewhere secure like an encrypted USB Drive or VeraCrypt partition.

If you happen to use two user accounts or two machines you can test this by running this script that will display your password on the user account and machine where the credential was created, and fail on an account or machine where it was not.  Note don’t do this in front of an audience.

$credpath = "v:\crypt\token.xml"

$cred = import-clixml -path $credpath
write-host $cred.GetNetworkCredential().password

I have found this a very helpful way to cut down my runtime when building out powershell scripts as well as prevent accidently committing or sharing my credentials with a co-worker.

 

Getting started in the Cloud

IT has gone under a wide range of technology shifts over the past several years, as storage prices have continued to drop, vitalization hanging the nature of server rooms, broadband and mobility providing internet connections all the time.

These technologies have come together and are the foundation of what we call the cloud, massive storage, compute and practically infinite global scale. In many ways it is similar to the mainframe computers that dominated the 70s and 80s before the desktop computer revolution.

Mastering new technologies has always been a critical part of a successful IT career, but the pace of change technology has never been faster, Microsoft has set up a cloud essentials subscription for IT Pro’s to have access to try these new technologies.

https://www.itprocloudessentials.com/

This is a good hub to get familiar with the various parts of the Azure Stack and how to work with it in a familiar application language.

Microsoft is also previewing a new platform to distribute additional documentation to developers in an easier to browse fashion.  It is currently previewing content for the Enterprise Mobility Suite that is part of the Office 365 offering.

https://docs.microsoft.com/