MICROSTRATEGY TUTORIALS

Introduction
The Freeform SQL functionality adds great flexibility to
MicroStrategy’s query and reporting capabilities.
Traditionally, you use the MicroStrategy Engine to generate
SQL to run against one specific relational database to get a
desired report. Starting from 8.0, in addition to generating
reports in the traditional way, you can also use your own
customized SQL statements to generate reports from
operational systems included in a MicroStrategy project. This
capability could save you a tremendous amount of work time
since you do not need to place the data into a data mart or
data warehouse first.

The following graphic is an illustration of the Freeform SQL
Editor, where you define the SQL statement for the report.
Notice the different panels for different purposes. For details
about these panels, please refer to the online help (search for
the “Using the Freeform SQL Editor” topic).





Freeform SQL reporting
The Freeform SQL reporting feature allows you to use your
own SQL statements to access data from various data
sources, including relational databases, Excel files, and flat
files, as long as they are included in the MicroStrategy
environment. Details on how to create Freeform SQL reports
from these data sources are discussed in this chapter as well
as in the online help (search for the “Creating Freeform SQL
reports” topic).
How to use the Freeform SQL reporting 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 very
valuable, but building 100 such reports could make
maintenance and testing very difficult.
Whether you should use the Freeform SQL reporting feature
at all is another question that you should ask yourself. Most
likely, you may want to consider using this feature if you are
in one of the situations discussed as follows.


When should I use the Freeform SQL reporting feature?
If your company is used to creating static reports using
customized SQL to retrieve data from a certain 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.
In the same spirit, 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 exact 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. For examples on different
databases, please see the online help topic “Creating
Freeform SQL reports using stored procedures”.
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. As for
all the Freeform SQL reports, connecting to the right data
source is a prerequisite.


SQL query syntax
Well-written SQL queries are the key to building successful
Freeform SQL reports. To take full advantage of the Freeform
SQL reporting feature, MicroStrategy recommends that you
ensure the correctness and validity of your SQL statements
before creating any such reports. 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, if you have not
created any before
• use existing SQL statements that you previously defined,
which have proven to be successful in terms of retrieving
data from the data source
• tune the MicroStrategy Engine-generated SQL by
modifying it to suit your needs
This means you can reuse the Engine-generated SQL by
changing some of its clauses or syntax to get a different
result set

Note that you cannot tune the Engine-generated SQL that
involves the use of the Analytical Engine. Typically, the
Analytical Engine comes into play for metric qualification
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


SQL support
With the Freeform SQL reporting feature, you can use both
single-pass and multi-pass SQL queries to generate reports.
Make sure that you use derived table or common table
expression syntax.
If you have to use derived table or common table expressions,
then you can have only one SELECT clause in your SQL
query. This means that a report SQL statement with Pass 1,
Pass 2, Pass 3..., 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. Please check the database that you use to ensure
that derived tables or common table expressions or both are
supported.


Freeform SQL reports vs. standard reports
You can create Freeform SQL reports using your own SQL
queries against a data warehouse, or from an Excel file, or a
flat file (information on how to create these reports is
provided later in this chapter). Although Freeform SQL
reports can only be created on Desktop, once created, they
can be executed from both Desktop and the Web like any
other standard reports. Functions that apply to the execution
and manipulation of standard reports also apply to Freeform
SQL reports, including the following
• formatting
• exporting
• thresholds
• graphing
• Narrowcast Server subscriptions and report execution
• object security
• OLAP services
• prioritization
• Report Services documents
• scheduling
• subtotals
• Web report execution
However, the following features do not apply to Freeform
SQL reports:
• custom group
• consolidation
• transformation
• filter
• save as template/filter
• report as filter
• data marting


Filters
A filter specifies the conditions that the data must meet to be
included in the report results. For information on Filters in
general, please refer to Chapter 5, Filters, in this guide.
You cannot use existing filters in a Freeform SQL report;
however, you can still do filtering by including a Where
clause in the SQL statement. You can even embed prompt
objects in the Where clause, if needed. For example,
where Year_ID=[Year_prompt]

