"SYLK: File format is not valid" error message when you open file

Before deciding to use ID as a column name, consider this scenario when dealing with CSV files…

"SYLK: File format is not valid" error message when you open file

"SYLK: File format is not valid" error message when you open file

SAP Data Services | A small notes. . .

Some information on SAP Data Services here, and a refresher on Databases and Data Warehousing.

Also, an interesting post on ETL vs ELT.  Would it be faster to use ETL to transform your data before loading, or would it be faster to just copy your data directly to the warehouse and manage it inside?

Perhaps this is the role the new FileTable type in SQL 2012 will play for you.  Why not FTP your CSV files directly into SQL as blobs, and manage the transformations within?

Maybe not the best example, but it does centralize things a bit more…

SAP Data Services | A small notes. . .

New programming features in SQL 2012

SQL Server 2012 is released this weekend.  SQL 2012, you can now use select-style statements to return customized results from a single stored procedure. 

EXEC uspGetEmployeeManagers 16 WITH RESULT SETS ( ([Reporting Level] int NOT NULL, [ID of Employee] int NOT NULL, [Employee First Name] nvarchar(50) NOT NULL, [Employee Last Name] nvarchar(50) NOT NULL, [Employee ID of Manager] nvarchar(max) NOT NULL, [Manager First Name] nvarchar(50) NOT NULL, [Manager Last Name] nvarchar(50) NOT NULL ) );

EXECUTE (Transact-SQL)

Custom paging using an ORDER BY clause

-- Skip 0 rows and return only the first 10 rows from the sorted result set. SELECT DepartmentID, Name, GroupName FROM HumanResources.Department ORDER BY DepartmentID OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Database sequence numbers

Throw to negate the need for custom messages with RAISERROR

C#/VB style Format to format dates, times, numbers

CONCAT to join 2 strings

OVER clause for windowed summarization / rolling totals

PARSE to convert strings to dates, currencies, etc

TRY_CONVERT to avoid errors when converting data types

TRY_PARSE to determine if strings = data types

DATEFROMPARTS to take a year, month and day and create a date

CHOOSE to return an index item from a list of selections. (Arrays in SQL?)

IIF for fast conditional checks.

Bob's blog - SQL 2008 R2 MDS Easter Egg - wBob

Some helpful tools coming out of SQL MDS, including regex and XSLT straight from T-SQL.

I've been having a play with SQL 2008 R2 and its new Master Data Services (MDS).  I was pleased to see it adds some additional functions when you create a new MDS database.  These include mdq.Split and mdq.RegexReplace which implement handy Regular Expression functions.  Most of these are documented at: http://msdn.microsoft.com/en-us/library/ee633712(SQL.105).aspx

I also discovered a scalar function called mdq.XmlTransform which allows you to use XSLT:

Bob's blog - SQL 2008 R2 MDS Easter Egg - wBob

Microsoft SQL Server 2012 Virtual Labs Available To You Online, plus many more SQL Server 2012 resources - Microsoft SMS&P Partner Community Blog - By Eric Ligman - Site Home - MSDN Blogs

Microsoft SQL Server 2012 Virtual Labs Available To You Online, plus many more SQL Server 2012 resources - Microsoft SMS&P Partner Community Blog - By Eric Ligman - Site Home - MSDN Blogs: "We’ve just come off the exciting SQL Server 201 Virtual Launch Event and the buzz about SQL Server 2012 continues to grow! Following on to my Windows Small Business Server 2011 TechNet Virtual Labs Available To You Online, plus more Small Business Server 2011 resources and SharePoint Server 2010 MSDN Virtual Labs Available To You Online, plus more SharePoint 2010 resources posts, I thought I would keep the momentum going and share out some SQL Server 2012 Virtual Labs that we have available to you online, as well as some additional SQL Server 2012 resources that you can use:"

'via Blog this'

SSMS puts extra lines in the result set in grid view for sp_helptext | Microsoft Connect

SQL 2012 bug and a couple alternatives that might work instead.

SSMS puts extra lines in the result set in grid view for sp_helptext | Microsoft Connect: "SSMS puts extra lines in the result set in grid view for sp_helptext"

http://www.sqlmusings.com/2009/05/17/how-to-get-definition-for-stored-procedures-udfs-and-triggers-using-t-sql/

'via Blog this'

50 Free Social Media Tools You Can't Live Without

Tracking your social media campaign?  Here are some tools to look at.

A couple years ago, Jay Baer wrote a great blog post called ‘The 39 social media tools I’ll use today’ which was an all-in-one toolkit for social media marketers (and still is).

A lot has changed in the two years since that post was published so here is a ’2012 remix’ featuring 50 (mostly free) tools you can use on a daily basis.

Whether you are just starting out in the social media arena or have been at it for a few years, this will hopefully be a handy resource. So, let’s serve ‘em up

50 Free Social Media Tools You Can't Live Without

CanvasMap Prototype = HTML5 Canvas + Mapping

Looking for a Windows 8 / HTML5 map control?

CanvasMap is a JavaScript map visualization control (similar to Bing Maps or Google Maps) that is built entirely on top of using the HTML5 Canvas element to render the map. Currently, neither Bing Maps or Google Maps use the Canvas element to render maps, so I thought I’d do this to see how the performance might compare. I know there are a few alternatives already, but I thought I’d have some fun trying to build my own.

CanvasMap Prototype = HTML5 Canvas + Mapping

VDS Technologies - Map Data and GIS Tools

