Understanding the Concept of Multiple Queries

Objective

After completing this lesson, you will be able to understand the concept of multiple queries

Multiple Data Providers

When you run a query against a single data source, the results of the query are stored in the microcube of that document and the query is identified as a data provider. Within one Web Intelligence document, you can include multiple queries based on one or more of the universes available in the SAP BI Platform.

Each universe is built for one functional area. Therefore, when you work with both sales data and customer demographics, the data comes from two separate universes. For example, your organization may use one universe to access data product line sales, and another universe for customer data. It might be useful to include data from both of these sources in one Web Intelligence document.

Universes, queries, and personal documents are all data providers. You can include data from personal data providers such as Excel spreadsheets, text files, and CSV files in your document.

If you want to present product line sales results and information on customer age groups in the same report, create a single document that includes data retrieved by two queries. Each of these queries is built using a different universe. You can then include and format the results from both queries in the same report.

Using Multiple Data Sources

Using multiple data sources: See text after image for more details.

The figure, Using Multiple Data Sources, shows an example of how data from various sources can be formatted within one document, irrespective of the format of the original source. The system administrator assigns rights for access to specific universes.

Advantages of Using Multiple Data Sources

The following list outlines some of the actions that using multiple data providers helps you to perform once the basic report is built:

  • Compare and contrast information in a single table.
  • Add calculations across data sources.
  • Synchronize multiple data providers and merge on common dimensions to display data in the same block.
  • Synthesize data from different sources in the same report.
  • Create new variables and develop your analysis further.

Data Projection from a Single Query into Different Blocks

Watch this video to learn about Data Projection from a Single Query to Different Blocks.

Procedure: To Project Data from a Single Query into Multiple Blocks

Follow these steps to retrieve only unique rows to project data from a single query into multiple blocks:

  1. In Web Intelligence, run a query including various objects.

    The report shows a single block of data.

  2. Select objects from the Document Dictionary tab and move them into the report next to the first block.

    The report shows two blocks of data. The first shows all of the objects in the query, the second shows data involving the objects that you moved into the report. The data for both blocks originates in a single query.

Data Projection from Multiple Queries Using a Single Universe

Data projection from multiple queries using a single universe: See the text after and after the image for more details.

You can create more than one query on the same universe in one document. Creating multiple queries allows you to use different query filters on each query, even though all the queries are in one universe.

To compare metrics between different time periods, you run multiple queries on one universe in a single document. For example, you want to see the sales revenue for the current week, current month, and current year all in one table. In this case, create three queries, each with three different filters for the three different time periods.

If the document contains a set of data returned by an existing query, you can edit the query to include more objects. You can also expand the data contained in the data provider. Sometimes, adding more detail to the existing query can be inefficient because we only need a small subset of the detail.

You can choose to perform the following actions within one document:

  • Add a second query to the document.
  • Select different objects from the same universe.
  • Expand the document data by using two separate data providers.
N/A

Procedure: To Project Data from Two Queries into Multiple Blocks

Follow these steps to project data from two queries into multiple blocks:

  1. In Web Intelligence, run a query including various objects.

    The report shows a single block of data.

  2. In the Query section of the toolbar, choose Edit.

    The Query Panel screen appears.

  3. Choose Add Query.

    The Select a Data Source screen appears.

  4. Choose SAP BI Platform RepositoryUniverse.
  5. Choose OK.

    The Select a Universe screen appears.

  6. Select the same universe.
  7. Choose OK.

    A second Query tab appears at the top of the Query Panel screen. Use these tabs to toggle between the two queries.

  8. Drag the dimensions and measures you want to include in the query into the Result Objects pane.
  9. Open the Run list.
  10. Select the second query.
  11. In the dialog box that appears, choose one of these options:
    • Insert a table in a new report:

      Display the data on a new report in the same document.

    • Insert a table in the current report:

      Display the data on the currently selected report in a new table.

    • Include the result objects in the document without generating a table:

      Include the data in the document without displaying the data on a report (this option gives you more control over where you want to insert the new data).

  12. Select the appropriate option, and choose OK.
  13. Choose Document Dictionary tab.
  14. Open the View Modes and Settings list.
  15. Select Queries and Cubes.

    The object listing changes to show the objects grouped by query.

  16. To create a new block, choose the appropriate objects from the Document Dictionary tab and move them into the report.

Data Projection of Queries From Multiple Universes

Watch this video to learn about Working with Multiple Data Providers.

Procedure: Project Data from Multiple Data Sources into Multiple Blocks

