MicroStrategy - Enterprise Manager

mstr

Articles

TNs
Command line tool
The data loader process
mstr-em-upgrade
mstr-em2 (purging and other information)
Troubleshooting
Questions
Reports

https://www.youtube.com/watch?v=6lDX33JK1MQ
https://www.youtube.com/watch?v=pGff1oTbOCc
Read the main administration manual for a list of tables
http://khaidoan.wikidot.com/microstrategy-enterprise-manager-reports

  1. Configure each projects to log all statistics including all advanced statistics (except for the "Report Job SQL" statistics) and restart the Intelligence server
  2. See if the above step get rid of the "No data" issue.
  3. Learn how to use the existing reports and dashboards
  4. See if I can create my own dashboard that would give me the "daily view", the "monthly view" and be able to slice and dice the data. This may not be real-time but may be good enough that I may not need a separate monitoring system.
  5. Look at why certain reports are still failing (take a very long time to run)
  6. See the mstr-todos page as well.

What statistics do I want to determine from using Enterprise Manager?

See the same question on the Reports page.

What is MicroStrategy Enterprise Manager?

MicroStrategy Enterprise Manager provides insights about governing and tuning all areas of your MicroStrategy environment. With Enterprise Manager, you can see a variety of Intelligence Server usage statistics. The statistics shown in predefined reports displayed by Enterprise Manager can help you make scheduling decisions, analyze bottlenecks, and tune performance.

MicroStrategy Enterprise Manager provides insights about governing and tuning all areas of your MicroStrategy environment. By analyzing the usage statistics collected by Intelligence Server, Enterprise Manager provides a historical overview of Intelligence Server operations.

MicroStrategy Enterprise Manager is another MicroStrategy installation / project that contains reports that display performance statistics for your main MicroStrategy installation. It is recommended that we setup a separate installation for the Enterprise Manager so that the work done by the Enterprise Manager reports do not adversely skew the performance or statistics of your main installation.

The heart of Enterprise Manager is a MicroStrategy project that comes with a set of predefined reports, and the metrics and attributes to create your own reports. When you log into the Enterprise Manager project, you can run reports that help you to:

  1. Allocate system resources based on data warehouse usage
  2. Research efficient aggregation, partitioning, and indexing strategies
  3. Determine the optimal time to run scheduled jobs, load data, or perform system and database maintenance
  4. Determine the most popular reports so you can schedule and cache them, thus increasing their response time and reducing the load on the system
  5. Identify unused objects from your metadata repository so they can be deleted later
  6. Identify peak usage times and patterns and, if necessary, tune your Intelligence Server to respond appropriately
  7. Determine whether you need to add more threads to the database connection threads if queue times are long
  8. Profile users based on their system resource usage

You can use the predefined reports as they are, copy the reports and then modify the copies, or build your own custom reports to suit your needs. The Enterprise Manager project includes over 300 metrics and 90 predefined reports. Many of these reports include prompts, which accept user input before a report is executed, for flexibility. You can create new metrics, prompts, filters, templates, or reports to suit the needs of your environment or the type of analysis you want to do. All the predefined objects are in the Public Objects folder in the Enterprise Manager project.

What are the components of the Enterprise Managers?

A MicroStrategy Enterprise Manager environment includes:

  1. the statistics tables: This is the statistics database that your main MSTR environment is logging its statistics data. The statistics database contains MicroStrategy-specific statistics tables created through the MicroStrategy Configuration Wizard. These tables contain data on the MicroStrategy system's usage and performance, and are populated by all projects that are configured to log statistics.
  2. the Enterprise Manager warehouse: This is a database that you setup so that when the loader collect data from the statistics database mentioned above, it store the statistics data here so that the Enterprise Manager reports can query this database. The Enterprise Manager monitoring project’s data warehouse is populated from the Intelligence Server statistics. The Enterprise Manager data warehouse is in the same physical database as the statistics tables. It contains configuration tables necessary for Enterprise Manager’s operation, lookup tables, and fact tables with transformed information from the statistics tables. Lookup tables contain descriptive information about each object in the monitored projects, such as name, owner, creation date, folder path, and so on. Fact tables contain data that has been loaded from the statistics database by the data load process.
  3. the Enterprise Manager project metadata: This is the metadata database that is setup to store the Enterprise Manager reports.
  4. the Enterprise Manager console: The Enterprise Manager console loads information about the monitored projects into the Enterprise Manager data warehouse lookup tables, and runs the data load scripts that move statistics data into the data warehouse fact tables.
  5. the data loader

