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

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:...

Case functions Microstrategy

Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned. Case can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 , ReturnValue1 , Condition2 , ReturnValue2 ,..., DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV evaluates a single metric and returns different values according to the results. It can be used to perfo...

Apply or Pass-through functions in Microstrategy

Ap ply (Pass-Through) functions MSTR Apply functions provide access to functions or syntactic constructs that are not standard in MicroStrategy but are provided by various RDBMS systems.. Syntax common to Apply functions Apply Function Name ("expression with placeholders", Arg1, Arg2, Arg3, …ArgN) where: Apply Function Name – is a generic name used for the predefined pass-through functions described above expression with placeholders – is the string describing the actual expression or syntax that the engine uses while generating the SQL and which is sent to the RDBMS. The placeholders are represented by #0, #1, and so on. "#" is a reserved character for MicroStrategy. Arg – is an argument that replaces the parameter markers in the pattern. Arg1 replaces #0, Arg2 replaces #1, and so on. There are five pre-defined Apply functions to replace regular, predefined functions of the same type. For more details, cli...

Microstrategy Dossiers explained

Microstrategy Dossiers With the release of MicroStrategy 10.9, we’ve taken a leap forward in our dashboarding capabilities by simplifying the user experience, adding storytelling, and collaboration.MSTR has evolved dashboards to the point that they are more than dashboards - they are interactive, collaborative analytic stories . Ultimately, it was time to go beyond dashboards, both in concept and in name, and so the've renamed VI dashboards to ‘ dossiers ’. Dossiers can be created by using the new Desktop product or Workstation or simply from the Web interface which replaces Visual Insights. All the existing visual Insights dashboards will be converted to Dossiers With MicroStrategy 10.9, there was an active focus on making it easier to build dashboards for the widest audience of end users. To achieve this, some key new capabilities were added that make it easier to author, read, interact and collaborate on dashboards ...

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...

Microstrategy Document Autotext macros:

Autotext code/macros in Microstrategy Document/dashboard This is a list of the available auto text macros that the Report Services Document engine recognizes. The following auto text codes allow you to add document variable information to your document. These auto text codes are automatically replaced by information about the document. Auto text codes for MSTR document/dashboard: AUTOTEXT DESCRIPTION {&PAGE} Display the current page. {&NPAGES} Display the total number of pages. {&DATETIME} Display the current date and time. {&USER} Display the user name that is executing the Report Services Document. {&DOCUMENT} Display the document name. {&DOCUMENTID} Display the document ID. {&DESCRIPTION} Display the document description. {&PROJECT} Display the project name. {&EXECUTIONTIME} Dis...

Types of prompts in Microstrategy

Types of prompts in Microstrategy The different types of prompts allow you to create a prompt for nearly every part of a report. Prompts can be used in many objects including reports, filters, metrics, and custom groups, but all prompts require user interaction when the report is executed. The correct prompt type to create depends on what report objects you want users to be able to base a filter on to filter data, as described in the list below. Filter definition prompts allow users to determine how the report's data is filtered, based on one of the following objects: Attributes in a hierarchy : Users can select prompt answers from one or more attribute elements from one or more attributes. The attribute elements that they select are used to filter data displayed on the report. This prompt lets you give users the largest number of attribute elements to choose from when they answer the prompt to define their filtering criteria. For example, on a repor...

Star schemas with MicroStrategy SQL Generation

Considerations for the use of star schemas with MicroStrategy SQL Generation The primary characteristic of star schema is its use of dimension tables rather than single-attribute lookup tables. For example, a Time dimension in a star schema may be supported by a dimension table with the following structure: DAY_ID DAY_DESC MONTH_ID MONTH_DESC QUARTER_ID QUARTER_DESC YEAR_ID By contrast, a snowflake schema has a separate lookup table for each level of a dimension. In a fully normalized snowflake schema, each lookup table contains only its attribute's ID and description columns, and the ID of its parent to facilitate joins up the hierarchy. Lookup tables may also be partially denormalized, in which ID columns of all parents in the dimension are included, or fully denormalized, in which IDs and descriptions of all parents are included. Fully normalized snowflake schema: DAY_ID DAY_DESC MONTH_ID MONTH_ID MONTH_DESC QUARTER_ID QUARTER_ID QUARTER_...

Microstrategy document/dashboard applying selections as filters or slices

Applying selections as filters or slices In a Microstrategy Document the selections a user makes in a selector can either filter or slice the data in the target: Filtering means that the data for the current selection is calculated only when it is requested by the user. The selections are used to filter the underlying dataset before the metric values are aggregated at the level of the Grid/Graph that is displayed in the document. If the source attribute is not included in the Grid/Graph, the metric values from all the selected elements are aggregated and shown at the level specified in the Grid/Graph. All metric condition selectors (which filter metric values or ranks) and selectors that target other selectors filter data by default. You cannot change them to slicing selectors. Slicing means that the data for each available item in the selector is calculated in advance when the document is first displayed. Selections made while ...

Derived metric based on attribute values

Derived metric based on attribute values Here is how could create and display data correctly on using below simple steps. Create a report with Category, Subcategory and Revenue. Create New Metric in a report or VI. Case((Category@ID = 1), Revenue, 0) Booksand Name it as Revenue for where 2 is Category ID for "Books" Report will display result as below. Result for new metric is blank. Now to fix this create a new Derived metric on Category attribute first with formula as Max(Category) {~ } and calling Books Now Edit the "Revenue for Books metric and Replace Category@ID with this new Books metric formula would looks like this Case((Books = 1), Revenue, 0). Report result would now display as expected as shown below

Microstrategy Analytics

Search This Blog