PowerPivot for Excel - Lookup Table in Data Warehouse

Businessman using laptop in office meeting
Ariel Skelley/Blend Images/Getty Images

One of the things I list most about PowerPivot for Excel is the ability to add lookup tables to your data sets. Most of the time, the data you are working with doesn't have every field you need for your analysis. For example, you might have a date field but need to group your data by quarter. You could write a formula, but it's easier to create a simple lookup table within the PowerPivot environment.

You can also use this lookup table for another grouping such as month name and first/second half of the year. In data warehousing terms, you are actually creating a date dimension table. In this article, I am going to give you a couple of example dimension tables to enhance your PowerPivot for Excel project.

New Text Dimension (Lookup) Table

Let's consider a table with order data (the Contoso data from Microsoft includes a data set simile to this). Assume the table has fields for customer, order date, order total, ​and order type. We are going to focus on the order type field. Assume the order type field includes values like:

  • Netbooks
  • Desktops
  • Monitors
  • Projectors
  • Printers
  • Scanners
  • Digital Cameras
  • Digital SLR Cameras
  • Film Cameras
  • Camcorders
  • Office Phones
  • Smart phones
  • PDAs
  • Cell Phones Accessories

In reality, you would have codes for these but to keep this example simple, assume these are the actual values in the order table.

Using PowerPivot for Excel, you would easily be able to group your orders by order type. What if you wanted a different grouping? For example, assume you need a "category" grouping like computers, cameras, and phones. The order table doesn't have a "category" field, but you can easily create it as a lookup table in PowerPivot for Excel.

The complete sample lookup table is below in ​Table 1. Here are the steps:

  • Step 1: You need a distinct list from the type field for your lookup table. This will be your lookup field. From your data set, create a distinct list of values from the order type field. Enter the distinct list of "types" into an Excel workbook. Label the column Type.
  • Step 2: In the column next to your lookup column (Type), add the new field you want to group on. In our example, add a column with a label called Category.
  • Step 3: For each value in your distinct list of values (types in this example), add the corresponding "Category" values. In our simple example, enter either Computers, Cameras or Phones into the Category column.
  • Step 4: Copy the Type and Category data table into your clipboard.
  • Step 5: Open the Excel workbook with the order data in PowerPivot for Excel. Launch the PowerPivot Window. Click Paste which will bring in your new lookup table. Give the table a name and make sure you check "Use first row as column headers." Click OK. You have created a lookup table in PowerPivot.
  • Step 6: Create a relationship between the Type field in the Order table and the Category field in the lookup table. Click on the Design ribbon and choose Create Relationship. Make the selections in the Create Relationship dialog and click Create.

    When you create a PivotTable in Excel based on the PowerPivot data, you will be able to group by your new Category field. Keep in mind that PowerPivot for Excel only supports Inner Joins. If you have an "order type" missing from your lookup table, all of the corresponding records for that type will be missing from any PivotTable based on the PowerPivot data. You will need to check this from time to time.

    Date Dimension (Lookup) Table

    The Date lookup table will most likely be needed in most of your PowerPivot for Excel projects. Most data sets have some type of date field(s). There are functions to calculate the year and month.

    However, if you need the actual month text or the quarter, you need to write a complex formula. It's much easier to include a Date dimension (lookup) table and match it up with the month number in your main data set. You will need to add a column to your order table to represent the month number from the order date field. The DAX formula for "month" in our example is "=MONTH([Order Date]). This will return a number between 1 and 12 for each record. Our dimension table will provide alternate values, which link to the month number. This will provide you flexibility in your analysis. The complete sample date dimension table is below in ​Table 2.

    The date dimension or lookup table will include 12 records. The month column will have the values 1 - 12. Other columns will include abbreviated month text, full month text, quarter, etc. Here are the steps:

    • Step 1: Copy the table from Table 2 below and paste into PowerPivot. You could create this table in Excel but I am saving you time. You should be able to paste directly from the selected data below if you are using Internet Explorer. PowerPivot picks up the table formatting in my testing. If you are using another browser, you may have to paste into Excel first and copy it from Excel to pick up the table formatting.
    • Step 2: Open the Excel workbook with the order data in PowerPivot for Excel. Launch the PowerPivot Window. Click Paste which will bring in your lookup table copied from the table below or from Excel. Give the table a name and make sure you check "Use first row as column headers." Click OK. You have created a date lookup table in PowerPivot.
    • Step 3: Create a relationship between the Month field in the Order table and the MonthNumber field in the lookup table. Click on the Design ribbon and choose Create Relationship. Make the selections in the Create Relationship dialog and click Create.

    Again, with the addition of a date dimension, you will be able to group the data in your PivotTable using any of the different values from the date lookup table. Grouping by quarter or the name of the month will be a snap.

    Sample Dimension (Lookup) Tables

    Table 1

    TypeCategory
    NetbooksComputer
    DesktopsComputer
    MonitorsComputer
    Projectors & ScreensComputer
    Printers, Scanners & FaxComputer
    Computer Setup & ServiceComputer
    Computers AccessoriesComputer
    Digital CamerasCamera
    Digital SLR CamerasCamera
    Film CamerasCamera
    CamcordersCamera
    Cameras & Camcorders AccessoriesCamera
    Home & Office PhonesPhone
    Touch Screen Phones Phone
    Smart phones & PDAs Phone

    Table 2

    MonthNumberMonthTextShortMonthTextFullQuarterSemester
    1JanJanuaryQ1H1
    2FebFebruaryQ1H1
    3MarMarchQ1H1
    4AprAprilQ2H1
    5MayMayQ2H1
    6JunJuneQ2H1
    7JulJulyQ3H2
    8AugAugustQ3H2
    9SepSeptemberQ3H2
    10OctOctoberQ4H2
    11NovNovemberQ4H2
    12DecDecemberQ4H2