Watch the video demonstration to add data to Power Pivot.
PowerPivot can import data from multiple source like Access, SQL Server, Oracle, Sybase Informix, DB2, Teradata, Flat file, Excel and OData feed. The process to import data table is similar. Following are the high level steps.
- Create a data connection using connection wizard. We need one data connection per each data source. For example if we need to import 2 tables from SQL Server and 3 tables from Oracle database, we need 2 data connection; 1 for each data source. However, we need 1 connection for each data feed table. In case of data feed, 1 connection = 1 table.
- Select the tables to be imported; provide friendly name (that can be changed later on); apply filters if required for each table that needs to be imported.
- Click OK to complete the Wizard.
Once all the required tables are imported, relationship between tables needs to be defined.
A Relationship is a connection between two tables that needs be correlated. For example Sales information table is correlated to Sales Customer table through SalesID clicca ora. Once the relationship is created, we will be able to filter the data by using related columns, lookup values in related tables, and derive values using formulas like SUM, Count and other complex output (will be discussed in advanced topics.)
There are different types of relationships. Power Pivot does not support many-to-many relationship.
- One-to-many: For example – 1 customer can have many orders
- One-to-one: For example – 1 customer has 1 address (not always, depend on requirement)
- Many-to-many: For example – 1 customer can buy many products, and same product can be purchased by many customer.
- Single relationship between tables: If you have two tables and multiple relationship, then you need to import multiple copies of the table that contains lookup column.
- One relationship for each source column: A source column cannot participate in multiple relationship. If you have used a column as a source column in one relationship already, but want to use that column to connect to another related lookup column in a different table, create the copy of column using DAX formula.
- Unique Identifier for each table: This is like primary key to uniquely identify each row in table.
- Compatible data types: Related columns in source and lookup column should have same data type.
- Composite keys is not supported. If required combine the multiple columns to create a single column. This can be done before import or after import using DAX
- Many to Many relationship, Self Joins and Loops are not supported/permitted
Power Pivot automatically establish inferred relationship. For example if relationship between A -> B and B -> C is created manually, then Power Pivot automatically infer relationship A-> C.