Analysis Services and PowerPivot Team Blog

Quick Start Guide Released: Learn DAX Basics in 30 Minutes

KayUnkroth — Fri, 27 Jan 2012 05:29:32 GMT

Never had the time to learn DAX? Now is the time!

Author Owen Duncan just released a DAX 101 course as part of the DAX Resource Center Wiki to help you learn DAX basics quickly and efficiently. The title says you can do this in 30 Minutes, but don’t rush it. Take your time. Enjoy this great tutorial, available at: http://social.technet.microsoft.com/wiki/contents/articles/7040.quickstart-learn-dax-basics-in-30-minutes.aspx

Removing VSTO-Based Customizations from a PowerPivot Workbook

KayUnkroth — Fri, 06 Jan 2012 22:29:00 GMT

This article is a follow-up on a previous blog post titled “How to Build a VSTO-Based PowerPivot Workbook,” which discussed some of the advantages and disadvantages of building PowerPivot workbooks by using VSTO. A key VSTO advantage is that you can bring data from virtually any source into PowerPivot even if there is no suitable data provider, but a significant disadvantage is that Excel Services in SharePoint doesn’t run the VSTO code. Among other things, this means that you cannot keep VSTO-based workbooks automatically updated by using the Scheduled Data Refresh feature of PowerPivot for SharePoint. Moreover, Excel Services displays warnings about unsupported and disabled features when viewing the workbook in the browser, such as for buttons, text boxes, and other such objects, as illustrated in the following screenshot, which shows the VSTO-based Analytics for Twitter workbook in Internet Explorer. Removing the VSTO customizations could help to improve the user experience, but how do you bring custom data into a PowerPivot workbook without VSTO if there is no direct data provider?

Converting a VSTO-based PowerPivot workbook into a non-VSTO version—such as to provide seamless browser access and to take advantage of Scheduled Data Refresh in SharePoint—requires the following high-level steps:

Devise a new import method to get data into PowerPivot without using VSTO.
Update the workbook’s PowerPivot model to use the new data import method.
Update the Excel workbook features and remove the VSTO code from the workbook.

Let’s take a closer look at these steps by converting the Analytics for Twitter solution. It is an excellent example that highlights many important PowerPivot elements and their dependencies.

Step 1: Importing data without using VSTO

VSTO-based PowerPivot workbooks rely on VSTO customizations primarily to pull data into Excel tables. Linked to PowerPivot tables, these Excel tables then simply act as PowerPivot data sources. Here’s the relevant figure from the previous blog post, which shows the data flow for the Twitter Analytics workbook.

Given the objective to eliminate the VSTO code on the client, we need to replace the Excel tables with a server-based repository that PowerPivot directly supports as a data source, such as a SQL Server database, text files on a file server, or SharePoint lists. The new data import solution can then periodically pull data from Twitter into the chosen server-based repository and PowerPivot can import the data from this repository without the need for VSTO.

The following figure illustrates the import method I chose for this article, which is based on a SharePoint Timer job and a collection of SharePoint lists. I chose this approach because it is relatively straightforward to create a Timer job to retrieve data at a configurable interval from the source and put it into one or multiple lists. PowerPivot can then import these lists through a data feed. For details about how this works, I recommend reading Uday Unni’s excellent white paper “Using SharePoint List Data in PowerPivot” available on MSDN at http://go.microsoft.com/fwlink/?LinkId=221005.

The new data flow is very similar to the original VSTO-based approach. My Timer job just takes on the role of the original VSTO code and the SharePoint lists simply replace the original Excel tables, as already explained. That’s all there is to it. In fact, I asked Analytics for Twitter author Aaron Meyers for permissions to reuse his original code. The source code is included in the Twitter workbook on the Sample Code worksheet. Reusing this code gave me a great head start. Thanks again for sharing, Aaron!

Without going into too much detail about SharePoint programming, here’s how I created my Timer job:

Installed a developer workstation running SharePoint 2010, PowerPivot for SharePoint, and Visual Studio 2010 with Tools for SharePoint Development.
Started Visual Studio with elevated permissions and created an initial SharePoint Timer job project according to the steps outlined in the technical article “How to: Create a Web Application-Scoped Timer Job” on MSDN at http://msdn.microsoft.com/en-us/library/ff798313.aspx.
Copied Aaron’s source code files into the project and modified TwitterSearch.cs so that it would compile. This mainly required commenting out Excel-specific code that is no longer needed and changing some private into protected members to make them accessible in a derived class.
Derived a TwitterSearchListImport class from the TwitterSearch class to implement the SharePoint-specific logic and updated the Timer job definition to perform data imports into my SharePoint lists.

Note: You can find the resulting Visual Studio project called TwitterImportTimerJob in the attachments to this article. The solution is not optimized for performance. It is for demonstration purposes only and not to be used in a production environment.

You can deploy the solution on a SharePoint developer workstation directly in Visual Studio 2010. By default, the Twitter Import Timer job is configured to run daily between midnight and one o’clock in the morning, but it’s also possible to run this job on demand at any time. In SharePoint Central Administration, click on Monitoring, click on Review Job Definitions, then click on Twitter Import Timer Job, and then in the Timer Job configuration click on Run Now. The following figure shows the results for a run in my test environment.

When running the Twitter Import Timer job for the first time, it creates all necessary SharePoint lists automatically, as summarized in the following table.

SharePoint List	Replaces	Comments
TwitterImportStatus	Message boxes that the Excel client displays during the interactive import process	The Twitter Import Timer job runs unattended and can’t display message boxes, so it reports processing status and error messages in the TwitterImportStatus list instead.
SearchTerms	Text box for comma-separated search queries on the Topics, People, Tone, and Details worksheets	The Twitter Import Timer job automatically creates the default search terms in the SearchTerms list. You can add and remove search terms to customize the Twitter import process. Each search term item also has an Include property that you can set to No to exclude the term from the searches without deleting it.
ToneDictList	tblToneDict table on the Tone Dictionary worksheet	The Twitter Import Timer job initializes the ToneDictList with the same entries found in the tblToneDict table.
TweetsList	tblTweets table on the hidden TweetData worksheet	The TweetsList columns are almost identical to the tblTweets table, except that TweetGUID replaces the GUID column and TweetAuthor replaces the Author column.
MentionsList	tblMentions table on the hidden TweetData worksheet	The MentionsList has the same columns as the tblMentions table in the workbook.
HashtagsList	tblHashtags table on the hidden TweetData worksheet	The HashtagsList has the same columns as the tblHashtags table in the workbook.
UtilityList	tblUtility table on the hidden TweetData worksheet	The UtilityList has the same columns as the tblUtility table in the workbook.

Step 2: Updating the PowerPivot model

Ultimately, this step is about replacing the original tables in the workbook’s underlying PowerPivot model with new versions that connect to the corresponding SharePoint lists as their data sources. However, if you simply remove the existing tables, you lose their associated column definitions and measures. It is therefore a good idea to first document the existing PowerPivot model (see TwitterAnalyticsModel.xslx in the attachment to this article for a completed example). It is also a good idea to keep a copy of the original PowerPivot workbook so that you can refer back to the original model if necessary.

There are several important PowerPivot elements in the Twitter Analytics workbook, which the model documentation should cover:

Table relationships Click on the Design tab in the PowerPivot window and then click Manage Relationships. Document the relationships as displayed and then click Close.
Table columns In the PowerPivot window, in Data View, go through each table and document for each column the name, data type, and format, as well as the DAX formula for every calculated column.
Explicit Measures Assuming that you are working with the SQL Server 2012 version of PowerPivot for Excel, it’s straightforward to document the measures because they are displayed in the Calculation Area of each table. Otherwise, you must work with the PowerPivot Field List in the Excel client. Either way, document the measures as you go through each table.
Implicit measures In addition to explicit measures, you must also document any implicit measures that the PivotTables in the Excel workbook created in response to dragging fields to the Values list in the PowerPivot Field List. You can display these measures in the Calculation Area in Advanced Mode. Just select the option Show Implicit Measures on the Advanced tab. Again, go through each PowerPivot table to document the implicit measures. Their DAX formulas are ready only and cannot be copied, so make sure you document them exactly as shown because you need these formulas later to define explicit measures in the updated PowerPivot model.

Having thoroughly documented the PowerPivot model, you are ready for the ultimate task, which is to replace the existing PowerPivot tables with new versions. First, remove the existing tables, but leave the tblDimDate and tbleDimHour tables in place because these are linked to static Excel tables that don’t have VSTO dependencies. The VSTO code doesn’t update tblDimDate or tblDimHour. Next, follow this procedure to create new tables that use SharePoint lists as their data sources:

Make sure you have the Analytics for Twitter.xlsx open in Excel, then go to your SharePoint site and open the ToneDictList in the browser.
On the SharePoint ribbon, click on List, and then click on Export as Data Feed.
In the File Download dialog box, click Open.
In the PowerPivot dialog box, verify that Analytics for Twitter.xlsx is selected, and then click OK.
In the Table Import Wizard, under Friendly Connection Name, type the name of the SharePoint list, such as ToneDictList, and click Next.
On the Select Tables and Views page of the Table Import Wizard, verify that the list is selected, and then click Preview & Filter.
Deselect all SharePoint system columns. In other words, import only the following columns:

Table	Columns
ToneDictList	Tone Index Dictionary
TweetsList	Title TID TweetGUID SearchQuery PubDate PrevPubDate TweetAuthor ToneScore
MentionsList	Username UID
HashtagsList	Hashtag HID
UtilityList	TID HID UID

Click OK and then click Finish.
Verify that the import completes successfully and then click Close.
Right-click the name of the new table and click Rename. Specify the name as follows:

Table	New Name
ToneDictList	tblToneDict
TweetsList	tblTweets
MentionsList	tblMentions
HashtagsList	tblHashtags
UtilityList	tblUtility

Repeat this procedure for the remaining lists TweetsList, MentionsList, HashtagsList, and UtilityList.
Save the changes to the Twitter analytics workbook.

The final step is to reapply the original columns and measures to the new tables as previously documented to ensure that all columns are identical to their original versions, that the calculations work, and that all the required table relationships and measures exist. The following procedure yields the desired result:

Start with the tblToneDict table and convert the data type of the Index column to Whole Number.
Next, continue with the tblTweets table as follows:

Convert the data types as in the original tblTweets table according to your model documentation.
Rename the column TweetGUID into GUID, TweetAuthor into Author, and ToneScore into Tone Score, and SearchQuery into Search Query.
Add the calculated columns. The last two calculated columns Tone and TimeOfDay will display error messages because the table relationships do not yet exist. This is OK.

Now, reestablish the table relationships according to your model documentation and verify that the Tone and TimeOfDay columns in the tblTweets table no longer show an error. Assuming that you are working with the latest version of SQL Server 2012 PowerPivot for Excel 2010, you can use the new Diagram View for this purpose. Otherwise, click on Manage Relationship on the Design ribbon to create the relationships.
Next, edit the tblMentions table to adjust the data type of the UID column and add the calculated MentionCount column according to your model documentation, then update the data types in the tblHashtags tables, and then finish this part of the work by converting the columns in the tblUtility table to the Whole Number data type and adding the calculated columns as outlined in your model documentation.
Finally, add all explicit measures as well as all implicit measures marked as “In Use” in your model documentation to their associated PowerPivot tables. Recreating the implicit measures as explicit measures in the updated model ensures that the PivotTables and cell formulas in the Excel workbook don’t break. The updated model is not exactly identical to the original version anymore, but this is OK because implicit and explicit measures are the same as far as queries are concerned. It’s just substantially easier to redefine these measures explicitly than to reconfigure the PivotTables in the Excel workbook to recreate them implicitly.
Don’t forget to save your changes.

Step 3: Updating the Workbook and Removing the VSTO code

At this point, you have completed the PowerPivot work so you can close the PowerPivot window. What’s left is to update the workbook data in Excel according to the following procedure:

Display the worksheets Topics_Pivot, People_Pivot, and Tone_Pivot by right-clicking a worksheet tab at the bottom of the Excel window and selecting Unhide. In the Unhide dialog box, select the worksheets one at a time and then click OK.
Switch to the Topics_Pivot worksheet and select a PivotTable to display the PowerPivot Field List. If the field list doesn’t appear automatically, click Field List on the PowerPivot ribbon.
Notice the warning in the PowerPivot Field List that the PowerPivot data was modified. Click Refresh. If Excel displays a dialog box asking you to replace the contents of destination cells, click OK.
Check the PivotTables on the People_Pivot, Tone_Pivot, and Details worksheets and repeat steps 2 and 3 if necessary.
Hide the Topics_Pivot, People_Pivot, and Tone_Pivot worksheets again by right-clicking the corresponding worksheet tab at the bottom and selecting Hide.
Display the Data ribbon in Excel and click on Refresh All to fully refresh the workbook one more time, then save your changes.

The final task is to remove the VSTO code and clean up the workbook so that it renders without warnings in Excel Services (a completed workbook is attached to this article). Here are the steps:

In order to remove VSTO, follow the article “How to: Remove Managed Code Extensions from Documents” on MSDN at http://msdn.microsoft.com/en-us/library/bb772099.aspx.
Open the workbook in Excel, then click on File and Options, and then on the Customize Ribbon tab, select the Developer checkbox to display the Developer ribbon. Click OK.
Switch to the Developer tab, enable Design Mode, and then remove the buttons and UI elements in the header areas of the Topics, People, Tone, and Details worksheets to specify search terms. Disable Design Mode again and save your changes.
On each worksheet, remove the remaining unsupported graphical elements, such as the divider line between header and content areas, and the artificial shadow elements around the upper left summary region.
On the Topics worksheet, also delete the Tweet Type and Tone textboxes placed on top of the bottom PivotCharts. Select the left of these two charts, display the Layout ribbon, click on Chart Title, select Centered Overlay Title, and then type Tweet Type, and place the heading in the original position. Repeat this step for the Tone chart title.
Display the Formulas tab, and click Name Manager. Remove all named elements with invalid references or references to external resources. Click Close.
Delete any obsolete worksheets as they might contain elements that Excel Services cannot render. For example, right-click the Sample Code worksheet tab and click Delete. In the Microsoft Excel dialog asking you to confirm that you want to delete this worksheet, click Delete. However, leave the following worksheets in the workbook: Topics, Topic_Pivot, People, People_Pivot, Tone, Tone_Pivot, DimDate, and DimHour.
Save the changes and upload the workbook to SharePoint. Open the workbook in the browser and verify that it provides full interactivity and renders without warnings, as shown in the following screenshot.

Congratulations! This was a serious piece of work, but it was definitely worth it. The Twitter Analytics solution is now a full-blown PowerPivot team application, easily shared through SharePoint and accessible in the browser without the need for Excel 2010 and PowerPivot add-in on the client computer. You can also eliminate the data refresh burden. It was a somewhat complicated and challenging procedure for information workers to refresh the data in the original VSTO version, but now manual refresh is definitely deemphasized. Just configure Scheduled Data Refresh to do this job for you, as illustrated in the following screenshot.

In my test environment, I configured Scheduled Data Refresh to run daily at 4 AM. This was a good choice because my Timer Job tends to finish its import processing around three o’clock every morning according to the Execute Import Job – Finished entries in my TwitterImportStatus list. Refreshing the data during off-business hours after the Twitter Import job finishes ensures that the workbook contains the most recent data when the new workday starts.

And that’s it for now. As demonstrated, you do not necessarily have to use VSTO to build advanced PowerPivot workbooks on top of custom data sources. It can be a valid choice, but in many cases it is worthwhile to look for an alternative import method based on a supported data source. Non-VSTO workbooks are easier to share and can take full advantage of PowerPivot for SharePoint capabilities that are not available to their VSTO counterparts. Admittedly, converting an advanced VSTO workbook, such as the Analytics for Twitter solution, can be a time-consuming effort, especially if you are not the original workbook author, but it’s fortunately not a very common scenario. In any case, I hope you had fun reading this article and following the steps perhaps in your own test environment.

Whitepaper and Samples Released: Data Analysis Expressions (DAX) In the Tabular BI Semantic Model

KayUnkroth — Wed, 04 Jan 2012 18:50:13 GMT

Happy New Year 2012, everybody! With the release of SQL Server 2012 ante portas, this is going to be a very exciting year. So, let’s start this year with a release announcement: An updated version of the whitepaper “Data Analysis Expressions (DAX) In the Tabular BI Semantic Model” is available for download at http://go.microsoft.com/fwlink/?LinkID=237472&clcid=0x409.

Authors Howie Dickerman and Peter Myers and contributors Kasper de Jonge and Owen Duncan worked hard over recent months and the holidays to bring this whitepaper and samples to you ahead of schedule so that you can take advantage of all the DAX functions and new capabilities in your prerelease SQL Server 2012 Analysis Services projects. This whitepaper teaches you how to define simple and advanced calculations in PowerPivot workbooks and tabular model projects authored in SQL Server Data Tools. It explains how to use DAX for extensive filtering to calculate on data across multiple tables, work with relationships, perform dynamic aggregation, and solve real-world business problems.

My all-time favorite is the Row Context and Filter Context section. If you fully understand these concepts, especially in regards to calculated columns, measures, and table relationships, then you are truly a DAX expert. Here’s a question for you: Given a DAX formula, say =[SalesQuantity] * [UnitCost], how does DAX manage to interpret the column names as values? The Row Context and Filter Context section has the answer. Reading this whitepaper… highly recommended for every PowerPivot power user!

Announcing Microsoft SQL Server 2012 Release Candidate 0 (RC0)

KayUnkroth — Thu, 17 Nov 2011 22:19:09 GMT

Today, we’ve reached the next big milestone on the road to unlocking breakthrough insights across organizations with a cloud-ready information platform—we released Microsoft SQL Server 2012 RC0! You can find the installation media at the Microsoft.com Download Center. When you try it, don’t forget to also download the corresponding Microsoft SQL Server 2012 PowerPivot for Microsoft Excel 2010 Release Candidate 0 (RC0).

There are many highlights and stunning BI features in SQL Server 2012. If you evaluated the previous SQL Server 2012 Community Technology Preview 3 (CTP3), you might already know that PowerPivot now supports hierarchies, multiple relationships between tables, a measure grid to easily create, edit, and manage measures and Key Performance Indicators (KPIs), new DAX functions (Time series, distinct count to name a few), and importing of binary large objects (BLOB). All of these features—and many more—are also available on the server in tabular Analysis Services databases, including row-level security, partitions and DirectQuery mode, enabling you to access the information directly from the source system. Substantial improvements can also be found in our SharePoint components. Among other things, we separated setup and configuration tasks, so it’s easy to install and uninstall, configure and re-apply configuration settings. All of these features are also in RC0. Moreover, Project "Crescent"—now called Power View—provides even more features than in CTP3 to create engaging visual experiences that are presentation ready at all times. Try it for yourself and perhaps also check out the SQL Server 2012 Customer Testimonials to see what others are saying about their SQL Server 2012 experience and the features they're most excited about.

Installation prerequisites at a glance:

SQL Server 2012 PowerPivot for Excel RC0 Install Visual Studio Tools for Office (VSTO) 4.0 runtime and .NET Framework 4.0 before you install PowerPivot. You can find the VSTO runtime at http://go.microsoft.com/fwlink/?LinkId=232657 and the .NET Framework 4.0 at http://go.microsoft.com/fwlink/?LinkId=232658. Also, if you are running Windows Vista or Windows Server 2008, install the Platform Update at http://go.microsoft.com/fwlink/?LinkId=186051.
SQL Server 2012 PowerPivot for SharePoint RC0 Install Microsoft SharePoint Server 2010 Service Pack 1 (SP1) available at the Microsoft.com Download Center at http://www.microsoft.com/download/en/details.aspx?id=26623. You must fully patch the SharePoint farm before you add SQL Server 2012 RC0 features. No separate PowerPivot download required because the PowerPivot for SharePoint components are installed through SQL Server Setup. Run the PowerPivot Configuration Wizard after the installation to configure your farm.
SQL Server 2012 Analysis Services RC0 Install .NET Framework 3.5 Service Pack 1 as well as .NET Framework 4.0 and Windows PowerShell 2.0. You also need an update for the .NET 3.5 SP1:
- For Windows Vista SP2 or Widows Server 2008 SP2, go to http://go.microsoft.com/fwlink/?LinkId=198093.
- For Windows 7 or Windows 2008 R2, go to http://go.microsoft.com/fwlink/?LinkId=198092.

For additional information and known issues, check out the Microsoft SQL Server 2012 RC0 Release Notes and the PowerPivot for Excel Online Help. You can access additional articles, blogs, and other community content on the PowerPivot TechNet page. And, of course, you can also find more info on PowerPivot and Analysis Services features on this blog as well as on the Reporting Services team blog at http://blogs.msdn.com/b/sqlrsteamblog/.

Released: DAX Editor for SQL Server

Cathy Dumas - MSFT — Thu, 03 Nov 2011 22:57:31 GMT

I am very pleased to announce that the DAX Editor for SQL Server has been released to CodePlex for general use. It is available at http://daxeditor.codeplex.com/. Thank you Nick Medveditskov, Karen Aleksanyan, and Sergey Volegov for your hard work in getting this sample up and running.

My previous blog post on the DAX Editor provides an overview of the project. Detailed usage notes are on the home page for the DAX Editor.

We are happy to hear about bug reports and feature requests. This is a project that our testers put together in their “spare time” so our ability to make changes may be limited at this time. Fortunately, since the code is available, you can feel free to help out and enhance the product.

Have fun!

Impact of the SQL Server OLE DB Deprecation on Analysis Services

T.K. Anand — Wed, 14 Sep 2011 06:10:00 GMT

A few weeks back, the SQL Server connectivity team announced on their blog that the SQL Server OLE DB provider is being deprecated and recommended that developers adopt ODBC instead. You can read the blog post in detail here. There has been some concern in the Microsoft BI community about this announcement and how it affects Analysis Services and BI solutions built on it. This blog post attempts to address these concerns.

Firstly, I would like to clarify that although the SQL Server OLE DB provider (also called SQLNCLI or SNAC OLE DB) is being deprecated, the OLE DB API and the OLE DB core components that ship in Windows are not being deprecated. There are a number of other OLE DB providers, some developed by Microsoft and some by third parties and ISVs, that are unaffected by this announcement. This includes the Analysis Services OLE DB provider (MSOLAP).

Analysis Services is an OLE DB consumer as well as an OLE DB provider.

OLE DB consumer: Analysis Services supports OLE DB for consuming data from relational databases such as SQL Server, Oracle, etc. It does not support ODBC directly although you can use the MSDASQL (OLE DB to ODBC bridge) provider to access ODBC sources. It does support ADO.NET however there is a performance penalty for the Analysis Services native code to ADO.NET managed code interop. For these reasons, OLE DB is the most recommended and high performance interface for Analysis Services to consume data. And this continues to be true in SQL Server “Denali”.
OLE DB provider: Analysis Services supports OLE DB for exposing data and analytics to client applications such Excel. The Analysis Services OLE DB provider (MSOLAP) supports the OLAP and Data Mining extensions to OLE DB that BI client applications rely on. The MSOLAP provider ships with every release of SQL Server (including “Denali”) and is also distributed with every copy of Excel.

SQL Server is the most commonly used data source for Analysis Services and SNAC OLE DB is most recommended and high performance way for Analysis Services to consume data from SQL Server. This recommendation continues to hold true in SQL Server “Denali” as well. So if you are building a new BI application using SQL Server “Denali”, our recommendation is to use SNAC OLE DB. Even though SNAC OLE DB is being deprecated, it is still supported in SQL Server “Denali”.

As a result of the SNAC OLE DB deprecation, Analysis Services will be enhanced in a future release to natively support ODBC for consuming data. At that time, customers can switch their BI applications from SNAC OLE DB to SNAC ODBC. This switch should be relatively straightforward and require just updating the connection string in the Analysis Services DataSource object.

Hopefully this blog post addresses most of the questions and concerns on the SQL Server OLE DB deprecation. If not, please add your comments to this post and we will do our best to clarify.

Exploring the PowerPivot for SharePoint Configuration Tool

KayUnkroth — Thu, 01 Sep 2011 17:57:00 GMT

The PowerPivot Configuration Tool included in SQL Server Code Name “Denali” CTP3 is undoubtedly a great new utility for SharePoint administrators. This tool doesn’t just enable you to configure or upgrade PowerPivot in a SharePoint farm. It takes PowerPivot configuration to a whole new level. Here are some key improvements in comparison to what’s available with SQL Server 2008 R2:

No special SharePoint or PowerPivot knowledge required to create a PowerPivot-enabled SharePoint farm.
Configuration tasks are separated from installation tasks—SharePoint configuration issues no longer interfere with the SQL Server Setup process.
All configuration settings are validated prior to applying changes.
The configuration process can be interrupted and resumed at any time.
Settings and results are automatically documented (except passwords and passphrases).

Let’s have some fun exploring this tool. First, you need an installation of Denali CTP3 PowerPivot for SharePoint. Thanks to the separation of installation and configuration tasks, this is relatively quickly accomplished, especially if you are deploying a single-server test environment. Log on using a domain account with administrative permissions on the local computer, install SharePoint 2010 (choose the options Server Farm and Complete – Install All Components) without running the SharePoint 2010 Products Configuration Wizard afterwards, and then update this installation with Service Pack 1 for SharePoint 2010, again without running the SharePoint 2010 Products Configuration Wizard. Next, launch Denali CTP3 Setup. On the Setup Role screen, select the options SQL Server PowerPivot for SharePoint and Add SQL Server Database Relational Engine Services to this Installation, and then click Next in all remaining wizard screens to accept the default settings as suggested. You only need to provide a domain account and a password for the Analysis Services service. Note that you must use a domain account to support PowerPivot for SharePoint, but this domain account does not require administrative permissions on the local computer.

Moving on to the PowerPivot Configuration Tool, the first thing to note is that Denali CTP3 Setup only installs and configures the SQL Server program files and services on the local computer, but it doesn’t configure the SharePoint farm. This is where the new PowerPivot Configuration Tool comes into play. Start it by clicking on the PowerPivot Configuration Tool link, which you can find on the Start menu under All Programs, Microsoft SQL Server Denali CTP3, and Configuration Tools. You can also start it from within SQL Server Installation Center, if you look under Tools. On the PowerPivot Configuration Tool’s first screen, click Configure or Repair PowerPivot for SharePoint, as depicted in the following screenshot.

At a first glance, the PowerPivot Configuration Tool might be overwhelming. It just covers a lot of ground. Take a moment to get familiar with the user interface. The left pane shows you a long list of actions that the tool is designed to carry out, neatly organized in a tree view for informational purposes. The right pane has three tabs: Parameters, Script, and Output. Their purpose is self-explanatory. As you can see in the screenshot above, you only need to specify five parameters to configure a new SharePoint farm for PowerPivot: service account, password, database server name, passphrase, and TCP port for Central Administration. The configuration tool even makes an attempt to suggest meaningful default parameters, so you only need to specify password and passphrase.

There are some subtle features in the PowerPivot Configuration Tool that only a connoisseur can truly appreciate. My absolute favorite is parameter validation carried out without performing any actual configuration tasks in SharePoint. This is one of those work items that is usually taken for granted and yet it takes so much to do it right. If you have written software, you know how tedious it is to validate user input, and here you see a tool that doesn’t just validate input, but goes through an exhaustive series of checks to ensure the PowerPivot for SharePoint configuration can succeed, as shown in the following screenshot. If you happen to change a setting after validation, the tool automatically flags the affected steps in the left pane and requires you to validate the configuration parameters again. Kudos to Software Developer Engineer Fernando Godinez Delgado, who built the PowerPivot Configuration Tool!

Perhaps you are not as excited about parameter validation as I am, but if you are an experienced SharePoint administrator or troubleshooter, you might get excited about the Script tab. This tab shows you the commands that the tool is going to execute when you click on the Run button. You can say that the PowerPivot Configuration Tool is essentially a script executor and nothing stops you from executing the commands manually. Note that the commands on the Script tab include the user input from the Parameters tab. It’s easy to copy all or individual sections of the script to the clipboard and paste them into a PowerShell window. Here’s an example demonstrating how helpful this is in a troubleshooting situation:

Run the PowerPivot Configuration Tool, specify the service account password as required, and then make sure you type AYX$dcv?00 into the Passphrase and Confirm Passphrase textboxes.

Hint: The dollar sign in the passphrase is super important because it causes the Secure Store configuration to fail, as shown in the following screenshot, even though the passphrase meets the complexity requirements. I’m exploiting a known CTP3 issue, which causes the error. The issue is most likely going to be fixed in the final release.

Click Validate, which correctly succeeds, and then click Run.
In the dialog box informing you that all configuration settings flagged as valid will be applied to the SharePoint farm, click Yes.
Note that the configuration unexpectedly fails when trying to update the Secure Store Master Key. In the Task Configuration dialog box, informing you that one or more actions failed, click OK.

Prior to Denali CTP3 and the PowerPivot Configuration Tool, such configuration issues would have caused SQL Server Setup to fail and roll back the entire deployment. Now, you can just switch to SharePoint 2010 Central Administration, click on Manage Service Applications, click on Secure Store Service, click on Generate New Key, and then under Pass Phrase and Confirm Pass Phrase, type the passphrase, and then click OK. Having generated the master key in this way, you can switch back to the PowerPivot Configuration Tool, reevaluate the configuration, and continue with the remaining steps. Yes, the PowerPivot Configuration Tool enables you to succeed even if you encounter unexpected configuration issues along the way.

But why does this update of the master key fail in the PowerPivot Configuration Tool? The error message includes an important hint: The passphrase supplied does not meet the minimum complexity requirements. This isn’t true because AYX$dcv?00 certainly meets the requirements. So, what exactly is failing? Let’s switch to the Script tab, locate the command, and investigate the root cause. The command line in question is undoubtedly:

UpdateSecureStoreMasterKey "Secure Store Proxy" "********"

The ConfigurePowerPivot.ps1 library that ships with the PowerPivot Configuration Tool implements the UpdateSecureStoreMasterKey function. By default, this library is located in the %ProgramFiles%\Microsoft SQL Server\110\Tools\PowerPivotTools\ConfigurationTool\Resources folder. You can determine the exact path on the Script tab. Just check the very first line, such as
# Open PowerShell library from: C:\Program Files\Microsoft SQL Server\110\Tools\PowerPivotTools\ConfigurationTool\Resources\ConfigurePowerPivot.ps1. Open this .ps1 file in Notepad, search for UpdateSecureStoreMasterKey, and you should find the corresponding function, which is implemented as follows:

Function UpdateSecureStoreMasterKey
{
       param($proxyName, $farmPassPhrase)

       ### Retrieve secure store service application proxy
       $proxy = Get-SPServiceApplicationProxy | where {$_.DisplayName -eq $proxyName}

       if($proxy)
       {
              Update-SPSecureStoreMasterKey -ServiceApplicationProxy $proxy -Passphrase $farmPassPhrase
              start-sleep -s 60

              Update-SPSecureStoreApplicationServerKey -ServiceApplicationProxy $proxy -Passphrase $farmPassPhrase
              start-sleep -s 60
       }
       else
       {
              throw "Secure Store Service Application proxy doesn't exist"
       }
}

As you can see, this function relies on standard SharePoint 2010 cmdlets and the parameters $proxyName and $farmPassPhrase receive the strings "Secure Store Proxy" and “AYX$dcv?00”. Let’s run these cmdlets manually to see what happens:

Start the SharePoint 2010 Management Shell by right-clicking on the corresponding link in the Microsoft SharePoint 2010 Products program group in the Start menu, and clicking Run as Administrator.
In the User Account Control dialog box, asking you if you want to continue, click Yes.
Type $proxyName = "Secure Store Proxy" and press Enter to set the first parameter.
Type $farmPassPhrase = "AYX$dcv?00" and press Enter to set the second parameter.
Type $proxy = Get-SPServiceApplicationProxy | where {$_.DisplayName -eq $proxyName} and press Enter to retrieve the Secure Store Service Application Proxy exactly as the PowerPivot Configuration Tool would do.
Type $proxy and press Enter to verify that the SharePoint cmdlet has successfully retrieved the service application proxy. The output should include DisplayName, TypeName, and ID.
Type Update-SPSecureStoreMasterKey -ServiceApplicationProxy $proxy -Passphrase $farmPassPhrase and press Enter to update the Secure Store Master Key. Note that the Update-SPSecureStoreMasterKey fails with the same error message that you already encountered in the PowerPivot Configuration Tool, as in the following screenshot.

So, it is the Update-SPSecureStoreMasterKey cmdlet that is failing. Does it incorrectly reject the specified passphrase? Well, not really. The Update-SPSecureStoreMasterKey cmdlet is fine. It’s our script that forgets to escape the dollar sign in the passphrase. The passphrase is enclosed in double quotation marks, so PowerShell is interpreting the dollar sign as a special character. You can verify this by typing $farmPassPhrase in the PowerShell window and pressing Enter. The output only includes AYX. Clearly, not a valid passphrase! To fix this, type $farmPassPhrase = 'AYX$dcv?00' and press Enter. The single quotation marks turn the passphrase into a literal string and eliminate the need to escape the dollar sign. Type $farmPassPhrase again and press Enter to verify that the entire passphrase is now returned. Repeat the command Update-SPSecureStoreMasterKey -ServiceApplicationProxy $proxy -Passphrase $farmPassPhrase and it will now succeed, as the following screenshot proves. And then, finish the configuration procedure by typing Update-SPSecureStoreApplicationServerKey -ServiceApplicationProxy $proxy -Passphrase $farmPassPhrase and pressing Enter again.

At this point, the master key issue has been resolved and you can switch back to the PowerPivot Configuration Tool to continue, as mentioned earlier. If you already closed the tool, you can start it again. Choose Configure or Repair PowerPivot for SharePoint, type any required information, such as the Default Account Password, and then click Validate. Note that the PowerPivot Configuration Tool detects the SharePoint configuration and continues with the correct step after the master key update (see the following screenshot).

Additionally, you can examine the individual steps that the PowerPivot Configuration Tool performed in great detail if you switch to the Output tab. Note that the tool automatically scrolls to the output section that corresponds to the selected action in the left pane. If you select Create Unattended Account for DataRefresh in the left pane, for instance, the tool scrolls down to display the relevant sections for CreateUnattendedAccountForDataRefresh and so forth. Note also that you can analyze the output outside of the PowerPivot Configuration Tool. Every time you run the tool, it writes the output into an xml file, which stores the values you entered and the results from the run. Output files are located in the %ProgramFiles%\Microsoft SQL Server\110\Tools\PowerPivotTools\ConfigurationTool\Log folder.

This concludes this excursion into the PowerPivot Configuration Tool. I hope you find it useful in your work as a SharePoint administrator. In one of the next posts, I’m going to show you how to use this tool to configure a multi-server SharePoint farm. Stay tuned!

Tips and Tricks: Mapping Data to the World in a PowerPivot Workbook

KayUnkroth — Mon, 15 Aug 2011 21:53:10 GMT

Last week I came across a stunning PowerPivot solution that visualizes research data on top of a world map, as illustrated in the following screenshot. What’s particularly stunning about this solution is that it works even in the browser despite the fact that Excel Services doesn’t support custom shapes or background images for country objects or maps. How did these guys manage to get the data onto this map? Out of sheer curiosity, I asked the creators and was happy to learn that it isn’t too complicated. The trick is to use a semi-transparent image in the foreground. All you need is Excel 2010 with PowerPivot and Excel Services with PowerPivot for SharePoint—and a Portable Network Graphics (PNG) file of a world map with perhaps 75% transparency.

The originator of the idea and project sponsor for the actual business solution here at Microsoft is Senior Business Development Manager Hicki Okamoto. Jeremiah Hansen, BI Consultant at Slalom Consulting, implemented it. Jeremiah also helped me to recreate the solution based on publicly available sample data by sharing his solution files with me. Thanks again, Hicki and Jeremiah!

This article’s sample solution relies on a Contoso Electronics workbook, created by following the tutorial “Create Your First PowerPivot Workbook” in the SQL Server PowerPivot for Microsoft Excel Help file. Just press F1 in PowerPivot to display the help information. It is not necessary to complete the entire tutorial. Stop after creating a calculated column for total profit. The finished workbook (50 MB) is also attached to this blog post.

The first step in building the Contoso Electronics dashboard is nothing new or special for PowerPivot users. Just add a PivotTable on a new worksheet. You might also want to hide the tutorial’s Stores worksheet (right-click the worksheet tab and click Hide) so that your workbook only shows Sheet1 with an empty PivotTable1.

Now, for the Contoso Electronics dashboard, let’s display the total sales amount in millions of dollars and the corresponding year-over-year growth (abbreviated as YoY Δ) for specific geographic regions. If you need a primer on calculating year-over-year growth, read Howie’s excellent article Time Intelligence Functions in DAX here at our team blog site. Howie calculates year-over-year growth for Contoso’s Store Sales. My approach is only slightly different to calculate year-over-year growth for all sales (not just Store Sales). Add the following measures to the FactSales table:

Measure Name	Measure Formula	Comments
TotalSalesInMillions	=CONCATENATE("$", CONCATENATE(FORMAT(CALCULATE(ROUND(SUM (FactSales[SalesAmount])/1000000, 0)) , "#,###"), "M"))	Creates a string that encloses the sales amount in a dollar sign and an ‘M’ for millions. The value is rounded to a million.
SalesPrevYr	=CALCULATE(SUM (FactSales[SalesAmount]), DATEADD(DimDate[DateKey], -1, YEAR))	Calculates the sales amount for the previous year.
YoY Δ	=IF(SUM(FactSales[SalesAmount]),(IF ([SalesPrevYr],(SUM (FactSales[SalesAmount]) - [SalesPrevYr])/[SalesPrevYr], BLANK())),BLANK())	Calculates the year-over-year growth if the current year and the previous year have sales values. The formula returns BLANK() if the previous or the current year has no value.

With the required measures in place, select PivotTable1 on Sheet1 and drag the following fields to the field areas in the PowerPivot Field List (see also the following screenshot):

Field Area	Fields	Source Tables
∑ Values:	TotalSalesInMillions, YoY Δ	FactSales
Row Labels:	ContentName, RegionCountryName	Geography
Slicers Horizontal:	Fiscal Year, ChannelName, ProductCategoryName	DimDate, DimChannel, ProductCategory

The next task is to rearrange the cells according to their geographic location on the map. As a prerequisite to this step, you must convert the PivotTable to individual formulas similar to the procedures described in the blog post Creating a Stock Ticker View for Performance Data in PowerPivot. It’s straightforward: With the PivotTable selected, switch to the Options ribbon, and then under OLAP Tools, select Convert to Formulas. Delete all rows that you don’t want to place on the map.

Follow these steps to finish the work on the data cells:

Select the YoY Δ data cells, right-click the selection, select Format Cells, and then on the Number tab under Category select Percentage and click OK.
Select the YoY Δ header cell and copy the formula without the equal sign to the clipboard, that is CUBEMEMBER("PowerPivot Data","[Measures].[YoY Δ]").
Select the first YoY Δ data cell and replace the cell reference for the YoY Δ header cell in the formula (such as D$11) with the expression you copied to the clipboard in the previous step. Verify that the value of the data cell remains the same when you apply the changes.
Double-click the lower right corner of the selected data cell to apply the formula changes to all remaining YoY Δ data cells.
Delete the YoY Δ header cell and verify that the YoY Δ values remain unaffected.
Repeat this procedure for the TotalSalesInMillions column.

The growth indicators are still missing. Follow these steps to add them:

Select the cell to the right of the first YoY Δ data cell, then type an equal sign (=) into the formula box, select the YoY Δ data cell, and then press Enter. Verify that the cell has the same value as its neighbor.
Select the cell again and then double-click the lower right corner to apply the formula changes to all remaining YoY Δ data cells.
Select all newly created cells, and then on the Home ribbon, under Conditional Formatting, select New Rule.
In the New Formatting Rule, under Format Style, select Icon Sets.
Under Icon Style, select the style Five Icons Colored.
For all icons, change the Type to Number, and then specify a desired value for each icon, such as 0.15, 0.05, -0.05, and -0.15.
Select the checkbox Show Icon Only and then click OK twice.
In the FiscalYear slicer, click on 2008. Verify that the growth indicators work, as the following screenshot illustrates. Note that YoY Δ values are only available for the fiscal years 2008 and 2009 in the data set.

This concludes the PowerPivot work. Next comes the creative part. Let’s add the map and move the cells to their correct locations:

Move the slicers to the very bottom of the worksheet for now.
Switch to the Paige Layout tab and then click on Background.
In the Sheet Background dialog box, select the .png file of your world map, and then click Open.
Using the world map as a background image on the worksheet helps moving the cells to the right locations. Adjust font styles and sizes, merge cells, and align text as necessary.

The dashboard isn’t finished yet. As mentioned earlier, Excel Services does not support background images on worksheets, so you must remove the background again and insert the semi-transparent image in the foreground. Follow these steps:

On the Paige Layout tab, click on Delete Background.
Select cell A1, switch to the Insert tab, and then click Picture.
In the Insert Picture dialog box, select the .png file of your world map, and then click Insert.
Resize the image to match the locations of your data cells.
Right-click the header of row 1 and click Insert. Repeat this step to insert 5 new rows at the top of the worksheet. Note that map and data cells move down together.
Enter a dashboard title and other useful information in the first row (see screenshot).
Right-click the image and select Send to Back so that the image is behind the slicer objects.
Move the slicers back to the top and resize them to make use of the available space.
Apply header formatting, such as background and borders, as desired.
Right-click Sheet1 at the bottom, click Rename, and then type World and press Enter.
Open the File menu, click Options, select Advanced, and then clear the checkboxes Show Row and Column Headers and Show Gridlines.
Click OK and save your work.

You can now upload the workbook to SharePoint and interact with it in a Web browser (see the screenshot earlier in this article). Of course, you can also add the solution in an Excel Web Access Web Part to a dashboard page. In the following screenshot, I added the worksheet to the homepage of a PowerPivot site. Never cease to be amazed at the versatility of Excel Services and PowerPivot!

In a follow-up blog post, I’m planning to show you how to convert this dashboard from a PowerPivot workbook to a tabular BI Semantic Model in order to support larger data sets, row-level security, and other cool features. Stay tuned!

Released: SQL Server Code Name “Denali” CTP3 Product Guide

KayUnkroth — Wed, 10 Aug 2011 18:48:58 GMT

Looking for datasheets, white papers, click-through and auto-running demonstrations, hands-on lab previews, technical presentations, and other useful links to evaluate SQL Server Code Name 'Denali' CTP3? Go to the Microsoft Download Center and search for “CTP3 Product Guide” or simply click on the following link: http://go.microsoft.com/fwlink/?LinkID=225814.

The SQL Server Denali CTP3 Product Guide includes the following material:

14 Product Datasheets
8 PowerPoint Presentations
5 Technical White Papers
13 Hands-On Lab Preview Documents
6 Click-Through Demonstrations
13 Self-Running Demonstrations
26 Links to On-Line References
44 Links to On-Line Videos including 26 Presentations from North America TechEd 2011

And please Submit Your Feedback on SQL Server Denali CTP3 to help us deliver the new version of SQL Server in the highest quality. We appreciate your contribution!

Analysis Services Blog Post Roundup for Denali CTP3

KayUnkroth — Tue, 09 Aug 2011 02:05:59 GMT

A few AS team members have started blogging on Denali functionality at their personal blog sites.

Cathy Dumas, a Program Manager on our team, is just warming up on her blog. She’s got a couple of interesting posts:

Kasper de Jonge, another Program Manager on our team, has been busy on his PowerPivot blog. He’s got a bunch of posts with great how-to information on:

Keep an eye out for more great content, both on the AS team blog and on the individual team member’s blogs.

How to Build a VSTO-Based PowerPivot Workbook

KayUnkroth — Thu, 04 Aug 2011 23:19:00 GMT

Microsoft Visual Studio Tools for Office (VSTO) is an awesome development suite for building advanced Office solutions, such as Excel applications that go above and beyond default features and capabilities. Even our PowerPivot for Excel add-in is based on VSTO. However, it is important to note that VSTO does not define application programming interfaces (APIs) for applying customizations on top of customizations. In other words, you can develop applications on top of Excel by using VSTO but you cannot develop applications on top of PowerPivot because VSTO does not include any APIs for this purpose and the PowerPivot add-in does not add any such APIs either. Appropriate APIs are only available with PowerPivot for SharePoint and SQL Server Analysis Services.

While it is not supported to interact with PowerPivot for Excel programmatically, you can very well interact with the usual elements of an Excel workbook by using VSTO and therefore—given that every PowerPivot workbook is essentially an Excel workbook—you can use VSTO to build advanced PowerPivot solutions. Just don’t attempt to go beyond programming Excel features. The Microsoft sample solutions Calendar Analytics Tool and Analytics for Twitter highlight this limitation. These solutions rely on VSTO to pull source data into the workbook and then remind the user to update the embedded PowerPivot database and PivotTable data manually, as the following figure illustrates. It would be great to automate these steps, but this requires a PowerPivot API that isn’t available, as mentioned before.

Another important limitation that you must keep in mind when building PowerPivot workbooks by using VSTO is that you cannot place datasets with more than 1 million rows on a worksheet because the maximum worksheet size in Excel 2010 is 1,048,576 rows by 16,384 columns. Furthermore, VSTO-based workbooks are not fully functioning without their VSTO components. For example, you cannot just copy the Analytics for Twitter.xlsx file to another computer or upload this workbook to a document library in SharePoint and expect the VSTO-based code to work. You can interact with the workbook’s slicers and PowerPivot data, but you cannot import updated source data if the corresponding VSTO code isn’t available. If you want to share a VSTO-based PowerPivot workbook, you must publish the workbook as a ClickOnce application (see ClickOnce Deployment Overview) or provide a Windows Installer package for deployment (see Deploying a Visual Studio 2010 Tools for Office Solution Using Windows Installer). (In another blog post, I’m going to show you how to redesign the Analytics for Twitter workbook so that it doesn’t depend on VSTO in order to share and update it in SharePoint.)

Having pointed out these limitations, what’s cool about VSTO is that it can bring data into a PowerPivot workbook even if there is no suitable data provider, such as Outlook items, tweets, or perhaps Windows Event Log entries, as I’m going to show you next. Without VSTO, you might have to export the data into an intermediary text file and then import the data into PowerPivot (see the blog post Analyzing Event Log Entries in PowerPivot). This is inconvenient. With VSTO, custom code can pull the data into one or multiple tables in an Excel workbook, linked to one or multiple PowerPivot tables, as illustrated in the following figure.

In the Analytics for Twitter solution, the data flows as follows:

When you click on the search button in the workbook, VSTO code pulls data from Twitter, processes the data, and places the results in a number of Excel tables on a hidden TweetData worksheet. (You can display this worksheet, by right-clicking on the tabs at the bottom of the workbook and selecting Unhide. In the Unhide dialog box, select TweetData and click OK.)
When you click on Update All on the PowerPivot ribbon, PowerPivot pulls the data from the Excel tables into linked PowerPivot tables.
When you click on Refresh All on the Data ribbon, Excel updates the data in its workbook cache based on the data from the linked PowerPivot tables. The PivotTables and PivotCharts on the worksheets now display the most recent data.

This data flow is not unique to the Analytics for Twitter solution. Aaron Meyers, creator of the Analytics for Twitter solution, was kind enough to include the TwitterSearch.cs source code on the Sample Code worksheet, which demonstrates all the details to get the job done. So, let’s see how these pieces fit together by building a VSTO-based PowerPivot workbook that follows the Analytics for Twitter approach but gets data from the Windows Event Log.

The first step is to create a workbook by using Visual Studio Tools for Office:

Make sure you have Excel 2010, PowerPivot add-in, and VSTO installed on your computer.
Create a New Project in Visual Studio. In the New Project dialog box, under Visual C# and Office select 2010, and then select Excel 2010 Workbook, as in the following screenshot. Click OK.
In the document selection dialog box, click OK to accept the defaults.
If a warning is displayed that you must explicitly enable access to the Visual Basic for Applications project system to create a VSTP project, click OK to allow access.
Verify that the workbook is opened in Visual Studio.

So far so good! Now, let’s add a table to the workbook and populate it by using VSTO-based code. Note that Excel assigns the table a generic name of Table1. The code below uses this name. If you want to use a different name, select a cell in the table and then on the Design ribbon, in the Properties group, type the desired name in the Table Name textbox. Make sure you update the table reference in the code (Globals.Sheet1.Range["Table1"]) accordingly.

Follow these steps to add a table and populate it programmatically:

In the workbook on Sheet1, opened in Visual Studio, type the following values in the cells of the first row:

A1	B1	C1	D1	E1	F1	G1	H1	I1	J1	K1
Log	Category	CategoryNumber	EntryType	InstanceId	MachineName	Message	Source	TimeGenerated	TimeWritten	UserName

Select cells A1 through K1, and then on the Home ribbon, under Styles, click Format as Table, and select a table format of your choice.
In the Format As Table dialog box, select the checkbox My Table Has Headers, and then click OK.
Switch to Sheet2, display the Visual Studio Toolbox, and drag a button onto the worksheet. Place the button in the upper left corner.
Right-click the button and then click Properties.
Change the Text property to Get Data, change the (Name) property to btnGetData, and then double-click the Get Data button to add a btnGetData_Click method to Sheet2.cs.
Replace the empty btnGetData_Click method with the following test code:

private void btnGetData_Click(object sender, EventArgs e)
{
   // Show an hourglass while Excel refreshes the source data table.
   this.Application.Cursor = Excel.XlMousePointer.xlWait;

   // Grab the linked table on Sheet1, named "Table1" by default.
   Excel.Range linkedTable = Globals.Sheet1.Range["Table1"] as Excel.Range;

   // Retrieve Event Log data.
   object[,] eventData = GetEventData();

   // Replace the values in the Excel table.
   linkedTable.ClearContents();
   linkedTable = linkedTable.Resize[eventData.Length / 11];
   linkedTable.Value = eventData;

   // Tell the user what to do next.
   MessageBox.Show("Next Steps:\n" +
    "1. Click PowerPivot --> Update All\n2. Click Data --> Refresh All",
    "Next Steps", MessageBoxButtons.OK, MessageBoxIcon.Information);

   // Restore the default mouse cursor.
   this.Application.Cursor = Excel.XlMousePointer.xlDefault;
}

/// <summary>
/// Reformats the list of event log entries for use as content of an Excel table.
/// </summary>
/// <returns>An object of rows and columns.</returns>
private object[,] GetEventData()
{
   List<object[]> dataRows = GetDataRows();

   int rows = dataRows.Count;
   int columns = 11;
   object[,] eventData = new object[rows, columns];

   int row = 0;
   foreach (object[] row_ar in dataRows)
   {
    for (int col = 0; col < columns; col++)
    {
     eventData[row, col] = row_ar[col];
    }
    row++;
   }

   return eventData;
}

/// <summary>
/// Retrieves the event data from the local Windows Event Log.
/// </summary>
/// <returns>A List object with a separate row for each event log entry.</returns>
private List<object[]> GetDataRows()
{
   // Create a list object for the event log data.
   List<object[]> dataRows = new List<object[]>();

   // Iterate through all the event logs available on the local computer.
   foreach (System.Diagnostics.EventLog evtLog in System.Diagnostics.EventLog.GetEventLogs())
   {
    try
    {
     // Add the contents of the currently selected event log to the list of data rows.
     foreach (System.Diagnostics.EventLogEntry logEntry in evtLog.Entries)
     {
      object[] dataRow = new object[11];

      dataRow[0] = evtLog.Log;
      dataRow[1] = logEntry.Category;
      dataRow[2] = logEntry.CategoryNumber;
      dataRow[3] = logEntry.EntryType.ToString();
      dataRow[4] = logEntry.InstanceId;
      dataRow[5] = logEntry.MachineName;
      dataRow[6] = logEntry.Message;
      dataRow[7] = logEntry.Source;
      dataRow[8] = logEntry.TimeGenerated.ToString();
      dataRow[9] = logEntry.TimeWritten.ToString();
      dataRow[10] = logEntry.UserName;

      dataRows.Add(dataRow);
     }
    }
    catch { /* On Error Resume Next */ }
   }

   return dataRows;
}

Press F5 to test the solution. Visual Studio opens a new Excel application window. On Sheet2, click on the Get Data button, wait until Excel has finished importing data, and then in the Next Steps dialog box, click OK. Verify that the table on Sheet1 is filled with data and then close the Excel workbook. When prompted, save the changes.

At this point, the VSTO portion to retrieve data from the local Windows Event Log is finished. Now, let’s engage in some PowerPivot plumbing outside of the VSTO environment. This is the tricky part. Note that you must perform this work in the customized (“compiled”) version of the workbook. Subsequently, you must remove the VSTO customization from this workbook so that you can use it as the project workbook in Visual Studio. If you forget to remove the VSTO customization, Visual Studio won’t be able to build the customized version of the workbook anymore. If you forget to replace the original project workbook, rebuilding the workbook in Visual Studio will overwrite your PowerPivot version. Note also that I am specifically not performing the PowerPivot work on the worksheet in Visual Studio because PowerPivot for Excel is not designed to run within a Visual Studio OLE container.

Follow these steps to create a linked PowerPivot table, finish the workbook, remove the VSTO customization, and replace the original project workbook with the PowerPivot version:

In Visual Studio Solution Explorer, right-click the project ExcelWorkbook1, and then click on Open Folder in Windows Explorer.
Close Visual Studio and, if you are prompted to save any changes, click Yes.
In Windows Explorer, expand the Bin and Debug folders, and then open the ExcelWorkbook1.xlsx file in Excel. This is the customized version of the workbook.
If you receive an error message that the workbook is already opened, click OK, close the Excel workbook without saving any changes, and then open the workbook again.
On Sheet1, verify that the table contains the data imported in the previous test run, and then on the PowerPivot ribbon click Create Linked Table.
Verify that the PowerPivot window is displayed with the event data imported from the Excel table. Close the PowerPivot window.
Back in the Excel window, right-click Sheet1 at the bottom, and then click Hide to make the source data table disappear from the user’s view.
Switch to Sheet2 and then on the PowerPivot ribbon, in the Report section, click on the little triangle underneath PivotTable. Select the option Chart and Table (Horizontal).
In the Create PivotChart and PivotTable (Horizontal) dialog box, select Existing Worksheet, specify 'Sheet2'!$A$3 as the Location, and then click OK.
PowerPivot places an empty chart and an empty PivotTable on the worksheet. Select the chart and then, in the PowerPivot Field List to the right, drag the following fields to the field areas:

Field Area	Fields
∑ Values:	Message (automatically changes to Count of Message)
Axis Fields (Categories):	TimeGenerated
Slicers Vertical:	Log, EntryType

On the Excel worksheet, click in the area of the PivotTable, and then in the PowerPivot Field List drag the fields Source and Message onto the Row Labels field area.
On the Excel worksheet, right-click the column that contains the PivotTable, such as column D, and click Column Width. Under Column Width, type 66, and then click OK.
Right-click the column that contains the PivotTable again and click PivotTable Options, and then in the PivotTable Options dialog box, clear the checkbox Autofit Column Widths On Update. Click OK.
On the Excel menu, click the File tab, then click Properties on the right and select Advanced Properties.
In the ExcelWorkbook1.xlsx Properties dialog box, switch to the Custom tab, and then delete the custom properties called _AssemblyName and _AssemblyLocation (see Custom Document Properties Overview on MSDN). These custom document properties associate the workbook with the VSTO customization assembly. Removing these properties is a prerequisite to using this PowerPivot version as the project workbook in Visual Studio. Click OK.
Switch back to the Home tab, save your work, and close Excel.
Copy the workbook from the \Bin\Debug folder to the parent folder ExcelWorkbook1, which contains the project workbook with the same name (ExcelWorkbook1.xlsx). Confirm that you want to replace the original project workbook with the PowerPivot version.
Start Visual Studio and open the ExcelWorkbook1 project. Note that Visual Studio now displays the project workbook with PivotChart and PivotTable.
Press F5 to verify that Visual Studio can build the workbook successfully and then test the solution by clicking on the Get Data button. Wait until Excel has finished importing data and then click OK in the Next Steps dialog box. Click Update All on the PowerPivot ribbon and then click Refresh All on the Data ribbon. Save the changes if desired and then close the Excel workbook.

