Thoughts on VR Conferences

I just attended the HR Global Summit, which is the first conference I have attended since the pandemic was announced. It was hosted in a Virtual Reality environment, and it was a very eye-opening experience.

I am not a stranger to virtual worlds, I like many started on BBSs, MUDs, and other text-based adventures before taking the initial steps into the first 3D worlds, I vaguely remember wandering around Meridian 59 when it was initially released in 1996, and then ultimately ended up at MMORPG’s in Azeroth making some of my best friends while playing World of Warcraft.

Virtual worlds and the power to create strong lasting friendships in the digital domain is something I have always known is possible, and strongly believed in that power for virtual teams and that it was only a matter of time before it made the jump over to the business world. Matt Mullenweg’s discussions about how they make it work at Automattic to build WordPress and all the other products is one of the many compelling examples of teams that have made it work.

Even as a true believer I was impressed by how connected to the experience I felt, in many cases, it was the little things that I do not normally notice in their absence were back.

Sound – The natural behavior of sound and getting the context clues of where people are around me is very powerful, hearing a discussion grow louder as I move closer to it transported me to the setting, and the feeling of a conference.

My Avatar – Being able to do some simple customizations to get an Avatar that I thought represented me was a fun process, but also not staring at myself in my video chat window was a world of difference as I didn’t have to worry about that.

Other Avatars – Seeing a crowd of other Avatars moving around, and interacting with space, and watching in the audience gave me a feel of being in the room again, which is very different then a chat commenting.

Technical Issues – Maybe strangely one of the pieces that made it feel like any other conference I have been to, was that occasional bout with technology. Speakers had a few connection issues, sound cutting out, and then the inevitable microphone issues with audience members asking questions. These are a staple of many of the smaller conference room settings I have been in, and something about that made it feel personal.

Where do I think we go from here?

Virtual reality is something that anyone planning an event should consider, I don’t think it will replace steaming events or in-person events (when those are safe again), nor do I think it should. Adding a new medium for connection is great as it allows engagement with a new audience that was not being well-served before, VR is still the domain of an early adopter.

My experience was using the Desktop Client, with a comfortable set of noise-canceling stereo headphones, which should be accessible to anyone. I am excited to see how it can improve with a VR Headset, and the added feedback and reduction of distractions will impact my experience.

Advertisement

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.

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.

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.

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

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.

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

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.