Mstr Em2

mstr-em

How can we manually purge statistics using Desktop / Developer?

  • Launch Desktop
  • Connect to appropriate project source
  • Right click on appropriate project source and select Configure Intelligence Server
  • Drill down on Statistics -> Purge
  • Provide appropriate value for the date range and click the Purge Now button

How can we configure Intelligence servers to not log statistics?

We have to configure each project not to log statistics:

  1. Launch Desktop
  2. Connect to appropriate project source
  3. Right click on appropriate project
  4. Select Project Configuration
  5. Click on Statistics (on the left hand side)
  6. Uncheck all the check boxes and click OK
  7. Click on Database instances (on the left hand side)
  8. Click on Statistics
  9. Select <None> and click OK.

Does the Enterprise Manager data loader delete data from the statistics tables after a successful load?

No. We have set that up ourselves.

How can we purge data for a specified time period from the Enterprise Manager warehouse?

Enterprise Manager collects a great deal of data. Over time some of this data may become outdated and no longer relevant to your analysis. When this happens, MicroStrategy recommends that you purge the outdated data from the data warehouse to ensure optimum database performance. Purging unused or out-of-date data from the warehouse keeps the database to a manageable size, making it easier to maintain and faster to query.

  1. Launch Enterprise Manager Console
  2. Click on Tools -> Data Load Options. The Data Load Options dialog box opens.
  3. Specify custom time window (set a Start date and End date).
  4. Click WH data deletion. The Purge Projects dialog box opens.
  5. Select the projects whose data you want to purge from the warehouse from the list of projects,
  6. Click OK. All data pertaining to the selected projects during the specified time window is deleted from the Enterprise Manager warehouse.

Purging unused or out-of-date data from the warehouse keeps the database to a manageable size, making it easier to maintain and faster to query.

All database purge processes are run as a database transaction, and the transaction is rolled back if any failure occurs, to ensure that the statistics database is in a consistent state. To keep the size of these transactions small, it is recommended that you set many small time windows for purge operations rather than specifying a single large time window, depending on the size of your statistics database.

How can we purge statistics?

To purge statistics for all projects on a particular project source at once:

  1. Launch Desktop / Developer.
  2. Right-click the project source and select Configure MicroStrategy Intelligence Server. The Intelligence Server Configuration dialog box opens.
  3. Select the Statistics category, and the Purge subcategory beneath it.
  4. Specify a date range for which to purge statistics.
  5. Specify the purge time out setting in seconds. The server uses this during the purge operation. The server issues a single SQL statement to purge each statistics table, and the time out setting applies to each individual SQL statement issued during the purge operation.
  6. Click the Advanced button. A list of statistics is displayed.
  7. Select the statistics that you want to purge. For an explanation of these statistics, see Overview of Intelligence Server statistics, page 346.
  8. If you are using clustered Intelligence Servers:
    • To only purge statistics logged by the current Intelligence Server, select the Only purge statistics logged from the current Intelligence Server machine check box. Statistics logged by other Intelligence Servers are ignored.
    • To purge statistics logged by any of the clustered Intelligence Servers, clear the Only purge statistics logged from the current Intelligence Server machine check box.
  9. Click the Purge Now button. The statistics for the specified dates are deleted from the statistics database.

To purge statistics for a particular project:

  1. Launch Developer / Desktop
  2. Right-click the appropriate project and select Project Configuration. The Project Configuration Editor opens.
  3. Select the Statistics category, and the Purge subcategory beneath it.
  4. Specify the date range for which to purge statistics.
  5. Specify the purge timeout setting in seconds. The server uses this during the purge operation. The server issues a single SQL statement to purge each statistics table, and the time out setting applies to each individual SQL statement issued during the purge operation.
  6. Click the Advanced button. A list of statistics is displayed.
  7. Select the statistics that you want to purge. For an explanation of these statistics, see Overview of Intelligence Server statistics, page 346.
  8. If you are using clustered Intelligence Servers:
    • To purge only project statistics logged by the current Intelligence Server, select the Only purge statistics logged from the current Intelligence Server machine check box. Statistics logged by other Intelligence Servers are ignored.
    • To purge project statistics logged by any of the clustered Intelligence Servers, clear the Only purge statistics logged from the current Intelligence Server machine check box.
  9. Click the Purge Now button. The statistics for the specified dates are deleted from the statistics database. Statistics at the project level can also be purged with a Command Manager script, using the PURGE STATISTICS command in the Project Configuration area.

