Job and Session

Every MicroStrategy execution will have a corresponding Job. A Job is any request to the system submitted by users from the MicroStrategy platform. The job is stored in the fact_access_transaction_view fact table. Jobs may include scheduled or ad-hoc report or document executions. Some MicroStrategy actions do not have jobs. In these cases, default values are applied. See the below chart to explain the default values.

Action Types	Default Value
All Badge Action Types	-1
History List Modifications (109, 122, 156, 157, 158, 159)	-2
Cube Modifications (161, 162, 163,)	-2
MicroStrategy Logins (100, 101, 102, 103)	-3

Parent Job​ is a result of a job triggering another child job. For example, when a document with reports as datasets is executed, it will first create a document job, which will trigger several child jobs for report execution. In this example, the job associated with the document execution is a parent job of report execution jobs. Standalone report execution will not have a parent job. 

lu_job_step_type

This table lists the Intelligence Server tasks involved in executing a report or a document. Below is list of all the possible values for Job Step. 

Column	Description		Data-Type
step_type_id	The fixed numeric ID for the document or report job type.		int(11)
step_type_desc	The Job Type that was executed against the Intelligence server. Job Types can include,  MD Object Request Close Job SQL Engine SQL Execution Analytical Engine Resolution Server Report Net Server Element Request Get Report Instance Error Message Send Output Message Send Find Report Cache Document Execution Document Send Update Report Cache Request Execute Datamart Execute Document Data Preparation Document Formatting Document Manipulations Apply View Context Export Engine Find Cube Task Update Cube Task Post-processing Task Delivery Task Persist Result Task Document Dataset Execution Task Document Process Report with Prompt Data Import Data Preparation Remote Server Execution Import Dashboards Async  Job Processing Last Step		varchar(255)

Job Step Types and Descriptions:

Job Step Type	Description
MD Object Request	Requesting an object definition from the project metadata
Close Job	Closing a job and removing it from the list of pending jobs
SQL Engine	SQL is generated that is required to retrieve data, based on schema
SQL Execution	SQL that was generated for the report is executed
Analytical Engine	Applying analytical processing to the data retrieved from the data source
Resolution Server	Loading the definition of an object
Report Net Server	Transmitting the results of a report
Element Request	Attribute element browsing
Get Report Instance	Retrieving a report instance from the metadata
Error Message Send	Sending an error message
Output Message Send	Sending a message other than an error message
Find Report Cache	Searching or waiting for a report cache
Document Execution	Executing a document
Document Send	Transmitting a document
Update Report Cache	Updating report caches
Request Execute	Requesting the execution of a report
Datamart Execute	Executing a datamart report
Document Data Preparation	Constructing a document structure using data from the document’s datasets
Document Formatting	Exporting a document to the requested format
Document Manipulation	Applying a user’s changes to a document
Apply View Context	Reserved for future use
Export Engine	Exporting a document or report to PDF, plain text, Excel spreadsheet, or XML
Find Cube Task	The cube instance is located from the Intelligent Cube Manager, when a subset report, or a standard report that uses dynamic caching, is executed.
Update Cube Task	The cube instance is updated from the Intelligent Cube Manager, when republishing or refreshing a cube.
Post-processing Task	Reserved for future functionality.
Delivery Task	Used by Distribution Services, for email, file, or printer deliveries of subscribed-to reports/documents.
Persist Result Task	Persists execution results, including History List and other condition checks. All subscriptions hit this step, although only subscriptions that persist results (such as History List) perform actions in this step.
Document Dataset Execution Task	A virtual task only used for statistics manager and enterprise manager to record the time that spend on dataset execution.
Document Process Report with Prompt	Will be triggered after SQL Engine step discovers prompts, collect unanswered prompts and present them to client.  After get answers launch jobs to execute these dataset which contains unanswered prompts.
Data Import Data Preparation Task	This task prepares the data for multiple tables in data import cubes.
Remote Server Execution Task	Direct access on remote MSTR project
Import Dashboards Async Task	Asynchronous Import of Dashboards

fact_step_sequence_view

This table is used when the Document and/or Report Job Steps option is enabled for Advanced Statistics logging via Command Manager. It stores information on each processing step of a document/dashboard/report execution. It is best used for troubleshooting the performance of an object at the job level. 

There are five facts sourced from this table:

• Job Step Start Timestamp (UTC) - the timestamp (in UTC timezone) when the Job Step begins.

• Job Step Finish Timestamp (UTC) - the timestamp (in UTC timezone) when the Job Step finishes.

• Job Queue Duration (ms) - the fact calculates the time spent waiting in queue for the job to be executed in milliseconds.
• Job CPU Duration (ms) - the time spent on CPU during the job execution in milliseconds. 

• Job Step Duration (ms) - the total execution time for the job execution in milliseconds.

Column	Description	Data-Type
parent_tran_id	The auto-generated numeric action ID.	bigint(20)
step_sequence_id	The sequence number ID for each job’s steps. Used to determine which order the steps were taken on the Intelligence server.	int(11)
step_type_id	The numeric ID of the document/dashboard/report job execution job step type.	int(11)
step_start_timestamp	The UTC timestamp when the job step started.	datetime
step_finish_timestamp	The UTC timestamp when the job step finished.	datetime
job_queue_time	The Queue duration in milliseconds.	bigint(20)
job_cpu_time	The CPU duration in milliseconds.	bigint(20)
step_duration_time	The total execution duration time in milliseconds.	bigint(20)

lu_session_view 

Each user that connects to the MicroStrategy Intelligence server and/or project has a unique ​Session connection GUID. A user cannot log in to a project without first having a session to the Intelligence server. However, a user can have a session to the Intelligence server without connecting to a project (i.e. performing administrative task in Developer). The lu_session_view table tracks the unique session connection information at the project and metadata level. 

For each unique user ​Session​ that is created, there will be an ​Intelligence Server Instance,​ a​ Session Source​, a ​Client Server Machine, ​and a ​Device​. 

Column	Description	Data-Type
session_id	The auto-generated numeric ID value for each unique session.	bigint(20)
session_guid	The GUID of the Session.	varchar(32)
iserver_instance_id	The numeric ID of the Intelligence Server Instance that was connected to the session. Not all sessions connections have an applicable I-Server Instance. For example, scheduled jobs.	bigint(20)
client_server_machine_id	The Client Server Machine IP that was connected to for the session. Not all session connections have a client server machine.	bigint(20)
session_source_id	The ID of the session source that was used to establish the user session connection.	bigint(20)
metadata_id	The metadata ID for which the user session was connected.	bigint(20)
device_id	MicroStrategy actions (executions, session, etc.), it is the IP address of the machine from which the session was created.	bigint(20)
connection_time	The timestamp of when the session was opened. The mapping of this column to the Platform Analytics project schema is pending.	datetime

lu_session_source

Each ​Session​ that is created as a user connection to the Intelligence server and Project has a source. The Session Source ​represents the client or tool that the user used to establish a connection.

Column	Description	Data-Type
session_source_id	The fixed numeric ID value for the Session Source.	bigint(20)
session_source_desc	The specific Session Source that was used to connect to the Intelligence server and/or Project. The Session Source can be: 0 Not Applicable 1 Developer 2 Intelligence Server Administrator 3 Web Administrator 4 Intelligence Server 5 Project Upgrade 6 Web 7 Scheduler 8 Custom Application 9 Narrowcast Server 10 Object Manager 12 Odbo Cube Designer 13 Command Manager 14 Enterprise Manager 15 Command Line Interface 16 Project Builder 17 Configuration Wizard 18 MD Scan 19 Cache Utility 20 Fire Event 21 Java Admin Clients 22 Web Services 23 Office 24 Tools 25 Portal Server 26 Integrity Manager 27 Metadata Update 28 COM Browser 29 Mobile 30 Repository Translation Wizard 31 Health Center 32 Cube Advisor 34 Desktop 35 Library 36 Library iOS 37 Workstation 39 Library Android 40 Workstation MacOS 41 Workstation Windows 42 Desktop MacOS 43 Desktop Windows 44 Tableau 45 Qlik 46 PowerBI 47 Microsoft Office 48 Hyper Browser Chrome 49 Hyper Mobile iOS 50 Hyper Mobile Android 51 Hyper Office Outlook Web 52 Hyper Office Outlook Windows 53 Hyper Office Outlook Mac	varchar(255)

lu_sql_pass_type

This table stores the static list of ​SQL Pass Types​. Each SQL Pass that is recorded in the fact_sql_stats table will have a corresponding SQL Pass Type. 

