Freeform SQL in MSTR

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 for a report. In addition to generating reports in the traditional way, you can use your own customized SQL statements to generate reports from operational systems included in a MicroStrategy project. This capability can save time since you do not need to place the data into a data mart or data warehouse first.

The Freeform SQL feature allows you to use your own SQL statements to access data from various ODBC data sources, including relational databases, Excel files, and flat files, as long as they are included in the MicroStrategy environment. Since you create your own SQL statements to create reports with Freeform SQL, a strong knowledge of how to create and use SQL statements is essential.

Details on how to create Freeform SQL reports from these ODBC data sources are discussed in this section.

The following image shows the Freeform SQL Editor, where you define the SQL statement for the report. Notice the different panes for different purposes.

ACL in MSTR

An access control list (ACL) is a list of users and groups and the access permission that each one has to objects in a MicroStrategy project. Different users may have different permissions on the same object.

When you use existing objects (including project objects) in Freeform SQL column mapping, the ACLs of these objects are used. However, new attributes and metrics created in Freeform SQL reports inherit the default ACL defined in the Project Configuration Editor. You can modify the default ACL in MicroStrategy Developer by right-clicking a project and selecting Project Configuration. In the Project Configuration window point to Project definition, then Security, and then for Set Freeform SQL and MDX objects default security select Modify. The Properties[XDA Objects] dialog box is displayed. The Permissions list has the following settings: 

The user who creates the new attributes and metrics with Freeform SQL is automatically given the Full Control permission of the new objects.

The changed settings will only affect the new attributes and metrics created subsequently in Freeform SQL reports, but not those objects created prior to the change.

User	Children
Administrator	Full Control
Everyone	View
Public/Guest	View