Data sources and models

Before commencing your analytical tasks in SAP Analytics Cloud, you need to create models. A model is the foundation of every analysis you create to evaluate the performance of your organization. It is a high-level design that exposes the analytic requirements of end users. Planning and analytics are the two types of models you can create in SAP Analytics Cloud.

Analytics models are simpler and more flexible, while planning models are full-featured models in which you work with planning features. Preconfigured with dimensions for time and categories, planning models support multicurrency and security features at both model and dimension levels. Chapters 2 and 3 of this book are dedicated to these two models.

To determine what content to include in your model, you must first identify the columns from the source data on which users need to query. The columns you need in your model reside in some sort of data source. SAP Analytics Cloud supports three types of data sources: files (such as CSV or Excel files) that usually reside on your computer, live data connections from a connected remote system, and cloud apps.

In addition to the files on your computer, you can use on-premise data sources, such as SAP Business Warehouse, SAP ERP, SAP Universe, SQL database, and more, to acquire data for your models. In the cloud, you can get data from apps such as Concur, Google Drive, SAP Business ByDesign, SAP Hybris Cloud, OData Services, and Success Factors. The following figure depicts these data sources. The cloud app data sources you can use with SAP Analytics Cloud are displayed above the firewall mark, while those in your local network are shown under the firewall.

As you can see in the following figure, there are over twenty data sources currently supported by SAP Analytics Cloud. The methods of connecting to these data sources also vary from each other. Due to space constraint, it is not possible to outline every connecting method in this chapter. However, some instances provided in this chapter would give you an idea on how connections are established to acquire data. The connection methods provided here relate to on-premise and cloud app data sources. Considering the audience of this book who may not have access to these data sources, and to keep things simple, Chapter 2, Models in SAP Analytics Cloud, will guide them to create an analytics model in the trial version of SAP Analytics Cloud using an Excel file provided with this book.

Due to relevance with the topic, the sub-sections that follow are added here to provide an overview of different types of connections you can establish in SAP Analytics Cloud to build your models.

Create a direct live connection to SAP HANA

Execute the following steps to connect to the on-premise SAP HANA system to use live data in SAP Analytics Cloud. Live data means that you can get up-to-the-minute data when you open a story in SAP Analytics Cloud. In this case, any changes made to the data in the source system are reflected immediately. Usually, there are two ways to establish a connection to a data source--use the Connection option from the main menu, or specify the data source during the process of creating a model. However, live data connections must be established via the Connection menu option prior to creating the corresponding model. Here are the steps:

1. From the main menu, select Connection.
2. On the Connections page, click on the Add Connection icon (+), and select Live Data Connection | SAP HANA.
3. In the New Live Connection dialog, enter a name for the connection (for example, HANA).
4. From the Connection Type drop-down list, select Direct. The Direct option is used when you connect to a data source that resides inside your corporate network. The Path option requires a reverse proxy to the HANA XS server. The SAP Cloud Platform and Cloud options in this list are used when you are connecting to SAP cloud environments. When you select the Direct option, the System Type is set to HANA and the protocol is set to HTTPS.
5. Enter the host name and port number in respective text boxes.
6. The Authentication Method list contains two options: User Name and Password and SAML Single Sign On. The SAML Single Sign On option requires that the SAP HANA system is already configured to use SAML authentication. If not, choose the User Name and Password option and enter these credentials in relevant boxes.
7. Click on OK to finish the process. A new connection will appear on the Connection page, which can now be used as a data source for models. The creation of the model is discussed in the next chapter. However, in order to complete this exercise, we will go through a short demo of this process here.
8. From the main menu, go to Create | Model.
9. On the New Model page, select Use a datasource.
10. From the list that appears on your right side, select Live Data connection.
11. In the dialog that is displayed, select the HANA connection you created in the previous steps from the System list.

12. From the Data Source list, select the HANA view you want to work with. The list of views may be very long, and a search feature is available to help you locate the source you are looking for.
13. Finally, enter the name and the optional description for the new model, and click on OK. The model will be created, and its definitions will appear on another page. Refer to the next two chapters for further details on models.

Connecting remote systems to import data

In addition to creating live connections, you can also create connections that allow you to import data into SAP Analytics Cloud. In these types of connections that you make to access remote systems, data is imported (copied) to SAP Analytics Cloud. Any changes users make in the source data do not affect the imported data.

To establish connections with these remote systems, you need to install some additional components. For example, you must install SAP HANA Cloud connector to access SAP Business Planning and Consolidation (BPC) for Netweaver. Similarly, SAP Analytics Cloud agent should be installed for SAP Business Warehouse (BW), SQL Server, SAP ERP, and others. Take a look at the connection figure illustrated on a previous page.

The following set of steps provide instructions to connect to SAP ERP. You can either connect to this system from the Connection menu or establish the connection while creating a model. In these steps, we will adopt the latter approach.

1. From the main menu, go to Create | Model.
2. Click on the Use a datasource option on the choose how you'd like to start your model page.
3. From the list of available datasources to your right, select SAP ERP.
4. From the Connection Name list, select Create New Connection.
5. Enter a name for the connection (for example, ERP) in the Connection Name box. You can also provide a description to further elaborate the new connection.
6. For Server Type, select Application Server and enter values for System, System Number, Client ID, System ID, Language, User Name, and Password. Click the Create button after providing this information.

