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 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 Topics

•About data foundation types

•How to build a data foundation

9.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 Topics

•About single-source data foundations

•About multisource-enabled data foundations

9.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 Topics

•About multisource-enabled data foundations

•How to build a data foundation

•Changing a connection in a data foundation

9.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 NetWeaver 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.

SQL-92 standard syntax is the default for calculated columns, derived tables, and join expressions. In addition, the SAP BusinessObjects SQL 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).

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

• You want to insert tables and joins from more than one relational data source.

• You want to use SAP NetWeaver 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 Topics

•Data foundations with multiple connections

•SQL expressions in multisource-enabled data foundations

•About single-source data foundations

•How to build a data foundation

•Universes on SAP NetWeaver BW

9.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 NetWeaver 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 Topics

•Universes on SAP NetWeaver BW

•About connections in the data foundation

•Changing a connection in a data foundation

9.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 AINSI standard syntax.

In SQL-92 expressions, you can include SAP BusinessObjects SQL functions and @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 NetWeaver BW connections.

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

Related Topics

•About @Functions