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!