7. Next, you need to create a query based on the SAP ERP system data. Enter a name for the query, for example, sales.
8. In the same dialog, expand the ERP object where the data exists. Locate and select the object, and then choose the data columns you want to include in your model. You are provided with a preview of the data before importing. On the preview window, click on Done to start the import process. The imported data will appear on the Data Integration page, which is the initial screen in the model creation segment and is discussed in subsequent chapters.

Connect Google Drive to import data

You went through two scenarios in which you saw how data can be fetched. In the first scenario, you created a live connection to create a model on live data, while in the second one, you learned how to import data from remote systems. In this section, you will be guided to create a model using a cloud app called Google Drive. Google Drive is a file storage and synchronization service developed by Google. It allows users to store files in the cloud, synchronize files across devices, and share files. Here are the steps to use the data stored on Google Drive:

1. From the main menu, go to Create | Model.
2. On the choose how you'd like to start your model page, select Get data from an app.
3. From the available apps to your right, select Google Drive.
4. In the Import Model From Google Drive dialog, click on the Select Data button.
5. If you are not already logged into Google Drive, you will be prompted to log in.
6. Another dialog appears displaying a list of compatible files. Choose a file, and click on the Select button.
7. You are brought back to the Import Model From Google Drive dialog, where you have to enter a model name and an optional description.
8. After providing this information, click on the Import button. The import process will start, and after a while, you will see the Data Integration screen populated with the data from the selected Google Drive file.

Refreshing imported data

SAP Analytics Cloud allows you to refresh your imported data. With this option, you can reimport the data on demand to get the latest values. You can perform this refresh operation either manually or create an import schedule to refresh the data at a specific date and time or on a recurring basis. The following data sources support scheduling:

• SAP Business Planning and Consolidation (BPC)
• SAP Business Warehouse (BW)
• Concur
• OData services
• An SAP Analytics BI platform universe (UNX) query
• SAP ERP Central Component (SAP ECC)
• SuccessFactors HCM suite
• Excel and comma-separated values (CSV) files imported from a file server (not imported from your local machine)
• SQL databases

You can adopt the following method to access the schedule settings for a model:

1. Select Connection from the main menu. The Connection page appears. The Schedule Status tab on this page lists all updates and import jobs associated with any data source.
2. Alternatively, go to main menu | Browse | Models. The Models page appears. The updatable model on the list will have a number of data sources shown in the Datasources column. In the Datasources column, click on the View More link. The update and import jobs associated with this data source will appear. The Update Model and Import Data job are the two types of jobs that are run either immediately or on a schedule.
3. To run an Import Data job immediately, choose Import Data in the Action column. If you want to run an Update Model job, select a job to open it.

The following refreshing methods specify how you want existing data to be handled.

The Import Data jobs are listed here:

• Update: Selecting this option updates the existing data and adds new entries to the target model.

• Clean and Replace: Any existing data is wiped out and new entries are added to the target model.

• Append: Nothing is done with the existing data. Only new entries are added to the target model.

The Update Model jobs are listed here:

• Clean and Replace: This deletes the existing data and adds new entries to the target model.

• Append: This keeps the existing data as is and adds new entries to the target model.

The Schedule Settings option allows you to select one of the following schedule options:

• None: The import is performed immediately

• Once: The import is performed only once at a scheduled time

• Repeating: The import is executed according to a repeating pattern; you can select a start and end date and time as well as a recurrence pattern

4. After setting your preferences, click on the Save icon to save your scheduling settings.

If you chose the None option for scheduling, select Update Model or Import Data to run the update or import job now.

Once a scheduled job completes, its result appears on the Schedule Status tab displaying any errors or warnings. If you see such daunting messages, select the job to see the details. Expand an entry in the Refresh Manager panel to get more information about the scary stuff. If the import process rejected any rows in the dataset, you are provided with an option to download the rejected rows as a CSV file for offline examination. Fix the data in the source system, or fix the error in the downloaded CSV file and upload data from it.

After creating your models, you access them via the main menu | Browse | Models path. The Models page, as illustrated in the following figure, is the main interface where you manage your models.

16. All existing models are listed under the Models tab. You can open a model by clicking on its name.
17. Public dimensions are saved separately from models and appear on the Public Dimensions tab. When you create a new model or modify an existing model, you can add these public dimensions.
18. If you are using multiple currencies in your data, the exchange rates are maintained in separate tables. These are saved independently of any model and are listed on the Currency Conversion tab.
19. Data for geographic locations, which are displayed and used in your data analysis, is maintained on the Points of Interest tab.

The toolbar provided under the four tabs carries icons to perform common operations for managing models.

20. Click on the New Model icon to create a new model.
21. Select a model by placing a check mark (A) in front of it. Then click on the Copy Selected Model icon to make an exact copy of the selected model.
22. Use the delete icon to remove the selected models.
23. The Clear Selected Model option removes all the data from the selected model.

24. The list of data import options that are supported is available from a menu beneath the Import Data icon on the toolbar.
25. You can export a model to a .csv file once or on a recurring schedule using Export Model As File.