SharePoint Saturday Huntsville

It’s another Saturday, must be time for another speaking engagement. Today I’m in Huntsville Alabama speaking at their SharePoint Saturday. Now you may wonder what a SQL BI guy is doing at a SharePoint event. There are two kinds of BI professionals in the world, those who are currently working with SharePoint and those who will be working with SharePoint. I believe the converse could also be said for SharePoint people. There those who are currently hosting BI solutions, and those who will be hosting BI solutions. It’s important then, that SharePoint professionals have a clear understanding of what business intelligence is, including all the terms that get used in the BI world. In addition SharePoint 2010 contains features to support a new tool called PowerPivot. Understanding what a PowerPivot workbook is, and how their customers will be using it will give SharePoint professionals better understanding of how to host PowerPivot content.

My first session of the day is entitled “Off and Running With PowerPivot for Excel 2010”. In this session I’ll be giving an overview of PowerPivot, then we’ll be looking at how to import and manipulate data within Excel. This presentation is demo heavy but there are a few slides which can be downloaded from this link.

In my second presentation we’ll dig into the guts of data warehousing and business intelligence. Business Intelligence has its own vocabulary, during the session the audience will learn all the buzzwords around BI and dig into the data warehouse to see how these terms are implemented. This particular presentation is a slide heavy deck, unlike my previous session there is little in the way of demonstrations. We’ll briefly touch on SharePoint, understand though that this presentation centered largely around understanding what BI is so you’ll understand what your SharePoint system is being used for. The slides are available from this link.

If you are reading this after the fact, then let me thank you for coming to my presentations. I hope you found them useful, please don’t hesitate to contact me if I can be of further assistance.

Import Filters in PowerPivot

PowerPivot has the ability to import millions of rows of data into Excel 2010 for purposes of analyzing, slicing and dicing. However, even though you can import vast amounts of data you may not always want to. There are many reasons for this.

You may wish to limit the amount of data for security reasons. Perhaps bringing in all of the data may put an unneeded strain on the server. Most likely though, is you simply do not need all of the data. As an analyst you may be interested in only the data for a segment of your organization, such as a single plant or department.

Fortunately filtering data is an easy task. If you are not familiar with importing data into PowerPivot, may I suggest you first review my step by step posted yesterday.

Launch Excel 2010 and go to the PowerPivot tab, then launch the PowerPivot window. Select the "From Database" to start the import process, and for this example we’ll use SQL Server. Now, like in my previous blog post enter your credentials to connect to a SQL Server. For this example we’ll just pick one table, SalesOrderDetail from the AdventureWorksLT2008 database.

With the SalesOrderDetail picked, look in the lower right side of the dialog. You will see a button labeled "Preview & Filter".

When you click that button you should see a new dialog appear.

Here you can see a subset of your data, only the first few rows. It’s enough to give you an idea of what the data looks like, but it won’t show you every single row. Considering the fact that PowerPivot is capable of importing millions of rows, this is probably a good thing.

Within this dialog we can do some pretty powerful things. Let’s start by eliminating a column we don’t need in the data we want to import. Scroll to the right until you see the rowguid column and uncheck the box next to the column header, as you see below.

By unchecking this box this column will not be imported into our Excel 2010 PowerPivot table.

Recall though that the name of this dialog was Preview & Filter. We can also do some review of our data to ensure it’s what we want. Let’s say we want to look to see the range of values for our line totals. Click the downward facing triangle button to the right of the LineTotal column header. A drop down menu will appear. Select "Sort Smallest to Largest"

Scrolling through the data you’ll notice that the data has indeed been sorted. Also note the menu icon to the right of the column name changes to indicate a sort has been applied to this column. (Remember though it’s not showing all rows, just the first few sorted in order.) An important thing to note tough, this sorting applies only to the data as you see it in the Preview & Filter area, once you click the Finish button on the Table Import Wizard the sort is removed. While the preview options in this dialog are not saved, filters are. If you had pressed Finished, you’d have seen that rowguid is not included in the result set. But don’t click Finish yet, we’re not quite done filtering.

For our next filter, let’s decide for purposes of this report we are only interested in large orders. We’ll define large as "Line Total greater than 1,000 dollars". Once again open the menu to the right of the LineTotal column header and select "Number Filters". A pop out menu will appear, from it select "Greater Than…"

When the Custom Filter pops up, enter 1000 next to the is greater than box and click OK.

