Big Piles Of Data - Facebook, LinkedIN etc

I've had the chance to work onsite at Facebook for a few weeks with their BI group doing a POC for Informatica's ETL tools against their platforms.

It was an interesting experience and I enjoyed the great food and hospitality of my Facebook colleagues. While I'm still not a fan of Facebook as a user I do admire the energy and fast paced environment. It is the type of place that breeds creativity and out of the box thinking. The Meno Park campus vibrates with excitement.

Some of the mottos stenciled everywhere are: "Move fast and break things!"    "HACK" is plastered everywhere and is the paradigm for their development mentality. "Done is better than perfect." Think of it like Agile after downing a couple of Red Bulls!

They typically show up at 10am and work until ..... its gets done!

With all the innovation and creativity Facebook still has to wrestle with down to earth problems.  How do I get data from point A to B?  What does the data mean?  Is the quality of the data good enough to make decisions and spend resources on?

The scale that they work on is LARGE.   How large?  The main HDFS is pushing 110PB and growing fast. They just passed 1 Billion active users and have plans to expand the usage as much as possible across the globe.  Zuckerberger gave every employee a little Red Book with some thoughts on this milestone. The basic theme is "1% is not done" and we've only just started with 1% of the population.

Back to reality; the fact is that they have discovered that Big Data is really just "Big Piles Of Data."  Which is totally useless until you extract value from it. Relationships, likes, dislikes, needs, desires, and dreams.

And the fact that everyone is distracted by Hype around Big Data is not lost on some of them.  I think that companies like Cloudera, Hortonworks, MapR, and other wannabes for "The Hadoop Standard" are the future Sybase, Informix, and Borlands of this wave of technology.

Yes there is room for these folks to make money and some will.  They will leverage their VC money and gain market share then cash out and move to the Next Gig.  But I doubt their products will have a lasting legacy. Market consolidation is not only a certainty it has already begun.

Ultimately, as the Facebook folks realize,  Hadoop is nothing but a cheap and commodity  technology to store Big Piles of data.  As more businesses come off the euphoria of Big Data Hype and realize it is NOT the silver bullet to solve their problems, more traditional software companies like Teradata / Aster, Oracle, IBM, Teracotta/Software AG and other data analytics companies will make large inroads by supplying software and systems to perform useful business analytics.

Watch this trend in the next 12 to 18 months.  I predict it will be an exciting time on the other side of the Big Data Wave.

Cheers,

Dave

The Seldon Vault is not dead...stop using term Big Data

It has been awhile since the last postings.
I've been busy with new projects and major clients.

My upcoming posts are going to start talking about HLH data .... aka BIG DATA.
The term "Big Data" is MEANINGLESS!!!!  So I'll propose a different vernacular that more accurately describes the problem and why Hadoop and noSQL platforms fit the HLH area.
HLH = High volume, Low density, High value   

More to follow!

The other area I'm interested in is the language Scala.

Dave

Locate an Object across all databases on server

How many times have you wanted to quickly find an object on a server?

You don't know which database it's located in and there are 30+ databases on the server.

The following is a quick snippet that does this task.  It's not very sophisticated but gets the job done.

Dave

--
--  Look for the object ColumnButtons across the server
--     Dave Winters 7/2011
use master
go
exec sp_msforeachdb @command1 = 'use [?];
  if object_id(''ColumnButtons'') > 0 print ''Found in:  ?''; '
--

Sorting NULL columns in SQL Server.

Recently I was working with a report developer who was struggling with NULL date-time values in their report output.  The problem was this BI programmer was used to working with another RDBMS that had control settings specifying the sort order for NULLs.  By SQL convention NULL are ordered first.

Unfortunately the case in the data had “end dates” for when policies come up for renewal. The desired output was to have policies without valid end dates(NULL) to be last in the output.  For this article I’ll use Microsoft’s sample database AdventWorks. The table we’ll use for our report is ProductCostHistory. The EndDate column has NULLs that we want to be listed last in our report.

If we simply query the data with an ORDER-BY we’ll get the following result:
SELECT *
  FROM [AdventureWorks].[Production].[ProductCostHistory]
  ORDER BY [EndDate]

ProductID   StartDate               EndDate                 StandardCost       ModifiedDate

----------- ----------------------- ----------------------- ------------------ -----------------------
707         2003-07-01 00:00:00.000 NULL                    13.0863            2003-06-17 00:00:00.000
708         2003-07-01 00:00:00.000 NULL                    13.0863            2003-06-17 00:00:00.000
              --Data edited out for clarity---
860         2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 9.7136             2003-06-30 00:00:00.000
860         2003-07-01 00:00:00.000 NULL                    9.1593             2003-06-17 00:00:00.000

As expected the NULL EndDate values come first in the list.

My report writer buddy came up some pretty clever work around involving temp tables and self-joins.  Not very clear and poorly performing to say the least.  So I showed him the following as the first  replacement solution to placing NULLs at the end.

SELECT *

  FROM [AdventureWorks].[Production].[ProductCostHistory]
  ORDER BY isnull([EndDate],'12/31/9999')
ProductID   StartDate               EndDate                 StandardCost       ModifiedDate
----------- ----------------------- ----------------------- ------------------ -----------------------
707         2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 12.0278            2002-06-30 00:00:00.000
708         2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 12.0278            2002-06-30 00:00:00.000
              --Data edited out for clarity---
860         2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 9.7136             2003-06-30 00:00:00.000
860         2003-07-01 00:00:00.000 NULL                    9.1593             2003-06-17 00:00:00.000

Exactly what we wanted!  BUT, this assumes that the date value ‘12/31/9999’ is beyond any other date in the table. Many times this solution is a safe assumption. This may NOT always be the case. I’ve seen databases where high values are sometimes used to indicate “out of range” or “present but invalid” etc.

The next solution addresses this condition by finding the MAX value in the column then adding an appropriate delta value to place the column after this maximum value. Here is the solution for our product cost history example:

DECLARE @maxEndDate DATETIME

SELECT @maxEndDate = MAX(EndDate)+1
     FROM [AdventureWorks].[Production].[ProductCostHistory]
SELECT *
  FROM [AdventureWorks].[Production].[ProductCostHistory]
  ORDER BY  isnull([EndDate],@maxEndDate)
ProductID   StartDate               EndDate                 StandardCost       ModifiedDate
----------- ----------------------- ----------------------- ------------------ -----------------------
707         2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 12.0278            2002-06-30 00:00:00.000
708         2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 12.0278            2002-06-30 00:00:00.000
              --Data edited out for clarity---
860         2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 9.7136             2003-06-30 00:00:00.000
860         2003-07-01 00:00:00.000 NULL                    9.1593             2003-06-17 00:00:00.000

As expected the data is sorted in the correct order with the NULL values following the valid dates.

Now for what I think is a better solution and does not require hard coding a MAX(or Min) value or scanning through the data finding the last value. Here is my preferred solution to our date problem:
SELECT *
  FROM [AdventureWorks].[Production].[ProductCostHistory]
  ORDER BY (case when EndDate IS NULL then 1 else 0 end),EndDate
ProductID   StartDate               EndDate                 StandardCost       ModifiedDate
----------- ----------------------- ----------------------- ------------------ -----------------------
707         2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 12.0278            2002-06-30 00:00:00.000
708         2001-07-01 00:00:00.000 2002-06-30 00:00:00.000 12.0278            2002-06-30 00:00:00.000
              --Data edited out for clarity---
860         2002-07-01 00:00:00.000 2003-06-30 00:00:00.000 9.7136             2003-06-30 00:00:00.000
860         2003-07-01 00:00:00.000 NULL                    9.1593             2003-06-17 00:00:00.000

Look at this for a moment.  It is obvious but has a subtle requirement; the CASE statement must precede the column that contains NULL values. The benefit is that this will always work and requires no additional consideration for the range or contents of the column.

Conclusion:  While this may be a simple little trick you’d be surprised how many report writers and data consumers  struggle with exactly this problem.  The CASE solution can also be expanded to provide table sort orders based on column ranges instead of simply NULL versus non-NULL.
-Dave Winters