Create Standalone (Public) Datasets: Data Sources Reference

This section acts as a reference for all procedures to create datasets for all supported data sources.

Personal 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 CloudInformation published on SAP site. 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:

  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 SAP Business Technology Platform (BTP) 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 Information published on SAP site 2408693 Information published on SAP site.
  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 Cloud Connector

SAP Analytics Cloud Agent

SAP Cloud for Customers

Context

Note
SAP Cloud for Customer was formerly named SAP Hybris Cloud for Customer.
  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:

  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 Customers Analytics

Context

Note
SAP Cloud for Customer was formerly named SAP Hybris Cloud for Customer.
  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:

  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

Note
This help topic refers to acquiring SAP HANA data into an SAP Analytics Cloud dataset. For Smart Predict, you can create a live SAP HANA dataset (the data is not acquired; it remains in an SAP HANA table). For details, see: Connecting to Live Data in SAP HANA - Overview.
Note

Also for Smart Predict, you cannot consume the live dataset generated with predictions directly in a story. You need to create a calculation view and then create an SAP Analytics Cloud model on top of this calculation view. Then you can use this model in a story. For details, see Creating Calculation Views to Consume Live Output Datasets and Using Your Live Generated Dataset in an SAP Analytics Cloud Model.

  • 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 Cloud Connector, and the agent needs to be allowlisted there. For more information, see:
  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're creating a new connection, check out Import Data Connection to SAP Integrated Business Planning.

    • 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 Cloud Connector.
      2. The cloud connector is configured. For more information, see Configuring the 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 S4/HANA

Prerequisites

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

Context

Note
  • SAP Analytics Cloud supports OData Version 4.0. Logical Operators (such as Equal, Not Equal, Less or Equal, Greater than, Greater than or Equal, Less than, Less than or equal, Logical and, Logical or, Startswith, and substringof) are supported for S/4HANA. Not logical negation, arithmetic operators, or functions are not supported. The following table lists the minimum requirements for S/4HANA OData services:

    Data Type

    Operator

    Format

    String (Edm.String)

    "eq", "ne", "startswith", "toLower","substringof", "gt", "ge", "lt", "le", "eq", "ne"

    "eq", "ne", "startswith", "toLower","substringof", "gt", "ge", "lt", "le", "eq", "ne"

    Number (Edm.Decimal)

    "gt", "ge", "lt", "le", "eq", "ne", "M"

    [value] m

    Number (Edm.Double)

    "gt", "ge", "lt", "le", "eq", "ne", "d"

    [value] d

    Number (Edm.Single)

    "gt", "ge", "lt", "le", "eq", "ne", "f"

    [value] f

    Number (Edm.Int16)

    "gt", "ge", "lt", "le", "eq", "ne"

    Number (Edm.Int32)

    "gt", "ge", "lt", "le", "eq", "ne"

    Number (Edm.Int64)

    "gt", "ge", "lt", "le", "eq", "ne", "L"

    [value] L

    Datetime (Edm.DataTime)

    "gt", "ge", "lt", "le", "eq", "ne"

    Datetime (Edm. DataTimeOffset)

    "gt", "ge", "lt", "le", "eq", "ne"

    Boolean (Edm.Boolean)

    "eq", "ne"

    True|False

    Edm.Guid

    "eq", "ne"

    guid'[value]'

    Logical Operator

    "and", "or"

  • Embedded Complex types are not supported.
  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.
Note

When creating a dataset based on data from an SAP univese, data is acquired from the database and retrieved to SAP Analytics cloud. The acquired data can be altered by the succession of serialization processes or business logic of the semantic layer.

The table below details the mapping strategy between the different layers involved:

Data Foundation Business Layer SAP Analytics Cloud Data Loss
BIGINT Numeric Number No
BINARY Blob N/A  
BIT Numeric Boolean No
CHAR String String No
DATE Date Date No
DECIMAL Numeric Numeric No
DOUBLE Numeric Numeric No
FLOAT Numeric Numeric No
INTEGER Numeric Integer No
LONGVARBINARY Blob N/A  
LONGVARCHAR Long Text String No
NUMERIC Numeric Numeric No
    High Precision Numeric Yes
REAL Numeric Numeric No
REF_CURSOR   N/A No
SMALLINT Numeric Integer No
TIME Datetime Datetime No
TIMESTAMP Datetime Datetime No
TINYINT Numeric Integer No
UNDEFINED   N/A  
VARBINARY Blob N/A No
VARCHAR String String No
XML   N/A  

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 Cloud Connector

Google Big Query

Context

Note
If your SAP Analytics Cloud system is hosted on a data center located within China, this feature is not available.
Note
The login prompt for Google BigQuery is displayed in a popup dialog. You'll need to disable the popup blocker in your browser before trying to connect.
  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

Note
If your SAP Analytics Cloud system is hosted on a data center located within China, this feature is not available.

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).

Note
The login prompt for Google Drive is displayed in a popup dialog. You'll need to disable the popup blocker in your browser before trying to connect.
Note
You can also import files from Google Drive in stories when adding data to a new story.
  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.
The data is imported from Google Drive, and is displayed in the data integration view.
Note
You are still signed in to Google. When you are finished using SAP Analytics Cloud, it is recommended to sign out of your Google account. To sign out, you can select Sign Out in the Select Google Drive File dialog, or sign out of Google in your browser

Next Steps

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

OData Services

