Month: October 2012

Data Mining Demo for Marketing vertical: How to create a Targeted mailing list?

Standard

Tools I’ll be using for the Demo:

Excel 2010

SQL Server 2012 (specifically SQL Server Analysis Services)

Excel Add-in for Excel.

Sample data-set that comes with the excel add-in

Scenario:

Marketing Department needs to create Targeted Mailing list.

What data do we need?

To create a Targeted mailing list – we’ll need a historical data-set of customer purchase history

What will we do with the data?

Based on the historical data-set, we’ll be able to find “patterns” in the past consumer behavior. E.g. A single male going to college living in Europe is likely to buy a bike. And the using these patterns – we would then classify NEW customers.

Technically, we’ll be using the classification method using the Microsoft’s decision Tree algorithm

(Read the difference between classification and clustering)

Let’s get in action!

STEP 1: Build a Model

Data Mining Tab > click on classify:

data mining in excel example customer classification for maketing maling list 0

Follow the steps:

data mining in excel example customer classification for maketing maling list 1

Select the data:

data mining in excel example customer classification for maketing maling list 2

In this case, since we want to predict the likelihood of buying a bike – our column to analyze is BikeBuyer

 

data mining in excel example customer classification for maketing maling list 0 3

For the Demo, I am going to just leave it default. There are “optimization” steps that you can do but for the demo I am going to keep it super simple

data mining in excel example customer classification for maketing maling list 4

Name the model:

data mining in excel example customer classification for maketing maling list 5

The Model has been created!

data mining in excel example customer classification for maketing maling list 6

STEP 2: Query the MODEL to predict the likelihood of bike purchase of a new customer

data mining in excel example customer classification for maketing maling list 7

Select the model:

data mining in excel example customer classification for maketing maling list 8

Select the data:

data mining in excel example customer classification for maketing maling list 9

Specify the columns that would be used in predicting the likelihood:

data mining in excel example customer classification for maketing maling list 10

Add the column that will have the “predicted value”

 

data mining in excel example customer classification for maketing maling list 11

And example of Data Mining Expressions (DMX):

data mining in excel example customer classification for maketing maling list 12

For the demo, I am just going to add the column to the existing table:

data mining in excel example customer classification for maketing maling list 13

Yay! Here’s our Targeted Mailing list – see the last column:

Screenshot 1

data mining in excel example customer classification for maketing maling list 14

Screenshot 2:

data mining in excel example customer classification for maketing maling list 15

Now what?

Marketers can now send “coupons” to ONLY those people who are most likely to buy a bike! And so that’s how you create a targeted mailing list using the Excel Data Mining add-in.

Related articles

How to Solve: Excel Data Mining add-in disappeared.

Standard

Introduction:

In this blog-post, we’ll see what you can do when the Excel data mining add-in disappears.

Steps:

1. What happened?

So I have installed the Excel Data Mining add-in. 

sql server 2012 data mining excel addin

But I do not see the Data Mining Tab in Excel:

excel sql server data mining tab missing

2) So Now what?

I searched and found this.  and got it working for the software versions (Excel 2010, SQL Server 2012) that I had and so I am documenting it here.

3) Logged in as Administrator > Office button > Options > Add-Ins > Do you see the Data Mining add-in Disabled?

sql server 2012 data mining excel addin disabled excel options

4) Select Disabled Items in the Manage > click GO

excel options enable a disabled item data mining5) Click on the data Mining add-in and enable it. > Click close > Click OK

6) Re-open Excel. Can you see it now? Yes? Yay!

excel sql server data mining tab enabled yay

That’s about it for this post.

Conclusion:

In this blog-post, we saw how to enable the data mining excel add-in.

Related articles

Getting started with HDInsight (a.k.a Microsoft’s Big Data hadoop Platform) on local Windows Machine!

Standard

Recently Microsoft announced HDInsight on Windows server! and so it’s good to get a chance to play with its public preview! Currently there two ways you can run HDInsight: 1) Via Windows Azure 2) On your local Windows machine.