Column	Description		Data-Type
sql_pass_type_id	The fixed numeric ID for the SQL Pass Type.		int(11)
sql_pass_type_desc	The descriptive name for the SQL Pass Type. The SQL Pass Type can include: Select Insert Into Select Create Table Analytical Select Into Insert into Values Homogeneous Partition Query Heterogeneous Partition Query Metadata Partition Pre-Query Metadata Partition Last Pre-Query Empty Create Index Metric Qualification Break By Metric Qualification Threshold Metric Qualification User Defined Homogeneous Partition Loop Homogeneous Partition One Table Heterogeneous Partition Loop Heterogeneous Partition One Table Insert Fixed Values Into Datamart From Analytical Engine Cleanup Temp Resources Return Element Number Incremental Element Browsing MDX Query Sap Bapi Intelligent Cube Instruction Heterogeneous Data Access Excel File Data Import Text File Data Import Database Table Data Import SQL Data Import Data Import Excel File Data Import Text File Data Import Table Data Import Custom SQL Data Import OAuth Data Import Open Refine SQL Incremental Data Transfer Data Import Cube From File		varchar(255)

lu_sql_clause_type

This table stores the static list of SQL Clause Types. Each SQL Pass that is recorded in the fact_sql_stats table will have a corresponding SQL Clause Type.

Column	Description	Data-Type
sql_clause_type_id	The fixed numeric ID value for the SQL Clause Type.	smallint(6)
sql_pass_type_desc	The descriptive name for the SQL Clause Type. The SQL Pass Type can be, 0 Not Applicable 1 Select 2 Select Group By 4 Select Aggregate 8 From 16 Where 17 Order By	varchar(255)

fact_sql_stats

This table contains the ​SQL Pass​ information that is executed on the warehouse during a report job executions. Each SQL Pass is recorded at the Parent Action​ level and one action​​ can correspond to multiple SQL​ Passes. 

One report execution (Parent Action) can have multiple ​SQL Pass Sequences​.

This fact table is best used for performance analysis of reports execution times to determine inefficient report definitions. Data will be available only when the Advanced statistics option is enabled during configuration in Command Manager.

The fact_sql_stats table is the source for the facts listed below:

• SQL Pass Duration (ms)​ - records the SQL Pass execution duration in milliseconds.
• SQL Pass End Timestamp ​- records the UTC timestamp when the SQL Pass finishes.
• SQL Pass Start Timestamp​- records the UTC timestamp when the SQL Pass begins.
• SQL Pass Tables Accessed​ - records the number of tables hit during the SQL pass.  

Column	Description	Data-Type
parent_tran_id	The auto-generated transaction ID for each report that is executed on the warehouse. Each Parent Action can correspond to multiple SQL Pass.	bigint(20)
sql_pass_id	The auto-generated SQL Pass ID for each execution. This is the primary key on the table.	bigint(20)
sql_pass_sequence_id	The sequence number of the SQL pass.	int(11)
sql_pass	The exact SQL used in the pass.	longtext
sql_start_timestamp	The UTC timestamp when the SQL Pass began.	timestamp
sql_end_timestamp	The UTC timestamp when the SQL Pass finished.	timestamp
sql_pass_type_id	The numeric ID corresponding to the SQL Type. For example,  Create Index Insert Into Values Incremental Element Browsing	int(11)
execution_time	The total time spent on the SQL Pass Statement. Defined as the start timestamp minus the end timestamp.	bigint(20)
total_tables_accessed	The number of tables hit by the SQL pass. This is the source column for the SQL Pass Tables Accessed fact.	smallint(6)
db_error_id	The auto-generated error ID for a DB error encountered during SQL execution. See lu_db_error for more details.	bigint(20)

lu_db_error

This table stores the list of Database Error Messages. Each SQL Pass that is recorded in the fact_sql_stats table will have a corresponding db_error_id.

Column	Description	Data-Type
db_error_id	The auto generated id for the database error.	bigint(20)
db_error_desc	The full text of the database error message returned from the server.	Varchar(4096)

fact_report_columns

Column	Description	Data-Type
parent_tran_id	The auto-generated parent transaction ID for each report that is executed on the warehouse.	bigint(20)
column_id	The auto-generated column ID that was hit during that report execution.	bigint(20)
sql_clause_type_id	The SQL Clause Type ID that corresponds to which type of SQL clause was executed against the given column / table. See lu_sql_clause_type for more details.	smallint(6)
table_id	The auto-generated Table ID that the SQL statement was run against. This is the source column for the Database Table attribute. See lu_db_table_view for more details.	bigint(20)
column_hit_count	The number of times the column/table/clause type combination occurs within an execution. This is the source column for the Column Hit Count fact.	int(11)