Creating queries is considered in many ways the core functionality of the Web Intelligence application. The source for every report is a query that is based on a database (relational or operational), a data warehouse, an OLAP cube, a BEx query, a web service, or even an Excel or a text file.

The general idea and main theme when building a query is that we create a structure that can access our data sources and fetch relevant, meaningful, useful data to our report presentation layer, according to the user requirements.

Creating queries is the first step in creating a report and it is important because it defines the context of the data we are analyzing as a well as its accuracy.

A good query would be one that retrieves only the data slice that is relevant to the report consumer.

In terms of efficiency, query running time, and performance, we are more dependent on the way and type the database is structured. However, for the end user (the report builder), what matters is the fact...

A universe is a semantic layer that maps a specific data area or several areas in the data warehouse or in any database such as SQL Server, DB2, Oracle, and many others. The universe uses a connection in order to connect to the database, and once it's set, users can navigate to the universe, build queries, and connect through the universe connection to the database, retrieving the most updated data according to the data update date in the database.

The universe represents the data in a lucid manner, so business users don't require database programming knowledge or an understanding of how the database is structured.

What the user does need to know is how the universe is structured: its main business purposes, what kind of queries can be built using it, and what kind of objects can be used to build his reports and where can he find them in the universe.

Traditionally, a universe maps a specific data subject such as Sales, Marketing, Human Resources, Logistics, Billing,...

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.

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...

Queries can be edited in order to adjust to the evolving requirements of the report. Through my work, I have come to know a simple fact: most reports are edited at one stage or another.

Editing is easy and is conducted in the same way a query is created, that is, by adding objects to the query or by removing some of them.

How to do it...

On the report screen, we will click on the Edit button under the Data Access ribbon; this will open the Query Panel.

We will drag-and-drop the Model object and run the query.

The result table will remain the same. In order to present the additional object that we added to the query, we will drag Model from the Available Objects panel to the existing table, as shown in the following screenshot:

The final table will now display the new object as well, showing for each category the model and...

Every query is built to retrieve a specific set of results.

When we define a query, the data can be processed and analyzed in several possible contexts:

Without a filtering context, a query is somewhere between serving a meaningless purpose and answering a very general question. The main idea when creating query filters is to fetch only the data that is relevant to the business question. The rule of "garbage in garbage out" applies here. By defining all the relevant conditions, we ensure that we will get accurate results as well as better performance.

Every condition is structured from the following three...

Prompt is an operand that enables a dynamic value insertion into the condition's last section. When we think about it, most reports use prompts as they require dynamic values in the conditions. The best example is a time-ranged report that requires the picking of different date values every day.

How to do it...

First, we will change the operand type to Prompt, as shown in the following screenshot. Note that generic text is added to the prompt area that says Enter values for Client.

We will click on the Run query button. A prompt window will appear. In this prompt window, we will be asked to pick a value from the existing list of values, as shown in the following screenshot. As long as a value isn't picked, a red arrow will appear next to the prompt text.

We will pick the country Japan. The red arrow will change to green, confirming that the prompt has...

Using several conditions in a query is a very common scenario because, in many cases, we are required to build a query with a set of filters to get the most accurate results.

Conditions in queries can use the AND or OR operators.

A relation that creates an intersection between conditions is called an AND relation. This means all the common rows for Filter A and Filter B will be retrieved.

Have a look at the following figure. Here, the data retrieved by the AND operator can be described as the dark area in the middle. It is the common intersected area.

For instance, if our query has a set of two conditions: year equal to 2014 AND product name equal to smart phones and no smart phones were sold in 2014, the query won't return any results since the conditions didn't have common rows.

The relation between conditions using an OR operator is a relation that uses a union operator, meaning there is no need for the conditions to have common rows. If any one of the conditions...

Nested filters are used when the query requires a more complex set of conditions.

Nested filters mean there is a priority set to a group of conditions while the other group or groups are set with a different priority.

The most basic rule is that the AND operation is prioritized before the OR operation.

Nesting filters allow us to ask more complex questions but implement them easily with the drag-and-drop method.

A subquery is a querying technique designed for more complex business questions that require us to analyze them further and additional information based on the data retrieved.

The subquery technique is used to return data that will be used by the main query. For example, we want to generate a better analysis for the sales team and understand which types of cars were bought by the same client.

The goal of this business question is to investigate whether a specific combination of car types are being bought by clients: did clients who bought Austin Martini cars buy other types of cars as well?

By analyzing this data, we will be able to make better marketing and sales offers to the customers as well as get some basic information about sales trends and customer behavior.

Combined queries are a form of advanced querying technique that uses several queries for comparison in order to return a single dataset.

Using combined queries enables us to retrieve results from more than one query into a single table by comparing the rows of each query while a subquery compares a single object result.

Combined queries can use the following operators:

When building combined queries, the order, data type, and number of result objects has to be the same; however, there is no limitation to the number of query filters.

Building several queries in the Query Panel is a core functionality of Web Intelligence.

The ability to centralize several data aspects in the same report is considered in many ways the heart of reporting and analysis.

There are many scenarios where we would be required to build more than one query; the main reasons are presented as follows:

When working with several queries, it is recommended to work with conventions.

First of all, we would like to give a meaningful name to each query. This is important for several reasons, as follows:

How to do it...

We will be able to edit the query by right-clicking on the Query tab, as shown in the following screenshot:

The Rename query pane will pop up and we will be able to give the query...

We can also manage query properties by navigating to the query options located on the Query Panel.

How to do it...

In the main Query Panel toolbar, we will click on the query properties button located to the right of the combined query icon, as shown in the following screenshot:

The Query Properties panel will appear, enabling us to change the query properties, as shown:

We can find the following options in this panel:

• Name: This is similar to the previous recipe's discussion.

• Universe This is the query source name.

• Limits: Using this, the number of rows in the query and the maximum retrieval time(s) can be limited; these limits can't exceed the universe definitions.

  This feature is mainly good for QA purposes and when we want to quickly apply formatting on a small amount of data while keeping the running time as short as possible. This option can't override the universe...

The data preview is a useful option in the Query Panel when we want to get a preview of the data; that is, see the raw data before it gets fetched and formatted in the table.

This option is great for getting a "sense" of the data, if the values we expect to be returned are actually these.

This feature can also be useful when we want to make sure a specific value was returned by the query but isn't shown in the report results for some reason, for example, a local report filter.

How to do it...

We will click on the Refresh option located to the upper-right corner of the Data Preview window.

Now, we will be able to see that this pane is populated with the row data, as shown in the following screenshot: