Skip to content

Dynamic Data Import into MetaMiner Database

This feature facilitates users to create or update their own custom tables in MetaMiner Database (MMDB).

It allows you to extend the MMTE capabilities for BI data analysis and reporting.

Although you can always add custom tables by using psql, pgAdmin, and other PostgreSQL tools, the feature described in this article does not require any special knowledge and is safe.

Custom tables are created/updated from source CSV (Comma Separated Value) or Microsoft Excel files you make in any office software.

When these tables are created and their data is uploaded, you can use them in your SQL scripts in Meta Query or alerts.

Source File Format

The following file formats are supported:

  • Comma Separated Value: plain text file format, extension .csv
  • Microsoft Excel Open XML Spreadsheet: extension .xlsx
  • Microsoft Excel version 97-2007: legacy format, extension .xls

Adding a Table

How to dynamically add your table to MMDB:

  1. Create a file with the data for your new table in one of the formats mentioned above.

    The filename will be used by MMTE for generating the table name.

    The value titles from the first (heading) row will be used for column names.

    Example: create a list of users as a CSV file named MyTestTable.csv:

    ID,Name,Seq,Value
1,Helen,349,"some text"
2,Clarice,943,"more value"
3,John,4111,"even more"

  2. Log in to the host where MMTE Server is running.

  3. Navigate to the following directory: <METAMINER INSTALLATION DIRECTORY>\server\dynamic_data_import

  4. Put your CSV or Excel file to the above folder.

MMTE polls this directory every 30 minutes. When it detects a new file there, it creates a table of the format described in this file in MMDB, if such table does not exist. Or updates it, if it exists.

Note

Custom tables are not shown in the MMDB Tree View in Meta Query of Tableau Edition. At least one SAP BO environment must be connected in order to see your custom tables in the MMDB Tree View.

Created Table Parameters

The table is always created in the public schema of the metaminer database.

The table name is generated from your source file name by adding the z_ prefix and converting all letters to lower case. In our case, for the MyTestTable.csv file, the table name is z_mytesttable. If the filename contains any characters illegal for table names (e. g. spaces) the table will not be created.

The column names are generated from the values of the first line in your file. The data type of all columns is always text. All column values are not nullable with no default value:

metaminer=# \d public.z_mytesttable 
          Table "public.z_mytesttable"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 ID     | text |           |          | 
 Name   | text |           |          | 
 Seq    | text |           |          | 
 Value  | text |           |          | 

metaminer=#

The values of the second and consequent lines of the source file are converted into table rows:

metaminer=# SELECT * FROM public.z_mytesttable;
 ID |  Name   |  Seq   |   Value    
----+---------+--------+------------
 1  | Helen   | 349    | some text
 2  | Clarice | 943    | more value
 3  | John    | 4111   | even more
(3 rows)

metaminer=#

If there are any inconsistencies in the source file content (illegal characters, extra or missing values, etc), no content will be inserted, the table will be created empty.

Using the Created Table

As soon as MMTE has imported the source file, you can use its content in your SQL scripts in Meta Query:

Imported Table in Meta Query

and/or alerts:

In Alerts

Updating Table

You can update the content of the existing custom table by uploading a file with the same name but a different content.

In this case, the table will be truncated with all the existing content being removed and the new content will be inserted.

Dropping the tables

Removing the CSV or Excel file from the Dynamic Data Import directory does not drop your custom table. To delete it, use the following command in psql, pgAdmin, or any other PostgreSQL management software you prefer, for example:

metaminer=# DROP TABLE public.z_mytesttable

See Also

Direct Access to MMDB

Back to top