Popular Data sources and models in SAP Analytics Cloud

[box type="note" align="" class="" width=""]This article is an excerpt from a book written by Riaz Ahmed titled Learning SAP Analytics Cloud.This book deals with the basics of SAP Analytics Cloud (formerly known as SAP BusinessObjects Cloud) and unveil significant features for a beginner.[/box]

Our article provides a brief overview of the different data sources and models, available in SAP Analytics Cloud.

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 multi-currency and security features at both model and dimension levels.  

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. However, some instances provided in this article 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.

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
5. 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.
6. Enter the hostname and port number in respective text boxes.
7. 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.
8. 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. However, in order to complete this exercise, we will go through a short demo of this process here.
9. From the main menu, go to Create | Model.
10. On the New Model page, select Use a datasource.
11. From the list that appears on your right side, select Live Data connection.
12. In the dialog that is displayed, select the HANA connection you created in the previous steps from the System list.
13. 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.
14. 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.

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.

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 article, 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 re-import 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 [DC3] 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.

1. All existing models are listed under the Models tab. You can open a model by clicking on its name.
2. 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.
3. 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.
4. Data for geographic locations, which are displayed and used in your data analysis, is maintained on the Points of Interest tab.
5. The toolbar provided under the four tabs carries icons to perform common operations for managing models.
6. Click on the New Model icon to create a new model.
7. 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.
8. Use the delete icon to remove the selected models.
9. The Clear Selected Model option removes all the data from the selected model.
10. The list of data import options that are supported is available from a menu beneath the Import Data icon on the toolbar.
11. You can export a model to a .csv file once or on a recurring schedule using
12. Export Model As File.

SAP Analytics Cloud can help transform how you discover, plan, predict, collaborate, visualize, and extend all in one solution. In addition to on-premise data sources, you can fetch data from a variety of other cloud apps and even from Excel and text files to build your data models and then create stories based on these models.

If you enjoyed this excerpt, check out the book Learning SAP Analytics Cloud to know more about professional data analysis using different types of charts, tables, geo maps, and more with SAP Analytics Cloud.