+34 671518985 info@primecubeit.com

In this article we will show you a real life example of removing some specific entries from Platform Analytics (PA). This method can also be used when PA repository gets too big, or you just want to delete data for objects that are no longer existing.

We base our article on this TN. However, in our case, some additional actions were required as the OOTB script did not remove all the entries.

Since the Metadata connection changed, we had two Metadata entries visible in PA

We want to delete information about this old metadata string that changed and is no longer actual.

To do this we need to first to get the ID of this metadata. Create dossier on the PA cube, drag metadata attribute to the template, right-click on the metadata name and activate ID form.

Copy the id and go to the file /installPath/MicroStrategy/install/PlatformAnalytics/conf/purgeConfig.yaml and edit it in the following way:

Then you run the script: /installPath/MicroStrategy/install/PlatformAnalytics/bin/platform-analytics-purge-warehouse.sh

And after republishing the cube, you can see that unfortunately, some of the tables has still some old entries, because now the deleted entry appears as an empty space:

Then we need to run this additional script in the command line:

PGPASSWORD=XXXXXX /installPath/MicroStrategy/install/Repository/bin/./mstr_psql -d platform_analytics_wh -w -U mstr_pa -h 127.0.0.1 -c ‘DELETE  from lu_subscription_base where metadata_id=idoftheoldmetadata’

(PGPASSWORD can be found under /installPath/MicroStrategy/install/Repository/ in Default_Account.txt file).

This was just an example of cleaning old metadata, but we can also leave only number of days we are interested in in the database.  The TN is worth reading to see other use cases of purging the PA.

Below, additionaly, you can also find a linux script that checks size of folders and tables that grow with the usage of PA. It is helpful to monitor its size. As always please adjust installPath, PGPASSWORD and host to your environment.

#

#set -x

#

echo -e “\n######################################”

echo “# Topic logs #”

echo -e “######################################\n”

du -sh /installPath/MicroStrategy/install/MessagingServices/Kafka/kafka_2.12-2.2.0/logs

echo -e “\n######################################”

echo “# Zookeeper snapshot #”

echo -e “######################################\n”

du -sh /installPath/MicroStrategy/install/MessagingServices/Kafka/tmp/zookeeper

 

echo -e “\n######################################”

echo “# Postgres total size #”

echo -e “######################################\n”

 

PGPASSWORD=XXXXX /installPath/MicroStrategy/install/Repository/bin/./mstr_psql -d platform_analytics_wh -w -U mstr -h 127.0.0.1 -c ‘select t1.datname AS db_name,

pg_size_pretty(pg_database_size(t1.datname)) as db_size

from pg_database t1

order by pg_database_size(t1.datname) desc;’

 

echo -e “\n######################################”

echo “# PA top 10 table by size #”

echo -e “######################################\n”

 

PGPASSWORD=XXXXX /installPath/MicroStrategy/install/Repository/bin/./mstr_psql -d platform_analytics_wh -w -U mstr_pa -h 127.0.0.1 -c ‘select schemaname as table_schema,

relname as table_name,

pg_size_pretty(pg_total_relation_size(relid)) as total_size,

pg_size_pretty(pg_relation_size(relid)) as data_size,

pg_size_pretty(pg_total_relation_size(relid) – pg_relation_size(relid))

as external_size

from pg_catalog.pg_statio_user_tables

order by pg_total_relation_size(relid) desc,

pg_relation_size(relid) desc limit 10;’

Share This