Kasper de Jonge BI Blog

Thanks to a Chris Webb post i found this great blog post on a new MS research project called the Vedea Project.

Vedea is a prototype of a new experimental language for creating interactive infographics, data visualizations and computational art.  It is designed to be accessible to people who are either new to programming or whose primary domain of expertise is something other than programming.  We wanted to give those users a tool that they can use to realize their own vision and visualizations without having to engage skilled programmers, but have it be an environment that skilled programmers would not find limiting.

Ok what does this mean? Developers get great tools to create all kinds of visualizations from code

The Microsoft Visualization Language is built on .net 4.0’s new Dynamic Language Runtime.  This gives us some important advantages over more traditional language implementations.  Syntactically, the Vedea language looks a lot like C#.  In its simplest form though, there are no class decorations – just a collection of functions.  You can introduce classes if you want to do object-oriented programming, but they are not required and your topmost functions aren’t wrapped in any of the syntactic trappings of a class.

A piece of sample code that really got me excited:

myData = DataSet(“mydata.csv”);
currentYear := slider.Value + 1900;
bubbles := from row in myData
where row.Year :== currentYear
select new Circle()
{
X = row.Latitude,
Y = row.Longitude,
Radius = row.Population * scalingFactor,
Fill = BlackBodyPalette(1., 1., row.DeltaCarbon)
};
Scene[“USMap”].Add(bubbles);

This gives some pretty serious possibilities to create all kind of visualizations, this could be huge for BI developers with some kind of programming background (like me )

Read the entire post with more background and samples on the blog of Martin R. Calsyn of MS Science:

http://blogs.msdn.com/martinca/archive/2009/12/03/introducing-the-microsoft-visualization-language.aspx

Great article about Collaborative BI, and what the added value of getting your BI to masses is. It talks not only about PowerPivot but also Qlikview, Dynistics and Gartner.

A must read article for all you BI consultants in the field.

Read the entire article here: http://np.netpublicator.com/netpublication/n67773854 (pdf download available)

Dan English wrote a great blog post about Analysis Services (SSAS) Processing and Aggregations with SSIS:

So you created an SSAS database solution and have deployed it to a production environment.  Now the data has accumulated and you need to take a look at moving to a incremental processing approach because you are either loading the data more regularly or the volume of data and processing does not meet your maintenance window anymore.  So what do you do?

Read the solution Dan proposes at his blog: http://denglishbi.spaces.live.com/blog/cns!CD3E77E793DF6178!2101.entry Great in depth information !

Thanks to the great discussion some of us had on Twitter and the follow up on the PowerPivotPro blog we now know a PowerPivot API will be available in the future:

Patience guys. The API will be there.

We are just crunched on time to ship V1.0 of PowerPivot (remember – just 18 months from SQL 2008). But we have the full intention of exposing the API in full.

As MS BI consultant i’m really looking forward to PowerPivot but as developer with C# affinity i’m really excited on the prospect of the PowerPivot API. Think of all the potential projects you could do!

At the MS Excel team blog they have a great blog post where a member of the SQL Server Analysis Services team, Ashvini Sharma, will tell us about the PowerPivot.
The post gives a comprehensive look at the entire PowerPivot product, from front to back and names all components.

Read the entire article here: http://blogs.msdn.com/excel/archive/2009/10/22/introducing-powerpivot.aspx

The big question i have is, will the VertiPaq engine have an API where developers can build there own frontend on PowerPivot… with loading, connection and analysing sources in memory.

The SQL Server magazine has posted an interview with Microsofts Donald Farmer, a great interview. It explains how PowerPivot (called Gemini in the post) will affect the end users and what the reason for existence is for PowerPivot and how it will affect us BI consultants, even with some Real world samples.  A great piece of info i was really waiting for.

A small excerpt:

SQL Server Magazine: How exactly are the services managed? And what does that mean for IT and end users?

Farmer: Let’s start with the story of one of the founding legends of Gemini: We were visiting a customer—a freight company. Somebody whispered in the IT manager’s ear, “You need to come and deal with a problem.” So the IT manager left for about an hour. The problem was that the cargo validation application had failed. What’s the cargo validation application? The IT manager had never heard of it. It turns out that this was a mission-critical application. Every cargo manager in the company was using it. The IT manager didn’t know it existed. Why not? It was an Excel spreadsheet. Somebody had built this application and shared it with other cargo managers. Soon every cargo manager in the organization had it. In fact, they wouldn’t ship a piece of cargo without it. And yet IT didn’t know it existed.

Gemini gives end users the power to build analytic applications, but when they deploy, share, and collaborate, IT can see what’s happening. IT will see the usage of any application that someone in the organization publishes. They’ll understand who published it, what the internals of that application are, what data sources it uses, and who else is using it. They’ll understand that it’s mission-critical. So IT understands that this is something they need to manage and control. But also maybe it’s something they need to secure, validate, and audit.

Read the entire article here: http://www.sqlmag.com/Articles/ArticleID/102613/pg/1/1.html

Microsoft just announced at the Microsoft SharePoint Conference 2009 in Las Vegas this morning that project codename Gemini will ship as “SQL Server PowerPivot for Excel” and “SQL Server PowerPivot for Sharepoint”. And immediately launched the new site http://www.powerpivot.com/.

Loads of information at the IT pro’s at PowerPivot datasheet.

