Bookshelf Home | Contents | Index | Search | PDF

Siebel Analytics Performance Tuning Guide > Siebel Analytics > Subset Large Physical Dimension Tables >

Subset Dimension Tables

---

The Siebel eBusiness Data Warehouse contains several large dimension tables that include well-defined subsets.

For example, the Person dimension (W_PERSON_D) includes contacts as well as employees. Similarly, the Organization dimension (W_ORG_D) includes accounts, partner accounts, competitor account, owner organization, and so forth.

These tables are shipped with the Data Warehouse and are mapped into the Siebel Analytics metadata as well as standard reports.

Creating a physical subset dimension makes sense only if you can identify a family of queries that reference only the rows in the subset. Otherwise, you incur the expense of additional physical storage, additional update management, overhead in the metadata and processing of your Business Model but receive no benefits.

Example

Suppose you have identified a set of frequently executed queries that reference only employees in a 2 million row Person dimension table. Of these 2 million rows, only 5000 represent employees, the remainder represent contacts.

To improve the performance of this family of queries, create a physical Employee table and direct queries that reference only Employees to the smaller table. This strategy makes sense because the query can scan the smaller table much faster than it can the larger one.

The W_EMPLOYEE_D table is already mapped into the Siebel metadata, and you can use it as an example for any custom subset dimension you might create. For a list of subset tables shipped with Siebel Analytics, see Siebel eBusiness Data Warehouse Data Model Reference guide.

To create and map a subset dimension

Create a W_EMPLOYEE_D table which is a subset of the W_PERSON_D table (rows which have `Y' in their EMP_FLG column).

CREATE TABLE SIEBEL.W_EMPLOYEE_D
   AS (select *
      from "W_PERSON_D"
      where EMP_FLG = `Y');

Create indexes that can improve access performance on the W_EMPLOYEE table. See Review Configuration Parameters.
Open the Analytics repository using the Siebel Analytics Administrator tool.
Import the new table from the Siebel Data Warehouse into the Physical layer.

This step modifies the Siebel Analytics metadata so that it points to the newly created W_EMPLOYEE_D table as illustrated below.

NOTE:  You will need to refresh the contents of the W_EMPLOYEE table anytime the contents of the W_PERSON_D table are refreshed or loaded.

Create the required joins (copy from the W_PERSON_D).

See illustration below.

Map Employee in the Business Model to the W_EMPLOYEE_D.

Siebel Analytics will now retrieve data from the smaller W_EMPLOYEE_D table when a query references Employee, and the query will run faster.

Remove Columns Not Required by a Report

Look at frequently run Dashboard reports and make sure that only the columns that are required by your reports occur are selected. Remove any superfluous columns. This simple action can improve query performance for those cases where a large number of columns not required by the report occur in the query select list.

Example

This example shows you how to remove columns that are not required by a report.

Suppose you have a report named "Product Lines with Open-Critical SRs" similar to the one shown below.
To see the criteria for selecting columns, click "Modify Request."
The "# of SRs" fact is selected although it is not required in the "Product Lines with Open-Critical SRs" report. See the figure above.
Remove this column from the selection criteria.



Click the Results tab, as shown in the figure above, to verify whether the report remained the same.
Save your changes.
Continue to search for other columns that force the database server to retrieve data that is not required by the report. See the figure above.

Bookshelf Home | Contents | Index | Search | PDF