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".

clip_image001

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

clip_image002

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.

clip_image003

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"

clip_image004

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…"

clip_image005

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

clip_image006

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.

clip_image007

When you click the link you’ll see:

clip_image008

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.

About these ads

5 Responses to “Import Filters in PowerPivot”

  1. Simon Slade Says:

    Good post, do you have an RSS feed I can bookmark please?

  2. arcanecode Says:

    Thanks Simon. If you look to the upper left side of my blog, under the MVP Logo you’ll see a big orange button. Either that or the link below it will let you subscribe to my RSS feed provided by FeedBurner.

    Enjoy!

  3. Lee Says:

    The problem I see with the filters is that they only act on one table. Supposed you have a star schema with a fact table and some dimension tables, for example one of the dim tables is Dim_Customer. I want to do an analysis of just a subset of customers. It appears I would need to put filters on BOTH the Fact table and Dim_Customers table. Worse, the filter on the Fact table would need to specify the foreign key values for Dim_Customers. It appears power pivot will NOT use relationships such that you can put a filter on a related table, e.g. Dim_Customer, and have that cause the Fact table import to only import rows pertaining to the customer filter. So is it me, or does this render the Import Table Wizard and Row Filtering to be almost useless???

  4. Andre van der Westhuizen Says:

    How do I exclude certain “text” from imported data.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 102 other followers

%d bloggers like this: