Why you should use “Tune Model Hyper parameters” module in Azure Machine Learning?

A machine learning algorithm in Azure ML has few parameter settings that you can set — in this post, we will talk about 1) why you should NOT stick with default settings 2) How can “Tune model hyperparameters” module help you do so?

So first up, why you should not be using the default setting? The parameter settings that are applied to a model impacts the accuracy (or call it predictive power) of the algorithm…sometimes it may be significant and sometimes not but either case, you won’t know until you try changing the default values. In other words, by tuning the hyperparameters you could significantly boost your model’s performance!

Now, how do you go about setting the parameters such that it gives optimal performance? Let’s say that there are 3 parameters then that is 27 different combinations! How do you know which one is the best? You could dig a little deeper into the mechanism of how algorithms works and narrow down your list but that would still take some time. So, there should be a better way, right? Luckily there is: This where “Tune Model Hyperparameters” comes in! You can use it with any algorithm in Azure ML. This module helps you tune the hyper-parameters. There’s some things that you still need do like decide: Do you want the module to just try random n combinations? OR Do you want the module to try all combinations (fyi: this is compute-intensive operation!)? … AND you will have to decide what are you are optimizing for? Depending on the algorithm it would let you pick the evaluation metric that you want to optimize.

Now, there are some good articles already written that walks you through how to get about doing this so I am going to share these links with you:

I want to conclude by sharing few tips:

Notes from the field:

Running the “Entire Grid” mode will slow down the training time for the model. You might want to make sure that it’s acceptable and the cost (longer training time) to benefit (better accuracy) is worth it for your case
When you are comparing algorithms to decide the best one that fits your problem, instead of comparing “model with default parameter settings” with each other, try comparing the “model with tuned hyper-parameters”

Every Data Analyst Needs to check out this FREE excel add-in: Power Query!

Power Query is amazing! It takes the data analysis capabilities of Excel to whole new level! In this post, I am going to share three reasons:

1. it enables repeatable mash-up of data!

Have you every had to do your data analysis tasks repeatedly on the data with same structure? Do you get “new” data every other week and need to go through the same data transformation workflow to get to the data that you need?

What’s the solution? Well, you can look at MACRO’s! Or you can request your IT department to create a Business Intelligence platform. However, what if you need to modify your data mashup workflow then these solutions don’t look great, do they now?

Don’t worry! Power Query is here!

It enables repeatable mashup of data like you might have never seen before! You need to try it to believe.

It’s very easy to input new data to Power Query and it enables you to retrieve final output based on new data using a “refresh” feature.

Each data-mashup is recorded as steps which you can go back and edit if you need to.

2. It’s super-flexible!

Any data mashup performed using Power Query is expressed using its formula language called “M”. You can edit the code if you need to and as you can imagine such a platform enables much-needed flexibility for the analyst’s.

3. It has awesome advance features!

Do you want to Merge data? How about Join? Are you tired with VLOOKUP’s! Don’t worry! it’s super easy with Power Query! Here’s a post: Join Excel Tables in Power Query

How about Pivot or Unpivot? Done! Check this out: Unpivot excel data using Power Query

How about searching for online & open data sets? Done!

How about connecting to data sources that “Data” section of Excel doesn’t support yet? (Example: Facebook) – DONE! Power Query makes that happen for you.

And That’s not a complete list!

Plus you can unlock the “Power” (pun intended) of Power Query by using it with other tools in Power BI Stack. (Power Pivot, Power View, etc…) OR you can use the your final output from Power Query with other tools too! After all it’s an excel file.

Action-Item!

If you haven’t already then check out Power Query! it’s free and works with Excel 2010 and above.

Author: Paras Doshi

News from PASS Summit’14 for Business Analytics Professionals: #sqlpass #summit14

This post is a quick summary for all Business Analytics related updates that I saw at PASS Summit’14:

1. Theme of the Keynote(s)/Session(s) seemed to be around educating the community about the benefits of the NEW(er) tools. I saw demos/material for cloud-based tools like SQL databases, Azure stream analytics, Azure DocumentDB, AzureHDInsight & Azure Machine learning. The core message was pretty clear: A data professional does two things – 1) Guards data OR 2) helps to generate Insights from Data – And they will need to keep up-to-date on the new tools to future-proof their career.

2. Coming soon: Power BI will be able to connect to on-premise SSAS data sources (multi-dim & tabular).

3. Coming soon: A better experience to create Power BI dashboards.