Context
Note
  • SAP Analytics Cloud supports OData Version 4.0. Logical Operators (such as Equal, Not Equal, Greater than, Greater than or equal, Less than, Less than or equal, Logical and, Logical or) are supported. Not logical negation, arithmetic operators, or functions are not supported.
    The following table shows which operators need to be supported for each data type, for a generic OData service to integrate with SAP Analytics Cloud:

    Data Type

    Operator

    Format

    String (Edm.String)

    "eq", "ne", "startswith", "toLower"

    "eq", "ne", "startswith", "toLower";

    Number (Edm.Decimal)

    "gt", "ge", "lt", "le", "eq", "ne", "M"

    [value] m

    Number (Edm.Double)

    "gt", "ge", "lt", "le", "eq", "ne", "d"

    [value] d

    Number (Edm.Single)

    "gt", "ge", "lt", "le", "eq", "ne", "f"

    [value] f

    Number (Edm.Int16)

    "gt", "ge", "lt", "le", "eq", "ne"

    Number (Edm.Int32)

    "gt", "ge", "lt", "le", "eq", "ne"

    Number (Edm.Int64)

    "gt", "ge", "lt", "le", "eq", "ne", "L"

    [value] L

    Datetime (Edm.DataTime)

    "gt", "ge", "lt", "le", "eq", "ne"

    Datetime (Edm. DataTimeOffset)

    "gt", "ge", "lt", "le", "eq", "ne"

    Boolean (Edm.Boolean)

    "eq", "ne"

    True|False

    Edm.Guid

    "eq", "ne"

    guid'[value]'

    Logical Operator

    "and", "or"

  • To integrate with SAP Analytics Cloud your OData service must support these query parameters and paging capabilities:

    OData Service Support

    Requirements

    Query parameters
    • $select: Filters properties (columns). Lets you request a limited set of properties for each entity.
    • $filter: Filters results (rows). Lets you filter a collection of resources that are addressed by a request URL.
    • $expand: Retrieves related resources. Specifies the related resources to be included with retrieved resources.
    • $skip: Specifies the number of items in the queried collection that are to be skipped and not included in the result.
    • $top: Sets the page size of the results. Specifies the number of items in the queried collection to be included in the result.
    • $orderby: Orders the results. Lets you request resources in either ascending or descending order using asc and desc.
    • $inlinecount: OData V2 only. Specifies that the response to the request includes a count of the number of Entries in the Collection of Entries identified by the Resource Path section of the URI.
    • $count: OData V4 only. Lets you request a count of the matching resources included with the resources in the response.
    Paging capabilities
    • Support paging based on $top and $skip.
    • Should have the ability to return 1000 data rows per request, but if it doesn't, it must implement the “nextLink” function.
    • The order of the data rows in the data source needs to be fixed during data acquisition (for example, by sorting on a column). Otherwise, when paging is done, there could be data correctness issues. For example, the same data row could appear more than once in different pages.
    • The order of data rows should be guaranteed on data source during paging by $skip and $top.
  • If you want to use the query builder in the step below when you create a new query, the data service must support the select system query option. Example: https://services.odata.org/OData/OData.svc/Products?$select=Price,Name.

    Key-as-Segment isn't supported by the query builder, and should only be used with freehand queries.

    Also, the $skip parameter must be supported by the data service.

  • Embedded Complex types are not supported.
  1. From the Datasets start page, click From a Data Source.
  2. Select OData Services.
  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 Cloud Connector.
      2. The Cloud Connector is configured. For more information, see Configuring the Cloud Connector.

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

    • Connect to an SAP OData service When you select this option, specific SAP metadata is respected. This metadata specifies default behaviors based on SAP OData services guidelines. For more information, see SAP Annotations for OData Version 2.0Information published on SAP site.
    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.
  4. Type a name for your query.
  5. Chooose 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.
      Note
      When using freehand queries, the columns (including expanded entity columns) need to be specified, otherwise they won't be picked up in SAP Analytics Cloud.

      For example, the following queries let you get the product by rating, or find a nearby airport:

      OData V2 syntax:

      KeyPredicate:

      Categories(1)/Products?$format=json&$select=Name,Category/Name,Supplier/Name,Supplier/Address&$expand=Category,Supplier&$filter=Supplier/ID eq 0

      FunctionImport:

      GetProductsByRating?rating=3&$format=json&$select=Name,Rating,Category/Name,Supplier/Name,Supplier/Address&$expand=Category,Supplier

      OData V4 syntax using the expand parameter:

      KeyPredicate:

      Products?$select=ID,Name,Description,ReleaseDate,DiscontinuedDate,Rating,Price&$expand=ProductDetail($select=ProductID),ProductDetail($select=Details)

      FunctionImport:

      GetNearestAirport(lat=80, lon=90)

      For more information on the OData query syntax, refer to the OData documentationInformation published on non-SAP site.

      SAP Analytics Cloud has the following validation rules for freehand queries:
      • Duplicated parameters ($select, $expand, $format, $top, $skip, $inlinecount, $filter) in the query are not allowed.
      • Only entity set and function import are supported.
      • For function import, entity set is only supported as a return type.
      • If $select contains the Nav property but without $expand property, the query is invalid.

    Next Steps

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

Qualtrics

Context

Note
If your SAP Analytics Cloud system is hosted on a data center located within China, this feature is not available.

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.

Restrictions

  • 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 SAP Business Technology Platform (BTP) 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 Cloud Connector

SAP Analytics Cloud Agent

Open Connectors Cloud Storage Data Sources

Prerequisites

Note
If your SAP Analytics Cloud system is hosted on a data center located within China, this feature is not available.
  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 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

Note
If your SAP Analytics Cloud system is hosted on a data center located within China, this feature is not available.
  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 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.