Creating a simple query

Our first step will be to create a simple query based on a universe called motors.

This universe is used for ad hoc reporting about a car rental and sales company.

The core data in this universe is the car rental and sales figures that can be sliced by customer, time, type of car, and geographical data.

Getting ready

We want to create a query that will display the sales revenue for each car category.

How to do it...

First of all, we will be required to access the motors universe.

We will launch the Web Intelligence application through the main launch panel in the home screen. In the empty tab that has opened, we will click on the New button that is located to the left of the Open file icon, as shown in the following screenshot:

The next screen will prompt us to choose the data source we want to use in order to create our query. The options are as follows:

• No data source: This enables us to preformat the report without creating or running a query
• Universe: As discussed earlier, a universe can be based on a relational database, an OLAP cube, or even Excel files
• Excel: This uses an external Excel file as the data source
• BEx: This uses a BEx query or a BW OLAP cube
• Analysis View: Analysis workspaces can be exported in Analysis View; these workspaces are created by using the Analysis edition for OLAP
• Web Services: This uses a web service as data source that is based on an HTTP communication protocol
  

We will choose the second Universe option. The universe prompt list screen will promptly appear and we will be able to choose the motors universe from the list, as shown in the following screenshot:

The universe list appearing in front of us is subject to business user security rights and business associations. We can access our relevant universes based on them.

After choosing the motors universe, we will access the Query Panel.

We will be able to see the universe outline structure on the left-hand side pane of the main screen, and on the right-hand side pane, we will see the following three panes:

• Result Objects: All the objects that the query will fetch as columns in the report layer via the presented data will appear here
• Query Filters: Here, the query conditions are set in order to restrict the fetched data to correspond to a specific set of rules
• Data Preview: This enables us to preview the row data returned before the data is presented in the report layer

We drag-and-drop the following objects from the left-hand side pane to the Result Objects pane: Category of Car, which can be found in the Car class, and Sales Revenue, which can be found in the Sales Figures subclass, as shown in the following screenshot:

We will click on the Run query button on the top-right-hand side of the Query Panel window. The query will run and fetch the relevant data and display it in the report, as shown in the following screenshot:

How it works...

When objects are being dragged-and-dropped into the Result Objects pane, the universe structure begins to be formed and SQL code is generated, which is the basic query language for databases.

This SQL code, generated according to the objects, is thrown to the database once we execute the query and retrieves all the relevant rows as the objects are mapped to the actual tables in the database.

The query panel can also be described as a SQL generator using drag-and-drop.

See also

• For further information on SQL code generation, see the Viewing the SQL recipe

There's more...

The order of the objects in the Result Objects pane will be the same as those of the objects displayed in the report. If a business user needs to change the order of the displayed columns, he or she can easily do so by dragging-and-dropping the table column.