While working with the Microsoft Sync Framework, I kept coming across an error while debugging an application that syncs data from a server data source to a local cache in a SQL Server Compact database. I was using the Sync Designer that ships with Visual Studio.NET 2008. I started by creating a Windows Form Application, then when I went to debug the sync object, I received the “vshost.exe has stopped working” error in the image below.

Next, I tried to start the application without debugging. This time I got a different error: “Unable to load DLL ‘sqlceme35.dll’: The specified module could not be found.”

I found a solution that resolved both of these errors. You need to set the CPU to x86 in the debugging menu. Next to the Debug button on the toolbar, there is a drop-down box that lists Any CPU.

After changing this setting to x86, the debugging can be completed without errors.

The next version of Microsoft Office is around the corner, so let’s take a look at some of the new features of Microsoft Word 2010.

New formatting tools allow you to add creative details to your documents. Things such as shadow, reflection, and glow (similar to the text effects currently available in PowerPoint 2007) can be used.

Speaking of PowerPoint features, SmartArt graphics can be created and inserted in Word. This is great if you want to create and plug in a dynamic org chart or some other graphic representation of data into your document.

In the left pane, you can now access the document map, allowing you to view, arrange, and manipulate content under specific headings. The document maps includes a search bar, allowing you to find a particular word or phrase, matches are highlighted within the document map for easy navigation.

Word 2010 also includes new online collaboration tools, offering better tools than ever to share documents with your coworkers. With a simple pop-up window, you can see who is editing a document and view their contact information, and even work on the document together at the same time.

From the back stage view, you can set permissions for the document, save it as a PDF, or send it to someone through email.

The printing and print preview features in Word have also undergone significant improvement, showing you exactly how your document will appear before you print it. From the print preview pane, you are always a single click away from printing.

So now let’s say that you want create firewall entries that port forward RDP requests to specific VMs running on your Hyper-V host. Again, you need to first make sure that each VM has a unique IP address on your local area network, can access the Internet through your firewall, and has RDP enabled as described previously.
So in this example, I have two VMs at 192.168.1.110 and 192.168.1.111. I’m going to pick ports 57110 (a custom port which I’m calling RDP VM1) and 57111 (which I’m calling RDP VM2) to forward to those respective IP addresses.

Now, if I try to RDP from an external address to router external address:57110 or 57111 my connection will automatically be forwarded to one of the VMs running on my Hyper-V host.

Now that PowerPivot for SharePoint is properly installed, you are ready to begin sharing an managing PowerPivot workbooks created in PowerPivot for Excel. If something is not working quite right, you might want to go back to my previous blog and verify your installation procedure.

I have already discussed using PowerPivot for Excel to create workbooks and worksheets to analyze data, so we will continue the discussion with sharing and managing those workbooks using PowerPivot for SharePoint.

PowerPivot for SharePoint consists of the PowerPivot System Service and the SQL Server 2008 R2 Analysis Services in VertiPaq Mode. Excel Calculation Services runs on top of these to provide the facility for sharing and reusing the Excel-based PowerPivot workbooks.

A web browser accesses the service using the Excel Web Access web part. Accessing a PowerPivot workbook with a browser enables anyone to load the workbook and interact with it live without the aid of additional software on the browser computer. The web part enables the user to manipulate the data using the slicers and filters configured when the workbook was created. Selecting and deselecting slicers and filters sends a query back to the PowerPivot System Service for processing and the resulting data is returned to the browser window within seconds. The results are based on the last data set saved with the workbook. If the latest data is required, the browser window includes options to refresh the data.

Another option in the browser window is to open the workbook in Excel. Keep in mind that this requires Excel 2010 with PowerPivot installed to work properly. When the workbook is loaded in Excel, all of the functions in PowerPivot for Excel are available to the user. This includes changing of the layout, slicers, filters, and value fields. The workbook also includes the cached data from the latest data refresh. After making changes to the workbook, it must be uploaded to SharePoint to make the changes permanent.

That is a quick tour of the workbook access layers. Now let’s look at some of the server side features that make PowerPivot for SharePoint such a powerful tool for self-service business intelligence.

The PowerPivot System Service is the middle application layer that handles requests for queries, including data refresh queries. The queried data is initially cached in the workbook itself. However, data refresh requests are forwarded to Analysis Services and the result set is passed back to the client or server application. Each new data refresh is cached by the service for faster response times on subsequent queries from the same client, and from other clients accessing the same workbook with the same data set.

Data refresh jobs can be created in PowerPivot for SharePoint to ensure that the data used in a specific workbook is accurate and up to date based on a set schedule. The jobs are assigned a suggested start time. That means that the job is scheduled to begin no earlier than the start time. However, it does not guarantee that the job will start at that time. PowerPivot has a special start time called “after business hours” which you define in the configuration settings. This option is provided as a default to assist in scheduling data refresh jobs for times that will have the least impact on network performance.

