Hitachi Vantara Pentaho Community Wiki
Child pages
  • 02. Importing Physical Tables and Columns
Skip to end of metadata
Go to start of metadata

Now that we have a few database connections (see Setting Up Your Database Connections), the next step is to describe the physical database tables and columns that we will want to include in our business model.

Importing Physical Tables

Fortunately, when you import a physical table, all of the table's columns come with it, so the import is a one step exercise instead of two. You can later remove those columns that you do not want in the connection or the model.

An Example Import: Importing Tables into a MySQL Connection

  1. Right-click (or <CTRl+click>) on the MySQLSampleData node in the Tree Navigator. Select the Import Tables... option from the pop-up menu.
  2. You will be prompted by a dialog with list of physical tables available in the database. Select the tables you want to include, and click OK.
    Drill down on your physical tables in the Tree Navigator. Notice that all of the physical columns have been imported with each table.

Trimming the Physical Columns

If you would like to remove extraneous columns from your physical tables, from here it's easy to do:

  1. Right-click (or <CTRl+click>) on the physical table node you wish to edit in the Tree Navigator. Select the Edit option from the popup menu. (Note: you can also get to the Physical Table Properties dialog by double-clicking the physical table node.)
  2. The Physical Table Properties dialog displays. In the dialog's Tree Navigator, select a column you wish to remove.
  3. Click the delete icon (the one with the red circle), to the right of the word Subject above the dialog's Tree Navigator.
  4. Repeat with any remaining columns that you want to remove. Click OK when you are done.

Defining the Physical Column Aggregations

Note: This functionality will not be available until the CITRUS release (date pending).

You are no longer limited to defining only one aggregation rule in a model. You can define multiple aggregation rules from which users can choose when they are working in the Chart Designer or other tool.

Note: This feature will not be supported for users of Ad Hoc Reporting.

In the example below, the model designer has designated three optional aggregations for PRICEEACH: Sum, Average, and Count. In the Chart Designer, (or other tool), users can decide whether they want to see the total price, average price, or the number of prices available. Metatada Editor displays a default aggregation type for a numeric column. You can choose to designate additional aggregations, when applicable.

To define aggregations in Metadata Editor...

  1. Select the column for which an aggregation must be defined.
  2. Under Model Descriptors, select Default Aggregation.
  3. Under Aggregation rule, select an Aggregation Type from the list.
  4. If applicable, select multiple aggregation types from the Optional Aggregations. Use <CTRL+click> to make your selections.

Note: There is no need to explicitly place the aggregation type in the formula definition; Metadata Editor uses your selections to generate the SQL query. See Second Use: Physical Table Column Formulas and Support for Multiple Aggregate Types for more information.

  • No labels

1 Comment

  1. Please share the steps, if we can use DB Views along with physical tables...