Data Analysis and In Memory Technologies, let’s connect the dots:

Posted on May 7, 2013 by Paras Doshi • Posted in All about Data!, Back to Basics, Business Intelligence, Data Analysis • Tagged Data analysis, Database, In-memory database, Microsoft, Powerpivot, Qlikview, SAP HANA • 6 Comments

SPEED is one of the important aspect of Data Analysis. Wouldn’t it be great if you query a data source, you get your answers as soon as possible? Yes? Right! Of course, it depends on factors like the size of the data you are trying to query but wouldn’t it be great if it’s at “SPEED OF THOUGHT“?

So Here’s the Problem:

Databases are mostly disk based and so the bottleneck here is the speed at which can get access to data off the disks.

So what can you do?

Let’s put data in a RAM (memory) because data-access via memory is faster.

If it’s sounds so easy, why didn’t people do it earlier? And why are we talking about “In Memory” NOW?

1) BIGGER Data Size/sets and so today with more data, it takes more time to query data from databases. And so researchers have looked at other approaches. One of the effective approach they found is: In-memory

(And I am not ignoring the advances in Database Technologies like Parallel databases, But for the purpose of understanding “Why In-memory”, it’s important to realize the growing size of data sets and a viable alternative we have to tackle the problem: In memory. And also I am not saying that it’s the ONLY way to go. I am just trying to understand the significance of in-memory technologies. We, as data professionals, have lot’s of choices! And only after evaluating project requirements, we can talk about tools and techniques)

2) PRICE of Memory: Was the price of RAM/memory higher than what it is today? So even though it was a great idea to put data in memory, it was cost-prohibitive.

So Let’s connect the dots: Data Analysis + In Memory Technologies:

What’s common between Microsoft’s PowerPivot, SAP HANA, Tableau and Qlikview?

1) Tools for Data-Analysis/Business-Intelligence 2) Their Back End data architecture is “In Memory”

So since Data Analysis needs SPEED and In-Memory Technologies solves this need – Data Analysis and Business Intelligence Tools adopted “In-memory” as their back-end data architecture. And next time, when you hear a vendor saying “in-memory”, you don’t have to get confused about what they’re trying to say. They’re just saying that we got you covered by giving you ability to query your data at “speed of thought” via our In-memory technologies so that you can go back to your (data) analysis.

That’s about it for this post. Here’s a related post: What’s the benefit of columnar databases?

your comments are very welcome!

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!

Merging/Joining datasets in Excel using Data Explorer add-in

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

Problem:

Merging/Joining/Combining data-sets in Excel has not been an easy task. There are third-party add-ins that makes it easy but out of the box, excel didn’t have an easy way to merge/join table data. But now with the Data Explorer add-in, we have an add-in that let’s us merge/join data in excel w/ few clicks.

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

Situation:

Input is Table 1 & Table 2. The output we need is merged Table.

Table 1:

Date	Daily New number	Month
1/1/2012	0	1
1/2/2012	0	1
1/3/2012	0	1
1/4/2012	0	1
1/5/2012	0	1
1/6/2012	0	1
1/7/2012	0	1
1/8/2012	0	1
1/9/2012	0	1

………………………………..

Table 2:

Month	Month Name
1	January
2	February
3	March
4	April
5	May
6	June
7	July

Merged Table:

Date	Daily New number	Month	Month Name
1/1/2012	0	1	January
1/2/2012	0	1	January
1/3/2012	0	1	January
1/4/2012	0	1	January
1/5/2012	0	1	January
1/6/2012	0	1	January
1/7/2012	0	1	January
1/8/2012	0	1	January
1/9/2012	0	1	January

………………………………

Solution:

Let’s see how data explorer can help us Join/Merge Table 1 & Table 2.

1) create query that connects to Table 1 & Table 2.

2) Once you have queries that connect to the tables need to be merged, then click on Merge

3) Once you click on Merge, you’ll see a dialog:

Here you need to configure three things:

a) First Table

b) Second Table

c) Columns that will be used to merge/join data

In this case, this is how my merge dialog looks:

4) Once configured correctly, click on OK. You’ll see a dialog box where you can configure the output of the merged table. click on the new column to see the options that are available to you to configure the output of the merged table:

5) In this case, I’ve selected just one column month name that needs to be merged. You can also explore the aggregate tab in case you’ve numbers that needs merging.

6) This is how the output looks:

7) Rename the new column.

Select the new column > Right Click > Rename

8) Click Done if it looks OK.

9) The merged data is now available to you in Excel!

And one can analyze it!

Let’s see before and after. Note that instead of month numbers, we now have month names

In this post, we saw how to merge/join/combine data from two different sources in Excel 2010.

Remove Duplicates in Excel Tables using Data Explorer Add-in: (parasdoshi.com)

