Advanced Reporting Guide: Enhancing Your Business Intelligence Application
Version: 9.5 Document Number: 09450950
Version 9.5 To ensure that you are using the documentation that corresponds to the software you are licensed to use, compare this version number with the software version shown in “About MicroStrategy...” in the Help menu of your software. Document number: 09450950 Copyright © 2015 by MicroStrategy Incorporated. All rights reserved. If you have not executed a written or electronic agreement with MicroStrategy or any authorized MicroStrategy distributor, the following terms apply: This software and documentation are the proprietary and confidential information of MicroStrategy Incorporated and may not be provided to any other person. Copyright © 2001-2015 by MicroStrategy Incorporated. All rights reserved. THIS SOFTWARE AND DOCUMENTATION ARE PROVIDED “AS IS” AND WITHOUT EXPRESS OR LIMITED WARRANTY OF ANY KIND BY EITHER MICROSTRATEGY INCORPORATED OR ANYONE WHO HAS BEEN INVOLVED IN THE CREATION, PRODUCTION, OR DISTRIBUTION OF THE SOFTWARE OR DOCUMENTATION, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, GOOD TITLE AND NONINFRINGMENT, QUALITY OR ACCURACY. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE SOFTWARE AND DOCUMENTATION IS WITH YOU. SHOULD THE SOFTWARE OR DOCUMENTATION PROVE DEFECTIVE, YOU (AND NOT MICROSTRATEGY, INC. OR ANYONE ELSE WHO HAS BEEN INVOLVED WITH THE CREATION, PRODUCTION, OR DISTRIBUTION OF THE SOFTWARE OR DOCUMENTATION) ASSUME THE ENTIRE COST OF ALL NECESSARY SERVICING, REPAIR, OR CORRECTION. SOME STATES DO NOT ALLOW THE EXCLUSION OF IMPLIED WARRANTIES, SO THE ABOVE EXCLUSION MAY NOT APPLY TO YOU. In no event will MicroStrategy, Inc. or any other person involved with the creation, production, or distribution of the Software be liable to you on account of any claim for damage, including any lost profits, lost savings, or other special, incidental, consequential, or exemplary damages, including but not limited to any damages assessed against or paid by you to any third party, arising from the use, inability to use, quality, or performance of such Software and Documentation, even if MicroStrategy, Inc. or any such other person or entity has been advised of the possibility of such damages, or for the claim by any other party. In addition, MicroStrategy, Inc. or any other person involved in the creation, production, or distribution of the Software shall not be liable for any claim by you or any other party for damages arising from the use, inability to use, quality, or performance of such Software and Documentation, based upon principles of contract warranty, negligence, strict liability for the negligence of indemnity or contribution, the failure of any remedy to achieve its essential purpose, or otherwise. The entire liability of MicroStrategy, Inc. and your exclusive remedy shall not exceed, at the option of MicroStrategy, Inc., either a full refund of the price paid, or replacement of the Software. No oral or written information given out expands the liability of MicroStrategy, Inc. beyond that specified in the above limitation of liability. Some states do not allow the limitation or exclusion of liability for incidental or consequential damages, so the above limitation may not apply to you. The information contained in this manual (the Documentation) and the Software are copyrighted and all rights are reserved by MicroStrategy, Inc. MicroStrategy, Inc. reserves the right to make periodic modifications to the Software or the Documentation without obligation to notify any person or entity of such revision. Copying, duplicating, selling, or otherwise distributing any part of the Software or Documentation without prior written consent of an authorized representative of MicroStrategy, Inc. are prohibited. U.S. Government Restricted Rights. It is acknowledged that the Software and Documentation were developed at private expense, that no part is public domain, and that the Software and Documentation are Commercial Computer Software provided with RESTRICTED RIGHTS under Federal Acquisition Regulations and agency supplements to them. Use, duplication, or disclosure by the U.S. Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of the Rights in Technical X="0.0;"x="-0.0
-12.34
x=-12.3
"Cust. No. " 0000
1234
Cust. No. 1234
m-d-yy
2/3/03
2-3-03
mm dd yy
2/3/03
02 03 03
mmm d, yy
2/3/03
Feb 3, 03
mmmm d, yyyy
2/3/03
February 3, 2003
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Designing Reports
Format
Cell type="xsd:string" /> ', $username,' ', $password,'') let $args := ($uri, $soap-version, $ws-sec-policy, $soap-custom-header, $soap-payload) let $response := mstr-soap:post($args)//loginResponse let $sessionToken := $response//sessionId let $sessionURI := $response//serverUrl return
[ID@ID] [Amount] [Date@ID] [Comments@DESC]
The record ID is required to make sure the proper record is updated with the transaction. This means this record must be mapped to required input objects, using the following syntax: [ID@ID] The entire syntax listed above is mapped to an ID attribute as an input, which is defined as a required input.
Transaction Services example: Item Order Form The MicroStrategy Tutorial project includes examples of Transaction Services in the location: MicroStrategy Tutorial\Public Objects\ Reports\MicroStrategy Platform Capabilities\ MicroStrategy Transaction Services. Included in these examples is the Item Order Form document. This document is an example of using Transaction Services to review the current inventory of items and place an order for items to replenish that inventory as needed. As part of the transaction, you can request a new order, including the quantity of the item and whether the order is high priority. You can also use the same document to update your orders before they are fully processed,
690 Updating > Show Shortcuts
© 2015 MicroStrategy, Inc.
Commands
905
C
MicroStrategy Developer Commands
Advanced Reporting Guide
Show Folders Hide Shortcuts Hide Folders Hide Folders and Show Shortcuts Show Folders and Hide Shortcuts
Editor The Editor command loads a new instance of a specified MicroStrategy Developer editor in the currently selected project. dss://editor EditorName
906 Commands
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
C
MicroStrategy Developer Commands
Parameter
Description
EditorName
Indicates the name of the editor to load. The command supports the following editors: • Search • ReportDefinition • DocumentDefinition • Prompt • Filter • Template • Metric • CustomGroup • Consolidation • Attribute • Fact • Hierarchy • Transformation • Partition
Example Open Search Editor
Execute The Execute command executes one or more reports or documents. The reports or documents are identified by their object type and object ID. dss://Execute ObjID1.ObjType1\ObjID2.ObjType2 \…\ObjIDn.ObTypen Parameter
Description
ObjID
The ID of the object to be executed. To view an object’s ID, in MicroStrategy Developer right-click the object and select Properties. The ID is displayed in the dialog box.
ObjType
The object type of the object to be executed. The type is used from the EnumDSSObjectType enumeration in the MicroStrategy Objects type library. The command supports the following two types: • 3—DSSTypeReportDefinition • 55—DSSTypeDocumentDefinition
© 2015 MicroStrategy, Inc.
Commands
907
C
MicroStrategy Developer Commands
Advanced Reporting Guide
Example Execute: {Profit Forecast 2003, Document (Customer Hierarchy)}
ExecuteDocument The ExecuteDocument command loads the Document Editor. The command can execute one or more documents. ExecuteDocument command executes the document only if the The current project source uses a server connection (three-tier). dss://ExecuteDocument DocumentID1\DocumentID2\…\DocumentIDn Parameter
Description
DocumentID
The ID of the document definition object. The command takes any number of documents to execute. To view an object’s ID, in MicroStrategy Developer right-click the object and select Properties. The ID is displayed in the dialog box.
Examples Execute Document: {Document (My Electronics Dashboard)} Execute Document: {Document (My Electronics Dashboard), Document (Product Hierarchy)}
ExecuteReport The ExecuteReport command runs a report and displays it in the view (Grid, Graph, or Grid Graph) that it was saved in. The command can execute one or more reports. 908 Commands
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
C
MicroStrategy Developer Commands
dss://ExecuteReport ReportID1\ReportID2\…\ReportIDn Parameter
Description
ReportID
The ID of the report. The command takes any number of reports to execute. To view an object’s ID, in MicroStrategy Developer right-click the object and select Properties. The ID is displayed in the dialog box.
Examples Execute Report: {Electronics Revenue vs. Forecast 2003} Execute Report: {Electronics Revenue vs. Forecast 2003, Electronics Revenue By Region}
Open The Open command connects to a project source in the object browser. The Open command searches for the project source by name. The search is case sensitive. After the project source is found, it is expanded. If the user is currently not connected to the project source the command opens the login window. You can also include a project ID to open a project. dss://Open ProjectSourceName\ProjectID\UserLogin\UserPassword
Parameter
Description
ProjectSourceName
The name of the project source node in the object browser control.
ProjectID
Optional. If the ProjectID parameter is included, the command finds and connects to the project. To obtain a project ID, right-click the project and select Project Configuration. The ID is displayed in the dialog box.
© 2015 MicroStrategy, Inc.
Commands
909
C
MicroStrategy Developer Commands
Advanced Reporting Guide
Parameter
Description
UserLogin
Optional. The UserLogin parameter defines the default value of the user login when the project source is expanded. The login window is only displayed if the user is not currently connected to the project source.
UserPassword
Optional. The UserPassword parameter defines the default value of the user password when the project source is expanded. The login window is only displayed if the user is not currently connected to the project source.
Examples Open the MicroStrategy Tutorial project source Open Tutorial Project Open Tutorial Project using Administrator login
Reset The Reset command closes a session to a project or a connection to a project source. dss://Reset ProjectSourceName\ProjectID
Parameter
Description
ProjectSourceName
The name of the project source in the folder list.
ProjectID
Optional. If the ProjectID parameter is sent, the command closes the session to the project. Otherwise, the command closes the connection to the project source. To obtain a project ID, right-click the project and select Project Configuration. The ID is displayed in the dialog box.
910 Commands
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
C
MicroStrategy Developer Commands
Examples Close connection to the MicroStrategy Analytics Modules project source Close session to Tutorial Project
Shortcut The Shortcut command opens a folder in the object browser and displays the contents of the folder. This command searches for the folder ID in the project that the user is currently browsing. To select a project in the object browser use the Open command (see Open, page 909). dss://Shortcut FolderID Parameter
Description
FolderID
The ID of the target folder. To obtain a folder ID, right-click the folder and select Properties. The Properties dialog box displays the ID of the folder.
The folder ID parameter can specify one of the following special folder names instead of a folder ID. Profile_MyAnswers
Public_Reports
Public_Templates
Profile_MyFavorites
Public_Prompts
Schema_Partition_Mappings
Profile_MyObjects
Public_Searches
Schema_Tables
Profile_MyReports
Public_Metrics
Schema_Hierarchies
Public_Autostyles
Public_Filters
Schema_Functions
Public_Consolidations
Schema_Objects
>Financial Reports My Reports
912 Commands
© 2015 MicroStrategy, Inc.
D LEARNING TO DESIGN A DOCUMENT
D.
Introduction This appendix demonstrates how to create reports for use within Report Services documents and dashboards. You will then learn to use these reports to build the documents and dashboards, so that you can analyze data from different business perspectives. Each example in this appendix guides you through the complete creation of a Report Services document or dashboard. Instructions are included to guide in creating several of the required objects for these documents and dashboards. With the included directions, you can create the objects from scratch, or simply locate them in the sample projects included with your MicroStrategy installation. appendix assumes that you have basic object and report This creation skills. For detailed information on creating basic objects and reports, see the Basic Reporting Guide. detailed instructions on creating and customizing Report For Services documents and dashboards, see the Report Services Document Creation Guide. The examples in this appendix are designed to highlight methods for applying MicroStrategy objects (such as specific metrics, filters, and attributes) to documents, so you can expand your ability to analyze your
© 2015 MicroStrategy, Inc.
913
D
Learning to Design a Document
Advanced Reporting Guide
business data. Techniques to perform business analyses of customer attrition, healthcare service distribution and patient satisfaction, sales trends, conversion rates of Web orders, and employee performance are described throughout this appendix. These techniques present possible ways to manipulate data for effective analysis. While some of the analysis sections refer to a specific business industry, many of the analysis techniques are useful across various businesses. This appendix includes examples from the following documents in the MicroStrategy Analytics Modules. in the sample projects within the software is updated regularly. Data The sample documents, objects, images, and the procedures in this guide may display data that no longer appears in the sample folders and reports in the software. •
Customer Explorer dashboard This interactive dashboard analyzes data to reflect category sales, revenue and profit, transactions, and customer trends for key user-defined demographics. A dashboard of this type can provide you with interactive feedback regarding the spending habits of your company’s customer base. One highlight is the analysis of customer characteristics and purchase history using MicroStrategy Web to drill on the document. Learn how to look at the underlying indicators of customer spending and identify trends which directly affect the most profitable customer groups.
•
Operational Performance dashboard This dashboard provides a visual representation of sales data to allow analysts to draw quick conclusions about ongoing trends. A pair of interconnected widgets are used in this dashboard, allowing you to analyze trends with one, and view specific regional and categorical data with the other. A number of advanced metrics are utilized to monitor the performance of key figures within the business workflow. Advanced techniques allow you to manipulate metrics to view data from previous time periods, and compare differences between your actual and forecasted values.
Accessing examples in the Analytics Modules The example dashboards used in this appendix are available within the MicroStrategy Analytics Modules. These modules are provided at no extra cost as part of your MicroStrategy installation.
914
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
D
Learning to Design a Document
The data analyzed in this appendix is based on the sample data model built for the Analytics Modules. The Analytics Modules are portable to different data models and should be similar to what is in your data model, depending on your areas of analysis. The Analytics Modules come with predefined reports, metrics, and attributes, which are used to create the sample Report Services documents and dashboards. For details on how to use specific MicroStrategy tools to create reports and underlying objects, refer to the appropriate chapters in this manual.
Customer Explorer dashboard A strong customer base is important to a successful business, and the ability to follow current customer trends as they emerge allows you to make timely business decisions. The Customer Explorer dashboard provides detailed insight into your customer base.
© 2015 MicroStrategy, Inc.
Customer Explorer dashboard
915
D
Learning to Design a Document
Advanced Reporting Guide
Document display and usage scenarios
Customer managers can use this dashboard to get a general overview of trends within the customer base, as well as the mix of customers that make up the customer base. The document’s data can be used to develop potential opportunities for greater revenue, as well as guide actions to prevent a possible drop in sales. Drilling down to greater detail can extract specific information regarding customer characteristics, such as status, profile, and purchase history, for further analysis. The data presentation methods used in this dashboard can be applied to various businesses. For example, in the healthcare industry, the customer metrics can be manipulated to represent patient statistics. You can gather insight on which services drive patient revenue, along with patient satisfaction with different services and facilities. To achieve the usage scenarios above, this section guides you through the document creation process, and provides methods to enhance your analysis. detailed instructions on creating and customizing Report For Services documents and dashboards, see the Report Services Document Creation Guide.
916 Customer Explorer dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Learning to Design a Document
D
Creating the base reports and report objects Before creating a Report Services document or dashboard, you must first create the underlying reports and report objects. These objects link your analytical data with a visually appealing document or dashboard. To create the necessary objects, follow the general instructions included in this section. following steps cover the creation of only those objects used in The the reports that are part of the Customer Explorer dashboard. The objects that are part of the report creation steps below often depend on the existence of other objects in your data model. For example, the Customer Income Range attribute is only useful in a data model that also includes the Customer attribute.
Attributes The following attributes are required to create all of the necessary reports for the dashboard. All of these attributes are located in \Customer Analysis Module\Schema Objects\Attributes. •
Quarter
•
Year
•
Customer
•
Customer Gender
•
Customer Income Range
•
Customer Lifetime Value Score
•
Customer Acquisition Date
•
Current Customer Status
•
Product Group
Filters The following table lists all of the required filters, and their definitions, to create the necessary reports. The Current Year filter is located in \Customer Analysis Module\Public Objects\Reports\ Dashboards and Scorecards\Datasets\Customer Explorer. The
© 2015 MicroStrategy, Inc.
Customer Explorer dashboard
917
D
Learning to Design a Document
Advanced Reporting Guide
Active Customer filter is located in \Customer Analysis Module\ Public Objects\Filters\Customer Status\Current. Filter
Definition
Current Year
Year In list (2009)
Active Customer
Current Customer Status In list (Active)
Metrics The following table lists all of the required metrics, and their definitions, to create the necessary reports. These metrics are located in \Customer Analysis Module\Public Objects\Metrics. metric definition is provided without being separated by its Each formula, level, condition, and transformation. If you are creating these objects, you can simply type the text for each definition below into the Metric Editor. This defines the formula, level, condition, and transformation as long as every object referenced in the definition has been created correctly. For example, if a metric you build has the target level as Time, then the Time hierarchy must be defined. Name
Definition
Active Customers
[Count of Customers (Customer Lookup)] {~}
Revenue
Revenue {~}
Profit
(Revenue - Cost)
Revenue per Transaction
(Revenue {~} / Transactions)
Transactions per Customer
((1.0001 * Transactions) / (1.0001 * [Customers with Transactions]))
Reports Now that you have identified or created all of the underlying objects, you can begin creating the reports you need for the dashboard. The underlying objects will be used to build the reports. The following table lists the objects required for each report. Open the Report Editor and create each report on the left, using the corresponding
918 Customer Explorer dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
D
Learning to Design a Document
objects listed on the right. Use the report’s description on the left to create any additional report features, such as page-by. formatting is not discussed here, you may want to format your While reports so that they are visually appealing, as well as to highlight analytical data that is important to you. It can be helpful to format your graphs now in the Report Editor rather than formatting them later within the Report Services Document Editor. Reports
Report Objects and Filters
List of Customers
• • • • • • • • •
This grid report presents a list of customers along with their acquisition date and the amount of revenue they have generated for the current year. There are several attributes in the page-by which allow you to narrow down the displayed results by customer gender, income range, lifetime value score, and marital status.
© 2015 MicroStrategy, Inc.
Customer (attribute) Customer Acquisition Date (attribute) Customer Gender (attribute) Customer Income Range (attribute) Customer Lifetime Value Score (attribute) Customer Marital Status (attribute) Revenue (metric) Current Year (filter) Active Customer (filter)
Customer Explorer dashboard
919
D
Learning to Design a Document
Advanced Reporting Guide
Reports
Report Objects and Filters
Average Revenue per Transaction
• • • • • • • •
This gauge graph lets you see the amount of revenue generated per transaction. There are several attributes in the page-by which allow you to narrow down the displayed results by customer gender, income range, lifetime value score, and marital status.
920 Customer Explorer dashboard
Customer Gender (attribute) Customer Income Range (attribute) Customer Lifetime Value Score (attribute) Customer Marital Status (attribute) Year (attribute) Revenue per Transaction (metric) Current Year (filter) Active Customer (filter)
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Learning to Design a Document
Reports
Report Objects and Filters
Average Transactions per Customer
• • • • • • •
This gauge graph displays the number of transactions per customer in the current year. There are several attributes in the page-by which allow you to narrow down the displayed results by customer gender, income range, lifetime value score, and marital status.
© 2015 MicroStrategy, Inc.
D
Customer Gender (attribute) Customer Income Range (attribute) Customer Lifetime Value Score (attribute) Customer Marital Status (attribute) Transactions per Customer (metric) Current Year (filter) Active Customer (filter)
Customer Explorer dashboard
921
D
Learning to Design a Document
Advanced Reporting Guide
Reports
Report Objects and Filters
Number of Customers
• • • • • •
Customer Gender (attribute) Customer Income Range (attribute) Customer Lifetime Value Score (attribute) Customer Marital Status (attribute) Current Customer Status (attribute) Active Customers (metric)
• • • • • • • • •
Quarter (attribute) Customer Gender (attribute) Customer Income Range (attribute) Customer Lifetime Value Score (attribute) Customer Marital Status (attribute) Profit (metric) Revenue (metric) Current Year (filter) Active Customer (filter)
This horizontal bar graph displays the number of customers whose data is currently being displayed, depending on the selected set of page-bys. There are several attributes in the page-by which allow you to narrow down the displayed results by customer gender, income range, lifetime value score, and marital status.
Revenue Profit and Margins This vertical bar graph displays the profit and revenue for each quarter of the current year. The graph highlights the profit margin by grouping the two metrics together by quarter. There are several attributes in the page-by which allow you to narrow down the displayed results by customer gender, income range, lifetime value score, and marital status.
922 Customer Explorer dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Reports
Report Objects and Filters
Top Categories
• • • • • • •
This horizontal graph displays the top categories for generated revenue. There are several attributes in the page-by which allow you to narrow down the displayed results by customer gender, income range, lifetime value score, and marital status.
D
Learning to Design a Document
Customer Gender (attribute) Customer Income Range (attribute) Customer Lifetime Value Score (attribute) Customer Marital Status (attribute) Product Group (attribute) Revenue (metric) Current Year (filter)
Creating the dashboard You can now enhance the analytical power of each report by integrating them into one dashboard. Customizing the dashboard formatting can improve your dashboard by better reflecting your business standards.
Dataset objects You have already created or located all of the reports that will be added as datasets to your dashboard. These required reports are located in \ Customer Analysis Module\Public Objects\Reports\ Dashboards and Scorecards\Datasets\Customer Explorer. •
Average Revenue per Transaction
•
Average Transactions per Customer
•
List of Customers
•
Number of Customers
•
Revenue Profit and Margins
•
Top Categories
© 2015 MicroStrategy, Inc.
Customer Explorer dashboard
923
D
Learning to Design a Document
Advanced Reporting Guide
Create a new dashboard Now that you are familiar with the location of the datasets, you can create a new dashboard to display them. Create a new dashboard
From MicroStrategy Developer: 1 From the New Object drop-down menu, select Document. 2 Click the Dashboards tab, and select 01 Blank Dashboard. 3 Navigate to the report location displayed in the Dataset objects section above, and choose all of the dataset reports. 4 Ensure that the List of Customers report is the primary dataset by verifying that it is displayed in bold in the Dataset Objects area. If this report is not displayed in bold, right-click it and select Set as Grouping and Sorting Dataset. From MicroStrategy Web: 1 Click the MicroStrategy icon Create Document.
at the upper left of any page and select
2 In the Dashboard Templates area, select 01 Blank Dashboard. 3 Click the Add Dataset button in the Dataset Objects area, and add the dataset reports from the Dataset objects section above. 4 Ensure that the List of Customers report is the primary dataset by verifying that it is displayed in bold in the Dataset Objects area. If this report is not displayed in bold, right-click it and select Set as Grouping and Sorting Dataset.
Dashboard details Now that you have added all of the required datasets to your dashboard, you can begin designing a layout scheme that will attract attention to key statistics, and make efficient use of screen area.
924 Customer Explorer dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Learning to Design a Document
D
Add a descriptive title to your document
5 To draw attention to the title, create a dark blue rectangle, and position it at the top of your dashboard’s panel. To do this, open the Insert menu, point to Shape, and select Rectangle. 6 Create a text box, and type the dashboard’s name in white. To do this, open the Insert menu, and select Text. 7 Insert the Year attribute into the text box as dynamic text. This will ensure that the document’s title is updated automatically each year. To do this, drag the Year attribute from the Dataset Objects area to the text box. Group your data and selectors into different subdivisions
8 Create five rectangles and place them strategically on the panel. These subdivisions will increase the dashboard’s readability by grouping similar features together while separating distinct features.
© 2015 MicroStrategy, Inc.
Customer Explorer dashboard
925
D
Learning to Design a Document
Advanced Reporting Guide
Each subdivision will display a distinct feature for the user. The first subdivision will encapsulate all of the selectors. The second, third, and fourth subdivisions will contain the following graphs and grid: •
Top Categories
•
Revenue and Profit
•
Customer List
The final subdivision, encompassing the right-hand side of the screen, will store the following three smaller graphs: •
Number of Customers
•
Average Number of Transactions
•
Average Revenue per Transaction
Create text boxes for each subdivision
9 To give structure to your dashboard, label each subdivision with text boxes. Include a title text box in the selector subdivision and name it Demographics Selectors to indicate its functionality. For all other subdivisions, insert text boxes to indicate the name of each graph. 10 In the selector subdivision at the top left, create four text boxes to denote the attributes which the selectors are associated with. Use the following names for these text boxes: •
Gender
•
Marital Status
•
Lifetime
•
Income Range
11 Ensure that end users are aware of the dashboard’s interactive selectors by including an instructional text box in the selector subdivision. A brief message should appear here to encourage end users to experiment with the selectors. Type the following message into the text box: “*Select attribute elements to segment customer base.” Insert grids and graphs into each subdivision
12 Ensure that the Customer List report is in grid view, and insert it into the dashboard by dragging it from the Dataset Objects area to the desired
926 Customer Explorer dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
D
Learning to Design a Document
subdivision. Be sure that you drag the Customer List graph into the subdivision that you previously labeled with the “Customer List” text box. 13 Insert the following five reports into the document as graphs. It can be helpful to format your graph reports in the Report Editor before including them in the document as datasets. Ensure that the following reports are in graph view, and insert them into the appropriate position by dragging them to their corresponding subdivisions: •
Top Categories
•
Revenue and Profit
•
Number of Customers
•
Average Number of Transactions
•
Average Revenue per Transaction
Incorporate interactive selectors
Selectors enable you to create an interactive experience and provide detailed information by allowing user input. Users have the ability to specify their desired attribute values, which are then automatically reflected in the grids and graphs on the dashboard. use the interactive demographics selectors, the Customer ToExplorer dashboard must be viewed in Interactive Mode in MicroStrategy Web. Create selectors
information on the creation of selectors is available in the More Dashboards and Widgets Creation Guide. 14 Create four button bar selectors to correspond to the selector text boxes created above. To do this, open the Insert menu, point to Selector, and choose Button Bar. 15 To ensure that the selectors control the attribute element selection, right click each selector, open the Properties and Formatting dialog, select the Selector area under Properties, and choose Select Attribute Element from the Action Type drop-down menu. 16 Select the corresponding attribute for each selector from the Source drop-down menu.
© 2015 MicroStrategy, Inc.
Customer Explorer dashboard
927
D
Learning to Design a Document
Advanced Reporting Guide
17 The selectors must now be associated with the grid and graphs on your dashboard to provide interoperability. Make sure that all five graphs and the grid on the dashboard are included in the Selected area under Targets for each selector.
Enhancing your analysis When analysts recognize an important trend in the data, how can they better understand the cause of this trend at different business levels?
Customer base analysis To allow analysts to perform in-depth analysis into a trend found in the data, you can provide a link from your document’s attributes to a related report. Using linking and other dashboard creation features lets you make this further analysis available to all end users of the dashboard. The Customer Explorer dashboard created in the preceding steps can be altered to provide an option for detailed data when requested by an analyst. From the following business case scenario, an analyst can see that a specific segment of customers spend heavily in the software category, and generate revenue sporadically throughout the year, as shown in the figure below:
Now that the analyst recognizes that customers in this segment spend heavily in the software category, he wants to take action to take advantage of this trend. Decisive action cannot be taken until the analyst has enough 928 Customer Explorer dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Learning to Design a Document
D
information to develop a strategy to market to these customers. For example, before sending out mailers and e-mails to target this type of customer, the analyst may want to find out further details such as this customer segment’s age range, education level, and geographic region. Investigating the main trend
1 The analysis begins by opening the Customer Explorer Dashboard, and selecting the desired customer demographics. In this case, the customer segment is female and divorced, with a low lifetime value, and an income of greater than 80K, as seen in the image below:
Navigating to the underlying trends
2 An investigation into this customer segment depicts a group which shops at moderate intervals, but spends a large amount with each transaction. This information suggests that company time would be well spent attempting to raise the number of transactions. Analysts may also notice that there are only 2 customers in this segment, prompting a desire to attract more shoppers of this type. All of this information is available in
© 2015 MicroStrategy, Inc.
Customer Explorer dashboard
929
D
Learning to Design a Document
Advanced Reporting Guide
the section of small graphs on the right hand side of the dashboard, as seen below:
In order to attain an accurate depiction of this customer segment, more research into customer characteristics is required. To properly market to this segment, analysts must delve further into the customers’ demographics and purchase history. A separate report with all of the above customer characteristics has been created elsewhere in the project. A sample of this report is displayed below:
Analysts want to be able to access this report along with the data offered to them by the dashboard. Although this information would take up too much
930 Customer Explorer dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Learning to Design a Document
D
space on the current dashboard, a link can be added to the dashboard to provide access to the data when needed. Linking your analysis together
3 You can allow dashboard users to navigate from your high level dashboard directly to the drilled report to continue data analysis by using a drill link. Within your dashboard, right click on the Customer attribute in the Customer List grid, and select Edit Links. To create a hyperlink that will execute your new drilled report, specify the report you would like to reference with a link. In cases where the linked report contains a prompt, as in the report above, specify the desired prompt answering behavior. information on creating drill links is available in the Report More Services Document Creation Guide. 4 You can provide the user with information on where your drill link will go and what the new report will uncover by using a tooltip for the text field. Input this information into the URL display text text box in the Links Editor.
© 2015 MicroStrategy, Inc.
Customer Explorer dashboard
931
D
Learning to Design a Document
Advanced Reporting Guide
Analysis navigation within MicroStrategy Web
5 Now when analysts log in to the project through MicroStrategy Web, they can open the document and observe the new level of analysis available through drilling.
6 By analyzing the customer’s purchase history, analysts may be able to suggest add-on accessories for previously purchased big ticket items, which may draw the customer back for additional shopping. Analysts may also be able to find shopping trends for the most profitable customers,
932 Customer Explorer dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Learning to Design a Document
D
which will allow the company to create a more effective marketing strategy.
With the information returned by the analysis, it is now apparent that this particular customer visits the store around the same time each month. Armed with this information, analysts may decide to send communications to the customer every month around that time to ensure a more consistent schedule of shopping. Furthermore, the items in the purchase history can be used to suggest possible items of interest. The customer profile data which appears in the linked report will allow analysts to target other customers with similar statistics. Additional data provided by this report, such as customer region, tenure, and education level, may prove to be critical when attempting to target highly profitable customers. The drilling technique outlined above allows analysts to investigate specific statistics, such as purchase history, to personalize company communications with the most profitable customer groups, and to create a tailored and more effective marketing strategy. This is just one example of the analysis that can be gathered by drilling on reports and documents. A document can contain many different drill links, which can execute additional reports that drill across different levels, or even allow you to send reports and documents via e-mail. While this example explores customer analysis trends, you can use the linking and drilling techniques discussed in this section to identify trends in various business systems.
© 2015 MicroStrategy, Inc.
Customer Explorer dashboard
933
D
Learning to Design a Document
Advanced Reporting Guide
Revenue distribution and patient satisfaction Consider a healthcare company interested in a study to determine their revenue distribution in terms of the services they offer. You can alter the definitions of the customer metrics from the example above to count the number of patients using the different healthcare services provided. Different revenue metrics can be created to determine the total revenue of each service and the percent of revenue gained per patient within each service. This data can be used to identify services with weak revenue. Drilling can be implemented on these services to determine what underlying costs may be causing the weak revenue numbers. Analyzing revenue on a per service basis can also help a healthcare company decide how it must divide its facilities for each service. Any service that sees a large growth of new patients reveals the need to hire more employees with expertise in the given service field. Patient satisfaction statistics can also be gathered within each service. By drilling across different patient characteristics, you can determine how facilities and services may need to be altered. A study of this type can uncover the need for increased handicap accessibility or improvements with patient counseling programs.
Operational Performance dashboard Dashboards that use managed metrics provide a quantitative focus by presenting lists of metrics or process-driven tabular views of the business. Managing your metrics to obtain information at specific business levels across different periods of time can be the next step towards maintaining a fully developed business intelligence system. To help organize your metrics into an appealing and informative format, implement one or more widgets on your dashboards. Widgets are useful for presenting data in a visually striking manner which allows analysts to digest data without the need to inspect long lists of words and numbers. When analysts require more than just a visual representation, many widgets can provide detailed data through the use of tooltips. The Operational Performance dashboard provides insight into managing your metrics, and using widgets to display them.
934 Operational Performance dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
D
Learning to Design a Document
Dashboard display and usage scenarios
The Operational Performance dashboard displays in Flash view, as shown above. Executives can use this dashboard to gain an understanding of overall sales activity, time comparison data, and profits in specific categories and regions. You can use the dashboard to analyze key performance indicators that measure the performance of the sales organization. Using widgets is very helpful when comparing metrics that use transformations. Widgets allow you to quickly discover what trends are occurring over time, which helps to better focus your analysis of the underlying trends. Although the dashboard in this example focuses on sales data, conversion rate metrics can reflect more than simple sales statistics. For example, businesses that allow their customers to purchase products and services online can manipulate these metrics to produce data on various Web traffic statistics. The versatility of managed metrics and widgets allow you to create visual representations of data for various business models. This section covers these scenarios through instructions on dashboard creation and methods to enhance your analysis.
© 2015 MicroStrategy, Inc.
Operational Performance dashboard
935
D
Learning to Design a Document
Advanced Reporting Guide
Creating the base reports and report objects Before creating a dashboard, you must first create the underlying reports and report objects. These objects link your analytical data with a visually appealing dashboard. To create the necessary objects, follow the general instructions included in this section. following steps cover the creation of only those objects used in The the reports that are part of the Operational Performance dashboard. The objects that are part of the report creation steps below depend on the existence of other objects in your data model. For example, the Fixed year selection filter requires the Year attribute to function. appendix assumes that you have basic object and report This creation skills. For detailed information on creating basic objects and reports, see the Basic Reporting Guide.
Attributes The following attributes are required to create the necessary reports. These attributes are located in \MicroStrategy Tutorial\ Schema Objects\Attributes. •
Category
•
Month
•
Region
Filters The following table lists the filter, and its definition, required to create the necessary reports. This filter is located in \MicroStrategy Tutorial\ Public Objects\Filters. Filter
Definition
Fixed year selection - Most recent year with data in demo DB
Year In list (2009)
936 Operational Performance dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
D
Learning to Design a Document
Transformations Transformations are schema objects, and therefore only a project designer with schema object privileges can create them. A transformation lets you directly analyze data over different time periods. The following transformation is required to create the necessary reports, and is located in \ MicroStrategy Tutorial\Schema Objects\Transformations. •
Last Month’s
information on the creation of transformations is available in More the Project Design Guide.
Metrics The following table lists all of the metrics, and their definitions, that are required to create the necessary reports. These metrics are located in \ MicroStrategy Tutorial\Public Objects\Reports\ MicroStrategy Platform Capabilities\ MicroStrategy Report Services\ and \MicroStrategy Tutorial\Public Objects\Metrics\ Sales Metrics. six “% vs. Forecast” metrics are derived metrics, which must be The created within the dashboard. These metrics do not exist as standalone metrics in any folders within the project. metric definition is provided without being separated by its Each formula, level, condition, and transformation. If you are creating these objects, you can simply type the text for each definition below into the Metric Editor. This defines the formula, level, condition, and transformation as long as every object referenced in the definition has been created correctly. For example, if a metric you build has the target level as Time, then the Time hierarchy must be defined. Name
Definition
% Diff from LM - Avg per Customer
(([Avg Revenue per Customer] - [Last Month's Avg Revenue per Customer]) / [Last Month's Avg Revenue per Customer])
% Diff from LM - Avg per Order (([Avg Revenue per Order] - [Last Month's Avg Revenue per Order]) / [Last Month's Avg Revenue per Order]) % Diff from LM - Profit
((Profit - [Last Month's Profit]) / [Last Month's Profit])
% Diff from LM - Profit Margin
(([Profit Margin] - [Last Month's Profit Margin]) / [Last Month's Profit Margin])
© 2015 MicroStrategy, Inc.
Operational Performance dashboard
937
D
Learning to Design a Document
Advanced Reporting Guide
Name
Definition
% Diff from LM - Revenue
((Revenue - [Last Month's Revenue]) / [Last Month's Revenue])
% Diff from LM - Units Sold
(([Units Sold] - [Last Month's Units Sold]) / [Last Month's Units Sold])
Average Revenue
Avg(Revenue) {~}
Avg Revenue per Customer
(Revenue / [Customer Count])
Avg Revenue per Customer Forecast
([Avg Revenue per Customer] * [AvgCust Forecast Factor])
Avg Revenue per Order
(Revenue / [Order Count])
Avg Revenue per Order Forecast
([Avg Revenue per Order] * [AvgOrder Forecast Factor])
Avg. Avgorder (12TM)
Avg([Avg Revenue per Order]) {}
Avg. Margin (12TM)
Avg([Profit Margin]) {}
Avg. Profit (12TM)
Avg(Profit) {}
Avg. Rev Customer (12TM)
Avg([Avg Revenue per Customer]) {}
Avg. Revenue (12TM)
Avg(Revenue) {}
Avg. Units Sold (12TM)
Avg([Units Sold]) {}
Avgorder vs. Target
[Avg Revenue per Order]
Avgrevcust vs. Target
[Avg Revenue per Customer]
Last Month’s Avg Revenue per Customer
([Last Month's Revenue] / [Last Month's Customer Count])
Last Month’s Avg Revenue per Order
([Last Month's Revenue] / [Last Month's Order Count])
Last Month’s Profit
Sum(Profit) {~} | [Last Month's] |
Last Month’s Profit Margin
([Last Month's Profit] / [Last Month's Revenue])
Last Month’s Revenue
Revenue {~} | [Last Month's] |
Last Month’s Units Sold
Sum([Units Sold]) {~} | [Last Month's] |
Low Avgorder
Sum(0) {~}
Low Avgrevcust
Sum(0) {~}
Low Margin
[Sales Documents] {~+}
Low Profit
[Sales Documents] {~+} | [Year to Quarter] |
Low Rev.
[Sales Documents] {~+}
Low Units
[Sales Documents] {~+} | [Year to Quarter] |
Margin Forecast
([Profit Margin] * [Margin Forecast Factor])
938 Operational Performance dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Learning to Design a Document
D
Name
Definition
Margin vs. Target
[Profit Margin]
Max. Avgorder
([Avg Revenue per Order] * 1.5)
Max. Avgrevcust
([Avg Revenue per Customer] * 1.5)
Max. Margin
([Profit Margin] * 1.5)
Max. Profit
(Profit * 1.5)
Max. Revenue
(Revenue * 1.5)
Max. Units
([Units Sold] * 1.5)
Med Avgorder
Sum(0) {~}
Med Avgrevcust
Sum(0) {~}
Med Margin
Sum(0) {~}
Med Profit
Sum(0) {~}
Med Rev.
Sum(0) {~}
Med Units
Sum(0) {~}
Order Count
[Order Count from Fact] {~}
Profit
Sum(Profit) {~}
Profit Forecast
(Profit * [Profit Forecast Factor])
Profit Margin
(Profit / Revenue)
Profit vs. Target
Profit
Revenue
Revenue {~}
Revenue Forecast
(Revenue * [Revenue Forecast Factor])
Revenue vs. Target
Revenue {~}
Units Sold
Sum([Units Sold]) {~}
Units Sold Forecast
([Units Sold] * [Units Sold Forecast Factor])
Units Sold vs. Target
[Units Sold]
% vs. Forecast (Units Sold)
([Units Sold] / [Units Sold Forecast])
% vs. Forecast (avg rev per customer)
([Avg Revenue per Customer] / [Avg Revenue per Customer Forecast])
% vs. Forecast (Avgorder)
([Avg Revenue per Order] / [Avg Revenue per Order Forecast])
% vs. Forecast (Margin)
([Profit Margin] / [Margin Forecast])
% vs. Forecast (Profit)
(Profit / [Profit Forecast])
% vs. Forecast (Revenue)
(Revenue / [Revenue Forecast])
© 2015 MicroStrategy, Inc.
Operational Performance dashboard
939
D
Learning to Design a Document
Advanced Reporting Guide
Reports Now that you have created or located all of the underlying objects, you can begin creating the reports you need for the dashboard. The underlying objects will be used to build the reports. All of the report objects in the following table are metrics unless noted otherwise. The following table lists the report objects required for each report. Open the Report Editor and create each report on the left, using the objects listed for that report on the right. managed metric reports have many metrics with slight The modifications, which leads to metrics with very similar names. Pay close attention while adding metrics to reports to ensure that you include the correct metrics.
940 Operational Performance dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Learning to Design a Document
D
Reports
Report Objects
KPI List
• Month (Attribute) • Fixed year selection - Most recent year with data in demo DB (Filter) • % Diff from LM - Avg per Customer • % Diff from LM - Avg per Order • % Diff from LM - Profit • % Diff from LM - Profit Margin • % Diff from LM - Revenue • % Diff from LM - Units Sold • Avg Revenue per Customer • Avg Revenue per Customer Forecast • Avg Revenue per Order • Avg Revenue per Order Forecast • Avg. Avgorder (12TM) • Avg. Margin (12TM) • Avg. Profit (12TM) • Avg. Rev Customer (12TM) • Avg. Revenue (12TM) • Avg. Units Sold (12TM) • AvgCust Forecast Factor • AvgCust Forecast Percentage • AvgCust Rand Metric • AvgOrder Forecast Factor • AvgOrder Forecast Percentage • Avgorder vs. Target • Avgrevcust vs. Target • Last Month’s Avg Revenue per Customer • Last Month’s Avg Revenue per Order • Last Month’s Profit • Last Month’s Profit Margin • Last Month’s Revenue • Last Month’s Units Sold • Low Avgorder • Low Avgrevcust • Low Margin • Low Profit • Low Rev. • Low Units • Margin Forecast • Margin Forecast Factor • Margin Forecast Percentage • Margin Rand Metric • Margin vs. Target • Max. Avgorder • Max. Avgrevcust • Max. Margin • Max. Profit • Max. Revenue • Max. Units
This graph report displays the changes in some key metrics throughout the year 2009.
© 2015 MicroStrategy, Inc.
Operational Performance dashboard
941
D
Learning to Design a Document
Advanced Reporting Guide
Reports
Report Objects
KPI List (continued)
• • • • • • • • • • • • • • • • • • • • • • • • • • • •
Med Avgorder Med Avgrevcust Med Margin Med Profit Med Rev. Med Units Order Count Forecast Profit Profit Forecast Profit margin Profit vs. Target Revenue Revenue Forecast Revenue vs. Target Trend - % Diff from LM - Avg per Customer Trend - % Diff from LM - Avg per Order Trend - % Diff from LM - Customer Count Trend - % Diff from LM - Order Count Trend - % Diff from LM - Profit Trend - % Diff from LM - Profit Margin Trend - % Diff from LM - Revenue Trend - % Diff from LM - Units Sold Units Sold Units Sold Forecast Units Sold Forecast Factor Units Sold Forecast Percentage Units Sold Rand Metric Units Sold vs. Target
KPI List - Region and Category
• • • •
Category (Attribute) Month (Attribute) Region (Attribute) Fixed year selection - Most recent year with data in demo DB (Filter) Avg Revenue per Customer Order Count Profit Margin Revenue
This simple grid report aggregates a few key metrics at the month and region levels.
• • • •
942 Operational Performance dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
D
Learning to Design a Document
KPI List graph report contains additional metrics that are not The listed in the table above. The table lists only the required metrics that are used in the Operational Performance dashboard.
Creating the dashboard You can now enhance the analytical power of each report by integrating them into a single dashboard. Customizing the dashboard formatting can improve your document by better reflecting your business standards.
Dataset objects You have already seen the two reports that will be added as datasets to your dashboard. The reports listed are located in \MicroStrategy Tutorial\ Public Objects\Reports\ MicroStrategy Platform Capabilities\ MicroStrategy Report Services\ MicroStrategy Widget Library\Datasets. •
KPI List
•
KPI List - Region and Category
Create a new dashboard Now that you are familiar with the location of the datasets, you can create a new dashboard to display them. This can be accomplished from either MicroStrategy Developer or MicroStrategy Web. Create a new dashboard in MicroStrategy Developer
1 In MicroStrategy Developer, from the New Object drop-down menu, select Document. 2 Click the Dashboards tab, and select 01 Blank Dashboard. 3 Delete the blank panel stack. 4 Navigate to the report location displayed in the Dataset objects section above, and select the dataset reports.
© 2015 MicroStrategy, Inc.
Operational Performance dashboard
943
D
Learning to Design a Document
Advanced Reporting Guide
5 Ensure that the KPI List report is the primary dataset by verifying that it is displayed in bold in the Dataset Objects area. If this report is not displayed in bold, right-click it and select Set as Grouping and Sorting Dataset. Create a new dashboard in MicroStrategy Web
1 In MicroStrategy Web, click the MicroStrategy icon of any page and select Create Document.
at the upper left
2 In the Dashboard Templates area, select 01 Blank Dashboard. 3 Delete the blank panel stack. 4 Click Add Dataset in the Dataset Objects area, and add the dataset reports from the Dataset objects section above. 5 Ensure that the KPI List report is the primary dataset by verifying that it is displayed in bold in the Dataset Objects area. If this report is not displayed in bold, right-click it and select Set as Grouping and Sorting Dataset.
Dashboard details Add a title to your dashboard
1 To add a descriptive title that will appear at the beginning of your document, open the Insert menu and select Text. 2 Type the following title into the text box, and format it to Bold by using the format tool bar: “OPERATIONAL PERFORMANCE DASHBOARD”. information on creating titles in Report Services documents More is available in the Report Services Document Creation Guide.
944 Operational Performance dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Learning to Design a Document
D
Metrics used to create the Microchart widget The Microchart widget, which appears in the top portion of the Operational Performance dashboard, includes the following six key performance indicators (KPIs): •
Revenue
•
Profit
•
Profit Margin
•
Units Sold
•
Average Revenue per Order
•
Average Revenue per Customer
Each KPI has ten associated metrics in the KPI List grid. To display the sparkline chart, bullet chart, and additional metrics in the Microchart widget, variations of the following ten metrics are placed into the grid for each KPI. “Revenue” with each of the six KPI names to get all 60 metric Replace names. •
Revenue
•
Average Revenue (12 TM)
•
Revenue vs. Target
•
Max Revenue
•
Low Revenue
•
Med Revenue
•
Revenue Forecast
•
Last Month’s Revenue
© 2015 MicroStrategy, Inc.
Operational Performance dashboard
945
D
Learning to Design a Document
•
% Diff from LM - Revenue
•
% vs. Forecast (Rev)
Advanced Reporting Guide
% vs. Forecast metrics are derived metrics which must be The created in the Dashboard Editor. Create the % vs. Forecast metrics
There are six % vs. Forecast metrics that you must create in the Dashboard Editor, as they are not available as standalone metrics. 3 In the Dataset Objects area, under KPI List, right-click any metric, and select Insert New Metric. 4 Type the name of the new metric in the Name text box. For example, type “% Vs. Forecast (Rev)”. 5 Type the desired operation in the Definition area. For example, type “Revenue / [Revenue Forecast]”. 6 Click OK. 7 In the Dataset Objects area, right-click the newly created metric, and select Number Format. 8 From the Category list, select Percentage, and type 1 into the Decimal places text box. Alter the metric display names
You may need to change the names of some of the metrics, so that the column headers in the microchart widget are standardized. The metric display names can be changed from the Dashboard Editor in MicroStrategy Developer. 9 If you are not in Design view, open the View menu, and click Design. 10 Double click the grid to select it. 11 From the Data menu, select Grid Data Options. 12 Under Categories, expand Display, and select Alias.
946 Operational Performance dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Learning to Design a Document
D
13 Type the desired display name for each metric in the corresponding Alias field. The following table lists the suggested alias for several metrics: Metric
Alias
% Diff from LM - Avg per Customer
%TM-LM
% Diff from LM - Avg per Order
%TM-LM
% Diff from LM - Profit
%TM-LM
% Diff from LM - Profit Margin
%TM-LM
% Diff from LM - Revenue
%TM-LM
% Diff from LM - Units Sold
%TM-LM
% vs Forecast (Units Sold)
%Forecast
% vs. Forecast (avg rev per customer)
%Forecast
% vs. Forecast (Avgorder)
%Forecast
% vs. Forecast (Margin)
%Forecast
% vs. Forecast (Profit)
%Forecast
% vs. Forecast (Rev)
%Forecast
Avg Revenue per Customer
Avg Rev per Cust.
Avg Revenue per Customer Forecast
Forecast
Avg Revenue per Order
Avg Rev per Order
Avg Revenue per Order Forecast
Forecast
Avg. Avgorder (12TM)
Avg Rev. per Order (12TM)
Avg. Rev Customer (12TM)
Avg. Rev. Customer (12TM)
Avgorder vs. Target
This Month
Avgrevcust vs. Target
This Month
Last Month’s Avg Revenue per Customer
Last Month
Last Month’s Avg Revenue per Order
Last Month
Last Month’s Profit
Last Month
Last Month’s Profit Margin
Last Month
Last Month’s Revenue
Last Month
Last Month’s Units Sold
Last Month
Margin Forecast
Forecast
Margin vs. Target
This Month
Profit Forecast
Forecast
© 2015 MicroStrategy, Inc.
Operational Performance dashboard
947
D
Learning to Design a Document
Advanced Reporting Guide
Metric
Alias
Profit Margin
Margin
Profit vs. Target
This Month
Revenue Forecast
Forecast
Revenue vs. Target
This Month
Units Sold Forecast
Forecast
Units Sold vs. Target
This Month
Create a grid for the Microchart widget
To create the Microchart widget, a grid must first be inserted into the dashboard. This grid will contain the Month attribute, along with the 60 metrics mentioned in Metrics used to create the Microchart widget, page 945. 14 To insert a grid into the dashboard, open the Insert menu, and select Grid. Use the cross hairs to position the grid, and determine its size. 15 From the KPI List dataset in the Dataset Objects dialog box, drag and drop the Month attribute and the 60 required metrics into the grid. Ensure that you keep all related metrics together in the order outlined in mentioned in Metrics used to create the Microchart widget, page 945. The first ten metrics are displayed in the image below:
Continue inserting the metrics in this order, until all 60 have been inserted. 16 To display the grid as a Microchart widget in Flash view, right-click the grid and select Properties and Formatting. Select Widget from the Properties area. From the Widget drop-down list, point to Flash and
948 Operational Performance dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Learning to Design a Document
D
then select Microcharts. Ensure that Flash is selected under Display widget in.
Fine tune your display settings with the Widget Properties dialog box From Flash view, you can see the initial display of the Microchart widget. Customize this display by altering the options in the Widget Properties dialog box. Use this dialog box to adjust the number of metrics used for each KPI, alter the look and feel of the microcharts, and adjust the labels in the widget. To open the widget properties, make sure you are in Flash view, right-click the Microcharts widget, and select Properties. Set the number of metrics to use for each KPI
17 From the Microcharts Properties dialog box, select Mode from the drop-down menu. 18 Select the Mode tab and type 10 into the Metrics per KPI text box. Adjust the display for the sparkline and bullet charts
19 From the Microcharts Properties dialog box, select Options from the drop-down menu. 20 On the Sparkline tab, ensure that only the following options are enabled: •
Show sparkline graph
•
End points
•
Reference line
•
Reference area
•
Associated metric
•
Show Tooltips
•
Inherit grid formatting
© 2015 MicroStrategy, Inc.
Operational Performance dashboard
949
D
Learning to Design a Document
Advanced Reporting Guide
21 On the Bullet tab, ensure that only the following options are enabled: •
Show bullet graph
•
Reference line
•
Reference bands
•
Show Tooltips
•
Inherit grid formatting
Alter the labels in the Microcharts widget display
22 From the Microcharts Properties dialog box, select Labels from the drop-down menu. 23 On the Sparkline tab, type the following labels: •
Header: 12 Trailing Months
•
Associated metric: This Month
24 On the Bullet tab, type the following labels: •
Header: Actual vs. Forecast
•
Associated metric: Actual
•
Band 1: Low
•
Band 2: Medium
•
Band 3: High
Adjust the position and the width of the columns
25 To move the metrics or charts to a new position on the grid, drag and drop the column header to the desired position. 26 To resize a column, hover the cursor over the left most or right most edge of the desired column header until the cursor transforms into the resizing cursor, then drag the column edge to the desired size.
950 Operational Performance dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
D
Learning to Design a Document
Creating the Bubble Grid widget Like the Microchart widget described above, the Bubble Grid widget in the Operational Performance dashboard is created from a pre-existing report. The KPI List - Region and Category report contains three attributes and five metrics, which are used to build the Bubble Grid widget displayed below.
Create a grid for the Bubble Grid widget
To create the Bubble Grid widget, a grid must first be inserted into the dashboard. This grid will contain the Region and Category attributes, along with the Order Count and Profit Margin metrics. 1 Create a new panel stack to hold the grid. This panel stack will allow you to set up selector controls. To create a panel stack, open the Insert menu and select Panel Stack. 2 To insert a grid into the dashboard, open the Insert menu and select Grid. Use the cross hairs to position the grid on the panel stack and determine its size. 3 From the KPI List - Region and Category dataset in the Dataset Objects dialog box, drag and drop the Region and Category attributes and the Order Count and Profit Margin metrics into the grid. 4 To display the grid as a graph in situations where the user is not using Flash view, right-click the grid, point to View Mode, and select Graph View. 5 To display the graph as a widget in Flash mode, right-click the graph, and select Properties and Formatting. Under Properties, select Widget. In the Widget Selection area, select Bubble Grid from the Widget drop-down menu. Ensure that Flash is enabled under Display widget in.
© 2015 MicroStrategy, Inc.
Operational Performance dashboard
951
D
Learning to Design a Document
Advanced Reporting Guide
Providing interactivity with selectors Now that you have created the Bubble Grid widget, you can provide end users with the ability to interactively choose the month for which the widget displays its data. The 12 Trailing Months sparkline chart that appears in the Microcharts widget will be used as the selector for your Bubble Grid widget. Use your Microcharts widget as a month selector for the Bubble Grid widget
6 The month attribute in the Microcharts widget must be set as the selector for the panel stack that contains the Bubble Grid widget. To do this, in the KPI List grid in the top portion of the dashboard, select the Month attribute, right-click it, and select Use as Selector. 7 To alert users of the selector functionality, place a message in the panel stack. To do this, select the panel stack, open the Insert menu, and select Text. Type the following message into the text box: “Select month on the sparkline chart to display Order Count (bubble size) vs. Profit Margin (bubble color) by Region and Category for” 8 Place a dynamic text box to the right of the previous text box to display the currently selected date. To do this, drag the Month attribute from the KPI List - Region and Category dataset in the Dataset Objects dialog box.
Enhancing your analysis By using managed metrics, you have created a side-by-side comparison of sets of data differentiated by time, and enhanced the analytical power of your dashboard. A well-formatted visual image can do more than look attractive; it can help analysts quickly recognize important business trends within your company. Finding these trends in the data better prepares analysts to alter short and long term strategies. The following example shows how managed metrics can be used in conjunction with visual images, such as gauge graphs, to display sales trends.
952 Operational Performance dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Learning to Design a Document
D
Sales trends By using managed metrics, you can create simple side-by-side comparisons of your data to track how your current metric values compare to metric values at various points in time. The image below compares sales metrics from 2006 Q2 to the Year to Quarter transformation total of both 2006 Q1 and 2006 Q2:
This document helps analysts visualize how the second quarter contributed to sales in comparison with the first and second quarter combined. The gauges in the document shown above are created as separate graph reports. Each gauge displays the net value amount of the metric name above it. For © 2015 MicroStrategy, Inc.
Operational Performance dashboard
953
D
Learning to Design a Document
Advanced Reporting Guide
example, the gauge in the bottom left corner of the document uses the Net Sales Order Amount metric. Using the graphs, an analyst can quickly determine that the second quarter contributed much less to sales for the first half of the year than the first quarter. This signifies a declining trend in sales. Analysts may then recognize the larger gap in terms of sales orders than for lost quotation sales between the two quarters. This indicates that a decline in sales in the second quarter was due to the fact that fewer orders were taken, rather than due to losing quotation sales. From the data presented by these gauge graphs, an analyst can help plan your company’s sales strategies to focus on capturing more sales orders. This analysis justifies actions such as increasing the number of sales opportunities pursued or focusing more time on sales orders with the opportunity for higher profits. Sales trends can be analyzed in a similar manner at different business levels. For example, analysts may want to compare how sales are affected by the acquisition and retention of customers. The sales metrics in this section can be manipulated to distinguish between new and existing customers. If 70 percent of the company’s sales comes from existing customers, efforts to retain these customers should be a high priority. Analysts have the ability to manipulate the sales metrics to represent sales figures for separate customers. Breaking down sales on a per customer basis determines the most profitable customers for your business. Extra effort must be taken to retain these important customers to maintain or increase sales performance.
Web traffic As more customers transition from purchasing goods and services in a store to purchasing them over the Internet, it becomes just as important to monitor web traffic as it is to monitor a store’s productivity. The metrics used to build your original document include metrics which measure the conversion rates of different sales and shipment figures. Within your business system, you can tune these metrics to reflect different web traffic statistics. Consider a telecom business interested in increasing sales through their online market. How would you provide the telecom business with analysis of their web traffic in an effort to boost purchase order conversion rates?
954 Operational Performance dashboard
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Learning to Design a Document
D
You need a logical data model that can track the conversion rate of customers purchasing products and services through the online market. This data can show how many customers began the purchasing process but then cancelled their order before purchasing anything. This analysis is achievable by mapping such data to metrics similar in nature to the conversion metrics used in this section. By using conversion metrics, an online market can be analyzed to see at what point most customers back out of the purchasing process. If a large number of customers end their session at the first page, an analyst can deduce that a problem exists with customers not being able to find the product or service they are looking for. Your company now has the ability to tailor an action plan to address the issues found through your analysis. Actions to improve online navigability can help improve conversion rates by making the site more user-friendly, and thus drive more sales through your online market. Another action may be to offer more promotions throughout the purchasing process to give customers incentives to complete the transaction. Analyzing web traffic includes much more than just conversion rates. Monitoring new and returning web customers can help show the strength of your online market to hold a customer base. Comparing online revenue to storewide revenue can show which products and services sell well through online sales and which perform more strongly when sold in stores.
© 2015 MicroStrategy, Inc.
Operational Performance dashboard
955
D
Learning to Design a Document
Advanced Reporting Guide
956 Operational Performance dashboard
© 2015 MicroStrategy, Inc.
E SORTING AND BROWSING STANDARDS E.
Introduction MicroStrategy employs its own sorting and browsing standards for characters and MicroStrategy projects. The sorting is also dependent on your machine’s locale. International Components for Unicode (ICU) libraries are used to compare strings. ICU uses the Unicode Collation Algorithm, which sorts on multiple levels, as described below: 1 Letters are sorted first. 2 Differences in accents resolve any ties resulting from the first sort level. 3 Differences in case (lower case vs. upper case) resolve any ties. 4 Differences in punctuation resolve any ties.
© 2015 MicroStrategy, Inc.
957
E
Sorting and Browsing Standards
Advanced Reporting Guide
Sort order of characters in MicroStrategy The sort order of characters in MicroStrategy is different from ASCII standards. The MicroStrategy sort order of characters affects: •
The order of files and folders in MicroStrategy Developer and MicroStrategy Web
•
The order of ascending and descending sorts of alphanumeric values in MicroStrategy reports
The sort order described below is for English locales only. The sort order may be different in different locales. The table below lists the non-printable characters which are first in the sort order. All these characters are equal to each other, so the final sorting order is determined by which character is retrieved from the data warehouse first. characters that are non-printable characters are listed in the ASCII tables below with their character representation. For example, a backspace is represented as BS. NULL
SOH
STX
ETX
EOT
ENQ
ACK
BEL
BS
SO
SI
DLE
DCL
DC2
DC3
DC4
NAK
SYN
ETB
CANC
EM
SUB
ESC
FS
GS
RS
US
DEL
The table below lists the sort order of characters in MicroStrategy. The order goes from left to right and top to bottom. For example, the top left table cell (TAB) is the beginning of the sort order, the cell to the right of that (LF) is next in order, and the bottom right cell (Z) is the end of the sort order. TAB
LF
VT
FF
CR
‘
^
_
-
,
;
:
!
?
.
‘
“
(
)
[
]
{
}
@
*
/
\
&
#
%
+
<
=
>
|
~
$
0
1
2
3
4
5
6
7
8
9
a
A
b
B
c
C
d
D
e
E
f
F
g
G
h
H
i
I
j
J
k
K
l
L
m
M
n
N
o
O
p
P
958 Sort order of characters in MicroStrategy
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
E
Sorting and Browsing Standards
q
Q
r
R
s
S
t
T
u
U
v
V
w
W
x
x
y
Y
z
Z
Project order in MicroStrategy Developer and Web Projects in MicroStrategy can only be created in MicroStrategy Developer, but they can be accessed in both MicroStrategy Developer and Web. After a project is created and its associated project source is refreshed, the projects can be sorted and browsed in the following ways: •
MicroStrategy Developer: The projects are sorted in alphabetical order within the folder list, as shown in the image below.
•
Web: By default, projects are sorted by project name, without regard to which Intelligence Server that they are on, but you can use MicroStrategy Web to change how the projects are sorted.
The Server Sorting property indicates whether the projects are grouped together by Intelligence Server before sorting. The options are: – None: (Default) Projects are sorted independently of which Intelligence Server they are on.
© 2015 MicroStrategy, Inc.
Project order in MicroStrategy Developer and Web
959
E
Sorting and Browsing Standards
Advanced Reporting Guide
– By Name: Intelligence Servers are sorted alphabetically by name, and then projects are sorted within each Intelligence Server. – Default: Intelligence Servers are sorted in the order in which MicroStrategy Web connects to them, and then projects are sorted within each Intelligence Server.
The Project Sorting property indicates how the projects are sorted. The options are: – Default: Projects are sorted in the order in which the projects are returned from Intelligence Server. – By Name: (Default) The projects are sorted by project name, in ascending alphabetical order. – By Description: The projects are sorted by project description, in ascending alphabetical order.
Both the Server Sorting and Project Sorting properties are found in the Default Server Properties section on the Web Administrator page.
You can also define a custom sort order for displaying projects, by typing a number in the Project Sort Index field in the Project Display page in the Project Defaults level of the Preferences. Any projects with a custom sort index are sorted before all other projects, regardless of how the Server Sorting and Project Sorting properties are defined. If two projects have the same sort order index, then they are listed in the order specified by the Project Sorting property described above.
960 Project order in MicroStrategy Developer and Web
© 2015 MicroStrategy, Inc.
GLOSSARY aggregate function A numeric function that acts on a column of data and produces a single result. Examples include SUM, COUNT, MAX, MIN, and AVG. aggregate table A fact table that stores data that has been aggregated along one or more dimensions. See pre-aggregation. aggregation The combining of numeric data at a specific attribute level. The most common function is sum, which creates an additive total. See also pre-aggregation. allocation An optional aspect of a fact extension that allows distribution of values according to a user-defined calculation expression. Compare degradation. Analytical Engine A component of MicroStrategy Intelligence Server responsible for performing complex mathematical calculations, consolidations, and advanced analytical functions needed to create a report. The Analytical Engine enables you to manipulate report data by sorting, pivoting, page-by, calculating thresholds, calculating subtotals and metrics. The Analytical Engine evaluates a filter without © 2015 MicroStrategy, Inc.
Glossary: aggregate function
961
Glossary
Advanced Reporting Guide
re-executing the report and also facilitates hierarchical display of custom groups. application-level In application-level partitioning, the application rather than partition the database server manages the partition tables. MicroStrategy supports two methods of application-level partitioning: metadata partition mapping and warehouse partition mapping. Compare database-level partition. application object MicroStrategy object used to provide analysis of and insight into relevant data. Application objects are developed in MicroStrategy Developer and they are the building blocks for reports and documents. Application objects include these object types: report, document, template, filter, metric, custom group, consolidation, prompt. Apply function A function that allows you to insert custom SQL into an expression. See also pass-through expression. atomic The lowest level of granularity. Cannot be decomposed into smaller parts. attribute A data level defined by the system architect and associated with one or more columns in a data warehouse lookup table. Attributes include data classifications like Region, Order, Customer, Age, Item, City, and Year. They provide a means for aggregating and filtering at a given level.
962 Glossary: application-level partition
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Glossary
See also: •
attribute element
•
attribute form
•
child attribute
•
constant attribute
•
derived attribute
•
parent attribute
attribute element A value of any of the attribute forms of an attribute. For example, New York and Dallas are elements of the attribute City; January, February, and March are elements of the attribute Month. attribute form One of several columns associated with an attribute that are different aspects of the same thing. ID, Name, Last Name, Long Description, and Abbreviation could be forms of the attribute Customer. Every attribute supports its own collection of forms. attribute relationship See relationship. attribute role A database column that is used to define more than one attribute. For example, Billing City and Shipping City are two attributes that have the same table and columns defined as a lookup table. banding A method of organizing values according to a set of descriptive or meaningful data ranges called buckets. For example, customers in the age ranges of 10–20, 21–30, and 31–40, where each set of ages is a band. Banding is also used for display purposes, where every other row is a different color and the two colors alternate. Compare consolidation.
© 2015 MicroStrategy, Inc.
Glossary: attribute element
963
Glossary
Advanced Reporting Guide
base table A fact table that stores data at the lowest level of dimensionality. cache A special data store holding recently accessed information for quick future access. This is normally done for frequently requested reports, whose execution is faster because they need not run against the database. Results from the data warehouse are stored separately and can be used by new job requests that require the same data. In the MicroStrategy environment, when a user runs a report for the first time, the job is submitted to the database for processing. However, if the results of that report are cached, the results can be returned immediately without having to wait for the database to process the job the next time the report is run. category In a graph, the set of data along the X-axis. Categories generally correspond to the rows of a grid report. An example of a category is a bar in a bar graph. child attribute The lower-level attribute in an attribute relationship. See also: •
parent attribute
•
relationship
compound attribute An attribute that has more than one key (ID) form. compound key In a relational database, a primary key consisting of more than one database column. compound metric A type of metric defined by a formula based on arithmetic operators and non-group functions. Arithmetic operators are +, -, *, and /; non-group functions are OLAP and scalar functions such as running sum or rank. The operators and functions can be applied to facts, attributes, or metrics. Examples are RunningAvg(Cost_Metric) and Sum(Cost_Metric) + Sum(Profit_Metric).
964 Glossary: base table
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Glossary
compression ratio The average number of child records combined to calculate one parent record. For example, the compression of ratio between monthly data and yearly data is 12:1. This is used to determine where aggregate tables would have the greatest impact. The larger the compression ratio between two attributes, the more you stand to gain by creating an aggregate table that pre-calculates the higher-level data. configuration object A MicroStrategy object appearing in the system layer and usable across multiple projects. Configuration objects include these object types: users, database instances, database login IDs, schedules. consolidation An object that can be placed on a template and is made up of an ordered collection of elements called consolidation elements. Each element is a grouping of attribute elements that accommodates inter-row arithmetic operations. Compare custom group. consolidation element A line item in a consolidation based on attribute elements. For example, Year=2002 / Year=2003. constant attribute See implicit attribute. custom group An object that can be placed on a template and is made up of an ordered collection of elements called custom group elements. Each element contains its own set of filtering qualifications. dashboard A popular means of displaying and distributing data from business intelligence projects. Dashboards provide key metrics as well as summary information. data definition Report execution steps that establish how the data is accessed and manipulated in the data warehouse.
© 2015 MicroStrategy, Inc.
Glossary: compression ratio
965
Glossary
Advanced Reporting Guide
data mining A technique that is generally used to find hidden predictive information from a large amount of data. This process involves using existing information to gain new insights into business activities by applying predictive models, using analysis techniques such as regression, classification, clustering, and association. Data Explorer A portion of the interface used to browse through data contained in the warehouse. Users can navigate through hierarchies of attributes that are defined by the administrator to find the data they need. database-level partition In database-level partitioning (sometimes called server-level partitioning), the database server rather than MicroStrategy manages the partitioned tables. The original table is not physically broken into smaller tables. Instead, the database server logically partitions the table according to parameters specified by the database administrator. You do not need to take any action in MicroStrategy to support the partitioning. Since only the logical table is displayed to the end user, the partitioning is transparent to MicroStrategy. Compare application-level partitioning. data mart 1) A database, usually smaller than a data warehouse, designed to help managers make strategic decisions about their business by focusing on a specific subject or department. 2) A database instance used to store result sets saved to data mart tables. data mart report A special kind of report that saves its report data in a database rather than returning those results to the user. Data mart reports either create a new table in the database to store the report data or append the report data into an existing table. data mart table A relational table that is used to store the report data from a data mart report.
966 Glossary: data mining
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Glossary
data warehouse 1) A database, typically very large, containing the historical data of an enterprise. Used for decision support or business intelligence, it organizes data and allows coordinated updates and loads. 2) A copy of transaction data specifically structured for query, reporting, and analysis. degradation A type of fact extension in which values at one level of aggregation are reported at a second, lower attribute level. Compare allocation. derived attribute An attribute calculated from a mathematical operation on columns in a warehouse table. For example, Age can be calculated from the expression [Current Date–Birth Date]. See also: •
attribute
•
implicit attribute
derived metric A metric based on data already available on the report. It is calculated on the Intelligence Server, not in the database. Use a derived metric to perform column math, that is, calculations on other metrics, on report data after it has been returned from the database. dimensionality See level. drill A method of obtaining supplementary information after a report has been executed. The new data is retrieved by requerying the Intelligent Cube or database at a different attribute or fact level.
© 2015 MicroStrategy, Inc.
Glossary: data warehouse
967
Glossary
Advanced Reporting Guide
See also: •
page-by
•
pivot
•
sort
•
subtotal
dynamic aggregation Rollup of metric values that occurs when an attribute is moved from the report grid to the Report Objects. Whenever the attributes in the Report Objects are not the same as the attributes on the grid, dynamic aggregation has occurred. Dynamic aggregation happens on-the-fly, in memory. dynamic relationship When the relationship between elements of parent and child attributes changes. These changes often occur because of organizational restructuring; geographical realignment; or the addition, reclassification, or discontinuation of items or services. For example, a store may decide to reclassify the department to which items belong. entry level The lowest level set of attributes at which a fact is available for analysis. extraction, 1) The process used to populate a data warehouse from transformation, and disparate existing database systems. loading (ETL) 2) Third-party software used to facilitate such a process. fact 1) A measurement value, often numeric and typically aggregatable, stored in a data warehouse. 2) A schema object representing a column in a data warehouse table and containing basic or aggregated numbers—usually prices, or sales in dollars, or inventory quantities in counts. See also metric.
968 Glossary: dynamic aggregation
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Glossary
fact table A database table containing numeric data that can be aggregated along one or more dimensions. Fact tables can contain atomic or summarized data. Compare: •
aggregate table
•
base table
filter A MicroStrategy object that specifies the conditions that the data must meet to be included in the report results. Using a filter on a report narrows the data to consider only the information that is relevant to answer your business question, since a report queries the database against all the data stored in the data warehouse. A filter is composed of at least one qualification, which is the actual condition that must be met for the data to be included on a report. Multiple qualifications in a single filter are combined using logical operators. Examples include “Region = Northeast” or “Revenue > $1 million”. A filter is normally implemented in the SQL WHERE clause. formatting layer The part of a report that allows you to control how a report looks. The basic formatting layers are zones, which are the rows and headers of a report, and grid units, which are the attribute values. Other formatting layers, such as thresholds and subtotals, can be thought of as extensions of these two basic types. formatting zone Determines what formatting is applied to any data or object located in the zone. When an object on a report is moved from one formatting zone to another (as a result of pivoting, for example), the formatting of the object changes based on the new zone. Freeform SQL A MicroStrategy reporting feature that allows you to use your own customized SQL statements to retrieve data from any relational databases that are included in a MicroStrategy project.
© 2015 MicroStrategy, Inc.
Glossary: fact table
969
Glossary
Advanced Reporting Guide
grid unit The individual attributes, metrics, consolidations, and custom groups that can be placed on a report grid. hierarchy A set of attributes defining a meaningful path for element browsing or drilling. The order of the attributes is typically—though not always—defined such that a higher attribute has a one-to-many relationship with its child attributes. HTML document 1) A compound report displaying multiple grids and graphs. 2) The MicroStrategy object that supports such a report. For information about HTML documents, such as procedures to create and format them, see the MicroStrategy Developer help (formerly the MicroStrategy Desktop help). also offers Report Services documents, MicroStrategy in a separate product called MicroStrategy Report Services. This product helps you create Report Services documents and interactive dashboards that can be used with Flash. For information about Report Services documents, see the Document and Dashboard Analysis Guide and the Report Services Document Creation Guide. implicit attribute An attribute that does not physically exist in the database because it is created at the application level. Such an attribute has its expression defined as a constant value, though nothing is saved in a column. For example, you may wish to create columns in the database with a value of 1 for every row to get around COUNT limitations. You do not have to actually create the column, though, because in the Attribute Editor, you can just enter a “1” in the expression to create a count. Implicit attributes are useful in analyzing and retrieving information. When analyzing data, you can use constant attributes to create a COUNT to keep track of the number of rows returned. You can use constant attributes when building metrics, where you can sum the column holding the constant to create a COUNT. Any constant is acceptable. Compare derived attribute.
970 Glossary: grid unit
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Glossary
Intelligent Cube A copy of the report data saved in memory and used for manipulation of the view definition. This division allows multiple reports with different views to share a common data definition. joint children Joint child relationships are another type of many-to-many relationship where one attribute has a many-to-many relationship to two otherwise unrelated attributes. These relationships can be modeled and conceptualized like traditional attributes, but like facts, they exist at the intersection of multiple attribute levels. For example, consider the relationship between three attributes: promotion, item, and quarter. In this case, promotion has a many-to-many relationship to both item and quarter. An example of a promotion might be a “Red Sale” where all red items are on sale. A business might run this promotion around Valentine's Day (Q1) and again at Christmas time (Q4). key form One of a set of attribute forms required for unique identification of an element in an attribute. Also called the ID or ID form. See also attribute form. level 1) In a data warehouse, facts are said to be stored at a particular level defined by the attribute IDs present in the fact table. For example, if a fact table has a Date column, an Item_ID column, and a fact column, that fact is stored at the Date/Item level. 2) With regard to metric calculation, the level is the level of calculation for the metric. For example, a metric on a report with Year and Store attributes would be calculated at the Year/Store level. See also level of aggregation. level of aggregation The point in an attribute hierarchy where aggregation is performed. For example, in the geographical State--City--Store hierarchy there are three possible levels of aggregation.
© 2015 MicroStrategy, Inc.
Glossary: Intelligent Cube
971
Glossary
Advanced Reporting Guide
link A connection in one report or document to another report or document. A link lets an analyst execute another document or report (the target) from a document or report (the source), and to pass parameters to answer any prompts that are in the target. logical data model A graphical representation of data that is arranged logically for the general user, as opposed to the physical data model or warehouse schema, which arranges data for efficient database use. logical table Logical tables are MicroStrategy objects that form the foundation of a schema. Logical tables in the MicroStrategy schema consist of attributes and facts. There are three types of logical tables: - A logical table is created for each physical table that is imported into a project, using the Warehouse Catalog. This type of logical tables maps directly to physical tables in the data warehouse. - A logical view does not point directly to a physical table and is defined using a SQL query against the data warehouse. - A table alias points directly to a physical table. A table alias can have a different name from the physical table. See also: •
logical view
•
table alias
logical view One of the three types of logical tables in the MicroStrategy environment. The other two types are logical tables and table aliases. A logical view does not point directly to a physical table and is defined using a SQL query against the data warehouse. Using MicroStrategy, you can create logical views, which can be used in the same way as the logical tables. This means that you define attributes, facts, and other schema objects based on the logical views. See also logical table.
972 Glossary: link
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Glossary
locked hierarchy A hierarchy that has at least one attribute that may not be browsed by end users. Application Designers typically lock hierarchies if there are so many attribute elements that element browsing is not usable. managed object A schema object unrelated to the project schema, which is created by the system and stored in a separate system folder. Managed objects are used to map data to attributes, metrics, hierarchies and other schema objects for Freeform SQL, Query Builder, and MDX cube reports. many-to-many An attribute relationship in which multiple elements of a parent attribute can relate to multiple elements of a child attribute, and vice versa. See also: •
one-to-one
•
one-to-many
•
many-to-one
•
relationship
many-to-one An attribute relationship in which (1) multiple elements of a parent attribute relate to only one element of a child attribute, and (2) every element of the child attribute can relate to multiple elements of the parent. See also: •
one-to-one
•
one-to-many
•
many-to-many
•
relationship
metadata A repository whose data associates the tables and columns of a data warehouse with user-defined attributes and facts to enable the mapping of the business view, terms, and needs to the underlying database structure. Metadata can reside on
© 2015 MicroStrategy, Inc.
Glossary: locked hierarchy
973
Glossary
Advanced Reporting Guide
the same server as the data warehouse or on a different database server. It can even be held in a different RDBMS. metric 1) A business calculation defined by an expression built with functions, facts, attributes, or other metrics. For example: Sum(dollar_sales) or [Sales] - [Cost]. 2) The MicroStrategy object that contains the metric definition. It represents a business measure or key performance indicator. See also fact. MOLAP Multidimensional online analytical processing. multidimensional Copy of the report data saved in memory. This cache is used cache for manipulation of the view definition. Also called an Intelligent Cube. multidimensional A query language similar to SQL. MDX is defined by expression Microsoft. An MDX expression returns a multidimensional result set (dataset) that consists of axis data and cell data. MDX is used to query cubes, which are used by SAP BI to store data. When accessing the data from SAP BI to generate reports, the MicroStrategy Intelligence Server generates MDX. See also SAP BI. nonaggregatable A metric that is not additive along all dimensions. For metric example, “Stock On Hand at End of Week” is not additive across time: the stock on hand at the end of the week is not the sum of the stock on hand at end of each day in the week. one-to-many An attribute relationship in which every element of a parent attribute can relate to multiple elements of a child attribute, while every element of the child attribute relates to only one element of the parent. The one-to-many attribute relationship is the most common in data models.
974 Glossary: metric
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Glossary
See also: •
one-to-one
•
many-to-many
•
many-to-one
•
relationship
one-to-one An attribute relationship in which every element of the parent attribute relates to exactly one element of the child attribute, and vice versa. See also: •
one-to-many
•
many-to-one
•
many-to-many
•
relationship
page-by Segmenting data in a grid report by placing available attributes, consolidations, and metrics on a third axis called the Page axis. Since a grid is two-dimensional, only a slice of the cube can be seen at any one time. The slice is characterized by the choice of elements on the Page axis. By varying the selection of elements, the user can page through the cube. See also: •
drill
•
pivot
•
sort
•
subtotal
parent attribute The higher-level attribute in an attribute relationship with one or more children.
© 2015 MicroStrategy, Inc.
Glossary: one-to-one
975
Glossary
Advanced Reporting Guide
See also: •
child attribute
•
relationship
partial relationship An attribute relationship in which elements of one attribute relate to elements of a second attribute, while the opposite is not necessarily true. See also: •
relationship
•
one-to-many
•
many-to-one
•
many-to-many
partition A relational database table broken down into smaller component tables. This can be done at the database level or at the application level. See also: •
application-level partition
•
database-level partition
partition base table A warehouse table that contains one part of a larger set of data. Partition tables are usually divided along logical lines, such as time or geography. Also referred to as a PBT. See also partition mapping. partition mapping The division of large logical tables into smaller physical tables based on a definable data level, such as month or department. Partitions minimize the number of tables and records within a table that must be read to satisfy queries issued against the warehouse. By distributing usage across multiple tables, partitions improve the speed and efficiency of database queries.
976 Glossary: partial relationship
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Glossary
partition mapping table A warehouse table that contains information used to identify the partitioned base tables as part of a logical whole. Also referred to as a PMT. See also: •
partition base table
•
partition mapping
pass-through An expression that provides access to special functions or expression syntactic constructs that are not standard in MicroStrategy, but are offered by various relational database management system (RDBMS) platforms. See also Apply function. physical warehouse A detailed graphic representation of your business data as it schema is stored in the data warehouse. It organizes the logical data model in a method that make sense from a database perspective. See also schema. pivot To reconfigure data on a grid report by placing report objects (attributes, metrics, consolidations) on different axes. Also, to reconfigure a grid report by interchanging row and column headers, and hence the associated data. Subset of cross-tab. See also: •
drill
•
page-by
•
sort
•
subtotal
PMML An XML standard used to represent data mining models that thoroughly describes how to apply a predictive model. It was developed by the Data Mining Group, DMG, an independent consortium consisting of over two dozen companies including MicroStrategy. © 2015 MicroStrategy, Inc.
Glossary: partition mapping table
977
Glossary
Advanced Reporting Guide
predictive input metric A metric used in data mining that encapsulates the definition of another attribute or metric. There are three types of predictive input metrics: attribute-based input metrics, metric-based input metrics, and conditional input metrics. pre-aggregation Aggregation, or the calculation of numeric data at a specific attribute level, that is completed before reports are run, with the results stored in an aggregate table. See also: •
aggregate table
•
aggregation
prompt 1) MicroStrategy object in the report definition that is incomplete by design. The user is asked during the resolution phase of report execution to provide an answer that completes the information. A typical example with a filter is choosing a specific attribute on which to qualify. 2) In general, a window requesting user input, as in “type login ID and password at the prompt.” qualification The actual condition that must be met for data to be included on a report. Examples include “Region = Northeast” or “Revenue > $1 million”. Qualifications are used in filters and custom groups. You can create multiple qualifications for a single filter or custom group, and then set how to combine the qualifications using the logical operators AND, AND NOT, OR, and OR NOT. quality relationship The relationship between a parent attribute and two or more “joint child” attributes. The parent attribute is referred to as a “quality” because its definition is complete only with the intersection of its joint children. regression A data mining technique that analyzes the relationship between several predictive inputs, or independent variables, and a dependent variable that is to be predicted.
978 Glossary: predictive input metric
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Glossary
relationship An association specifying the nature of the connection between one attribute (the parent) and one or more other attributes (the children). For example, City is a child attribute of State. See also: •
parent attribute
•
child attribute
•
partial relationship
•
quality relationship
•
one-to-one
•
one-to-many
•
many-to-one
•
many-to-many
report The central focus of any decision support investigation, a report allows users to query for data, analyze that data, and then present it in a visually pleasing manner. See also: •
filter
•
template
report creation The process of building reports from existing, predesigned reports in MicroStrategy Developer or in MicroStrategy Web. report design The process of building reports from basic report components using the Report Editor in MicroStrategy Developer or MicroStrategy Web. SAP BI SAP Business Intelligence is the business intelligence tool developed by SAP. MicroStrategy’s integration with SAP BI allows users to conduct reporting and analysis with the data from SAP BI. For information on SAP BI, please refer to documentation by SAP; for information on MicroStrategy’s © 2015 MicroStrategy, Inc.
Glossary: relationship
979
Glossary
Advanced Reporting Guide
integration with SAP BI, refer to the MDX Cube Reporting Guide. See also multidimensional expression. schema 1) The set of tables in a data warehouse associated with a logical data model. The attribute and fact columns in those tables are considered part of the schema itself. 2) The layout or structure of a database system. In relational databases, the schema defines the tables, the fields in each table, and the relationships between fields and tables. schema object MicroStrategy object created, usually by a project designer, that relates the information in the logical data model and physical warehouse schema to the MicroStrategy environment. These objects are developed in MicroStrategy Architect, which can be accessed from MicroStrategy Developer. Schema objects directly reflect the warehouse structure and include attributes, facts, functions, hierarchies, operators, partition mappings, tables, and transformations. scorecard A popular means of displaying and distributing data from business intelligence projects. Scorecards typically follow a specific methodology and are focused on key metrics within a business area. series In a graph, it generally corresponds to the rows of a grid report. Series are represented as legend items in a graph. shadow metric A metric that represents the attribute to be included in a predictive model. It allows an attribute to be used as a predictor. shortcut metric A metric based on metrics already included in a report. They provide a quick way to add new metrics to that report. Shortcut metrics belong to one of these types: percent-to-total metrics, transformation metrics, rank metrics, and running sum metrics.
980 Glossary: schema
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Glossary
simple metric A type of metric defined by a formula containing a mathematical expression based on at least one group function, such as sum or average, which is applied to facts, attributes, or other metrics. It can also contain non-group functions or arithmetic operators, in addition to the required group function. Examples are Avg(Sum((Cost_Fact + Profit_Fact))) and Avg((Cost_Metric + Profit_Metric))). smart metric A property of a compound metric that allows you to change the default evaluation order. Smart metrics calculate subtotals on the individual elements of the compound metric. For example, a smart metric uses the formula Sum(Metric1)/ Sum(Metric2) rather than Sum(Metric1/Metric2) when calculating subtotals on a report. sort Arranging data according to some characteristic of the data itself (alphabetical descending, numeric ascending, and so forth). See also: •
drill
•
page-by
•
pivot
•
subtotal
source system Any system or file that captures or holds data of interest. subtotal A totaling operation performed for a portion of a result set.
© 2015 MicroStrategy, Inc.
Glossary: simple metric
981
Glossary
Advanced Reporting Guide
See also: •
drill
•
page-by
•
pivot
•
sort
system hierarchy The superset hierarchy containing all attributes in a project. Unlike a browse hierarchy, it is not explicitly created but is automatically deduced by the MicroStrategy platform from all information available to it. Compare user hierarchy. table size The estimated size of a database table in terms of number of rows. table alias One type of logical table. A table alias is created outside of the Warehouse Catalog and points directly to a physical table. A table alias can have a different name from the physical table. One physical table can have more than one table alias. See also logical table. template A MicroStrategy object that serves as a base on which you can build other objects of the same type. You can create a template for almost any kind of MicroStrategy object, such as filters or reports.
982 Glossary: system hierarchy
•
Object templates allow you to start with a predefined structure when creating a new object. You can use object templates for many MicroStrategy objects, including metrics, documents, reports, and report templates.
•
Report templates define the layout of general categories of information in a report. In a report template, you specify the information that you want to retrieve from your data source, and the way that you want the data to be displayed in Grid view. A report template does not include filter information. Report templates are often referred to as just as templates. © 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Glossary
threshold Used to create conditional formatting for metric values. For example, if revenue is greater than $200, format that cell to have a blue background with bold type. transformation A schema object that maps a specified time period to another time period, applying an offset value, such as current month minus one month. Although the vast majority are based on time, a transformation can also map different objects, such as defunct product codes to new ones. Time transformations are used in metrics to compare values at different times, such as this year versus last year or current date versus month-to-date. transformation metric An otherwise simple metric that takes the properties of the transformation applied to it. For example, a metric calculates total sales. Add a transformation for last year and the metric now calculates last year’s total sales. user hierarchy Named sets of attributes and their relationships, arranged in specific sequences for a logical business organization. They are user-defined and do not need to follow the logical model. Compare system hierarchy. view definition Report execution steps which represent how the data is viewed and manipulated in the Intelligence Server. The view definition determines how the final report data set generated in the data definition steps is manipulated.
© 2015 MicroStrategy, Inc.
Glossary: threshold
983
Glossary
Advanced Reporting Guide
984 Glossary: view definition
© 2015 MicroStrategy, Inc.
INDEX Numerics 3D Surface graph 424 Auto Arrange 496 best practices 425 example 424
A access control list Freeform SQL report and 638 Query Builder report and 733 across level subtotals 289 add-in function. See custom function. Advanced Metric Assistant, creating multiple metrics with 97 advanced qualification 128 activating 128 custom expression 154, 156 joint element list 172 advanced subtotals across a level 289 by position 289 group by 290 aggregation, grouping in a level metric 21 Alerter. See threshold.
© 2015 MicroStrategy, Inc.
alias for a metric column 122 alignment formatting default 896 analysis, time-series 90 Analyst privileges 355 Analyst user 353 Analytical Engine null checking 865 Apply function 164 ApplyAgg 165 ApplyComparison 165 ApplyLogical 165 ApplyOLAP 165 ApplySimple 165 custom expression 164 example 167, 168, 169 handling by the SQL Engine 119 syntax 166 ApplyAgg 165 ApplyComparison 165 applying series effect to a graph report 378 ApplyLogical 165 ApplyOLAP 165 ApplySimple 165 Area graph 390
985
Index
best practices 391 Combination graph 415 curved effect 391 example 390 association drill map 238 level 239 removing 240 association rules analysis 799 example 851 attribute defined on 3 element 3 grouping multiple in a joint element list 172 on a report 336 virtual (consolidation) 207 attribute element 3 on a report 336 attribute element list qualification 127 attribute element prompt link and 574, 575 attribute form qualification 127 attribute form selection option for intermediate passes 877 attribute join type 335, 336 changing 339 object prompt and 341 attribute level of a set qualification 146 attribute qualification 127 attribute element list qualification 127 attribute form qualification 127 Date and Time Editor 136 date offset 135 dynamic date 135 exported filter element 145 imported filter element 143 prompted 175 attribute qualification prompt 245
986
Advanced Reporting Guide
in a filter 175 attribute selection option for intermediate passes 877 attribute-to-attribute qualification filter 130 creating 130, 160 custom expression in 160 example 131, 133, 160 Auto Arrange in a graph 496 automatic layout in a graph 492 autostyle 328 default format 895 deploying 329 average moving 114 running 115 axis formatting graph 488 report 324
B banding defined on 188 count 188 formatting 324 formatting default 898 metric value 189 output level 188 point 189 qualification 188 size 188 type 188 Bar graph 392 best practices 399 Combination graph 415 example 393 bevel effect on a graph object 378 border of a report 325 default formatting 897 © 2015 MicroStrategy, Inc.
Advanced Reporting Guide
box and whisker plot. See Boxplot. Boxplot 435 best practices 438 example 436 break by in a metric qualification 149 browsing project order in Developer and Web 959 standards in MicroStrategy 957 Bubble chart 413 best practices 415 example 414 Budgeting graph Lipstick Bar graph 395 Overlapping Bar graph 395 bulk exporting 372 by position subtotals 289
C cache 359, defined on 359 default setting 359 disabling 359 enabling 359 Intelligent Cube 288 invalidating 360 Cartesian join 868 warning 868 categories defined on 386 in a graph 386 specifying number of 388 ChangeView command 905 character sort order 958 choose from all attributes in a hierarchy prompt 244 choose from attribute element list prompt 245 circle graph. See Pie chart. cluster analysis 796
© 2015 MicroStrategy, Inc.
Index
example 842 color palette for a graph 444 color style for a graph 444 column alias for a metric 122 column subtotal formatting 324 Combination graph 415 comparison operator 891 compound metric defined on 11, 93 count function 113 moving average 114 moving sum 115 non-group function 111 n-tile 116 rank function 111 running average 115 running sum 115 smart metric 96 smart subtotals 96 condition. See filter. conditional metric 61 advanced options 68, 87 default 85 combining embedding method and remove related elements settings 75 creating 67 Advanced Metric Assistant 97 embedding method 68 level and condition 63 link and 575, 580 multiple conditions 63 remember option setting 85 removing related report filter elements 73 report filter interaction 68 conditional predictive input metric 788 creating 789 confusion matrix 795 consolidation defined on 206 987
Index
custom group comparison 221 element. See consolidation element. evaluation order 212, 349 evaluation order error example 349 formatting 322 importing an element 212 row level math 208 SQL generation and 213 subtotal 214 virtual attribute 207 consolidation element defined on 209 formatting 208, 325 from attributes in different hierarchies 211 from different levels of the same hierarchy 211 from the same attribute 210 importing 212 using existing 212 contribution metric 13 conversion rate, prompting for 250 converting currency 250 count distinct 113 count function 113 count metric 113 currency conversion 250 current date offset 135 curved effect applying to a graph object 378 applying to a Line graph 402 applying to an Area graph 391 custom expression 154 Apply function in 164 attribute-to-attribute qualification filter 160 qualification filter 156 example 158 string manipulation in 158 relationship qualification filter 161, 988
Advanced Reporting Guide
162 string manipulation in 157 using a date 158 custom format 310 character/text data 313 color 315 conditional 316 currency 315 date 313 example 316 numeric data 311 time 313 custom function 118 R Integration Pack 119 Custom graph 416 custom group defined on 182 advantages 187 banding qualification 188 consolidation comparison 221 creating 193 creating multiple simultaneously 193 Custom Group interaction with report filter property 200 element 183 element header 183 element header position 195 example 185 formatting 322, 325 hierarchical display 191 Keep Group Structure option 198 prompt 201 report filter interaction with 200 sorting 197 by item metric value 198 SQL generation and 187 store inventory example 185 structure on report 191
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
subtotals 196 position 196 totals 196 position 196 custom prompt style for MicroStrategy Web 261 custom report subtotals 294 customizing a drill map 228
D data access using a security filter Freeform SQL 639 Query Builder 734 data definition defined on 286 evaluation order 344 object 287 data mart defined on 749 creating 750 governing settings 764 metric alias 122 objective of 749 optimizing 760 performance parameters 764 report defined on 750 running a report against 762 SQL statement 765 table defined on 750 table creation properties 765 used as a source table 755 VLDB properties 765 data mining defined on 769 association rules analysis 799 example 851 cluster analysis 796 example 842 confusion matrix 795 dataset 778
© 2015 MicroStrategy, Inc.
Index
decision tree analysis 797 example 845 example 837, 840 exponential regression 817 integrating with third-party application 829 linear regression 817 example 837 logistic regression 795 example 840 market basket analysis 799 example 851 multivariate regression model 794 PMML 774 regression 791 seasonal regression 837 standard regression model 822 time series analysis 804 training metric 815 Training Metric Wizard 816 training the model 815 tree regression model 821 univariate regression model 794 workflow 775 database connecting Freeform SQL to 738 connecting Query Builder to 738 database instance VLDB property 884 web service 645 database scoring 770 database-specific function 164 dataset for data mining 778 guidelines for creating 780 non-MicroStrategy 790 date dynamic 135 in a custom expression 158 in an Apply function 169 989
Index
Date and Time Editor 136 date offset 135 decision tree analysis 797 example 845 default drill path 231 evaluation order 343 inherited value 862 link 571 deployment Object Templates folder 358 Public Objects folder 358 report 352, 358 Shared Reports folder 358 Design View 274 designing a report for deployment 356 detail. See fact. Developer privileges 355 Developer user 353 dimensional subtotals 103 dimensionality. See level. Distribution Services bulk exporting reports 372 division by zero 865 document creating a report for 913 Freeform SQL report in 616 moving sum example 115 n-tile function example 116 Query Builder report in 704 rank example 112 running sum example 115 widget in 515 drill map 225, 226 See also drilling.
990
Advanced Reporting Guide
association 238 level 239 removing 240 template 240 creating 228 custom 228 default 226, 238 deleting 226 destination 229 drill path type 229 drill path. See also drill path. 226 grid unit level 239 project 240 project default 226 project level 239 report level 239 template 240 template level 239 drill path 226 base template 236 creating 228 custom 228 default 227, 231 destination 229 display mode 237 display name 231 drilled-on object 236 filter interaction 232 keep parent 236 page-by 236 priority 231 properties 230 set name 231 system hierarchy 227 threshold 237 type 229 VLDB properties 238 drilling defined on 226
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
drill map. See also drill map. 226 drill path. See also drill path. 226 filter interaction 227, 232 Freeform SQL report and 633 Query Builder report and 731 shortcut to an existing drill map 230 to a template 230 dropping temp table method 880 dynamic date 135 Date and Time Editor 136 prompt and 139 dynamic graph title 463
E Editor command 906 element of a consolidation. See also consolidation element. 209 embedded filter 128, 177, 357 See also shortcut-to-a-filter qualification. template 357 embedding method 68 changing default 85 combining with remove related elements setting 75 example 70, 85 empty object template 367 showing 371 user-defined 367 equi-join 870 evaluation order consolidation 212 example 349 data set 344 example 345, 349 default 343 defining 344
© 2015 MicroStrategy, Inc.
Index
derived metric 345 incompatibility error example 345, 349 report 342 view 344 example 345, 349 example attribute-to-attribute qualification filter 131, 133 currency conversion 250 custom expression qualification filter 158 custom format 316 data mining. See example, data mining. document Customer Explorer dashboard 915 Operation Performance dashboard 934 dynamic date 136 evaluation order data set 345, 349 evaluation order error 345, 349 First function 104 graph. See example, graph. level prompt 252 link. See example, link. metric qualification 280 metric. See example, metric. nonaggregatable metrics and level grouping 27 relationship qualification filter 163 Report Details pane 518 report details. See example, report details. running sum 115 SQL aggregation 3 subtotals across level 292 subtotals by position 290 subtotals group by 293 subtotals, user-defined 104
991
Index
user-defined subtotals 104, 106 value prompt 250 view evaluation order 345, 349 year-to-date transformation 91 example, data mining 837, 840 cluster analysis 842 decision tree analysis 845 linear regression 837 logistic regression 840 seasonal regression 837 example, graph 3D Surface graph 424 Area graph 390 Bar graph 393 Boxplot 436 Bubble chart 414 Funnel graph 429 Gauge graph 426 graph style 389 Histogram 423 Line graph 401 Pareto chart 432 Pie chart 403 Polar chart 409 Radar chart 412 Scatter plot 406 Stock graph 418 example, link answering a hierarchy prompt 583 answering a target prompt with source prompt answers 577 conditional metric and 580 ignoring a target prompt 578 linking reports 566 multiple links 571 prompt answer for any other prompts not listed 587 example, metric
992
Advanced Reporting Guide
compound 94 conditional metric with multiple qualifications 63 embedding method 70, 85 level 35 level grouping with a nonaggregatable metric 27 rank 112 removing the report level 33 reusing metrics with the Filter setting 54 transformation 90 example, report details 524 filter details 551, 553 alias display 557 content of 546 drill filter 548 filter type 545 logical operator display 554 prompt details 538 report details delimiters 523 report details preferences 561 template details 530, 532 Excel connecting Freeform SQL to 739 connecting Query Builder to 739 Execute command 907 ExecuteDocument command 908 ExecuteReport command 908 exponential regression 817 exporting 372 filter element 145 graph 512
F fact defined on 2 fallback table type 881 filter defined on 126, 275
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
qualification types. See filter qualification types. adding to a report 177 attribute level 146 comparison operator 891 conditional metric and 68 creating. See filter, creating. custom expression qualification in 156 date offset 135 drilling and 227 dynamic date 135, 139 embedded 177, 357 exporting a filter element 145 Freeform SQL report and 629 importing a filter element 143 linked 177 local 177 logical operator 127, 888 metric 61 metric and report interaction 68, 73, 85 operator 887 output level 146 pattern operator 893 prompted. See also filter, prompted. 174 qualification types. See filter qualification types. Query Builder report and 723 rank and percent operator 892 report and metric interaction 68, 73, 85 report as filter 283 security 639, 734 shortcut to 177, 357, 361 stand-alone 177 filter details 543 alias display 557 attribute element list qualification © 2015 MicroStrategy, Inc.
Index
display 550 example 551 attribute form and set qualification display 553 configuring 558 content of 544, 546 example 546 drill filter 548 example 548 filter type 544, 545 example 545 logical operator 554 report details preferences interaction 544 filter object prompt 176 filter qualification types 127 advanced qualification 129 attribute qualification 127 attribute-to-attribute qualification 160 joint element list qualification 172 relationship qualification 161 set qualification 127 shortcut-to-a-filter qualification 128 shortcut-to-a-report qualification 128 filter, creating attribute qualification using a dynamic date 137 attribute-to-attribute qualification 130, 160 custom expression qualification 156 dynamic date based on a prompt answer 141 joint element list 173 filter, prompted 174 attribute qualification prompt 175 filter definition prompt 244 metric qualification prompt 175 object prompt 176
993
Index
report object prompt 176 find and replace 329 privilege 330 First function 104, 118 Flash Mode graph visualization 515 font anti-aliasing applied to graph text 379 formatting default 897 Format Cells dialog box 310 formatting autostyle 328 custom 310 default project 367 default value 895 graph. See also graph formatting. 442 grid unit 318 layer. See also formatting layer. 317 report 309 threshold 320 using empty object template 367 zone defined on 318 formatting layer defined on 317 all metrics 324 axis 324 banding 324 column subtotals 324 consolidation 325 custom group 325 grid unit 324 metric 323 metrics label 324 order of layers 322 report border 325 report metric 324 row subtotals 325 threshold 325 formula join type for a compound metric 97
994
Advanced Reporting Guide
Freeform SQL functionality 610 Freeform SQL report 610 access control list 638 common table expression 614 creating. See Freeform SQL report creation. derived table 614 document 616 drilling 633 filter 629 managed object 741 multi-pass SQL 613 MultiSource Option 634 prompt 630 query syntax 612 security filter 639 security for data access 638 shortcut-to-a-report qualification 634 SQL support 613 standard report versus 615 Freeform SQL report creation from a database 738 from a text file 740 from a web service 644 from an Excel file 739 using a stored procedure 622 Freeform SQL used to create an Intelligent Cube 746 full outer join support 870 function add-in. See custom function. Apply 164 ApplyAgg 165 ApplyComparison 165 ApplyLogical 165 ApplyOLAP 165 ApplySimple 165 custom 118
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
database-specific 164 First 118 Last 118 OLAP 115 Pass-through 164 plug-in 118 Funnel graph 429 best practices 431 example 429
G Gantt chart 438 Gauge graph 426 best practices 428 displaying multiple gauges 428 example 426 gradient, applying to a graph object 378 grand totals default 101 displaying 101, 291 function 101 total subtotal function 101 graph 375 adding data to 380 Auto Arrange 496 best practices 376 categories 386 categories and series 388 copying into a third-party application 513 dynamic title 463 exporting 512 footnotes 462 creating 462 formatting. See graph formatting. insufficient data for 385 label. See graph label. layout. See graph layout. © 2015 MicroStrategy, Inc.
Index
legend. See graph legend. moving an object on 386 object placement in 384 resizing 496 scaling 492, 497 selector and 378 series 386 sizing 492 thresholds 454 example 457 title 462 creating 462 variables for a title 463 viewing 379 graph formatting axis label 488 background picture 461 bevel effect 461 color 444 effects 444 footnotes 462 gradient 460 gradient (using MicroStrategy Web) 452 graph object manual format 459 graph series (using MicroStrategy Web) 450 label 483 layout 492 legend 484 metric color 445 inheriting 449 numeric values 476 inheriting 477 pattern 461 positioning a chart element 492 shading 444 texture 461
995
Index
thresholds 454 example 457 title 462, 464 tooltip 489 transparency 460 graph label displaying 483 formatting 483 graph layout 492 Auto Arrange 496 automatic 492 automatic vs. manual 493 example 495 manual 494 selecting 493 graph legend displaying (using MicroStrategy Developer) 484 displaying (using MicroStrategy Web) 486 formatting (using MicroStrategy Developer) 486 formatting (using MicroStrategy Web) 486 manually positioning 495 graph style 3D Surface 424 advanced styles 417 applying custom 507 Area 390 Bar graph 392 Boxplot 435 Bubble chart 413 Budgeting graph 395 Combination 415 creating custom 507 Custom 416 example and usage scenario 389 Funnel 429 996
Advanced Reporting Guide
Gantt chart 438 Gauge graph 426 Histogram 422 insufficient data for 385 Line graph 400 Lipstick Bar graph 395 minimum object requirements for 382 object placement in 384 Overlapping Bar graph 395 Pareto chart 432 Pie chart 403 Polar chart 408 Radar chart 410 report object placement for 384 Scatter plot 406 selecting 384 Stock graph 417 Graph view 379 Grid Graph view 376, 380 grid unit defined on 318 formatting 318, 324 subtotal formatting 319 Grid/Graph, link in 569 group by ID attribute 879 non-ID attribute 880 subtotals 290 grouping elements in a joint element list 172 growth percentage 90
H hierarchical sorting 298 hierarchy as the target of a level metric 45 hierarchy prompt and linking 576, 583 Histogram 422 best practices 423 example 423 © 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Index
homepage in MicroStrategy Developer 902 HTML document 970 HTTP authentication for an XQuery statement 650
report 120 type 97 joint element list qualification 172
I
key performance indicator. See fact.
importing filter element 143 table 711 importing data 6 inner join 120 integer constant in a metric 863 Intelligent Cube defined on 288 cache 359 Freeform SQL 746 personal 288 Query Builder 746 report cache 359 shared 288 intermediate table type 881 international support xxxiv internationalization, converting currency for 250 inventory metric 21
J join attribute 335 Cartesian 868 equi-join 870 formula 97 inner 120 metric 120 outer 120 outer join support VLDB property 870 Query Builder report and 712
© 2015 MicroStrategy, Inc.
K L label on a graph. See also graph label. 482 Last function 118 layout of a graph. See also graph layout. 492 legend on a graph. See also graph legend. 484 level defined on 12 grouping 21 grouping for a nonaggregatable metric 21 prompt 251 properties 15 displaying 15 report level 32 removing 33 target 20 level metric 12 advanced options 51, 57, 87 aggregation in 21 allowing other users to add extra units to the definition 57 applying the metric filter to the metric calculation 51 attribute level 20 elements of 14 filter setting 51 grouping 21 hierarchy as the target 45 properties 15 property symbols defined 15 997
Index
removing the report level 33 report level 12, 32 security filter 39 target 20 level prompt creating 254 example 252 object prompt vs. 252 Line graph 400 applying a curved effect 402 best practices 402 Combination graph 415 example 401 linear regression 817 example 837 link 565, defined on 569 components 570 conditional metric and 575 copying 593 creating 589 prerequisites 589 default 571 in MicroStrategy Developer 571 in MicroStrategy Web 572 example 566 MDX cube report as the source 582 multiple 569 default link 571 example 571 prerequisites 568 prompt and 570 prompt answer method. See also prompt answer method for a link. 573 source 569 target 570 linked filter. See shortcut to a filter. linked template. See shortcut to a template.
998
Advanced Reporting Guide
linking reports and documents 565 See also link. comparison of 570 example 566 prerequisites 568 Lipstick Bar graph 395 local filter 177 logical operator exclusion 890 filter 127 functional description 888 intersection 889 union 889 logistic regression 795 confusion matrix 795 example 840
M managed object defined on 741 editing 745 market basket analysis data mining 799 example 851 maximum SQL size 884 MDX cube report, linking from 582 MDX cube, Intelligent Cube based on 746 merging header cells 898 metric 4, defined on 10 column alias 122 compound 11, 93 conditional. See also conditional metric. 61 contribution 13 count 113 creating multiple simultaneously 97 creating, using Advanced Metric Assistant 97 default subtotal function 101 © 2015 MicroStrategy, Inc.
Advanced Reporting Guide
dimensionality. See level. division by zero 865 formula 10 formula join type 97 function plug-in 118 integer constant in 863 inventory 21 join 120 join default 121 join type 97, 121 level. See also level metric. 12 nested 58 nonaggregatable 21 null checking 864 predictive 776 predictive input 782 prompted 61 simple 10 smart 96 sorting hierarchically 298 subtotal dimensionality 865 subtotals 100, 289 total subtotal function 101 transformation. See also transformation metric. 88 type 10 VLDB properties 122, 863 zero checking 865 Metric Editor condition 65 level 15 metric join type 120 Subtotals/Aggregation tab 101 transformation 92 metric filter conditional metric and 68 report filter and 68 metric formatting
© 2015 MicroStrategy, Inc.
Index
defining a graph color 445 metric headers 324 metric level 323 metric values 324 metrics label 324 Metric Formula Join Type dialog box 97 metric level. See level metric. metric qualification 127, 280 advanced options 151 break by property 149 example 280 in relationship qualification filter 163 merging attribute qualifications 151 metric-to-metric comparison 150 output level 148 prompt 245 prompted 175 resolving in a report 151 metric set qualification. See also metric qualification. 127 metric-based predictive input metric 786 metric-to-metric comparison 150 MicroStrategy Developer command 901 setting the homepage 902 MicroStrategy Developer anchor 902 MicroStrategy Developer command 901 ChangeView 905 Editor 906 Execute 907 ExecuteDocument 908 ExecuteReport 908 Open 909 Reset 910 Shortcut 911 syntax 902 viewing 904 MicroStrategy Transaction Services 681
999
Index
report 682 MicroStrategy Web browsing and sorting order for projects 959 configuring graph image format for 511 prompt display in 258 moving average 114 sum 115 multiple links 569 multiple-key sorting 298 MultiSource Option Freeform SQL report 634 Query Builder report 732 multivariate regression model 794
N nested aggregation metric 58 nested metric 58 nonaggregatable metric defined on 21 grouping 21 non-group function 111 moving average 114 moving sum 115 n-tile 116 rank 111 running average 115 running sum 115 n-tile function 116 example 116 null checking 864 Analytical Engine 865 null value sorting 306 number formatting default 896
1000
Advanced Reporting Guide
O object name delimiter for report details 522 object prompt 246 attribute join type and 341 level prompt vs. 252 object re-use 359 object template 366, 369 default 370 empty 367 Object Templates folder 358 project level settings 370 showing empty 371 user-defined empty 367 viewing folder 358 object. See attribute. OLAP function 115 Open command 909 operator comparison 891 filter 887 logical 888 pattern 893 rank and percent 892 optimizing a data mart 760 Oracle SQL hint 880 outer join 120 output level banding 188 filter 146 metric qualification 148 relating to the filter qualification 148 relationship qualification 147 set qualification 146 Overlapping Bar graph 395
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
P Pareto chart 432 best practices 435 example 432 Pass-through expression. See Apply function. Pass-through function. See Apply function. pattern formatting default 897 pattern operator 893 performance indicator. See fact. personal Intelligent Cube defined on 288 Pie chart 403 best practices 405 example 403 Plug-In Wizard 118 PMML defined on 774 Polar chart 408 best practices 410 example 409 predesigned report 356 accessing 358 predictive input metric 782 conditional 788 predictive metric aggregating 834 attribute as input 782 conditional metric as input 788 in a report 835 level metric as input 786 performance 776 predictive model creating 791 importing 829 workflow 814 Predictive Model Markup Language. See PMML. Predictive Model Viewer 836 prerequisites for Advanced Reporting © 2015 MicroStrategy, Inc.
Index
Guide xxiii preserving all final pass result elements 870 preserving all lookup table elements 871 priority of a drill path 231 privilege 354 Analyst 353, 355 Developer 353, 355 Web Analyst 353, 355 Web Professional 353, 355 Web Reporter 353, 355 project progression 352 project sorting and browsing order 959 project time line. See Gantt chart. prompt 4, defined on 241 attribute qualification 245 choose from all attributes in a hierarchy 244 choose from attribute element list 245 custom group and 201 custom prompt style for MicroStrategy Web 261 display style for MicroStrategy Web 259 dynamic date filter and 139 expression properties for MicroStrategy Web 265 filter 174 filter definition 244 Freeform SQL report and 630 function and 243 level 251 link and. See prompt answer method. metric qualification 245 MicroStrategy Web display 258 object 246 Query Builder report and 726 report 356 scheduled report and 243 1001
Index
search object 246 style properties for MicroStrategy Web 262 system prompt 258 type 243 user login system prompt 258 value 249 prompt answer method for a link 570, 573 All valid units 576, 583 example 583 Answer with the same prompt 574, 576 example 577 Any other prompts 570, 587 example 587 by prompt type attribute element prompt 574, 575 hierarchy prompt 576, 583 value prompt 574 Current unit 576, 583 example 583 Default answer 574 Dynamically 574 example 566 Empty answer 575, 577 example 578 Prompt user 574 selecting 591 Static element list 575, 580 example 580 MDX cube data and 582 prompt details 537 configuring 541 example 538 report details preferences interaction 540 prompted custom group 201
1002
Advanced Reporting Guide
filter 174, 175 attribute qualification 175 shortcut-to-a-filter qualification 176 shortcut-to-a-report qualification 176 metric 61 report 356 Public Objects folder 358
Q qualification advanced 129 attribute 127 attribute prompt 245 banding 188 filter 127 logical operator 127 metric 127 prompted attribute 175 prompted metric 175 prompted shortcut-to-a-filter 176 prompted shortcut-to-a-report 176 relationship 127 set 127 shortcut-to-a-filter 128 shortcut-to-a-report 128 type 127 qualify on a metric prompt 245 Query Builder 610 creating an Intelligent Cube 746 Query Builder report access control list 733 attribute form 722 column 718
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
creating 706 from a database 738 from a text file 710, 740 from an Excel file 709, 739 document 704 drilling 731 filter 723 join 712 managed object 741 MultiSource Option 732 prompt 726 security filter 734 security for data access 733 selecting a table 711 shortcut-to-a-report qualification 732 standard report versus 703
R R (analytics) integrating with MicroStrategy 119 R Integration Pack 119 Radar chart 410 best practices 413 example 412 rank and percent operator 892 rank function 111 RDBMS function 164 regression defined on 791 relating the output level and filter qualification 148 relation in a relationship qualification filter 163 relationship qualification 127, 162 advanced options 153 applying a qualification independently 153 example 163
© 2015 MicroStrategy, Inc.
Index
output level 147 filter relation and 148 relationship table 164 relationship qualification filter. See relationship qualification. relationship set qualification. See relationship qualification. remember option setting (conditional metric) 85 removing related report filter elements in a metric 73 default 85 embedding method and 75 replace. See find and replace. report 4, defined on 271 cache 359 data definition 286 deploying 352, 358 Design View 274 embedded filter 357 embedded template 357 evaluation order 342 default 343 defining 344 execution flow 286 exporting 372 filter 275 formatting. See also report formatting. 309 Intelligent Cube 288 join 120 MicroStrategy Transaction Services 681 object re-use 359 Object Templates folder 358 predesigned 356 privilege 354 prompted 356 Public Objects folder 358
1003
Index
reducing SQL passes 282 report limit 276 Report Objects 357 Shared Reports folder 358 shortcut to 283 sorting 298 advanced 298 hierarchical 298 multiple-key 298 SQL optimization 282 static 356 subtotals. See also subtotals. 101 template 361 Template Dependency Validator 364 Transaction Services 682 view definition 286 VLDB properties 867 web service data 665 report as filter. See shortcut-to-a-report qualification. report border formatting 325 report cache invalidated with find and replace 330 report creation defined on 272 Report Data Options attribute join type 335 evaluation order 213, 344 metric join type 120 report limit 276 report description in the report details 524 report design defined on 272 for use on a document 913 multiple simultaneously 272 report details 517 configuring 527 disabling 519 displaying 519 enabling 519 1004
Advanced Reporting Guide
example 524 filter details 543 hiding 519 level of configuration 519 document 520 order of precedence 520 project level 520 report level 520 text field 519 object name delimiter 522 prompt details 537 report description 524 report details preferences and 520, 526 template details 528 types 521 Report Details pane 517 example 518 report details preferences 560 customizing 562 disabling 521 enabling 562 example 561 filter details interaction 544 prompt details interaction 540 report details interaction 520, 526 template details interaction 534 report filter conditional metric and 68 custom group and 200 drilling and 227 removing related elements of 73 report limit and 276, 278 report formatting autostyle 328 autostyle default 895 axis layer 324 banding layer 324 column subtotal layer 324 © 2015 MicroStrategy, Inc.
Advanced Reporting Guide
consolidation layer 325 custom 310 custom group layer 325 default values 895 formatting layers 317 order of 322 grid unit 318 grid unit layer 324 metric header layer 324 metric layer 323, 324 metric value layer 324 metrics label layer 324 report border layer 325 report metric layer 324 row subtotal layer 325 subtotal 319 threshold 320 threshold layer 325 zone 318 report level 12, 32 removing 33 report limit report filter and 278 report filter versus 276 report object prompt filter 176 Report Objects 357 report post statement 874 report pre statement 874 report qualification. See shortcut-to-a-report qualification. Reset command 910 resizing a graph 496 REST architecture 644 re-using an object 359 row subtotal formatting layer 325 running average 115 sum 115
© 2015 MicroStrategy, Inc.
Index
S saving a report with a shortcut to a stand-alone filter 179 scaling a graph 492, 497 Scatter graph. See Scatter plot. Scatter plot 406 best practices 407 example 406 scheduled report containing a prompt 243 scheduling bulk exports 372 scope of analysis. See Intelligent Cube. search object 246 searching for dependents 359 seasonal regression example 837 security Freeform SQL report 638 Query Builder report 733 XQuery statement 650 security filter defined on 639 creating 641 Freeform SQL and 639 in a level metric 39 Query Builder report and 734 segmentation 116 selector, controlling a graph with 378 series defined on 386 graph 386 specifying the number of 388 set qualification 127 attribute level 146 metric qualification 127 output level 146 relationship qualification 127 Shared Reports folder 358 shortcut to a filter 357, 361 to a report 283 to a template 357, 361 1005
Index
Shortcut command 911 shortcut to a filter 177 saving a report with 179 shortcut to a template 357, 361 Template Dependency Validator 364 shortcut-to-a-filter qualification 128 advanced options 151 merging attribute qualifications 151 prompted 176 resolving in a report 151 shortcut-to-a-report qualification 128 example 283 Freeform SQL reports 634 prompted 176 Query Builder report 732 simple metric defined on 10 sizing a graph 492 smart metric 96 smart subtotals 96 SOAP protocol 644 sorting 298 advanced 298 attribute form 301 character sort order 958 custom group 197, 198 default order 298 hierarchical 298 multiple attribute forms 301 multiple-key 298 null values 306 project order in Developer and Web 959 standards in MicroStrategy 957 source of a link 569 source table 755 SQL common table expression 614 creating a Transaction Services
1006
Advanced Reporting Guide
report 682 date formatting 885 derived table 614 Freeform SQL report 610 in a document 616 standard report versus 615 hint 880 maximum size 884 metric alias 122 multi-pass SQL 613 passes, reducing in a report 282 query syntax 612 report post statement 874 report pre statement 874 support 613 table post statement 875 VLDB property 861 static report 356 Stock graph 417 best practices 420 example 418 string manipulation 158 using a custom expression 157 sub query type 875 Subtotal Editor 102 subtotal function 102 subtotals defined on 100, 289 across a level 289 example 292 advanced 289 applied level option 289 by position 289 example 290 column subtotals formatting layer 324 consolidation 214 custom 294 custom group 196 dimensional 103
© 2015 MicroStrategy, Inc.
Advanced Reporting Guide
Dimensionality Aware VLDB property 865 function default 101 multiple 103 nested 103 predefined 102 standard subtotal 102 group by 290 example 293 memory usage and 297 metric dimensionality 865 metric in the formula 103 report 289 row subtotals formatting layer 325 smart 96 tasks. See subtotals - tasks. user-defined 102 weighted 106 subtotals - tasks creating 102, 104 disabling 110 displaying on a report 101, 291 formatting 319 sum moving 115 running 115 support international xxxiv support. See technical support. system hierarchy drill path 227 system prompt 258 user login 258
T table importing 711 selecting in a Query Builder report 711
© 2015 MicroStrategy, Inc.
Index
VLDB properties Cartesian join 868 option 882 post statement 875 space 882 target of a link 570 technical support xxxv template 361, 367 embedded 357 empty object 367 object 366 shortcut to 357, 361 Template Dependency Validator 364 template details 528 base report 529 example 530 base template name 531 example 532 configuring 528, 534 report details preferences interaction 534 view report 529 example 530 temporary table dropping 880 intermediate table type 881 text file, exporting a report to 372 third-party web service, connecting to 644 threshold defined on 320 formatting 320, 325 graph report 454 time series analysis 90, 804 title, formatting in a graph 464 tooltip for a graph 489 totals disabling 110 formatting 319 metric 100 overview 289 1007
Index
report 289 smart 96 user-defined subtotals 102 training metric 815 dependent metric 821 exponential regression 817 independent metric 821 linear regression 817 output model 825 segmentation metric 821 tree level metric 821 Training Metric Wizard 791, 816 training the model 815 Transaction Services. See MicroStrategy Transaction Services. transformation defined on 88 transformation metric 88 creating 92 Advanced Metric Assistant 97 multiple transformations 93 year-to-date 91 transparency on a graph object 378
U union multiple insert 880 univariate regression model 794 updating data with MicroStrategy Transaction Services 681 user audience xxiv user input with a prompted filter 174 user login system prompt 258 user-defined empty object template 367 user-defined function. See custom function. user-defined subtotals 102 creating 104 example 104, 106
1008
Advanced Reporting Guide
V value prompt 249 link and 574 variable for a graph title 463 variance 90 view definition defined on 286 evaluation order 344 Intelligent Cube 288 object 287 virtual attribute (consolidation) 207 VLDB property 861 accessing advanced report VLDB properties 868 database instance VLDB properties 884 metric VLDB properties 863 report VLDB properties 868 Attribute Form Selection Option for Intermediate Passes 877 Attribute Selection Option for Intermediate Passes 877 Cartesian Join Warning 868 data mart 765 database instance level 884 default inherited value 862 Drop Temp Table Method 880 Fallback Table Type 881 Full Outer Join Support 870 Group by ID Attribute 879 Group by Non-ID Attribute 880 hierarchy of 862 Integer Constant in Metric 863 Intermediate Table Type 881 Maximum SQL Size 884 metric level 863 Null Check 864 Null Checking for Analytical Engine 865 © 2015 MicroStrategy, Inc.
Advanced Reporting Guide
order of precedence 862 Preserve All Final Pass Result Elements 870 Preserve All Lookup Table Element 871 report level 867 Report Post Statement 874 Report Pre Statement 874 SQL Date Format 885 SQL Hint 880 Sub Query Type 875 Subtotal Dimensionality Aware 865 Table Option 882 Table Post Statement 875 Table Space 882 Union Multiple Insert 880 Where Clause Driving Table 876 Zero Check 865
Index
X XQuery 644 creating a Transaction Services report 682 retrieving data from a web service 647 security 650
Z zero checking 865 zone formatting 318 subtotals 319
W Web Analyst user 353, 355 Web Professional user 353, 355 Web Reporter user 353, 355 web service as a data source 644 connecting MicroStrategy to 645 creating a report 665 security in an XQuery statement 650 XQuery for data retrieval from 647 Web Services Security (WS-Security) for an XQuery statement 651 weighted subtotals 106 Where Clause Driving Table VLDB property 876 widget 515 write-back. See MicroStrategy Transaction Services. WS-Security. See Web Services Security.
© 2015 MicroStrategy, Inc.
1009
Index
Advanced Reporting Guide
1010
© 2015 MicroStrategy, Inc.