Configure a report for use with Bulk Export in MicroStrategy

The Bulk Export feature enables a large report to be saved as a delimited text file. Using this feature, it is possible to retrieve result sets from a large dataset without having to load the entire dataset into memory.

PS: Once a report is setup for bulk export it cannot be used as a regular report. So if the report needs to be run as a normal report and as a bulk export report, the first step is to make a copy of the report for use with bulk export.

Configure Bulk Export

Bulk Export options are only available in MicroStrategy Developer. Open a 3-tier connection using MicroStrategy Developer and edit the desired report.
Go to 'Data' on the top menu bar.
Select 'Configure Bulk Export':
Specify any additional desired configuration options.

General Settings

Bulk export database instance: This is the database instance to use to store the bulk export results. Temporary tables will be created in this database to hold the intermediate export results.

Default delimiter: How the data in the resulting text file will be delimited. Available options include: Comma, Tab, Semicolon, Colon, Space, and Other to specify another character

Advanced Settings:

Governing > Maximum time for SQL execution to build table (sec): Maximum amount of time that the SQL used to build the table may run before being terminated.

Governing > Maximum time for exporting data to file (sec): Maximum time allowed to write the completed results to file. If this fails, a timeout error will occur.

Governing > Numbers to rows retrieved in each step: Maximum number of rows to retrieve from the temp table and write to the file at a time.

Table creation properties > Table Space: The database table space in which the temporary table for the export is created.

Table creation properties > Table Prefix: Prefix to use when creating, inserting, and selecting data from the table.

SQL Statements:

Bulk export data governing > SQL to be executed prior to exporting data to file (after populating the table): SQL statements to be executed after the table is populated with all of the data, but before the data is exported to a file.

After saving and closing the report, the icon will change in MicroStrategy Developer and the report type will be 'Non Interactive':

Setup the Bulk Export Subscription

Only file based subscriptions can be used with bulk export, thus bulk exports can only be subscribed to in MicroStrategy Web.

Using MicroStrategy Web, navigate to the bulk export report, and setup a file based subscription. The options available are the same as any other file subscription that can be created:

When the subscription runs it will incrementally write data to the file based on the governing setting. The resulting file will be a text file:

Comments

OLAP features in Microstrategy

OLAP features in Microstrategy MSTR OLAP Services uses Intelligent Cube Technology—an in-memory version of report data that can 1 About MicroStrategy OLAP Services can be manipulated by the MicroStrategy Analytical Engine. MicroStrategy Desktop, Web, and Office users can slice and dice data in reports within Intelligent Cubes without having to re-execute SQL against the data warehouse. Many of the standard OLAP features that MicroStrategy provides out of the box, such as: Page-by Pivoting Sorting Subtotals Banding Aliasing Outline mode Thresholds etc.. With an OLAP Services license, user can perform additional OLAP analysis, using the following features: Displaying data on the fly: dynamic aggregation, page Creating metrics on-the-fly: derived metrics, Defining attribute elements on-the-fly: derived elements, Filtering data on the fly: view filters and metric filters, Importing data as an Intelligent Cube

Microstrategy "Error type: Odbc error. Odbc operation attempted

"Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HYT00:0: on SQLHANDLE] [MicroStrategy][ODBC Oracle Wire Protocol driver]Timeout expired" is shown when executing reports from Web When users are trying to execute some reports in MicroStrategy web in particular, they may receive the Error “SQL Generation Complete Index out of range” and “Timeout expired” error as shown below: Possible Causes: One possible cause is that the MicroStrategy Intelligence Server using a cached database connection that was already dropped by the RDBMS. To resolve this: Admin should delete the database connection caches and create a new DSNs in case they are sharing DSNs to connect to different databases. In addition, change the settings for the ‘Connection lifetime’ and the ‘Connection idle time out’. Follow the steps below to perform the mentioned changes and verify the report after each step and some of the settings require i-server r...

Logical Views to specify an outer join between two attribute lookup tables when only attributes are on a report

Logical Views to specify an outer join between two attribute lookup tables when only attributes are on a report Apart from using the VLDB properties to create the left outer join, article describes how to use the Logical View to specify an outer join between two attribute lookup tables when only attributes are on a report. This method exists as attribute only outer joins will not be generated on their own by the MicroStrategy SQL engine. This is because they are only necessary with r agged/unbalanced hierarchies which are not supported as null attribute IDs are not supported (parent elements with no child elements or child elements with no parents). Brief instructions are provided using the example below. Consider, two attributes: Parent01 and Child01 have a parent-child relationship. Their Lookup tables are defined, as follows Parent01 Child01 Note that although there are 4 ID values for the attribute Parent01, there is no defined relationship ...

Fiscal Week, Fiscal Month, Fiscal Quarter and Fiscal Year calculations in Microstrategy

Fiscal Week, Fiscal Month, Fiscal Quarter and Fiscal Year calculations in Microstrategy FiscalWeek Returns the numeric position of a week within a fiscal year, for a given input date. This function is useful in financial reporting when the start of the fiscal year is different than the start of the calendar year. Syntax FiscalWeek< firstWeekDay , firstMonth >( Date / Time ) Where: • Date / Time is the input date or timestamp. • firstWeekDay (default value is 1) is a parameter that determines which day of the week is considered as the first day of the week. You can type an integer value from 1 to 7, with 1 representing Sunday, 2 representing Monday, and so on until 7 representing Saturday. • firstMonth (default value is 1) is a parameter that determines which month is considered as the start of the fiscal year. You can type an integer value from 1 to 12, with 1 representing January, 2 representing February, and so on until ...

