HowTo: Setup SQL Server Linked Server to MySQL

This week I had to create a linked server to a MySQL database on SQL Express (development environment)….whoohooo!…NOT
But ok, it’s possible. Found this step-by-step blogpost.

Start by downloading the MySQL Connector/ODBC.
(You need to register first…)

The funny thing is, I had to enter a full provider string (instead of what the blog mentioned):

Driver={MySQL ODBC 5.1 driver};Server=;Port=3306;Database=;Uid=;Pwd=

(Port is optional and default btw)

The result:

Conclusion: It’s easy once you’ve done it before 🙂
@webhosting companies: Want reports or a PowerPivot on that thing?
Send me a mail! @BumbleBI (Dutch)

Excel Spatial Visualizer Add-In

My colleague Karlo Bartels made a very nice Excel 2010 Add-In:

To easily facilitate the process of displaying spatial objects in Excel, I created an Excel add-in that allows you to select a range of cells containing coordinates and show them on a map. To do this, the add-in accesses the Bing Maps SOAP Services. This is a set of programmable SOAP services that allow you to match addresses to a map (and much more). You will need a Bing Maps Application ID to access these services.

Check out his blog/article here (source and examples included!).

Columnstore Indexes for SQL Server 11.0 release (code named “Denali”)

Microsoft released a new whitepaper on Columnstore Indexes:

Summary: The SQL Server 11.0 release (code named “Denali”) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. This new index, combined with enhanced query processing features, improves data warehouse query performance by hundreds to thousands of times in some cases, and can routinely give a tenfold speedup for a broad range of decision support queries. This can allow end users to get more business value from their data through fast, interactive exploration. IT workers can reduce development costs and ETL times since columnstore indexes limit or eliminate the need to rely on pre-built aggregates, including user-defined summary tables, and indexed (materialized) views. Furthermore, columnstore indexes can greatly improve ROLAP performance, making ROLAP more attractive.

SQL Server Denali columnstore indexes are “pure” column stores, not a hybrid, because they store all data for separate columns on separate pages. This improves I/O scan performance and buffer hit rates. SQL Server is the first major database product to support a pure columnstore index.

Download the whitepaper here.

Welcome BI Semantic Model (BISM)!

Frank from MIB
Microsoft announced the BI Semantic Model @ the PASS Summit.
Chris Webb was there and he gave his opinion on his Blog

The quotes below are from Chris Webb’s blogpost here. Thanks for that Chris!


The BISM – BI Semantic Model – is the name for the new type of Analysis Services database that gets created when you publish a PowerPivot model up to the server. It’s SSAS running in the special in-memory mode, and SSAS instances will either work in this mode or in regular MOLAP mode. In Denali we’ll be able to install a standalone instance of SSAS running in in-memory, BISM mode without needing Sharepoint around.

MS are clear that BISM is the priority now. While MOLAP SSAS isn’t deprecated, the efforts of the SSAS dev team are concentrated on BISM and PowerPivot and we shouldn’t expect any radical new changes. I asked why they couldn’t have just kept SSAS as it is today and bolted Vertipaq storage on as a new storage mode (we will, of course, be able to use SSAS cubes in ROLAP mode against SQL Server/PDW with Vertipaq relational indexes) but I was told that it was seriously considered, but didn’t turn out to be easy to implement at all. The other question I asked was why they are abandoning the concept of cubes and explicitly multidimensional ideas in favour of a simpler, relational model, and they told me that it’s because multidimensionality put a lot of people off; I can see that’s true – yes, a lot of people have been converted to the OLAP cause over the years, but we all know that many relational people just can’t stomach/understand SSAS today. The vast majority of people who use SSRS do so directly on relational sources, and as we know while there’s a great demand for things like Report Builder, Microsoft has had nothing that worked really well to enable end user reporting in SSRS; BISM, as I said, is aimed at solving this problem.

AdventureWorks for SQL Server Denali CTP1

Microsoft released the new AdventureWorks for SQL Server Denali (CTP1, aka SQL Server 2011) yesterday!

The release notes:

This release is dedicated to the Adventureworks sample databases that ship for Microsoft SQL Server code-named “Denali”, Community Technical Preview 1 (CTP1). This release uses the AdventureWorks2008R2 and AdventureWorksDW2008R2 sample databases. They have been upgraded to the “Denali” CTP1 version and the FILESTREAM properties have been removed from the Production.Document table. No additional schema or data changes have been made.

Note: These databases cannot be installed on any version of SQL Server other than SQL Server code-named “Denali” CTP1.

Get it here.

SQLPASS Summit Keynote summary

Google told me, here’s a great SQLPASS Summit Keynote summary by Brent Ozar:

#SQLPASS Summit Keynote

9:37 – The changes in SQL Server Integration Services will be huge, he says – big improvements in management and servers. R2 was a big release for reporting & analytics, plus Office 2010 focused on managed self-service analytics. Excel users were empowered to build BI applications, and PowerPivot was embedded into SharePoint.

9:39 – Project Crescent is a new web-based reporting system letting end users tell their own stories about the data. Amir Netz is demoing PowerPivot, then saying that’s good, but we need something bigger for enterprise datastores. The new BI Development Studio, running on top of Visual Studio 2010 Premium, hooks into the same column-oriented storage engine that PowerPivot used, but now you can use it on a server so you get centralized security and bigger horsepower.

9:49 – SQL Server Denali will have columnar indexes built into the database engine. Columnar indexes are what makes PowerPivot so insanely fast. More on this later.

Thanks Brent!

Microsoft SQL Server code-named ‘Denali’ – Community Technology Preview 1 (CTP1)

Microsoft released the CTP1 for SQL Server code-named ‘Denali‘ (aka SQL Server 2011?) yesterday.

Get it here.

This next version of SQL Server is expected to be ready for launch at the beginning of third quarter in 2011. One thing is certain, BIDS will receive a GUI-update that will look the same as Visual Studio 2010 🙂