Gartner’s Magic Quadrant for Advanced Analytics Platforms 2014

Gartner’s Magic Quadrant for Advanced Analytics Platforms 2014

Predictive analytics and other categories of advanced analytics are becoming a major factor in the analytics market. We evaluate the leading providers of advanced analytics platforms that are used to build solutions from scratch.

It’s such a pivotal moment for data scientists and the growing open-source R community that Gartner has embarked on its first ever Magic Quadrant for Advanced Analytics Platforms. Gartner estimates advanced analytics to be a $2 billion market that spans a broad array of industries globally, and ‘Gartner predicts business intelligence and analytics will remain top focus for CIOs Through 2017.’ We believe that this new Magic Quadrant puts a spotlight on big data as the great analytics disruptor which we feel highlights the need for solutions like Revolution Analytics’ that are built upon a flexible, open platform, and designed for today’s Big Data Big Analytics challenges.” — Dave Rich

Magic Quadrant for Advanced Analytics Platforms 2014

Gartner’s Magic Quadrant for Business Intelligence and Analytics Platforms 2014

Gartner’s Magic Quadrant for Business Intelligence and Analytics Platforms 2014

Microsoft offers a competitive and expanding set of BI and analytics capabilities, packaging and pricing that appeal to Microsoft developers, independent distributors and now to business users. It does so through a combination of enhanced BI and data discovery capabilities in Office (Excel) 2013, data management capabilities in SQL Server, and collaboration, content, and user and usage management capabilities in SharePoint.

Magic Quadrant for Business Intelligence and Analytics Platforms 2014

More info:
Gartner’s Magic Quadrant for Business Intelligence and Analytics Platforms 2014

Gartner’s Magic Quadrant for BI Platforms 2013

Power Map is released (Office 2013 SP1)!

As of yesterday, Power Map was released as part of Office 2013 SP1.

Power Map for Excel - Office 2013 SP1

More info about the announcement:
Power Map for Excel Now Generally Available, Automatically Updated for Office 365

Announcing the release of Service Pack 1 for Office 2013 and SharePoint 2013

(thanks Chris Webb for the headsup!)

One important point to note here is that Power Map will only be available to Office 365 customers. If you have a standalone version of Excel, or have a regular (ie not Office 365) Professional Plus license, you will no longer be able to use Power Map. See:

Power Map for Excel New!

If you have a subscription for Microsoft Office 365 ProPlus, Office 365 Midsize Business, or for the Office 365 Enterprise E3 or E4 plans, you’ll have access to Power Map as part of the self-service business intelligence tools. To determine which subscription you have, see Office 365 ProPlus and Compare All Office 365 for Business Plans.

If you have Office 2013 Professional Plus or a standalone version of Excel 2013, you’ll be able to download and use the Power Map Preview for Excel 2013 until May 30, 2014. After that date, the preview will no longer work in any non-Office 365 subscription version of Excel.

To learn more about Power Map, see Get started with Power Map.

Join us at LinkedIn!
Don’t forget to join the ‘Microsoft Power BI‘ group on LinkedIn and stay updated!:

Power BI for Office 365 update

The SQL Server blog posted a new blog post about the new features of Power BI

Microsoft Updates Power BI for Office 365 Preview with New Natural Language Search, Mapping Capabilities

Today we’re pleased to announce the addition of significant new features to the Power BI for Office 365 preview, including natural language search with Q&A and improved experiences in two preview add-ins for Excel with 3D mapping visualizations through Power Map and improved data search in Power Query.

Power Map

Search-driven data visualization with Q&A, want to see an example?

The full blog post about Power BI’s new features can be found here.

Join us at LinkedIn!
Don’t forget to join the ‘Microsoft Power BI‘ group on LinkedIn and stay updated!:

SSIS Anchor Modeling example (tutorial)

This Anchor Modeling SSIS example is brought to you by:

Clint Huijbers (BumbleBI IT)

Bas van den Berg (C2H)

The first version of the demo was created by Bas and shared on

Tutorial Video by Bas
Advice you to watch it first 🙂


New version of the SSIS package
The fast performing package (final step in his tutorial) was rebuild by Clint and is described below:

To download the SSIS package and T-SQL scripts, click here.

The Anchor Model (AM)
The AM is about telephone calls, that includes two Anchors (Call and Phone), two Ties and three Attributes:

Anchor Model with mnemonics

Anchor Model without mnemonics

How to install

1) Create a new database called ‘AM_SSIS_Example

2) Create all AM-objects by executing the script: ‘Generated SQL Script by AM Online Modeler.sql

3) Generate source data by executing the script: ‘Create dbo_Calls table (source).sql
Give it a few minutes, it will generate 691.200 rows

