Siebel Business Analytics Server Administration Guide > Setting Up Mobile Analytics >

Creating and Testing Tables and Indexes in the SQL Anywhere Database

Before you can download data to the mobile machine, you need to set up a SQL Anywhere database with the appropriate tables.

NOTE:  It is recommended that you use SQL Anywhere to test creation of the mobile tables (your DDL scripts).

You will add pointers to these scripts when you create your application configuration file. When the Disconnected Analytics Application Manager initiates synchronization, the database tables are created or overwritten.

To create and test mobile tables and indexes, use the guidelines in the following topics:

• Creating SQL Scripts
• Scenario for Using SQL Scripts to Create Mobile Tables and Indexes
• Testing SQL Scripts That Create Mobile Tables and Indexes
• Storing SQL Scripts in the Mobile Directory

Creating SQL Scripts

Administrators need to perform the following tasks so that the Disconnected Analytics Application Manager can create tables and indexes in the SQL Anywhere database on the mobile machine:

• Determine the tables and indexes needed for a mobile application.
• Create corresponding scripts, creating one script for each table. Optionally, you can also create one for all indexes of a given table.

For more information about SQL syntax for SQL Anywhere, you can download the documentation (Adaptive Server Anywhere, SQL Reference) from the Sybase Web site, or you can contact Sybase for copies of the documentation.

Scenario for Using SQL Scripts to Create Mobile Tables and Indexes

A simple retail business application might keep track only of products sold and the stores where sales occur. A mobile version of this application might consist of two dimension tables (Product and Store) and a central fact table called SalesFact. Each of these three tables would need a corresponding SQL Script file for the mobile application.

This section describes the SQL Script files for these three tables.

Product.sql

drop table Product;

create table Product (

P_ID integer,

P_Name char(30),

);

Store.sql

drop table Store;

create table Store (

S_ID integer,

S_Name char(30),

S_City char(20),

S_State char(2)

);

SalesFact.sql

drop table SalesFact;

create table SalesFact (

F_ProductID integer,

F_StoreID integer,

F_Timestamp datetime,

F_Quantity smallint,

F_Price smallint

);

Optionally, mobile applications may have SQL script files for creating one or more indexes on a given table. Continuing to use the simple retail business example, this section describes a SQL script for creating an index on the P_Name column of the Product table.

ProductIdx.sql

drop index ProductIX;

create index ProductIX on Product (P_Name);

Testing SQL Scripts That Create Mobile Tables and Indexes

Siebel Business Analytics administrators can test their scripts using the Sybase Interactive SQL Utility (dbisqlc.exe). Sybase documentation provides instructions about how to use this utility. You can download the documentation from the Sybase Web site and search for Interactive SQL utility, or you can contact Sybase for copies of the documentation.

Storing SQL Scripts in the Mobile Directory

After successfully testing the scripts, Siebel Business Analytics administrators should store them in the mobile application metadata directory for the appropriate application. For the retail business application scenario, this directory might be D:\SiebelAnalyticsData\Mobile\Retail\app. For a description of mobile directories, see About the Mobile Directory Structure.