Importing Data into Power Pivot
One 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.
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.Click Manage.
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.
After setting up the connection the next step is to query the database to retrieve the data.
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.
By selecting a column and clicking the Format button , you can modify the formatting of data to the according to your business needs.
When working with large data sets it is a good idea, for performance reasons, to only import the data you are interested in. There is a lot of overhead in bringing in all the columns of a table if you are only interested in a few.Likewise, if you are only interested in the last three years of sales, don’t bring in the entire 20 years of sales data.
Post a Comment