The table dbo.Calls is already filled with demo data:

4) Create a Batch-table for metadata by executing the script: ‘Create BA_Batch table.sql

The database should now have a couple of tables (also Views, SPs and UDFs):

5) Open the SSIS package in BIDS/SSDT and execute the package

The SSIS package demos two scenarios:
#1 – Insert records by using the INSERT-trigger on view dbo.lPhone
#2 – Insert records directly into the Anchor and Attributes
(by using the fast load / bulk insert and parallel processing of the Attributes)


Raw File
A Raw File Source is used to temporary store and quickly re-load the full dataset, located in ‘C:\Temp\AM_SSIS_Example_RAW_FILE‘. Important: make sure to manually create/verify this Raw File. You’ll be able to find it here:


Open the ‘Raw File Destination Editor‘-window (right-click and ‘Edit’) and verify the Raw File’s location:


6) Watch the show


7) Verify the results by executing the script: ‘Verify the results.sql


8) Increase the volume!
Would you like to re-process all data and perhaps with a lot more records? 😀
Truncate all tables by executing the script: ‘Truncate and delete all data.sql’
Now, drop the table dbo.Calls.
Execute the script ‘Create dbo_Calls table (source).sql‘ again, but this time increase the variable @EndDateTime with just a couple of days.
Have fun!


Data Vault loading automation using Pentaho Data Integration

It’s a completely different ball-game, but open-source BI/DWH is not new.
I found this full-size demo environment based on MySQL, Pentaho and Pentaho Data Integration (ETL-tool)

More info, click here.

PDI Data Vault framework

A metadata driven ‘tool’ to automate loading a designed Data Vault. It consists of a set of Pentaho Data Integration and database objects. At the moment the version for MySQL includes the latest developments.
The PostgreSQL and Oracle version will be published later.

Thel Virtual Machine (VMware) is a 64 bit Ubuntu Server 12.04, with MySQL (Percona Server) as the database and PDI version 4.4.0 CE.

Version management is accomplished by Git (PDI objects) and neXtep (database objects).

User/passwd : percona/percona
MySQL user/passwd : root/percona
neXtep user/passwd : nextep_user/nextep_user

More info, click here.

A possible architecture:


Thanks to Kasper de Graaf and Aly Hollander:

Review: Twitter Search functionality for SQL Server (by SQL# – SQLsharp)

SQL# (SQLsharp)
Expanding the capabilities of T-SQL

SQL# is a small but extensive library of useful functions to help you code more powerful SQL Stored Procedures and User-Defined Functions.

From a Social BI’s perspective, I wanted to review the new Twitter Search functionality of SQL# (SQLsharp), which is available in the Full Version of SQL#.

System specifications:
This review was performed on a laptop with Windows 7 and SQL Server 2008R2 installed on it. Since SQL# uses a lot of the .NET framework and extra coding in DLLs, this should also work on SQL Server 2012 (and SSDT).

Solomon Rutzky did a great job by adding the Twitterizer .Net library (discontinued) and Twitter Search functionality to SQL# in this new version. For documentation about SQL#, click here.

Twitter – SearchTweets
The Full Version of SQL# provides 65 more functions than the Free Version, and it includes the Twitter: SearchTweets. An important note here, if you would only like to send tweets (twitter updates) and use other ‘basic’ Twitter functionalities, the Free Version would suffice:

SQL# – Free Version

Twitter: BlockUser, CreateFavorite, DestroyDirectMessage, DestroyFavorite, DestroyStatus, FollowUser, GetBlocks, GetFavorites, GetFollowers, GetFriends, GetHomeTimeline, GetMentions, GetMessages, GetRetweetedBy, GetRetweets, GetRetweetsOfMe, GetSentMessages, GetStatus, GetUser, GetUserTimeline, Retweet, SendDirectMessage, UnBlockUser, UnFollowUser, Update, xAuth

SQL# – Full Version

Twitter: SearchTweets

For more info about SQL# and perhaps to download the free version of SQL#, go to the website

Setup your Twitter Application:
A prerequisite is to have a Twitter Application already setup and connected to your Twitter-account. You’ll need to have the ‘Consumer key‘, ‘Consumer secret‘, ‘Access token‘ and ‘Access token secret‘.

Here’s HowTo to setup Twitter, the Twitter application and fetch the required keys:
Documentation: Twitter Setup
How to setup a Twitter Application (API)

So I’ve setup my Twitter Application called ‘PowerPivotBook‘ after the self-study course I wrote earlier:

Install SQL# (by executing the provided *.sql file):
To install SQL#, just open the *.sql-file in SQL Server Management Studio (SSMS, or SSDT) and execute it:


Set Security to External Access first
After installing SQL#, you’ll first need to set the security of Twitterizer to have ‘external access’. Otherwise you’ll be getting this error:

Execute this SQL-statement to set the security to ‘external access’:
EXEC SQL#.SQLsharp_SetSecurity 2, 'SQL#.Twitterizer'

The result:

Twitter Update functionality (send a Tweet via SSMS):
It’s time to send out a test Tweet and let the world know we’re alive 🙂

DECLARE @ConsumerKey NVARCHAR(100),
@ConsumerSecret NVARCHAR(100),
@AccessToken NVARCHAR(100),
@AccessTokenSecret NVARCHAR(100)

SELECT @ConsumerKey = '', //---Needs to be filled in.
@ConsumerSecret = '', //---Needs to be filled in.
@AccessToken = '', //---Needs to be filled in.
@AccessTokenSecret = '' //---Needs to be filled in.

SELECT SQL#.Twitter_Update(@ConsumerKey, @ConsumerSecret,
@AccessToken, @AccessTokenSecret, 'TweetTest - '+CAST(GETDATE() AS VARCHAR(100)), null, null, null)


The result:


SQL#.Twitter_SearchTweets() (TVF)
Now it’s time to use the Twitter Search functionality!

DECLARE @ConsumerKey NVARCHAR(100),
@ConsumerSecret NVARCHAR(100),
@AccessToken NVARCHAR(100),
@AccessTokenSecret NVARCHAR(100)

SELECT @ConsumerKey = '', //---Needs to be filled in.
@ConsumerSecret = '', //---Needs to be filled in.
@AccessToken = '', //---Needs to be filled in.
@AccessTokenSecret = '' //---Needs to be filled in.

SELECT * FROM SQL#.Twitter_SearchTweets(@ConsumerKey, @ConsumerSecret,
@AccessToken, @AccessTokenSecret, 'PowerPivot', NULL)

The results:

Optional parameters
You’re able to minimize your search area by supplying optional parameters, like for example geo-location (geocode), language (lang) or max number of tweets (count):

DECLARE @Params SQL#.Type_HashTable
SET @Params = ''
SET @Params = @Params.AddItem('count', '100') --Max = 100
SET @Params = @Params.AddItem('lang', 'NL') --LanguageCode (2 letters, ISO2)
SET @Params = @Params.AddItem('geocode','52.3708305359,4.9040098190,50km') --Geocode for 'Amsterdam, NL'

SELECT * FROM SQL#.Twitter_SearchTweets(@ConsumerKey, @ConsumerSecret,
@AccessToken, @AccessTokenSecret, 'weer', @Params)


SQL#.Twitter_SearchTweets() (TVF) Return Table specs:

[StatusID] [bigint] NULL,
[Created] [datetime] NULL,
[InReplyToStatusID] [bigint] NULL,
[InReplyToUserID] [int] NULL,
[IsFavorited] [bit] NULL,
[IsTruncated] [bit] NULL,
[Source] [nvarchar](200) NULL,
[StatusText] [nvarchar](200) NULL,
[RecipientID] [int] NULL,
[TimeZone] [nvarchar](100) NULL,
[ScreenName] [nvarchar](100) NULL,
[UserName] [nvarchar](100) NULL,
[UserID] [int] NULL,
[Location] [nvarchar](100) NULL,
[PlaceID] [nvarchar](50) NULL,
[PlaceName] [nvarchar](500) NULL,
[PlaceFullName] [nvarchar](500) NULL,
[PlaceType] [nvarchar](500) NULL,
[PlaceCountry] [nvarchar](500) NULL,
[PlaceLatitude] [float] NULL,
[PlaceLongitude] [float] NULL,
[RateLimit] [int] NULL,
[RateLimitRemaining] [int] NULL,
[RateLimitReset] [datetime] NULL

You’ll be able to fetch the results and feed it into your ETL-process. Social BI is just a few steps away 🙂

SQL# is a great addition to your cookbook and/or standard functionalities of SQL Server.

Free Version
If you would like to build a TweetBot that tweets automated updates, like for example job vacancies or weather updates, the Free Version of SQL# is a great toolkit. It’s free and easy to install, but as a best practice for security or practical reasons, create a dedicated (new) database for it or even a different SQL Server instance.

Full Version
Today’s BI is all about gathering social data, an easy way to add twitter search data to your BI-stack is by using the SQL# Full Version. Of course an experienced .NET developer can easily set things up with the Twitterizer-kit, but keep in mind that Twitter makes changes to its API and that requires maintenance. Nevertheless, for US$ 200,- (about 2~3 times an average hourly rate) you’ll be getting a lot more functionalities.