PowerPivot – Personal BI on your desktop

What do you think about business intelligence (BI)? A lot of people believe BI is only for large enterprise because it is expensive, takes long time to implement and requires special trained consultants. The worst part is that it does not often deliver what it promises to do – real time, self-service information delivery. Even there is BI within the organization, many analysts still heavily rely on their most trusted software, Excel to do their analysis because their corporate BI just does not provide everything they want or they are not familiar with the new BI environments. We want a BI system that we can manage and ready to use tomorrow.

PowerPivot may be the solution that we have been looking for; it is a free Excel Add-in from Microsoft and if you know pivot table then we can start using it right away. And it is much more powerful than Excel. We can download it from http://www.microsoft.com/en-us/download/details.aspx?id=29074. Once we install PowerPivot, we can download an example workbook from Microsoft, http://www.microsoft.com/en-us/download/details.aspx?id=28572. There are 7 tabs in the workbook. Let’s examine the first one; SalesByCountry which you will find a very simple pivot table.

You can also see a slicer that you can use to select sale channels:

And a PowerPivot Field List (it is very similar to pivot table field list):

When I showed this workbook to my colleagues, they often said “Hold on here! Is this just a regular Excel Pivot Table?” Well, this is the beauty of PowerPivot; it is an extension of Excel Pivot Table. If you know Pivot Table, then you can start using PowerPivot. The major difference is the data behind it. You can analyze much, much more data with PowerPivot. How much more? There are 2 fact tables and 10 dimension tables in this workbook. FactSales has 3.4 million records and FactInvesntory has just over 8 million records. MILLIONS! PowerPivot can handle millions of records; this is the beauty of PowerPivot. One of the news features in PowerPivot 2012 is Diagram View which allows us to easily add and change relationships of the tables. I like this new feature because BI often includes hundred or even thousands of tables; it is great to visualize them.

You can see the relationship between the tables if you click on the arrow line.

 

i.e. FactSales and DimStore are connected by StoreKey. Let’s go back to our first PivotTable; it is a very simple PivotTable which only has 4 elements.

  • Slicers Vertical – ChannelName (DimChannel)
  • Column Labels – CalendarYear (DimDate)
  • Row Labels – RegionCountryName (DimGeography)
  • Values – Sales (FactSales)

If you right click on the first 3 elements, you will see their Field Settings:

 

 

The last one, Sales has its own Edit Measure dialogue because it is a Measure Field:

 

 

It has a very simple formula: =SUM(FactSales[SalesAmount]), i.e. sum the SalesAmount from FactSales. There is a fundamental difference between PowerPivot and Excel; the basic element in PowerPivot is column so all the formulas in PowerPivot are against columns rather than cells. Let’s review the data behind this report by clicking PowerPivot Window button.

 

 

PowerPivot for Excel will be displayed and we can see all the data behind all these reports. DimDate has 2,556 records and many data columns that describe the date information.

 

 

FactSales has over 3.4 million records; it has many data columns and other important information. Have you noticed the first half of the screen has all the data columns but there is something else at the bottom:

  • Sales
  • StoreSales
  • PctAllRegions
  • YOYGrowth
  • And more….

What are these rows?

 

 

If we go back to our first report SalesByCountry and look at the PowerPivot Field List under FactSales, you would notice some of the fields have a calculator icon next to them. These are the measure fields; each has its own formula.

 

  • Sales:=SUM(FactSales[SalesAmount])
  • StoreSales:=CALCULATE([Sales],DimChannel[ChannelName]=”Store”)
  • PctAllRegions:=[Sales]/CALCULATE([Sales] ,ALL(DimGeography))
  • YOYGrowth:=[Sales] – [Sales](PREVIOUSYEAR(DimDate[Datekey]))
  • PrevYearSales:=IF( COUNTROWS(VALUES(DimDate[CalendarYear]))=1 , [Sales] (PARALLELPERIOD(DimDate[Datekey],-12,MONTH)), BLANK() )

