Importing data from OpsMgr Data Warehouse

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 OpsMgr 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.

Make sure that all hardware and software prerequisites are met for the computer used to create PowerPivot workbooks.
Launch Microsoft Excel, open the PowerPivot tab and click Manage.
In the PowerPivot for Excel window, switch to the Home tab and click From Database > From SQL Server to open the Table Import Wizard.
At the Connect to a Microsoft SQL Server Database step, select the server which hosts OpsMgr 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.

At the Connect to a Microsoft SQL Server Database step, select the Write a query that will specify the data to import check box.
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.

Click Finish to save the query.
Once you have created all the queries you need, establish relationships between the tables:

In the PowerPivot for Excel window, switch to the Home tab and click Diagram View.
Right-click the necessary column header and click Create Relationship.
In the Create Relationship window, select the table and the row in this table with which you want to create a relationship.

Alternatively, drag and drop the necessary columns to the tables you want to create relationships with.