Importing Data into Power PivotOne of the first steps in creating the Power Pivot model is importing data.one of the greatest strengths of the Power Pivot model. You can easily and quickly combine data from a variety of sources into your model. The data sources can be from relational databases, text files, web services, and OLAP cubes.
The connection information depends on the data source you are connecting to. For most relational databases the information needed is very similar.
In the previous lesson , we know how to open the Power Pivot window & how to find out the various data sources available to connect to.
Note: We will use MS SQL Server as a data sources and TSQL2012 Database.
You Can download the database script from this link:Database link.
Connecting to a SQL Server.Click Power Pivot.This is the tab where you work with Power Pivot.
The Power Pivot window will open.
On the Get External Data > choose From Database > From SQL ServerRemember to click the Test Connection button to make sure everything is entered correctly.
You can choose to import the data from a list of tables and views or you can write a query to import the data.
Even if you select to import the data from a table or view under the covers, a query is created and
sent to the database to retrieve the data.
By selecting a table and clicking the Preview & Filter button , you can preview the data in the table and filter the data selected.
If you switch to the diagram view of the Model Designer you will see the tables and you can modify the relationships between the tables.