Only two kinds of prompts can be used: value prompts
and element list prompts. For more information,
please refer to the Prompts subsection in this chapter.
In addition, you can use the view filter functionality for
Freeform SQL reports in the same way as for regular reports.


Prompts
A prompt is a MicroStrategy object that allows user
interaction at report run time. For general information on
prompts, please refer to Chapter 9, Prompts, in this guide.
For Freeform SQL reports, only two types of prompts are
supported—value prompts and element list prompts. To add
prompts, you can select from the two options on the Edit
menu in the Freeform SQL Editor:

• Add New Prompt: launches the Prompt Wizard that
allows you to create a new value prompt or an element list
prompt.

Note the following:
– Only project attributes can be used to create prompts
in Freeform SQL reports.
– Any prompt created this way is saved as a normal
object in the metadata.
• Insert Prompt: displays the Open dialog box where you
can select an existing prompt that you have previously
created in the project, either a value prompt or an element
list prompt.

You cannot type the name of an existing prompt
directly into the SQL statement.
Once you exit the Prompt Wizard or the Open dialog box, the
prompt is inserted automatically into the SQL statement at
the current cursor position. If an area in the SQL statement is
highlighted, it is replaced by the prompt name. Prompt
objects appear in the SQL statement in pink and are enclosed
in brackets ([ ] ) if the name of the prompt contains any
space, for example, where Year_ID = Year_prompt and
where Year_ID = [Year prompt].



Drilling
Drilling allows you to look at specific data at levels other than
what is originally displayed on the grid or graph. For regular
reports, you can drill in different directions, such as down,
up, or across attributes, based on the system-generated drill
maps or custom drill maps. For Freeform SQL reports,
support for drilling is limited to only attributes within the
Intelligent Cube.

This functionality is controlled by the “Drill with
Intelligent Cube” privilege.
For example, a report has the Year and Quarter attributes.
When you move Quarter off the report and into the Object
Browser, you can only drill down from Year to Quarter on the
report. However, if both attributes are placed on the report,
you cannot drill from either one of them.

If you move Year off the report and into the Object
Browser, you cannot drill up from Quarter to Year on
the report.


Managed objects
A managed object is just like a normal object except that it is
managed by the system and is stored in a special system
folder. When you create a Freeform SQL report, you can use
existing attributes (including project attributes) to map the
columns in the SQL statement in the Freeform SQL Editor.
Alternatively, you can create new attributes, attribute forms,
and metrics on the fly to which to map the columns in the
SQL statement; these new objects are managed objects.
You can find out whether the object (attribute or metric) is a
managed object or not by using the Properties dialog box. If it
is a managed object, the Location field on the General tab
indicates “Managed by the system” (see the graphic below).




Creating Freeform SQL reports
Freeform SQL reports Freeform SQL reports can be created
on Desktop only. However, these reports can be manipulated
and executed from both Desktop and the Web. This section
describes the process of creating a Freeform SQL report
• from a database
• from an Excel file
• from a flat file
• using a stored procedure

Creating a Freeform SQL report from a database
The process of creating a Freeform SQL report from a
database involves the following general steps:
1 On Desktop, create a database instance for the data source
that the Freeform SQL report will run against.

You must create a database instance before
defining a Freeform SQL report.
2 Make the database instance available for Freeform SQL
reports (in the Project Configuration Editor, select
Project Definition, then Database Instances, and then
the database instance name from the Database Instance
list).
3 Create a Freeform SQL report by selecting New from the
File menu and then Freeform SQL. The Freeform SQL
Editor is displayed.

Access to the Freeform SQL Editor is available only
to Desktop Designers with the “Use Freeform SQL
Editor” privilege and those with the “Create
schema objects” Common Privilege.
4 Below the toolbar, from the database instance drop-down
list, select the database instance against which the
Freeform SQL report is set to query.
5 Type your SQL statement in the SQL statement panel.
6 In the Mapping panel, map the columns in the SQL
statement to MicroStrategy objects (attribute forms and
metrics).

The number of mappings should match the
number of columns in the SQL statement.
7 Insert prompts into the SQL statement, if needed.
8 Insert security filters, if needed.
9 Click OK to exit the Freeform SQL Report Editor. The
Report Editor is displayed.
10 Define the Freeform SQL report in the same way as you
define a standard report, using features such as
formatting, sorting, view filters, thresholds, exporting,
and so on.
11 Save the Freeform SQL report.

You must save the report first before you can run it.
12 Run the report.


Creating a Freeform SQL report from an Excel file
The Freeform SQL reporting feature also allows you to create
reports using data from Excel files. The creation process
involves the following steps.
Create a table with the Excel file
1 Prepare the Excel file.
– Make sure that all the columns with data have proper
headers: no space in the header name (for example,
Category_ID, not Category ID) and are alphanumeric,
starting with alphabets (for example, M2004Q1, not
2004Q1M).
– Make sure that all cells for the ID column have value in
them (not empty).
2 Create a table by doing the following:
– Highlight the rows and columns with the data that you
want to create a report with, including the column
headers, such as Category_ID and Category_DESC.

Do not use the column headings (at the top of the
Excel spreadsheet, marked as A, B, C...) to select
the whole column because doing so may include
numerous empty cells with NULL value.
– Type a name for the highlighted part (rows and
columns) in the name box, and then press ENTER.

You may create multiple tables in one Excel file by
highlighting different parts of the file and naming
them differently.
3 Save the Excel file with a name.

Make sure that the file is not password-protected.

Set up the data source (ODBC)
1 From the Control Panel, select Administrative Tools and
then Data Sources (ODBC). The ODBC Data Source
Administrator dialog box is displayed.
2 Select the System DSN tab, and then click Add. The
Create New Data Source dialog box is displayed.
3 Select the Microsoft Excel Driver if you are using the
Windows platform, and then click Finish. The ODBC
Excel Setup dialog box is displayed.
4 Enter a Data Source Name (DSN) in the space provided.
5 Click Select Workbook. The Select Workbook dialog box
is displayed.
6 Under Database Name, select the Excel file that you saved
and named in Step 1, Prepare the Excel file.
7 Click OK to close the Select Workbook dialog box and
return to the ODBC Excel Setup dialog box.
8 Click OK to return to the ODBC Data Source
Administrator dialog box.
9 Click OK. The ODBC data source is set up.
You can then use the MicroStrategy ODBC Test Tool to test if
data can be retrieved from the table(s) you created from the
Excel file.

Make sure to select the correct DSN for the Excel file
for the test.

Create a database instance for the Excel file
1 On MicroStrategy Desktop, create a new database
instance that points to the DSN for the Excel file.
2 Make the new database instance available for your
Freeform SQL report.
In the Project Configuration dialog box, select Project
Definition, then Database Instances, and then choose
the DSN for the Excel file from the available database
instances list.


Create a Freeform SQL report from the Excel file
1 From the File menu on Desktop or right-click anywhere in
the right panel on Desktop, select New and then Report.
2 In the New Grid dialog box, select Freeform SQL.
3 In the Freeform SQL Editor, from the Database Instance
drop-down list, select the database instance you created
previously (see the “Create a database instance for the
Excel file” subsection).
4 In the SQL Statement panel, type in your SQL query.
5 In the Mapping panel, map the columns in your SQL
statement to attributes and metrics to be used in the
MicroStrategy report.
6 Click OK to close the Freeform SQL Editor. The Report
Editor opens in Design view by default.
7 Format the Freeform SQL report as you do with a
standard report.
8 From the File menu, click Save or Save As.

You must save the report before you can run it.
Otherwise, you will see a Desktop message saying
that the report “cannot be executed unless it is
saved.”
9 In the Save Report As dialog box, enter a name for the
report.
10 Run the Freeform SQL report.