Clicking OK will reveal lesser amounts have been removed. Other filters besides numeric filters are available. For text data, you have "is equal to" and "is not equal to" available. Date filters work the same, having the "is equal to" and "is not equal to" available. I’m hopeful that for date types further functionality will be added in the future, such as date ranges or "is greater/less than" types of functionality.

Let’s click the OK button on the Preview & Filters window to return to the Table Import Wizard. You’ll now see an Applied filters link in the row with the table name, click it to see what filters are in effect.

When you click the link you’ll see:

While you cannot edit this information, it is nice to see it all in one location. Click OK to close and return to the previous window.

Click Finish to complete the import. You will see the data you asked for, only rows with LineTotal greater than 1000 and without the rowguid column.

Hopefully you’ve seen how powerful the filtering tools included with PowerPivot are. Using them you can remove unwanted rows and columns, limiting not only the amount of data you have to pull across the network but that you have to store locally in the PowerPivot Excel 2010 spreadsheet. Limiting your data will ensure only the rows required for the analysis are included, saving time and enhancing security.

SharePoint Saturday Birmingham – Intro to Microsoft PowerPivot

Last Saturday I not only attended but presented at SharePoint Saturday Birmingham. First I have to give Terry Webster a shout out, he did a fantastic job organizing and running the event. I learned a lot, although I admit I felt a bit like a fish out of water, being a SQL Server guy in the SharePoint pond.

Thanks too to all the volunteers who helped run the show, my co-presenters, and the many vendors who attended. Without the support of all those folks this event would not have been possible.

I also want to thank everyone who attended my PowerPivot presentation. Last Friday I did a rather lengthy overview that encapsulated much of my presentation; here are the slides that you saw at the event.

I also want to give you a quick overview of the steps I did in my demo so you can follow along. First you’ll need the bits. The PowerPivotPro has a good blog entry on where to download them from:

http://powerpivotpro.com/2009/11/18/powerpivot-ctp3-beta-download-links/

All you need are the first two items, Office 2010 Beta 2 and PowerPivot for Excel 2010 to reproduce what I did in the demo I presented.

Next you’ll need some data. I had a SQL Server instance installed and used the AdventureWorks2008 LT database available at CodePlex. The LT version is the light weight version of the full AdventureWorks sample, I chose it for it’s simplicity.

http://msftdbprodsamples.codeplex.com/

If you don’t have SQL Server on your box you can use alternate data, or check with your friendly neighborhood DBA. Many have AdventureWorks installed on a test box and might be able to help you out.

OK, so to get this far you’ve downloaded and installed everything, and have access to a dataset. Ready? Let’s go.

1. Launch Excel 201, and click the PowerPivot tab.

2. Click the PowerPivot window button.

3. Click the “From Database” button on the Home tab. In the menu select “From SQL Server”

4. The Table Import Wizard will appear. In “Server name” field, click the drop down and pick your database server. Leave it set to the “Use Windows Authentication” (unless told otherwise if you are using a database provided by a DBA). Under Database name, pick AdventureWorksLT2008. It’s probably a good idea to click the Test Connection button just to make sure everything is correct. If so press Next to continue.

5. On the next screen, leave it at the default of “Select from a list of tables and views to choose the data to import” and click Next.

6. On the next screen pick these tables: SalesOrderDetail, SalesOrderHeader, Product, Product Category, Address, Customer, CustomerAddress. Note the “Preview & Filter” button. In the future you may wish to experiment with this to filter the data you are importing, but it’s not needed for this demo. Click Finish to move on.

7. On the next screen you’ll see it importing the tables. When it gives you the Success message click Close.

8. (Note, this next step differs just slightly from the live presentation, in it I added a column but did it in a different place.) Click on the SalesOrderDetail tab. Click on a blank cell under the Add Column out to the right of the data.

9. In this example we’ll decide that 80 percent of our Line Total is cost, which means the other 20% is profit. We’ll calculate the profit by clicking in the fx bar and typing =[LineTotal]*.20

10. Right click on the “CalculatedColumn1” column header and select “Rename column” from the menu. The column header will highlight and allow you to type over it. Change the name to Profit.

11. Click on PivotTable and pick “Single Pivot Table” in the drop down menu.

12. In a pop up dialog, it will ask if you want a new or existing worksheet. Pick “New worksheet” and click OK.

13. Over on the right you will see the “Gemini Task Pane”. Gemini is the original code name for PowerPivot. To create a basic PowerPivot pivot table, follow these steps.

14. Under SalesOrderDetail, click on then drag the LineTotal and Profit fields to the Values area.

15. Under ProductCategory, drag the Name down to Row Labels.

16. Under Product drag the Name to the Row Labels area.

17. Under the Address, click and drag the CountryRegion, then StateProvince to the ColumnLabels area.

18. Drag the StateProvince to the Slicers Vertical area.

19. Your Gemini Task Pane should look something like:

20. Your spreadsheet should look something like:

At this point you have completed all of the steps as shown in the presentation. Now start playing. You can jump back to the PowerPivot data sheets by clicking the “PowerPivot window” button as shown in step 1. Back there you might try experimenting with a chart, or on the pivot table apply different effects and slicers.

Introducing Microsoft PowerPivot

What is PowerPivot? Well according to Microsoft:

“PowerPivot is Microsoft Self-Service Business Intelligence”

I can see from the glazed looks you are giving your monitor that was clear as mud. So let’s step back a bit and first define what exactly is Business Intelligence.

Business Intelligence

Business Intelligence, often referred to as simply “BI”, is all about taking data you already have and making sense of it. Being able to take that information and turn it from a raw jumble of individual facts and transform it into knowledge that you can take informed actions on.

In every organization there is already someone who is doing BI, although they may not realize it. Microsoft (and many IT departments) refer to this person as “that guy”. A power user, who grabs data from anyplace he (or she) can get it, then uses tools like Excel or Access to slice it, dice it, and analyze it. This person might be an actual Business Analyst, but more often it’s someone for who BI is not their main job. Some common examples of people doing their own BI today are production managers, accountants, engineers, or sales managers, all who need information to better do their job. Let’s look at an illustration that will make it a bit clearer.

In this example, put yourself in the role of a sales manager. You have gotten IT to extract all of your sales orders for the last several years into an Excel spreadsheet. In order to determine how well your sales people are doing, you need to measure their performance. You’ve decided that the amount sold will be a good measure, and use Excel to give you totals.

In BI terms, the column “Total Sales” is known as a measure, or sometimes a fact, as it measures something, in this case the sales amount. The grand total sales amount is often called an aggregation, as it totals up the individual rows of data that IT gave us. But now you might be wondering why Andy’s sales are so low? Well, now you want to dig deeper and look at sales by year.

In BI terms, the names of the sales people are a dimension. Dimensions are often either a “who” (who sold stuff) or a “what” (what stuff did we sell). Places (where was it sold) and dates (when was it sold) are also common dimensions. In this case the sales dates across the top (2007, 2008, 2009) are a date dimension. When we use two or more dimensions to look at our measures, we have a pivot table.

Now we can see a picture emerging. It’s obvious that Andy must have been hired as a new salesperson in late 2008, since he shows no sales for 2007 and very small amount in 2008. But for Paul and Kimberly we can look at something called trends in the BI world. Kimberly shows a nice even trend, rising slowly over the last three years and earns a gold star as our top performer.

By being able to drill down into our data, we spot another trend that was not readily obvious when just looking at the grand totals. Paul has been trending downward so fast the speed of light looks slow. Clearly then we now have information to take action on, commonly known as actionable intelligence.

So remind me, why do we need PowerPivot?

As you can see in the above example, “that guy” in your company clearly has a need to look at this data in order to do his job. Not only does he need to review it, he also has the issue of how to share this information with his co-workers. Unfortunately in the past the tools available to “that guy” have had some drawbacks. The two main tools used by our analyst have been either Excel, or a complete BI solution involving a data warehouse and SQL Server Analysis Services.

Excel’s main limitations center around the volume of data needed to do good analysis. Excel has limits to the number of rows it can store, and for large datasets a spreadsheet can consume equally large amounts of disk space. This makes the spreadsheet difficult to share with coworkers. In addition mathematical functions like aggregations could be slow. On the good side, Excel is readily available to most workers, and a solution can be put together fairly quickly.

A full blown BI solution has some major benefits over the Excel solution. A data warehouse is created, and then SQL Server Analysis Services (often abbreviated as SSAS) is used to precalculate aggregations for every possible way an analyst might wish to look at them. The data is then very easy to share via tools like Excel and SQL Server Reporting Services. While very robust and powerful solution, it does have some drawbacks. It can take quite a bit of time to design, code, and implement both the data warehouse and the analysis services pieces of the solution. In addition it can also be expensive for IT to implement such a system.

Faster than a speeding bullet, more powerful than a locomotive, it’s PowerPivot!