Sneak Peek at live Power BI Dashboard (coming soon) #sqlpass #summit14 pic.twitter.com/C8A5IB6ut5
— Eric Flamm (@eflamm) November 5, 2014

Read more about Power BI updates here: http://www.jenunderwood.com/2014/11/05/pass-summit-2014-bi-news/

4. Azure Machine Learning adds a free-tier! You won’t need a credit-card/subscription to sign up for this.

5. I also saw sessions proposing new way of thinking about an architecture for “Self Service BI” and “Big Data” which might be worth following because since these are newer tools, it’s definitely worth considering an architecture that’s designed to make the most of the investments in these new tools. That’s it & I’ll leave you with a quote from James Phillips from Day 1’s keynote:

Data on its own is just a bucket of potential. People convert data to opportunities – James #sqlpass #summit14
— Paras Doshi (@paras_doshi) November 5, 2014

Quick note on evolution of Business Intelligence & Microsoft’s vision for BI space:

I attended “Enabling Familiar, Powerful Business Intelligence“ hosted by PASS BA VC last week & I got to listen to Microsoft where they shared their vision for the BI space, so I thought of posting this quick note about it:

“Corporate BI” has been around for may years. This space has established players like Microsoft, SAP, IBM, Oracle. But in recent times, “Self Service BI” space has been gaining momentum. Players like Qlikview & Tableau that lead the Self Service BI space have been ranked as leaders in the Gartner 2014 magic quadrant. Microsoft has also been making serious advancements in this space since last few years & with their latest offering called “Power BI” they have shown that they putting their bets on Self Service BI space. So, as Microsoft said in the event, they view themselves as the only player that offers a full suite of Corporate BI as well as Self Service BI:

you can watch the recorded session here: http://www.youtube.com/watch?v=0yKhxSPlykg

Questions Power Users Ask about Excel: #2 of N

In this series, I intend to document common questions asked by Power users about Excel connected to SSAS cubes (or data warehouse) after they go beyond the basic stage of understanding & using Row labels, column labels, report filter & values in Pivot Tables. This post is #2 of N:

How to add calculated measures (Excel 2010)?

Situation:

Power users in your organization/company are developing Excel 2010 reports against OLAP Cubes. They want to add their own calculations for analysis.

BUT Excel 2010 does not allow end-users to add their own private MDX calculations.

Solution:

A Free Community Excel add-in helps in this case. It’s called “OLAP Pivot Table Extensions”. Here are the steps to download, Install and use it:

1) Check Excel Version (32-bit/64-Bit)

Open Excel > File > Help> About Microsoft Excel

2) Download OLAP Pivot Table Extensions and Install it

Download URL: http://olappivottableextend.codeplex.com/ > Navigate to Downloads Tab & install the right version based on your local excel version.

In my case, I had 64-bit excel so I’ll download the 64-bit version of OLAP Pivot Table extensions.

Close Excel and Install add-in

3) Add simple measure

Open Excel.

Connect to Cube.

Let’s add a simple measure that calculates the difference between two measures.

So I created a simple Pivot Table that looks like:

Now let’s add a calculated measure:

Select the Pivot Table > Right Click > OLAP Pivot Table Extensions

On the next screen, please enter the name Difference. and the simple formula:

[Measures].[Meausre1]-[Measures].[Measure2]

(please replace measure1 & measure2 with the measure names from your cube)

And click on Add to Pivot Table

You can now see that the calculated measure Difference got added to the Pivot Table!

Note

1) OLAP Pivot Table extensions is not supported by Microsoft. It’s a community software.

2) To maintain the single version of truth (after all that’s why you create cubes/Data-warehouse’s!), it’s recommended that calculated measures that end users want in the cube.

Conclusion

In this post we saw a very common question “How to add calculated columns” asked by Power users about excel while they analyze data from SSAS cube.

Previous Article: http://parasdoshi.com/2013/12/10/questions-power-users-ask-about-excel-1-of-n/

How to embed or integrate Power View reports into SharePoint pages?

Why do you want to do that?

One of the common tactic that you can consider to drive adoption of a Business Intelligence system is to integrate/embed the BI reports to the APPS/SITE that the users are already using. Don’t make your users come to you, go to them! As a part of that, I figured out a way to integrate/embed Power View in a Site that was used by existing user base.

You can integrate/embed Power View reports in SharePoint web Parts. Here’s How:

Image Credit & For step by step tutorial, please refer to: Integrate Power View with SharePoint using web parts

Note:

Environment: SQL Server 2012 in SharePoint Integrated mode w/ Power View Activated for the site. Also, SharePoint Enterprise 2010.

