Create Standalone (Public) Datasets from Files and Data Sources

This image is interactive. Hover over each area for a description. Click highlighted areas for more information.

Create Datasets from Files

You can import data from an external file, such as an Excel spreadsheet or comma-separated-values file, into a new dataset. The data columns in the dataset will be exposed as dimensions or measures that you can use in stories.

Context

First, the source data is analyzed, and then the data is shown with proposed dimensions for the new dataset. You then refine the proposal by specifying dimension types and fixing any data-quality problems.

The workflow to create a new dataset from a file is:

1. Import a source file.
2. Decide whether data columns are used as measures or dimensions.
3. Cleanse your data and fix any mapping or data quality problems.

Data files can be in your local file system or in your network. The source data can be an Excel spreadsheet (.xlsx) or a delimited text file (.csv or .txt). If you import data from Microsoft Excel, and if the data is saved in separate sheets in the Excel workbook, either you can choose which sheet to import (if from a local file system) or the first sheet is automatically imported (from a network file).

1. From the Datasets start page, select From a CSV or Excel File.
2. In the Create Dataset From File dialog, choose whether you want to import data from a file on your local system, or from a file server.

   If you don't see the options to choose a local system or file server, see Allow Data Import and Model Export with a File Server.

   Tip

   If you import a file from a file server, you can also schedule imports from that file. For more information, see Update and Schedule Models.
3. If you're importing from a file server, choose a file server connection, or select Create New Connection.

   If you create a new file server connection, specify the path to the folder where the data files are located. For example: C:\folder1\folder2 or \\servername\volume\path or /mnt/abc.

4. Choose the file you want to import.
5. If you are importing from a local Excel workbook containing multiple sheets, select the Sheet you want to import.
   Note

   If you are importing from an Excel file on a file server, the first sheet is automatically imported.
6. If you're importing a .csv file, select which delimiter is used in the file, or select Auto-detect.
7. Select Import.

   For small data files, the data is imported and displayed in the data integration view, which shows the imported data columns that you'll define as dimensions, measures, and attributes.

   Larger data files may take some time to upload. You can work on other tasks while the data is being uploaded in the background.

   When the draft data is finished uploading, the data appears in the data integration view.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

SAP Business ByDesign Analytics

Context

For more information about extending SAP Business ByDesign using SAP Analytics Cloud, see Extending SAP ByDesign Analytics using SAP Analytics Cloud. Information about available data sources in SAP Business ByDesign can be found here.

1. From the Datasets start page, click From a Data Source.
2. Select SAP Business ByDesign Analytics.
   Note

   From the acquire data panel, select the filter icon to narrow down the number of data sources in the list. You can filter by data source type or by category.
3. Select an existing connection, or select Create New Connection to create a new connection.
   Note

   Advanced features of customized OData data sources, such as SAP Cloud for Customer and SAP Business ByDesign Analytics, are only available using customized data source types. These features are not available using generic OData services. It is highly recommended to use the relevant customized data source types, if available, for your data service to leverage full capability of the data connector. It is possible to request a customized OData data source solution.

   For more information, see:

   • Import Data Connection to OData Services.
   • Import Data Connection to SAP Business ByDesign Analytics
   • Import Data Connection to SAP Cloud for Customer
4. Type a name for your query.
5. Select a table, and then select Next.

   Build your query by moving data elements into the Selected Data and Filters areas. For more information, see Building a Query.

6. Select Create.

   The data appears in the data integration view, where you can complete the mapping of your new data to the dataset's dimensions.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

SAP BW

Prerequisites

You use an SAP Business Warehouse (BW) system, version 7.3x or higher release, or an SAP BW/4HANA system, SP4 or higher, and both the SAPCP cloud connector and SAP Analytics Cloud agent are installed and configured.

Note

If you're importing data via a BEx query using an SAP BW import data connection, see these SAP Notes: 2416705 2408693 .