PowerPivot combines the best of both worlds. In fact, it’s not one tool but two: PowerPivot for Microsoft Excel 2010, and PowerPivot for SharePoint 2010. What’s the difference you ask? Good question.

PowerPivot for Microsoft Excel 2010

PowerPivot acts as an Add-on for Excel 2010, and in many ways is quite revolutionary. First, it brings the full power of SQL Server Analysis Services right into Excel. All of the speed and power of SSAS is available right on your desktop. Second, it uses a compression technology that allows vast amounts of data to be saved in a minimal amount of space. Millions of rows of data can now be stored, sorted, and aggregated in a reasonable amount of disk space with great speed.

PowerPivot can draw its data from a wide variety of sources. As you might expect, it can pull from almost any database. Additionally it can draw data from news feeds, SQL Server Reporting Services, other Excel sheets, it can even be typed in manually if need be.

Another issue that often faces the business analyst is the freshness of the data. The information is only as good as the date it was last imported into Excel. Traditionally “that guy” only got extracts of the database as IT had time, since it was often a time consuming process. PowerPivot addresses this through its linked tables feature. PowerPivot will remember where your data came from, and with one simple button click can refresh the spreadsheet with the latest information.

Because PowerPivot sits inside Microsoft Excel, it not only can create basic pivot tables but has all the full featured functionality of Excel at its disposal. It can format pivot tables in a wide array of styles, create pivot charts and graphs, and combine these together into useful dashboards. Additionally PowerPivot has a rich set of mathematical functionally, combining the existing functions already in Excel with an additional set of functions called Data Analysis eXpressions or DAX.

PowerPivot for SharePoint 2010

PowerPivot for Excel 2010 clearly solves several issues around the issue of analysis. It allows users to quickly create spreadsheets, pivot tables, charts, and more in a compact amount of space. If you recall though, creation was only half of “that guys” problem. The other half was sharing his analysis with the rest of his organization. That’s where PowerPivot for SharePoint 2010 comes into play.

Placing a PowerPivot Excel workbook in SharePoint 2010 not only enables traditional file sharing, but also activates several additional features. First, the spreadsheet is hosted right in the web browser. Thus users who might not have made the transition to Excel 2010 can still use the PowerPivot created workbook, slicing and filtering the data to get the information they require.

Data can also be refreshed on an automated, scheduled basis. This ensures the data is always up to date when doing analysis. Dashboards can also be created from the contents of a worksheet and displayed in SharePoint. Finally these PowerPivot created worksheets can be used as data sources for such tools as SQL Server Reporting Services.

Limitations

First, let me preface this by saying as of this writing all of the components are either in CTP (Community Technology Preview, a pre-beta) or Beta state. Thus there could be some changes between now and their final release next year.

To use the PowerPivot for Excel 2010 components, all you have to have is Excel 2010 and the PowerPivot add-in. If you want to share the workbook and get all the rich functionality SharePoint has to offer, you’ll have to have SharePoint 2010, running Excel Services and PowerPivot 2010 Services. You’ll also have to have SQL Server 2008 R2 Analysis Services running on the SharePoint 2010 box. Since you’ll have to have a SQL Server instance installed to support SharePoint this is not a huge limitation, especially since SSAS comes with SQL Server at no extra cost.

One thing I wish to make clear, SharePoint 2010 itself can run using any version of SQL Server from SQL Server 2005 on. It is the PowerPivot service that requires 2008 R2 Analysis Services.

One other important item to note: at some point the load upon the SharePoint 2010 server may grow too large if especially complex analysis is being done. Fortunately SharePoint 2010 ships with several tools that allow administrators to monitor the load and plan accordingly. At the point where the load is too big, it is a clear indication it’s time to transition from a PowerPivot solution to a full BI solution using a data warehouse and SQL Server Analysis Services.

What does PowerPivot mean for business users?

For business users, and especially “that guy”, it means complex analysis tools can be created in a short amount of time. Rich functionality makes it easier to spot trends and produce meaningful charts and graphs. It also means this information can be shared with others in the organization easily, without imposing large burdens on the corporate e-mail system or local file sharing mechanisms.

No longer will users be dependent on IT for their analysis, they will have the power to create everything they need on their own, truly bringing “self service BI” to fruition.

What does PowerPivot mean for Business Intelligence IT Pros?

The first reaction many BI developers have when hearing about PowerPivot is “oh no, this is going to put me out of a job!” Far from it, I firmly believe PowerPivot will create even more work for BI Professionals like myself.

