digest
Welcome to Power Pivot video number two. If you want to download these workbooks and follow along, the first is "Power Pivot 2 01 Lots of Non-Normalized Data," and I'm going to use Control+Tab to go over to the other Excel workbook, "Power Pivot 2 Columnar Database Non-Normalized," and Control+Tab again to "Power Pivot 2 03 Lots of Normalized Data."
In this video we want to talk about importing a data set from an Excel workbook into Power Pivot. The point of this video is to see that a huge data set, even if it's not normalized, can have its file size dramatically reduced when you import it into Power Pivot.
Let's check this table out. Control+Down Arrow shows 500,000 rows of data in Excel. This is not truly big data by database standards. For Excel it's kind of big, but we want to illustrate that when we import something like this, we'll look at the file size, and then when we import it into our columnar database, the reduction in file size will be remarkable.
Non-normalized data means that in the Sales Rep column we have lots of repeats. Instead, we could have a key, and then a whole lookup table with sales rep name, product, price, and region. Let's go look at that. I'm going to Control+Tab to the third workbook, "Lots of Normalized Data." It's the same transaction data set, but instead of sales rep, region, and product, we have keys, which are numbers that refer to other, smaller tables. Here are all the products and prices. In Excel, we use a VLOOKUP to bring the price and product information over. Here is the sales rep table, and here is the region table. In databasing or Power Pivot, we could relate these with a relationship. That's lots of normalized data, and we'll come back to this data set later.
What we want to do now is take the non-normalized data set and import it into Power Pivot. Remember, it has 500,000 rows and is not normalized. I'm going to close this workbook without saving, and then import it. We can go to Power Pivot, Manage Data Model, or use the keyboard shortcut Alt+B+M. In the Get External Data group, we can choose From Other Sources, and there is a long list of options, including Access and other databases. Scroll all the way down to Excel files, which we'll look at more later. We want the Excel file option. Click Next.
I'll check "Use first row as column headers," enter the Excel file path using the Browse button, and select "Lots of Non-Normalized Data." Double-click it. Then click Next. These dialogue boxes look similar for Access and other data imports, and we'll cover those later. The key is that once we get here, I don't want to click Finish yet. I want to click Preview and Filter, and we'll see more about that button later. I always click it to make sure there's no trouble. It looks okay. We don't need to examine all the data; I just want to make sure there are no extra columns. I'll click OK. Later we'll see how to filter out items we don't want. Now I'll click Finish.
That's it. We've imported the data. This is the first time in this video series that we've imported from an external source into Power Pivot. The rows are loading, almost 500,000 of them. Click Close, and there's our data set. We're not going to worry about sorting or doing any data modeling now. I'm simply going to close the Manage Data Model window and return to Excel. Enable Control+S to save, and then let's look at the file size of the actual columnar database, non-normalized file.
We started with lots of non-normalized data at 20 megabytes. When we imported it into Power Pivot, it went from 20 to about 17 megabytes using the columnar database we talked about last time. Now look at the lots of normalized data in the columnar database. I imported this before the video just to see the file size. The normalized version started at 16,000 and went down to 1.8 megabytes.
So the point of this video is that the columnar database is amazing. It keeps the data in a columnar format, compresses it, and produces a smaller file size. That database also helps with formula speed. We'll see you in the next video.