That’s it. The VSTO-based PowerPivot workbook is now complete. What’s left to do is to create a Windows Installer package for deployment, but this is beyond the scope of this blog post. See Deploying a Visual Studio 2010 Tools for Office Solution Using Windows Installer for details.

About the Relativity of Large Data Volumes

KayUnkroth — Tue, 26 Jul 2011 19:58:00 GMT

Some conversations in our team are really priceless. The other day, while working on an article about Analyzing Performance Data in PowerPivot, I was bragging to Ashvini about the fact that I was able to import a 55 GB database with more than 200 million rows of data into a 1 GB PowerPivot workbook, as illustrated in the following figure, and then I found myself flabbergasted when Ashvini asked back, “Why is this workbook so large?” – Clearly, the concept of large data volumes must be relative to the observer. So, let’s see why this workbook is so large and what we can do to deal with this data volume.

First of all, to find out what consumes the most space in a PowerPivot workbook, I recommend reading Denny’s classic article “Understanding why an Excel PowerPivot workbook is so large” at http://powerpivottwins.com/2009/11/07/understanding-why-an-excel-powerpivot-workbook-is-so-large/. Following Denny’s suggestions, I opened my workbook, ran the command explorer %TEMP% to navigate to the Temp folder in Windows Explorer, and then within the Temp folder, I searched the most recent VertiPaq_<GUID> folder for all the temporary data files (*.*). The Analysis Services engine, running within the Excel client process, creates these temporary files when loading the PowerPivot database. As the following screenshot reveals, the largest temporary file with over 500 MB was called 1.13bb5f42-774c-4a5c-9281-e88a9fba0114.CounterDateTime.0.idf, which pointed me to the CounterDateTime column.

The reason for the CounterDateTime column to consume so much space can be tracked back to the data import query:

SELECT CONVERT(DATETIME, CAST(CounterDateTime AS VARCHAR(19))) AS CounterDateTime,

MachineName, ObjectName, CounterName, InstanceName, CounterValue

FROM PerfData.dbo.CounterData JOIN

PerfData.dbo.CounterDetails ON CounterData.CounterID = CounterDetails.CounterID JOIN

PerfData.dbo.DisplayToID ON CounterData.GUID = DisplayToID.GUID

The T-SQL expression CONVERT(DATETIME, CAST(CounterDateTime AS VARCHAR(19))) AS CounterDateTime takes 19 characters from the CounterDateTime column in the PerfData.dbo.CounterData source table and converts the resulting string into a DATETIME value (such as, 2011-04-02 14:13:49). The result is precise to a second and at this level of precision, my source column includes 1,168,290 unique values, as the DistinctCounterDateTime Values measure in the following screenshot attests.

By reducing the precision to minutes, the number of unique values drops by roughly a factor of 60 (1,168,290 / 60 = 19,471.5). One way to do this is to reduce the VARCHAR length to 16 characters (CAST(CounterDateTime AS VARCHAR(16)) in the import query. Another is to convert the string to a SMALLDATETIME instead of a DATETIME value. The choice depends on whether you want to truncate the seconds or round the result to the nearest minute. Either way, the unique count dropped in my example to 19,492 values, which provides for much better compressibility over more than 200 million rows than 1,168,290 unique values. In fact, the gain in compressibility helped me to bring the workbook size down to 575 MB, as you can see in the following screenshot. The tradeoff: I can no longer analyze the imported data at the level of seconds, but I didn’t drop any performance counter values, so I still can aggregate the data for each individual minute by using SUM, MIN, MAX, AVERAGE, etc. For my purposes, this is acceptable.

Reducing the workbook size is great. Among other things, smaller workbooks are easier to handle when uploading to SharePoint. However, it is important to note that the workbook size does not necessarily reflect the size of the PowerPivot database in memory. You can verify this by using Performance Monitor. Just track the Available Mbytes counter for the Memory object when opening the workbook. The following figure shows two Performance Monitor graphs on top of each other. The blue line corresponds to the optimized 575 MB workbook and the red is the original 1 GB workbook. As you can see, both workbook versions consume exactly the same amount of memory, approximately 2.5 GB.

Going back to the relativity of large data volumes, a PowerPivot workbook consuming 2.5 GB in memory might not be an issue for users running 64-bit Excel clients, but 32-bit clients see this very differently. Given that all PowerPivot data must fit into memory, 2.5 GB of PowerPivot data is simply too large for 32-bit clients. It’s a straightforward calculation: 2³² bits gives you an addressable space of 4 GB; the operating system reserves 2 GB for the kernel; Excel client, Analysis Services engine, any further add-ins plus the Excel data structures for the workbook itself (such as, cell data and pivot cache) consume some of the remaining 2 GB, leaving perhaps 1-1.5 GB of virtual memory to load PowerPivot data. So, a 32-bit client can absolutely not load a 2.5 GB database. The user gets an error message, as in the following screenshot. If your users regularly exceed the 32-bit memory limitation with their PowerPivot workbooks, I recommend deploying 64-bit Excel 2010 and PowerPivot throughout your organization. It will noticeably improve the user experience, avoid frustrated helpdesk calls, and increase the productivity of your information workers overall.

No doubt, 64-bit Excel 2010 with PowerPivot is great, but not all organizations can justify an upgrade to 64-bit clients in one big all-encompassing swoop. Don’t despair, PowerPivot for SharePoint 2010 can help. If the desktop client cannot load the PowerPivot workbook, publish the workbook in SharePoint and open it in a Web browser or link to it from within your 32-bit Excel client as if it was an Analysis Services database. The following screenshot shows you a 32-bit Excel client happily analyzing my 575 MB PowerPivot workbook with 216 million rows of data. It works because the client doesn’t load the embedded database. It’s loaded on a PowerPivot application server. If you are interested in an introduction to the PowerPivot for SharePoint architecture and the technical details around linking to published workbooks as a data source, check out Lee’s excellent 4-part video series titled “PowerPivot for SharePoint Architecture,” particularly “Part 4: Using PowerPivot Workbooks as a Data Source,” available on the TechNet Wiki at http://social.technet.microsoft.com/wiki/contents/articles/powerpivot-for-sharepoint-architecture.aspx.

Note: In order to prepare my large PowerPivot workbook for consumption as a data source, I explicitly added the measures CounterValueSUM, CounterValueMIN, CounterValueMAX, and CounterValueAVG to the workbook, similar to the DistinctCounterDateTime Values measure displayed in a screenshot earlier in this article. The CounterValueSUM, CounterValueMIN, CounterValueMAX, and CounterValueAVG measures are very straightforward based on the corresponding DAX functions SUM, MIN, MAX, and AVERAGE over the CounterValue column in my PerfData table.

You might have guessed it; SharePoint adds yet another dimension to the relativity of large data volumes. Large files with hundreds of megabytes inflate the size of SharePoint content databases and might require a re-evaluation of BLOB storage options and backup and recovery procedures. This isn’t necessarily a favorite pastime of SharePoint administrators. In order to keep content databases under control, it is not uncommon for large SharePoint farms to enforce upload limits far below 500 MB. So, uploading 575 MB or even larger workbooks might not always be possible.

If you find yourself without a suitable SharePoint environment to make your large workbooks accessible to 32-bit Excel users, you could consider deploying SQL Server code-named “Denali.” The Denali version of Analysis Services supports tabular models that are similar to PowerPivot models but with more capabilities. In fact, it is very easy to migrate a PowerPivot workbook to a tabular model. Just start Business Intelligence Development Studio (BIDS), create a new project, and select Import from PowerPivot as the project type, as the following screenshot illustrates. Tabular models are only limited by the physical amount of memory available on the SSAS server. So, now your users can access large data volumes by means of a genuine Analysis Services database and, all of a sudden, 1 GB of data doesn’t seem quite so big anymore at all. For more information about tabular models, read Cathy’s recent blog post “Welcome to Tabular Projects.”

On the other hand, if you don’t have Denali yet, you might have to go the extra mile to reduce the data in the PowerPivot database further. This isn’t as bad as it sounds. In fact, it is quite positive as smaller workbooks load faster, consume less space on disc and in memory, and are just easier to handle on 32-bit and 64-bit workstations. In my example, I decided to reduce the data volume by aggregating the data in the relational database based on the following T-SQL batch prior to importing the data into PowerPivot.

SELECT CONVERT(DATETIME, CAST(CounterDateTime AS VARCHAR(16))) AS CounterDateTime,

MachineName, ObjectName, CounterName, InstanceName, CounterValue

INTO PerfData.dbo.TruncatedCounterData

FROM PerfData.dbo.CounterData JOIN

PerfData.dbo.CounterDetails ON CounterData.CounterID = CounterDetails.CounterID JOIN

PerfData.dbo.DisplayToID ON CounterData.GUID = DisplayToID.GUID

SELECT CounterDateTime,

AVG(CounterValue) as CounterValueAVG,

MIN(CounterValue) as CounterValueMIN,

MAX(CounterValue) as CounterValueMAX,

SUM(CounterValue) as CounterValueSUM,

MachineName, ObjectName, CounterName, InstanceName

INTO PerfData.dbo.AggregatedCounterData

FROM PerfData.dbo.TruncatedCounterData

GROUP BY CounterDateTime, MachineName, ObjectName, CounterName, InstanceName

The first SELECT statement converts the CounterDateTime values into the desired DATETIME format and writes the results into a TruncatedCounterData table. The second SELECT statement then aggregates the data and puts the results into an AggregatedCounterData table. PowerPivot then imports the data directly from the AggregatedCounterData table. My PowerPivot database now only contains 3,625,067 rows and consumes 537 MB in memory, as the following screenshot reveals. This is a size both 32-bit and 64-bit clients can handle. And the workbook size dropped to 94.2 MB, which makes it possible to share this workbook even in a SharePoint farm with very restrictive upload limits. Moreover, because PowerPivot has significantly fewer rows to scan, the solution is faster and more convenient to use.

In conclusion, putting a 55 GB database into a 1 GB PowerPivot workbook might be an impressive achievement, but it’s still a good idea to look at the result with a critical eye and keep asking the question “Why is this workbook so large?” Perhaps a tabular model is more suitable for the intended purpose than a PowerPivot workbook, or perhaps there are more opportunities to reduce the data volume. With the right preparations in the data source, a 55 GB database might very well fit into a 100 MB PowerPivot workbook without sacrificing analyzability of the data. Smaller workbooks load faster, consume less space on disc and in memory on 32-bit and 64-bit workstations, and are easier to share in SharePoint. I hope you enjoyed this little excursion into the relativity of large data volumes.

The Diagram is here…

Julie Strauss [MSFT] — Wed, 20 Jul 2011 22:24:10 GMT

Probably one of the most frequently requested features we have had for PowerPivot V1 has been the request for a Diagram. As a result we are super excited that the Diagram View found its way through with CTP3

The Diagram View is - as the name suggests - basically a diagrammatic view of the model. This means that in CTP3 you will have two views available when working with your models:

· The Data View, representing the data grid you may know from PowerPivot V1 where you work directly with the data that resides in your tables

· The new Diagram View, representing the metadata view of your model for viewing and defining your model at an object level.

The Diagram View is available for both the Tabular project types in BIDS as well as for PowerPivot. The entry points for the two views can be found in the lower right hand corner of the modeling window as show below.

We aimed at having feature parity between the two views for the most common operations such as hide, rename and delete in order to prevent frequent switching between the two views. However, given the fact that one view represents data and other metadata there are obviously some tasks that do lent themselves better to be performed in one view versus the other. So there are some key differences in terms of modeling capabilities that are worth pointing out.

Features Specific to Data View

All data enriching operations using DAX such as adding calculated columns and defining measures are available in the Data View only. The same counts for creation of KPIs, which are directly tied to existing measures.

In future versions this may change, but for Denali we made the choice to only add these data driven operations to the Data View where the direct access to the data makes it very easy to verify the operations as they are performed. Other options where interactivity with the data is an advantage, such as sort and filter are also available in the Data View only.

Features specific to Diagram View

Creation of user defined hierarchies is a feature that you will find in the Diagram View only. It’s a metadata driven operation, which simply made more sense in the Diagram, where each table is used as modeling surface for creating and editing the hierarchies.

Interacting with the Diagram

The Diagram is more than just a view that provides a great overview of the tables and relationship, though. It’s a fully-fledged designer, which allows for greater interactivity and navigation across the entire model.

So despite the fact that most operations (the exceptions outlined above) can be performed in either view it’s easy to imagine that many BI professionals will spend the majority of the time modeling from within the Diagram. Let’s take a closer look at the Diagram:

Creating Relationships

You will often see the Diagram referenced as the Entity Relationship (ER) Diagram. So yes, you can obviously create relationships from the Diagram View. You can either use drag and drop of columns or use the table or column context menu to launch the Create Relationship dialog box.

When you mouse over an existing relationship the primary and foreign key tables and columns will light up for greater visibility. See above. This is particular useful when your model is zoomed out and a large number of tables makes the relationship lines more challenging to follow.

To edit a relationship you simply double click the relationship line on the Diagram surface.

Maximizing tables

You have the ability to maximize each table. The idea here is to provide a better modeling surface as you work with each table. When in maximized state only the particular tables active and the rest of the diagram is disabled

Creating Hierarchies

As mentioned earlier the Diagram View is where you will be creating user defined hierarchies. Hierarchies can be created either by using the column context menu or by using the Create Hierarchy button in the table header.

Using Display Filters

The Display Filters are located at the tool bar at the top of the Diagram.

They can be used for filtering out the different object types from the tables in the Diagram allowing you to narrow down your view to only display particular objects. The Display Filters are in particular use when working with larger models helping you better navigate your model.

Leveraging Perspectives

Another way to narrow down the view of your model is to use the Perspective selection. Perspectives are designed to enable you to define subsets of the model that represents only most relevant parts of a model to end users as they consume the model via client tools. However, once the perspectives are created you, as a model designer can also leverage the perspectives to narrow down the number of objects in the Diagram providing a simplified view of the model at design time.

Navigating using the Go to option

The Go To option is located in the context menu of every object that exists in both the Diagram and the Data view.

The Go To option will take you to the same object in the Data View allowing you an easy navigation path to exploring the data behind each object.

Obviously there are a lot more features available in the Diagram overall and as mentioned you will find (almost) all the features that exists in the Data View in the Diagram as well. But hopefully this post gives you a taste of what the Diagram will provide!

Enjoy your modeling using CTP3!

Released: Denali CTP3 Samples and TechNet Wiki

KayUnkroth — Fri, 15 Jul 2011 23:06:02 GMT

This was a big announcement week for us. Let’s finish it with a note about our new Denali CTP3 samples. JuanPablo already introduced the AMO sample in his Creating a Tabular Model with AMO blog post, but wait there’s more:

SQL Server Samples Readme on the TechNet Wiki Ties all samples together and provides step-by-step installation instructions and links to related resources. The TechNet Wiki is our main portal for Denali CTP 3 samples. Bookmark it for the latest information.
Image Files for HelloWorldPicnic Tabular Model Sample Contains more than 50 image files, especially for tabular models in support of Project Crescent reports.
Getting Started with Project “Crescent” and PowerPivot for Excel in SQL Server Code-Named “Denali” CTP3 Includes PowerPoint slide shows about Getting Started With Crescent and Tabular Model Reporting Properties and a HelloWorldPicnic PowerPivot workbook and Project Crescent report.
Adventure Works for SQL Server Denali CTP3 on CodePlex Five downloads:
- AdventureWorks2008R2 – CTP3 version of the OLTP database (no schema or data changes)
- AdventureWorksDWDenali —CTP3 version of the data warehouse (new fact table with product inventory data and date data added to support Analysis Services scenarios)
- SSAS Multidimensional Model Projects—new Adventure Works tabular model and updated multidimensional projects for the Analysis Services tutorial (customers like this a lot)
- SSAS Tabular Model Projects—tabular models, PowerPivot workbook, and related Project Crescent reports
- SSAS AMO2Tabular—programming sample that shows how to create a tabular model using AMO

Many thanks to our UE team for compiling these samples. Take a look at the samples and let us know what you think by submitting your feedback through the SQL Server Connect page.

Adding Values to Rows and Columns

Julie Strauss [MSFT] — Fri, 15 Jul 2011 08:17:20 GMT

The release notes for CTP3 references the ability to add values to rows and columns. But what does this really mean? Inspired by quite a few questions on this already I thought I’ll try to provide a little more details on this.

In the RTM version of PowerPivot you can add multiple measures to the values area of a PivotTable, but you are not able to add measures to rows and columns. Let me add some examples to illustrate.

Behavior in PowerPivot RTM

If you add multiple measures, let’s say Sales Amount and Budget Amount - to the values area of the Field List the two measures will show in the values area of the PivotTable as shown below.

Now if you add another field, let’s say Year to the Column Labels the two measures you added previously will be nested within the newly added column field.

In my example, the Sales Amount and Budget Amount will be nested under the columns of Year 2003 and Year 2004. The measures will always show up nested under columns. This measure location cannot be changed meaning that you will not be able to reverse the order and show Year nested under Sales Amount and Budget amount. Nor will you be able to show Sales Amount and Budget Amount on rows. This is the behavior in the RTM version of PowerPivot.

New behavior in CTP3 of Denali

The new feature listed in the release notes for CTP3 as the ability to add values to rows and columns will allow you to accommodate these limitations. Going back to the example, in addition to nesting Sales and Budget within Years on columns I will now be able to show my measures in three other locations as follows:

I can show Year nested within the Sales and the Budget Amount on columns:

I can add measures to rows nesting Sales and Budget Amount within Product:

I can add measures to rows nesting products within Sales and Budget Amount:

In summary using CTP3 you will be able to show measures on rows in addition to columns and to show fields nested within measures on both rows and columns. An increased flexibility, which will greatly enhance the PivotTable layout

Hope this helps!

Creating a Tabular Model with AMO - New Version SQL 2012 RC0

JuanPablo Jofre — Fri, 15 Jul 2011 06:28:00 GMT

With the release of SQL Server 2012 RC0 we have included a complete set of database samples, to help you get started, and a sample to programmatically create a Tabular model using the AMO API, in C#.

The purpose of the AMO sample is to help developers to understand how to map AMO objects to the logical objects in a Tabular model. For example, a logical table in tabular modeling is represented by a dimension, a cube, a cube dimension, a measure group and a partition in the measure group (actually, it can be one or more partitions) in AMO; in this case you can see that a single logical object, a table, is mapped to many AMO objects. When trying and working with the sample, it is of capital importance that you realize that the sample is the code, not in the execution of the code; executing the code is a visual aid to understand the process of creating a tabular model.

The sample covers the process of creating a database, a data source, selecting up to two tables in the relational data source with the desired columns to include in the model and creating the tabular tables; up to here you will have the bare bones of a tabular model.

From this point forward you have the option to choose what you want to do first:

Add calculated columns
Add calculated measures
Add hierachies
Add KPIs (Key Performance Indicators)
Add relationships (requires two tables in the model)
Add Perspectives
Add and manage Partitions.

In order create the tabular objects, above mentioned, the sample will take you to create several AMO objects that are hidden to user in Management Studio or BIDS.

When you compile and run the sample, it runs as an interactive form that takes you through the steps of creating a tabular model; the sample, itself, is meant to be run once or twice to understand the logical steps involved in creating a model. Later the focus should turn to understand the source code and how AMO objects are used to generate the logical tabular objects; in this context, the sample should be run with the source code at hand to understand what are the different steps and events involved in making tabular objects; another alternative, to having the code at hand, is to run the code in Visual Studio 2010 in debug mode. Also, an important note, when reading the source code, each tab in the form has its source code in a separate file, with a matching name to easily identify where to read.

You can find the sample at Adventure Works for SQL Server 2012 RC0. Click on the SSAS AMO2Tabular SQL Server 2012 RC0 link to download the sample. The readmes, for all samples in this page, can be found at this other page SQL Server Samples Readme or inside the download files.

Prerequisites, to compile and run the sample:

Visual Studio 2010 with C#
An instance of SQL Server 2012 RC0 Analysis Services deployed in tabular mode.
The sample needs any MS SQL Server relational database as data source; if you don’t have any database at hand, you can download the AdventureWorksDWDenali data file to use as a data source (the download link is also on the same page).

Released: Microsoft SQL Server 2008 R2 Service Pack 1

KayUnkroth — Thu, 14 Jul 2011 03:37:00 GMT

This week is packed with big announcements: We released Microsoft SQL Server Code Name “Denali” Community Technology Preview 3 (CTP3) with breakthrough innovations that will help our customers to shape their Business Intelligence (BI) future; and we also released Microsoft SQL Server 2008 R2 Service Pack 1 with important improvements of our existing technologies, and PowerPivot for Microsoft Excel 2010 was also updated as a separate download.

Overall, SQL Server 2008 R2 Service Pack 1 consolidates more than 150 quick fixes and improvements. For example, under some circumstances, MDX queries might consume all the physical memory on a computer running Analysis Services 2008 R2 and eventually fail with an “out of memory” error. So, we investigated the circumstances, optimized the affected algorithms, and solved the issue in SP1. In other cases, we simply rephrased error messages to provide better guidance. Particularly, some of our PowerPivot for SharePoint health rule error messages were confusing and misleading. This was also a great opportunity to fix a number of localization issues.

We didn’t add new functionality to PowerPivot for Microsoft Excel 2010, but PowerPivot for SharePoint introduces two new configuration settings that let you determine how long cached data stays in the system. In the new Disk Cache section on the PowerPivot configuration page, you can specify how long an inactive database remains in memory before it is unloaded. You can also limit how long a cached file is kept on disk before it is deleted. Also noteworthy are the performance and scalability improvements that you can gain by using SQL Server 2008 R2 Service Pack 1 together with Microsoft SharePoint Server 2010 Service Pack 1. In fact, some optimizations in SharePoint 2010 SP1 might require you to update PowerPivot in your farm to SP1; especially if you notice that the PowerPivot Management Dashboard is missing workbook activity data or that the workbook snapshot generation in the PowerPivot Gallery stops working after an upgrade to SharePoint 2010 SP1.

For a detailed list of new features and improvements that are included in SQL Server 2008 R2 SP1, review the What's New Section in Release Notes.

Welcome to Tabular Projects

Cathy Dumas - MSFT — Wed, 13 Jul 2011 18:51:00 GMT

With SQL Server “Denali” CTP3, there are two types of Analysis Services projects in Business Intelligence Development Studio (BIDS). You can create and edit the multidimensional projects that you all know and love. You can also use BIDS to create tabular projects. This post will focus on getting you up and running in tabular development.

Setup

Here is what you need to install to get up and running with the tabular project tools:

Install BIDS (now in the Visual Studio 2010 shell).
Install an Analysis Services instance running in tabular mode. BIDS will connect to this server while you are modeling, reading from and writing to temporary databases. You must be an administrator on this server (called the workspace database server). It is best if this instance is on the same machine as BIDS.

You should probably have both SSMS and a version of Office on the machine with BIDS and the workspace database server. This allows you to browse and administer your models while you are working. However, if this machine configuration doesn’t work for you, you can always browse or administer your tabular databases remotely. Just open port 2383 on the firewall on the machine hosting the workspace database instance and you’re good to go.

See the release notes for some more details about setup.

Finding your way around the designer

The designer is pretty straightforward. There are three menus (Model, Table and Column) that contain most commands used during tabular modeling. There is also a toolbar that provides quick access to some frequently used commands. Inside the designer, there are two views - the grid view and the diagram view.

Here’s an annotated picture of the grid view, showing the menus and toolbars. You’ll spend a lot of time in the grid view, creating calculated columns in the grid and writing measures and KPIs in the measure grid.

The other view is the diagram view, which you will use for creating relationships and hierarchies.

You can create perspectives, partitions and roles from either view. You can perform most metadata operations, such as changing data types, applying reporting properties, marking a date table, and so on from either view. Keep your property grid open, you’ll use it pretty frequently to make metadata changes.

Your first model

The documentation team put together a pretty thorough tutorial that will help you get up and running. Here’s a few things not covered in the tutorial:

· You can get started in the tabular designer by importing an existing PowerPivot workbook. In the File->New Project dialog, choose the “Import from PowerPivot” template under the Analysis Services node.

· To build DirectQuery models, you must enable DirectQuery mode in the designer first.

· You’ll need to get familiar with DAX in a hurry. There is a DAX overview on MSDN that provides basic conceptual information and function reference. For more in-depth information, you can consult the DAX resources we have for PowerPivot users. Two nice resources are the DAX whitepaper and the TechEd talk on DAX. For people familiar with DAX that are wondering what’s new for Denali, see the PowerPivot release notes for a list of new functions.

Have fun with the bits. We look forward to hearing your comments, suggestions, and bug reports. Kay’s post yesterday explains how to give feedback, file early and often. Cheers

Submit Your Feedback on Analysis Services and PowerPivot

KayUnkroth — Tue, 12 Jul 2011 23:58:42 GMT

Now that the Microsoft SQL Server Code Name “Denali” Evaluation edition is available for download, we are eager to hear from you. Do you like the new Business Intelligence (BI) features, such as the new professional BI developer tools? Do you find it easy to create tabular models on an Analysis Services server? What additional features would you like to suggest? Did you discover any of those “undocumented features” generally known as software bugs? Please take the time to let us know:

Go to the SQL Server Connect page at http://connect.microsoft.com/SQLServer/Feedback.
Add new feedback or comment on existing issues.

As a side note, according to Wikipedia, Thomas Edison used the term Bug in a letter as far back as 1878: “'Bugs' — as such little faults and difficulties are called—show themselves and months of intense watching, study and labor are requisite before commercial success or failure is certainly reached.” And this is what our Community Technology Preview is all about: Intense watching, study and labor to deliver the next version of SQL Server in the highest quality. Your help in this effort is very much appreciated!

Announcing Microsoft SQL Server Code Name “Denali” Community Technology Preview 3 (CTP3)

KayUnkroth — Tue, 12 Jul 2011 15:54:00 GMT

Today we released our next public CTP of Microsoft SQL Server Code Name “Denali.” This milestone is super exciting because it is packed with amazing Business Intelligence (BI) improvements and innovations for relational databases as well as for BI solutions. You can find the Denali CTP Evaluation edition at the Microsoft.com Download Center. Try it for yourself.

SQL Server “Denali” builds on what we started with Microsoft SQL Server 2008 R2. On the relational database side, Project "Apollo" leverages the VertiPaq technology we first introduced in SQL Server 2008R2 for blazing-fast query performance. On the BI side, Denali complements self-service analysis with self-service reporting through PowerPivot and Project "Crescent", as well as self-service alerting with SSRS in SharePoint integrated mode. It provides the foundation to the cloud-ready information platform and will help customers unlock breakthrough insights across the organization and quickly build solutions that extend data across on-premises and public/private clouds backed by exciting new capabilities for greater mission critical confidence. Moreover, it brings our traditional and new data models together in an all-encompassing Business Intelligence Semantic Model (BISM), which is a significant step forward on our Vision for Analysis Services (see Analysis Services – Vision & Roadmap Update). It gives our customers a flexible platform that can meet the performance and scalability demands of some of the world’s largest BI implementations. It delivers advanced capabilities based on sophisticated business logic. And, it comes with a broad choice of professional developer and business-user tools that are easy to use.

There are many new features that you can put to use. PowerPivot “Denali” supports hierarchies, multiple relationships between tables, a measure grid to easily create, edit, and manage measures and Key Performance Indicators (KPIs), new DAX functions (Time series, distinct count to name a few), and importing of binary large objects (BLOB). All of these features—and many more—are also available on the server in tabular Analysis Services databases, including row-level security, partitions and DirectQuery mode, enabling you to access the information directly from the source system. Substantial improvements can also be found in our SharePoint components. Among other things, we separated setup and configuration tasks, so it’s easy to install and uninstall, configure and re-apply configuration settings.

Here are some important installation prerequisites:

SQL Server “Denali” PowerPivot for Excel Install Visual Studio Tools for Office (VSTO) 4.0 runtime and .NET Framework 4.0 before you install PowerPivot. You can find the Denali version of PowerPivot for Excel in the Microsoft.com Download Center at http://www.microsoft.com/download/en/details.aspx?id=26721 and the VSTO runtime at http://www.microsoft.com/downloads/en/details.aspx?familyid=06C32242-2289-4471-93AA-CE96AA5CBC36&displaylang=en.
SQL Server “Denali” PowerPivot for SharePoint Install Microsoft SharePoint Server 2010 Service Pack 1 (SP1) available at the Microsoft.com Download Center at http://www.microsoft.com/download/en/details.aspx?id=26623. You must fully patch the SharePoint farm before you add SQL Server “Denali” features. No separate PowerPivot download required because PowerPivot for SharePoint is directly included in SQL Server Code Name “Denali.”
SQL Server “Denali” Analysis Services Install .NET Framework 3.5 Service Pack 1 as well as .NET Framework 4.0 and Windows PowerShell 2.0. You also need an update for the .NET 3.5 SP1:
- For Windows Vista SP2 or Widows Server 2008 SP2, go to http://go.microsoft.com/fwlink/?LinkId=198093.
- For Windows 7 or Windows 2008 R2, go to http://go.microsoft.com/fwlink/?LinkId=198092.

For additional information and known issues, check out the Release Notes for Microsoft PowerPivot Code Name "Denali" CTP3 and Microsoft SQL Server Code Name "Denali" CTP3. The following links might also be helpful:

Microsoft SQL Server: The Data and BI Platform for Today and Tomorrow Quentin Clark on vision, strategy and roadmap for SQL Server products and technologies.
What's New in Microsoft SQL Server Code-Named "Denali" for SQL Server Analysis Services and PowerPivot T.K. and Ashvini bringing you up to speed on our breakthrough BI innovations and technologies in Analysis Services and PowerPivot for the "Denali" release.
Abundantly "Crescent": Demos Galore Sean Boon, Carolyn Chau, and Sabrena McBride demonstrating what's possible with our new self-service reporting technology.
Deploying and Managing Microsoft SQL Server PowerPivot for SharePoint Dave and Dana giving you a head start with your PowerPivot “Denali” deployment in SharePoint 2010 SP1.
Enriching Your BI Semantic Models Using Data Analysis Expressions (DAX) Ashvini and Kasper presenting the new DAX features and capabilities coming in "Denali" and discussing key concepts in DAX, how to add calculated columns and measure to your model, and so forth.

And, of course, stay tuned for more info on Denali features on this blog as well as on the Reporting Services team blog at http://blogs.msdn.com/b/sqlrsteamblog/.

Creating a Stock Ticker View for Performance Data in PowerPivot

KayUnkroth — Tue, 05 Jul 2011 17:51:00 GMT

My previous blog post “Analyzing Performance Data in PowerPivot” explained how to create a basic PowerPivot solution to display performance counters for individual servers or server roles in a PivotChart. Subsequently, Jesse Harris, Premier Field Engineer for Microsoft System Center Operations Manager, suggested expanding this solution by providing a view similar to a stock ticker for performance information. Great idea, Jesse! Here is a screenshot of the final solution.

The stock ticker view has three main areas: A header with summary data, the PivotChart, and a section with links to additional information. The PivotChart already exists, which leaves summary header and links to additional information to be implemented. Let’s tackle the summary header first.

The summary header is easily generated by using measures based on straightforward Data Analysis Expressions (DAX). The only exception is the standard deviation (STD) field, which is added separately in a later step. For now, just focus on the simple measures:

Start by moving the PivotChart 4 or 5 rows down from its current position on the Excel worksheet to make room for the summary header.
Select the cell that previously marked the upper left corner of the PivotChart, such as cell D9, and then on the PowerPivot ribbon, click on the PivotTable button.
In the Create PivotTable dialog box, select Existing Worksheet, and then click OK.
With the newly created PivotTable selected, on the PowerPivot ribbon, click on the New Measure button.
In the Measure Settings dialog box, define the necessary settings according to the following table, and then click OK.

Measure Name	Formula
StartTime	=MIN(PerformanceData[CounterDateTime])
EndTime	=MAX(PerformanceData[CounterDateTime])
SelectedRangeLow	=ROUND(MIN(PerformanceData[CounterValue]), 2)
SelectedRangeAverage	=ROUND(AVERAGE(PerformanceData[CounterValue]), 2)
SelectedRangeHigh	=ROUND(MAX(PerformanceData[CounterValue]), 2)
SelectedRangeVolume	=COUNTROWS(PerformanceData)
PctOfTotalVolume	=COUNTROWS(PerformanceData) / COUNTROWS(ALL(PerformanceData))

Note that PowerPivot automatically adds the newly created measures as new columns to the selected PivotTable, but the values are most likely incorrect because the PivotTable is not connected to the slicers yet. For example, SelectedRangeVolume does not take the current slicer selection into account and always returns the total number of rows in the PerformanceData table and PctOfTotalVolume is always 1 (see the following screenshot).

To apply the slicers to the PivotTable, follow these steps:

With the PivotTable selected, display the Options ribbon in Excel.
Under Sort & Filter, click on Insert Slicer, and then select Slicer Connections.
In the Slicer Connections (PivotTable1) dialog box, select the checkbox of every listed slicer, and then click OK. Verify that the PivotTable now correctly applies the filter context to the DAX formulas.

Tip: If you like to learn more about measures and filter context, refer to Howie Dickerman’s excellent blog article “DAX (Data Analysis Expressions) Measures in PowerPivot” at http://blogs.msdn.com/b/analysisservices/archive/2010/04/05/dax-data-analysis-expressions-measures-in-powerpivot.aspx.

Now what about the standard deviation? The formula to calculate this value would be SQRT( (SUM(PerformanceData[CounterValue]^2) / COUNTROWS(PerformanceData) ) - ((SUM(PerformanceData[CounterValue]) / COUNTROWS(PerformanceData))^2) ), but this doesn’t work because the SUM function only accepts a column reference as an argument, yet PerformanceData[CounterValue]^2 isn’t a column (see the following screenshot).

Hence, in order to implement the standard deviation field, you must create a calculated column for PerformanceData[CounterValue]^2:

In Excel, switch to the PowerPivot ribbon, and then click on PowerPivot Window.
In the PowerPivot window, make sure the PerformanceData table is displayed.
Under Add Column, select an empty cell, and then in the formula text box indicated by an fx symbol, type =PerformanceData[CounterValue]^2 and press Enter.
Verify that PowerPivot calculates the values for the new column successfully; then right-click the column header named CalculatedColumn1 and select Rename Column.
Type CounterValue^2 as the new column name and press Enter.
Close the PowerPivot window and save your changes.

At this point, you can repeat the procedure listed earlier in this blog post to create a measure for the standard deviation. Use the following information as a reference.

Measure Name	Formula
SelectedRangeSTD	=SQRT( (SUM(PerformanceData[CounterValue^2]) / COUNTROWS(PerformanceData) ) - ((SUM(PerformanceData[CounterValue]) / COUNTROWS(PerformanceData))^2) )

The measures for the summary header are now complete, yet the PivotTable does not really convey the impression of a stock ticker. In order to rearrange the cells, you must convert the PivotTable into cells with individual formulas:

In the Excel worksheet, select the PivotTable.
Switch to the Options ribbon, and then under OLAP Tools, select Convert to Formulas. Note that the PivotTable changes to a collection of unformatted cells.
Select the first header cell, such as StartTime. Note that the cell uses the CUBEMEMBER function to lookup the cell value in the PowerPivot model, such as =CUBEMEMBER("PowerPivot Data","[Measures].[StartTime]"). Copy the formula without the leading equal sign to the clipboard. Press Escape to exit formula editing mode.
Select the first value cell, such as the value for StartTime. Note that the cell uses the CUBEVALUE function to lookup the value in the PowerPivot model, such as =CUBEVALUE("PowerPivot Data",D$9,Slicer_ObjectName,Slicer_InstanceName,Slicer_CounterName,Slicer_Machine_Name,Slicer_Server_Role). Replace the cell reference in the formula, such as D$9, with the CUBEMEMBER formula you copied to the clipboard in the previous step in order to eliminate the formula’s dependency on another cell’s content.
Right-click the value cell, such as the value for StartTime, and select Format Cells. Select an appropriate format, such as Date, 3/14/01 1:30 PM.
Repeat the steps for the remaining fields. Use the following table as a reference.

Measure Name	Cell Formula	Cell Format
StartTime	=CUBEVALUE("PowerPivot Data", CUBEMEMBER("PowerPivot Data","[Measures].[StartTime]"), Slicer_ObjectName, Slicer_InstanceName, Slicer_CounterName, Slicer_Machine_Name, Slicer_Server_Role)	Date 3/14/01 1:30 PM
EndTime	=CUBEVALUE("PowerPivot Data", CUBEMEMBER("PowerPivot Data","[Measures].[EndTime]"), Slicer_ObjectName, Slicer_InstanceName, Slicer_CounterName, Slicer_Machine_Name, Slicer_Server_Role)	Date 3/14/01 1:30 PM
SelectedRangeLow	=TEXT(CUBEVALUE("PowerPivot Data", CUBEMEMBER("PowerPivot Data", "[Measures].[SelectedRangeLow]"), Slicer_ObjectName, Slicer_InstanceName, Slicer_CounterName, Slicer_Machine_Name, Slicer_Server_Role), "#,##0.#0") & " (Low)"	General
SelectedRangeAverage	=TEXT(CUBEVALUE("PowerPivot Data", CUBEMEMBER("PowerPivot Data", "[Measures].[SelectedRangeAverage]"), Slicer_ObjectName, Slicer_InstanceName, Slicer_CounterName, Slicer_Machine_Name, Slicer_Server_Role), "#,##0.#0") & " (Average)"	General
SelectedRangeHigh	=TEXT(CUBEVALUE("PowerPivot Data", CUBEMEMBER("PowerPivot Data", "[Measures].[SelectedRangeHigh]"), Slicer_ObjectName, Slicer_InstanceName, Slicer_CounterName, Slicer_Machine_Name, Slicer_Server_Role), "#,###.#0") & " (High)"	General
SelectedRangeSTD	=TEXT(CUBEVALUE("PowerPivot Data", CUBEMEMBER("PowerPivot Data", "[Measures].[SelectedRangeSTD]"), Slicer_ObjectName, Slicer_InstanceName, Slicer_CounterName, Slicer_Machine_Name, Slicer_Server_Role), "#,###.#0") & " (STD)"	General
SelectedRangeVolume	=TEXT(CUBEVALUE("PowerPivot Data", CUBEMEMBER("PowerPivot Data", "[Measures].[SelectedRangeVolume]"), Slicer_ObjectName, Slicer_InstanceName, Slicer_CounterName, Slicer_Machine_Name, Slicer_Server_Role), "#,###") & " (Samples Selected}"	General
PctOfTotalVolume	=TEXT(CUBEVALUE("PowerPivot Data", CUBEMEMBER("PowerPivot Data", "[Measures].[PctOfTotalVolume]"), Slicer_ObjectName, Slicer_InstanceName, Slicer_CounterName, Slicer_Machine_Name, Slicer_Server_Role), "0#.####%") & " (of Total Samples)"	General

Rearrange header and value cells to resemble the header of a stock ticker (see the first screenshot in this blog post) and save your work. Apply formatting, such as boldface and gray cell background, according to your personal preferences.

This concludes the summary header work. Let’s now turn to the links for additional information. These links are an interesting tweak because PowerPivot provides no support for a hyperlink data type out of the box. However, you can construct hyperlinks dynamically by using a hidden PivotTable as an input source for the HYPERLINK function that is available in Excel. Howie Dickerman showed me this tweak. Here are the steps:

Switch to Sheet 2, which might already contain the table for machine names and server roles.
Select a cell in an empty area, such as cell D1 to the right of the table for the machine names and server roles.
On the PowerPivot ribbon, click on the PivotTable button.
In the Create PivotTable dialog box, select Existing Worksheet, and then click OK.
With the newly created PivotTable selected, in the PowerPivot Field List, expand ServerRoles, and drag the Machine Name node to the Row Labels box. Note that the PivotTable lists all machine names because it isn’t connected to the slicers yet.
Display the Options ribbon in Excel, and then under Sort & Filter, click on Insert Slicer, and then select Slicer Connections.
In the Slicer Connections (PivotTable2) dialog box, select the checkboxes for the Machine Name and Server Role slicers, and then click OK. Verify that the list of machine names now reflects the selections in the connected slicers.
On the Options ribbon, under PivotTable Name, click on Options, and then select Options.
In the PivotTable Options dialog box, on the Layout & Format tab, deselect Autofit column widths on update.
Switch to the Totals & Filters tab and deselect the checkboxes Show grand totals for rows and Show grand totals for columns. Click OK.
Switch to Sheet1, which contains the slicers, summary header, and PivotChart.
Select an empty cell underneath the PivotChart, such as cell D30.
Type Additional Information: and apply boldface text formatting.
Select an empty cell underneath the new Additional Information header, such as E31, and enter the following formula:
=IF(ISBLANK(Sheet2!D2), "", HYPERLINK("http://intranet.site?server=" & MID(Sheet2!D2, 3, LEN(Sheet2!D2)-2), "Real-Time Report for " & MID(Sheet2!D2, 3, LEN(Sheet2!D2)-2)))
Replace the cell reference Sheet2!D2 with the actual cell that contains the first machine name and change the URL part, that is http://intranet.site?server=" & MID(Sheet2!D2, 3, LEN(Sheet2!D2)-2), as appropriate for your environment. Press Enter.
Select the cell with the hyperlink formula as well as the cells to the right for the full width of the PivotChart, such as E31:J31, right-click the selection, and select Format Cells.
Switch to the Alignment tab and select the checkbox Merge cells. Click OK.
and then drag the outside selection down several rows to extend the series. Fill as many rows as you have servers in the data set. For example, my workbook includes performance data from 30 servers, so I extended the selection down 30 rows. This ensures that every machine name is going to be associated with a hyperlink even if the list of servers in the PivotTable is not filtered by using the Server Role or Machine Name slicers.
Verify that the hyperlinks show the correct information and save your work.

The stock ticker view for performance data is now complete. Feel free to add further elements. For example, you could add event log data to the PowerPivot workbook and display relevant information in yet another PivotTable according to the user’s selection in the Server Role and Machine Name slicers. Just make sure you establish appropriate slicer connections as outlined above. In my next blog post, I’m planning to cover PowerPivot storage efficiency. At roughly 250 million rows of performance data, my workbooks tend to be about 800 MB, which might be too large for 32 bit Excel clients. Perhaps something can be done to reduce the workbook size without cutting back on performance analysis. Stay tuned.

Released: Service Pack 1 for Microsoft SharePoint Server 2010

KayUnkroth — Tue, 28 Jun 2011 19:43:41 GMT

Today is a great day for us PowerPivoteers because Service Pack 1 for Microsoft SharePoint Server 2010 has been released. You can download it from our new and fantastic Microsoft.com Download Center at http://www.microsoft.com/download/en/details.aspx?id=26623.

Why is this SharePoint Service Pack so important for PowerPivot? Among other things, it lowers the number of query requests Excel Calculation Services (ECS) generates in response to user actions in the browser. Over the course of the last 12 months, we worked hard with the Excel Services team to achieve this and it’s great to have it because it improves the scalability of PowerPivot for SharePoint overall.

The following diagram illustrates the communication behavior in somewhat simplified form. The important point is that when a user interacts with a PowerPivot workbook in the browser, the browser sends requests to Excel Web Access (EWA), which forwards the requests to ECS, which queries the workbook’s embedded PowerPivot database loaded on a PowerPivot application server. Depending on the workbook complexity, ECS might generate multiple query requests, especially when the workbook includes multiple slicers that are related to each other. It’s not unusual for ECS to send 10 or 20 requests to the PowerPivot application server in response to a slicer action in order to determine the state of each individual slicer corresponding to the user’s current selection.

In SharePoint 2010 Service Pack 1, our Excel Services colleagues were able to cut the number of ECS query requests nearly in half, which means half the load on PowerPivot application servers per user action and therefore almost double the PowerPivot scalability. Our tests showed a scalability increase of roughly 80% in comparison to SharePoint 2010 RTM. It varies to some degree depending on workbook complexity, but the improvement is definitely significant enough to update every PowerPivot-enabled farm—after proper testing and signoff in your organization, of course. Needless to say that performance and scalability improvement is a continuous effort in our team. We’ll keep pushing the envelope in SQL Server codenamed “Denali” and beyond… the sky is the limit!

PowerPivot at the Microsoft.com Download Center

KayUnkroth — Mon, 27 Jun 2011 19:50:47 GMT

The Download Center team just released a new version of the Microsoft.com Download Center (DLC). It includes a new and improved search engine, an updated user experience, and more paid and free products within its catalog. Sounds great… Let’s see how good the new DLC really is when it comes to PowerPivot.

Go to http://www.microsoft.com/download/en/. This is the new English version. International versions will come later.
Notice the nice and clean site design. Awesome job, given the amount of content available here… If you have the time, feel
free to navigate through all the products and downloads, but for now let’s just
type PowerPivot into the search box and press Enter.
Undeniably, super-fast query results! And it’s all there… PowerPivot add-in, samples, even our client/server architecture
poster, the Analytics for Twitter solution, the Calendar Analytics Tool, and lots of other PowerPivot-related content that you might find interesting.

Now you only need to add the search link (http://www.microsoft.com/download/en/search.aspx?q=PowerPivot&p=1&r=50&t=20) to your favorites for convenient access in the future. Enjoy!

And as a side note, perhaps one fine day the Download Center team is going to give us a data feed to import download statistics straight into PowerPivot for further analysis. Considering that the DLC gets close to 1 billion visits a year, approximately 700 million downloads, this is certainly within the range of PowerPivot capabilities... analyzing Microsoft downloads on your desktop, wouldn’t that be something!

Analyzing Performance Data in PowerPivot

KayUnkroth — Tue, 14 Jun 2011 17:58:00 GMT

PowerPivot is an absolutely fantastic performance analysis tool. You might say that I’m biased, but wait until you see firsthand how easy it is to analyze large amounts of performance data from any number of computers. For example, I recently had to analyze resource utilization in a SharePoint farm with 16 front-end servers, 8 Excel Services application servers, 4 PowerPivot application servers, and 2 content database servers. Each load test sampled a variety of performance counters from all of these servers in one-second intervals and generated over 250 million rows of data, and I needed to analyze this data for individual servers as well as for server roles. Sounds like a lot of work? Piece of cake if you use PowerPivot! Here are the steps:

Configure a user-defined data collector in Performance Monitor to track selected performance counters in a relational database, then run your load test.
Import the performance data from the relational database into a PowerPivot workbook.
Create PivotTables and PivotCharts as necessary to analyze your performance data based on individual servers as well as based on server roles.

Point 1 is by far the most work-intensive step—especially if you have to configure Performance Monitor on some 30 or more servers. As a prerequisite, you must create a relational database on a central SQL server. Let’s call it PerfData. You also need to create a System Data Source Name (DSN) on every computer running Performance Monitor, pointing to the PerfData database. You can simplify this configuration task by importing the following .reg file content into your computers’ registry. Just make sure you replace <Server Name> with the name of your SQL server hosting the PerfData database. Use two backslashes as a separator if you must specify server and instance names, such as SERVER\\POWERPIVOT.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"PerfDSN"="SQL Server"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\PerfDSN]
"Driver"="C:\\Windows\\system32\\SQLSRV32.dll"
"Server"="<Server Name>"
"Database"="PerfData"
"Trusted_Connection"="Yes"

Having imported the .reg file content into the registry, you can verify the presence of the System DSN called PerfDSN by using ODBC Data Source Administrator, which is available in the Control Panel under Administrative Tools.

The next step is to create a data collector in Performance Monitor, which uses the PerfDSN to connect to the PerfData database. Again, it’s best to automate this configuration task, this time by means of an XML template and a batch file. A sample XML template is attached to this blog post. The batch file has the following content (make sure you replace the user account and server names with actual information and add further lines as necessary).

logman import "PerfCollector" -xml "Template.xml" -u contoso\administrator p@ssword1 -s SERVER01
logman import "PerfCollector" -xml "Template.xml" -u contoso\administrator p@ssword1 -s SERVER02

Note that the specified user account requires write permissions to the PerfData database. Performance Monitor uses this account to write the performance counter values. Of course, it’s a good idea to test the configuration:

Start Performance Monitor from the Administrative Tools.
Expand Data Collector Sets, expand User Defined, select PerfCollector, right-click PerfCollector, and then click Start.
Verify that the PerfCollector starts successfully.
Check the PerfData database in SQL Server Management Studio. Performance Monitor creates three tables called CounterData, CounterDetails, and DisplayToID to log performance data.
Stop Performance Monitor again.

Tip: If Performance Monitor does not start the PerfCollector successfully, then either your PerfDSN configuration is incorrect (use ODBC Data Source Administrator to adjust the configuration) or the account you specified for the PerfCollector doesn’t have the required permissions (edit the PerfCollector properties in Performance Monitor and specify a different account under Run As on the General tab).

The performance data gathering system is almost complete. An optional but very useful piece is a batch file to start and stop the PerfCollector on all test machines. The following batch can serve as a starting point. Just make sure you replace SERVER01 and SERVER02 with actual computer names, and add start and stop commands for further computers as necessary. The batch expects a command-line argument, which specifies the number of minutes to run the PerfCollector on the specified computers. It automatically stops the data collection when the specified time has elapsed and ensures in this way that no PerfCollector instances are left running. This helps to avoid flooding the PerfData database.

@echo off
setlocal ENABLEDELAYEDEXPANSION

REM Start the PerfCollector on all servers
@echo on
logman start "PerfCollector" -s SERVER01 -as
logman start "PerfCollector" -s SERVER02 -as
@echo off

REM Set start and end time for performance data collection
set /A Start=1
set /A End=%1

REM Loop for the specified number of minutes
:LOOP
if !Start! gtr !End! GOTO LOOP_END
choice /d y /t 60 > nul
set /A Start+=1
goto LOOP
:LOOP_END

@echo on
logman stop "PerfCollector" -s SERVER01 -as
logman stop "PerfCollector" -s SERVER02 -as

The hard work is done, but before moving on to PowerPivot let’s briefly discuss why you need the PerfData database. After all, Performance Monitor could also write the data to comma- or tab-separated text files. The most obvious disadvantage of text files, however, is that it takes a very long time to import any number of them, especially if you want to import hundreds of millions of rows into PowerPivot. Another significant disadvantage is that the text files tend to be extremely wide. Performance Monitor tracks every selected performance counter in a separate column. For example, the PerfCollector template includes approximately 200 counters, which would imply that PowerPivot ends up with very wide tables. Wide tables increase metadata overhead and decrease storage efficiency. Furthermore, the column names are not intuitive as they include the server name, performance object, and counter name separated by backslashes. Cleaning this up for 200 columns is just too much work. Moreover, text files contain counter values as strings. You’d have to convert them into DateTime and decimal numbers after the import. Again, a tedious task for 200 columns, so don’t torture yourself and just use a SQL Server database.

Now, let’s have some fun with performance data:

Run the PerfCollector for a few minutes to gather some data, then start Excel 2010 on your workstation, and display the PowerPivot window.
Click From Database and select From SQL Server.
In the Table Import Wizard, under Server Name, specify the name of the SQL server hosting the PerfData database. Under Database Name, type PerfData, and then click Next.
Select Write a query that will specify the data to import, and then click Next.
On the next wizard page, in the SQL Statement box, paste the following query:

SELECT CONVERT(DATETIME, CAST(CounterDateTime AS VARCHAR(19))) AS CounterDateTime,
MachineName, ObjectName, CounterName, InstanceName, CounterValue
FROM PerfData.dbo.CounterData JOIN
PerfData.dbo.CounterDetails ON CounterData.CounterID = CounterDetails.CounterID JOIN
PerfData.dbo.DisplayToID ON CounterData.GUID = DisplayToID.GUID
Click Validate to verify that the SQL statement is syntactically correct and then click Finish.
Observe that PowerPivot imports the data successfully and then click Close.

If desired, rename the table to PerformanceData. This is about all it takes to prepare the data for basic analysis:

Switch to the Excel window, and then on the PowerPivot ribbon, in the Report section, click on the little triangle underneath PivotTable. Select the PivotChart option.
In the Create PivotChart dialog box, select Existing Worksheet, and then click OK.
PowerPivot places an empty chart on the worksheet. Select the chart and then, in the PowerPivot Field List to the right, drag the following fields to the field areas:

Field Area	Fields
∑ Values:	CounterValue (automatically changes to Sum of CounterValue)
Axis Fields (Categories):	CounterDateTime
Slicers Vertical:	MachineName
Slicers Horizontal:	ObjectName, InstanceName, CounterName

Under ∑ Values, right-click Sum of CounterValue, and click Edit Measure.
In the Measure Settings dialog box, under Custom Name, type Average Counter Value. Under Choose how you want the selected field to be aggregated, select Average, and then click OK.
Test your solution by clicking on the following slicers:

Slicer	Value
ObjectName	Processor
InstanceName	_Total
CounterName	% Processor Time
MachineName	<Any Computer>

Format the chart according to your own preferences, such as by deleting the Total legend and the date values from the x axis. Don’t forget to save your work.

Additionally, if you want to analyze performance data based on server roles, you must add a lookup table to the PowerPivot model that associates each server with a role. A linked Excel table is a good choice for this purpose:

Switch to Sheet 2 in Excel. In cell A1 type Machine Name and in cell B1 type Server Role.
In the A and B columns of subsequent rows, type the machine names as they appear in the MachineName slicer and specify a server role, such as WFE, ECS, PP, or DB. Make sure that you list each machine name only once (the lookup table must not contain duplicate machine names).
Select the rows that have values in the A and B columns (make sure you do not select any empty rows), and then on the Home ribbon, click Format as Table.
Select an appropriate table style and then in the Format as Table dialog box, select the checkbox My table has headers, and click OK.
Switch to the PowerPivot ribbon and click on Create Linked Table. Verify that PowerPivot creates the linked table successfully. Rename the new table to ServerRoles.
Switch to the PerformanceData table, right-click the MachineName column, and then select Create Relationship.
In the Create Relationship dialog box, under Related Lookup Table, select ServerRoles, and under Related Lookup Column, select Machine Name. Then click Create.
Switch back to Sheet 1 in the Excel window and select the chart with the performance counter values.
In the PowerPivot Field List, notice the message that the PowerPivot data was modified. Click Refresh.
A new ServerRoles node appears in the field list. Expand this node and then drag Server Role to the first position in the Slicers Vertical box, drag the Machine Name node to the second position in the Slicers Vertical box, and then remove the old MachineName slicer by dragging it out of the Slicers Vertical box and dropping it in an empty area.

Note: Due to the direction of the table relationship, the Machine Name column from the ServerRoles table slices both, the ServerRoles table and the PerformanceData table. The MachineName column from the PerformanceData table only slices the PerformanceData table. Therefore, replace the existing MachineName slicer with a new Machine Name slicer to slice the data consistently.
Click on any entry in the Server Role slicer to analyze average performance counter values for groups of servers based on roles.

That’s it for now. Save the workbook and copy it to a document library or other location if you want to share it with others. Note also that you don’t ever need to repeat the modeling work for subsequent performance tests. Just refresh the data in PowerPivot and Excel to load the latest performance counter values from the PerfData database and your job is done! Of course, it might be worth adding some advanced features to this solution by using DAX. This will be the topic of my next blog post. Stay tuned.

Sample Application Released: Analytics for Twitter

KayUnkroth — Sat, 11 Jun 2011 17:44:19 GMT

This week, we released Microsoft Analytics for Twitter, an awesome PowerPivot application to query Twitter within Excel 2010 and perform ad-hoc analysis on Tweet statistics, such as time and frequency of tweets, top tweets, top tweeters, and so forth. The Microsoft Analytics for Twitter application even detects the tone score (see screenshot) based on keywords that you can customize in the workbook.

Useful links:

Download Microsoft Analytics for Twitter http://go.microsoft.com/fwlink/?LinkId=221191
Wiki Help for Troubleshooting http://go.microsoft.com/fwlink/?LinkId=221192