Note the following:
– Column names in the SQL statement have to match
the column headers in the Excel file.
– Case does not have to match, as long as the column
names are correct.
– Do not use the Excel file name as the table name for
the “From” clause. Use the table name instead.
Remember that the Excel file is the data source that
contains the “tables”.
5 In the Mapping panel, map the columns in your SQL
statement to attributes and metrics to be used in the
MicroStrategy report.


Creating a Freeform SQL report from a text file
The Freeform SQL reporting feature also allows you to create
reports using data from text files. The creation process
involves the following steps:
Prepare the text file for MicroStrategy use
1 Make sure that the file type is text file (.txt).
2 Select a correct delimiter, for example, comma.
3 Make sure that field (column) names appear in the first
row of the file and are delimited.
4 Save the text file in a folder, which will be used as the data
source for MicroStrategy reports.
Set up the data source (ODBC)
1 From the Control Panel, select Administrative Tools and
then Data Sources (ODBC). The ODBC Data Source
Administrator dialog box is displayed.
2 Select the System DSN tab and then click Add. The
Create New Data Source dialog box is displayed.
3 Select DataDirect5.0 Text File (version 5.00.00.42) as
your ODBC driver and then click Finish. The ODBC Text
Driver Setup dialog box is displayed.

If you do not have this driver on your machine, you
need to install it.
4 On the General tab, enter a Data Source Name (DSN).
5 In the Database Directory field, provide the path of the
directory where you store the text file.
6 Select Comma as the Default Table Type.
7 Select the Column Names in First Line check box.
8 On the Advanced tab, click Define. The Define File dialog
box is displayed.
9 Select the text file you want to define and click Open. The
Define Table dialog box is displayed.
10 In the Table Information section, in the Table text box
enter the name of the table for the text file, for example,
LU_EMPLOYEE (for LU_EMPLOYEE.txt).
11 Select the Column Names in First Row check box.
12 Click Guess to display all the columns contained in this
table.
13 Click OK to return to the Define File dialog box.
14 Click Cancel to return to the ODBC Test Driver Setup
dialog box.
15 Click Apply and then OK to return to the ODBC Data
Source Administrator dialog box.
16 Click OK. Your data source for the text file is now set up.
17 (Optional) Use the MicroStrategy ODBC Test Tool to test
retrieving data from the table (for the text file).

Make sure to select the correct DSN for the text file.


Create a Freeform SQL report from a text file
1 From the File menu on Desktop, right-click anywhere in
the right panel on Desktop, select New and then Report.
2 In the New Grid dialog box, select Freeform SQL. The
Freeform SQL Editor is displayed.
3 Below the toolbar, from the Database Instance drop-down
list select the database instance you created in the
previous procedure, “Create a database instance for the
Excel file”.
4 In the SQL Statement panel, type in your SQL query.

Note the following:
– Column names in the SQL statement have to match
the field names in the text file.
– Case does not matter, as long as the column names are
correct.
5 In the Mapping panel, map the columns in your SQL
statement to attributes and metrics that will be used in the
MicroStrategy report.

Note the following:
– When mapping the columns, it is important that you
follow the same sequence of the columns as they
appear in the SQL statement. Doing otherwise will
cause the report to fail.
– Make sure that the number of mappings is the same as
the number of columns in the SQL statement. For
example, if your SQL statement lists 10 columns from
which to retrieve data, you should map them to exactly
10 objects (including attributes and metrics).
– For each attribute, you must map the ID form at least.
6 Click OK to close the Freeform SQL Editor. The Report
Editor opens in Design view by default.
7 From the File menu, click Save or Save As.

You must save the report before you can run it.
Otherwise, you will see a Desktop message saying
that the report “cannot be executed unless it is
saved”.
8 In the Save Report As dialog box, enter a name for the
report and click Save.
9 Run the Freeform SQL report.