These are not Excel formula; they are something new that we need to learn if we want to maximize PowerPivot’s potential. They are called Data Analysis Expressions (DAX) Language which I will discuss it in near future. But even without DAX, PowerPivot allows us to handle analysis on data volume that is impossible on Excel before and it is much faster! If you would like to find out more about PowerPivot, please stay tuned, I am going to discuss more about DAX, how to import data into PowerPivot. PowerPivot offers us the opportunity to have our own personal BI on our desktop. It has been available since the release of Excel 2010 and recently Microsoft has a new version, PowerPivot 2012 which includes many great new features that will make our analysis better and faster!

Microsoft PowerPivot – Data Model

There are many challenges in building our own PowerPivot.  Most of them are related to data; we probably all heard about “Garbage in, garbage out”.  I am not going to talk about how to clean your data which is a huge subject to discuss.  Instead, I am going to show some basic tips on how to store data in PowerPivot.  We can consolidate all the data into one table or we can normalize the data and store them into many smaller tables.

One big simple table

If we store all data into one table; it can be huge.  A product name can be 20 times longer than a product ID which is a numeric field, e.g. “Contoso Laptop Cooling Hub notebook fan with 4 ports USB hub E80 Black” vs. "754”.  PowerPviot can hold a lot of data but it still has a limit, i.e. 4 GB, so we better make use of available memory while we can.

Normalization (Star Schema)

We can fully normalize our data into multiple small tables, then it would save a lot of memory space.  Normalization is a very common database design in a lot of business application.  However, there would be a performance issue; because the report may require information from many different tables and PowerPivot has to link them all before it can return the requested information.  It can take a long time to process.  We don’t want our CFO to waste his / her time in front of their computer; they want the information and they want it now!

Star Schema

The star schema is the most common data model that is used in PowerPivot.  It is called a star schema because the model looks like a star, with a big fact table at center and linked with multiple dimension tables.

image

We notice that columns from fact table are normally used in the Value section of PowerPivot Field List and columns from dimension tables are found in either Column Labels or Row Label sections.

image

Fact Table

A fact table usually has 2 sets of columns.  The fact columns(or business information) are typically numeric; e.g. TotalCost, TotalSales, and TotalPorfit.  The key columns are identification keys that are used to connected to dimension tables, e.g. SalesKey, DateKey, and ChannelKey.

A fact table normally contains a lot of details records. and can take up as much as 90% of storage.

Dimension Table

Dimension table provides meaning to fact table,  Each dimension table describes certain attributes of a particular business information; e.g. DimStores contains store information such as Name, Manager, Location etc.  They are normally descriptive, textual values and much compact than fact table.

Most dimension tables are denormalized; however, there are situations that you may want to normalize dimension tables; i.e. spilt the tables into multiple smaller tables.  The most typical situation is that if you have a dimension table and some of the columns are hardly used in any report, then you may want to take out those columns and put them in another table.  We can also normalize a table if some of the columns have a lot of empty cells.  Another example is that if there is different update behaviours of the columns, some can be updated by replacing the original value but some cannot be overwritten.

If we normalize the dimension tables, then we create a star schema which may slow down the performance.  It is something that we must make our judgement.

image

Data model design is often one of the most challenging tasks during a PowerPivot development; there is no universal design rule.  And it would change over time, so we have to monitor our PowerPivot and fine tune as it is necessary.  This may be the part that we want to involve an expert.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Microsoft PowerPivot for SharePoint

It is not a typo!  Microsoft PowerPivot for Excel has a twin, PowerPivot for SharePoint.  Before I tell you what it is; let’s review what you can do with PowerPivot for Excel.  You can now consolidate all you data into PowerPivot, define the relationship among them and extract information in lighting speed.  Isn’t it great?  The only problem is now everyone would come to you to get their information.  You become the central information hub!  Well, you can store the PowerPivot workbook in a shared network drive; so everyone can get the information they want.  But Microsoft have a more effective collaboration platform, SharePoint, which allows feasible controls and extensive collaboration features.  Here are some of the key benefits of PowerPivot for SharePoint:

Excel Services

Once you developed a PowerPivot workbook, you can upload it to SharePoint server.  SharePoint has an Excel Services that would allow CxO and other decision makers to use the workbook interactively and concurrently from their browser.  They can do every analysis that you do on desktop Excel.  They don’t even need to install Excel 2010 and PowerPivot.  The best thing is that you don’t need to worry if someone accidentally modifies your PowerPivot workbook which is securely stored in SharePoint Server.

