Creating the Query

In this example, you use the BI Integration Wizard to generate the query that addresses the business question "What are my top three and bottom three materials, based on incoming orders value?"

Prerequisites

As outlined in Creating a BI iView.

Procedure

Create the model and name it appropriately, such as BI_iView.
Create an iView (dragging the iView icon from the Compose Model task panel) and call it Top and Bottom Three. Drill down into the iView.
Start the BI Integration Wizard by choosing BI BI Integration Wizard .
Click Next to bypass the Welcome screen, and select your system:
1. In the Available Systems list in the Select a System screen, expand the XMLA connections folder.
2. Select the data service that contains the BI system in which the demo InfoCube is activated.
3. Click Next.
Select the query template:
1. In the Available Templates tree in the Select a Query Template screen, expand the Rank category.
2. Select the Top N template. Notice that the description and sample business question appropriate for this template appear in the sections to the right.
3. Click Next.
Select the cube:
1. From the Look for drop-down list in the Available Cubes section on the Select a Cube screen, choose Look for a cube.
2. Enter the technical name of the cube and query, 0D_SD_C03/0D_SD_C03_Q009, in the Name field, and click Search.
3. Select the 0D_SD_C03/0D_SD_C03_Q009 entry. Notice that the description and properties of the query appear in the sections to the right.
4. Click Next.
Configure the parameters for the query:
In the Configure Top N Parameters screen, there are two sections: Define Query Ranks and Values, and Define Members. Here we configure the query to return both the top and bottom three values, and we specify that we want to see top and bottom sales of materials, by incoming orders value.
1. Define query ranks and values:
  In this section, note that you can independently configure top rank and bottom rank values. You want to show both the top three and bottom three results, so configure the parameters according to the values in the following table:
  
  Define Query Ranks and Values Section
  Option
  
  Checked?
  
  Type
  
  Value
  
  Top rank type
  
  Yes
  
  Count
  
  3
  
  Bottom rank type
  
  Yes
  
  Count
  
  3
2. Define members:
  In this section, note that the drop-down lists are populated with metadata from the demo InfoCube. Configure the parameters needed for this section according to the table below:
  
  Define Members Section
  Option
  
  Value
  
  Dimension
  
  Material
  
  Hierarchy
  
  Material
  
  Level
  
  Material Level 01
  
  According to measure
  
  Incoming orders value
3. Click Next.
In the Define Filters screen, bypass the optional step of defining filters by clicking Next.
In the Review Output screen, review the table that appears. It shows the result of the query you have defined. Note that the top three materials sold are listed first, followed by a top total, and then the bottom three materials are listed, followed by a bottom total:
(Query Results)
Click Finish.
Save your model.

Define Query Ranks and Values Section
Option	Checked?	Type	Value
Top rank type	Yes	Count	3
Bottom rank type	Yes	Count	3

Define Members Section
Option	Value
Dimension	Material
Hierarchy	Material
Level	Material Level 01
According to measure	Incoming orders value

Result

You return to the Visual Composer workspace, where a data service has been created with the Top N query you have configured. The objects in the workspace should look something like this:

(BI Query - Design)

Note the data service is configured automatically with a start input and a table view as output. These three elements constitute a complete iView, ready for preview and deployment to your portal.

Now you're ready to move on to Creating and Configuring the Chart.