PASS BA VC EVENT: Integrating SSRS with SharePoint on Nov 25th, Monday:

Topic: Integrating SSRS with SharePoint

Register TODAY! URL: bit.ly/PASSBAVC

Speaker: Kevin Goff

Topic Abstract:

Have you ever seen a SharePoint site that integrates reports from SSRS, and wonder how all the pieces fit? If so, this session is for you. I’ll cover the necessary integration/configuration steps for integrating SSRS 2008R2/SSRS 2012 with SharePoint 2010/2013, as well as deploying reports to a SharePoint location. Because different versions of SSRS integrate differently with the available versions of SharePoint, I’ll provide a feature matrix for specific version integrations. I’ll also show how to integrate SSRS reports into SharePoint/PerformancePoint Services dashboard pages, as well as how to schedule reports for delivery to SharePoint document libraries. You’ll also see the improvements from Microsoft that make reporting against SharePoint lists much easier than before. At the end, I’ll show 2 new features in SSRS: Data Alerts in SharePoint, along with the new SSRS data visualization tool, Power View

I hope to see you there!
Paras | VP of Marketing | PASS BA VC

Power Pivot DAX: Difference between two DATE values

Requirement:

Take difference between two data values.

Example:

EndDate: 11/20/2013

StartDate: 11/14/2013

DateDifference: 6

Let’s start writing some DAX!

Ok, seems simple, right? Try creating a measure DateDifference:=[EndDate]-[StartDate]

Did that work? NO? Does it return date?

Don’t worry, Here’s the solution. Try creating following DAX Measure:

[code language=”sql”]
DateDifference:=1.0*([EndDate]-[StartDate])
[/code]

That’s about it for this post. Here are some related Posts:
Calculate the difference between two dates in DAX
Q: How can I calculate difference between two dates in DAX (seconds, minutes, hours, days and months)
NETWORKDAYS() Equivalent in PowerPivot?

Power Pivot: Casting DateTime to Date in SQL Server source query

DateTime columns can be tricky for analysis purposes. They don’t work well with Pivot Tables because of the time part, each value seems unique to the Pivot Table & it also creates problems while creating relationships with Date Dimensions. And so, It’s a common need to convert them to just Date before analyzing data & also a common need to create a relationship between the Date (and not DateTime) with Date Dimension Table.

So if it’s possible, I try to do the data type conversion in the source system query. If your source system is SQL Server, you could use this piece of code:

[code language=”sql”]
select [your-fields],cast([DateTime_Col] as date) as Date_Col from TableName
[/code]

Doing the data type conversion upfront in the source system query is a good thing to do. And I hope this is helpful.

PASS Business Analytics VC’s Online Event: “Power BI Info Management and Data Stewardship”

Power BI is an exciting new technology in the business analytics space from Microsoft. I’ve played with its current preview version & attended couple of sessions on Power BI at PASS Summit 2013. Based on my first impression, I noted down Problems that Power BI solves. Note that as of today, it’s in preview & so information around cost is not availale yet but I try to learn and understand as much as I can Today about how Power BI is going to help business users & power users in the future. As a part of that, I’m attending Business Analytics VC’s session on “Power BI Info Management and Data Stewardship” by Matthew Roche & Ofer Ashkenazi on Nov 7th 12 PM EST.

Topic: Power BI Info Management and Data Stewardship

Date & Time: Nov 7th 12 PM EST

Here’s the Link to register: http://bit.ly/PASSBAVC

Topic Abstract:

“Business intelligence tools continue to improve, letting users shorten their time to insight and take that insight to more devices in more places. But this evolution of BI doesn’t change one fundamental fact of information management: You can’t gain insight from data you can’t access.

In this session, Matthew Roche and Ofer Ashkenazi will introduce the role of the data steward and the self-service information management capabilities included in Power Query and Power BI for Office 365, focusing on how Power BI empowers business users to add value to the organization.”

Closing note:

I recently volunteered at Business Analytics VC as VP of Marketing, so it’s in my interest to spread word about the event but I would not spread word about something unless it gets me personally excited about it! 🙂

I hope to see you at the session and for some reason if you can not make it, we usually record sessions & so you can check out the meeting archives section of the PASS BA VC site after the event.

Insight Extractor – Blog

Paras Doshi's Blog on Analytics, Data Science & Business Intelligence.

Microsoft

Why you should use “Tune Model Hyper parameters” module in Azure Machine Learning?

Like this: