Database Reference
In-Depth Information
3.
Provide the following information to connect to your database:
Friendly Connection Name:
The Friendly Connection Name field allows you to specify your
own name for the external source. Enter a name that is descriptive and easy to read.
●
Server Name:
This is the name of the server that contains the database you're trying to
connect to. You get this from your IT department when they give you access.
●
Log On to the Server:
These are your login credentials. Depending on how your IT depart-
ment gives you access, you select either the Use Windows Authentication or Use SQL
Server Authentication radio button. Use Windows Authentication means that the server
will recognize you by your Windows login. Use SQL Server Authentication means that the
IT department created a distinct username and password for you. If you're using Use SQL
Server Authentication, you need to provide a username and password.
●
Save My Password:
Select the Save My Password check box if you want your username
and password to be stored in the workbook. This allows your connections to remain refre-
shable when being used by other people. There are obviously security issues with this
option, as anyone can view the connection properties and see your username and password.
You should only use this option if your IT department set you up with an application
account; that is, an account created specifically to be used by multiple people.
●
Database Name:
Every SQL Server can contain multiple databases. Enter the name of the
database you are connecting to. You get this from your IT department when they give
you access.
●
4.
After you enter all the pertinent information, click Next.
The next screen of the wizard (see Figure 11-16) gives you the choice of selecting from a list
of tables and views, or writing your own custom query using SQL syntax.
5.
Select the Write a Query That Will Specify the Data to Import radio button and click Next.
6.
Enter a valid SQL statement into the SQL Statement box (see Figure 11-17).
Instead of writing a simple
Select
statement, you can take advantage of an existing stored
procedure. In this example, we're calling the
SP_GetEmailList
stored procedure.
You can also enter a friendly name for your custom query. This name becomes the iden-
tifier within Power Pivot for the imported data.
Tip
7.
Click Finish to start the import process.
Once processing is complete, you'll see the results of your stored procedure on a new tab in the
Power Pivot window. Like all other connections in Power Pivot, your custom query can be refreshed,
effectively triggering the stored procedure to run again and return updated results.
Search WWH ::
Custom Search