KB484672: How to configure MySQL as the Platform Analytics warehouse in MicroStrategy 2020

Description 

Beginning in Strategy 2020, the Platform Analytics Repository now supports PostgreSQL. By default, the Repository (PostgreSQL) is installed by the Strategy installer on the same machine as the Platform Analytics Consumer. However, you can also use your own MySQL.
To configure remote PostgreSQL as the Platform Analytics warehouse, refer to KB483930: How to Configure Remote PostgreSQL with Platform Analytics. 
To use your own your own MySQL, you must perform the following steps:

1. Create a User, Database, and Schema for Platform Analytics. Ensure remote access is enabled if MySQL is on another machine
2. Modify the PAConsumerConfig.yaml to point to MySQL
3. Populate the Platform Analytics Repository
4. Create a Platform Analytics project
5. Place mysql-connector-java.jar in C:\Program Files (x86)\MicroStrategy\Platform Analytics\lib and restart the Platform Analytics Consumer

Create a User, Database, Schema for Platform Analytics

Create User and Create Database instructions
There are three steps to setup Platform Analytics warehouse:
1. Create a user.
MySQL 8:
CREATE USER 'mstr'@'%' IDENTIFIED WITH mysql_native_password BY 'YOUR_PASSWORD';
2. Create the database.
Platform Analytics requires the database be named platform_analytics_wh.
CREATE DATABASE platform_analytics_wh DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
3. Grant privileges.
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, EXECUTE, CREATE TEMPORARY TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE ON platform_analytics_wh.* TO 'mstr'@'%';
FLUSH PRIVILEGES;
NOTE: Work with MySQL DBA to ensure the steps are correctly performed if the database version varies; MySQL product manual could also been used as reference.
CREATE DATABASE Statement
https://dev.mysql.com/doc/refman/8.0/en/create-database.html
CREATE USER Statement
https://dev.mysql.com/doc/refman/8.0/en/create-user.html
Ensure remote access is enabled if MySQL is on another machine. If installing MySQL on another machine, the database user for the Platform Analytics Repository must be configured to allow remote access. Remote access can be enabled at the time of creating the Database (DB) User or after by following the steps in this documentation.

1. Connect to the MySQL Server using any client (MySQL Workbench, DB Query Tool, etc.).
2. Run the command:
   

   
   UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='<DB User Name>'; FLUSH PRIVILEGES;
   

   
   
3. Replace <DB User Name>  with the user installing the Platform Analytics Repository.
4. The database user can now connect to this MySQL server instance from any remote machine.

Modify the PAConsumerConfig.yaml to point to MySQL

1. Locate

   PAConsumerConfig.yaml

   in the below directory:
   
• Linux:

  /opt/MicroStrategy/PlatformAnalytics/conf

• Windows:

  C:\Program Files (x86)\MicroStrategy\Platform Analytics\conf

2. Change whDbType to MySQL and input the warehouseDbConnection entries:
   

   
   whDbType: "Postgresql"
     warehouseDbConnection:
       whHost: "127.0.0.1"
       whUser: "root"
       whPasswd: null
       whPort: 3306
   whDb: "platform_analytics_wh"
   

   
   
   For details on PAConsumerConfig.yaml file, see Configure Platform Analytics Using the PAConsumerConfig.yaml File.
   

Populate the Platform Analytics Repository

After you have created a user, database, and schema for Platform Analytics, and modified the

PAConsumerConfig.yaml

file, you must populate the repository.
Use the Platform Analytics custom installer to populate all the necessary tables for Platform Analytics. For instructions, see Manually Deploy the Platform Analytics Repository.
 

Create a Platform Analytics project

Finally, you need to create a Platform Analytics project.
Use the Configuration Wizard to create a Platform Analytics project to point to the above Platform Analytics Repository. For instructions, see Create the Platform Analytics Project.

Place mysql-connector-java.jar in C:\Program Files (x86)\MicroStrategy\Platform Analytics\lib 

Place

mysql-connector-java.jar

in

C:\Program Files (x86)\MicroStrategy\Platform Analytics\lib

 on the Platform Analytics Consumer machine and then restart the Platform Analytics Consumer.

mysql-connector-java.jar

can be copied from any other machine that has MySQL installed.
Stop, start, and check the status of the Telemetry Store using the following commands:

1. Stop the Consumer
   

   
   /platform-analytics-consumer.sh stop

   
   
2. Start the Consumer:
   

   
   ./platform-analytics-consumer.sh start

   
   
3. Check the status of the Consumer. 
   

   
   ./platform-analytics-consumer.sh status