How can we log performance statistics?

  1. Open the Diagnostics and Performance Logging Tool (From the Windows Start menu, point to All Programs, then MicroStrategy Tools, and then select Diagnostics Configuration)
  2. From the Select Configuration drop-down list, select CastorServer Instance.
  3. Select the Performance Configuration tab.
  4. Make sure the Use Machine Default Performance Configuration check box is cleared so that your logging settings are not overridden by the default settings.
  5. In the Statistics column, select the check boxes for the counters that you want to log to the statistics database.
  6. In the Statistics Properties group, in the Logging Frequency (min), specify how often (in minutes) you want the performance counters to log information.
  7. From the Persist statistics drop-down list, select Yes.
  8. From the File menu, select Save

What are the 3 processes used by Enterprise Manager?

  1. logging statistics: You choose the MicroStrategy projects to log usage statistics into the statistics tables.
  2. loading data: Before the raw information in the statistics database can be analyzed with the monitoring project, it must be converted. In addition, Enterprise Manager needs up-to-date information about the projects it monitors to report accurately on topics like per-user resource usage. The data load process populates both the lookup and fact tables in the Enterprise Manager data warehouse.
  3. reporting on that data: The Enterprise Manager administrator executes reports in the Enterprise Manager project to analyze the information in the data warehouse.

What are the two steps of the data loading process?

The data load process is broken into two steps.

  1. Retrieves information about the structure of the monitored projects by means of the Intelligence Server API and populates the lookup tables in the data warehouse with that information.
  2. Migrates data from the statistics tables into the data warehouse fact tables by means of a set of SQL scripts.

What is the purpose of the Enterprise Manager console?

The Enterprise Manager console provides a wizard that walks you through the steps to initialize and configure the Enterprise Manager project. It consists of three major sections:

  1. Initializing your Enterprise Manager production environment
  2. Selecting the projects to monitor
  3. Scheduling how often statistics are loaded into the Enterprise Manager data warehouse.

You can close the Enterprise Manager Console at any time without losing anything you have already completed. The Enterprise Manager Console automatically saves all the work that you do and continues from where you stopped when you open the console again.

How can we create the Enterprise Manager metadata database?

The Enterprise Manager-specific tables in this database are created and populated when you proceed through the Enterprise Manager Console, in the Initialization step. This is done by importing the Microsoft Access metadata database into the production database.

When you install a newer version of the Enterprise Manager over the previous verion, a dialog box notifies you that the project will be overwritten. What does this mean?

When you install a newer version of MicroStrategy Enterprise Manager over your previous version, a dialog box notifies you that the Enterprise Manager project will be overwritten. The message only refers to the files EM_WH.mdb, EM_Proj_MD.mdb, and the *.sql files; the MicroStrategy Enterprise Manager warehouse and metadata databases are not affected by the upgrade. If you have made any changes to these files, make sure you have backed them up before you install the new version of Enterprise Manager.

How can we select projects to monitor?

Search for Specify project sources and projects to be monitored by this Enterprise Manager instance section on the main Enterprise Manager page.

How can we manually run a data load?

  1. Launch Enterprise Manager Console
  2. Click on the third tab (the Schedule tab)
  3. Click on the Run Manual Loading Now

How can we configure how frequent the statistics are loaded?

Search for Specify the schedule for the data loader on the main Enterprise Manager page. You can also schedule the data load process to run by using the Windows AT command and calling the MicroStrategy Enterprise Manager data load command: maemetl.

How can we run a manual data load immediately?

  1. In the Enterprise Manager console, select Schedule.
  2. Read the Scheduling Overview page and click Next.
  3. Click Run Manual Loading Now. A dialog box opens with a progress bar indicating the progress of the data load process. When the data load finishes, click OK to close this dialog box.

How can we load data for a specific time period?

  1. In the Enterprise Manager console, from the Tools menu, select Data Load Options.
  2. In the drop-down list boxes, specify the Start Date and End Date for the custom time window.
  3. Click Custom Time Window. The Data Load Options dialog box closes, and a dialog box opens with a progress bar indicating the progress of the data load process. When the data load finishes, click OK to close this dialog box.

How can we perform an Enterprise Manager maintenance task immediately?

  1. From the Tools menu in the Enterprise Manager Console, select Data Load Options. The Data Load Options dialog box opens.
  2. Click the button corresponding to the task to perform.

Why does MSTR use two buffers for logging statistics?

Intelligence Server logs the specified statistics to a memory buffer. Every 10 seconds, the contents of the buffer are copied to a second buffer, and then written from this second buffer into the statistics database. Using two buffers avoids simultaneous read and write cycles in the memory buffer, which could otherwise occur during periods of heavy logging activity. For a detailed examination of the tables in the statistics database, see the Statistics Data Dictionary in the Supplemental Reference for System Administration.

How many database connection does the Intelligence server use for logging statistics per project?

Intelligence Server may open up to one database connection for each project that is configured to log statistics. For example, in a project source with four projects, each of which is logging statistics, there may be up to four database connections opened for the purpose of logging statistics. However, the maximum number of database connections is typically only seen in high concurrency environments.

In a clustered environment, each node of the cluster requires a database connection for each project loaded onto that node. For example, a two-node cluster with 10 projects loaded on each node has 20 connections to the warehouse (10 for each node). Even if the same ten projects are loaded on both nodes, there are 20 database connections.

What is Complete Session Logging?

By default, all projects for a given project source must be configured to log statistics individually. This configuration is called Complete Session Logging. It allows some projects to log statistics to one database and some projects to log to another database. The Enterprise Manager data warehouse must be in the same database as the statistics database for a given project. If you are using Enterprise Manager in a complete session logging configuration, there are as many Enterprise Manager data warehouses as there are statistics databases. A separate Enterprise Manager project must be configured for each statistics database. MicroStrategy recommends that you configure all projects in your project source to log statistics to the same database. This is accomplished by configuring your system to use Single Instance Session Logging. This can minimize session logging and optimize system performance. Under single instance session logging, you must still specify which statistics are logged for each individual project in the project source.

What is Single Instance Session Logging?

MicroStrategy recommends that you configure all projects in your project source to log statistics to the same database. This is accomplished by configuring your system to use Single Instance Session Logging. In other words, MicroStrategy recommends that we use Single Instance Session Logging. This can minimize session logging and optimize system performance. Under single instance session logging, you must still specify which statistics are logged for each individual project in the project source. To use single instance session logging successfully, the selected single instance session logging project must be loaded onto the Intelligence Server at startup. If clustered Intelligence Servers are being used, the project must be loaded onto all the clustered Intelligence Servers. Failing to load this project on all servers at startup results in a loss of session statistics for any Intelligence Server on which the project is not loaded at startup. For details on the possible side effects of not loading all projects, see MicroStrategy Tech Note TN14591.

How can we use Single Instance Session Logging?

To log all statistics from a project source to the same database:

  1. Launch Developer / Desktop.
  2. Connect to appropriate project source
  3. Right click on the project source and select Configure MicroStrategy Intelligence Server. The Intelligence Server Configuration Editor opens.
  4. Under Statistics, select General.
  5. Select the Single Instance Session Logging option.
  6. Select a project from the drop-down list. The statistics for all projects on this Intelligence Server will be logged to the database instance specified for this project.
  7. Click OK. The Intelligence Server Configuration Editor closes.
  8. Configure each project to log statistics

How can we create a new statistics database?

  1. Request DBA to create the empty data warehouse database. This database must be one of the databases certified for Intelligence Server statistics, as listed in the MicroStrategy Readme.
  2. Create the DSNs using the system ODBC interface.

How can we create the statistics tables in the new statistics database?

After the statistics database has been created, use MicroStrategy Configuration Wizard to create the empty statistics tables:

  1. Start the MicroStrategy Configuration Wizard. From the Windows Start menu, point to All Programs, then MicroStrategy Tools, and then select Configuration Wizard.
  2. On the Welcome page, select Metadata, History List, and Statistics Repository Tables and click Next.
  3. Select the Statistics Tables option and clear all other options. Click Next. The Statistics Tables page opens.
  4. From the DSN drop-down list, select the DSN for the statistics database. Any table in this database that has the same name as a MicroStrategy statistics table is dropped. For a list of the MicroStrategy statistics tables, see the Statistics Data Dictionary in the Supplemental Reference for System Administration.
  5. In the User Name and Password fields, enter a valid login and password for the data warehouse database. The login that you specify must have permission to create and drop tables in the database, and permission to create views.
  6. The final screen shows a summary of your choices. To create the statistics tables, click Finish.

How can we configure a project to log statistics?

Refer to the What are the steps to setup Enterprise Manager for the very first time question on the main Enterprise Manager page.

