How conditionally formatting your data in Excel can help you save time in answering business questions?

Posted on May 6, 2013 by Paras Doshi • Posted in All about Data!, Data Analysis, Data Visualization • Tagged Data Warehousing, Databases, DataSet, Excel, Excel 2010, Microsoft Excel, Spreadsheet, Visual analytics • 1 Comment

Visual analytics is amazing – it helps “data enthusiasts” save time in answering questions using Data. Let’s see one such example. For the purpose of the blog post, I am going to show how to do it in Excel 2010:

Problem:

Here’s the Business Question: What was sales of Tea in North Region in 2012 Q1

Here’s the data:

SALES DATA(2012 Q1)	East	West	Central	North	South
Coffee	$ 7,348.00	$ 7,238.00	$ 1,543.00	$ 9,837.00	$ 1,823.00
Tea	$ 9,572.00	$ 8,235.00	$ 3,057.00	$ 8,934.00	$ 13,814.00
Herbal Tea	$ 5,782.00	$ 8,941.00	$ 9,235.00	$ 392.00	$ 1,268.00
Espresso	$ 9,012.00	$ 2,590.00	$ 4,289.00	$ 7,848.00	$ 340.00

So it’s easy to give out answer using the data: $8934

But let me CHANGE the business question:

WHICH Products in WHAT regions are doing the best?

Now this questions is not as easy as the previous one? WHY? because you’ll have to manually go through each number in a linear fashion to answer the question. Now imagine a bigger data-set. It’ll take even more time.

Solution

What can Excel Power users and Data Enthusiasts do to answer the new business question in an efficient way? Well, let’s see what conditional formatting can do it:

Now with the Data Bars, it’s easier to just glance at the report and see best performing products and regions. For instance, it’s very easy to spot that Tea is performing best in South among all products and region.

So how do you create data bars?

1. Select the data

2. Home > Conditional Formatting > Data Bars

3.Done! you’ll see this:

4. You can play with other options here to see what suits the best for your needs. But I just wanted to point out that there is a way for you to highlight the data in a way that helps you save time in answering business questions using data

Conclusion:

Visual analytics is a great way to quickly analyze data. In most cases, Human brain is much faster at interpreting the visual results as oppose to text/numbers – so why not use it to your advantage. And tools like Excel have inbuilt functionality to help you do that!

Remove Duplicates in Excel Tables using Data Explorer Add-in: (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)

Data Reporting ≠ Data Analysis

Posted on March 19, 2013 by Paras Doshi • Posted in All about Data!, Data Analysis • Tagged Big Data, Data analysis, Data mining, Data point, Data Warehousing, Databases, Exploratory data analysis, Math • Leave a comment

One of the key thing I’ve learned is importance of differentiating the concepts of “Data Reporting” and “Data Analysis”. So, let’s first see them visually:

Here’s the logic for putting Data Reporting INSIDE Data Analysis: if you need to do “analysis” then you need reports. But you do not have to necessarily do data analysis if you want to do data reporting.

From a process standpoint, Here’s how you can visualize Data Reporting and Data Analysis:

Let’s thing about this for a moment: Why do we need “analysis”?

We need it because TOOLS are really great at generating data reports. But it requires a HUMAN BRAIN to translate those “data points/reports” into “business insights”. This process of seeing the data points and translating them into business insights is core of what is Data Analysis. Here’s how it looks visually:

Note after performing data analysis, we have information like Trends and Insights, Action items or Recommendations, Estimated impact on business that creates business value.

Conclusion:

Data Reporting ≠ Data Analysis

Examples of Machine Generated Data from “Big Data” perspective:

Posted on January 28, 2013 by Paras Doshi • Posted in Big Data • Tagged Big Data, Business, Data Warehousing, Databases, EMC Corporation, IBM, Machine Generated Data • Leave a comment

I just researched about Machine Generated Data from the context of “Big data”, Here’s the list I compiled:

- Data sent from Satellites

- Temperature sensing devices

- Flood Detection/Sensing devices

- web logs

- location data

- Data collected by Toll sensors (context: Road Toll)

- Phone call records

- Financial

And a Futuristic one:

Imagine sensors on human bodies that continuously “monitor” health. How about if we use them to detect diabetes/cancer/other-diseases in their early phases. Possible? May be!

Interesting Fact:

Machine can generate data “faster” than humans. This characteristics makes it interesting to think about to analyze machine generate data and in some cases, how to analyze them in real-time or near real-time

Ending Note:

Search for Machine Generated Data, you’ll be able to find much more, it’s worth reading about from the context of Big Data.

Thanks:

http://www.dbms2.com/2010/04/08/machine-generated-data-example/

http://en.wikipedia.org/wiki/Machine-generated_data

http://tdwi.org/articles/2012/10/23/machine-generated-big-data.aspx

Matching activity in Data Quality Services in action!

Posted on January 7, 2013 by Paras Doshi • Posted in Data Quality Services, Master Data Services, Microsoft Business Intelligence • Tagged Data quality, Data Quality Services, Data set, Data Warehousing, Database, DQS, Knowledge base, Master data management, Reference data, sql, WCF Data Services • 3 Comments

In this post, we’ll see matching activity in action. For the demo purpose, I’ll be using Data-Sets that I’ve found via Movies Sample & EIM tutorial .

Situation 1:

we’ve a list of movies and we want to identify “matching” movie titles.

Solution 1:

Create a Matching Policy

1) Mapping the Domains:

2) Configuring the Matching Policy:

Note: You can have Matching Rules on more than one domain. I used one domain for demo purposes.

3) View Matching results:

4) Publish the KB (which stores the matching policy)

Once you have the matching policy, you can use this in a Data Quality Project:

5) See How I ran a Data Quality Project (w/ matching activity) in the image below.

Note: You can export the clean data-set via Data Quality Project.

Situation 2:

we’ve a list of Supplier Names and we want to identify “matching” supplier names.

Note that in this situation, you would see how to use more than one domain to create a matching rule.

Solution 2:

Most of the steps would remain same as situation 1, But I want to show you Matching Policy & Matching Results

Matching Policy:

Matching results:

Also, I want to show that, the matching policy (DQS KB) that we created earlier can be used in Master Data Services too! For more details check out the resource: DQS, MDS & SSIS for EIM

Conclusion:

In this post, we saw how DQS can be used to clean “matching” records. For step by step tutorial, please refer to Movies Sample & EIM tutorial .

SQL Server 2012 Data Quality Services Term based Relation’s in action! (parasdoshi.com)
How to clean records using Regular Expressions in Data Quality Services? (parasdoshi.com)
How to standardize data using Data Quality Services? (parasdoshi.com)
How to clean address records using third-party reference data-sets in Data Quality Services? (parasdoshi.com)
Guest Blog: How we use Fuzzy Lookup add-in in our company to solve data inconsistency problems: (parasdoshi.com)
Difference between Term based relations and Domain values in SQL server 2012 Data Quality Services (parasdoshi.com)
How to detect unrealistic or invalid values using Data Quality Services? (parasdoshi.com)
Master Data Services: Analogy to remember what are Models, Entities and Attriutes (parasdoshi.com)
Data Quality Service’s Composite Domains in action! (parasdoshi.com)
Data Profiling and SQL Server 2012 Data Quality Services (parasdoshi.com)

Data Profiling and SQL Server 2012 Data Quality Services

Posted on January 3, 2013 by Paras Doshi • Posted in Data Quality Services, Microsoft Business Intelligence • Tagged Data cleansing, Data Integrity and Cleansing Tools, Data profiling, Data quality, Data Quality Services, Data Warehousing, Database, Knowledge base, Knowledge discovery, sql • 1 Comment

Data Profiling in Data Quality Services happens at following stages:

1) While performing Knowledge Discovery activity

1A: In the Discover step:

1b. Also in the manage domain values step:

While profiling gives you statistics at the various stages in the Data Cleaning or Matching process, it is important to understand what you can do with it. With that, Here are the statistics that we can garner at the knowledge discovery activity:

Newness
Uniqueness
Validity
Completeness

2) While Performing Cleansing activity:

2A: on the cleansing step:

2b: Also on the mange and view results step:

Here the profiler gives you following statistics:

Corrected values
Suggested Values
Completeness
Accuracy

Note the Invalid records under the “source statistics” on left side. In this case 3 records didn’t pass the domain rule.

3) While performing Matching Policy activity (Knowledge Base Management)

3a. Matching policy step:

3b. Matching Results step:

Here the profiler gives following statistics:

newness
uniqueness
number of clusters
% of matched and unmatched records
avg, min & max cluster size

4) While performing Matching activity (Data Quality Project)

4a. Matching step:

4b. Export step:

Here Profiler gives following statistics:

Newness
uniqueness
completeness
number of clusters
% of matched and unmatched records
avg, min & max cluster size

Conclusion:

In this post, I listed the statistics provided by Profiler while performing Knowledge Discovery, cleansing, matching policy and matching activity in SQL Server 2012 Data Quality Services.

