Azure Machine Learning: Getting Started

This week, we're going to start talking about Microsoft's new Cloud Data Science offering, Azure Machine Learning.  Let's start with a little terminology.  What we're referring to as Data Science, also known as Predictive Analytics, Data Mining and Machine Learning, is the task of trying to get algorithms to make complex decisions based on data.  It's a huge area that we are super excited about.  For the past decade or so, the only major Microsoft Data Science offering in the Microsoft BI world was Data Mining in SQL Server Analysis Services.

At every SQL conference, there would be a couple of speakers talking about Data Mining in SSAS.  However, there wasn't much usage around the community.  We authored a 31-part blog series on utilizing this functionality within Excel.  This was great for a learning exercise, but didn't have much impact.  We think there are a couple of reasons for this.

First, the tools in SSAS were not nearly as developed as those from other major vendors, like SAS.  Second, we never encountered encountered any businessmen who were ready for the kind of "uncertainty" that comes from predictive modeling.  They wanted to know last month's sales compared to this month's inventory, and so on.  Well, it's starting to look like times are changing.  More and more businessmen seem to be getting more comfortable (and educated) when it comes to Data Science, and this is great for us.

This is where Azure ML comes in.  It's Microsoft's "new kid on the block" and it's far superior to the old SSAS functionality.  For this post, we won't dive into too many specifics.  But, you can immediately tell that we're not in Kansas anymore.

Sample 1 Workflow

For those of you that have used SSIS or some other ETL tool with a GUI, you'll recognize this type of layout.  But we have to say, it definitely looks better than anything we've used before.

So, how do you get started?  It's easy, just go to the Azure Portal and select New -> Intelligence -> Machine Learning.

Create Machine Learning Environment

From here, all you have to do is decide on names and all that boring stuff.  We will comment that Azure Machine Learning and SQL Azure are NOT free.  However, if you're just tinkering around on the weakest machines doing small stuff, you'll probably get by on a couple bucks per month.  There is great news though!  You may be able to get free Azure credits (read: money) on a one-time or recurring basis.  So, check with your IT team to see if your organization has access to these types of subscriptions.

In the coming posts, we're going to start looking through the free samples to see what sort of cool things this tool can do.  Thanks for reading.  We hope you found this informative.

Brad Llewellyn
BI Engineer
Valorem Consulting
@BreakingBI
www.linkedin.com/in/bradllewellyn
llewellyn.wb@gmail.com

Power BI/PowerPivot: Using LOOKUPVALUE() to Emulate Relationships

Today, we're going to talk about a neat function in DAX called LOOKUPVALUE().  This function is pretty aptly named, as it simply returns a value from a different (or possibly the same) table.  Let's start with a very simple data model.

Sales Data

Data Model

We can see that we have two distinct transaction tables, Sales and Marketing, which roll up to a single Customers lookup table.  Pretty much all of these could be accomplished using a different function.  However, we always strive to find the easiest solution to understand and implement. That's where LOOKUPVALUE() shines.  Let's look at some options for looking up values across (or within) tables.

Retrieving a Value from the Same Table

Sometimes, you need to retrieve a value from a specific row in the table you're already in.  This is one of the great use cases for LOOKUPVALUE().  For our scenario, we want to compare the Sales for each row in the Sales table to the Sales for January for that same customer.  Let's see how it's done.

January Sales (Formula)

January Sales (Results)

All we have to do is define the value we're looking for, then a unique set of identifiers to identify where we want to look in the target table.  In this case, we're looking for the same [Customer ID], and a fixed [Month Start Date] of January 1, 2016.

Retrieving a Value From Up the Hierarchy

Now, what if we wanted to retrieve a specific value from the lookup table.  In our case, let's say that we want to pull Customer Type down into the Sales table.  We could use LOOKUPVALUE() to accomplish this as well.  However, there's a far simpler way, the RELATED() function.  The RELATED() function uses Row Context to retrieve a value from up the hierarchy.

Customer Type

The RELATED() function is far simpler than the LOOKUPVALUE() function.  All you have to supply is the field you are looking for.  However, RELATED() only works when you want to retrieve a single value from up the hierarchy and requires an active relationship.

Retrieving a Value From Down the Hierarchy

Now, what if we wanted to pull a value from the transaction table into the lookup table.  Ordinarily, this is done using aggregations and the CALCULATE() function.  However, in some cases, you want to retrieve a specific value with no aggregation.  Let's try pulling the Sales for February up into the Customers table.

February Sales

This time, we need to provide Customers[Customer ID] because we are querying from the Customers table.  The rest remains pretty simple.

Retrieving a Value from an Unrelated Table

We tried our best to find an example of this that works in a useful.  Simply put, this is VERY sticky territory.  DAX is built around utilizing relationships.  Therefore, it can become quite difficult to work without relationships.  To summarize, if you are trying to connect two tables without some type of relationship chain, you may want to reconsider your modelling.  If anyone in the comments can find a good example, let us know!

Hopefully, this post opened up your mind to the possibilities of using LOOKUPVALUE().  It's not a unique function by any means, but it sure is easy and intuitive to use.  That's worth quite a bit in our book.  Thanks for reading.  We hope you found this informative.

Brad Llewellyn
BI Engineer
Valorem Consulting
@BreakingBI
www.linkedin.com/in/bradllewellyn
llewellyn.wb@gmail.com