Follow these steps to project data from multiple data sources into multiple blocks:

  1. In Web Intelligence, run a query including various objects.

    The report shows a single block of data.

  2. In the Query section of the toolbar, choose Edit.

    The Query Panel screen appears.

  3. Choose Add Query.

    The Select a Data Source screen appears.

  4. Choose SAP BI Platform RepositoryUniverse.
  5. Choose OK.

    The Select a Universe screen appears.

  6. Select the new universe.
  7. Choose OK.

    A second Query tab appears at the top of the Query Panel screen. Use these tabs to toggle between the two queries.

  8. Drag the dimensions and measures you want to include in the query into the Result Objects pane.
  9. Open the Run list.
  10. Select the second query.
  11. In the dialog box that appears, choose one of these options:
    • Insert a table in a new report:

      Display the data on a new report in the same document.

    • Insert a table in the current report:

      Display the data on the currently selected report in a new table.

    • Include the result objects in the document without generating a table:

      Include the data in the document without displaying the data on a report (this option gives you more control over where you want to insert the new data).

  12. Select the appropriate option, and choose OK.
  13. Choose Document Dictionary tab.
  14. Open the View Modes and Settings list.
  15. Select Queries and Cubes.

    The object listing changes to show the objects grouped by query.

  16. To create a new block, choose the appropriate objects from the Document Dictionary tab and move them into the report.

Refreshable Queries

You can select the queries to refresh when refreshing a document.

After you have once refreshed a document that contains several data providers, you can decide not to refresh specific queries for further refreshes. In this case, the data provider keeps the data-set retrieved from the previous refresh and stored in the data provider. This improves refresh performance when you do not need to refresh queries whose data you know do not change in time, as there is no need to fetch the data to retrieve results.

The following data providers are refreshable:

  • Universe
  • SAP BW
  • SAP HANA
  • Excel
  • Free-hand SQL
Refreshable queries: See text before image for more details.

Procedure: To Set a Query to be Refreshable

Follow these steps to set a query to be refreshable:

  1. In the Query section of the toolbar, choose Edit.
  2. Choose Show Query Properties in the Query Panel's toolbar.
  3. Select Refreshable.
  4. Choose OK.

Select a Data Source

The universe is not the only data source.

Select Data Source: See the text after and after the image for more details.

Building Queries on SAP BW InfoProviders and BW Queries

You can leverage your SAP BW (incl. BW/4HANA and S/4HANA) environment and query a range of BW InfoProviders or BW queries via OLAP connections based on the BICS connector.

BW InfoProviders are SAP objects containing physical data that BI applications like Web Intelligence can retrieve for reporting purposes. In SAP BW, you use BW queries created in SAP Query Designer to run analyses on the different types of InfoProviders, such as Data Store Objects, cubes, InfoObjects, InfoSets, and others. You decide whether you want to reuse existing BW queries, or query InfoProviders directly.

No universe is needed when you query BW InfoProviders and BW queries, as the application uses direct access to the BW data source to retrieve metadata. When accessing BW InfoProviders and BW queries, Web Intelligence automatically maps the BW metadata to hierarchies, attributes, measures, and dimensions, like BW OLAP universes queries, so you can reuse them in your report.

Building Queries on SAP HANA Views

With Direct Access, create queries on SAP HANA views directly and bypass universes.

Direct Access provides direct access to SAP HANA information models, also called HANA views, allowing the application to connect directly to the cube representing the information model. Direct Access generates a transient universe on the fly so that you don't have to go through the universe authoring process. It saves you time, as you can get straight to the query specification in the query panel.

Web Intelligence supports native HANA views modeled in SAP HANA Studio, and HANA HDI calculation views modeled in SAP Web IDE for HANA with XS Advanced.

Building Queries on Relational Connections using Free-Hand SQL Statements

In Web Intelligence, you can use a Free-hand SQL (FHSQL) statement to query a relational database.

FHSQL data providers are useful when you have complex SQL statements that use advanced database functions not supported by the standard semantic layer.

Building Queries on SAP Datasphere

SAP Datasphere enables a business data fabric architecture that uniquely harmonizes mission-critical data throughout the organization, unleashing business experts to make the most impactful decisions. It combines previously discrete capabilities into a unified service for data integration, cataloging, semantic modeling and data-warehousing. It also virtualizes workloads spanning SAP and non-SAP data. SAP Datasphere preserves the full meaning and context of SAP data across systems and clouds. It integrates with other data vendor’s platforms, delivering seamless and scalable access to one authoritative source for your most valuable enterprise data. SAP Datasphere leverages existing data investments. It does not require moving data into yet another data store. It radically simplifies your data landscape, ensuring inherent governance throughout the data life-cycle.

Web Intelligence can access to SAP Datasphere native views such as Analytic Models.

Log in to track your progress & complete quizzes