The System Service also manages active and cached connections to Analysis Services. These connections are generally added to connection pools based on the in-memory data source they use. If twenty users are accessing the PowerPivot data set for period-end accounting transactions, the data is cached in memory and each connection is pointed to the same data set.

Active PowerPivot data sets in-memory data sets. Basing the data set in memory enables PowerPivot to manipulate and evaluate millions and millions of rows of data without the burden of hardware I/O slowing down the process. These in-memory data caches are monitored for activity and cached to disk if there is no activity within a specified period of time. The default is 30 minutes without activity.

There are two monitoring jobs that the PowerPivot System Service performs to assist in server management. The first is the collection of usage data to assist in monitoring connections, data cache loads and unloads, query reporting and server status. The data is compiled and made available as both an entry in the PowerPivot Management Dashboard, and excel spreadsheets.

The other task is monitoring server health. In a single server farm this is useful for server and performance diagnostics. If the PowerPivot is installed on a multi-server farm, the monitoring assists in load balancing. Load balancing is performed in a round robin format by default. However, for larger farms it may be advisable to set the Service to perform load balancing based on individual server help.

Now we are down to the lowest layer of PowerPivot for SharePoint, SQL Server 2008 R2 Analysis Services. The new version of Analysis Services includes PowerPivot for SharePoint when installed with SharePoint integration. The Analysis Services engine runs in VertiPaq mode. VertiPaq mode provides the mechanism for in-memory caching of the extremely large data sets used by PowerPivot. The data set is stored in memory using highly optimized compression. Without the in-memory cache, it would quickly become impractical to provide the volumes of data the users require to create meaningful business intelligence solutions. Analysis Services manages the data within the cache by restructuring the data based the slicers and filters selected in a PowerPivot workbook. By providing this restructuring and query capability in memory, the users experience extremely fast response times.

I should mention that Analysis Services in VertiPaq mode does not support traditional OLAP options. If you need to perform operations against traditional cube databases on the same server you can install another instance of Analysis Services that is not in VertiPaq mode for this purpose.

This is a very brief overview of some of the most important things to know about PowerPivot for SharePoint and how it works. So far, I have found PowerPivot for SharePoint and PowerPivot for Excel to be amazingly, and deceptively, simple to install and place into production. The functionality provided by PowerPivot promises some very exciting days ahead for all of the users that have had to wait for days, months, or even years for the creation of a custom business intelligence solution to provide answers to pressing issues. These users will soon find themselves creating business intelligence solutions that answer all of their questions and more in as little as a few minutes.

In my previous blogs, I looked at PowerPivot for Excel and how it enabled the average user to access and analyze data without the aid of an IT professional. However, the advantages do not stop there. The other side of making business decisions is the sharing of those decisions along with the data used to support them.

PowerPivot for SharePoint 2010 is part of the new SQL Server 2008 R2 Analysis Services when installed with SharePoint integration. Installing PowerPivot for SharePoint looked to be a simple matter. However, I found that there are some very important planning steps required for a smooth installation.

I was fortunate enough to begin with a clean slate. Because PowerPivot for SharePoint only works with SharePoint 2010, and because SharePoint 2010 is in beta, I had the luxury of starting with the SharePoint installation. PowerPivot for SharePoint can be installed on an existing SharePoint Server, or on a newly installed server. If your organization is planning to use PowerPivot for SharePoint, seriously consider installing both products at the same time. Waiting until your SharePoint server is configured and well established means that much of the PowerPivot installation and configuration will require manual efforts.

The following are the most important things I learned while installing both products:

• SharePoint 2010 Beta
  
  • Both SharePoint 2010 and PowerPivot for SharePoint require the 64-bit version of Windows Server 2008 SP2 or R2. I used Windows Server 2008 R2.
  • The minimum 4GB of RAM really is the minimum, at least for installation. SharePoint 2010 Beta would not install properly with less RAM. Believe me, I tried. Using Hyper-v virtual machines to create the server requires some tweaking of RAM to make sure resources are available for other virtual machines in a demo setup. After completing the install and configuration of the SharePoint 2010 and PowerPivot virtual machine, I was able to run the server with 2GB of RAM. This made it run very slow, but it did run fine. If possible, I would run with the full 8GB that is recommended.
  • 80 GB of disk space is recommended. However, PowerPivot caches data to speed retrieval and will require considerably more space. Remember that PowerPivot is designed to handled many millions of rows of data and plan accordingly.
  • If you run SharePoint 2010 on Windows Server 2008 R2 you need to download the Windows6.1-KB976462-v2-x64.msu hotfix file located at http://go.microsoft.com/fwlink/?LinkID=166231. The file fixes an issue that prevents PowerPivot data refresh and the PowerPivot Management Dashboard from working properly. Interestingly, this hotfix is apparently automatically installed as part of the Windows Server 2008 SP2 setup.
  • SharePoint 2010 should be installed using the Server Farm option on the Choose the installation you want dialog. Choose this option even if the server will be the only one.
  • Make sure and select the Complete option on the Server Type dialog.
    
  • Do not run the SharePoint Configuration wizard after the install process is complete. The PowerPivot for SharePoint installation will take care of all configuration tasks for you. If you forget and run the wizard, you will be forced to configure everything for PowerPivot manually.
    