What are the steps to setup Enterprise Manager for the very first time?

  1. Confirm that you have fulfilled the prerequisites for installing Enterprise Manager. For a list of Enterprise Manager’s prerequisites, see MicroStrategy Enterprise Manager prerequisites, page 834.
    1. Determine the frequency and best times for the Enterprise Manager to load data. If you need near-real-time data, you can load the data as often as once per hour. However, if you are collecting statistics in great detail, you should run the data load during times when Intelligence Server usage is low, such as overnight.
    2. Confirm that you have license for MicroStrategy Report Services. To use the dashboards that come with Enterprise Manager, you need a MicroStrategy Report Services license. If you do not have a Report Services license, contact your account representative for information about obtaining one.
    3. These dashboards are designed for use with MicroStrategy Web. If you are using MicroStrategy Web Universal, the links to other reports in the dashboards do not function. To correct the links, edit the dashboards and change all occurrences of Main.aspx in the links to mstrWeb.
  1. Request DBA to create 3 separate database users so that we can set up Enterprise Manager. Enterprise Manager should be a separate MicroStrategy installation, and therefore need its own metadata, statistics, and history databases. I recommend that we name these database users as MSTR_EM_METADATA, MSTR_EM_HISTORY, and MSTR_EM_STATISTICS
  2. Install Intelligence server and Enterprise Manager on a separate server. For more information about installing Enterprise Manager, see Installing Enterprise Manager, page 836 of the System Administration Guide.
  3. Configure the Intelligence server on the Enterprise Manager server.
  4. Upgrade the 9.4 Enterprise Manager mdb project that comes with MSTR 9.5.1 to 9.5.1 (if applicable) as per http://community.microstrategy.com/t5/Server/TN229967-quot-The-source-project-Enterprise-Manager-is-using-an/ta-p/229967:
    1. Create a DSN that connects to the MicroStrategy 9.5 (9s) out of the box Enterprise Manager Metadata. This is a Microsoft Access file called "EM_Proj_MD.mdb" and located at: "<install_drive>:\Program Files (x86)\MicroStrategy\Enterprise Manager"
      1. Go to C:\\Program Files (x86)\MicroStrategy\Enterprise Manager
      2. Remove the read-only bit from EM_Proj_MD.mdb
        1. Right click on the file
        2. Select Properties
        3. Uncheck the Read-only check box
        4. Click OK, and click on OK again to confirm the permission.
      3. Open a Windows command prompt
      4. Go to C:\Windows\SysWOW64
      5. Run odbcad32
      6. Click on the System DSN tab
      7. Click on the Add button
      8. Select Microsoft Access Driver (*.mdb)
      9. Click on the Finish button
      10. In the Data Source Name field, put: Specify MSTREnterpriseManagerSourceMDB
      11. Provide a description if you want to
      12. Click on the Select button
      13. Select the C:\Program Files (x86)\MicroStrategy\Enterprise Manager\EM_Proj_MD.mdb file
      14. Click OK
      15. Click on the Advanced button
      16. Click on ExtendedAnsiSQL and change its value to 1 and hit Enter
      17. Click on the Advance button
      18. Click on MaxBufferSize, change its value to 8192 and hit Enter
      19. Click the OK button until all ODBC dialogs are closed
      20. Click on the Windows Start menu and run regedit
      21. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0
      22. Right click on MaxLocksPerFile and select Modify
      23. Change it to 200000 (decimal). See TN20459
    2. Open MicroStrategy Configuration Wizard and select "Configure Intelligence Server".
    3. Select the DSN created in step 1 and click "Next".
    4. Leave the default username and password and click "Next".
    5. Select "Use the selected Server Definition as active" and click on the "Enterprise Manager default" Server Definition. Click "Next".
    6. Continue clicking "Next" keeping all the default settings and click "Finish". Once this process is completed, the Intelligence Server will be pointing to the out of the box Enterprise Manager Metadata.
    7. Once the process is completed, the Configuration Manager main screen will be displayed. Select the option for "Upgrade Existing Environment to MicroStrategy Analytics Enterprise".
    8. Select "Intelligence Server Components". Click "Next".
    9. Leave the default username and password and click "Next"
    10. See step 10 from http://community.microstrategy.com/t5/Server/TN229967-quot-The-source-project-Enterprise-Manager-is-using-an/ta-p/229967 for the screenshot of selected options
    11. Modify the ExtendedAnsiSQL and MaxBufferSize advanced settings in the ODBC Administrator as per TN20459 and click "Ok" in the popup window. (We already did this above, so just click on the OK button)
    12. Click "Finish" to start the Upgrade process.
    13. Reconfigure the Intelligence server back to its default DSNs.
  1. Create the statistics database if you haven't done so already. This is for the Intelligence servers that are to be monitored via Enterprise Manager to log statistics. This is typically done by DBA.
  2. Create the statistics tables if you haven't done so already. This is for the Intelligence servers that are to be monitored via Enterprise Manager to log statistics. To create the statistics tables:
    1. Create the ODBC DSN for the statistics database.
      1. Open a Windows command prompt
      2. Go to c:\Windows\SysWOW64
      3. Run odbcad32.exe
      4. Click on the "System DSN" tab
      5. Click on the Add button
      6. Select "MicroStrategy ODBC Driver for Oracle Wire Protocol" if you are using Oracle
      7. Click Finish
      8. Provide the details required to connect to your statistics database and click OK
    2. Launch MicroStrategy Configuration Wizard (Start menu -> Programs -> MicroStrategy -> Configuration Wizard)
    3. Select the first option ("Create Metadata, History List, and Statistics Repository Tables") and click Next
    4. Select the Statistics Tables option and clear all other options.
    5. Click Next
    6. From the DSN drop-down list, select the DSN for the statistics database. Any table in this database that has the same name as the MicroStrategy statistics table is dropped.
    7. In the User Name and Password fields, enter a valid login and password for the statistics database. The login that you specify must have permission to create and drop tables, and permission to create views in the statistics database.
    8. Click Advanced
    9. In the Script field, the default script is displayed. The selected script depends on the database type that you specified previously. To select a different script, click … (the Browse button) to browse to and select a script that corresponds to the DBMS that you are using. The default location for these scripts is: C:\Program Files\Common Files\MicroStrategy
    10. Click Next
    11. Click Finish
  1. Duplicate the DoNotDeleteOrModify project from DEV
    1. Launch MicroStrategy Desktop
    2. Connect to the existing DEV project source
    3. From the Schema menu, select Duplicate Project. Be careful. Before clicking on the Finish button, make sure that the options selected is appropriate. Make sure that we select "None" for "Users and user groups".
  2. Configure MicroStrategy Intelligence servers to use Single Instance Logging.
    1. Launch MicroStrategy Desktop
    2. Log into appropriate project source (drill down on appropriate project source)
    3. Right click on the project source and select Configure Intelligence Server. The Intelligence Server Configuration Editor opens.
    4. Expand Statistics -> General, and select Single Instance Session Logging
    5. From the drop-down list, select a project.
    6. Click OK
    7. Right click on appropriate project and select Project Configuration. If you are using Single Instance Session Logging, the project that you select must be the project that you selected when you set up Single Instance Session Logging
    8. Create a new Database Instance in MSTR for the statistics database if one is not already created:
      1. Expand the Database Instances category, and select SQL Data Warehouse sub-category.
      2. You need to create a new database instance for the statistics database. Click New. The Database Instances dialog box opens.
      3. In the Database instance name field, type in a name for the statistics database instance
      4. From the Database connection type drop-down list, select the database type and version that corresponds to the statistics database DBMS.
      5. You need to create a new database connection to connect to the database instance. Click New. The Database Connections dialog box opens.
      6. In the Database connection name field, type a name for the database connection.
      7. From the ODBC Data Sources list, select the Data Source Name (DSN) used to connect to the statistics database.
      8. If your database supports parameterized queries, you can improve the efficiency of the statistics logging process by enabling parameterized queries in the statistics database instance. To do this, on the Advanced tab, select the Use Parameterized Queries check box.
      9. You need to create a new database login to log into the database instance. On the General tab, click New. The Database Logins dialog box opens.
      10. Type a name for the new database login in the Database login field. If this database login is more than 32 characters long, the statistics logging will generate errors in the DSS Errors log.
      11. Type a valid database login ID and password in the corresponding fields. MicroStrategy does not validate this login ID and password, so be careful to type them correctly.
      12. Click OK 3 times to return to the Project Configuration Editor. Each time you click OK, make sure your new database login and database connection are selected before clicking OK.
    9. In the Database Instances category, select the Statistics sub-category
    10. From the Statistics DB Instance drop-down list, select your new statistics database instance.
    11. Click OK.
  3. Check to make sure that we are using the right database credential for the the statistics database instance. Somehow the statistics database instance is using the "SEM Hospital" DSN.
  4. Specify the statistics to log for each project
    1. Launch MicroStrategy Desktop
    2. Log into appropriate project source (drill down on appropriate project source)
    3. Right click on appropriate project and select Project Configuration
    4. Expands Database Instances -> Statistics and select appropriate database instances for the statistics database
    5. Expands Statistics -> General
    6. Select the Basic Statistics check box.
    7. To log advanced statistics, select the check boxes for the statistics you wish to log
    8. Click OK
  5. Create a new log destination name ObjectServerErrorLog and direct Object Server error message to this new log destination.
    1. Launch the MicroStrategy Diagnostics and Performance Logging Tool
    2. Click on Tools -> Log Destination
    3. Specify ObjectServerErrorLog for the File name
    4. Click on the Save button
    5. Click on the Close button
    6. Scroll down to the "Object Server" section
    7. Locate the "Error" row under the "Object Server" section, and under the "File Log" column, specify the ObjectServerErrorLog destination that we just created above
    8. Click on the Save icon
    9. Click on File -> Exit
  6. Configure the Intelligence servers to log performance statistics:
    1. Launch MicroStrategy Diagnostics Configuration editor
    2. Click on the 'Performance Configuration' tab
    3. Under the Statistics column, check the boxes for the counters that need to be logged to Statistics.
    4. Under 'Statistics Properties' on the right side, select the required logging frequency and set 'Persist Statistics' to 'Yes' as shown below. See http://community.microstrategy.com/t5/Server/TN20536-How-to-set-up-collection-of-performance-counters-to/ta-p/180697
    5. Save and close the diagnostics editor.
  7. Configure the Enterprise Manager project to be loaded:
    1. Launch Developer / Desktop
    2. Connect to the project source that host the enterprise manager project
    3. Right click on the project source that host the enterprise manager project and select Configure Intelligence Server
    4. Click on Projects -> General
    5. Check the appropriate check boxes next to the Enterprise Manager project
    6. Click OK
  8. Restart the Intelligence server to enable statistics logging
  1. Close all user connections, to ensure that there is no session activity on the Intelligence Servers being monitored by Enterprise Manager. In other words, shutdown Tomcat Web, and other Developer / Desktop connection.
  2. Run an Enterprise Manager data load. Make sure the Close orphan sessions option is selected. This data load ensures that the statistics tables are completely up to date, and that all open Intelligence Server sessions are closed. If you do not run a data load before upgrading Enterprise Manager and the projects on the Intelligence Servers, you may lose access to some statistics data.
  3. Upgrade all the Intelligence Servers that are being monitored by Enterprise Manager to the latest version.
  4. Update all projects on those Intelligence Servers to the latest version.
  5. Shut down all Intelligence Servers that are being monitored by Enterprise Manager.
  6. Back up the statistics database and the Enterprise Manager warehouse database.
  7. Upgrade the Enterprise Manager console to the latest version. This is already done if you installed or upgrade Enterprise Manager along with the Intelligence Server or other product.
  8. Upgrade the statistics repository through Configuration Wizard.
    1. Open the Configuration Wizard
    2. Select the Upgrade existing environment to MicroStrategy Analytics Enterprise option and click Next
    3. Select the Statistics & Enterprise Manager Repository options and click Next
    4. Select appropriate DSN for the statistics database / table and provide appropriate database credential and click Next. This will display a warning: DATA MANIPULATION LANGUAGE (DML) REQUIRED AFTER UPGRADE. This screen also display the current version of the statistics repository, and the version that is being upgrade to, a long with the DML scripts that we must run after completing this upgrade. The script that we have to run in this case: statistics_upgrade_partitioned_9xto93_OR_94_DML_Oracle.sql. This script can be found: InstalledDirectory\MicroStrategy\Enterprise Manager\DML Scripts.
  9. Upgrade the Enterprise Manager Warehouse tables.
    1. Open the Enterprise Manager console. You are prompted to upgrade the Enterprise Manager Warehouse tables. Click Yes.
  10. Initialize the Enterprise Manager. Here, we are upgrading the Enterprise Manager project using the replace approach. If we wish to keep the existing Enterprise Manager project, refer to the official Upgrade Guide that comes with your MicroStrategy download. To initialize the Enterprise Manager project:
    1. Launch Enterprise Manager console. From the Start menu, point to All Programs, then MicroStrategy Tools, then select Enterprise Manager Console. The Enterprise Manager Console opens.
    2. Click on Initialize (at the top)
    3. Click Next
    4. Click on the Transfer button. The Project Mover tool opens.
    5. Do not change anything on the first screen. Click Next.
    6. In the Transferred Project Name field at the bottom of the screen, change the project name to an appropriated project name for your environment and click Next.
    7. Select C:\Program Files (x86)\MicroStrategy\Enterprise Manager\em_sql_ora.sql and click Next.
    8. Select the appropriate DSN for the Metadata repository, and the corresponding database credential. This is the location where the Enterprise Manager project will live. If you want to host your Enterprise Manager on the same Intelligence server as your other projects, specify the same Metadata DSN here.
    9. Select the Append the project to be transferred to the existing MD option and click Next.
    10. Provide the Administrator's password for the selected metadata and click Next.
    11. Select appropriate DSN for the statistics warehouse location. The statistics warehouse must be on the same database as the statistics tables, so specify the DSN that you normally use for the statistics table.
    12. Review the information about the project transfer, and click Transfer when you are ready. The process can take several minutes. When the transfer process is complete, the Project Mover Wizard closes and you are returned to the Enterprise Manager Console. Do not continue with Enterprise Manager Console. Go on to the next step, and we will come back to the Enterprise Manager Console below.
  11. (Optional) Execute the appropriate DML scripts against the statistics tables and the Enterprise Manager data warehouse.
  12. Restart all Intelligence servers, including the one that is for hosting the Enterprise Manager.
  13. Specify project sources and projects to be monitored by this Enterprise Manager instance:
    1. Launch Enterprise Manager Console
    2. Click on Configure (at the top)
    3. Select the project source that contains the projects that you want to monitor using Enterprise Manager from the Available Server Project Sources list. If the project source that you want to monitor has the default name of “New Project Source,” you must change the name of the project source before adding it to Enterprise Manager. Otherwise Enterprise Manager will be unable to load statistics and fact data from the projects in the project source.
    4. Click the right arrow (>). The Select Projects (Project Source Name) dialog box opens.
    5. In the User Login area, type a MicroStrategy login ID that has Administrator access to the selected projects, and the password for that login ID. Enterprise Manager console only supports standard authentication. Warehouse and single sign-on authentication modes are not supported.
    6. Click Validate. The available projects are listed in the selection area.
    7. Select the projects you want to monitor with Enterprise Manager and click OK. You can only select projects that have had statistics logging enabled.
    8. Repeat steps 3 through 7 for any other project sources containing projects you want to monitor. In a clustered environment, you can only monitor a project once, regardless of how many Intelligence Servers it is running on. This is because Enterprise Manager connects to the project to populate the lookup tables, and because the project is the same on all Intelligence Servers when clustered. All the statistics are available in Enterprise Manager as long as each clustered Intelligence Server is set up to log statistics to the Enterprise Manager data warehouse.
  14. Specify the schedule for the data loader.
    1. Launch Enterprise Manager
    2. Click on Schedule (at the top)
    3. Specify appropriate options. Make sure that we use the once a day option, and not once every hour.
    4. From the Tools menu, select Options. The Options dialog box opens.
    5. Select the Data Load tab.
    6. Select the check box for any maintenance tasks (none required as per MSTR support team which seems contradicting) you want to perform during each data load. I think I am going to run all of them during each data load.
    7. Click Apply Changes. Changes made on this page do not take effect until you click Apply Changes.
    8. Click on the Stop button and click on the Start button.
    9. Click on the Next button.
  15. Restart all the Intelligence Servers.
  16. Run an Enterprise Manager data load. This data load populates the new Enterprise Manager warehouse tables.

Do we need to do anything with the initialization screen when changing the MicroStrategy Enterprise Manager machine?

No. It is not necessary to perform the initialization if we are changing the MicroStrategy Enterprise Manager machine. The initialization step is a one time thing that needs to be performed when creating the MicroStrategy Enterprise Manager Warehouse. It is not necessary to perform the initialization if we are changing the MicroStrategy Enterprise Manager machine.

How can we delete data from the Enterprise Manager data warehouse?

  1. Launch the Enterprise Manager Console
  2. Click on Tools -> Data Load Options
  3. Specify a custom window and click on WH data deletion

We can also re-initialize the statistics database using the Configuration Wizard. This will drop the existing tables and re-create them. We will also loose the statistics that were previously collected. Another alternative is to use the em_clean_sql.sql and em_clean_sql_ora.sql scripts that are found inside C:\Program Files (x86)\MicroStrategy\Enterprise Manager\Scripts. I was using Oracle, so I probably should run the script inside the em_clean_sql_ora.sql, but I am not sure about the em_clean_sql.sql file though. I think that em_clean_sql.sql is for MS SQL, but I am not sure.

How can we fix the "404 page not found" problem that happens with Enterprise Manager reports?

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License