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