Oracle^® Business Intelligence Disconnected Analytics Administration and Configuration Guide > Preparing Applications for Oracle BI Disconnected Analytics > Working with Data Set Definitions >

Generating a Combined Table from Multiple Tables

You can generate a single combined table by joining a fact table to all its dimension tables.

The single table strategy results in simpler data set definitions and fewer SQL scripts to manage. It might lead to improved performance on the disconnected machine because the SQL Anywhere database no longer needs to perform join operations on the combined table. However, joining the tables increases the amount of data because dimension table column values are repeated for each fact table entry.

CAUTION:  You should not choose the single table strategy for applications that have large fact tables and many dimension tables. Choose the single table strategy only if the resulting table is less than 20 MB.

For example, using the retail application example in Example of an Application Configuration File, you can join the Product and Store dimensions to the SalesFact table producing a single table.

Example of How to Generate a Combined Table from Multiple Tables

The following example of a file called CombinedRetail.sql illustrates the SQL that would generate a combined table.

drop table CombinedRetail;

create table CombinedRetail (

C_ProductctID integer,

C_StoreID integer,

C_ProductName char(30),

C_StoreName char(30),

C_City char(20),

C_State char(2),

C_Timestamp datetime,

C_Quantity smallint,

C_Price smallint

);

Example of the Simplified Retail Data Set

The following example illustrates the simplified retail data set after the tables are combined.