Building Queries on Spreadsheet and Text Data Sources

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.

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

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.

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.

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.