3 Modeler
3.6 Data Import
You can populate models with data by importing data from files or other systems. It is also possible to directly create a model by importing from an appropriate data source.
The table below lists the available options for importing data. To import data from a flat text file, the data must be delimited (such as comma-separated values). Data in Microsoft Excel can be imported. If the data is saved on separate sheets in the Excel workbook, you can choose which sheet to import. Data files can be stored locally on your file system or network; direct import from Google Drive is also supported.
Integration of SAP Cloud for Analytics with a number of other systems is also possible. In the current release, direct connections to SAP HANA database views are supported, as is import of data from SAP Business Planning and Consolidation (BPC).
Import Option More Information
Import Model from File ● About Importing a Model from a File [page 42]
● Importing a Model from a File [page 45]
Import Data from File ● About Importing Data from Files [page 47]
● Importing Data from Files [page 49]
Import Model from BPC ● About Importing Data from Other Systems [page 50]
● About Importing a Model from BPC [page 51]
● Importing a Model from BPC [page 52]
Import Data from BPC ● Importing Data from BPC [page 55]
● About Importing Data from BPC [page 53]
Import from Google Drive Connecting to Google Drive [page 41]
Create Model from HANA Creating a Model from HANA [page 56]
Create Model from Remote System
3.6.1 Connecting to Google Drive
If your data files are stored on Google Drive, you can connect directly to Google and select a file from there.
Context
When you connect to Google Drive, you can import any of the following file formats: Google Sheets, comma- delimited text files (csv), and Microsoft Excel files (xlsx).
On the Modeler Import menu, two options are available to connect to Google Drive, but in both cases the connection procedure to retrieve either a data file or model data is the same: you use the Import Data option to add data to an existing model and the Import Model option to create a new model. After selecting the source file, the import action is the same as described for other similar import procedures.
This same option to import files from Google Drive is also available in Stories when adding data to a new story.
Note
You must have your Google Drive ID and password ready. The login prompt for Google Drive is displayed in a popup dialog, and it is therefore essential to disable the popup blocker in your browser before trying to connect.
Procedure
1. Select the option you require from the menu. This opens the Import from Google Drive dialog. 2. Select the Choose File button to open the Select Google Drive File dialog.
3. For your first access to Google Drive, select the Sign in link to open the login popup. Enter your ID and password and select the account you want to use. If you are already signed in and have already got the address of the target file on the clipboard, you can paste it directly into the dialog.
4. The content of the drive you selected is now displayed in the My Drive section of the dialog. Navigate to the file you want to import and choose the Select button.
5. Complete the other details in the Import from Google Drive dialog; that is, select the existing model or enter the name of the new model that will be created.
3.6.2 About Importing a Model from a File
You can import a model from a file of external data such as an Excel spreadsheet. The data columns in the source file are used to create dimensions in a new model.
The import process analyzes the source data and creates an initial data view with proposed dimensions for the new model. You must review this proposal and refine it by specifying the attribute type for each dimension and mapping the columns to appropriate dimensions in the new model. The workflow to import a model from a file is:
1. Select a source file and enter a name for the model that will be created
2. Start the import and automatic initial mapping process that creates the initial data view 3. Map the data in the data view to align the source data with the model
4. Start the automatic creation of the model and the initial report.
Source Data
The source data can be an Excel spreadsheet (.xlsx) or delimited text file (.csv or .txt). The source data must include columns which can be used to create dimensions in a new model and it must also include transactional data (referred to in Modeler as KeyFigure / SignedData). The transactional data is not visible in Modeler but it will be displayed in the report which is generated.
The data will typically include details for the main account for the model. You can identify which dimension is the main account during the import process but if no account data is found then an account dimension will be generated automatically for the model.
In addition to the main dimensions and transactional data the source can also include properties for dimensions, that is, text information such as descriptions, hierarchy information, or other details. Properties are linked to a specific parent dimension during the mapping process.
Import and Initial Mapping
The data is analyzed as it is imported to identify the unique records in each column and to determine the data types: in the initial analysis a basic distinction between numeric and text data is made. Columns containing text are typically identified as dimensions and numeric data is identified as either transactional data or time values.
Data View
The result of the initial import process is the DataView. In the following illustration we can see that some columns have already been identified as potential dimensions - they have been given a default name such as Account_5540 (a numeric suffix is added where necessary to make the name unique in the database).
The original column headings from the source data are shown beneath the new proposed names together with a symbol to show the data type which has been recognized (numeric, time and date, or text strings). You can edit the column headers but for some dimension types (such as Description type) the header is automatically updated.
The content of the data view and the success of the initial analysis and mapping is dependent on the quality of the source data, such as the column headings and the consistency of the data in each column. In this example, four columns have been provisionally identified as dimensions, one column has been recognized as transactional data and three columns (Currency, Time, Version) have been initially flagged as properties.
Mapping Process
The objective of the manual mapping process is to improve the quality of data and to align it with the
requirements of Modeler so that the complete new model and initial report can be generated. The screen display in the DataView is highly dynamic and proactive. Many features are built into this view to support the mapping process, these include:
● Suggested actions: depending on the currently selected element in the display a choice of editing options may be offered
● Highlighting of cells (red background) to indicate anomalous data which may be incorrect
● Automatic selecting of cells with matching values and automatic counting of values.
Additionally, many context-sensitive editing features are available: firstly from the context menu in the data area to correct or replace cell values, and also from the drop-down menu available in the column heading area. This menu provides features to manage the columns in the grid, sort and filter the data, convert the data type, or add additional columns of geolocation information for the model.
A history of all changes you make in the data view is maintained and displayed in the collapsible History panel. If necessary, you can roll back the changes you make either by clicking on entries in the history or using the Undo /
Redo buttons on the toolbar.
Details Panels
The collapsible blue Details panel on the right of the screen is available to apply settings to each selected column. This panel has two views:
● Data Level - shows general information about the import
● Column - shows information for the currently selected column and lists the attribute and dimension types which you can apply.
The Column panel is shown here with the content of the drop-down menus visible. The three main data types are shown color coded and the commentary that follows explains how these are handled.
1) Dimensions: Any column which must appear as a separate dimension in the model requires the attribute type ID plus a dimension type. The example shows the column / dimension 'Product' which has been assigned the dimension type 'Generic'.
Note
The date / time data column must also be set to attribute type ID with dimension type 'Time' to create a time dimension. This is required for planning-type models. When you have set up this column you can also set the Time Format by selecting a template from the drop down list. This will ensure that the data is correctly parsed as it is imported.
2) Properties: Columns containing descriptions, hierarchy information and other properties can be imported and added as additional columns on any selected dimension. In this case, you must also select the parent dimension where the column will appear (click the arrow symbol in the attribute type menu to open a new selection dialog). If you set a column to attribute type Description then the column heading is immediately updated with the word "Description".
3) Signed Data (transactional data): This data type requires the attribute type Key Figure and is automatically assigned to the dimension type SignedData (this data is not visible in Modeler and SignedData is not listed as a dimension type).
3.6.2.1
Importing a Model from a File
To import a model from a file, you select the file, give the new model a name, and then specify how incoming data should be mapped to model dimensions.
Prerequisites
You have a delimited text file (.csv, or .txt) or a Microsoft Excel (.xlsx) file that contains the transactional data and the master data that can be matched to the dimensions of the target model.
Procedure
1. In the main menu, choose Modeler.
2. On the Models page, choose the Import Data icon and select Import Model from File from the menu. 3. In the Import Model from File dialog, choose Select Source File.
4. Browse to the file you wish to import and choose Open.
The name of the file you selected is now displayed. If you need to select a different file you can remove the file by using the X icon and then select another.
5. If you are importing from an Excel workbook containing multiple sheets, select the Sheet you want to import. 6. In the Target Model section, enter a Model Name and a description (optional).
7. Select Import to begin the initial import of the source data. After the import completes the Data View is displayed.
8. In the Data Level panel, specify whether the model is Planning Enabled by selecting or deselecting the check box and indicate whether to use the first row of the data as headers by selecting the Use first row as header
checkbox.
9. Continue with the data mapping for the new model: use the Column panel to apply settings for each column in the Data View.
10. When the mapping is complete choose Create Model. A confirmation prompt is displayed before the creation process begins.
Results
3.6.2.2 Enriching Coordinate Data for Geospatial Analysis
Before you perform geospatial analysis in stories, you must first import coordinate data and enrich it in the
Modeler. This process creates a new column in the data view with an enriched format of latitude and longitude coordinates.
Prerequisites
You must have an Excel file (.xlsx) or CSV file with a location ID column that contains unique data, as well as latitude and longitude columns.
Context
These steps describe how to enrich coordinate data while creating a model from a file. You can also enrich coordinate data while uploading data to an existing model. For more information, see About Importing Data from Files [page 47].
Procedure
1. From the main menu, select Modeler.
2. In the Models page, select Import Model from File and choose a file to import. 3. Type the Model Name and Description, and select Import.
The data from the file appears in the Data Integration page.
4. Ensure that at least one numeric column has Key Figure as its attribute type.
5. Select the arrow next to a column containing latitude or longitude data and choose Geo Enrich By
Coordinate .
6. In the Geo Enrich Data dialog, specify the following information:
○ Geo Dimension: Type a name for the dimension that you will create using the geo enrich process.
○ Tooltip Text: Select the column that will provide the tooltip text for dimension members displayed in a geo map.
○ Parent Dimension: Select the dimension with unique location ID data that you want to map the new geo dimension to.
○ Latitude: Select the column that contains the latitude coordinate.
○ Longitude: Select the column that contains the longitude coordinate. 7. Select Create.
The Geo Code Results dialog appears, showing the coordinates that were successfully created in the Solved
8. To solve unmatched entries, select No Geo matches, and type new values for unmatched entries. To provide
more information, you can choose any columns that may contain geographical data from the Select
supplementary information list. 9. Select OK.
A new column with the attribute type ST-Point is created.
10. Finish the data integration and mapping process and in the Details panel, select Create Model.
Results
The model is created. When working with stories, the geo dimension will be available to add to geo maps.
Related Information
Analyzing Geographical Data [page 146] About Importing a Model from a File [page 42]
3.6.3 About Importing Data from Files
You can import data in a delimited (column) format to existing models. During the import process you can finalize the mapping of the source data to the dimensions in the model.
The import data process and the import model process are very similar. In both cases the data is imported, analyzed and mapped to the dimensions of a model. A special feature of importing data to an existing model is the option to align new data with existing version categories or to create new version categories during the import. For example, if the existing model contains actual values you can now import planning data by selecting the Planning category and entering a label for the new version data. Then, in the report that is generated from the model both the actual and the new planning columns will be visible.
The workflow to import data from a file into a model is:
1. Select a source file and select the name of the model that will be updated
2. Start the import and automatic initial mapping process that creates the data view 3. Map the data in the data view to align the source data with the model
4. Start the automatic update process.
Source Data
The source data can be an Excel spreadsheet (.xlsx) or delimited text file (.csv or .txt). The source data must include columns which can be matched to the existing dimensions in the selected model. It must also include transactional data (referred to in Modeler as KeyFigure / SignedData).
Import and Initial Mapping
After the initial import the data is displayed in the DataView grid. The status and success of the initial mapping is shown in each column header of the grid as a color-coded status bar indicating if the source column was mapped and what percentage of the imported data was accepted. If the status is not 100 percent green you can click the status bar to see details of which values have not been matched.
As with the Import Model from File procedure, the screen display in the DataView is highly dynamic, many editing features to support the mapping process are available from context menus and the drop-down menus in the column header area.
A history of all changes you make in the data view is maintained and displayed in the collapsible History panel. If necessary, you can roll back the changes you make either by clicking on entries in the history or using the Undo /
Redo buttons on the toolbar.
Details Panel: Data Level
The Data Level panel shows general details of the import process and is used to apply general settings to the import process as a whole.
An important setting here is the import method to determine how existing data is handled. A choice of three import methods is available:
Clean & Replace Deletes the existing data and adds new entries to the target model.
Replace Updates the existing data and adds new entries to the target model.
Append Keeps the existing data as is and adds new entries to the target model.
You can also choose how accounting data is handled in this import. Special data types for accounting data are available which automatically apply negative signs to values in the database as necessary although the data shown on screen is shown as a positive value (this applies to the types INC and LEQ - refer to the Account dimension type for more information). A checkbox is available on this panel to apply this feature to the transactional (signed) data as it is imported.
Details Panel: Column
The Column panel shows details for the currently selected data column. It shows the mapping status and also the mapping quality as well as frequency counts and summaries of all the data in the column.
For any column containing version information you can set which category of data (such as Actual or Planning) you are going to update in the model.
3.6.3.1
Importing Data from Files
You can import data saved in spreadsheets or text files to update existing models.
Prerequisites
To import data to an existing model you must have a delimited text file (.csv, or .txt) or a worksheet in a Microsoft Excel (.xlsx) file that contains the transactional data and the master data that can be matched to the dimensions of the target model.
Procedure
Import Data
1. In the main menu, choose Modeler.
2. On the Models page, choose the Import Data icon and select Import Data from File from the menu. 3. In the Import Data File dialog, choose Select Source File.
4. Browse to the file you wish to import and choose Open.