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

With SharePoint 2010 on the horizon Microsoft released more information about SharePoint 2010 and the integration with BI at the SharePoint conference last week.
They are so serious about BI and SharePoint they even released a name for the subject calling it “SharePoint Insights”:

SharePoint Insights enables users to find the information they need across unstructured assets (blogs, wikis, presentations, documents) and structured assets (reports, spreadsheets, analytical systems). Empower users to discover the right people and expertise to make better informed and more agile business decisions.

They released a blog specific to the subject: http://blogs.msdn.com/sharepointbi where you can find more information about SharePoint Insights. You can also check out the official SharePoint Insights page and download the new datasheet and whitepaper to help you start thinking about what SharePoint 2010 can help your organizations use Business Intelligence.

The new SharePoint Insights will also incorporate PerformancePoint dashboards now called PerformancePoint Services:

and of course the new self service BI project formerly called Gemini, now named PowerPivot:

Something called Visio services (also from PPS) in which you can dynamicly show Visio documents:

In SP 2010 they have a successor for the SharePoint 2007 Business Data Catalog (BDC) called the Business Connectivity Services (BCS), It provides out-of-box features, services and tools that streamline development to deeply integrate external data and services. What the direct added value for us BI’er is i’m not sure, but surely these will be new sources to extract data from.

And there is even good news for your server admin: they made the installation a walk in the park (according to a Donald Farmer interview)

Also, we provide a really nice setup. You run a SQL Server 2008 R2 setup and if you don’t have a SharePoint farm we’ll say “Insert your SharePoint disc and we’ll go ahead and install the farm for you and configure it with all the defaults that you need for Gemini.” So it’s actually very cool. Even if you’re not a SharePoint administrator, the setup will walk you through the process of setting up the SharePoint farm and configuring it, which we know could be a challenge if you’re not familiar with it.

I really hope they can back this up, installing Reporting services with SharePoint 2007 is NOT easy.

Microsoft is really putting more and more into SharePoint and want a tighter integration with BI, whether you like it or not. I think as MS Partner it opens a lot of new doors so i suggest to keep an eye on SharePoint the following months.

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

I have developed a report where i have to use data from different datasets, to get these on one report i have to use multiple tablix data regions
(one dataset per tablix). The report must appear as one tablix in stead of multiple. The tablix data regions are placed below each other with the same columns.

And that’s where the layout problems start, I made sure the columns from the tablix data regions have the same width and are aligned by using the alignment grids.
While using preview mode it looks great the columns are aligned perfect, but after publishing the report to the reporting server the layout is all fumbled up. The columns are no longer aligned below each other.

After some extensive debugging and playing around, I found the solution: it appears reporting services renders the preview different from the browser, the solution from my problem was to set the cangrow property of the row to false (the default setting is true).

I understand this is the solution to my specific problem but it can give you a hint to your solution, there are several properties that can alter the layout of your report.

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!

Even though it’s my holiday i couldn’t resist to download and install SQL Server 2008 R2 and play around with Gemini. I forgot you have to have access to Excel 2010 to do so, luckily some co-workers have access to the Technical Preview so i could get it there.

After installing SQL Server 2008 R2 and Office 2010 i still didn’t have a Gemini tab, it appears you have to download the Gemini add-in separately, you can do so here: https://sharepoint.connect.microsoft.com/SQLServer/Gemini. You do have to get access from Microsoft before you can download the plug-in.

Ok on to Gemini.

After installing the plug-in you get an extra tab in Excel 2010 that looks like this:

To start we have to load & Prepare data, i decided to use the AdventureWorksDW2008 database and recreate a part of the AdventureWorks cube.  When you click the Load & Prepare data button the Gemini client will open in a new window.

As you can see there a few options to choose from, i’ll choose the database now but the Data Feed and Report options look very promising (think connecting Berlin to the Data feed in an ESB environment).  After clicking the From database button you have to choose a data source and then select a table or write a query of the data to import. I’ll choose for a the DimSalesTerritory and the FactInternetSales tables to start with, you can even choose a friendly name:

At the last step the preparation is completed:

The Gemini add-in has now loaded the data into the Gemini client

It even found the relationship on the tables and created it in Gemini as we can see in the manage relationships window:

Now before looking at the result i want to add the time dimension to the data by hand, to do that we need to click the  “from database” button again and add the DimDate table

Now important in Gemini is to create the relationships for each table to the fact table, we’ll do the DimDate by hand, click on Create relationships and select the corresponding fields:

Make sure you get primary and foreign key in the right order. Now its time to see the result and add the data to a PivotTable by clicking the PivotTable button.

You now have a full working PivotTable in excel:

After playing around with data you can’t even feel the difference between the PivotTable on the AdventureWorks cube and the same data from Gemini. Great stuff and very powerful.

The only thing i can’t get working are the slicers, they keep coming up empty where there should be data:

Thanks to the comment of Tatyana below the slicers work as well, i love this functionality, it, besides looking great, works great too

Overall a very powerful tool and i see many great applications for it. I for one am very enthusiastic and I know some clients who are waiting for this.

Although i have my reserves about it still being somewhat too technical with the relationships for the real business analysts, it would be better if the relations where somehow automatically suggested when you add a table. I would see a DBA creating various views on the datawarehouse which the business analyst then can use to analyze. On views relationships don’t exist so Gemini won’t recognize them, a automatic relationship recognition would be great. Well maybe after some training the analysts will be able to do it themselves with the right training.

UPDATE: news on twitter travels fast i got a reply from Donald Farmer on my blog post and apparently MS is working on automatic relationship detection right now. Can’t wait to see the final product

The Microsoft World Wide Partner Conference 2009 is just finished and loads of BI news, i made a small linkflood:

MS BI wpc round up for microsoft bi
Office 2010: Excel 2010 – New Buttons on Ribbon for Pivot Tables – Custom Named Sets!
What’s new for Analysis Services users in Excel 2010?
First impression of Excel 2010

Some great stuff in here !  I will try to get a hold of Office 2010 Tech preview to try it out myself !

A pretty comprehensive blog post on how to develop Microsoft Business Intelligence Solutions in a team environment:
http://blog.stevienova.com/2009/06/04/microsoft-business-intelligence-development-in-a-team-environment/

Want to see a cool example of data visualization? This case study video that shows how the Tampa Police Department used Microsoft Surface to help law enforcement and emergency response crews provide safety, analyze what was going on and communicate better during the Super Bowl. The Super Bowl attracts millions of tourists and the Tampa Police Department was looking for a method of processing incident and event details in a manner that was intuitive and easily understood.

From MS bi blog http://blogs.msdn.com/bi/archive/2009/06/07/bi-dashboard-for-the-super-bowl.aspx

New in Sql Server 2008 are the integrated Best Practice Design shown as warnings, a lot of times you want to ignore these warnings because you know it isn’t applicable in your situation.

Before you deploy your project, you should clean up – to the extent you wish – the current warnings in your project. Warnings won’t stop your deployment, but you should make a conscious decision whether to ignore the surfaced warnings. A comprehensive list of all warnings in your project can be found in the Error List window (which you can open with Ctrl+E). Double-click on an error to access the designer and fix the problem. Alternatively, you can right-click the error and click dismiss to clear it off the list if you don’t intend to fix it. You can even add a comment to document your reason for ignoring this error. This method of clearing the error is instance-based and will not clear the same error if it’s found in a different dimension or cube. To globally dismiss a particular type of error, whether proactively before you start development or after the fact, you can access the new Warnings tab in the Database editor (which you can open on the Database menu by clicking Edit Database). Incidentally, the Warnings tab also contains a list of the warnings dismissed individually and the related comment.

Found at at: http://www.sqlskills.com/BLOGS/STACIA/post/SQL-Server-2008-Best-Practice-Design-Alerts.aspx