Utilizing Multiple Data Providers

Objective

After completing this lesson, you will be able to Create multiple queries in a document.

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 BI launch pad.

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 local documents are all data providers. You can include data from local 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

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 from Another Query

See the following video to learn more about queries.

Data Projection from a Single Query into Different Blocks

See the following video to learn more about Data Projection from a Single Query to Different Blocks.

How To Project Data from a Single Query into Multiple Blocks

In this demonstration, create a document to analyze the sales revenue figures for the last three years, and display the sales revenue per store.

Block 1 will show the sales revenue of the stores in the eFashion.unx database for different years. Block 2 will show only a subset of this information from the same query.

Steps

  1. Create a document with a query against the eFashion universe using the Store name, Year, and Sales revenue objects.

  2. Add a second block of data containing Store name and Sales revenue.

    1. Select Store name, hold down Ctrl, select Sales revenue, and drag the objects to the space next to the first block of data.

    The new report now displays two blocks of data, as follows:

    • Block 1 shows Sales revenue calculated for each store and per Year.
    • Block 2 shows the overall Sales revenue calculated per store, with all Years combined.

    Both blocks were built using data from a single query.

  3. Save the document as Multi Block 1.

    You use this document in the next demonstration.

Data Projection from Multiple Queries Using a Single 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.

How To Project Data from Two Queries into Multiple Blocks

In this demonstration, you show learners how to add a new query to retrieve new data from the eFashion.unx universe.

Continue using the document Multi Block 1.

Steps

  1. To add the second query to the document, In the Query section of the toolbar, choose Edit.

  2. On the toolbar, choose Add Query, and from the drop-down menu, choose Universe.

    The Universe dialog box appears.

  3. Choose eFashion as the data source, and choose OK.

    The Query panel appears.

    Note

    At the bottom of the panel, there are now two tabs: Query 1 and Query 2. You can use these tabs to toggle back and forth between the two query definitions.
  4. In the Query 2 tab, select Store name and Margin and drag the objects into the Result Objects area.

  5. Choose the arrow next to the Query 2 tab, choose Rename, enter Margin query, and choose Enter.

  6. Choose the arrow next to the Query 1 tab, choose Rename, enter Revenue query, and choose Enter.

  7. Choose the Run button.

    The Add Query dialog box appears. It lists the options to include the data from the new query in the report.

  8. Select Include the result objects in the document without generating a table, and choose OK. Choosing not to generate a table gives you more control over where you want to insert the new data. When you choose to insert a new table, you cannot determine its position in the report. It can even display on top of the existing tables. The objects you selected in the Margin query appear in the Show document objects panel.

  9. In the Show document objects panel, in the View modes and settings drop-down list, choose Queries View.

  10. Save the document as Multi Block 2.

    You use the document in the next demonstration.

Data Projection of Queries From Multiple Universes

See the following video to learn more about Working with Multiple Data Providers.

How to Project Data from Two Data Sources into Multiple Blocks

In this demonstration, you show learners how to add a new query from a new universe to an existing document with queries from another universe. You want to analyze how a store’s staffing level affects the sales revenue.

Continue using the document Multi Block 2.

Create and run a query on the eStaff.unx universe. Then add the data in a third block in the same report. This block shows the number of employees for each store.

Steps

  1. To add a new query to your document, in the Query section of the toolbar, select Edit.

  2. Choose Add Query, choose Universe, and choose OK.

    The Universe dialog box displays.

  3. Choose eStaff as the data source for the third query, then choose OK.

    The Query Panel appears. Note that there are now three tabs: Revenue query, Margin query, and Query 3. You can use these tabs to toggle back and forth between the query definitions.

  4. In the Query 3 tab, select Store and Number of Employees, and drag into the Result Objects area.

  5. Choose the arrow next to the Query 3 tab, choose Rename, enter Employee query, and select Enter.

  6. Choose Run.

  7. In the Add Query dialog box, select the Include the results objects without generating a table, and choose OK.

    Store and Number of Employees in the Employee query from the eStaff.unx universe appear in the Show document objects area.

  8. In the Show document objects area, choose Store, hold Ctrl, choose Number of Employees, and drag the objects next to the existing table in the report.

    A new table is inserted next to the existing table and the table from the Employee query is projected into the new table.

    You have now displayed data from two different data sources in three tables side-by-side in the report. These tables allow you to compare each store’s margin with the number of employees per store.

    However, it is clear that both data sources, eFashion and eStaff, contain data about the same stores. Next you will learn how to show the same data but this time in a single block of data, or a single table. To do this, you manually merge two dimension objects, so that you can project the store names in a single column in the table. This is known as synchronizing data.

  9. Save the document as Multi Block 3.

    You use the document in the next demonstration.

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

Log in to track your progress & complete quizzes