This is the second part of my series, dedicated to a brief and clear introduction to PowerPivot for Excel. The idea of the series is to help any user to start using the product without reading tons of articles and documentation and at the same time to be introduced to the full range of possibilities of that tool.
Today I will write on the matter of importing data into PowerPivot and will show how to import data from plain text files, create relationships between imported tables in PowerPivot and browsing through the other possible sources of data that PowerPivot supports.
So let’s begin. Last time I showed how to navigate to PowerPivot Window.In order to import data from file (Text or Excel), you go to the drop down From Files and choose From Text (for our example).
Then as usual when importing Text file you choose the file, the separator and if the first column in the file should be used for defining column headers. The different part here is, that you can filter the data you are importing. For example if I want to import only Velo French Product CategoryName, I just click on the filter drop down and uncheck all unwanted values. This is very powerful when it comes to dealing with only a part of a source data in which you are interested in.
After that the import process is carried out and you have a new table imported into your PowerPivot solution.
This is as it comes to importing data. But when you import several tables and want to prepare a report on them, there is something that you should always take care of – relationships. If you do not define them your data will not have desired integrity and of course won’t have a correct data in the report. In the next few lines I will explain and show how to create relationships between tables.
So far I imported two tables – FactInternetSales and DimProductCategory (both part of Adventure Works datawarehouse database).
To create a relationship between both tables, you go to Table ribbon (right next to Home) and then – Create Relationship button.
For those of you that have basic knowledge on relational databases and database design, creating a relationship in creating a foreign key constraint on a table. In PowerPivot first you point out the Table and the field that is common between both tables. First you select the master table and then – related (lookup) one. Be careful for the datatype of the columns you are referencing, because if they are not the same an error will be raised and the relationship creation will fail.
Now I want to talk about what kind of datasources you can use to import data to PowerPivot. Basically you can import from anything – starting form MS SQL and ending with Sybase, Teradata and Informix. You can also import from Oracle, IBM DB2 and most interesting for me (and the ones that will make the difference for PowerPivot) – importing form SQL Azure and RSS Feeds (including SSRS Report Feed). I will dedicate a special article to importing data from RSS Feed of a SSRS report so please stay tuned.