• PowerPivot for SharePoint – SQL Server 208 R2 Analysis Services
  
  • Run the SQL Server 2008 R2 setup application.
    
  • On the Feature Role dialog select Analysis Services with Sharepoint Integration and be sure an change the Install Analysis Service in option to A new farm (install separately). This tells the PowerPivot portion of the setup to configure the new SharePoint server with the necessary settings to run PowerPivot.
    
  • Download and run the SQLSERVER2008_ASAMO10.msi file located at http://go.microsoft.com/fwlink/?LinkID=168693&clcid=0×409. The SQL Server 2008 R2 Analysis Services OLEDB Provider version 10.5 update is required on each PowerPivot for SharePoint server to enable smooth data connections between clients and Analysis Services for data refresh.
    
  • The Analysis Services service account must be a domain account. Setup will display an error if you try to use the local machine Service or Network Accounts.
    
  • PowerPivot Service Application Pool accounts must also be domain accounts. When installing PowerPivot on multiple servers in a farm, this account does not necessarily need to be the same for all servers. The account requires connect, read, and write permissions to the PowerPivot service application database.
    
• SharePoint settings in SharePoint 2010 Central Administration
  
  • Change the value in Maximum upload size under Application Management | Manager web applications | SharePoint – 80 to 2047. This will allow PowerPivot workbooks of up to 2GB in size to be uploaded to the SharePoint PowerPivot Gallery.
    
  • In the Application Management | Manage service applications | ExcelServiceApp1 | Trusted File Locations | http:// change the following values:
    
    • Change the Maximum Workbook Size to 2000.
      
    • Change the Maximum Chare or Image Size to 10,
      

If you followed all of these recommendations, your SharePoint 2010 server with PowerPivot is ready to go!

In my next blog, I will look at what PowerPivot for SharePoint adds to the SharePoint server.

In my previous blog, I discussed the PowerPivot workbook. I looked at how to create it by importing data, and I briefly covered the new Data Analysis Expressions (DAX) that are used to create new measures. In this blog, I would like to look at the presentation of PowerPivot data in worksheets.

Worksheets created from the new workbook house PivotTables and PivotCharts. You can have PowerPivot create the workbook with one of several possible layouts that include one or more PivotTables and PivotCharts.

After the layout is determined, you simply select the PivotTable or PivotChart to work on and drag the fields you want into lists in the task pane. Adding a field to the axis, label, filter, and value lists, automatically changes the related table or chart to reflect aggregations of the data including subtotals and grand totals. PowerPivot creates each aggregation based on the relationships between the PowerPivot tables in the workbook. All of the aggregates are SUMs by default. However, it is a simple matter to change the default operation to calculate and average, minimum, maximum, or count.

Fields are also used to create slicers. Slicers add components to the worksheet that contain buttons for selecting data to display in the PivotTable or PivotChart. Each button represents a distinct value from the field being used. If several slicers are added, then you can use any combination of selections to filter the data. The combinations of filters are limited only by your imagination and can range from predetermined business criteria to random combinations. I used the Contoso retail database to create my workbook. I was able to design a PowerPivot PivotTable that showed me all of the sales filtered by product category and subcategory. Then I added demographic slicers that enabled me to change the sales figures based on customer gender, income, marital status, sales source (reseller or individual), and territory. Being able to do all of this within about ten minutes was very cool! I am used to spending hours or days with various report writers trying to perform simpler data analysis reports.

Most of what PowerPivot can do to analyze and present data is automatic and requires very little operator assistance. Naturally, you can spend a little extra setup time and manually create measures using DAX. But, you can also create reports from PowerPivot workbook tables using the new Excel cube functions. This is considered advanced PowerPivot. For an excellent example of using PowerPivot and cube formulas, you can go to http://powerpivotpro.com/2010/01/18/pulling-back-the-curtain-intro-to-cube-formulas and walk through the tutorial.

For now, PowerPivot for Excel is still part of Microsoft Office Excel 2010 Beta. That means if you want to try it out you should be prepared to deal some idiosyncrasies until the final release. Nevertheless, I can guarantee that if you wait for the official release of Excel 2010 and PowerPivot for Excel, you will not be disappointed in the least.

Oh! Did I mention that PowerPivot for Excel is a FREE add-in!