Working with data foundations

10.1 About data foundations

A data foundation contains a schema of relevant tables and joins from one or more relational databases that are used as a basis for one or more business layers.

You reference relational connections in the data foundation. You insert tables and joins from the databases referenced in the connections.

Using the Data Foundation Editor, you can enhance the data foundation by adding federated tables (designed in the federation layer), derived tables, alias tables, calculated columns, additional joins, contexts, prompts, and lists of values. The availability of some features depends on the data foundation type. See the related topic for more information about data foundation types.

You can build any number of business layers on the same data foundation. In this case the data foundation becomes the basis for multiple universes.

Related Information

About data foundation types [page 128]

About the federation layer [page 182]

How to build a data foundation [page 133]

10.1.1 About data foundation types

Single-source and multisource-enabled are two types of data foundations that allow you to take advantage of different data foundation features.

Related Information

About single-source data foundations [page 128]

About multisource-enabled data foundations [page 129]

10.1.2 About single-source data foundations

Single-source data foundations support a single connection. The connection can be local or secured, which means you can publish universes based on the data foundation either locally or to a repository.

Single-source data foundations support database-specific SQL syntax for derived tables, calculated columns, and join expressions. Database-specific SQL syntax allows functions or operators that are offered by a specific database and not by standard SQL-92 (for example, Oracle analytical functions). You must select single-source if you want to publish to a local folder the universes that are based on this data foundation.

Single-source data foundations are recommended for the following situations:

● You want to work exclusively with database-specific SQL syntax.

● You want to publish the universe locally and work outside of a repository.

Related Information

About multisource-enabled data foundations [page 129]

How to build a data foundation [page 133]

Changing a connection in a data foundation [page 138]

10.1.3 About multisource-enabled data foundations

Multisource-enabled data foundations support one or more connections. You can add connections when you create the data foundation and anytime later. Multisource-enabled data foundations only support secured connections, and universes based on this type of data foundation can only be published to a repository.

Multisource-enabled data foundations support most relational connections supported in single-source data foundations. In addition, multisource-enabled data foundations support the following relational connections that are not supported in single-source data foundations:

● SAP BW connections

● SAS connections

Connections for multisource-enabled data foundations are managed by the data federation service. For information about tuning the data federation service, see the Data Federation Administration Tool Guide.

The federation layer is available in multisource-enabled data foundations. It allows you to create federated tables that you can then include in the data foundation.

SQL-92 standard syntax is the default for calculated columns, derived tables, and join expressions. In addition, the SAP BusinessObjects SQL database functions are available. You can use database-specific SQL syntax in a multisource-enabled data foundation by defining a database-specific derived table or calculated column.

Database-specific SQL syntax allows functions or operators that are offered by a specific database and not by standard SQL-92 (for example, Oracle analytical functions).

Note

For database functions, the SAP BusinessObjects syntax can be different from the syntax of the same function provided by database-specific SQL.

Multisource-enabled data foundations are required in the following situations:

● You want to insert tables and joins from more than one relational data source, or create federated tables.

● You want to use SAP BW or SAS connections.

● You want to use SQL-92 standard syntax and SAP BusinessObjects SQL functions.

For more information on these situations, see the related topics.

Related Information

Data foundations with multiple connections [page 130]

About the federation layer [page 182]

SQL expressions in multisource-enabled data foundations [page 131]

About single-source data foundations [page 128]

How to build a data foundation [page 133]

Using SAP BW data sources [page 38]

10.1.3.1 Data foundations with multiple connections

To be able to add multiple connections to a data foundation, you must select the multisource-enabled type when you create the data foundation.

You can select multiple connections when you create the data foundation. You can also add connections to an existing multisource-enabled data foundation. Connections must be secured, and therefore available in a repository. The connections are represented by a connection shortcut in the local project.

The connections in a multisource-enabled data foundation have the following additional properties:

● A short name used to identify the connection in the data foundation and to modify the table name in SQL expressions. You specify the short name when adding the connection. This name must be unique within the data foundation and is limited to forty characters. If you change the short name for the connection, the SQL expressions are automatically updated with the new name.

● A color for the connection. This color is used in the table header in data foundation views. You select the color when adding the connection. You can change the color for a connection at any time.

● A catalog used to identify the connection to the query server. A default catalog name is registered

automatically with the query server the first time the connection is added to any multisource-enabled data foundation.

● For SAP BW connections, properties related to the automatic insertion of tables and joins. For more information on these properties, see the related topic.

In a multisource-enabled data foundation, the table name as it appears in SQL expressions has the format:

@catalog(short name)."database _qualifier.database_owner"."table_name"

A multi-source join can be created between tables from different connections. You can use the Detect Joins command to detect joins between tables referenced in different connections, or explicitly define them with the Insert Join command.

Related Information

Using SAP BW data sources [page 38]

About connections in the data foundation [page 135]

Changing a connection in a data foundation [page 138]

10.1.3.2 SQL expressions in multisource-enabled data foundations

SQL expressions that define joins, calculated columns, and derived tables in a multisource-enabled data foundation use SQL-92 ANSI standard syntax.

In SQL-92 expressions, you can include SAP BusinessObjects database functions. The SQL syntax can be different from the syntax of the same function provided by database-specific SQL. For more information, see the related topic.

In SQL-92 expressions, you can include @functions. Which @functions you can include depends on the type of expression. For more information, see the related topic.

In order to use functions or operators that are offered by the database and not by SQL-92 (for example, Oracle analytical functions), you define database-specific calculated columns and derived tables. An option in the SQL Expression Editor allows you to use database-specific SQL.

Database-specific calculated columns and derived tables support the SQL syntax of the associated connection.

The following rules apply to database-specific SQL expressions:

● You can reference only standard tables and database-specific derived tables in a single connection.

● You cannot reference tables in SAS or SAP BW connections.

● You can include @functions with certain restrictions. For more information, see the related topic.

Related Information

SAP BusinessObjects SQL function reference for multisource-enabled universes [page 329]

About @Functions [page 392]

10.2 About the Data Foundation Editor

This topic describes how to navigate the Data Foundation Editor. For steps to help you build the structure of your data foundation, see How to build a data foundation [page 133].

The Data Foundation Editor is divided into a data foundation view pane, a properties pane, and browsing panes.

The data foundation view is a graphical representation of the tables and joins. The Master view contains all tables and joins and cannot be deleted. You can define custom views that contain subsets of the tables. Access the views by the tabs at the bottom of the view pane. For more information about custom views, see the related topic.

The properties pane displays the properties of the data foundation object that is currently selected (the entire data foundation, a table, a column, or a join). To edit properties that apply to the entire data foundation, see the related topic.

In the data foundation view, you can work on tables and joins using commands in the Insert and Detect menus, or by clicking objects directly in the view.

The browsing panes allow you to work with different elements of the data foundation. Access the panes by clicking the corresponding tab:

● Connections

● Data Foundation (displays a tree view of the tables and joins)

● Aliases and Contexts

● Parameters and Lists of Values

● Federation Layer

For more information about what you can do in each of the browsing panes, see the related topic.