Excellent repository of shape files for maps.

VDS Technologies - Map Data and GIS Tools

The 2012 Microsoft Product Roadmap -- Redmond Channel Partner

One fork in the road may be a single platform for Windows Phone & Windows 8 apps, or the ability to run WP7.5 apps on Win8.

Which would be nice.  Kind of like running iPhone apps on the iPad.

The star of last year's product roadmap was Office 365. Microsoft this year looks likely to pivot from the public cloud to the private cloud, while also shipping staples like SQL Server and releasing major betas -- and possibly final versions -- of blockbusters including Windows 8 tablet, client and server, and Office 15.

The 2012 Microsoft Product Roadmap -- Redmond Channel Partner

Geocoding in Excel

Need a fast way of getting lat/long values for a batch of addresses in Excel?

A lot of folks have to geocode data that they get from various folks in their organization, and it often makes its way in to Excel.  Of course, you can batch geocode using Bing Maps Services, but in some scenarios it is a lot more convenient or reasonable to use Excel.

Westin's Technical Log

Another Data Mining Blog

Another Data Mining Blog:

Excellent examples of using R with SQL Server, from a milestone winner in the $3 million dollar Heritage Provider Network health prize.

'via Blog this'

Y2K 0229–Azure’s down?

Don’t forget to ensure that your test cases contain time-based events, like leap years and Daylight Savings.   Not that it would have helped much, since it was a cert expiry that caused the issue.  The moral of the story?  Ensure critical apps have a disaster recovery plan that doesn’t include a single source of failure (cloud provider).

When the clocks struck midnight, things quickly got janky, and a cloud-system domino effect took charge. A large number of Western Hemisphere sites and the U.K. government's G-Cloud CloudStore were among the many stopped cold by the outage. Microsoft has been retracing its steps in finding out what exactly happened and hasn't said very much yet, although it did report in an Azure team blog that the problem has "mostly" been fixed.

IT in Canada - Canada's Only Integrated Social Media News Network

SQL Azure Upgrade

SQL Azure Upgrade Advisor?  Here you go...

Get your SQL Server database ready for SQL Azure! - SQL Server Team Blog - Site Home - TechNet Blogs: "One of our lab project teams was pretty busy while the rest of us were taking a break between Christmas and New Year’s here in Redmond. On January 3rd, their new lab went live: Microsoft Codename "SQL Azure Compatibility Assessment". This lab is an experimental cloud service targeted at database developers and admins who are considering migrating existing SQL Server databases into SQL Azure databases and want to know how easy or hard this process is going to be. SQL Azure, as you may already know, is a highly available and scalable cloud database service delivered from Microsoft’s datacenters. This lab helps in getting your SQL Server database cloud-ready by pointing out schema objects which are not supported in SQL Azure and need to be changed prior to the migration process. So if you are thinking about the cloud again coming out of a strong holiday season where some of your on-premises databases were getting tough to manage due to increased load, this lab may be worth checking out."

'via Blog this'

Based on the recent training course I took using SQL Azure, the backend is a SQL 2005 "Express" style instance.  Lack of some key "newer" features that use the CLR (datatypes like spatial and xml) may quickly exclude some of your more complex databases from being migrated to the cloud.

Million Song Dataset | scaling MIR research

Looking for some Big Data to test out a project?  This one looks like fun.

Million Song Dataset | scaling MIR research: "The Million Song Dataset is a freely-available collection of audio features and metadata for a million contemporary popular music tracks.


Its purposes are:


To encourage research on algorithms that scale to commercial sizes
To provide a reference dataset for evaluating research
As a shortcut alternative to creating a large dataset with APIs (e.g. The Echo Nest's)
To help new researchers get started in the MIR field
The core of the dataset is the feature analysis and metadata for one million songs, provided by The Echo Nest. The dataset does not include any audio, only the derived features. Note, however, that sample audio can be fetched from services like 7digital, using code we provide.


The Million Song Dataset is also a cluster of complementary datasets contributed by the community:


SecondHandSongs dataset -> cover songs
musiXmatch dataset -> lyrics
Last.fm dataset -> song-level tags and similarity
Taste Profile subset -> user data


The Million Song Dataset started as a collaborative project between The Echo Nest and LabROSA. It was supported in part by the NSF."


'via Blog this'

NoSQL Data Modeling Techniques « Highly Scalable Blog

NoSQL, or DIY SQL is the most common technique for blasting out distributed data stores. 

This article is a great overview of the types of NoSQL designs out there, and ways to model the data.

NoSQL databases are often compared by various non-functional criteria, such as scalability, performance, and consistency. This aspect of NoSQL is well-studied both in practice and theory because specific non-functional properties are often the main justification for NoSQL usage and fundamental results on distributed systems like CAP theorem are well applicable to the NoSQL systems.  At the same time, NoSQL data modeling is not so well studied and lacks of systematic theory like in relational databases. In this article I provide a short comparison of NoSQL system families from the data modeling point of view and digest several common modeling techniques.

NoSQL Data Modeling Techniques « Highly Scalable Blog

Top analytic blogs and websites, with trending information - AnalyticBridge

I recently signed up for mailings from Analyticbridge, the social network for analytic professionals.   It seems like daily emails are the norm, which I’ll have to setup a rule to move someplace.  In the meantime, they just released a list of the top analytic blogs and websites.

Worth checking out.

Top analytic blogs and websites, with trending information - AnalyticBridge