Working With Data Sources

To create data models, you need to import data from different data sources. You can use the following functions −

• Data connections
• Embedded spreadsheets
• BEX and Universe queries

Embedded Spreadsheets

In your data model, you can link your components to cells in the spreadsheet. It supports many features that are supported by Excel files. However, few of its functions like Macros, Conditional Formatting and Function Formulas, etc. are not supported.

You can insert data in the spreadsheets using different ways, some of which are −

• You can copy paste the data from other excel spreadsheets to the embedded spreadsheets.

• You can import a spreadsheet from excel.

• You can manually enter the data in a spreadsheet.

• You can link query objects from a BEX or a Universe query to cells in an embedded spreadsheet.

Before you create a data model using embedded spreadsheets, it is necessary for you to create a performance optimized structure. You can consider multiple points to ensure that the structure is good. Some of these points are −

• Use Excel functions with small data sets.

• Use only supported Excel functions.

• You can use color, labels and borders to find out cells or range in spreadsheet.

• In case you have a large volume of data in the spreadsheet, you can create multiple tabs.

How to Set Preference for Embedded Spreadsheets?

To set preference for Embedded Spreadsheets, go to File → Preferences as shown in the following screenshot.

On the left side, go to Excel Options and you can select the following options −

• Live Office Compatibility − You can work with Live Office enabled spreadsheet within dashboards.

• Maximum Number of Rows − Here you can select the maximum number of rows in a spreadsheet that can be bound to components.

• Optimize Spreadsheet − This is used to optimize the spreadsheet at run time. This allows to calculate the formulas and save them in SWF, hence the model performs better at run time.

• Ignore Excel Formula Errors − You can use this option to ignore the errors in the spreadsheet.

How to Import an Excel Spreadsheet?

To use an existing spreadsheet that will be used as a source for a new data model, you can import it to a new spreadsheet.

For this you will have to go to Data → Import as shown in the following screenshot.

In the dialog box which appears soon afterwards, click Yes as shown in the following screenshot.

Select the spreadsheet you want to import and click Open.

To Generate SWF Using an External Spreadsheet

To create the same model using the same spreadsheet structure but different values, the model can be exported using an external spreadsheet.

To do this, go to File → Export Settings as shown in the following screenshot.

Select Another Excel File → Go to folder and select the spreadsheet to use. Click OK.

Now go to File → Export → Flash (SWF).

This opens a Save As Dialog Box. Enter the path and name of SWF File → OK.