1. From the Datasets start page, click From a Data Source.
2. Select SAP BW.
3. Choose an existing connection, or create a new connection
4. Create a new query.
   1. Type a name for your query.
   2. Select a BW query from the list, or search for a query by name, and then select Next.
   3. If the data source contains prompts, select values for them. If you're using a variable that is filled by customer exit, you can select the Use Backend Default option. Then when you refresh the data, the data is based on the current value filled by the customer exit instead of the value saved when the query initially ran.
   4. Select dimensions and measures for your query.

      (If you're importing data via a BEx query using an SAP BW import data connection, see the note in the prerequisites above.)

   5. You can select the icon to open the Select Presentations dialog, and then choose which presentations you want to see. Depending on the characteristic, you could have these presentations available:

      Key (DISPLAY_KEY)
      Key (Internal) (KEY)
      Long Text (LONG_TEXT)
      Medium Text (MIDDLE_TEXT)
      Short Text (SHORT_TEXT)
      Text (TEXT)

   6. If one of the selected dimensions has a hierarchy, you can click (Select Hierarchy and Drill Level) for more options, such as specifying which hierarchy to use. Note that No Hierarchy / Flat Presentation (Default) is the default.

      You can also specify the drill level. The default drill level is 2.

   7. Select Create.

   You can continue to work on other tasks while the data is being uploaded in the background.

   When the draft data is finished uploading, the data appears in the data integration view.

5. Complete the following substeps if your SAP BW data source contains date dimensions that you want to enrich with time-hierarchy information.

   If you enrich the date dimensions, you can use date-related features such as sorting by date, and date range sliders for filtering.

   1. Select the column that contains the SAP BW date dimension data.
   2. In the right hand Details side panel, in the Column Details section, choose the Date data type.
   3. Choose the appropriate Conversion Format.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

Related Information

Import Data Connection to an SAP BW or SAP BW/4HANA System

Installing the SAPCP Cloud Connector

SAP Analytics Cloud Agent

SAP Cloud for Customer

Context

1. From the Datasets start page, click From a Data Source.
2. Select SAP Cloud for Customer.
3. Select an existing connection, or select Create New Connection to create a new connection.
   Note

   Advanced features of customized OData data sources, such as SAP Cloud for Customer and SAP Business ByDesign Analytics, are only available using customized data source types. These features are not available using generic OData services. It is highly recommended to use the relevant customized data source types, if available, for your data service to leverage full capability of the data connector. It is possible to request a customized OData data source solution.

   For more information, see:

   • Import Data Connection to OData Services.
   • Import Data Connection to SAP Business ByDesign Analytics
   • Import Data Connection to SAP Cloud for Customer
4. Type a name for your query.
5. Select a table, and then select Next.

   Build your query by moving data elements into the Selected Data and Filters areas. For more information, see Building a Query.

6. Select Create.

   The data appears in the data integration view, where you can complete the mapping of your new data to the dataset's dimension.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

SAP Cloud for Customer Analytics

Context

1. From the Datasets start page, click From a Data Source.
2. Select SAP Cloud for Customer Analytics.
3. Select an existing connection, or select Create New Connection to create a new connection.
   Note

   Advanced features of customized OData data sources, such as SAP Cloud for Customer and SAP Business ByDesign Analytics, are only available using customized data source types. These features are not available using generic OData services. It is highly recommended to use the relevant customized data source types, if available, for your data service to leverage full capability of the data connector. It is possible to request a customized OData data source solution.

   For more information, see:

   • Import Data Connection to OData Services.
   • Import Data Connection to SAP Business ByDesign Analytics
   • Import Data Connection to SAP Cloud for Customer
4. Type a name for your query.
5. Select a table, and then select Next.

   Build your query by moving data elements into the Selected Data and Filters areas. For more information, see Building a Query.

6. Select Create.

   The data appears in the data integration view, where you can complete the mapping of your new data to the dataset's dimensions.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

SAP HANA

Prequisites

• The HANA database must first be set up by the system administrator. The HANA views in the database (analytic or calculation-type views) are available to create new models and datasets from.
• You need to install the SAP Analytics Cloud agent, with location ID. This location ID is configured through the SAPCP Cloud Connector, and the agent needs to be allowlisted there. For more information, see:
  • SAP Analytics Cloud Agent
  • Installing the SAPCP Cloud Connector

1. From the Datasets start page, click From a Data Source.
2. Select SAP HANA.
3. Choose an existing connection, or create a new connection.

   For information on creating an SAP HANA connection, see Import Data Connection to SAP HANA

4. Type a name for your query.
5. Choose whether you want to build a query, or type in a query manually.
   1. If you want to type in a query manually:
      1. Select Freehand Query.
      2. Type in your query.
      3. Select Next.
   2. Or, to build a query from scratch:
      1. Select Build a Query.
      2. Select a HANA view from the list, or search for a view by name, and then select Next.
         Restriction

         • Calculation views without a data category are not available.
         • Users need to have read access to the view (to execute a SELECT on the run-time object; for example, grant select on schema Schema123 to User123), and an appropriate analytic privilege (for example, _SYS_BI_CP_ALL).
      3. If the data source contains prompts, select values for them.
      4. Drag dimensions and measures into the Selected Data and Filters areas to build your query. Note that you can filter dimensions but not measures.

         See Building a Query for more information.

      5. Select Create when you're done.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

If your HANA data contains location information, you will need to add location dimensions. For more information on live HANA data see Creating Geo Spatial Models from HANA Calculation Views. For information on acquired HANA data see Creating a Model with Coordinate or Area Data for Geospatial Analysis.

Related Information

Import Data Connection to SAP HANA

Models Based on Live Data Connection to HANA Views

SAP Integrated Business Planning

1. From the Datasets start page, click From a Data Source.
2. Select SAP Integrated Business Planning.
3. Select an existing connection, or select Create New Connection to create a new connection.

   If you create a new connection, you can select the following options:

   • Connect to an On-Premise OData service
     Note

     To connect to an On-Premise OData service, ensure that the following tasks are completed:

     1. The cloud connector is installed. For more information, see Installing the SAPCP Cloud Connector.
     2. The cloud connector is configured. For more information, see Configuring the SAPCP Cloud Connector.

     The SAP Analytics Cloud agent doesn't need to be installed during the configuration process.

   For more information, see: Import Data Connection to SAP Integrated Business Planning.

4. Type a name for your query.
5. Choose whether you want to build a query, or type in a query manually.
   1. Select Build a Query to build a query using the query builder, or Freehand Query to manually type a query using V2 OData query syntax.
   2. If you chose to use the query builder, select a table, and then select Next.

      Build your query by moving data elements into the Selected Data and Filters areas. For more information, see Building a Query.

   3. If you selected the Freehand Query option, type a query in the box and select OK

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

SAP S/4HANA

Prerequisites

You are using a supported version of SAP S/4HANA. For more information, see System Requirements and Technical Prerequisites.

Context

1. From the Datasets start page, click From a Data Source.
2. Select SAP S/4HANA.
3. Select your S/4HANA connection or select Create New Connection to create a new connection. For more information, see Import Data Connection to SAP S/4HANA
4. Type a name for your query.
5. Select a table, and then select Next.

   Build your query by moving data elements into the Selected Data and Filters areas. For more information, see Building a Query.

6. Select Create.

   The data appears in the data integration view, where you can complete the mapping of your new data to the dataset's dimensions.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

SAP SuccessFactors

Context

For more information on the data you can access, see the SAP SuccessFactors HCM Suite OData API: Reference Guide in the SuccessFactors Product Page.

1. From the Datasets start page, click From a Data Source.
2. Select SAP SuccessFactors.
3. Select an existing connection, or select Create New Connection to create a new connection.

   For more information, see Import Data Connection to SAP SuccessFactors.

4. Type a name for your query.
5. Select Build a Query or Start from a Template Query, select a table or a template query to build your query, and select Next. For more information, see Building a Query.
   • The Build a Query option lets you build a query from a table by moving data elements into the Selected Data and Filters areas.
   • The Start from a Template Query option automatically selects the table and data elements to be included in the Selected Data and Filters areas based on the template query. For example, you can select the Headcount template query to build your query; you can then add or remove elements to finish building your own query.
   • Select (Refresh list) to display the list of reports from SuccessFactors in real time. Note: The list of templates cannot be refreshed.
6. Select Create.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

SAP Universe

Prerequisites

• The cloud connector and SAP Analytics Cloud agent are installed and configured.
• You use a supported version of SAP BusinessObjects Business Intelligence platform. For information on supported versions, see System Requirements and Technical Prerequisites.

1. From the Datasets start page, click From a Data Source.
2. Select SAP Universe.
3. Choose an existing universe connection, or create a new connection.
4. Type a name for your query.
5. Create a new query.
   1. Select a universe from the list, or search for a universe by name, and then select Next.
   2. Select data objects from the universe tree, and drag or double-click to add them to the Selected Data or Filters area.
   3. Select Create.
   4. If the universe contains prompts, select values for them.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

Related Information

Import Data Connection to an SAP Universe

Selecting the Right Model for Your Data

Installing the SAPCP Cloud Connector

Google BigQuery

Context

1. From the Datasets start page, click From a Data Source.
2. Select Google BigQuery.
3. Choose an existing BigQuery connection, or create a new one.
4. Type a name for your query.
5. Choose whether you want to build a query or type in Freehand SQL.

   If you want to build a query, select a table from the list. If you want to use Freehand SQL, type your query in the Query box. Note: BigQuery standard SQL is supported, but legacy SQL is not.

6. Select Next.

   If you're building a query, drag objects from the Available Data list to the Selected Data and Filters boxes.

7. Select Create to begin importing data.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

Google Drive

Context

When you connect to Google Drive, you can import any of the following file formats: Google Sheets, comma-separated-values text files (csv), and Microsoft Excel files (xlsx).

1. From the Datasets start page, click From a Data Source.
2. Select Google Drive.
3. Select a Google account, or create a new Google connection, or type or paste the URL for your file directly into the dialog. For your first access to Google Drive, you'll need to sign in to your Google Drive account.
4. Choose a file from the list, and select Next.
5. If you're importing from Google Sheets, and there are multiple sheets, select the sheet you want to import, and select Next.
6. If you're importing a .csv file, select which delimiter is used in the file, or select Auto-detect.
7. Select OK to begin the import procedure.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

OData Services

Qualtrics Data

Context

Best practices for consuming Qualtrics surveys

SAP Analytics Cloud can access data that has been provided through responses to a Qualtrics survey. When you're creating a model or dataset, it will be helpful if you have access to that survey in Qualtrics, so that you understand the structure of the survey.

Here are some suggestions to help you get the best out of analytics on Qualtrics surveys.

Note that questions in the Thrash section of your survey shouldn't be used when creating your model or dataset.

During the data preparation step while creating a model or dataset, the following basic initial steps will help in getting better insights:

1. Add a count column in SAP Analytics Cloud.

   Create a calculated column with the value 1 in each row, to count your responses.

2. The first row appears with descriptions of questions. This row should be removed.

   Why is this row provided at all?

   If you don't have access to the Qualtrics survey while creating the model or dataset, since the column headers provide the question numbers, the description information will help you decide which questions are relevant for analysis.

3. Create a Geo dimension.

   Use the columns LocationLatitude and LocationLongitude to create your Geo dimension. Video: Enrich Geographic Dimensions

Modeling different types of questions

Qualtrics offers several options for question types that may be included in a survey. Here are suggestions for handling some question types in SAP Analytics Cloud.

Net Promoter Score (NPS)

For a question of type NPS, for example Q1, Qualtrics generates a field Q1_NPS_GROUP with the value Promoter or 3, Detractor or 1, or Passive or 2 for each response. To calculate an aggregated NPS in SAP Analytics Cloud, calculate the counts of Promoters and Detractors, and calculate the NPS as: (Promotercount/count - Detractorcount/count)*100.

Matrix Table

For a question of type Matrix Table, columns are generated based on the number of statements. For example, if Q2 has 3 statements, then the generated columns are Q2_1, Q2_2, and Q2_3, and the descriptions are available as mentioned in basic step 2 above. You can create a model or dataset for each matrix question, by selecting the columns ResponseID and Q2_1 to Q2_3, doing an unpivot on the Q2 columns, and then adding a count as mentioned in basic step 1 above.

Link this dataset to the other dataset for this survey through ResponseID.

A similar approach can be followed for the Rank Order question type.

Text Entry

For these questions, if there is a TextIQ license, then performing text analysis generates some sentiment related fields. It is useful to have calculated measures based on the Sentiment score field, which can have Positive, Negative, or Neutral values.

Highlight

Avoid importing this question type during model creation because it generates a large number of columns, which could exceed the maximum number of columns supported by SAP Analytics Cloud. For these kinds of questions, use datasets instead.

Adding descriptions for dimension names and dimension members

The question numbers are available as dimension names. To have dimensions look more meaningful, add a description for each dimension in the model or dataset, based on your Qualtrics survey. For dimension members, the code values are provided. You'll need to provide descriptions based on the Qualtrics survey. You can get all of this information using the “Export Survey to Word” option in Qualtrics.

Limitations

• CSV export size limit = 1.8 GB: Currently, response exports that exceed 1.8 GB will fail. To prevent your export from failing, use limits and filters to limit the size of your final export file.
• Each API token can run a maximum of 15 jobs per minute.

1. From the Datasets start page, click From a Data Source.
2. Select Qualtrics.
3. Select your connection or select Create New Connection.

   For more information, see Import Data Connection to Qualtrics.

4. Type a name for your query.
5. Select a table, and then select Next.

   Build your query by moving data elements into the Selected Data and Filters areas. For more information, see Building a Query.

6. Select Create.

   The data appears in the data integration view, where you can complete the mapping of your new data to the dataset's dimensions.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

SQL Databases

Prerequisites

• The SAPCP Cloud Connector and SAP Analytics Cloud agent are installed and configured.
• You have installed a JDBC driver. For details, see Import Data Connection to an SQL Database.

1. From the Datasets start page, click From a Data Source.
2. Select SQL Databases.
3. In the Create Dataset dialog, do the following:
   1. Choose a connection location.
   2. Choose a connection type.
   3. Choose an existing connection, or create a new connection.
4. To create a new query, follow these steps:
   1. Select Next.
   2. Select Freehand SQL if you want to manually type an SQL statement, which adds a new card representing that query to the linking page.
   3. Drag and drop one or more tables from the list, or search for tables by name or description. When you add tables with matching primary keys, a join is created.
   4. If two or more tables are joined, you can select the (Inner Join) icon to change the type of join to one of the following options:
      • Intersecting data only (Inner Join) – This is the default option.
      • All primary data (Left Outer Join)
      • All data (Full Outer Join)
      • Remove matched data (Exception) – This option removes rows from the left-hand side table that have a match in the right-hand side table.
   5. Select Preview to review the data; if two or more tables are joined, the Joined Table Quality chart will show the number of accepted rows from the first table as well as matched values, duplicated values, and omitted values from other joined tables. The values are displayed on the next page.
   6. Select Hide Preview.
   7. Select View SQL to show the SQL query generated for joined tables. You can then select Save as Freehand SQL Query to create a freehand SQL query based on the content of the View SQL dialog.
      Note

      When creating a Freehand SQL query, if the query contains parameters that are shown with question marks, the Freehand SQL query cannot be edited.
   8. Select Next to build your query.
   9. Type a name and description for your query and select Done. You can continue to work on other tasks while the data is being uploaded in the background.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

Related Information

Import Data Connection to an SQL Database

Selecting the Right Model for Your Data

Installing the SAPCP Cloud Connector

SAP Analytics Cloud Agent

Open Connectors Cloud Storage Data Sources

Prerequisites

1. From the Datasets start page, click From a Data Source.
2. Choose an Open Connectors cloud storage data source, such as Dropbox or Microsoft OneDrive.
3. Select an existing connection, or select Create New Connection to create a new connection.

   For more information, see Import Data Connection to SAP Cloud Platform Open Connectors Cloud Storage Data Sources.

4. Choose a file from the list, or search for a file, and select Next.
   Note

   Only files that have been loaded will appear in the search results. To see more results, open more folders.
   Note

   If you're importing an Excel workbook containing multiple sheets, the first sheet is automatically imported.
5. Specify whether the first row of data contains column headers.
6. If you're importing a .csv file, select which delimiter is used in the file, or select Auto-detect.
7. Select Import to begin the import procedure.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.

Open Connectors Query-Based Data Sources

Prerequisites

1. From the Datasets start page, click From a Data Source.
2. Choose an Open Connectors query-based data source, such as Microsoft Dynamics CRM or Netsuite CRM 2018 Release 1.
3. Select an existing connection, or select Create New Connection to create a new connection.

   For more information, see Import Data Connection to SAP Cloud Platform Open Connectors Query-Based Data Sources.

4. Type a name for your query.
5. Select a table, and then select Next.

   Build your query by moving data elements into the Selected Data and Filters areas. For more information, see Building a Query.

6. Select Create.

   The data appears in the data integration view, where you can complete the mapping of your new data to the dataset's dimensions.

Next Steps

After importing the raw data, continue with data preparation before completing your dataset: About Adding Data to a Story.