What is installed when we install Enterprise Manager?

  1. Microsoft Access™ metadata database: This database contains the metadata for the Enterprise Manager project, including all the facts, attributes, hierarchies, metrics, filters, and reports that are predefined as part of Enterprise Manager.
  2. Microsoft Access data warehouse database: This database contains the fact and lookup tables that need to be populated, as well as configuration tables with information necessary for the operation of Enterprise Manager.
  3. Enterprise Manager Console: This application helps you configure Enterprise Manager, and it launches the data loading scripts.
  4. SQL scripts: A wide variety of SQL scripts that create warehouse tables, perform various other setup tasks, and perform the data load process.

What is the overall purpose of the Enterprise Manager maintenance tasks?

In addition to loading data from the statistics tables and project metadata, the data load process can perform certain system maintenance tasks. These tasks keep your Enterprise Manager project and data loads performing efficiently.

When are these Enterprise Manager maintenance tasks run?

They can be performed during each data load, or can be run separately, although running them as part of the normal / scheduled data load is probably the best option because then we do not have to worry about potential race / concurrent issue between these maintenance tasks and the data loader process itself. The maintenance tasks are run after the data load finish.

When should we run these Enterprise Manager maintenance tasks?

The overall purpose of these maintenance tasks is to keep your Enterprise Manager project and data loads performing efficiently. However, as per MSTR support team, we should not need to run these as part of our normal / scheduled data load. This seems contradicting to each other, so it is up to you to decide. I probably would run some of these maintenance tasks as part of the normal / scheduled data load.

What is the purpose of the "Update folder paths" maintenance task?

The "Update folder paths" maintenance task updates the location property of attributes such as Report, User, and so on. It synchronizes the Enterprise Manager warehouse lookup tables with the actual folder paths in the metadata.

What is the purpose of the "Update object deletions" maintenance task?

The "Update object deletions" tasks ensures that objects that are deleted in the project metadata are marked as having been deleted in Enterprise Manager. Information about deleted objects is retained in the Enterprise Manager lookup tables for historical analysis. A deleted object is marked with a Deleted flag in the corresponding lookup table.

What is the purpose of the "Repopulate relate tables" maintenance task?

The "Repopulate relate tables" task synchronizes the relationship (relate) tables in the Enterprise Manager warehouse, such as IS_SCHED_RELATE or IS_USR_GP_USR, with the metadata.

What is the purpose of the "Close open sessions" maintenance task?

The "Close open sessions" maintenance task closes all sessions that are listed as open in the statistics database. Using this task helps to avoid orphan sessions, entries in the statistics database that indicate that a session was initiated in Intelligence Server, but no information was recorded when the session ended. Orphan sessions occur rarely, but they can affect the accuracy of Enterprise Manager reports that use Session Duration. For example, one long-running orphan session may skew the average time a session lasts by several days. When this task is executed, all sessions whose disconnect time is NULL that have been open for more than 24 hours have their disconnect time updated to 24 hours after the connect time. The SQL script run for this option is em_close_orphan_sessions_DBname.sql.

What is the purpose of the "Update database statistics" maintenance task?

The "Update database statistics" maintenance task executes SQL scripts that cause the Enterprise Manager warehouse and statistics database to collect statistics on these warehouse tables. The database uses these statistics to improve response times for Enterprise Manager reports. This option is available for SQL Server, MySQL, Oracle, Teradata, and DB2 version 8.2.2 or later. This task should be run frequently to improve the performance of Enterprise Manager reports. The SQL scripts that are run for this option are: Upd_Stat_Table_Stats_DBname.sql and Upd_Fact_Table_Stats_DBname.sql

How can we specify that certain maintenance tasks should be run as part of each data load?

  1. From the Tools menu in the Enterprise Manager Console, select Options. The Options dialog box opens.
  2. Select the Data Load tab.
  3. Select the tasks to perform during the data load. For a detailed explanation of each task, see Enterprise Manager maintenance tasks, page 853.
  4. Click OK to save your choices and close the Options dialog box. The selected data load options will be performed during each subsequent data load.

