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:
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 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.
- At the Connect to a Microsoft SQL Server Database step, select the Write a query that will specify the data to import checkbox.
- 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, simply drag and drop the necessary columns to the tables you want to create relationships with.