PowerPivot Gallery

This is a new feature for SharePoint; PowerPivot Gallery is used to specially store the PowerPivot workbooks.  Decision makers can preview all the PowerPivot workbooks in thumbnail.  They can see if everything is green without opening any workbook.  Executives can find financial dashboard, sales scorecard and other KPIs all in a single page.

Collaboration

SharePoint is a very powerful collaboration platform which allows workflow processes to be triggered automatically by predefined criteria, e.g. if one of the marketing KPI is below certain value, then an investigation processes would be triggered.  When the investigation is completed and VP marketing approves the report, it can be linked to the KPI that initiates the investigation.

Change Management

How many times did someone modify you Excel workbooks that were stored on network drives?  You have no idea who did it?  Why it was modified?  SharePoint provides a good set of change management tools like check-in/out, collaboration, version control, approval workflows, email alerts when documents are updated or new documents posted, retention policies, etc.

Automatic Data Refresh

You can setup SharePoint to automatically upload data to the PowerPivot workbook in any specified schedules.  Financial and sales data can be refreshed daily and hourly respectively; product categories can be uploaded once a month.  The consumers of your PowerPivot workbook would always get the latest information.  If any upload process is failed, SharePoint can alert you immediately via email, SMS or pager.

PowerPivot for SharePoint is a natural extension of PowerPivot for Excel!  You prepare the PowerPivot workbook.  Once it is ready, you upload it to SharePoint Server and let the decision makers to get the information themselves.  PowerPivot for SharePoint provides managed, secured, self-service environment.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Microsoft PowerPivot for Excel 2010 – Data Analysis Expressions (DAX)

Formula is the reason that we use Excel; some formula are simple, e.g. “=A1+B1”, other can be complicated, e.g. DDB(cost,salvage,life,period,factor). PowerPivot also has its own formula, Data Analysis Expressions (DAX).  DAX is built on top of Excel formula but it is designed to work with multiple tables of data. It also adds new functionality that allows you to create dynamic aggregations.

You will be glad to know that most DAX functions are the same as Excel functions.  However, DAX functions always refer to a column or a table, e.g. “=[TotalSales][TotalCost]”; where [TotalSales] and [TotalCost] are 2 columns in the same table.

The biggest enhancement is DAX provides time intelligence function, which lets you use selected ranges of time frames and compare results across parallel periods.

There are 2 main types of DAX functions:

Calculated columns

Calculated column is a formula that includes functions and values, e.g. “=[Price] * 0.7”.  It is similar to the formula in Excel.

Measures

Measures are used to create dynamic aggregations for use in PivotTables which are placed into the Values area of a PivotTable. It would be evaluated for each cell within the PivotTable.  Based on the column value and row value, each cell will be calculated differently

DAX measures can be defined implicitly and explicitly.

Implicit DAX Measures

When you drag a field from PowerPivot Field List to the Values section, PowerPivot would automatically create a measure for you, e.g Sum of TotalProfit.

image

  • Change the Field Setting by right click on the measure and select Edit Measure.

image

  • Change the Custom Name to Profit by Category or change how the field to be aggregated, e.g. Average.

image

Implicit Measure only allows you to select simple aggregation (SUM, COUNT, AVERAGE, MIN, or MAX).  If you want anything advanced, then you must create a measure explicitly.

Explicit DAX Measures

To create a DAX measure,

  • Click anywhere on the PivotTable.
  • Select the New Measure on the PowerPivot ribbon.

image

  • Define the Measure Settings.

image

  • Click the fx button to bring up the Insert Function dialogue where you can select what formula to be used in the measure.

image

  • You can refer to other measures that are already established, e.g. “= CALCULATE ([Profit by Category ], DimChannel [ChannelName]=”Store”)”
  • The second portion of the formula, “DimChannel [ChannelName]=”Store”)”, is known as filter in PowerPivot which would constraint how this measure to be evaluated in the PivotTable,

Time Intelligence Functions

No matter if you are doing financial, sales, or other performance analysis, time is always a critical dimension.  You may want to compare year-to-date, same period last year, or year over year growth.  PowerPivot is much more powerful than Excel in term of time series analysis.  DAX provides a comprehensive list of time intelligence functions.  Below is a short list of what you can use:

  • FIRSTDATE (Date_Column)
  • LASTDATE (Date_Column)
  • FIRSTNONBLANK (Date_Column, Expression)
  • LASTNONBLANK (Date_Column, Expression)
  • STARTOFMONTH (Date_Column)
  • STARTOFQUARTER (Date_Column)
  • STARTOFYEAR (Date_Column [,YE_Date])
  • ENDOFMONTH (Date_Column)
  • ENDOFQUARTER (Date_Column)
  • ENDOFYEAR (Date_Column [,YE_Date])
  • PREVIOUSDAY (Date_Column)
  • PREVIOUSMONTH (Date_Column)
  • PREVIOUSQUARTER (Date_Column)
  • PREVIOUSYEAR (Date_Column [,YE_Date])
  • NEXTDAY (Date_Column)
  • NEXTMONTH (Date_Column)
  • NEXTQUARTER (Date_Column)
  • NEXTYEAR (Date_Column [,YE_Date])
  • DATESMTD (Date_Column)
  • DATESQTD (Date_Column)
  • DATESYTD (Date_Column [,YE_Date])
  • SAMEPERIODLASTYEAR (Date_Column)
  • TOTALMTD (Expression, Date_Column [, SetFilter])
  • TOTALQTD (Expression, Date_Column [, SetFilter])
  • TOTALYTD (Expression, Date_Column [, SetFilter] [,YE_Date])
  • OPENINGBALANCEMONTH (Expression, Date_Column [,SetFilter])
  • OPENINGBALANCEQUARTER Quarter (Expression, Date_Column [,SetFilter])
  • OPENINGBALANCEYEAR (Expression, Date_Column [,SetFilter] [,YE_Date])
  • CLOSINGBALANCEMONTH (Expression, Date_Column [,SetFilter])
  • CLOSINGBALANCEQUARTER (Expression, Date_Column [,SetFilter])
  • CLOSINGBALANCEYEAR (Expression, Date_Column [,SetFilter] [,YE_Date])

The time intelligence functions also allow fiscal years having a yearend date other than Dec 31.

I hope you enjoy my blogs on PowerPivot but I cannot tell you more than 1% of what PowerPivot can do.  You must experience it yourself to truly appreciate its full potential.  Download Excel 2010, PowerPivot and sample data now.  Once you are able to trial it for couple hour, you would agree with me that PowerPivot is the biggest excitement of Excel 2010.

 

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Microsoft PowerPivot for Excel 2010 – Adding Slicer

I have used Excel in my everyday work since 1993 when Excel 5.0 was released.  Microsoft added new features to every version of Excel; however, I never felt so excited about a new release until Excel 2010.  Excel 2010 has many enhancements.  Some of them are behind the scenes; e.g. 64 bit edition which waive the 2GB file size limit; multithreading helps speed up data retrieval, sorting, and filtering in PivotTables.   Some are visible and very powerful data visualization tools; e.g. conditional formatting, sparkline, and slicer.  In this blog, I am going to discuss slicer, how it can be used to slice-and-dice the PivotTable.

Add Slicers to a PivotTable

This time we work on the Profit by Category PivotTable,

  • Click anywhere on the Profit by Category PivotTable.
  • Click Options in the PivotTable Tools area.
  • Click Insert Slicer.

image

  • In the Insert Slicers window, locate the Stores table and select RegionCountryName.

image

  • Select ChannelName under the DimChannel table.
  • Select ProductCategoryName under the ProductCategory table.
  • Select ProductSubcategoryName under the ProductSubcategory table.
  • Click OK.
  • 4 Slicers are added to the worksheet.

image

Format Slicers

  • Click the grey border and drag the Slicers so that all of them can be seen.

image

Rename Slicer
  • Right-click the ProductSubcategoryName Slicer and select Slicer Settings.
  • In the Caption box, type Subcategory.

image

  • Click OK.

image

Add Slicer Columns to see more content
  • Select the RegionCountryName Slicer.
  • Select Size and Properties.
  • Select Position and Layout.
  • Enter 4 in the Number of columns dropdown.

