Making the switch: VMWare Workstation 7 to Hyper-V server for performance reasons (PowerPivot on SharePoint 2010)

 

History

Back in 2002 I was working with a company which produced a CAD application written in VB6. The product was released in several languages. For each language we had a Microsoft Windows NT 4 workstation with the corresponding language package. I quickly realized that managing these workstations was difficult. So I started to look at virtualization, but it was not until 2003 with new hardware and an other company when I decided to take a look at VMware Workstation version 4.0. This was great I could run all Microsoft Windows languages and different versions on 1 system! Then in 2004 Microsoft released Microsoft Virtual Server 2005 I took a look at the product but soon realized that it could not compete with VMware Workstation and I kept on using VMware Workstation. This worked great for me, except for 2 things:

  1. Performance, if I installed a Windows operating system on a workstation en booted from it, it was much, much faster then when I used a Virtual Machine containing the same windows operation system on that same workstation.
  2. Snapshot – Backup host, if I installed a program or driver on the host and that corrupted the host, there was no out of the box recovery (yes there where al kinds of products that can do that, but not windows out of the box) possibility. One trick I used was to install an windows host OS and then only installed VMware Workstation on it. I would only use the VMware virtual machines and only used the host OS to take snapshots and backups of my virtual machines.

VMware Workstation 7 (paid product)

Part 2 worked fine for me, but then I shifted from being a C# developer to a self-service BI specialist and Microsoft introduced PowerPivot and SharePoint 2010. In late 2009 I started to work with PowerPivot (Gemini at the time) and SharePoint 2010, so I create a VMware 7 development virtual machine with Microsoft Windows 2008 R2, Microsoft SQL Server 2008 R2, Microsoft SharePoint 2010 and Microsoft Office 2010 on a 3 year old dell laptop with 4 GB memory. Well that really did not perform at all. I bought a 80 GB Intel Postville G2 M-25 SSD, but the VMware Workstation virtual machine would not perform well and I was waiting on the system all of the time. Then one time I installed Microsoft Windows 2008 R2 as host OS with Microsoft SharePoint 2010 and PowerPivot on it. Well I was really impressed by the speed of the system, I booted in 3 times as fast an all Office products would directly open when I clicked on a document, instead of waiting for the splash screen. I know 4GB of RAM is not enough for a Microsoft SharePoint 2010 PowerPivot development machine, but with the help of the SSD I could manage it. But then I could not take snapshots of my host.

As I said before, VMware Workstation 7 is to slow for me on my hardware for running Microsoft SharePoint 2010 with PowerPivot, that’s why I started to look at hypervisors.

Pros
– Don’t have to no anything about hypervisors, just install windows host OS and then install VMware Workstation 7.0, it’s only a program.
– Supports not only Windows guest operating systems

