Selecting a Query Template 
Use
After you have selected a system in the BI Query Wizard, or if you are entering the BI Query Wizard from configuring your data service directly in the workspace, you select a query template. A query template is a set of screens that you complete to answer a typical business question ‑ a question formulated to organize and analyze the data in your data source.
The set of templates available depends on whether the data service you have selected is an OLAP or a relational data service.
Queries based on Business Information Warehouse (BW) Web API Connections bypass this screen and go to the Select a Query screen. See Working With BW Query Views for more information.
Procedure
...
1. OLAP data services only: In the Query Templates list, expand the category of templates you wish to work with.
OLAP templates in the BI Query Wizard are organized into categories, each addressing different types of business questions.
2. In the Query Templates list, select the query template upon which you wish to base your query.
The set of templates displayed varies depending on whether you have selected an OLAP or a relational data service.
For each template, read a description in the Description section, and a sample business question in the Sample Business Question section.
The following templates, in categories and listed together with their descriptions and sample business questions, are provided for OLAP data services:
OLAP Query Templates
Category / Name |
Description |
Sample Business Question |
Grand Totals (default) |
Aggregates all measures to the highest level. Returns one row of data with the total for each measure. |
What is the total revenue of all sales transactions? |
Aggregation |
||
For a selected level, counts members contributing to a specific measure. |
Sales returns for MyCompany equal $25,000. How many product lines does this represent? |
|
Calculates the share that selected members represent within the sum of the level members, and expresses this as a percentage. |
What percentages do Canada and Mexico represent of my total sales? |
|
Dynamic Calculation |
||
Cumulates the value of a measure over a specific date range. |
What is my monthly year-to-date expense total? |
|
Calculates the average values of selected measures based on specified time periods. |
What are the average manufacturing costs of products A and B for Q2 and Q3, based on the previous four months? |
|
Rank |
||
Based on a specific measure, calculates the change in rank of members of a particular level compared with their rank from a previous period. |
What is the change in rank for my top ten customers between last year and this year? |
|
Calculates the rank of a level based on a specific measure. |
Who are my top ten or bottom ten customers? |
|
Trend |
||
Displays the trend of a specific measure for a selected member over time. |
What is the trend in my gross margin over the past few years? |
|
Variance |
||
Calculates the percentage by which a specific measure has increased or decreased for a selected member in comparison to the same period of time previously or subsequently. |
By what percentage did my sales increase this year compared to last year? |
|
Calculates the values of a measure defined as actual and a measure defined as planned, illustrating the difference between actual and expected results. |
How do my actual costs compare to the planned costs? |
|
Identifies members of a selected level that have had sales in the past, but which have had no sales in a specified time period. |
Which products didn't sell last year? |
|
The following templates, listed together with their descriptions and sample business questions, are provided for relational data services:
Relational Query Templates
Name |
Description |
Sample Business Question |
All Data (default) |
Selects all records and all fields as the final result, without any filters or joins. |
What are all the available grade levels and their details? |
Counts all occurrences of a certain type of data in a group or groups within a table. |
How many employees is each different manager responsible for? |
|
Walks you through defining a query without the constraint of a particular business question. |
Not applicable. |
|
Aggregates values of a selected primary group and compares them with values of a secondary group by displaying the results in boxes of differing sizes and colors. |
What is the relative distribution of sales among certain divisions and companies? |
|
Finds the minimum or maximum value of a field, and provides details from the row that holds that value. |
Which store has the smallest revenue; where is that store and who is the manager? |
The templates designated as default above are the default templates when you configure a query directly in a data service. See Configuring Queries Directly in Data Services for more information.
The relational Count template is not available for SAP Query data services.
3. Click Next.
Although we provide a sample business question for each query template, note that a given template can address many different questions. For example, you can configure the Top N query template to address any of the questions below:
· What are my top five customers based on sales?
· What are my bottom ten products based on margin?
· What products make up the top 10% of sales?
Result
You proceed to the first screen specific to configuring your query. Future steps in the navigation trail update based on the template you have chosen.
Select a template from the table above to view the specific steps involved with configuring that query.