image

  • Click Close.
  • Resize the Slicer until all content is visible.

image

Use Slicers to Analyze your PivotTable

I can produce the profit for Boxed Games sold Online in China with just a few mouse clicks.

image

You want to see the profit for Audio products through Reseller in US, please give me 2 seconds.

image

Let’s look at how Slicers can work with the Profit % by Category PivotChart.

Add Slicers to a PivotChart

  • Click anywhere on the Profit % by Category PivotChart.
  • Click Analyze in the PivotTable Tools area.
  • Click Insert Slicer.
  • In Insert Slicers window, select CalendarYear and CalendarMonth under the DimDate table.
  • Select RegionCountryName under the Stores table.
  • Click OK. You can format RegionCountryName Slicer the same way that we just did for the PivotTable and format other Slicers as needed.Use Slicers to Analyze your PivotChartWhat do you notice when you compare the following 2 diagrams, the Profit % by Category for Canada in 2007 and 2009?image

    image

    Can you imagine how these new features, PowerPivot, and Slicer can improve the turnaround time of report generation?  I can and this is why I am so excited about Excel 2010!

     

  • Andrew Chan is the owner and founder of ALG Inc.

    We help you to make better and faster decisions!

    Microsoft PowerPivot for Excel 2010 – Adding Pivot Chart

    I discussed how to add a Pivot Table in my previous blog, and now I am going to show you how to add its twin brother, Pivot Chart.

    • Start on the worksheet that contains your Sales by Channel PivotTable.
    • On the PowerPivot tab in Excel, click PowerPivot Window.
    • On the PowerPivot Home ribbon, click the arrow below PivotTable and select PivotChart.

    image

    • Select Existing Worksheet and click OK.

    image

    • Select the empty PivotChart.

    image

    • In the FactSales table, select the TotalSales field.
    • In the DimChannel table, select the ChannelName field.
    • In the DimDate table, select CalendarQuarter.  Move SumofCalendarQuarter to the Legend Fields box.

    image

    • The following PivotChart would be generated.

    image

    • Right-click on the numbers and select Format Axis. Click Axis Options and in the Display units dropdown, select Millions and then click Close.

    image

    If you were able to create the Profit by Category Pivot Table that I suggested in my previous blog, then try to build a similar Profit by Category  Pivot Chart.  Have fun!

    image

     

    Andrew Chan is the owner and founder of ALG Inc.

    We help you to make better and faster decisions!

    Microsoft PowerPivot for Excel 2010 – Adding Pivot Table

    Who hasn’t used Pivot Table before?  Everyone uses it in Excel; it is very powerful tool to extract meaningful information from data.  When we do data analysis in PowerPivot, we are going to use the same tools.  PowerPivot allows you to handle more data and provides extra analytical functions, i.e. Data Analysis Expressions (DAX).

    Add a PivotTable

    • In the PowerPivot window, on the PowerPivot Home ribbon, click PivotTable.   There is also a PivotTable button on the Excel ribbon but Excel PivotTables cannot access your data in PowerPivot, so make sure you are creating PivotTable in PowerPivot window.

    image

    • Select PivotTable.

    image

    • Select New Worksheet.
    • Excel adds an empty PivotTable to the location you specified.

    image

    • You would also see the PowerPivot Field List.  It contains two sections: a field section at the top for adding and removing fields, and a section at the bottom for rearranging and repositioning fields.

    image

    • Select the empty PivotTable.
    • In the PowerPivot Field List, scroll down and locate the FactSales table.
    • Select the TotalSales field.
    • In the DimChannel table, select the ChannelName field.
    • In the DimDate table, select the CalendarQuarter field.
    • In the PivotTable Field List, move this field from the Values box into the Column Labels box.
    • The bottom section of PowerPivot Field List should look like the following picture. 

    image

    • Rename the PivotTable by double-clicking Sum of TotalSales in the first cell, and typing Sales by Channel.

    image

    • Highlight the data cells in the PivotTable, right-click and select Format Cells.
    • In the Format Cells window, select Number and enter 0 in the Decimal places dropdown. Select the checkbox for Use 1000 Separator.
    • Click OK.

    image

    • Here is the new format.

    image

    • You can choose to only show the first quarter of every year in your analysis by removing the arrow of other quarters.

    image

    • Or you may want to just look at the Online Sales.

    image

    This is nothing new so far; apart from the fact that you are working on 4 million records.  Do you think you can build the following Profit by Category Pivot Table on your own?

    image

     

    Andrew Chan is the owner and founder of ALG Inc.

    We help you to make better and faster decisions!

    Microsoft PowerPivot for Excel 2010 – Creating Relationships Among Data

    Now we have imported data into PowerPivot, we can create relationships between tables.  A relationship establishes how the data from different sources should be correlated. You can create relationships between data sources by joining columns that contain same data.

    You can manually create relationships or let PowerPivot to automatically detects existing relationships when importing data into a PowerPivot workbook.

    This demonstration continues from the previous blog; Microsoft PowerPivot for Excel 2010 – Importing Data.  So if you haven’t followed the last blog, you should go back and read it now.  You can downloaded sample data from Codeplex Web site.

    Why Create Relationships?

    We have used VLookup to link data from different tables together so that we can do our in depth analysis.  You can consider relationship is another form of VLookUp.

    Relationships allow you to:

    • Filter data in one table by selecting data from related tables.
    • Integrate data from multiple tables into a PivotTable or PivotChart.
    • Easily look up values in related tables using Data Analysis Expressions (DAX) formulas.

    Existing Data

    You already have data from three different sources in your PowerPivot workbook:

    • Sales and product data.
    • Product category data.
    • Store information. 

    Create New Relationships between the Stores and Entities

    • Click the Stores tab.
    • Right-click the EntityId column header and select Create Relationship.

    image

    • The Table field and the Column field are auto-populated in he Create Relationship dialogue.

    image

    • In the Related Lookup Table field, select dbo_DimEntity.
    • In the Related Lookup Column field, select EntityKey.

    image

    • Click Create.
    • You would find an icon displays at the top of the column which indicates the existence of a relationship.

    image

    Create New Relationships between the Stores and Sales

    • Click the dbo_FactSales tab.
    • Select the StoreId column.
    • On the Design tab, click Create Relationship.
    • The Table field and the Column field are auto-populated.
    • In the Related Lookup Table field, select Stores.
    • In the Related Lookup Column field, select StoreKey.
    • Click Create.

    Create New Relationships between the Products and Sales

    • Click the dbo_FactSales tab.
    • Right-click the ProductKey column header and select Create Relationship.
    • In the Related Lookup Table drop down, select dbo_DimProduct.
    • In the Related Lookup Column drop down, select ProductKey.
    • Click Create.

    Repeat the steps for the following relationships.

    Foreign Table Foreign Column Primary Table Primary Key
    dbo_FactSales DateKey dbo_DimDate Datekey
    dbo_FactSales ChannelKey DimChannel ChannelKey
    dbo_DimProduct ProductSubcategoryKey Dbo_DimProductSubcategory ProductSubcategoryKey
    dbo_DimProductSubcategory ProductCategoryKey ProductCategory ProductCategoryKey
    • Click Manage Relationships on the Design tab.

    image

    • You can review all the relationships that have been created.

    image

    As long as you understand the data and the relationships among them, you don’t need an IT administrator to build the relationships.  PowerPivot is a DIY tool!

     

    Andrew Chan is the owner and founder of ALG Inc.

    We help you to make better and faster decisions!

    Microsoft PowerPivot for Excel 2010 – Importing Data

    PowerPivot can import data from database (Access, Oracle, Sybase, Informix, DB2, Teradata and more), Excel, OLAP Cube, report, text file, and data feed.  In this blog, you would find step by step demonstration on how to import data from an Access database and Excel worksheet into your PowerPivot.  You can download the sample data if you want to follow the steps.

    Importing Data From an Access Database

    image

    • In the PowerPivot window, click From Database and select From Access. This launches the Table Import Wizard.

    image

    • In the Friendly connection name field, type Contoso Sales.
    • In the Database name field, enter the location of your sample Access data file, ContosoSales.
    • Click Next and verify that Select from a list of tables and views to choose the data to import is selected.

    image

    • Click Next and  select all tables.

    image

    You don’t have to import all data, you can choose what columns and rows to be imported.

    • Select the row for the DimProduct table and click Preview & Filter.
    • The Preview Selected Table window opens with all the columns in the dbo_DimProduct table displayed.
    • Clear the checkboxes at the top of the columns for all the columns from ClassID through StockTypeName (a total of 15 columns) and then click OK.

    image

    • Notice that the words Applied filters are now displayed in the Filter Details column in the dbo_DimProduct row.

    image

    • Now select the row for dbo_DimProductSubcategory and click Preview & Filter.
    • At the top of the ProductCategoryKey column, click the arrow and deselect 7 and 8.

    image

    • At the top of the ProductSubcategoryDescription column, clear the checkbox.

    image

    • Click OK.
    • Click Finish.

    image

    At the end of import process, you can see 6 tables were imported and dbo_FactSales has nearly 4 million records.

    • Click Close, and you will see all 6 tables in your PowerPivot Windows.

    image

    Copy and Paste from an External Excel Worksheet

    • Open ContosoStoreData Excel file from your sample directory.
    • Highlight and copy cells A1 through I307.

    image

    • Back in your PowerPivot window, on the Home tab, click Paste.

    image

    • The Paste Preview dialog displays the new table that will be created. Change the Table Name to Stores, select Use first row as column headers, and click OK.

    image

    • In the Excel window, click the File tab.
    • Click Save As. .
    • The Save As dialog box opens.  In the File name text box, type PowerPivotTutorialSample and then click Save.

    Now data is imported into your PowerPivot worksheet and you are ready to define the relationships among data which I am going to cover in my next blog.

     

    Andrew Chan is the owner and founder of ALG Inc.

    We help you to make better and faster decisions!

    Why Microsoft PowerPivot?

    Microsoft Excel already has a lot of powerful features e.g. Pivot Table / Pivot Chart, Analysis ToolPak, Solver; power users can even developed their own add-in. Why do we still need PowerPivot?  Why is Microsoft so excited about PowerPivot?  What can PowerPivot deliver?

    Before I answer the question, let me suggest who PowerPivot is for. PowerPivot is for advanced Excel users who need to transform enormous amount of data into meaningful information instantly.  It allows you to consolidate data from finance, human resources, operations and sales departments.  You can easily handle 10 years expenses analysis (how many records we are talking about?) and drill down to investigate different expense categories, territories or even compare the income from different industries.

    Here a few things that make PowerPivot shine:

    • Row-and-column limitation in Excel – You can have more than 1 million rows of data.  If you are using 64 bit Excel and have double digits gigabytes memory, you can have tens or hundreds of millions of rows.
    • Lighting fast – It handles tens or hundreds of millions of rows around the same time as Excel handles tens or hundreds of thousands of row.  It uses the new In-Memory BI (IMBI) database and a great compression algorithm in order to significantly reduce the size of the overall data stored.
    • Data from multiple sources – You can load data from databases (Access, SQL Server, Oracle, Terradata, DB2 and Sybase), Excel, text file, OLAP Cube, reports and even data feed; virtually anything that you can think of.
    • Data Relationship – PowerPivot provides a data relationship layer that lets you integrate data from different sources so you can analyze all you data as it is from a single source, so no more VLookup!
    • Powerful new analytical capabilitiesData Analysis Expressions (DAX) is designed to work with multiple tables of data.  DAX also adds new functionality that allows you to create dynamic aggregations, making DAX formulas smart about calculating values in a PivotTable.
    • Time series calculations in pivots – there are about 40 DAX functions to handle time; e.g. Year Over Year Growth, Previous Period…etc.

    I strongly encourage you to download and try both Microsoft Office 2010 and PowerPivot.  You would be as excited as me; especially the first 2 features.  See is believing!  Try to import over 10 million records into PowerPivot and you would not believe what you see.  I even repeated the same test on my 5 years old laptop which only has single core and 2GB of memory.  The result is quite encouraging; i.e. you don’t need quad core / 8GB machine to run PowerPivot.

    Have fun with PowerPivot and my next blog will be discussing how you can import data into PowerPivot.

    Andrew Chan is the owner and founder of ALG Inc.

    We help you to make better and faster decisions!