In Excel 2007 or later, EPM creates a tab on the Ribbon called EPM. With this tab, users can create connections with one or more EPM Servers, as well as perform several types of queries.
EPM Ribbon
In addition to this tab, if Excel's Add-Ins tab is not showing, the EPM tab is then displayed, containing a menu of EPM options related to the Automation version. If users are using Excel XP or 2003, this tab corresponds to EPM's menu of commands.
To use EPM Add-in for Microsoft Excel, users must have at least a connection to an available EPM Server. To do so, click Connection to open the configuration window shown on the next figure.
EPM Connection Manager window
The Available Connections list shows the available connections, and users can add new ones or remove them by clicking Add or Remove, respectively. Clicking Set as Default means that this is the default connection to use, if a connection is not informed when executing queries.
The EPM Server Connection group shows information about the selected connection, which define how a connection is established with a EPM Server. The next table contains a description of all options of this group.
Available options for connection
Option |
Description |
|---|---|
EPM Server Machine |
Name of an EPM Server for connecting and performing queries |
Connection Name |
Name of a connection used when executing queries. This name corresponds to a user-defined alias for a given connection |
Authentication |
Connection's authentication method. The available options are EpmServer authentication: Authentication using EPM or Windows authentication: Windows-integrated authentication |
User Name |
User for the connection |
Password |
User's password |
Remember Password |
Selecting this option allows keeping a user's password between Excel sessions, that is, users can open and close a file without informing their password again |
Test Connection |
Tests whether the connection is valid |
Once a connection is defined with an available EPM Server, users can perform queries to that server. The types of queries on the Ribbon are described on the next table.
Types of queries available
Type |
Description |
|---|---|
Dataset |
Retrieves data from the selected Dataset to Excel |
Aggregate |
Allows performing a query by informing the variables, the time interval, and the type of aggregation to apply to data |
Raw |
Allows performing a query for variables, retrieving all data stored in a certain period of time |
NOTE |
In an Sistema EPM, a Dataset is an object created in EPM Studio that stores information about variables, time intervals, sampling, and type of aggregation in a query. When requested using EPM Add-in for Microsoft Excel, this object retrieves data corresponding to the configuration and display it on a table format. |
These queries display their results in Excel's table format. Whenever the focus is on this queried data table, the Refresh option of this query and the previously executed query are enabled. When clicking Refresh, this query's data is updated and the table is automatically resized, if needed. When clicking the query, the configuration window opens again, showing the previously user-selected options.