PowerPivot for Excel allows you to import, filter, sort and
build relationships between data from different data sources.
Particularly, you can create a PowerPivot connection to the Ops Mgr
Data Warehouse and extract Veeam objects for performance data
analysis and reporting purposes:
Note
|
The procedure below
applies to Microsoft Excel 2013. Similar actions can be performed
in Microsoft Excel 2010.
|
2.
|
Launch Microsoft
Excel, open the PowerPivot tab
and click Manage. |
3.
|
In the
PowerPivot for
Excel window, switch to the
Home
tab and click From Database > From SQL Server to open the Table Import Wizard. |
4.
|
At the
Connect to a
Microsoft SQL Server Database step,
select the server which hosts Ops Mgr Data Warehouse from
the Server
name drop-down list, specify
credentials used to connect to the server and choose a database to
collect data from. |
5.
|
At the
Connect to a
Microsoft SQL Server Database step,
select the Write a query that will specify the data to
import checkbox. |
6.
|
At the
Specify a SQL
Query step, type the name of the
PowerPivot table in the Friendly Query Name field. Then type a SQL query in the SQL Statement
field. |
Some
examples of SQL statements are described in section Veeam VMware
PowerPivot Example.
7.
|
Click
Finish
to save the query. |
8.
|
Once you have
created all the queries you need, establish relationships between
the tables:
|
a.
|
In the
PowerPivot for
Excel window, switch to the
Home
tab and click Diagram View. |
b.
|
Right-click the
necessary column header and click Create Relationship. |
c.
|
In the
Create
Relationship window, select the table
and the row in this table with which you want to create a
relationship. |
Alternatively, simply drag and drop the
necessary columns to the tables you want to create relationships
with.
|