SQL Server 2012 Data Quality Services Term based Relation’s in action! (parasdoshi.com)
Difference between Term based relations and Domain values in SQL server 2012 Data Quality Services (parasdoshi.com)
How to clean records using Regular Expressions in Data Quality Services? (parasdoshi.com)
How to standardize data using Data Quality Services? (parasdoshi.com)
How to detect unrealistic or invalid values using Data Quality Services? (parasdoshi.com)

How to clean address records using third-party reference data-sets in Data Quality Services?

Posted on December 18, 2012 by Paras Doshi • Posted in Data Quality Services, Microsoft Business Intelligence • Tagged Data Integrity and Cleansing Tools, Data quality, Data Quality Services, Data set, Data Warehousing, Database, DQS, Knowledge base, Melissa Data, Reference data, sql • 4 Comments

In this post, we’ll see how to clean address records using third party reference data-sets in Data Quality Services.

You need to perform following steps to do so

Note that they are not step by step procedures, a high level overview of how DQS works to clean address records using 3rd party reference data-sets:

1) Configure Data Quality Services to use reference data sets. Tutorial here: http://msdn.microsoft.com/en-us/library/hh213070.aspx

2) Go to datamarket.azure.com > and I subscribed to “Address check – verify, correct, Geocode US and canadian Addresses Data” ; we’ll see how to use this in next steps.

3) Note that as of now, we can only have refernece data-sets from Azure Data Market. However, the MSDN thread: http://social.msdn.microsoft.com/Forums/hu-HU/sqldataqualityservices/thread/750faef8-dd69-4a71-b0c1-18ca2f93d59d suggests that we’ll have an ability to provide our (private/self-provided) reference data-sets in next service updates. So for now we’ll have to connect to Azure data market for reference data-sets and for the purpose of the Demo, I connected to Melissa Data’s Address Check.

4) Now via DQS client, let’s create a Knowledge Base!

5) I’ve created following domains:

Address Line
City
State
Zip

And a composite domain: Full Address which contains domains: Address line, city, state and zip in the composite domains.

6) And for the composite domain Full Address – this is how you configure reference data-sets:

7) After creating the Knowledge Base, start a new DQS project. Here the cleaning happens at the composite domain level and this is a demo of how DQS uses third party reference data-set to classify records as suggested, new, invalid, corrected or correct:

You can see that DQS corrected few records and mentions that the reason in this case was Melissa data’s address verify reference data-set:

That’s about it for this post. I hope it helps.

How to detect unrealistic or invalid values using Data Quality Services? (parasdoshi.com)
How to standardize data using Data Quality Services? (parasdoshi.com)
How I think of what is KnowledgeBase in SQL Server 2012 Data Quality Services (parasdoshi.com)
How to Delete a Knowledge Base in SQL Server 2012 Data Quality Services? (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:

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?

Three V’s of Big Data with Example:

Posted on November 22, 2012 by Paras Doshi • Posted in Big Data • Tagged Big Data, BigData, Data, Data mining, Data Warehousing, Databases, Social media, Unstructured data • 1 Comment

In this blog-post, we would see the Three V’s of Big Data with Example:

1. Volume:

TB’s and PB’s and ZB’s of data that gets created:

2. Velocity:

The speed at which information flows.

Example: 50 Million tweets per day!

(This is back in Nov. of 2010 – the number must have increased!)

UPDATE 23 Nov 2012: on, wikipedia it says – 340 million tweets per day!

3. Variety:

All types of data is now being captured which may be in structured format or not.

Example: Text from PDF’s, Emails, Social network updates, voice calls, web traffic logs, sensor data, click streams, etc

Image courtesy

And this may be followed by other V’s like V for Value.

Conclusion:

In this blog-post, we saw Three V’s of Big Data with Example.

Who on earth is creating “Big data”?

Examples to help clarify what’s unstructured data and what’s structured?

Resource: Learn to build an Enterprise Information Management system using Data Quality Services, Master Data Services and SQL Server Integration Services

Posted on November 7, 2012 by Paras Doshi • Posted in BeyondRelational.com, Data Quality Services, Master Data Services, Microsoft Business Intelligence, SQL server • Tagged Data quality, Data Warehousing, Database, DQS, Information management, Master data, Master data management, Microsoft Excel, sql • 4 Comments

[UPDATE 29 Dec 2012: One more whitepaper that's on the same subject and should be read after the tutorial: Cleanse and Match Master Data by Using EIM ]

Here’s the resource: Tutorial: Enterprise Information Management using SSIS, MDS, and DQS Together

Here’s what you’ll learn: