Kasper de Jonge PowerPivot Blog

Since this week the public beta of Predixion Software’s Data mining in the cloud for Excel is available. Those of you who are familiar with the the Microsoft SSAS Data mining Add-ins should be very comfortable with what is inside Predixion Data mining for Excel.  I have done a previous blog post on doing data mining using PowerPivot with the MS data mining add-in where you can see how it currently works .

Predixion Insight for Excel is like a new version of the current SSAS add-in, the Predixion insight team consists of the folks that previously build the Add-in for MS and now started on their own.

The biggest change is that you no longer need an SSAS server installed. All action happens on the Predixion servers in the cloud. Second biggest (for me) is that you can use PowerPivot data as a datasource for you Data mining. Using it in combination with PowerPivot requires nothing more then Excel and a Predixion subscription for data mining. Furthermore the overal UI had been improved to make data mining a more user friendly experience. And it support 64 bits.

From the Predixion site:

Predixion’s intuitive and easy-to-use solution allows users to run predictive analytics in the familiar environments of Microsoft Excel^® and PowerPivot. Whether you are an existing SQL Server^® Data Mining user, a BI specialist or a newcomer to the arena of Predictive Analytics, Predixion Insight™ will enable you to easily create, manage and run powerful and accurate predictive models without extensive training or specific knowledge of the methodologies currently required to create successful predictive projects.

In this blog post we are going to see what are the key influencer are of the number of items on stock from the Contoso sample database.

Read more…

This weekend i returned from the MS BI conference. For me it has been a week of introductions and great conversations. Ofcourse meeting Rob Collie, Vidas Matelis, Marco Russo,Alberto Ferrari, Stacia Misner, and Andrew Brust for the first time. I also meet a lot of the great folks from the Microsoft AS en RS teams,  talking to them was great and valuable.

As my fellow PowerPivot bloggers already blogged on the TechEd i am not going to repeat them

• Vidas on: Microsof presented first information on what to expect in next PowerPivot release
• Rob on: PowerPivot observations from TechEd/BI Conf, Pt 1

What you didn’t see yet was something on the Pivot viewer. The Pivot viewer is a data visualization tool in silverlight, build on top of project Pivot.Currently it is just a experimental project and it takes a lot of work to attach the viewer to the data. But it sure looks great:

Fortunately the guys from MS have build a tool that can help in populating the data which will be available in the next 30 days.I hope to have an up and running sample very soon and make a blog post on it.

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