Settings for Outer Join between metrics in MicroStrategy

Settings for Outer Join between metrics in MicroStrategy MicroStrategy adopts multi-pass logic to determine the execution plan for a report. This means that every metric is evaluated in separate SQL passes. Outer Joins come into play when MicroStrategy Engine merges the results from all SQL passes into one report. For a multi-pass report, different Outer Join behaviors can give the user completely different results. In addition, report metrics can be of different types which can, in some cases, influence the result of the outer join. In MicroStrategy, there are two settings that users can access to control Outer Join behavior : Formula Join Type and Metric Join Type . Metric Join Type: VLDB Setting at Database Instance Level Report and Template Levels Report Editor > Data > Report Data Options Metric Level Metric editor > Tools > Metric Join Type Control Join between Metrics Formula Join Type: Only at Compound/Split...

Predictive modelling in Data Science using Microstrategy

Creating a predictive modelling in MicroStrategy MicroStrategy Data Mining Services has been evolving to include more data mining algorithms and functionality. One key feature is MicroStrategy Developer’s Training Metric Wizard. The Training Metric Wizard can be used to create several different types of predictive models including linear and exponential regression, logistic regression, decision tree, cluster, time series, and association rules. Linear and exponential regression The linear regression data mining technique should be familiar to you if you have ever tried to extrapolate or interpolate data, tried to find the line that best fits a series of data points, or used Microsoft Excel’s LINEST or LOGEST functions. Regression analyzes the relationship between several predictive inputs, or independent variables, and a dependent variable that is to be predicted. Regression finds the line that best fits the data, with a minimum of error. For example, you have a dataset ...

Custom Tooltips in Microstrategy developer and Web

Custom Tooltips in Microstrategy developer and Web The following table describes the macros you can use to customize graph tooltips in both MicroStrategy Developer and MicroStrategy Web: Macro Information Displayed {&TOOLTIP} All relevant labels and values associated with a graph item. {&GROUPLABEL} Name of the graph item's category. This value is often the graph item's attribute element information, as attributes are commonly used as the categories of graph reports. {&SERIESLABEL} Name of the graph item’s series. This value is often the graph item's metric name information, as metrics are commonly used as the series of graph reports. {&VALUE} The value of a given data point. {&XVALUE} The X-value of a data point. Only applicable to Bubble charts and Scatter plots. {&YVALUE} The Y-value of a data point. Only applicable to Bubble charts and Scatter plots. {&ZVALUE} The Z-value of a data point. Only applicable to Bubble charts and Scatter plots. {...

Microstrategy Caches explained

Microstrategy Caches Improving Response Time: Caching A cache is a result set that is stored on a system to improve response time in future requests. With caching, users can retrieve results from Intelligence Server rather than re-executing queries against a database. To delete all object caches for a project 1 In Developer, log into a project. You must log in with a user account that has administrative privileges. 2 From the Administration menu, point to Projects , and then select Project Configuration . The Project Configuration Editor opens. 3 Expand Caching , expand Auxiliary Caches , then select Objects . To delete all configuration object caches for a server 1 Log in to the project source. 2 From the Administration menu in Developer, point to Server , and then select Purge Server Object Caches . 4 Click Purge Now . To purge web cache follow the steps in the link ...

MicroStrategy URL API Parameters

MicroStrategy URL Structure The following table summarizes the root URL structure used for every request to MicroStrategy Web. Environment Main Application URL Administration URL J2EE http://webserver/MicroStrategy/servlet/mstrWeb http://webserver/MicroStrategy/servlet/mstrWebAdmin .NET http://webserver/MicroStrategy/asp/Main.aspx http://webserver/MicroStrategy/asp/Admin.aspx Every request sent to MicroStrategy Web calls a central controller. Parameters are appended to Main.aspx or mstrWeb (in a .NET and J2EE environment, respectively) to indicate to the controller how the request should be internally forwarded and handled. The following examples show a URL for accessing a MicroStrategy folder when the user does not have an existing session. The URL contains not only the parameters needed to connect to MicroStrategy Web, but also the parameters needed to log on and create a session. J2EE environment: <a href="http:...

Create a transaction services photo uploader

Create a transaction services photo uploader 1. Create a new table "photo_upload" in Tutorial warehouse database (the default location: C:\Program Files\MicroStrategy\Tutorial Reporting\TUTORIAL_DATA_7200.mdb), as shown below: 2. The 'photo_upload' table has to be pre-populated with *exactly* 10 rows of data, the values for the 'ID' column should be 1-10 and the values for the 'uploaded' column should all be 0 3. In MicroStrategy Desktop, create a freeform report "R1" based on the new table "photo_upload" in Tutorial data created at step 1, as shown below: SELECT Location, Description, ID, uploaded, numbers FROM PHOTO_UPLOAD 4. Create another table for transaction insert SQL. Make sure to create an 'autonumber' type ID as primary key for this table, or auto_increment ID for different DBs. 5. Create...

Microstrategy Analytics

Search This Blog