You can drag and drop the colum ns to change the order or right-click the colum ns and use the Move Up or Move Down options. You can perform m any types of report m anipulations on Freeform SQL reports just as you can with standard reports.
Customer Purchase Analysis. With October 20 11 selected in the page-by field, the first part of the result set should look like the following: You can use the detailed instructions if you want help. Add subtotals 2 On the Data m enu, select Subtotals. Remove the Profit metric from the grid 15 Right-click the Profit m etric and select Remove from Grid. It should also contain an Average Perform ance Score m etric. The original report does not have any filtering conditions.
You need to m odify the SQL statem ent for the Employee Performance Analysis report to include only em ployees in the East and West regions who have an average performance score of 4 or above.
When you finish m odifying the report, answer the two follow-up questions at the end of the detailed instructions for this exercise. The condition in the HAVING clause lim its the result set to display only em ployees in these two regions with an average perform ance score of 4 or higher. After you have answered the questions, close the report. Why or why not? In Freeform SQL reports, you can drill on project attributes to only attributes that are part of the personal Intelligent Cube.
In this lesson, you will learn about the types of prom pts that are supported for Freeform SQL reports. Then, you will learn how to add new prom pts or insert existing prom pts into Freeform SQL reports and how to configure prom pts as optional. Configure prom pts as optional. Prom pts are MicroStrategy objects that you can use in reports to enable end users to select the content of a report at run tim e.
When you create a prom pt as part of a Freeform SQL report, it is saved in the m etadata as a norm al prom pt object, not a m anaged object. The rem ainder of this lesson describes how to incorporate elem ent list and value prom pts into Freeform SQL reports.
OR If you want to create a value prom pt, select Value prompt. When you finish creating the prom pt, it is inserted into the SQL statem ent at the location where you positioned the cursor. Prom pts display in the SQL statem ent in pink text. If a prom pt nam e contains spaces, it is enclosed in brackets.
For exam ple, a prom pt nam ed Select Year would display as [Select Year]. Again, the prom pt is either inserted at your cursor position, or it replaces any SQL that you have selected. J ust as with new prom pts that you create in the Freeform SQL Editor, existing prom pts display in pink text and are enclosed in brackets if the prom pt nam e contains spaces.
The selections that you m ake determ ine what data is included in the result set. The WHERE clause contains a prom pt that enables you to select the custom er gender that you want to include in the report. The other colum ns are all m apped to Freeform SQL attributes or m etrics. The parentheses are not needed in all cases and, in som e circum stances, can cause syntax errors.
Therefore, to m ake prom pt usage m ore flexible in Freeform SQL reports, they are not autom atically inserted. You can select one or m ore custom er genders. Notice that the result set includes only m ale custom ers. As with any prom pted report, if you save it, you can choose to save it as static or prom pted. You can also choose whether you want to retain your prom pt answers as the default answers the next tim e you run the report.
In this exam ple, the SQL statem ent uses the IN operator, and it includes the custom er gender that you selected. With these operators, you do not need to insert parentheses around the prom pt nam e as you do with the IN operator. However, unlike the IN operator, which can take m ultiple prom pt answers, these other operators only allow for a single answer. To ensure that end users do not select m ore answers than an operator allows, you need to define the prom pt so that it lim its the num ber of answers to one.
You can include value prom pts in various parts of a SQL statem ent. The WHERE clause contains a prom pt that enables you to enter the description of the custom er city that you want to include in the report.
With value prom pts, you are not lim ited to project attributes. Therefore, in this exam ple, all the colum ns are m apped to Freeform SQL attributes or m etrics. You can enter a value for only one city. Notice that the result set includes on ly custom ers in Seattle. You can also choose whether you want to retain your prom pt answer as the default answer the next tim e you run the report.
End users m ust answer a required prom pt to execute a report, whereas they can choose not to answer an optional prom pt and still execute the report. You can determ ine whether a prom pt is required or optional by viewing its definition in the Prom pt Generation Wizard. Otherwise, if end users choose not to answer an optional prom pt, the report fails since part of the required SQL is m issing.
For exam ple, you could m odify the Custom er Gender elem ent list prom pt that was used earlier in this lesson to m ake it an optional prom pt. This incom plete SQL causes the report to fail. To account for the optional prom pt, you need to m ake the prom pt and all SQL related to it in this case, the condition in the WHERE clause prom pt dependent. MicroStrategy Freeform SQL Essentials Prom pts in Freeform SQL Reports 3 Now when you run the report and do not select a custom er gender in response to the prom pt, the report executes and returns data for both the custom er genders in the database for which revenue data exists.
When you use text value prom pt, you have to m anually type single quotes around the nam e of the prom pt in the SQL statem ent.
Exercise 3. It should also contain the Revenue m etric. You need to form at this m etric to display as currency with two decim al places. You can drag the colum ns to change the order or right-click the colum ns and use the Move Up or Move Down options.
This prom pt enables you to select the em ployees to be included in the report based on their average perform ance scores. You should use an operator that enables you to see em ployees with average perform ance scores greater than or equal to the num eric value you enter. Finally, because the prom pt is optional, you need to m ake the associated SQL prom pt dependent.
Run the report and enter 4. Employee Performance Analysis. This button is indicated by the following icon: 31 When the prom pt displays, clear your previous prom pt answer and do not enter another value. In this lesson, you will learn about using derived and com m on table expressions, stored procedures, and non-relational data sources with Freeform SQL reports.
When you create a standard report, you m ay include functionality such as level m etrics, conditional m etrics, and so forth that typically require the MicroStrategy Engine to generate m ultiple passes of SQL to retrieve the result set. When you create a Freeform SQL report, you m ay also want to aggregate m etrics at different levels, apply conditions to selected data, or perform other tasks that generally involve using m ultiple SQL passes.
Databases expect m ultiple passes of SQL to be sent one at a tim e for processing. Therefore, databases do not support processing m ultiple passes of SQL separated by sem icolons. One m ethod that you can use to sim ulate m ultiple passes of SQL is to em bed them in subqueries using derived an d com m on table expressions.
This result is then used in the outer query. Both types of expressions are equally valid m eans of producing subqueries. However, support for each type of expression does vary based on the database platform that you are using. For exam ple, support for com m on table expressions is not as widely available as for derived table expressions. The exam ple you will learn about in this course involves using derived table expressions in a Freeform SQL report.
However, you can use the sam e steps to include a com m on table expression in a Freeform SQL report. The only difference is in the syntax of the SQL statem ent that you pass to the database, not the m ethod. For exam ple, consider the following report: Employee Overtime Percent Contribution Example Although this report contains only a couple of attributes and m etrics, the Percent Contribution to Region Overtim e m etric requires m ore com plexity than a single, sim ple pass of SQL.
One alternative for achieving this sam e result in a Freeform SQL report is to use derived table expressions to create subqueries. The subqueries perform the sam e functions as the individual passes of Engine-generated SQL, but they elim inate the need to use m ultiple passes of SQL.
Depending on your database platform , the exact syntax m ay vary. Also, ensure that your database platform supports derived table expressions. Overtim e hours data is stored at the Em ployee level, so it has to be aggregated to the Em ployee Region level.
Because the overtim e hours are stored at the Em ployee level, there is no need to aggregate the data. Finally, the outerm ost SELECT clause retrieves the inform ation to display on the report, including the attribute descriptions, and calculates the Percent Contribution to Region Overtim e m etric. If you want to execute m ultiple passes of SQL in a Freeform SQL report, another alternative to using derived and com m on table expressions is to create stored procedures.
A stored procedure is a collection of com piled SQL statem ents that you can run against a database by executing the stored procedure. Most database environm ents contain stored procedures that have been created to accom plish routine tasks or queries. When executing such queries, you can use Freeform SQL reports to take advantage of the stored procedures that exist in your database, rather than having to m anually enter your own custom SQL.
Alternatively, if a stored procedure does not exist for a query that you want to execute, you can create a stored procedure in the database that includes the SQL you want to use for a report.
The database does not have to com pile an execution plan each tim e you run stored procedures as it does for individual queries. Therefore, the database can m ore efficiently process stored procedures. Before you can define the SQL for the Freeform SQL report, you first have to create a stored procedure in the database that contains the data you want to retrieve.
You execute this statem ent to create the stored procedure in your database. Within the stored procedure, the first statem ent retrieves the overtim e hours for each em ployee. It also creates a tem porary table in which to store this interm ediate result set. The third statem ent retrieves the inform ation to display on the report, including the attribute descriptions, and calculates the Percent Contribution to Region Overtim e m etric.
It consolidates inform ation from several database tables as well as the two tem porary tables that contain the interm ediate result sets from the first two passes of SQL.
The last two statem ents sim ply rem ove the tem porary tables that were created from the database. After you create the stored procedure, you can create a Freeform SQL report that executes the stored procedure and displays the data from the final SELECT statem ent as the report result set.
Depending on your database platform , the exact syntax for executing a stored procedure m ay vary. So far in this course, you have learned about executing Freeform SQL reports against databases.
However, you can also use Freeform SQL reports to access non-relational data sources such as a Microsoft Excel file or a text file. If you have data that is stored in a Microsoft Excel file or text file, you can easily analyze that data in Freeform SQL reports without having to im port it into a database.
To access a Microsoft Excel file as a data source, you need to perform the following steps: 1 Prepare the Microsoft Excel file by creating tables. Microsoft Excel file you want to use has been created specifically purposes, it m ay already contain tables for the data. Also, if a colum n header nam e is alphanum eric, it m ust start with letters, not num bers for exam ple, Q20 13, not 20 13Q. Table option only if you have form atted the data as spreadsheet.
If you do not see the Table option, go to step 7. This ensures the workbook is saved as an. The following im age shows a Microsoft Excel file with m ultiple tables: Microsoft Excel File with Multiple Tables This Microsoft Excel file contains data that corresponds to several different tables.
The Table Nam e drop-down box, which is circled in the im age above, enables you to select a table. When you select a table, the corresponding data is selected in the file. This table contains data about individual transactions, including the custom er, products, date, revenue, cost, and num ber of item s sold. Worksheet nam es in a SQL query require specific syntax. You also need to enclose them in single quotes if the worksheet nam es contain spaces.
In the exam ple above, the colum n nam es do not contain spaces. However, if you do have colum n nam es that contain spaces, you need to enclose them in brackets. The steps for creating the DSN are sim ilar to the steps that you use for databases. The steps for creating a database instance for a Microsoft Excel file are sim ilar to the steps that you use for databases, with two slight differences.
To access a text file as a data source, you need to perform the following steps: 1 Prepare the text file. If you want to use m ultiple text files as the data source for a Freeform SQL report, you need to store the files in the sam e location. You can use any delim iter in the text file as long as it is supported by the ODBC driver that you use to connect to the file.
However, if you include them in the body of the text file, you avoid having to m anually define the colum ns of the text file when you create the DSN to connect to the file.
The following im age shows a sam ple text file that is com m a delim ited with the colum n nam es in the first row of the text file: Comma Delimited Text File The first row in this text file lists the colum n nam es using the com m a delim iter. The rem ainder of the text file contains the actual data, which consists of regions, states, account representatives, the num ber of accounts each representative owns, and the com bined revenue from all of the accounts that each representative owns.
This em bedded driver is the recom m ended one for accessing text files. If you use any previous version of MicroStrategy, this driver is not available as part of the software installation. The procedure for using this driver is described in this lesson. If you use this driver, the procedure for creating the DSN is sim ilar to the one described in this lesson. However, som e steps do vary because of differences in the interfaces.
The table nam e cannot contain any spaces. To m odify the data type for a colum n, select the colum n. In the Type drop-down box, select the correct data type. Click Modify. In the Modify Colum n window, click OK. Notice that the colum ns that display m atch the colum n nam es contained in the first row of the text file. The steps for creating a database instance for a text file are sim ilar to the steps that you use for databases. However, there are two slight variations.
First, you should use the Generic DBMS connection type since you are not connecting to a specific database platform.
Second, because you are not accessing a database that requires a specific login, you can select from any of the existing logins or create your own database login. After you create the database instance, you can configure it as a Freeform SQL database instance using the Project Configuration window. The definition for this Freeform SQL report would look like the following: Freeform SQL Report Definition - Text File The SQL statem ent for this report selects the region, state, and account representative IDs and nam es and aggregates the num ber of accounts each representative owns and the com bined revenue of those accounts.
The Microsoft text driver does not enable you to define a table nam e for use in queries. The report displays the num ber of accounts and total account revenue by region, state, and account representative for the East and Pacific regions.
Exercise 4. The purpose of this report is to analyze the num ber of custom ers in each city who are in the 21 to 40 and 41 to 60 age ranges and then to determ ine what percentage of custom ers in each city consists of custom ers in these two age ranges.
SQL Server autom atically processes the division of counted colum ns as integers, which yields 0 as the value for all percentages. To resolve this issue, you need to convert each count to float data type. The Detailed Instructions contain the syntax for perform ing this conversion.
You should create this stored procedure using m ultiple passes of SQL and tem porary tables. After creating the stored procedure, create a Freeform SQL report that executes the stored procedure. The purpose of this report is to analyze the revenue for each product, the contribution of each individual product to the revenue for its entire product group, and the contribution of each individual product to the revenue across all products. Form at the revenue m etric to display as currency with two decim al places.
Form at the percent contribution to product group revenue m etric to display as a percentage with two decim al places.
Form at the percent contribution to all revenue m etric to display as a percentage with four decim al places. You do not need to save the SQL, so you can click No. Close the file. If you do not know how to im port external data into a Microsoft Excel you can use the detailed instructions for help.
Configure this database instance as a Freeform SQL database instance. The purpose of this report is to analyze the average satisfaction score by em ployee departm ent across all survey questions for 20 You should create a report that displays the Em ployee Departm ent attribute and an Average Satisfaction Score m etric. You should filter the report to include only 20 11 data. Therefore, the report returns only 11 rows even though there are 12 em ployee departm ents.
If you do not have this login, click New. In the Database Logins window, in the Database login box, type Excel. In the Login ID box, type Excel. Leave the Password box blank and click OK. The com pleted colum n m apping should look like the following: 42 Click OK. You should use the Division. After you have set up the appropriate connections, create a Freeform SQL report that executes against the Division.
The purpose of this report is to analyze the num ber of em ployees that report to each m anager. You should add subtotals to the report so that it displays the subtotal of em ployees for each division and the grand total of em ployees across all divisions. The result set should look like the following: You can use the detailed instructions if you want help. Define the columns for the Division.
Define the columns for the Manager. Define the columns for the Employee. In the Database Logins window, in the Database login box, type Text. In the Login ID box, type Text. In this lesson, you will learn how perm issions are applied in the access control lists for existing project attributes and Freeform SQL objects in Freeform SQL reports.
You will also learn how to incorporate security filters in Freeform SQL reports, including how to apply top and bottom range attributes. Every object in a MicroStrategy project has an access control list ACL that determ ines the perm issions that different users or groups have for that object. To view the access control list for an object: 1 Right-click the object and select Properties.
The Adm inistrator user and users who belong to the Developers user group can do anything with the report—view it, execute it, m odify it, delete it, and so forth.
For exam ple, they can view and execute the report, but they do not have perm issions to m odify or delete it. For m ore inform ation on access control lists and other security settings, see the MicroStrategy Adm inistration: Configuration and Security course. If you m ap a colum n to an existing project attribute, as with standard reports, the access control list of that attribute determ ines the perm issions. However, Freeform SQL objects work a little differently. They inherit a default access control list that is defined at the project level.
In addition to the perm issions in the default access control list, the who creates a Freeform SQL object also has Full Control access for the object. On the tem plate or in the Report Objects window, right-click the object and select Properties.
To modify the default access control list for new Freeform SQL objects: 1 In MicroStrategy Developer, under the appropriate project source, right-click the project for which you want to m odify the access control list, and select Project Configuration. After you change the default access control list for Freeform SQL objects, the m odified access control list applies to any new Freeform SQL objects that you create. One of the application-level security features available in MicroStrategy is security filters.
A security filter is a filter that you can assign to a user or group to lim it the result set that the user or group obtains when executing a report or browsing attribute elem ents. Security filters enable you to control at the MicroStrategy level the warehouse data that is available to specific users or groups. When a user executes a standard report, if that user, or a group to which the user belongs to, has a security filter, it is included in the Engine-generated SQL for the report.
As a result, the query retrieves only data that m eets the conditions of the security filter as well as any filters on the report itself. For exam ple, consider the following report: Report Without Security Filter This report contains two filters in its definition—one to display data only for specific regions Northeast, Mid-Atlantic, and Southeast and one to display only Electronics data.
However, this report displays all of the available subcategories within the Electronics category. However, if you want to force users to only be able to see as high as the Region attribute, you can set Region as the Top Range Attribute to prevent users from accessing data stored at a higher level than Region. All security filters contain a filter expression. For m ore inform ation on security filters, see the MicroStrategy Adm inistration: Configuration and Security course.
However, you can apply security filters to a Freeform SQL report by inserting a security filter placeholder into the SQL statem ent for the report. Security filters are based on project attributes. You cannot create security filters using Freeform SQL objects. Therefore, when you apply security filters to a Freeform SQL report, they work as designed only if the report contains corresponding project attributes. Select this option only when you are sure that your query does not retrieve data outside the restriction defined by the security filters.
How you configure each of these param eters determ ines the behavior of security filters within the Freeform SQL report. Entering this syntax as part of the replacem ent string ensures that all the syntax related to the security filter is rem oved from the report SQL if a user without a security filter runs the report. Therefore, if a user without a security filter runs the report, the incom plete security filter expression produces invalid SQL, which causes the report to fail.
If you use table aliases in the SQL statem ent, you must use those aliases, not the table nam es in the string. If you have security filters that contain top and bottom range attributes, you must select this option. Otherwise, the report will fail when users who are associated with those security filters execute it. When you finish defining the security filter placeholder, it is inserted into the SQL statem ent at your cursor position. A security filter The report applies security filter placeholder exists qualifications for attributes that are mapped in the placeholder definition.
The report does not apply security filter qualifications for attributes that are ignored in the placeholder definition. The report fails for any users with security filters that contain attributes that are left out of the placeholder definition not mapped or ignored. If you have security filters that use top and bottom range attributes, these settings also affect the behavior of Freeform SQL reports in addition to any qualifications contained in the security filter. User A is allowed to view data only for Pittsburgh for Electronics.
User B is allowed to view data only for Seattle for Books. It also includes the Custom er attribute as the top range attribute. User C is allowed to view data only for San Francisco at the Custom er level or below. Relational data tables Relational data is stored in tables which can be represented in tabular format. Data processing is used extensively in the government and corporate business sectors.
In the following example, details are extracted from the customer table and the transaction table. In this example, the surname and first- name of the customer appears beside each transaction relevant to that customer. SQL is the basis for most query operations against large-scale data storage systems. This is specified as follows. Note: the operator is the standard SQL operator for string concate nation. Only some combinations of clauses will produce meaningful results.
In these cases it is preferable to name the result column. Columns can also be renamed in the result set in this way. This is usually unintended. Tables must be connected using key fields. These are generally columns such as customer number, product code, transaction date, etc. Key fields identify a record, rather than being stored data such as amounts, text values, etc.
Joins may be specified in one of two ways. Join syntax select t. Also, if a column name appears in more than one input table, then an alias name should be used to identify the relevant input table.
More than two join tables. Each table is joined to the result of the previous joins. The query should be placed within brackets, and used in place of a table name within another query. In this case, a count of records is calculated from customer records joined to product codes. The statement within the brackets is equivalent to a table containing the same data. In many cases it is not possible to recover data that is accidently altered or deleted.
0コメント