• “SQL Server PowerPivot for Excel” Is the Excel plugin to get new data analysis features on the desktop, business users can load very large data sets into memory and use powerful relational capabilities to create advanced analytics applications.
• “SQL Server PowerPivot for Sharepoint”. Is when you deploy the excel plugin to SharePoint. PowerPivot encapsulates enterprise data to help centralize BI and data management while providing reliable access to it. The PowerPivot administrative dashboard even enables IT departments to monitor and manage shared applications for security, availability and performance. As you can see below:

The thing i’ve been waiting for to surface is the use of Silverlight in BI. Bart Czernicki has released a book describing the combination of BI and Silverlight, a thing I’m very interested in!

What will you learn from investing in this resource?

Covering the entire scope of BI and applying these concepts to Silverlight applications is simply not realistic in one single resource.  Even if I had the option of writing 750 pages or more, important facets of BI would be missed.  Therefore, I decided to focus on the presentation tier of Business Intelligence applications.   For example, I didn’t think it was fair to focus on the data and services tier with these technologies going through a rapid implementation and tooling evolution (RIA Services, WCF REST, Oslo, ADO.NET Data Services 1.5, etc.).  However, Silverlight’s rendering and client processing engine is mature enough to warrant a guide on how to implement client-side BI concepts.  Therefore, topics like visual intelligence, data visualizations, predictive analytics, collective intelligence, interactive tools, parallel computing, working with large data sets, etc., are covered in my book on the presentation tier.

This book has three intended audience segments and their goals:

• Silverlight Developers – Learn how to extend your Silverlight knowledge in real-world applications.  Learn the basics of Business Intelligence 2.0.
• Business Intelligence Professionals - Get a better understanding of how Silverlight can help you overcome some of the challenges to implement simple BI tools.
• Strategic Decision Makers (architects, CIO, technical director, etc.) – Understand if Silverlight is the right platform to deliver BI software.

Read the entire release post here:

Announcing Next Generation Business Intelligence Software with Silverlight

This is one book I will order asap!

Timo Elliott found a great best practices report from TDWI on “how to create and deploy effective metrics” (registration required). The introduction to the document explains the background:

Performance metrics are a powerful tool of organizational change. The adage “What gets measured, gets done,” is true. Companies that define objectives, establish goals, measure progress, reward achievement, and display the results for all to see can turbo-charge productivity and gracefully move an organization in a new direction.

Executives use performance metrics to define and communicate strategic objectives tailored to every individual and role in the organization. Managers use them to identify underperforming individuals or teams and guide them back on track. Employees use performance metrics to focus on what’s important and help them achieve goals defined in their personal performance plans.

But performance metrics are a double-edged sword. The wrong metrics can have unintended consequences: they can wreak havoc on organizational processes, demoralize employees, and undermine productivity and service levels. If the metrics don’t accurately translate the company’s strategy and goals into concrete actions that employees can take on a daily basis, the organization will flounder. Employees will work hard but have nothing to show for their efforts, leaving everyone tired and frustrated. In short, the company will be efficient but ineffective.

The document then goes into to look at lots of different aspects of creating and deploying KPIs and dashboards, a must read for any BI’er.

By accident I heard one of our sales people talk about showing data from a geographical location on a report. I immediately jumped in the discussion and told about the new Spatial datatype of SQL server and the reporting data region Map. Of course when talking to Sales people the immediatly asked for a demo. In this blog post my findings about Spatial data, Reporting map control and Bing maps. My starting point was Robert Brucker’s reporting blogpost, and try to recreate his demo. Had it up and running in no time.

The first thing i had to do was create a Geography data type, since this is the base of the Reporting map control. This was pretty easy since i had some test data with Latitude and Longitude as float in a table. All i had to do to converting these to a geography datatype was:

update Location set GeoPostion = geography::Point(Latitude, Longitude, 4326)

There are several ways of different ways to add point data in SQL Server 2008, i found them at this blog post: http://blog.colinmackay.net/archive/2008/02/07/1812.aspx

Next up was creating some kind of representable chart, i used the steps as described by Robert Brucker to create a report of my data, the one thing that immediatly caught my attention is that the Reporting map control using Bing maps is data aware. The Bing map automatically centers and zooms in at my locations (i used the Person table from Adventure Works to shoot this screenshot, as there are persons from around the globe he centers on the world).  I created a view of the data so i can change its source on a later stage:

Next up was choosing the visualization of the data, i wanted to create a analytical overview so i chose Bubble map:

Since i wanted to show some analytical data i changed the query to point to my Dutch data and added an amount field (which i filled ranomly with values 15 to 2000) using a top 50 of the Netherlands, you can see the data awareness again, the map switched to the Netherlands only.

In the data visualization step i selected the Bubble size property along with a colour scheme to represent my data:

Resulting in the following map in reporting services, so no programming necessary (except the Query of course):

It would be great if users could zoom in on the data .. luckily the data is a layer in reporting services so the data points are reporting objects where you can set properties like an Action:

So i turned my view into a stored procedure with a parameter and using the data awareness of the reporting control i called my own report with a reporting parameter that selected the top 50 in a region (the Id they clicked)

Using the same report for master and detail, this results in the following map when clicked:

Conclusion: Reporting with geographical data has become very very easy and offers some great possibilities to report developers.