The software provides the following categories of data sources to connect to your components:
• The embedded spreadsheet
This is best suited for small- to medium-sized business users who have most, if not all, of their business data in Excel spreadsheets. The embedded spreadsheet is available in all versions of Dashboards.
• External data connections
These are best suited for small- to medium-sized business users who need a dynamic data source;
these connections provide up-to-date data every time the model runs. Many of these connections are available in all versions of Dashboards - see the individual sections for more information.
• BEx and universe queries
These are best suited for businesses running the BI platform. Queries are used to select specific result objects from the chosen data source, and, like external data connections, provide up-to-date data every time the model runs. However, you can link components to queries directly, without inserting data into the embedded spreadsheet. Queries are only available in SAP BusinessObjects Dashboards.
Related Topics
•Understanding the embedded spreadsheet
•Managing data connections
•To browse queries
6.1 Understanding the embedded spreadsheet
The embedded spreadsheet is a functional Excel spreadsheet that you can work with in the same way as you would in Excel. To associate your models with specific data, you can link components to cells in the embedded spreadsheet.
Note:
The embedded spreadsheet does not support all Excel functions. It does not support macros, conditional formatting, linking to other external spreadsheets, and some formula functions. For a full list of supported functions, seeSupported Excel functions.
The embedded spreadsheet includes the Excel toolbars. If you use Excel 2003, the embedded spreadsheet shows your default toolbars. Buttons that are not related to working with data are disabled:
the Save, Open, and Print options are handled by Dashboards and are disabled on the embedded spreadsheet toolbar. If you use Excel 2007, the embedded spreadsheet displays the ribbon that appears at the top of the spreadsheet in. Any buttons or menus not related to working with data are disabled.
When saved locally, Dashboards uses the regional formatting settings defined on your computer, such as the settings for numerical separators, time separator, and date separator. To change the regional formatting settings, open the "Windows Control Panel", double-click Regional and Language Options, adjust the settings, and click OK.
In SAP BusinessObjects Dashboards, if you save a model to the BI platform, the regional formatting settings are determined by the users' viewing locale. For more information about using regional formatting settings with the BI platform, seeTranslating and globalizing models on the BI platform.
While you are working on your model, you can display the embedded spreadsheet below the canvas.
This arrangement allows you to see the data and bind components' input and output to cells on the spreadsheet. The embedded spreadsheet is completely independent from Excel: you can add, modify, and delete data directly within the embedded spreadsheet without having to import or re-import from Excel.
To insert data into the embedded spreadsheet, you can:
• Import a spreadsheet from Excel.
• Copy and paste data from another spreadsheet to the embedded spreadsheet.
• Manually enter data into the embedded spreadsheet.
• Link a query's result objects to cells in the embedded spreadsheet. This functionality is only available in Dashboards.
6.1.1 Preparing Excel spreadsheets
Before you import an Excel spreadsheet, make sure the spreadsheet provides a good structure for achieving the best performance for models. Consider the following guidelines:
• Use only supported Excel functions
For a list of supported Excel functions, seeSupported Excel functions.
• Use computationally intensive functions with small data sets only
While the following Excel functions are supported in Dashboards, they can negatively affect the model's performance if used with larger data sets (over 100 rows):
• SUMIF
• COUNTIF
• HLOOKUP
• VLOOKUP
• Organize your data in a logical fashion
To make your spreadsheet easier to understand, lay out your data in a logical fashion. Group related items together and use colors, labels, and borders to describe the structure and function of cells.
• Use colors, labels, and borders to identify data types (input and output)
To make it easier to maintain your visual models, use colors, labels and borders to identify cells or ranges of cells in the spreadsheet and to describe their use. Adding a legend that describes what the colors represent helps maintain consistency and usability of the model.
• Place frequently used data and logic at the top of the spreadsheet
To make it easier to select the data you want to bind to components and minimize the amount of scrolling required, place frequently used or common data and logic at the top-left of the tab in your spreadsheet.
• Use multiple tabs
If your spreadsheet becomes large and requires a lot of scrolling to see your logic or data, consider separating data into multiple tabs (or worksheets).
6.1.2 To set preferences for the embedded spreadsheet
1. Click File > Preferences.
2. In the "Preferences" dialog box, from the list on the left, click Excel Options.
3. In the "Excel Options" area, set the following options:
Description Option
Allows you to work with Live Office enabled spreadsheets within Dash-boards. Enabling this option may affect the performance of other Microsoft Office programs. If this option is not enabled, you must set up and work with Live Office in a spreadsheet outside of Dashboards and then import the spreadsheet into Dashboards.
Live Office is only available in SAP BusinessObjects Dashboards.
Live Office Compatibil-ity
Use this option to set the maximum number of rows in the embedded spreadsheet that can be selected and bound to components.
The software supports an unlimited number of rows in a range selection;
however, linking components to large range selections can affect the performance of models. By default, you can select a maximum of 512 rows in a range.
Maximum Number Of Rows
Use this option to optimize the spreadsheet for runtime. When this option is enabled, the software calculates formulas in the model when you export the model and saves them with the SWF. It takes longer to generate the SWF, but since the calculations are already complete, the file performs better at runtime.
Optimize Spreadsheet
Use this option to avoid errors appearing in models because of formula errors in the spreadsheet. When selected, any cell that contains a formula error is treated as a blank cell.
Note:
If formula errors are ignored, functions that refer to cells with errors, such as ISERROR, will not work correctly in the embedded spreadsheet.
Ignore Excel Formula Errors
4. Click OK.
6.1.3 Synchronizing data between Excel and the embedded spreadsheet
The embedded spreadsheet is not connected to the source data imported from an Excel spreadsheet:
changes made to the data in one location are not automatically updated in the other location. If you want to maintain synchronized copies of the data in the embedded spreadsheet and Excel, you can export the modified data back to Excel or copy and paste the changed cells to the source spreadsheet.