Building Queries on Spreadsheet and Text Data Sources

Objectives

After completing this lesson, you will be able to:

  • Use personal data providers as a data source

Use Google Drive as Data Source

Before being able to create reports based on files hosted on Google Drive, make sure your administrator has set up an authorization server configuration for OAuth authentication in the Central Management Console so that the SAP BI Platform can access these cloud storage services.

The Data Source dialog box contains a new category Cloud Storage, with Google Drive.

You can then navigate in the Google Drive repository and select your data source file.

If you have selected an Excel or a Text file, you must enter the same file interpretation settings as for the SAP BI Platform Repository. If you have selected a Google spreadsheet file, you must also enter the settings that define what data to query.

The Query Panel which has got the same look no matter the data source you use, is displayed.

Select the objects to query or to use in filters.

When your query is complete, click Run.

Once the query has been run, you can work with the dataset retrieved from the data source on the Google Drive and format your document.

Object properties are no longer set in the Query Panel, but in a new Object Properties tab once you have run the query.

Note

For more information, please refer to Unit: Working with Formulas and Variables - Lesson:  Editing Objects Properties.

Watch this video to learn how to build queries on Google sheet file:

Schedule Destination

When you schedule a Web Intelligence document, you may select Google Drive as a destination for the generated output. In Google Drive, you can only schedule Excel or Text files. In the Select Destinations dialog box, you can browse the Google Drive and select a destination folder for which you have writing rights.

Use Microsoft OneDrive as Data Source

Following the support of Google Drive, Microsoft OneDrive can be also used as a data source repository or a schedule destination.

As for Google Drive, to authorize the SAP BI Platform to access and use your resources on Microsoft OneDrive, you must configure the OAuth protocol.

The Data Source dialog box contains a new category Cloud Storage, with Microsoft OneDrive.

Click Microsoft OneDrive to open the Microsoft Drive dialog box where you can browse your folders and select the Excel, Text or CSV file to use as data source.

In the Microsoft OneDrive dialog box, the OneDrive folder contains the files stored in your personal space in OneDrive, whereas the Shared with Me folder contains the files and folders that have been shared with you through Microsoft OneDrive or SharePoint online.

The Query Panel which has got the same look no matter the data source you use, is displayed.

Select the objects to query or to use in filters.

When your query is complete, click Run.

Object properties are no longer set in the Query Panel, but in a new Object Properties tab once you have run the query.

Note

For more information, please refer to Unit: Working with Formulas and Variables - Lesson: Editing Objects Properties.

Schedule Destination

When you schedule a Web Intelligence document, you may select the configured Microsoft OneDrive as destination and select the folder for the generated output.

Use Text File as Data Source

When you create a document, or when you add a query to a document, the Text option is available.

Selecting this option allows you to browse the SAP BI Platform Repository and list the Text and CSV files that have been uploaded to the public or your personal folders. Select a file and click Open.

In the Text Data Provider file options, you may define the properties to interpret the Text file. In the Data Separator section, where you can select how columns are separated in the file, the Character menu contains by default the comma and the semicolon. You may also type any character in this text field.

Once you have selected your options, click OK to open the Query Panel where you can finalize and run your query.

Object properties are no longer set in the Query Panel, but in a new Object Properties tab once you have run the query.

Note

For more information, please refer to Unit: Working with Formulas and Variables - Lesson:  Editing Objects Properties.

Use Microsoft Excel as Data Source

If you're working in the Web Client, make sure the Excel file you want to use as a data source is available in the SAP BI Platform Repository.

The Data Source dialog box contains a category SAP BI Platform Repository, with Excel.

When you build a new query and the data source you use is an Excel file you have various options to browse the SAP BI Platform Repository such as Categories, Favorites, and so on.

In the Excel Data Provider dialog box, you may define the properties to interpret the Excel file.

The Query Panel which has got the same look no matter the data source you use, is displayed.

Select the objects to query or to use in filters.

When your query is complete, click Run.

Object properties are no longer set in the Query Panel, but in a new Object Properties tab once you have run the query.

Note

For more information, please refer to Unit: Working with Formulas and Variables - Lesson: Editing Objects Properties.

Log in to track your progress & complete quizzes