Resetting Metadata in the MetaMiner DB

You may need to rebuild metadata for certain datatypes after a new release that provides additional data about your BusinessObjects or Tableau metadata. It's also possible that after a patch update which corrects an extraction issue, that you would need to rebuild certain metadata.

Scenario: A new version of MetaMiner is released - How is the MMDB affected?

In this example, we'll explore the changes to the MMDB, specifically, new columns added as part of a customer enhancement to offer more metadata for a particular object type, Webi in this case.

Let's suppose our current version of MetaMiner is 5.2.100. In this version there is a table called WEBI_UNIVERSEOBJECTS that only has one column, OBJECT_NAME.

Table	Column
WEBI_UNIVERSEOBJECTS	OBJECT_NAME

Infolytik product team after working with customers decide that more metadata should be exposed for Webi objects.
So its decided that in a future release a new column OBJECT_DESC is added to the table.

Table	Column
WEBI_UNIVERSEOBJECTS	OBJECT_NAME
WEBI_UNIVERSEOBJECTS	OBJECT_DESC

In most cases, the software update handles such updates behind-the-scenes. But for more complex or comprehensive updates that require a datatype reset, we recommend applying this reset manually.

Resetting Specific Datatypes - Isolated Re-extraction

In some scenarios you may wish to only reset specific data to force MetaMiner to "pick up" changes in your Tableau or BusinessObjects system.

Installations with 50,000+ Web Intelligence Reports

Earlier we mentioned an example of a MetaMiner update that adds new metadata attributes to the MetaMiner DB for a specific datatype - specifically, Web Intelligence. If you have a large number (50k+) Webi reports, a step-wise isolated extraction may be the best approach to reduce the time to extract and test the update.

Walk-thru: Isolated Reset

Directly connecting to your Postgres MMDB

Before moving onto the steps below, you should first learn how to launch the PGAdmin3 in order to directly manage your MetaMiner DB.

1. Launch PGAdmin3 or your favorite SQL tools

Resetting Entire Datatypes

The MMDB has several Top-level database tables. The goal of resetting metadata is to ensure that the top-level tables and all descendant tables that reference the top-level table have their metadata regenerated. In MetaMiner terms it means dropping and re-creating this metadata.

Documents

truncate table publication_formats;
truncate table publication_prompt_values;
truncate table destinations_disk;
truncate table destinations_inbox;
truncate table document_categories;
truncate table publication_prompts;
truncate table prompts_default;
truncate table prompts;
truncate table publications;
truncate table destinations_ftp;
truncate table document_targets;
truncate table files;
truncate table destinations_email_add;
truncate table object_events;
truncate table document_datasources;
truncate table destinations_email;
truncate table destinations;
truncate table documents;

Crystal Reports

truncate table crystal_fields;
truncate table crystal_table_map;
truncate table crystal_promptgroup;
truncate table crystal_bv_rel;
truncate table crystal_tables;
truncate table crystal_logons;
truncate table crystal_connections;
truncate table crystal_documents;

Web Intelligence

truncate table webi1400_busitems;
truncate table webi1400_sqlcontainers;
truncate table webi1400_expressions;
truncate table webi1400_cellexpr;
truncate table webi1400_used_objects;
truncate table webi1400_variables;
truncate table webi1400_resultobjects;
truncate table webi1400_reports;
truncate table webi1400_querynodes;
truncate table webi1400_dataproviders;
truncate table webi1400_documents;

Users

truncate table user_aliases;
truncate table user_preferences;
truncate table user_attributes;
truncate table users;

Groups

truncate table userusergroups;
truncate table usergroup_aliases;
truncate table usergroup_parentgroups;
truncate table usergroup_childgroups;
truncate table usergroups;

Folders & Security

Stale Security Metadata in SEC_USERS, SEC_USER_ROLES

In some scenarios, its been seen that security metadata may be stale. If that's the case, it is recommended to reset folders and security tables and re-query the data of interest (typically from the v_folder_security table).

truncate table folders;
truncate table sec_users;
truncate table sec_user_roles;