As upper management grows to rely on the information provided by PowerPivot, they will also begin to understand the true value BI can bring to an organization. Selling a new BI solution into an organization where none currently exists can be difficult, as it can be hard to visualize how such a solution would work and the value it brings. PowerPivot allows BI functionality to be brought into an organization at a low development cost, proving the value of BI with minimal investment. Thus when there is a need to implement a larger, traditional BI project those same managers will be more forthcoming with the dollars.

Second, as users pull more and more data, they are going to want that data better organized than they will find in their current transactional business systems. This will in turn spur the need to create many new data warehouses. Likewise the IT department will also want data warehouses created, to reduce the load placed on those same transactional business systems.

I also foresee PowerPivot being used by BI Pros themselves to create solutions. The database structure of many transactional database systems can be difficult to understand even for experienced IT people, much less users. BI Pros can use PowerPivot to add a layer of abstraction between the database and the users, allowing business analysts to do their job without having to learn the complexity of a database system.

BI Pros can also use PowerPivot to implement quick turnaround solutions for customers, bringing more value for the customer’s dollar. When a BI Pro can prove him (or her) self by providing rich functionality in a short time frame it’s almost always the case they are brought back in for multiple engagements.

PowerPivot also provides great value to BI Pros who are employed full time in an enterprise organization. They can create solutions much quicker than before, freeing them up to do other valuable tasks. In addition PowerPivot solutions can provide a “stop gap” solution, pushing the date at which the organization needs to spend the dollars for a full blown BI solution and allowing IT to plan better.

Finally I see great value in PowerPivot as a prototyping tool for larger BI projects. Now users can see their data, interact with it, analyze it, and ensure the required measures and dimensions are present before proceeding with the larger project.

I’ll reiterate, if anything I believe PowerPivot will create an explosion of work for the Business Intelligence Professional.

Where can I learn more?

Well right here for one. I have become quite interested in PowerPivot since seeing it at the SQL PASS 2009 Summit. I think it will be a valuable tool for both myself and my customers. This will be the first of many blog posts to come on PowerPivot. I am also beginning a series of presentations on PowerPivot for local user groups and code camp events. The first will be Saturday, November 21^st 2009 at the SharePoint Saturday in Birmingham Alabama, but there will be many more to come. (If you’d like me to come speak at your group just shoot me an e-mail and we’ll see what we can arrange.)

There’s also the PowerPivot site itself:

http://powerpivot.com/

I’ve also found a small handful of blogs on PowerPivot, listed in no particular order:

Summary

Thanks for sticking with me, I know this was a rather long blog post but PowerPivot has a lot of rich functionality to offer. While PowerPivot is still in the CTP/Beta stage as of this writing, I see more and more interest in the community, which will continue to grow as PowerPivot moves closer to release. I hope this post has set you off on the right step and you’ll continue to come back for more information.

.Net University – BizTalk

Earlier this week I was privilidged to attend the first .Net University for BizTalk. It was a very informative day long session, I feel like I now have a grasp on at least the fundementals of BizTalk and can talk intelligently about it. I have a long way to go, of course, but this was a great launching point.

If you are not familiar with .Net University, you need to check out their website http://www.dotnet-u.com/ . All of the slide decks, labs, and other courseware are availble not only for you to look at, but to use in doing your own presentation. Your user group or business could put on it’s very own .Net University using the supplied materials. Don’t worry if you are not a guru, they are even publishing videos of the sessions for you to watch and see how the “pros” did it. Currently courseware is available for .Net 3.0 and BizTalk, and the video sessions for .Net 3.0 were just released. They videoed the presentations at the BizTalk session I was in, so I would expect them to be released in the near future. Coming soon will be courseware for Sharepoint and Silverlight.

.Net University was the brainchild of Microsoft Developer Evangelist Doug Tunure ( http://blogs.msdn.com/dougturn/ ). Recently he and Mark Dunn of Dunn Training (http://www.dunntraining.com/) went to TechEd in Asia and used .Net U there. Mark Dunn recorded interviews and talked about it in a recent Dot Net Rocks episode (#288: http://www.dotnetrocks.com/default.aspx?showNum=288 ). Take a listen, they do a better job of explaining it than I can. Mark, by the way, was also one of the presenters at the BizTalk session I was in.

If your user group is looking for a good opportunity to reach out to the community, consider putting on your own .Net U. You can do it in one day, or break it into two or four sessions. Looks like there will be a lot of good material to get you started, and you can even get certificates to present to your attendees.