In this blog-post, I would show you step by step to install a HDInsight on a local Windows Machine. For the purpose of this blog-post, I am going to show it on Windows 7 but it also supported on Windows Server 2008 R2.

download hadoop on windows machine hdinsight

Note that the ideal audience for this blog-post would be a developer who wants to kick tires of Hadoop on windows machine to see what it can do! If I had wanted to target it to Hadoop administrators then I would have shown how to do it on Windows Server and also how to manage the Hadoop cluster with system center. But for this blog-post, I am going to target developers so that they can get started playing with Hadoop on a windows machine! With that, here are the steps to install Hadoop (HDInight) on Windows 7:

1) open Web Platform Installer. Download and install it if you haven’t yet.

2) search for Hadoop

install hadoop windows via web platform installer

3) Install it!

4) You should get a message saying that it successfully installed it!

5) Do you see a Microsoft HDInsight Dashboard ICON on your Desktop? Yes? Great! Open it!

windows hadoop big data dashboard6) And here’s the IIS manager showing the site that hosts the above Dashboard. Just wanted to show this to folks who might not see the Dashboard at http://localhost:8085/

IIS windows hadoop local host site port 8085

7) That’s about it for his post. If you want to continue learning, check out the “documentation” link at the bottom on the Hadoop Dashboard which is: http://social.technet.microsoft.com/wiki/contents/articles/6204.hadoop-based-services-for-windows-en-us.aspx

Conclusion:

In this blog-post, we saw how to install HDInsight (Microsoft’s Hadoop) on local windows machine.

Related Articles:

Who on earth is creating “Big data”?

Want to learn about BigData? read Oreilly’s Book “Planning for BigData”

How to Install Microsoft HDInsight Server Hadoop on Windows 8 Professional

Five quick reasons why I use virtual machines on my personal computer:

Standard

I was talking to a friend yesterday about virtual machines.  The topic got started because I had about  4 virtual machines and I had to explain why I had such a setup – why just not “dual-boot” – and so I thought I document the reasons that I gave out. Note that I am using Virtual Machines on my personal computer and this blog post falls into the class of “personal technology” and I’ll not touch upon why businesses use virtual machines. Before we begin, a quick note about what are virtual machines? Think of virtual machines as “software application” that can run “operating systems” in them. Example: You have Windows X on your machine and there’s an application on your machine that says “LinuxOS.xyz” – if you click on it, it would open Linux operating system as an application without leaving your windows machine. This is not technically correct definition but from a personal technology standpoint, all you need to know is that “virtual machines” lets you run operating systems like linux, windows, etc like an application on your main operating system. Here’s my current configuration: “I have windows 7 as my main operating system. and I have a couple of virtual machines running Windows Server 2008 R2, one virtual machine having a windows 7 environment & one machine to kick tires of Hadoop”. with that, here are the reasons that I use virtual machines:

1) I can have multiple flavors of operating systems running as application on top of my “main operating systems”. And I do not have to worry about the hassles of dual/multiple boot.

2) I can COPY a virtual machine and PASTE it on a different machine. Basically share “OS along w/ app installed” with others or open them up using a different computer

3) I can “Save” a state of a virtual machine. For example I can save the state of my virtual machine today and if something happens tomorrow then I can just “restore” it to the previously saved state. Think of it like “system restore”

4) When I am on a virtual machine, it gives me the freedom to play around with “do not touch” and “not recommended” configuration. I can experiment things I want to without worrying about “breaking” my main operation system

5) Do you have software’s on your OS that you installed for one-off purpose and forgot to uninstall it later? I usually install applications that I rarely use on a separate virtual machine. This helps me keep my main operating system cleaner.

Those were the quick five reasons I use virtual machines on my computer, if you want to get started you can check out: Microsoft virtual PC or Oracle’s Virtual Box.

Question: Do you use virtual machines on your personal computer? Yes? What is your “why”?  why not share that in the comments section?

Error in Importing KPI’s to Performance Point 2010 Dashboard designer from SQL Server 2012 Analysis Services MultiDimensional cube

Standard

This blog post is meant to document how I solved an error while trying to import KPI’s to Performance Point 2010 Dashboard Designer from SSAS 2012 Multidimensional cube. The error said “An unexpected error occurred.. details have been logged for your administrator”. Here are the details:

1) few days back, I had solved the error listed here: “An unexpected system error occured…” – While trying to establish a Data Connection in Performance Point 2010 Dashboard Designer –  And after I had solved this error, I got another error and this time it was about importing KPI’s in the Dashboard Designer. Here’s how I was able to solve it:

2) So to view the error I used the SPTraceVIew Tool

3) And via SPTraceVIew, I was able to see the message:
”
An unexpected error occurred.  Error 47205.
Exception details:
System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.AnalysisServices, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
File name: ‘Microsoft.AnalysisServices, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’
at Microsoft.PerformancePoint.Scorecards.Server.ImportExportHelper.GetImportableAsKpis(DataSource asDataSource)
at Microsoft.PerformancePoint.Scorecards.Server.PmServer.GetImportableAsKpis(DataSource dataSource)
”

4) I checked that the Microsoft.AnalyisServices dll was present at c:/windows/assembly

5) What I didn’t realize was that the version number was different. The version that I had on my machine was 11 while the message clearly mentioned that it required 10. Thanks to this tip here: Creating scorecard error

6) So I followed the instructions that was on the MSDN forum thread and installed the AMO with version 10 via “Microsoft SQL Serer 2008 feature pack, April 2009”

7) And after installing the version 10 of the AMO, I was able to import KPI’s from SSAS 2012 multidimensional cube!

Related articles

PowerPivot: Quick Tip regarding Copy-Pasting Tables in PowerPivot 2010

Standard

Power Pivot Paras Doshi Microsoft Business Intelligence Excel 2010I was working on couple of PowerPivot models recently and both models needed a Date Table. So here’s what I did:

1. Imported the filtered version of the Date Table (DateStream) from Azure Data Market. Added Calculated Columns to the table. Let me call this Table1

2. I copied entire Table1 and pasted it to an another PowerPivot model – let’s call that Table2

 

After this:

And I went to happily create data models! Later, I while testing the model (that had Table2), I realized that it had lost the “formulas” – in other words when I copy-pasted from Table1 to Table2 in PowerPivot – it did not copy-paste the formulas but it just pasted the values.

So, 1. Copy Pasting a Table in a PowerPivot does not copy-paste’s the Formula’s in the columns.

And also it does not keep the “Data connection”. In this case, Table1 was connected to Azure Data source and I could “refresh” data if I chose to. But Table2 was “Static” – In other words, It lost the data connection with the data sources. So I could not “refresh/update” data from the data source if need be.

S0, 2. If you copy Paste a Table in a PowerPivot, the newly copied table does not keep the “Data connection” to data source.

 

I have also posted this here: http://beyondrelational.com/modules/1/justlearned/tips/17976/power-pivot-copy-paste-a-table-in-a-powerpivot-does-not-copy-pastes-the-formulas.aspx

Related articles

Crunch more than 1 million rows in Excel 2010 with free addin called Power Pivot!

Standard

Lately, I have been talking to few business folks who do their own data analysis in excel (2010) and sometimes they run into the excel 2010 limit of 1 million rows. And so when I hear that, I talk about Power Pivot and I talk about what It can do and what it cannot and they are just amazed that there’s a FREE add-in that will help them crunch more than 1 million rows!

happy suprised business user excel power pivot

Image courtesy

You can explore more about this amazing add-in here: http://www.microsoft.com/en-us/bi/powerpivot.aspx

And Read more about pros/benefits of PowerPivot:

Top 5 Ways PowerPivot Helps Excel Pros

PowerPivot? But I use pivot tables in Excel

Related articles

SQL Azure: Is there a “per transaction” cost for Windows Azue SQL Database?

Standard

Question: Is there a “per transaction” cost for Windows Azue SQL Database (SQL Azure)?

Short Answer: No

I recently answered the question on MSDN forum where the question was about Transactions and the associated cost in SQL Azure. As of now, There is no “per transaction” cost associated with SQL Azure. There are two parameters that affect your SQL Azure Bill: 1) Database Size 2) Outbound Data Transfer and an example of an outbound transfer would be data access by an application hosted outside of your Azure DB’s data-center.

If you want to read more about SQL Azure pricing, here’s the official resource:

https://www.windowsazure.com/en-us/pricing/details/#header-4

And here are some of the blog-posts that I’ve written on the same topic:

SQL Azure got a new pricing model!

SQL Azure: Indexes are very helpful but they cost $Money$

Getting started with SQL Azure – Part 3: Provisioning and Billing Model of SQL Azure << Paras Doshi

Related articles

Things I shared on Social Media Networks during Oct 11 – Oct 18

Standard

The Goal of this series is to recap the conversations that I’m having on social networks and I do not want my Blog readers to miss that. So Here is this week’s post:

1)

WOW! Global Internet Traffic (in Peta Bytes Per Month):
27483 PB/Month!
via http://www.evolutionoftheweb.com/

growth of the internet big data

(Thanks Hardki pandya for sharing. Here’s his tweet: https://twitter.com/HPsay/status/258207371162181632 )

Also for the a related link:

https://www.facebook.com/suvendu.shekhar.giri said “This is really awesome and very informational ”

evolution of the web and surge in Data

2)

Patent wars: http://flowingdata.com/2012/10/08/patent-war/

patent wars google apple motorola htc samsung

3)

Quote: One reason Wikipedia is still a popular source for information is because most people don’t know they can edit it themselves.

Via https://www.facebook.com/thedolt and Gaurang Patel (https://www.facebook.com/Gaurang.patel7 ) commented that: “Yes, Absolutely true,most people don;t know they can edit post them selves,and i think they don’t have other alternative to get quick information.”

4)

https://www.facebook.com/janakiramm quote:

“Without Stackoverflow, 50% of the developers cannot continue to be developers and without Google, 80% of the developers cannot even claim to be developers!”

5)

Status update: “I just worked on a SQL Server 2012 Master Data Services Tutorial where I learned how to 1) create a new Model 2) Use MDS Excel Add – In to load entities to the model 3) use Master data manager web app 4) add a new member via MDM web app 5) And create a subscription view to retrieve entity members. if you want to do learn that: search for “SQL Server 2012 Developer Training kit“. Awesome resource!”

MDS SQL Server 2012 master data services tutorial

6)

Shared a Resource: Introducing SQL Server 2012 Master Data Services Improvements http://channel9.msdn.com/posts/SQL11UPD05-REC-03

7)

SolidQ mentioned me a post about the Sep 2011 SolidQ Journal edition: https://plus.google.com/u/0/105279914944150120482/posts/cKh6hurWndj

8)

SolidQ India (https://twitter.com/SolidQIndia) Re-Tweeted my post about “Blog: Why am I not seeing “Month Names” in correct logical order?” http://parasdoshi.com/2012/07/22/powerpivot-model-why-am-i-not-seeing-month-names-in-correct-logical-order/

9) Milena Petrovic said “Lovely Graph” for this graphic: https://plus.google.com/u/0/105010538932095629627/posts/2uvE5B2HSk5

From the webinar "How to Walk The Path from BI to Data Science: An interview with Michael Driscoll, data scientist and CEO of Metamarkets" - A global surge in Data // A nice slide about Big Data

Image Courtesy: From the webinar “How to Walk The Path from BI to Data Science: An interview with Michael Driscoll, data scientist and CEO of Metamarkets” – A global surge in Data

That’s about it. You can read last week’s post here: Things I shared on Social Media Networks during Oct 3 – Oct 10

Let’s connect and converse on any of these people networks!

paras doshi blog on facebookparas doshi twitter paras doshi google plus paras doshi linkedin

Related articles