Usage Scenarios and SQL Standards

How to use the Freeform SQL feature effectively depends on your work environment. As with every other MicroStrategy functionality, before you start using this feature, you need to assess your particular work situation and find a way to strike a good balance between project maintainability and fast-paced development. For example, building three or four Freeform SQL reports could be valuable, but building 100 such reports could make maintenance and testing difficult.

Whether you should use the Freeform SQL feature to build reports at all is another question that you should ask yourself. You may want to consider using this feature if you are in one of the situations discussed below. For information on using the Freeform SQL Editor to report on third-party web services, see Reporting on Third-Party Web Services with XQuery.

When to Use the Freeform SQL Feature

If your company is accustomed to creating static reports using customized SQL to retrieve data from a certain ODBC data source, and especially if your SQL queries have worked well in the past, then you may want to simply use MicroStrategy to deploy those reports to your users. There is no need to recreate the SQL with the MicroStrategy Engine, as is done if the data is moved to a data warehouse for report generation. Freeform SQL allows you to use your own SQL statements rather than generating new and possibly different SQL statements.

If you have existing stored procedures that have proven to be successful, then you can continue to use them to generate MicroStrategy reports. One important thing to note is that you must know what data the stored procedure is supposed to retrieve because this information is essential in building a Freeform SQL report. Specifically, you need to know the number of columns, column names, and their data types, all of which are necessary for mapping the columns to MicroStrategy objects.

Another situation for which you might want to use Freeform SQL reporting is when you need to run queries against a set of OLTP tables that are not set up for OLAP analysis. Data warehouses that do not lend themselves well to MicroStrategy's attribute and fact schema can be accessed with Freeform SQL and mapped to new MicroStrategy objects automatically created by the Freeform SQL feature. As for all the Freeform SQL reports, connecting to the desired ODBC data source is a prerequisite.

Freeform SQL can also be a helpful tool to perform database maintenance tasks such as updates and inserts. For information on this scenario see Using Freeform SQL for Updates, Inserts, and Other Database Maintenance Tasks.

SQL Query Syntax

Well-written SQL queries are the key to building successful Freeform SQL reports. To take full advantage of the Freeform SQL feature, MicroStrategy recommends that you ensure the correctness and validity of your SQL statements before creating any reports using Freeform SQL. MicroStrategy does not offer consultation or technical support for the syntax or composition of your SQL queries.

Depending on your needs, you can compose SQL statements in several ways:

Create new SQL statements from scratch.
Use existing SQL statements that you previously defined, which have proven to be successful in terms of retrieving data from the data source.
This includes using stored procedures.
Tune the MicroStrategy Engine-generated SQL by modifying it to suit your needs.
This means you can re-use the Engine-generated SQL by changing some of its clauses or syntax to get a different result set.
You cannot tune the Engine-generated SQL that involves the use of the Analytical Engine. Typically, the Analytical Engine comes into play for metric qualifications using analytical functions (such as OLAP functions), custom group banding, use of the plug-ins, and so on. If the Analytical Engine is used for any part of the SQL during the report generation, that part is labeled as "[An Analytical SQL]" in the report SQL.

All MicroStrategy functions, including the ones that use the Analytical Engine, are described in detail in the Functions Reference.

SQL Support

With the Freeform SQL feature, you can use both single-pass and multi-pass SQL queries to generate reports. However, you must convert your multi-pass SQL statements into derived table or common table expression syntax that can be completed in a single pass.

Derived table and common table expressions can have only one SELECT clause in the SQL query. This means that a report SQL statement with Pass 1, Pass 2, Pass 3, and so on, as can be found in many MicroStrategy Tutorial reports, cannot yield the desired report, unless you modify the query using derived tables or common table expressions. Check the database that you use to ensure that derived tables, common table expressions, or both are supported.

For example, if you have the following multi-pass SQL statement, it needs to be converted to derived table or common table expression syntax shown below.

Multi-Pass SQL

create table MQ00(

	A1 INTEGER,

	B1 INTEGER)

insert into MQ00

select a11.[A1] AS A1,

	a11.[B1] AS B1

from [T1] a11

	where (a11.[M1] > 0);

select a11.[A1] AS A1,

	a11.[B1] AS B1,

	a12.[B2] AS B2,

	a11.[M1] AS M1

from [T1] a11,

	[MQ00] pa1,

	[T2] a12

where a11.[A1] = pa1.[A1] and

	a11.[B1] = pa1.[B1] and

	a11.[B1] = a12.[B1]

drop table MQ00

Equivalent Derived Table

select a11.[A1] AS A1,

		a11.[B1] AS B1,

		a12.[B2] AS B2,

		a11.[M1] AS M1

from [T1] a11,

		(select a11.[A1] AS A1,

			a11.[B1] AS B1

		from [T1] a11

		where a11.[M1] > 0) pa1,

		[T2] a12

where a11.[A1] = pa1.[A1] and

		a11.[B1] = pa1.[B1] and

		a11.[B1] = a12.[B1]

Equivalent Common Table Expression

with pa1 as

(select a11.[A1] AS A1,

		a11.[B1] AS B1

		from [T1] a11

		where a11.[M1] > 0)

select a11.[A1] AS A1,

		a11.[B1] AS B1,

		a12.[B2] AS B2,

		a11.[M1] AS M1

from [T1] a11,

	pa1,

	[T2] a12

where a11.[A1] = pa1.[A1] and

	a11.[B1] = pa1.[B1] and

	a11.[B1] = a12.[B1]

Freeform SQL Reports vs. Standard Reports

A Freeform SQL report is simply a standard report, which has been built using the Freeform SQL feature. Due to some variations in how standard reports and Freeform SQL reports are built, there are also some varying functionalities between the two.

You can create Freeform SQL reports using your own SQL queries against a data warehouse, or from an Excel file, or a flat text file. Information on how to create these reports is provided later in this section. Although Freeform SQL reports can only be created on MicroStrategy Developer, once created, they can be executed from both MicroStrategy Developer and MicroStrategy Web like any other MicroStrategy standard reports. Functions that you can perform on MicroStrategy standard reports can also be performed on Freeform SQL reports, including the following:

Sorting
Auto-styles
Caching
Exporting
Thresholds
Graphing
Filtering
Value prompts
Shortcut-to-a-report qualification. For more information on using a Freeform SQL report as a shortcut-to-a-report qualification, see Reporting Analysis Features.
Narrowcast Server subscriptions and report execution
Object security
OLAP services
Prioritization
Report Services documents
Scheduling
Subtotals

The following features are available for use with Freeform SQL reports if an attribute and fact schema is used to model the data returned from your queries:

Element list prompts
Security filters

The following features do not apply to Freeform SQL reports:

Custom groups
Consolidations
Transformations
Existing filters
Save as template/filter
Data marting

Freeform SQL Reports in Report Services Documents

Once created, Freeform SQL reports can be included in Report Services documents in the same way as standard reports. The same document can also contain reports from other data sources, such as MDX cube reports. For information regarding MDX cube reports, refer to the MDX Cube Reporting Help.

For data to be joined across different data sources in a document, a common attribute is needed across the datasets. In the following diagram, the common attribute which appears on each of the three reports is named A1.

You can establish a common attribute by mapping objects, such as attributes and prompts, retrieved from different data sources to existing objects in the MicroStrategy environment. Information on mapping columns for Freeform SQL reports is provided in Access and Analyze Multiple Data Sources with Freeform SQL.

For example, in a Report Services document, you have three datasets from three reports: one standard MicroStrategy report, one Freeform SQL report, and one MDX cube report using SAP BI as the MDX cube source. All three reports use the same attribute, Product. This means that Product is used in the standard report as a project attribute, the Freeform SQL report has one object mapped to Product, and the MDX cube report uses Product to map one of its characteristics from the imported SAP BI query cube. Because data is joined by the common attribute Product, the document is generated successfully.

If each of the three reports originally has a prompt on Product, then the prompt is only displayed one time when a user executes the document; this means a user only needs to answer the prompt one time, instead of three times.

Using Freeform SQL for Updates, Inserts, and Other Database Maintenance Tasks

While Freeform SQL is designed to return data from a data source to be displayed on a report, it can also be used for database maintenance tasks such as updates and inserts. This can be achieved because Freeform SQL allows you the flexibility to create the SQL statements that are run against your database.

Database maintenance actions such as update, insert, and create table actions do not return any results. By default, Freeform SQL reports return a warning when no result is returned. If you routinely use Freeform SQL for these purposes, you can hide these warnings since an empty result set is expected. The procedure below shows you how to disable these warnings and use Freeform SQL for database maintenance tasks.

To Use Freeform SQL for Updates, Inserts, and Other Database Maintenance Tasks

In MicroStrategy Developer, choose File > New > Report. The New Grid dialog box opens.
On the Freeform Sources tab, select Create Freeform SQL report.
In the Source area, select a database instance for the database to access using Freeform SQL. For information on connecting to databases, see Connect to Databases.
Click OK. The Freeform SQL Editor opens.
Create the SQL statement for the database maintenance task.
Click OK. The Report Editor opens.
Choose Data > VLDB Properties. The VLDB Properties Editor opens.
In the VLDB Settings list, expand the Freeform SQL folder, and select Ignore Empty Result for Freeform SQL.
Clear the Use default inherited value check box.
Select Turn off warnings for Freeform SQL statements with empty results, such as updates to hide all warnings when a Freeform SQL statement causes a Freeform SQL report to return an empty result.
This prevents users from seeing a warning every time a SQL statement to perform a database maintenance task is executed using Freeform SQL.
Click Save and Close.
From the toolbar, click the Run Report icon to execute the Freeform SQL report.