Remove Duplicates in Excel Tables using Data Explorer Add-in:

Posted on April 23, 2013 by Paras Doshi • Posted in Business Intelligence, Microsoft Office • Tagged Column, Data Explorer, Data set, DataSet, Excel, Microsoft, Microsoft Excel, Spreadsheet, Spreadsheets, Tables • 3 Comments

In this blog post, we’ll see how you can remove duplicated and clean data in excel tables using Data Explorer Add-in.

Problem:

Our Excel Table has following Data:

Month	Month Name
1	January
1	January
1	January
2	February
2	February
3	March

And we want to remove duplicates to make the data-set look like this:

Month	Month Name
1	January
2	February
3	March

In real world data-sets, we wouldn’t have few rows but lot’s of rows and doing it manually wouldn’t be the wisest option. With that in mind, let’s look for a few-clicks solution that can help us remove duplicates.

Solution:

If you haven’t already, download the Data Explorer add-in preview available for Excel 2010 & 2013. It can do a lot more than removing duplicates – it’s a great add-in and it’ll save you lots of time especially if your job involves discovering, cleaning and combining data for analysis purposes. After you’re done installing the add-in, use the steps below to remove duplicates in an excel column:

1. Open Data in Excel. Switch to Data Explorer Tab

2. For the purpose of the demo, I am assuming that you already have the data in excel file. If not, you can connect to other sources via the add-in.

3. Data Explorer add-in > Excel Data> From Table

4. After you’ve clicked on the From Table, a query editor will pop up:

5. Select both columns

(you can select both columns by: select first column > hold down the ctrl key and then click on second column)

6. Right click > Remove Duplicates

7. click on done if you see that the duplicates have been removed correctly

Conclusion:

In this blog post, we saw how to remove duplicates and clean data in Excel using the Data Explorer Preview add-in.

If you’ve not 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

Note:

1) URL to download the add-in may change in future

2) The steps that I described may also change because as of today the ad-in is in “preview” stage and things may change in future.

PASS Business Analytics Conference Keynote Day #1 (parasdoshi.com)
Data Explorer for Excel: An experiment with Sachin Tendulkar (romitmehta.com)
Found something interesting by exploring a “List of companies by revenue” Data Set: (parasdoshi.com)

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)

How to solve common Data Quality Problems using Data Quality Services? (Part 1)

Posted on April 17, 2013 by Paras Doshi • Posted in Business Intelligence, Data Quality Services, Microsoft Business Intelligence • Tagged Data quality, Database, DQS, Microsoft SQL Server, Programming, Regular expression, sql, SQL Server 2012 • Leave a comment

New Journal Article: First article of 2, where you will be able to see how you can use SQL Server 2012′s DQS to solve common data quality problems. http://bit.ly/172Kh5L

Topics covered:
- Data standardization
- Identifying and correcting unrealistic or invalid values
- Validation and correcting records using Regular Expressions

Read Here: How to solve common Data Quality Problems using Data Quality Services (Part 1)

PASS Business Analytics Conference Keynote Day #2

Posted on April 12, 2013 by Paras Doshi • Posted in All about Data!, Business Intelligence • Tagged Analytics, Chicago, Data, Data analysis, data science, Freakonomics, Levitt, Steven Levitt, SuperFreakonomics • 2 Comments

Dr. Steven Levitt’s (Indiana Jones of economics & Author of Freakonomics) work involves finding insights from data. In the keynote, he shared some of the interesting & fun insights that he found from data.

One Example: Dr. Levitt: According to the data, It was 7 times more dangerous to sell crack in Chicago than it was being in combat in Iraq. https://twitter.com/markvsql/status/322707949158006786

He also talked about other insights that he found which could also be found in his book Freakonomics. After getting audience fascinated about what analyzing data can do – he moved to his real world experiences of analyzing data for businesses. And tied all these fascinating insights back to some tips he had for the audience. Here is a brief recap of the tips he shared:

> “Ideas don’t come out of the blue. Almost always ideas come out of the data” – Dr. Steven Levitt

> “You guys are the future. What you’re doing is the key to a business’ success or failure.”

> Experiment & Test Hypothesis using DATA

> Misconceptions can cripple you. Let the data speak, even when it might be difficult

> Most important people = who understand and know what to do with data, not those who pretend they know the answer.

> Dr. Levitt: without data any biz will be left behind, must experiment and accept failure

*Above text is linked to tweets.

That’s about it for this post. What do you think about the tips that Dr Levitt shared?

PASS Business Analytics Conference Keynote Day #1 (parasdoshi.com)
Resource: 12 recorded sessions from the 24hop business analytics edition are online! #passbac #msbi (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 Business analytics, Business intelligence, Data Explorer, Databases, Excel, Microsoft, Microsoft PowerPoint, Spreadsheets, Tools • 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)

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: