Using the Microsoft Excel plugin for the Altair SLC Hub

IanBD
IanBD
Altair Employee
edited April 15 in Altair RapidMiner

The Microsoft Excel plug-in for Altair SLC Hub enables you to connect an instance of Microsoft Excel to a Hub installation. Published programs from that Altair SLC Hub can then be run from Excel, with input passed to the program from Excel and output from the program displayed in Excel.

The Excel plug-in installation application can be downloaded from the Downloads page of the World Programming website (https://myaccount.worldprogramming.com). Once downloaded, install as follows:

  1. Log in as administrator to the computer on to which you want to install the Excel plugin.
  2. Run the downloaded MSI file to start the installer.
  3. Read the license agreement, and if you accept, select I accept the terms in the License Agreement.
  4. Click Install.

When the plug-in is installed, a new Hub tab is available on the Excel ribbon. The functionality in the tab can be used for:

Connecting Microsoft Excel to Altair SLC Hub

Excel can be connected to an Altair SLC Hub installation, enabling programs in that Altair SLC Hub installation's Directory to be run from Excel. When Excel is closed, the connection to Hub will be remembered for the next time Excel is opened.

To connect Excel to Altair SLC Hub:

  1. Open Excel, either with an existing or a blank workbook.
  2. In the Hub Connection group, click Connect.
  3. In Hub Connection, enter:
    1. Hostname: Type the name of the server hosting Altair SLC Hub
    2. Port Number: Type the port number of Hub.
    3. Protocol: Select HTTP or HTTPS.
    4. Authentication: Enter your Altair SLC Hub user name and password in the User Name and Password fields.

    If you are unsure of any of the above values, contact your Hub Administrator.

  4. Click Connect.

Disconnecting Microsoft Excel from Altair SLC Hub

To disconnect Excel from Altair SLC Hub:

  1. Open Excel, either with an existing or a blank workbook.
  2. Click the Hub tab.
  3. In the Hub Connection group, click Disconnect.
    Excel is now disconnected from Altair SLC Hub.

Executing Altair SLC Hub programs from Excel

Once the Altair SLC Hub Excel plug-in has been installed, deployed Altair SLC Hub programs can be run from Excel, with input passed to the program from Excel and output from the program displayed in Excel. A wizard enables you to select the program to run, the input parameters and the destination for the output. Once you've configured the parameters, you can save them for running again.

Run a program from Excel

To run a deployed Altair SLC Hub program from Excel:

  1. Click the Hub tab.
  2. In the OnDemand Functions group, click Insert Function.
  3. Select a program to run from the list, using the following features to help you locate the program:
    • All: A tab listing all programs.
    • Saved: Choose from a list of previously saved customised program executions.
    • Recent: Choose from a list of previously run customised program executions.
  4. Click Next.
  5. Enter values for the program's input parameters in one of two ways:
    • Enter values directly in the boxes next to each variable name.
    • Click Select Value (image) and choose a cell from the worksheet to use the value of that cell.
  6. Choose the location in the current Excel Workbook that the results will be written out to:
    • Existing worksheet: Write the results of running a program to the existing worksheet, at a location that you specify. Type a cell reference in the box, or click Select Value (image) and choose a cell in the worksheet.
  7. Click Run.

    The program's output will be inserted into MS Excel at the requested location.

Saving previous program invocations

Once you have run a program for Excel, you can save that invocation. Before carrying out this procedure, you must have executed a program with the settings that you want to save.

To save a previously invoked program:

  1. Click the Hub tab.
  2. In the OnDemand Functions group, click Insert Function.
  3. Select the Recent tab.

    A list of previous program invocations is displayed, showing: program names, program paths and the dates the programs were run.

  4. Locate the invocation that you want to save.
  5. Right click on the invocation and click Save As.
  6. Type a name for the invocation. If the name has been used before, warning is displayed.

    The program invocation is saved and appears in the Saved tab.

  7. Click Cancel to return to Excel.

Importing datasets

Published libraries can be imported as Datasets to Excel, either as a flat table to a Workbook sheet or as a pivot table. By default, all observations (rows) and variables (columns) in the dataset are imported. The dataset can be configured on import to create the required dataset contents, for example:

  • Variables in the dataset can be included or excluded.
  • Observations (rows) can be included or excluded by creating a filter using one or more variables (columns) in the row.
  • The maximum number of observations imported into the workbook sheet can be specified.

To import a dataset

  1. Open an existing or a blank workbook.
  2. Click on the Hub tab.
  3. In the Dataset Operations group, click Insert Dataset
    The Insert Dataset dialog box is displayed.
  4. Select the Namespace containing the required library definition.
  5. In the Libraries list, select the library containing the required dataset.
  6. In the Datasets list, select the required dataset.
  7. Click Next.
  8. If required, the dataset content can be configured using the tabs in the dialog box:

    To view the effects of the dataset configuration, click Preview.

  9. To import the dataset into the worksheet click Insert.

Modifying a dataset after import.

A dataset can be modified after import by using the Update Dataset dialog box. To open the dialog box:

  1. Open workbook containing a dataset imported from Altair SLC Hub.
  2. Click on the Hub tab.
  3. In the Dataset Operations group, click Configure Dataset
    The Update Dataset dialog box is displayed.
  4. The dataset content can be configured using the tabs in the dialog box:
  5. To view the effects of the dataset configuration, click Preview.
  6. Click Update to apply the changes to the imported dataset.

Excluding variables from a dataset

Variables can be excluded from a dataset during import using the Import datasets dialog box. Variables can also be excluded from the dataset after import using the Update dataset dialog box. The Fields tab of the dialog box displays all available fields that can be imported from the dataset referenced in Altair SLC Hub:

  • To exclude a dataset variable, in the Fields panel, clear Include in the row for the variable.
  • To include a previously-excluded dataset variable, in the Fields panel, select Include in the row for the variable

The Fields panel can also be used to sort the observations in a dataset. To apply a sort order, in the required variable select the Sorting type, either ascending or descending. If multiple variables are used, the sort order priority can specified in the adjacent column.

Filtering observations in a dataset

Observations (rows) can be excluded from a dataset during import using the Import datasets dialog box. Rows of data can also be excluded from the dataset after import using the Update dataset dialog box. The Filter tab of the dialog box can be used to select the rows of data that are imported from the dataset referenced in Altair SLC Hub.

The Expression builder enables you to specify an expression that selects the observations to import in the dataset:

  1. In the Expression Builder panel, select the required Field to use as a filter.
  2. Select a Condition to apply to the content of the field.
  3. In the Values field, click ... to specify the content value tested in the expression.
    Any rows for which the expression is true are included in the imported dataset.
  4. The expression can be extended by selecting an Operator:
    • Select AND to create a further expression that must also be evaluated to true for row of data to be included in the imported dataset.
    • Select OR to create an alternative expression. If either expression specified evaluates to true for a row of data, that row is included in the imported dataset.
  5. To view the effects of the dataset configuration, click Preview.

Specifying the number of rows and worksheet

The number of visible observations (rows) to display from a dataset, the location and the type of dataset can be specified during import using the Import datasets dialog box. Once imported, the visible number of rows of the data imported data can be modified using the Update dataset dialog box, but the dataset type and location cannot be changed.

To specify the number of rows of data imported:

  1. In the Rows/Table tab, enter a value in the Number of Rows field to specify a block size for the  number of visible rows of data from the imported from the dataset..
    The imported data is divided into blocks of rows, with one block visible at a time. The Previous page and Next page buttons can be used to navigate through the imported rows of data one block at a time.
  2. Alternatively, select All records to import all rows of data from the dataset referenced in Hub.

During import, the location of the imported dataset can be specified:

  • To import the dataset into a new, empty worksheet select New Worksheet.
  • To import the dataset into a worksheet already in the workbook select Existing Worksheet and enter a Location for the first column and first row of the table. By default the location is the currently selected worksheet cell. To change location click Select value (image) and choose a worksheet and cell.

The table type can be specified during import:

  • Select Data Source Tablestrong> to import the dataset only.
  • Select Pivot Table to import the dataset as a data source for Excel Pivot Table functionality.

Re-Running a program in a worksheet

Once an Excel Worksheet has been populated by running an Altair SLC Hub program, it can be re-run to refresh its contents, either manually or automatically each time it is opened.

Re-running a worksheet manually

Worksheets can be re-run manually at any time.

To re-run a worksheet:

  1. Open Excel, either with an existing or a blank workbook.
  2. Click Hub tab.
  3. In the Refresh group, click Refresh All and then click Re-Run Sheet.

    The Altair SLC Hub program associated with the worksheet is re-run, and the worksheet populated with the new output data.

Re-running a worksheet automatically on opening

Worksheets can be configured to re-run automatically each time they're opened.

To set a worksheet to re-run automatically each time it is opened:

  1. Click the Hub tab.
  2. In the Refresh group, select Re-Run on Open.
  3. Save the Workbook.

When the workbook is opened in the future, the program associated with it will be re-run.

Tagged:

Categories