What are the best practices for using Enterprise Manager?

  1. Make all users who need access to the Enterprise Manager reports members of the MicroStrategy Web Viewer user group. Users in this group have all the necessary permissions and privileges to use the out-of-the-box Enterprise Manager reports.
  2. Make all users who need administrative access to the Enterprise Manager project members of the EMAdmin user group. Users in this group have all the necessary permissions and privileges to administer the Enterprise Manager project.
  3. Use Enterprise Manager to monitor itself. This feedback can help you fine-tune Enterprise Manager’s monitoring ability. For instructions on how to monitor a project in Enterprise Manager, see Selecting the projects to monitor, page 843.
  4. For additional information about every object in the Enterprise Manager project, see the object’s Long Description property (right-click the object, select Properties, and select the Long Description category). The long description includes sample reporting requirements for the object, where applicable.
  5. Install Enterprise Manager on a machine that is separate from Intelligence Server. This configuration allows you to upgrade Enterprise Manager without upgrading Intelligence Server. You can have the Enterprise Manager project in the same metadata as your other projects.
  6. To ensure that you can successfully upgrade the Enterprise Manager project in the future, do not modify schema objects. Rather, make copies of the objects you want to modify and then modify the copies.
  7. Upgrade to Enterprise Manager service packs when they become available. MicroStrategy includes your feedback in the service packs, including fixes to issues and additional enhancements.
  8. For data loading:
    1. Set up the scheduled data loads based on the answers to these questions:
      1. How long does the data load take?
      2. How current does the data need to be?
      3. If you need near-real-time data, and the data load does not take longer than a few minutes, you can run the data load as often as once per hour. However, if the data load process takes a long time, you should run it during times when Intelligence Server usage is low, such as overnight.
      4. The data load maintenance tasks may significantly impact the load on your Intelligence Server. Close open sessions, for example, requires very little overhead. MicroStrategy recommends that you run this task with every data load. In contrast, Update database statistics can increase the load dramatically. If you are running frequent data loads, this task should not be enabled by default during all data loads.
    2. When you delete a monitored project in Developer, you must also remove it from the Enterprise Manager console. Otherwise Enterprise Manager will attempt to load data from that project during data load.
    3. When you change the name of a project in Developer, you should refresh the list of available projects in the Enterprise Manager console before the next data load. Otherwise Enterprise Manager will continue to refer to the project under its old name.
    4. Synchronize the time of the Intelligence Server machine with the data warehouse time if possible. When Intelligence Server writes statistics into the database, it uses the data warehouse RDBMS timestamp. This is written as em_record_ts (in the fact tables) and as recordtime (in the statistics tables). Enterprise Manager uses recordtime to determine which statistics to move over according to the “time window” for a data load process. The time window is determined by the data warehouse RDBMS time. Also, if the data warehouse is different from the Intelligence Server machine time, certain reports in Enterprise Manager can have missing data. For example, if statistics appear for “Deleted report” in Enterprise Manager reports, it may be because statistics are being logged for reports that, according to the warehouse’s timestamp, should not exist.
  9. Configure your system for single instance session logging, so that all projects for a project source use the same statistics database. This can reduce duplication, minimize database write time, and improve performance.
  10. Use the sizing guidelines. These guidelines are only for planning purposes; MicroStrategy recommends that you monitor the size of your statistics database and adjust your hardware requirements accordingly.
    1. When the Basic Statistics, Report Job Steps, Document Job Steps, Report SQL, Report Job Tables/Columns Accessed, and Prompt Answers statistics are logged, a single user executing a single report increases the size of the statistics database by an average of 70 kilobytes. This value assumes that large and complex reports are run as often as small reports. In contrast, in an environment where more than 85% of the reports that are executed return fewer than 1,000 cells, the average report increases the size of the statistics database by less than 10 kilobytes.
    2. When the Subscription Deliveries and Inbox Messages statistics are logged, each subscription that is delivered increases the size of the statistics database by less than 100 kilobytes. This is in addition to the database increase from logging the report execution.
    3. When performance counters are logged to the statistics database, each performance counter value that is logged increases the size of the database by an average of 0.4 kilobyte. You can control the growth of this table by specifying what counters to log and how often to log each counter. For more information on logging performance counters to the statistics database, including instructions, see Recording performance counters in the statistics tables, page 347.
    4. To determine how large a database you need, multiply the space required for a report by the number of reports that will be run over the amount of time you are keeping statistics. For example, you may plan to keep the statistics database current for six months, and archive and purge statistics data that are older than six months. You expect users to run an average of 400 reports per day, of which 250, or 63%, return fewer than 1,000 rows, so you assume that each report will increase the statistics table by about 25 kilobytes. For example, 25 KB/report * 400 reports/day * 30 days/month * 6 months = 1,800,000 KB or 1.8 GB. Based on these usage assumptions, you decide to allocate 2 GB of disk space for the statistics database.
  11. Back up your statistics database regularly, and retain only as much data as is required for your analysis. You can purge the statistics database on a regular basis to decrease table size and improve performance. For instructions on how to purge statistics, see Improving database performance by purging statistics data, page 358.
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License