You may need to change a query for several reasons:
- To meet new business requirements by adding dimensions or metrics.
- To improve performance by adding query filters to reduce the volume of data.
- To create formulas or variables by adding new measures.
To modify a query, you select Edit in the Query section of the toolbar.
You select the object and place it in the table.
The Two Levels of Aggregation: Database vs. Web Intelligence
In Web Intelligence, you work with two levels of aggregation. Aggregation means summarizing data, for example, by calculating a sum or a count. When you understand these levels, you can build efficient and flexible documents.
- Aggregation at the data source level is the first and most important step. It takes place before the data reaches your Web Intelligence document. When you run a query, the Web Intelligence Processing Server creates SQL code and sends it to the source database, such as Oracle, SQL Server, or SAP HANA. This SQL code often includes aggregation functions such as Sum, Count, Avg, Max, and Min. The database engine runs the query, performs the calculations, and returns only the summarized results.
- Aggregation at the Web Intelligence document level happens after the data is retrieved from the database and loaded into the Web Intelligence document’s microcube. For example, if your query includes sales revenue by country and year, you can perform further analysis:
- You can create a table with the country dimension and the sales revenue measure. Web Intelligence sums the sales revenue for all years to give you a total for each country.
- You can calculate total sales revenue using a simple formula, such as =Sum([Sales revenue]), in a free-standing cell. Web Intelligence adds all the sales revenue figures in the microcube.
- You can change the context of your table. If your table shows year and sales revenue, and you add country, Web Intelligence updates the data to show country, year, and sales revenue.
Let's Summarize What You've Learned
- Modify queries to meet new business requirements, improve performance, or create formulas or variables.
- Aggregation occurs at two levels: first in the database, then in the Web Intelligence document.
- Database-level aggregation summarizes data before it reaches Web Intelligence, improving efficiency.
- Web Intelligence document-level aggregation allows further analysis and flexible data summarization after data retrieval.