Access and Analyze Multiple Data Sources with Freeform SQL

With Freeform SQL, you can access multiple data sources in the same project and use MicroStrategy reporting features to analyze your data. Connection to databases, Excel files, and text files is described in Connect to Databases, Excel Files, and Text Files.

For information on using the Freeform SQL Editor to report on third-party web services as a data source, see Reporting on Third-Party Web Services with XQuery.

Once you connect to a valid data source, you can create a Freeform SQL report to retrieve and analyze the data from the data source. This section describes the process of using Freeform SQL to:

• Create a Freeform SQL Report to Access Databases, Excel Files, or Text Files
• Create a Freeform SQL Report Using a Stored Procedure
• Mapping Columns to Metadata Objects

Create a Freeform SQL Report to Access Databases, Excel Files, or Text Files

After you create a connection to your data source, you can create a Freeform SQL that queries that data source.

The steps to create a Freeform SQL report from a database, Excel file, and text file are the same, except for which data source the report connects to. For steps to create a Freeform SQL report from a stored procedure, see Create a Freeform SQL Report Using a Stored Procedure.

To Create a Freeform SQL Report

1. In MicroStrategy Developer, choose File > New > Report.
2. On the Freeform Sources tab, in the Source area, select a database instance for the data source to access using Freeform SQL. The sections listed below provide the configuration steps for the different data sources:
• Connect to Databases
• Connect to Excel Files
• Connect to Text Files

If a database instance is not defined in the metadata, the Freeform SQL Editor cannot be loaded and a message is displayed.

3. Select Create Freeform SQL report and click OK.
4. In the SQL Statement pane, enter your SQL query. Be aware of the following when creating a SQL statement:
• Column names in the SQL statement have to match the column names in the database, column headers in the Excel file, or the field names in the text file.
• The column names you use in your SQL query are NOT case sensitive.
• When connecting to Excel and text files, you do not use the file name as the table name for the "From" clause. You must use the table names you defined when creating and connecting to the files. Remember that the Excel file or text file is the data source that contains the tables, which are named individually.
5. In the Mapping pane, map the columns in your SQL statement to attributes and metrics to be used in the report. Be aware of the following when mapping columns in your SQL statement to attributes and metrics:
• 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 causes 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 these columns to exactly 10 attributes or metrics.
• You must map a column to the ID form for each attribute you use in your Freeform SQL report.
6. Insert prompts into the SQL statement, if needed. For information on including prompts in Freeform SQL reports, see Reporting Analysis Features.
7. Insert security filter qualifications, if needed.

   For more information on inserting security filter qualifications in Freeform SQL reports, see Security for Data Access.

8. Click OK.
9. Format and define the Freeform SQL report in the same way as you would a standard report, using features such as sorting, view filters, thresholds, exporting, and so on.
10. Choose File > Save As.

    You must save the report before you can run it.

11. Enter a name for the report and click Save.
12. Run the Freeform SQL report.

Create a Freeform SQL Report Using a Stored Procedure

Since stored procedures are run against databases, creating a Freeform SQL report using a successful stored procedure requires you to first connect to a database (see Connect to Databases). Mapping columns to attributes and metrics requires prior knowledge of your data warehouse structure. Although the stored procedure itself does not display any column names, you need to know in advance what columns will be retrieved once the procedure is executed. Without this knowledge, it may be difficult for you to map the columns.

Below are the general steps you need to take when you use a stored procedure to create a Freeform SQL report.

To Use a Stored Procedure to Create a Freeform SQL Report

1. In MicroStrategy Developer, choose File > New > Report.
2. On the Freeform Sources tab, select Create Freeform SQL report.
3. In the Source area, select the database instance you created for Freeform SQL reports with stored procedures.
4. Click OK.
5. In the SQL Statement pane, enter your stored procedure.

   Different databases use different syntax to execute stored procedures. Information on stored procedure execution for some major databases is given in the table below

   Database	Stored procedure	Notes
   DB2	call stored_procedure_name with DB2 ODBC driver	The stored procedure must have been created indicating that it has a result set. The results will be sent back to the client.
   Oracle	call stored_procedure_name( ) with MicroStrategy ODBC driver {call stored_procedure_name} with Oracle ODBC driver	The stored procedure must return the results into a table that can subsequently be selected.
   SQL Server	exec stored_procedure_name with SQL Server ODBC driver	The stored procedure returns the data to the client. No particular precaution is needed.

6. In the Mapping pane, map the columns that are retrieved by the stored procedure to attributes and metrics that are used in the MicroStrategy report.
7. Click OK.
8. Choose File > Save As.

   You must save the report before you can run it, otherwise a message is displayed.

9. Enter a name for the report and click Save.
10. Run the Freeform SQL report.

Mapping Columns to Metadata Objects

You use the Mapping pane in the Freeform SQL Editor to map the columns in your SQL query to attributes and metrics included in a MicroStrategy report.

You can map columns to:

• Managed object attributes and metrics: Managed objects are created on the fly and only exist in the context of Freeform SQL and Query Builder reports. These objects are stored in the Freeform Objects folder, and are known as managed objects. Managed objects enable you to create a Freeform SQL or Query Builder report quickly, without having to take the time to manually create or map data to schema objects.

  For more information on managed objects and how they are used in MicroStrategy, see Map Data to Non-Project Objects: Managed Objects.

  Managed objects are also created when integrating MDX cube sources into MicroStrategy. MDX cube sources include SAP BW, Microsoft Analysis Services, Hyperion Essbase, and IBM Cognos TM1. For information on integrating MDX cube sources into MicroStrategy, see the MDX Cube Reporting Help.

• Project attributes: These attributes exist in the project as part of the relational schema, and therefore can be used to generate standard reports. Mapping columns in Freeform SQL reports to project attributes enables you to present a consistent representation of data to report analysts. This also allows you to use Freeform SQL reports as filters on standard reports, as described in Reporting Analysis Features.

  Project attributes also facilitate joins with standard MicroStrategy reports in a Report Services document. This capability enables you to create many types of documents. For example, you can create a Report Services document with two datasets that share a common project attribute. One retrieves historical data from a data warehouse, and the other contains current data using Freeform SQL from an operational system.

  Unlike attributes, which can be mapped to project attributes, all metrics are created or mapped to managed object metrics. The only existing metrics you can use are managed object metrics that are defined for Freeform SQL and Query Builder reports in the current project.

For information on mapping attributes and metrics to columns in your Freeform SQL statement, see the sections below:

• Mapping a New Attribute Form to a Column
• Mapping a New Metric to a Column
• Mapping an Existing Object to a Column
• Renaming an Object Mapped to a Column
• Replacing an Object Mapped to a Column

Mapping a New Attribute Form to a Column

You can map a column in a SQL statement to a new attribute form in the MicroStrategy environment. All new attribute forms are mapped to managed objects, which are discussed in Map Data to Non-Project Objects: Managed Objects.

By default, every newly added attribute uses the ID form. You can change the column mapped to the ID form by using the Forms drop-down list in the Mapping pane of the Freeform SQL Editor.

To Map a New Attribute Form to a Column

1. Choose Mapping > Add New Attribute Form.

   An attribute can have multiple attribute forms, and every attribute must map a column to the ID attribute form.

2. Enter the object name of your choice and make the selection for Form and Type.

   You can show and hide column data types along with other pertinent information. Choose View > Show Column Data Type. This option displays the full data type information for the column, including Data Type, Precision/Length, and Scale.

3. Ensure that the column number for the object matches the order of the column in the SQL statement.

   An object is to be mapped to the second column listed in the SELECT clause. In the Mapping pane, the object must be included as column 2. You can use the Move Up and Move Down options in the toolbar to change the column position of an object.

   Use the DB Query Tool to test the validity of the stored procedure before creating any Freeform SQL report.

   If you use a stored procedure, it is important that you know its content in advance, including exactly which columns the stored procedure retrieves. Otherwise, it may be difficult for you to correctly map the columns retrieved.

Mapping a New Metric to a Column

You can map a column in the SQL statement to a new metric that can be recognized in the MicroStrategy environment. All new metrics are mapped to managed objects, which are discussed in Map Data to Non-Project Objects: Managed Objects.

To Map a New Metric to a Column

1. Choose Mapping > Add New Metric.
2. Enter the metric name of your choice and make the selection for Form and Type.

   You can show and hide column data types along with other pertinent information. Choose View > Show Column Data Type. This option displays the full data type information for the column, including Data Type, Precision/Length, and Scale.

3. Ensure that the column number for the object matches the order of the column in the SQL statement.

   An object is to be mapped to the second column listed in the SELECT clause. In the Mapping pane, the object must be included as column 2. You can use the Move Up and Move Down options in the toolbar to change the column position of an object.

Mapping an Existing Object to a Column

By mapping existing objects to columns in Freeform SQL reports, you can avoid creating duplicate copies of the same object. Mapping with a project object also serves the purpose of joining data when the object is used in a Report Services document that contains multiple datasets. Using project objects also enables you to use security filters with your Freeform SQL reports.

An existing object can be:

• An attribute defined in an existing MicroStrategy project that is part of a relational schema.
• A managed object attribute that has been created for previous Freeform SQL reports or Query Builder reports in the current project.
• A managed object metric that has been created for previous Freeform SQL reports or Query Builder reports.

  A metric defined in an existing MicroStrategy project that is not a managed object cannot be used for mapping in Freeform SQL reports or Query Builder reports.

To Map an Existing Object to a Column

1. Choose Mapping > Insert.
2. Select an attribute or metric and click OK.

3. Ensure that the column number for the object matches the order of the column in the SQL statement.

   An object is to be mapped to the second column listed in the SELECT clause. In the Mapping pane, the object must be included as column 2. You can use the Move Up and Move Down options in the toolbar to change the column position of an object.

Renaming an Object Mapped to a Column

You can use the Rename option to change the name of an attribute or a metric in the Object field in the Mapping pane. Only new attributes and metrics can be renamed. You cannot rename those that are mapped to existing attributes and metrics that have already been saved to the metadata, that is those that already exist in the Freeform Objects folder or in any folder displayed in the Object Browser window. This reduces the number of duplicate metadata objects.

To Rename an Object Mapped to a Column

1. In the Mapping pane, right-click the row for the object you want renamed, and select Rename.
2. Enter the new name for the object.

Replacing an Object Mapped to a Column

You can replace an object mapped to a column with a different existing object. This can be helpful if you previously used managed object attributes to map your Freeform SQL data, but now you want to use project attributes that are part of your relational schema.

To Replace an Object Mapped to a Column

1. In MicroStrategy Developer, browse to a Freeform SQL report, right-click the report, and select Edit.
2. View the report in Design view.
3. Choose Data > Freeform SQL Definition.
4. In the Mapping pane, right-click the row for the object you want to replace, and select Replace.
5. Select an object that you want to use and click OK.