Cons
– Must be installed on a host operating system (host operating systems consumes cpu, memory and disk space
– Is not free
– “Bad” performance compared to native boot

 

VMware ESXi (freeware)

Then in begin 2010 I learned about hypervisors. VMware has a free product ESXi 4, that can be installed on a system with a (32MB foot print, maximizing the space on the 80GB OS for Virtual Machines) and then you can run different virtual machine containing not only Microsoft OS but also Linux etc without installing a Host OS and having the possibility to take snapshots and backups of the virtual machines . So I created a bootable USB ESXi drive but found out my NIC and SATA controller where not supported, damn! Yes I know, ESXi is not supposed to be installed on a laptop but it would have been nice.

Pros
– Freeware
– No host OS (so not need to snapshot or backup host, only snapshot and backup virtual machines)
– 32 MB footprint
– Supports not only Windows guest operating systems
– Can run Microsoft Windows XP virtual machines
– Can boot from USB without installing

Cons
– Can’t native boot from virtual machines (so a performance impact, but far better then VMware Workstation)
– Could not get it to work with my DELL laptop

 

Microsoft Hyper-V 2008 R2 (freeware)

In 2010 Microsoft released there hypervisor: Microsoft Hyper-V 2008 R2 and it’s free! What did you say, is Microsoft releasing a product that’s free of charge, the answer is; YES. It can be downloaded, here: http://www.microsoft.com/downloads/details.aspx?FamilyID=48359dd2-1c3d-4506-ae0a-232d0314ccf6&displaylang=en it’s a 1,5 GB ISO. Installed it and after installing I could boot from a VHD file created with Microsoft Windows 2008 R2

Pros
– Freeware
– Native boot from VHD, maximizing performance and hardware possibilities
– No host OS (so not need to snapshot or backup host, only snapshot and backup virtual machines)
– 6GB footprint (after installing)
– Can boot from USB without installing

Cons
– Supports only booting from Microsoft Windows 7 or Microsoft Windows Server 2008 R2
– Have to use a “workstation” virtualization product in a booted virtual machine to manage other virtual machines

 

What do I exactly use for developing Microsoft SharePoint 2010 PowerPivot applications

So today what do I exactly use for developing C# vs2008, C# vs2010 and SharePoint 2010 PowerPivot applications

  1. Laptop with Microsoft Hyper-V Server 2008 R2 (freeware) installed on a 2,5 inch 80GB SSD boot disk, a 500GB 7200 rpm 2,5 inch for data and virtual machines I don’t often use and 8GB of RAM.
  2. When I start the laptop I boot into a VHD containing Microsoft Windows 2008 R2, Microsoft SQL Server 2008 R2, Microsoft SharePoint 2010, Microsoft Office 2010 and the hyper visor role installed to manage other virtual machines. Because I primarily do self service BI work and VS2010 development this is the machine I want the best performance for. I use the hyper-v manager in the this virtual machine to start legacy virtual machines (VS2005 with SQL 2005 or VS2008 with SQL 2008)
  3. In the SharePoint 2010 virtual machine I keep 2 images up date date, a base Microsoft Windows 7 and a base Microsoft Windows Server 2008 R2 virtual machine. They serve as base for creating new virtual machines, see https://www.roelvanlisdonk.nl/?p=1530. They are updated every week and configured the way I want to, see https://www.roelvanlisdonk.nl/?p=1462
  4. In all I mange 6 virtual machines
    • Microsoft Windows 7 x64 – base for client virtual machines
    • Microsoft Windows Server 2008 R2 x64 – base for server virtual machines
    • Legacy 2005 development – clone of Microsoft Windows 7 x64 base containing VS2005, Office 2003, SQL Server 2005, SSIS 2005, SSRS 2005, SSAS 2005
    • Legacy 2008 development – clone of Microsoft Windows 7 x64 base containing VS2008, Office 2007, SQL Server 2008, SSIS 2008, SSRS 2008, SSAS 2008
    • SharePoint 2010 development (native boot), clone of Microsoft Windows Server 2008 R2 x64, containing VS2010, SharePoint 2010 with PowerPivot, Office 2010, SQL Server 2008 R2, SSIS 2008 R2, SSRS 2008 R2, SSAS 2008 R2
    • VPN – clone of Microsoft Windows 7 x64 base containing VPN connections and remote desktop shortcuts to customer machines (I use a virtual machine for this purpose, because, when connected to the VPN I don’t have access to the rest of the network)

 

Conclusion

Microsoft Hyper-V Server 2008 R2 rules:

  1. Creating new virtual machines in minutes, containing a fully patched windows host OS and configured the way I like it to be with the ability to boot from the virtual machines, maximizing performance
  2. Can use legacy development machines without rebooting (vs2005, vs2008 for legacy support)
  3. Install a full blown Microsoft SharePoint 2010 development server within 30 minutes on a new laptop or workstation and booting from it, maximizing performance

Difference between PowerPivot Field List and the PivotTable Field List in Excel 2010

When you are using PowerPivot in Excel 2010 there are two field lists:

 

PowerPivot Field List
Used for PowerPivot charts and tables
Click on a PowerPivot chart or tables then on the ribbon > PowerPivot tab > Field List

image

 

PivotTable Field List
Used for normal charts and pivot tables
Click on a chart then on the ribbon > PivotChart Tools > Analyze > Field List
Click on a pivot table then on the ribbon > PivotTable Tools > Options > Field List

image

image

 

 

Field Lists

image

Create you’re first Microsoft PowerPivot report based on the Microsoft AdventureWorks sample databases

If you want to get started with PowerPivot, you will need to install Microsoft Office 2010 and a separate Excel Add-in: PowerPivot for Microsoft Excel 

PowerPivot for Microsoft® Excel X86 Executable (32 bit)
PowerPivot for Microsoft® Excel X64 Executable (64 bit)

After installing the Add-in you should see the extra Excel tab “PowerPivot”, in the screen dumps second tab on the right:

image

 

Microsoft SQL Server 2008 R2 AdventureWorks sample databases

If you want to use the Microsoft SQL Server 2008 R2 AdventureWorks sample databases as source for you’re PowerPivot reports, you will need to install the sample database on you’re database server. The Microsoft SQL Server 2008 R2 AdventureWorks sample databases can be found here: http://msftdbprodsamples.codeplex.com/ 

Before installing the sample database, make sure you’re Microsoft SQL Server 2008 R2 instance has FILESTREAM enabled and is running the SQL Full-text Filter Daemon Launcher service. To enable both feature, follow the steps on: http://msftdbprodsamples.codeplex.com/wikipage?title=Database%20Prerequisites%20for%20SQL%20Server%202008R2

 

PowerPivot tab > PowerPivot Window

image

Home > From Database > From SQL Server

image

Enter servername and databasename (AdventureWorksDW2008R2)

image

Select from a list of tables and views to choose the data to import

image

Select fact table “FactInternetSales” > Select Related Tables

image

On my virtual machine the 80.000 rows where imported in 10 seconds.

You can set friendly names for tables to show in the user interface.

image

During the import the PowerPivot add-in automatically detected relationships

Design > Manage Relationships

image

Add, edit or delete relationships 

image 

PivotTable > Chart and Table (Vertical)

image

Select “Existing Worksheet”

 

image

This will add a chart and a PowerPivot table to sheet1. It will also create a “data sheet“

Rename Excel sheet tabs

image

Click on the chart or the pivottable to show the “Field List”, if the “Field List” does not show click PowerPivot tab and click “Field List”.

image

Drag and drop the EnglishProductname to the “Axis Fields”

Drag an drop the SalesAmount to the Values (default this will calculate the sum of the SalesAmount, but you can change this to Count, Min, Max or Average, by right clicking the Measure SalesAmount and choose Edit Measures…

image

Drag and drop the WeekNumberOfYear to the “Slicers Horizontal”

image

Result: you’re first PowerPivot chart!!!

How to enable https on Microsoft SharePoint Server 2010 RTM by configuring alternate access mappings

Open the Microsoft SharePoint Server 2010 Central Administration > System Settings > Configure alternate access mappings:

 

image

 

Add Internal URLs

image

Enter https://youreservername and choose intranet zone, click on ok

image

Result

image

Start inetmgr right click you’re site and click on Edit bindings…

image

Click Add..

image

 

Choose the server generated certificate if you don’t want to by a certificate, but then you will get an warning when accessing you’re SharePoint site.

 

image

 

Click OK and click on Brows *:443 (https)

image

The warning page

image

 

Click on Continue to this website (not recommended).

image

 

Followed the steps on http://blogs.msdn.com/sowmyancs/archive/2010/02/12/how-to-enable-ssl-on-a-sharepoint-web-application.aspx for more information on using hostheaders (dns entries) and ssl certificates.

Install a development Microsoft SharePoint 2010 RTM Server with PowerPivot and Microsoft SQL Server R2 Reporting Services integration

These are the steps I followed to install a development Microsoft SharePoint 2010 server with PowerPivot and Reporting Services integration.

 

I installed Microsoft SharePoint Server 2010 by following the steps on:

http://powerpivot-info.com/post/66-step-by-step-guide-on-installing-powerpivot-for-sharepoint

 

Then I followed the steps on: http://technet.microsoft.com/en-us/library/bb326356(SQL.105).aspx and http://msdn.microsoft.com/en-us/library/aa905871(SQL.105).aspx to enable Microsoft SQL Server 2008 R2 Reporting Services integration for Microsoft SharePoint Server 2010.

After following these steps, I was able to publish PowerPivot excel reports and Microsoft SQL Server 2008 R2 Reporting Services reports to one top level site collection on Microsoft SharePoint Server 2010. See screen dumps:

 

Start Microsoft SQL Server 2008 R2 setup > Installation > New installation or add features to an existing installation

image

New installation or add shared features

image

SQL Server Feature Installation

image

Reporting Services

image

Instance Configuration

image

Server Configuration

image

 

 

 

Start Reporting Services Configuration Manager

image

Choose Report Server Instance

image

Create a new report server database

image

Report Server Database Configuration Wizard

image

Report Server Mode > SharePoint Integrated Mode

image

Additional actions in Reporting Services Configuration Manager

Web Service URL > Apply

image

Report Manager URL > Apply

image

 

The SharePoint 2010 pre-requisite installer now automatically guides you to download and install the Reporting Services 2008 R2 for SharePoint 2010 Add-in.  Btw, you can download the Reporting Services add-in manually as well. In most cases you can just activate the Report Server Feature in SharePoint Central Administration (http://technet.microsoft.com/en-us/library/bb677366(SQL.105).aspx). In some case you must first download and install the add-in manually, in my case I re-configured the Reporting Services 2008 R2 for SharePoint 2010 Add-in and then activated the Report Server feature on the Central Administration site.

Reconfigure the Microsoft SQL Server 2008 R2 Reporting Services Add-in > Start > Control Panel > Programs and Features > Right click SQL 2008 R2 Reporting Services SharePoint 2010 Add-in > Configure

image

Activate Report Server Integration Feature and Report Server Central Administration Feature > Central Administration > Site Actions > Site Settings > Site Collection features

image

Configure Reporting Services Integration > Central Administration > General Application Settings > Reporting Services Integration

image

Reporting Services > Reporting Services Integration

image

Reporting Services > Set server defaults

image

 

Create a Report Library > Site Actions > More Options > Report Library

image

Enable Report Server Content Types

http://technet.microsoft.com/en-us/library/bb326289(SQL.105).aspx

 

Create a Report > Report Library > Documents > New Report Builder Report (Click once deployment Report Builder 3.0 should start)

image

 

Run click once deployment Report Builder 3.0 (78.2 MB)

image

Create you’re first report

image

 

The Microsoft SQL Server 2008 R2 sample databases can be found at: http://sqlserversamples.codeplex.com/ (82.5 MB). At this moment the November CTP sample database are the last version. RTM sample database should be released soon.

 

Save the report to SharePoint 2010

image

Open report in Microsoft SharePoint 2010

image

Result full screen

image

Result as webpart

image

Links on Managed self-service BI – PowerPivot and Corporate BI

Some useful links on Managed self-service BI – PowerPivot and Corporate BI:

General

http://msdn.microsoft.com/en-us/library/ff628113.aspx

http://powerpivotfaq.com

http://powerpivotpro.com

http://powerpivot-info.com

 

DAX and time intelligence functions

http://blogs.msdn.com/powerpivot/archive/2010/04/12/time-intelligence-functions-in-dax.aspx

http://blogs.msdn.com/powerpivot/archive/2010/04/05/dax-data-analysis-expressions-measures-in-powerpivot.aspx

http://powerpivot-info.com/post/56-introducing-powerpivot-dax-measures

 

Datasets

http://pinpoint.microsoft.com/en-US/Dallas

http://developer.netflix.com/

 

Kasper de Jonge (colleague)

http://www.powerpivotblog.nl

 

SAP as datasource (SAP extractor)

 http://www.theobald-software.com/en/products/xtractppv.htm

 

Webinars

http://www.sqlpass.org/24hours/2010/

 

Virus scanner for Microsoft SharePoint 2010

http://www.microsoft.com/forefront/sharepoint/en/us/default.aspx

In Dutch
Self-service BI in soccer terms: http://www.computable.nl/artikel/ict_topics/beheer/2568712/1277800/scoren-met-business-intelligence-competence-center.html

Cubes
Create you’re first cube and benefits: http://technet.microsoft.com/en-us/magazine/ee677579.aspx

How to PowerPivot outlook data (*.ost or *.pst) data in Microsoft Office Excel 2010

First I extracted some data from the outlook inbox with C# to a CSV file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Outlook;
using System.IO;
namespace Common
{
public class Generator
{
const string Splitter = ",";
const string CsvFIleName = "MailData.csv";
const string CsvPath = @"C:\BDATA";
public void Start()
{
Application outlook = new Application();
// Get the inbox folder
MAPIFolder folder = outlook.GetNamespace("MAPI").GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderInbox);
// Get received mails
IEnumerable<Microsoft.Office.Interop.Outlook.MailItem> items = folder.Items.OfType<Microsoft.Office.Interop.Outlook.MailItem>();
// Clear file
File.WriteAllText(Path.Combine(CsvPath, CsvFIleName), string.Empty);
foreach (MailItem item in items)
{
StringBuilder csvContent = new StringBuilder(string.Empty);
// Add [Sender] to output
if (item.Sender != null && !string.IsNullOrEmpty(item.Sender.Name)) { csvContent.Append(item.Sender.Name.Replace(Splitter, string.Empty)); }
csvContent.Append(Splitter);
// Add [Subject] to output
if (!string.IsNullOrEmpty(item.Subject)) { csvContent.Append(item.Subject.Replace(Splitter, string.Empty)); }
csvContent.Append(Splitter);
// Add [Year] to output
csvContent.Append(item.ReceivedTime.Year);
csvContent.Append(Splitter);
// Add [Month] to output
csvContent.Append(item.ReceivedTime.Month);
csvContent.Append(Splitter);
// Add [Day] to output
csvContent.Append(item.ReceivedTime.Day);
csvContent.Append(Splitter);
// Add [Hour] to output
csvContent.Append(item.ReceivedTime.Hour);
// Per line export, so you will see the filesize grow on filesystem
using (StreamWriter sw = File.AppendText(Path.Combine(CsvPath, CsvFIleName)))
{
sw.WriteLine(csvContent.ToString());
}
}
}
}
}

  • Open the CSV file with Microsoft Office Excel 2010 and File > Save As “MailData.xlsx”

image

image

  • Click on the ribbon tab [Data], select column A and then click on [Text to Columns]

image

  • Click [Delimited] and click [Next]

image

  • Uncheck [Tab] and check [Comma], then click [Next]

image

  • Click Finish

image

  • Result

image

  • Press Ctrl + L to create a table

image

  • Rename the generated column names (Column1 to Name etc.)

image

  • Click on the excel tab [PowerPivot] and click on [Create Linked Table]

image

  • In the powerpivot screen add column [Count] with formula [=1]

image

  • Click on PivotTable to create a PowerPivot report in Microsoft Excel 2010

image

  • Click on [Chart and Table (Horizontal)

image

  • Click ok
  • Select chart
  • Drag and drop [Count] to [Values]
  • Drag and drop [Name] to [Axis Fields (Categories)]
  • Select chart and right click chart
  • Click [Change Chart Type]

image

  • Select Bar and click [OK]

image

  • Click on [Name] in the chart

image

  • Choose More Sort Options…

image

  • Sort [Ascending (A to Z) by] [Sum of Count ]

image

 

  • Again click on [Name] in the chart and click [Value Filters] [Greater Than]

image

  • The email adresses in the screendumps are partial hidden, but you can see by hovering over the first bar, that [Heide] has send me the most messages [679]

image

  • Select chart and Drag and drop [Hour] to [Slicers Horizontal]

image

  • If you click on 12 in the hour slicer, you can see [Heide] had sent 49 mails on 12 o’clock