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!

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)

Found something interesting by exploring a “List of companies by revenue” Data Set:

Posted on February 19, 2013 by Paras Doshi • Posted in Data Visualization, Power View • Tagged Data mining, Data visualization, Database, DataSet, Google, IBM, Trafigura, Vitol • 2 Comments

I like exploring data sets to find interesting patterns from them. To that end, I was exploring a data-set: List of companies by revenue and I added a column to calculate Revenue/Employee to explore the dataset:

And I found an outlier!

Here’s the outlier: Exor

Here’s what it’s interesting:

It’s revenue in 2012 is: 109.15 billion USD

And number of employees is just 40!

Just think of Revenue/Employee !

To put things in perspective, Lets Compare that with its neighbor in the data-set:

Rank | Company | Industry | Revenue in USD billion | Employees

48	Koch Industries	Conglomerate	110.00	60000.00
49	EXOR	Investment	109.15	40.00
50	Cardinal Health	Pharmaceuticals	107.55	40000.00
51	CVS Caremark	Retail	107.10	202000.00
52	IBM	Computer services	106.92	433362.00

I got to know about this by quickly creating a data visualization to explore the data-set:

And removing Trafigura, Vitol and Exor, this is what we have:

Observation: oil and gas industry have relatively higher revenue/employee ration.

That’s about it for this post. Thanks for reading about my data exploration!

Playing w/ the Occupational employment Statistics Data-Set: (parasdoshi.com)

Quick Note about SAS’s acroynm SEMMA:

Posted on February 8, 2013 by Paras Doshi • Posted in Data Mining • Tagged Big Data, Data mining, Database, Raw data, SAS, Tool Vendors • Leave a comment

SEMMA is an acronym introduced by SAS which stands for:

Sample, Explore, Modify, Model and Assess.

I had recently posted about the Data Mining & Knowledge Discovery Process which had following sequential steps:

Raw Data => cleaning => sampling => Modeling => Testing

SEMMA follows the similar sequential steps as we had seen in the data mining process. So while Data Mining process is applicable to any data mining tool out their, SEMMA helps when you use SAS enterprise miner. In fact, it has helped me quickly find the data mining functions available in SAS tool:

Back to basics: Data Mining and Knowledge Discovery Process

Posted on January 30, 2013 by Paras Doshi • Posted in All about Data!, Back to Basics, Data Analysis, Data Mining, Predictive Analytics • Tagged Data mining, Database, Google, Raw data • 4 Comments

Once in a while I go back to basics to revisit some of the fundamental technology concepts that I’ve learned over past few years. Today, I want to revisit Data Mining and Knowledge Discovery Process:

Here are the steps:

1) Raw Data

2) Data Pre processing (cleaning, sampling, transformation, integration etc)

3) Modeling (Building a Data Mining Model)

4) Testing the Model a.k.a assessing the Model

5) Knowledge Discovery

Here is the visualization:

Additional Note:

In the world of Data Mining and Knowledge discovery, we’re looking for a specific type of intelligence from the data which is Patterns. This is important because patterns tend to repeat and so if we find patterns from our data, we can predict/forecast that such things can happen in future.

Conclusion:

In this blog post, we saw the Knowledge Discovery and Data Mining process.

Seven Demo’s for SQL Server 2012 Data Quality Services:

Posted on January 18, 2013 by Paras Doshi • Posted in BeyondRelational.com, Data Quality Services, Microsoft Business Intelligence • Tagged Data Quality Services, Database, DQS, Knowledge base, Knowledge discovery, Reference data, Regular expression, sql • Leave a comment

Here are the seven Demo’s that I had posted over the past few weeks, listing them here:

Detecting invalid values in Price domain or unrealistic values in Height domain

How to standardize data using DQS

How to clean records using third-party reference data-sets from Azure Data Market

How to use regular expressions to validate records?

cleaning records like “my company Inc.” and “my company incorporated” using Term Based Relations

How to use cross-domain rules using composite domains

Matching activity in Data Quality Services in action!

How to create a MDS entity via Excel add-in?

Posted on January 17, 2013 by Paras Doshi • Posted in Master Data Services, Microsoft Business Intelligence • Tagged Add-Ins, Data model, Database, Excel, Master data, Master data management, MDS, Microsoft Excel, sql • Leave a comment

In this Post, we’ll see how I (as an Administrator) created a Master Data Services Entity via MDS add-in for Excel:

1. Created a Model via MDS Web Application. I named it “customer Information”

2. Switch to Excel

3. Open the File that has the Data that you want to load to MDS

4. Switch to Master Data tab in Excel
[Resource: Steps I followed to Install SQL Server 2012 Master Data Services (MDS) on my Demo Machine: And then Installed the Excel Add-in]

5. Connect to MDS server (via Excel add-in)

6. Select Model as CustomerInformation

7. Under Build Model, select Create Entity

8. Configure the values in the “Create Entity” > click OK

9. Switch to MDS web application to see the new entity:

I hope this helps! Your comments are very welcome!

Master Data Services: Analogy to remember what are Models, Entities and Attriutes (parasdoshi.com)
Matching activity in Data Quality Services in action! (parasdoshi.com)
Guest Blog: How we use Fuzzy Lookup add-in in our company to solve data inconsistency problems: (parasdoshi.com)
SQLAuthority News – Download Whitepaper – Cleanse and Match Master Data by Using EIM (sqlauthority.com)
How to clean similar textual data in Excel via Fuzzy lookup add-in? (parasdoshi.com)
Official Resource: http://msdn.microsoft.com/en-us/library/hh479642.aspx (MSDN)
Master Data Services Add-in for Microsoft Excel (MSDN)

Microsoft® HDInsight Preview for Windows: How to use Sqoop to load data into HDFS from SQL Server?

Posted on January 11, 2013 by Paras Doshi • Posted in Big Data, Hadoop HDInsight, Microsoft Business Intelligence • Tagged Apache Hadoop, BigData, Database, HDFS, Hive, MapReduce, Microsoft, Microsoft SQL Server, sql, sql server, Sqoop • 9 Comments

In this post, we’ll see how to use Sqoop to load data into HDFS from SQL Server?

With that, here are the steps:

1. You have the Microsoft® HDInsight Preview for Windows Installed on your machine. Here’s a tutorial: Installing HDInsight (Microsoft’s Hadoop) on windows 7

2. Make sure that the Cluster is up & running! To check this, I click on the “Microsoft HDInsight Dashboard” or open http://localhost:8085/ on my machine

Did you get any “wait for cluster to start..” message? No? Great! Hopefully, all your services are working perfectly and you are good to go now!

3. Before we begin, decide on three things:

3a: Username and Password that Sqoop would use to login to the SQL Server database. If you create a new username and pasword, test it via SSMS before you proceed.

3b. select the table that you want to load into HDFS

In my case, it’s this table:

3c: The target directory in HDFS. in my case I want it to be /user/data/sqoopstudent1

You can create by command: hadoop fs -mkdir /user/data/sqoopstudent1

[to learn about how to create directory, read: How to create a directory in Hadoop File System? ]

4. Now Let’s start the Hadoop Command Line (can you see the Icon on the Desktop? Yes? Great! Open that!)

5. Navigate to: c:\Hadoop\sqoop-1.4.2\bin>

*This path may change in future, but navigate to the bin folder under the SQOOP_HOME.

6. Run dir command to see various files under this directory.

Also you can run sqoop help for more information on the command that we are about to run.

7. Now here’s the command to Load data from SQL Server to HDFS:

c:\Hadoop\sqoop-1.4.2\bin>sqoop import –connect “jdbc:sqlserver://localhost;dat
abase=UniversityDB;username=sqoop;password=**********” –table student –tar
get-dir /user/data/sqoopstudent1 -m 1

8. After successfully running the above command, let’s browse the file in HDFS!

That’s about it for this post!

Thanks

Thanks Aviad Ezra who answered my question on this MSDN thread: An error while trying to use Sqoop on HDInsight to import data from SQL server to HDFS

Conclusion:

In this post, we saw how to load data into Hadoop from SQL Server using Sqoop (SQL Hadoop)

Related Articles:

Matching activity in Data Quality Services in action!

Posted on January 7, 2013 by Paras Doshi • Posted in Microsoft Business Intelligence, Data Quality Services, Master Data Services • Tagged sql, Database, Data set, Data Warehousing, Master data management, Knowledge base, DQS, Data quality, Data Quality Services, Reference data, 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)

Paras Doshi – Blog

Tag Archives: Database

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

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

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

Found something interesting by exploring a “List of companies by revenue” Data Set:

Quick Note about SAS’s acroynm SEMMA: