Unpivoting data using the data explorer preview for Excel 2010/2013:

Posted on April 25, 2013 by Paras Doshi • Posted in Business Intelligence, Data Analysis, Microsoft Office • Tagged Business intelligence, Column, Data Explorer, DataSet, Excel, Excel 2010 & 2013, Excel 2010/2013, Microsoft, Spreadsheet, Tables • 2 Comments

Introduction:

Data Explorer add-in is amazing! It’s helps you: combine, find and re-shape your data in Excel 2010/2013. I’ve blogged about: 1) How to merge Table Data and 2) How to clean duplicate data and now in this blog post, I want to share a step-by-step on Unpivoting data using the Data Explorer add-in.

Before we begin, If you haven’t downloaded and installed the data explorer add-in for Excel 2010 & 2013, you can find Information about it here: http://office.microsoft.com/en-us/excel/download-data-explorer-for-excel-FX104018616.aspx

Problem:

What is un-pivoting? I hear you ask. Instead of explaining it, let me share an Image:

BTW, the above data is from my Facebook Page Insights.

So our problem statement is (please refer to above Image): we are given table blue and we need to output table green. In other words, we need to Unpivot the data.

Solution:

Here are the steps:

1) Open Excel, Open Data Explorer add-in. And Connect to your data. Wait when you see the Query Editor.

2) (Optional) In the Query Editor, Rename the query. I renamed it to “Unpivot Data”. And this how my query editor looks:

3) Now, Select the columns that need to be unpivoted > Right Click > Unpivot Column

Note that I’ve selected all columns that I want to UnPivot:

4) You’ll see the updated results in the query editor window. I renamed the columns “Attribute” to “Age and Gender” and “value” to “reach”. If you want to rename the columns, select the column > Right click > rename.

If everything looks OK, click on Done in the bottom right corner

5) There you have it, Unpivoted data in Excel 2010/2013 using Data Explorer add-in!

And then its super easy to create charts, Here’s one I created after I had unpivoted the data:

Insight: For my blog, my Target Audience seems to Male between the age of 18-24 and then 25-34.

FYI: The Date Range of the Data Set of 1st Jan 2013 – 25th Apr 2013.

That’s about it for this post, Here are some Related articles:

Remove Duplicates in Excel Tables using Data Explorer Add-in: (parasdoshi.com)
Merging/Joining datasets in Excel using Data Explorer add-in (parasdoshi.com)
PASS Business Analytics Conference Keynote Day #1 (parasdoshi.com)

Your comments are very welcome!

Business Analytics project is like an iceberg:

Posted on April 22, 2013 by Paras Doshi • Posted in Business Intelligence, Data Analysis • Tagged Analytics, Business analytics, Business intelligence, Data Integration, Data Warehouse, Data Warehousing, Databases • Leave a comment

Business persons may not realize that Business Analytics project may involve significant efforts for the under the hood technical tasks like Data Cleaning, Data Integration, Building-a-data-warehouse, creating ETL processes, gathering business requirements among other tasks. And that explains the title of this blog: Business Analytics project is like an iceberg. It’s because, a business person may just see the tool used to visualize data but may not realize the work that went into making it “analytics-ready”. From a project management standpoint – before a project is initiated, the discussion about this different aspects of the project need to communicated to the business stakeholders so that they are in the know of efforts involved in building an analytics solution. And with that, Here’s the summary of this discussion in form of an Image:

PASS Business Analytics Conference Keynote Day #1 (parasdoshi.com)
Business Analytics Continuum: (parasdoshi.com)
PASS Business Analytics Conference Keynote Day #2 (parasdoshi.com)

PASS Business Analytics Conference Keynote Day #1

Posted on April 11, 2013 by Paras Doshi • Posted in Business Intelligence, Microsoft Business Intelligence • Tagged Microsoft, Spreadsheets, Excel, Tools, Business intelligence, Databases, Microsoft PowerPoint, Business analytics, Data Explorer • 8 Comments

In this post, I’ll summarize the PASS Business Analytics Conference’s Keynote Day #1:

The structure of the Keynote:

One of the NEW challenges that Data Pros face today is complexity involved in building a BI solution. Following slides nicely represent the challenge from the Tools standpoint:

Image Courtesy: https://twitter.com/SQLGal/status/322342662013321216

Microsoft’s Goal is to SIMPLIFY the above situation

We need to do the same thing with BI that PowerPoint did to the slide projector. SIMPLIFY IT. #passbac

— Mark Vaillancourt (@markvsql) April 11, 2013

NEW Tools:

> Data Explorer (Excel add-in)

> Power View in Excel 2013

> Geo Flow

Key Take away from the demo’s was:

Power View is a great tool that you could use to extract insights from data.

E.g. Insights about Music Charts from Germany:

#MilliVanilli for Germany?JS Bach just rolled over #passbac

— Mark Tabladillo (@MarkTabNet) April 11, 2013

Now combine the power of Power View w/ the new capabilities like Data Explorer that let’s you find, combine & refine data via Data Explorer.

In the Demo, they combined data in hadoop w/ data in relational sources. This is Powerful!

And Also

The Preview for GeoFLow in Excel was announced!

They had a great demo on a pretty big touch device:

Sorry for the poor image – but imagine a touch device of that size w/ an interactive data visualization that has 3D geo maps!

Conclusion:

They had a nice message at the end of the keynote:

Nice Message: EVERYONE should be able to extract insights from data. It should not be business intelligence but BASIC intelligence #passbac

— Paras Doshi (@paras_doshi) April 11, 2013

Business Analytics Continuum:

Posted on March 13, 2013 by Paras Doshi • Posted in Business Intelligence, Microsoft Business Intelligence, Predictive Analytics • Tagged Analytics, BigData, Business, Business analytics, Business intelligence, Databases, PASS Business Analytics conference • 1 Comment

Think of “continuum” as something you start and you never stop improving upon. In my mind, Business Analytics Continuum is continuous investment of resources to take business analytics capabilities to next level. So what are these levels? Douglas McDowell explained about this concept in recent post here – I think it was a great food for thought for me and hence I posting about this particular concept here.

Here is the visual representation of the concept:

And I would encourage you to read the entire post and other posts in the series here: PASS BAC Preview Series: Business Analytics Defined

Resource: 12 recorded sessions from the 24hop business analytics edition are online! #passbac #msbi (parasdoshi.com)

Resource: 12 recorded sessions from the 24hop business analytics edition are online! #passbac #msbi

Posted on February 22, 2013 by Paras Doshi • Posted in All about Data!, Microsoft Business Intelligence • Tagged BigData, Business analytics, Business intelligence, Data mining, Data visualization, Databases, Knowledge Management, Microsoft • 2 Comments

Recently, PASS hosted a 24hop business analytics event:

it’s #24hop (Business Analytics edition) time! 24hoursofpass.com #sqlpass

— Paras Doshi (@paras_doshi) January 30, 2013

And now, the 12 one hour sessions ranging from data visualization, predictive analytics to Big Data are online for you to watch! They also serve as “Trailer” for what you can expect at the PASS Business Analytics conference!

Here’s the URL: http://passbaconference.com/Sessions/SneakPeeks.aspx

Have you watched any of the Business Analytics #24HOP session recordings yet? Available Now: ow.ly/htXsS #passbac #msbi

— 24hop (@pass24hop) February 8, 2013

And I was following some of these sessions live on the event day – and I can tell you, these sessions are great resources!

Also, I participated in the twitter contest (by Microsoft BI) that was happening along w/ the event – and this is what I got for my win!

hoodie w/ embedded earphones!

That’s about it for this post. Enjoy the recordings!

Things I shared on Social Media Networks during Noc 12 – Dec 31 (2012)

Posted on January 1, 2013 by Paras Doshi • Posted in Paras Doshi - Blog, Technology, ted, Things I shared in Social Media Networks series • Tagged BigData, Business analytics, Business intelligence, Databases, Facebook, Google, Richard Branson, Wordpress • 1 Comment

Big Data: The Coming Sensor Data Driven Productivity Revolution http://bit.ly/TQAPsW

Check out some nice getting started tutorials at beyondrelational site: http://bit.ly/RVVHRV

Complexity is your enemy. Any fool can make something complicated. It is hard to make something simple – Richard Branson

— via Paras Doshi – Blog http://on.fb.me/WAQ5ky

The success of companies like Google, Facebook, Amazon, and Netflix, not to mention Wall Street firms and industries from manufacturing to retail and healthcare, is increasingly driven by better tools for extracting meaning from very large quantities of data,” says Tim O’Reilly

— via Paras Doshi – Blog http://on.fb.me/WAQ5ky

Nice collection of about 20+ videos around the topic of “Data Science”: http://bit.ly/WMkZqc

Nice collection of videos by Berkeley school of information: http://bit.ly/Tf1yAD #Information #Data

Just found Facebook’s data team’s page: http://on.fb.me/ToYILO

via V Talk Tech – A Parth Acharya Blog – Nice HeatMap of stocks! http://on.fb.me/SfBbvF

what’s the biggest fear about cloud computing? via Windows Azure http://on.fb.me/VjIiHR

Resource: Presentations from the Sentiment Analysis Symposium http://bit.ly/VtPH3B

If I switched to the newest “holiday” theme on WordPress, this is how it would look: http://on.fb.me/UEuyFr

Nice! Code School now has R programming language! I have been playing with R for a while now and definitely want to learn more – here’s the link to learn R: http://bit.ly/VEAnkZ

Interesting tool from Google to optimize and analyze web page speeds: http://bit.ly/HTubNC

Performed #sentiment #Analysis on #starbucks twitter data using #R ! It was fun! http://on.fb.me/Z3qLo8

In 2002: The Data Warehousing Institute estimates that data quality problems cost U.S. businesses more than $600 billion a year. And of course, over the past 10 years, this number would be bigger. http://bit.ly/TPT9r3

Reading: Business Analytics vs Business Intelligence? http://bit.ly/YUtJwx

Big data is a nickname for the recent increase in largely external and unstructured business and consumer information. How are businesses across industries harnessing traditional enterprise information management functions and systems to translate big data into useful business intelligence? http://www.deloitte.com/view/en_US/us/Services/additional-services/deloitte-analytics-service/217c19e69249b310VgnVCM2000003356f70aRCRD.htm

For business analytics professionals: 12 webcasts on Jan 30th 2013 http://bit.ly/RUFsZ3 #sqlpass #analytics #24hop

Some nice insights about how to build an Internet platform, from the founder of Zipcar: http://bit.ly/Yco6IP

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

There’s been a growing interest in Hadoop & Big Data, Here’s the Proof:

Posted on December 14, 2012 by Paras Doshi • Posted in All about Data!, Big Data, Business Intelligence, Hadoop, Microsoft Business Intelligence • Tagged Apache Hadoop, Big Data, Business intelligence, Data Warehousing, Databases, Google, Google Trend, Hadoop • 2 Comments

I like to keep an eye on Technology Trends. One of the ways I do that is by subscribing to leading magazines for articles – I may not always read the entire article but I definitely read the headlines to see what Industry is talking about. during last 12 months or so I have seen a lot of buzz around Big Data and I thought to myself – It would be nice to see a Trend line for Big Data. Taking it a step further, I am also interested in seeing if there is a correlation between growing trend in “Hadoop” and “Big Data”. Also, I wanted to see how it compares with the Terms like Business Intelligence and Data Science. With this, I turned to Google Trends to quickly create a Trend report to see the results.

Here’s the report:

Here are some observations:

1) There’s a correlation between Trend of Big Data and Hadoop. In fact, it looks like growing interest in Hadoop fueled interest in “Big Data”.

2) Trend line of Big Data and Hadoop overtook that of Business Intelligence in Oct 2012 and sep 2012 respectively.

3) Decline in Trend line of Business Intelligence.

4) There seems to be a steady increase in Trend line for Business Analytics and Data Science.

And Here’s the Google Trend report URL: http://www.google.com/trends/explore#q=Big%20Data%2C%20Hadoop%2C%20Business%20Intelligence%2C%20Business%20Analytics%2C%20Data%20Science&cmpt=q

What do you think about these trends?

Mapping Business Intelligence Developer’s Tools: Microsoft SQL server & SAP Netweaver BW

Posted on August 8, 2012 by Paras Doshi • Posted in Microsoft Business Intelligence, Paras Doshi - Blog • Tagged Business intelligence, Business Intelligence Tools, Business Objects, Data modeling, Microsoft, SAP, SAP NetWeaver Business Intelligence, SAP Netweaver BW • 2 Comments

This Post is not about Microsoft BI VS Sap BI. NO. NO. NO.

Then What is it?

well, I have been playing with SAP’s Netweaver BW Tools for past three months now as a part of a Business Intelligence class that’s about to conclude – Also, I have been involved with work on Microsoft’s SQL server Business Intelligence Tools. So I thought – it would be FUN to map SAP Netweaver BW Tools (that I got to play with in an academic capacity) and Microsoft’s Business Intelligence Tools (which is currently what I am working on) – so, here you go:

	Tool in Microsoft BI	Tool in SAP Netweaver BW
ETL (Extract, Transform, Load)	SQL Server Integration Services (SSIS)	SAP Netweaver BW: Data Warehousing Workbench
Cube	SQL Server Analysis Services – Multidimensional Mode (SSAS)	SAP Netweaver BW: Data Warehousing Workbench: Modeling
Report Design Tool and Reporting Layer(It’s not an exhaustive list and does not include third part tools)	SQL Server Reporting Services (SSRS) Report Builder Excel (Excel Services/Pivot Tables) PerformancePoint	Business Explorer (BEx): BEx Query Designer BEx Analyzer (Excel Add-in) BEx web Analyzer
Data Mining	Data Mining Projects in SQL Server Analysis Services	SAP Netweaver BW: Data mining – Analysis Process Designer

Note about SAP BusinessObjects: I mapped the Tools in Microsoft BI with the tools that I got to study in my SAP class. Then I was searching what’s the current scenario in SAP world (I know about Microsoft’s!)– I learned that SAP BI world is comprised of TOOLS in SAP Netweaver BW + SAP BusinessObjects (BO). And in the course I studied the following components of Business Objects:

Web Intelligence for ad-hoc query and reporting
Crystal Reports for enterprise reporting
Xcelsius (BO Dashboard) for Dashboard designing

For those interested I am also mapping few terms used while cube development in Microsoft BI and SAP Netweaver BW

Microsoft: SSAS Multidimensional mode	SAP Netweaver BW
Cube	InfoCubes
Dimensions	Characteristics
Measures	Key Figures
Data Source Views (DSV’s)	Data Source

Note:

1) I have not mapped the Tools in Self Service BI space.

2) This comparison is not for deciding between Microsoft BI vs. SAP Netweaver BI/SAP BusinessObjects – this post is just meant for mapping tools available in Microsoft BI and SAP Netweaver BW and so if you are an expert in say Microsoft BI – this post will help you see what corresponding tool are available in SAP Netweaver BW world. Consider it as a starting guide for your research.

3) Note the date the post was written – the name of the products may have changed in future. refer to official sites for latest & greatest!

Thanks for reading.

Where can we find datasets that we can play with for Business Intelligence, Data Mining, Data Analysis Projects?

Posted on July 31, 2012 by Paras Doshi • Posted in Data Mining, Paras Doshi - Blog • Tagged Business intelligence, Data mining, data science, data sets, kdnuggests, Quora • 4 Comments

Update 1st August: I found this too: UCI MAchine Learning Repository http://archive.ics.uci.edu/ml/

Update 12 Nov 2012: I found this! Link to 400 datasets! http://www.datawrangling.com/some-datasets-available-on-the-web

Update 19 Dec 2012: Lynn Langit has a list here: http://lynnlangit.wordpress.com/public-datasets/

——————————————-

Recently on SQL Server Data Mining Forum, I answered a question about where to find DataSets for Business Intelligence Project.

Apart from Datasets AdventureWorks and Contoso data-sets, there are places where you can download data-sets to play with for your Business Intelligence, Data Mining or Data Analysis Projects.

Here is the List of data-sets that I have collected:

1. KDNuggests: Datasets for Data Mining

2. Quora: Where can I get large datasets open to the public?

3. Windows Azure Data Market

4. National council of Teachers of Mathematics

5. Introduction to Data Science: Data Sets

6. Hilary Mason’s Data-Set Bundle: https://bitly.com/bundles/hmason/1 (Also featured in Quora Link that I shared earlier)

7. And If you can’t find the data-set, ask it here: http://getthedata.org/

Have I missed anything? Do comment! I’ll add the link with due credit.

How to Solve Error: After Reboot, SharePoint sites says “Service Unavailable”

Posted on July 16, 2012 by Paras Doshi • Posted in Microsoft Business Intelligence, Paras Doshi - Blog • Tagged Business intelligence, Internet Information Services, Microsoft, Microsoft SharePoint, Microsoft SQL Server, Powerpivot, Servers • Leave a comment

I had to reboot my Dev Machine on which I have my SQL Server 2012 Business Intelligence Setup. After reboot, It turns out that when I wanted to open the SharePoint Site (for Power View related work) – it gives me error:

“Service Unavailable

HTTP Error 503. The Service is unavailable”