TAGS saas, soa, All

A Practical Demonstration of SaaS using Oracle Application Express

by Steve Bobrowski

Learn the basic concepts of "software as a service" architecture by building your own mini-SaaS application.

Published August 2007

Whether you believe in creation, intelligent design, or Darwinism, evolution is an undeniable process that spans most everything around us. In nature, species evolve to survive in an ever-changing environment. And in business, well-managed companies evolve their business operations to compete better and increase profits.

One of the most prevalent trends in today's business world is an evolution toward the delivery and consumption of software as a service, or SaaS. This article explains some of the fundamental concepts, benefits, and implementation details of SaaS and provides a workshop on building a demonstration application so that developers and consumers can better understand and take advantage of this emerging technology.

Why SaaS?

In the broadest sense, SaaS is an uncomplicated concept: customers access software as a hosted application over the Internet. So why is such a straightforward idea all the rage? Consider a simple example that compares how a medium-size or large business might implement a standard customer relationship management (CRM) application by taking two different approaches: the traditional on-premises, application ownership model versus the on-demand SaaS, or application subscriber model.

The traditional application ownership model typically requires a company to

• Buy software and support licenses for an operating system, database, and CRM application
• Purchase hardware (one or more servers and storage) to support the CRM application
• Hire a staff of one or more administrators and consultants to install, configure, and maintain the CRM environment

From the customer's perspective, the total cost of ownership for the first year of owning a traditional CRM application would no doubt be hundreds of thousands of dollars, not to mention the ongoing annual costs to maintain it. From the CRM application developer's viewpoint, the pool of potential customers is limited to companies that can afford to pay the hefty price tag associated with owning and managing their own copy of the application.

Compare this with the simplified SaaS approach—a company just subscribes to a CRM software service for however many users require access. The company doesn't need to buy any special hardware or software and doesn't need to hire staff to install, configure, patch, monitor, and otherwise maintain operating systems, software, and data. The company's users do nothing more than use inexpensive PCs to load application pages into a standard Web browser and do their job. Consequently, the total cost of the company providing its users with a CRM application drops significantly. For example, one popular on-demand CRM solution, Salesforce, from salesforce.com, sells for as little as $140 per user per year! From the customer's point of view, the bottom line is certainly compelling—unless a company has thousands of users, subscribing to a CRM application is much more cost-effective than paying for the privilege of owning one outright.

Application vendors can also benefit tremendously from a shift to SaaS. Low-cost commodity hardware and open source operating systems facilitate profitable hosting of SaaS applications, provided that the applications are well designed and scale as hundreds or thousands of users subscribe to their services. By passing on the reduced cost to customers, software vendors now have tremendous sales potential from new markets such as small businesses that were traditionally unapproachable with the application ownership model.

Why Not SaaS?

SaaS critics would point out that, although SaaS may be a good model for delivering small applications that do not maintain critical data, SaaS will have difficulty succeeding with line-of-business applications, because of several important caveats:

• Consumers must trust a third party to manage, protect, and secure access to their business data.
• Consumers do not necessarily own their data that is managed by an independent software vendor's service.
• Consumers have no recourse if the SaaS vendor goes out of business and may consequently lose all of their data.

Note that each of these valid arguments against the early adoption of SaaS centers on the two key concepts of trust and control (or lack thereof). But as time passes and SaaS matures, standard business practices and solutions will surely evolve that address all of these concerns. It is therefore reasonable to expect that the anxiety about using SaaS will settle down to the same degree of unease that people and companies have when they unceremoniously put all of their money into banks and other financial institutions.

SaaS All Around Us

CRM applications are just one example of consumer-oriented and line-of-business applications now being offered as a service. Countless examples of SaaS can be found in every niche of the software industry:

• eBay is perhaps the best-known example of SaaS, providing online auction and shopping services to millions of sellers and buyers worldwide. It collects fees from sales transactions to generate revenue.
  
  
• Yahoo!, Google, and Microsoft offer free online e-mail applications—Yahoo! Mail, Gmail, and Hotmail, respectively. And Google now offers the beginnings of a SaaS "office suite," Google Docs & Spreadsheets. These companies generate revenue by selling advertisements that are incorporated into such services.
  
  
• Full-featured online office suites include zoho.com and thinkfree.com.
  
  
• The employee recruiting applications monster.com, dice.com, and hotjobs.com make money from both advertising and user fees.
  
  
• A medical or dental practice can use any one of several subscription-based, hosted software solutions to manage its business operations at a fraction of the cost of traditional in-office software deployments.
  
  
• A small business and its accountant can use Intuit's QuickBooks Online to manage the company's finances. Cost is as little as $40 for an annual subscription that includes three users plus one accountant.

Adapt, or Go the Way of the Dodo

As more and more businesses learn about and realize the cost savings available to them from outsourcing the operations of standard business applications to SaaS vendors, revenue generated from traditional application sales will no doubt take a hit. If you are an application developer, you cannot afford to ignore the emergence of SaaS. But building, hosting, and maintaining SaaS applications requires that you evolve your mind-set from that of an "application developer" to that of an "application provider." Not only will you have to learn how to develop highly scalable applications for SaaS delivery but you'll also need to address and overcome the challenges of application hosting and maintenance.

The software development environment I'll be using in this article to demonstrate SaaS design is Oracle Application Express. To get the most benefit from this article, I encourage you to register for a free Oracle Application Express workspace at http://apex.oracle.com and follow along to gain some practical experience.

Note: If you don't have the time or the patience to implement the steps in this article yourself, you can see a live demo of the finished application at www.dbdomain.com/saasdemo.htm.

Metadata: The Key to Dynamic Security and Customization

Most people would agree that an successful SaaS application must be scalable, multi-tenant (that is, it can meet the requirements of various "tenants" via a single, secure, and customizable database schema), and easily configured.

The miniature SaaS application you are about to build can be deployed once for any number of tenants, yet each tenant will operate in virtual isolation, not realizing that he or she is sharing the application with others, and there is no danger that one tenant's data will be leaked to another tenant.

A single application that can meet the stated objectives cannot be a statically compiled application in the traditional sense; it must inherently be dynamic in nature. Parametric applications that can morph at runtime are driven by metadata. A generally accepted definition of metadata is "data about data." In other words, a polymorphic application that varies in appearance and function uses metadata that describes the runtime attributes of the application at any given time during its execution.

Oracle Application Express is a perfect example of an application development environment that utilizes metadata to derive all aspects of an application at runtime. The Oracle Database Application Express User's Guide introduces Oracle Application Express as follows:

The Application Express engine renders applications in real time from data stored in database tables. When you create or extend an application, Oracle Application Express creates or modifies metadata stored in database tables. When the application is run, the Application Express engine then reads the metadata and displays the application.

Just Do It

Let's put your newfound basic knowledge about SaaS into action, by building a very simple SaaS CRM application, and learn firsthand about some of the challenges you've been reading about.

This demo application illustrates what I refer to as the static generic column data model for supporting a tenant-configurable SaaS application. Another implementation approach you might consider would be what I call a three-layer data model (see sidebar).

The schema this demo application uses is shown as a UML class diagram in Figure 1. The schema is intentionally simple, so that you can focus on learning the concepts of SaaS development rather than the intricacies of a complicated schema design.

Figure 1. CRM class diagram

The first table in the schema is the TENANTS table, which stores the most-basic information about a subscriber to (a tenant of) the fictitious CRM application: a primary key (TENANT_ID) and a company name (COMPANY). The data in the TENANTS table might resemble that in Table 1.

The next table to understand is our fictitious CRM application's principal application table, the CUSTOMERS table. At first glance, this table might look a bit odd, because of the column names and datatypes. The CUSTOMERS table has a typical primary key (CUSTOMER_ID) and a foreign key (TENANT_ID) that references the primary key of the TENANTS table, followed by eight atypical generic VARCHAR2 columns named COL_2, COL_3, ... COL_9. Why on earth would I suggest this table design rather than a CUSTOMERS table with a standard set of columns such as ADDRESS, CITY, STATE, and so on? In order to realize two of our SaaS application design goals—to make the application's data model flexible for any tenant-requirements variation and to use a single schema design for all tenants—you cannot employ standardized "one size fits all" table designs you might normally build with statically compiled applications. Instead, you must somehow employ an application schema with tables that allow each tenant to dynamically customize and extend the data model at will, tables that effectively morph to each tenant's needs at application runtime. If you are confused, Table 2 below should help make things clear.

Note that Tenant No. 1 has two customers: yours truly and someone named Tom Kyte; meanwhile, Tenant No. 2 also has two customers, some prominent businessmen named Ellison and Gates. For each customer of each tenant, COL_2 and COL_3 store the customer's last and first names, respectively—but those are the only two things these tenants have in common. Tenant No. 1 uses COL_4, COL_5, COL_6, and COL_7 to store each customer's physical address information; meanwhile, Tenant No. 2 uses COL_4 to store each customer's e-mail address and is not using COL_5, COL_6, and COL_7 at all. (This figure indicates nulls as "-".) So what we have here is a single table that can store varying customer attributes for each tenant of this very simple demonstration application.

The next question you might be asking is, "How does the application ascertain the purpose of COL_2, COL_3, ... COL_9 in the CUSTOMERS table for each tenant?" The answer is to create a table that can store metadata about the application's tables that store actual data: in this demonstration application, the metadata table is TENANT_COLUMNS. The TENANT_COLUMNS table has four columns (see Table 3):

• The TENANT_ID column is a foreign key that references the primary key of the TENANTS table.
• The TABLE_NAME column stores the name of a particular table in the CRM application schema, such as CUSTOMERS.
• The COLUMN_ID column stores an integer that matches the numerical identifier in the corresponding table's generic column names.
• The COLUMN_NAME column stores a string that indicates the name of the corresponding column.

Note above that Tenant No. 1 has indicated that in the CUSTOMERS table, COL_2 stores each customer's "Last Name," COL_3 stores each customer's "First Name," COL_4 stores each customer's "Address," COL_5 stores each customer's "City," COL_6 stores each customer's "State," and COL_7 stores each customer's "Zip Code." Meanwhile, Tenant No. 2 has indicated that in the CUSTOMERS table, COL_2 stores each customer's "Last Name" and COL_3 stores each customer's "First Name," but unlike with Tenant No. 1, COL_4 stores each customer's "Email" address. I purposely put each column's labels in quotation marks in the foregoing to help you realize that these labels are pure metadata that the application queries and uses at runtime. If a tenant decided to add new metadata to the table or change the label of a column, the application would automatically adjust at runtime without any modification to the code base. When each customer registers to use the fictitious CRM application, a tenant administrator would use a setup wizard that makes it easy for tenants to customize their use of the application.

The final table in the CRM application schema is the USERS table. This simple table stores only the most basic information about a tenant's users (people who execute the application to perform work): a primary key (USER_ID), a user's login name (USERNAME), and a foreign key (TENANT_ID). The data in the USERS table might resemble Table 4 below.

One important point: Each user's USERNAME must be in all uppercase letters to support the user authentication scheme that the sample application uses. More about this point later when you build the application.

Create the CRM Application Schema

Now that you understand the CRM application schema, it's time to build the application's tables and supporting data structures with Oracle Application Express. Once you have a workspace at apex.oracle.com, use the given workspace name and credentials to establish an Oracle Application Express session. The Oracle Application Express home page displays three main icons you can use to start building applications with the tool.

For three of the four tables you will create, you also need to create a sequence—a primary key generator—and an associated trigger to populate the table's primary key with sequence numbers. Oracle Application Express's wizard-driven interface makes creating a table, associated integrity constraints, sequences, and triggers easy.

To create a TENANTS table and all related objects in one operation, click SQL Worksheet -> Object Browser -> Create -> Table to launch the Create Table wizard.

1. On the Columns page of the wizard, specify the table name as TENANTS and specify two columns: TENANT_ID as NUMBER and COMPANY as not-null VARCHAR2(4000). For those of you unfamiliar with Oracle Database, VARCHAR2 is Oracle Database's variable-length column datatype, with 4000 being the maximum length (in bytes, by default). Click Next to continue.
   
   
2. On the Primary Key page of the wizard, select Populated from a new sequence, accept the default names for the new sequence and primary key constraint, and select the TENANT_ID column from the Primary Key list. Then click Next.
   
   
3. The next two pages of the wizard let you declare foreign key (referential), check, and unique constraints for the table; the simple TENANTS table you are creating has none of these types of constraints. Click Next and then Finish to move to the Confirm page of the wizard. On this page, if you click SQL, you should reveal SQL statements similar to those below, which the wizard will execute when you click Create.

   CREATE table "TENANTS" (
       "TENANT_ID"  NUMBER,
       "COMPANY"    VARCHAR2(4000) NOT NULL,
       constraint  "TENANTS_PK"
        primary key ("TENANT_ID")
   )
   /
   
   CREATE sequence "TENANTS_SEQ" 
   /
   
   CREATE trigger "BI_TENANTS"  
     before insert on "TENANTS"              
     for each row 
   begin  
       select "TENANTS_SEQ".nextval
         into :NEW.TENANT_ID
         from dual;
   end;
   /
   

1. On the Columns page of the wizard, specify the table name as CUSTOMERS. Next, specify the CUSTOMER_ID and TENANT_ID columns as NUMBER and disallow nulls from the TENANT_ID column. Finally, specify the eight generic VARCHAR2(4000) columns COL_2 ... COL_9 (you'll have to click Add Column a couple of times to make room for more columns) and then click Next to continue.
   
   
2. On the Primary Key page of the wizard, select Populated from a new sequence, accept the default names for the new sequence and primary key constraint, and select the CUSTOMER_ID column from the Primary Key list. Then click Next.
   
   
3. On the Foreign Keys page of the wizard, add the TENANT_ID column to the Key Column(s) list, specify the TENANTS table in the References Table field, and add the TENANT_ID column to the Referenced Column(s) list. Don't forget to click Add to add the declared foreign key.
   
   
4. To skip the remaining wizard pages, click Confirm. Then click SQL and make sure the SQL is similar to that below.

   CREATE table "CUSTOMERS" (
       "CUSTOMER_ID" NUMBER,
       "TENANT_ID"   NUMBER NOT NULL,
       "COL_2"       VARCHAR2(4000),
       "COL_3"       VARCHAR2(4000),
       "COL_4"       VARCHAR2(4000),
       "COL_5"       VARCHAR2(4000),
       "COL_6"       VARCHAR2(4000),
       "COL_7"       VARCHAR2(4000),
       "COL_8"       VARCHAR2(4000),
       "COL_9"       VARCHAR2(4000),
       constraint  "CUSTOMERS_PK"
        primary key ("CUSTOMER_ID")
   )
   /
   
   CREATE sequence "CUSTOMERS_SEQ" 
   /
   
   CREATE trigger "BI_CUSTOMERS"  
     before insert on "CUSTOMERS"              
     for each row 
   begin  
     if :NEW."CUSTOMER_ID" is null then
       select "CUSTOMERS_SEQ".nextval
       into :NEW."CUSTOMER_ID" from dual;
     end if;
   end;
   /   
   
   ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "CUSTOMERS_FK" 
   FOREIGN KEY ("TENANT_ID")
   REFERENCES "TENANTS" ("TENANT_ID")
   /
   

1. On the Columns page of the wizard, specify the table name as USERS. Next, specify the USER_ID column as NUMBER, the USERNAME column as VARCHAR2(10) AND not null, and the TENANT_ID column as NUMBER and not null. Then click Next to continue.
   
   
2. On the Primary Key page of the wizard, select Populated from a new sequence, accept the default names for the new sequence and primary key constraint, and select the USER_ID column from the Primary Key list. Then click Next.
   
   
3. On the Foreign Keys page of the wizard, add the TENANT_ID column to the Key Column(s) list, specify the TENANTS table in the References Table field, and add the TENANT_ID column to the Referenced Column(s) list. Click Add to add the declared foreign key.
   
   
4. To skip the remaining wizard pages, click Confirm. Then click SQL and make sure that the SQL is like that below.

   CREATE table "USERS" (
       "USER_ID"    NUMBER,
       "USERNAME"   VARCHAR2(30),
       "TENANT_ID"  NUMBER,
       constraint  "USERS_PK"
        primary key ("USER_ID")
   )
   /
   
   CREATE sequence "USERS_SEQ" 
   /
   
   CREATE trigger "BI_USERS"  
     before insert on "USERS"              
     for each row 
   begin  
     if :NEW."USER_ID" is null then
       select "USERS_SEQ".nextval
       into :NEW."USER_ID" from dual;
     end if;
   end;
   /   
   
   ALTER TABLE "USERS" ADD CONSTRAINT "USERS_FK" 
   FOREIGN KEY ("TENANT_ID")
   REFERENCES "TENANTS" ("TENANT_ID")
   
   /
   

• The wizard does not allow you to create a primary key constraint with more than two columns. All columns in the TENANT_COLUMNS table constitute its primary key.
  
  
• The wizard does not allow you to create anything other than standard heap-organized tables, but this lookup table is better suited to be an index-organized table (IOT). An IOT is a table that Oracle Database creates as an index; in other words, there aren't separate data structures for the table and the primary key index. An IOT can offer a significant performance boost when applications query the lookup table predominantly by its primary key. Performance should be a key goal of any application design but especially with a SaaS application that needs to be able to scale when supporting thousands of concurrent users. Oracle Database has tons of performance-related features such as IOTs that can make an application efficient.

CREATE table "TENANT_COLUMNS" (
    "TENANT_ID"   NUMBER,
    "TABLE_NAME"  VARCHAR2(30),
    "COLUMN_ID"   NUMBER,
    "COLUMN_NAME" VARCHAR2(30),
     CONSTRAINT "TENANT_COLUMNS_PK"
      PRIMARY KEY (
       "TENANT_ID", "TABLE_NAME",
       "COLUMN_ID", "COLUMN_NAME"),
     CONSTRAINT "TENANT_COLUMNS_FK" 
      FOREIGN KEY ("TENANT_ID")
      REFERENCES "TENANTS" ("TENANT_ID"))
    ORGANIZATION INDEX;

You could use the Create Index wizard of the Oracle Application Express Object Browser to easily create the necessary foreign key indexes. However, in the interest of brevity, you can accomplish the same task by using the SQL Commands page to execute the two CREATE INDEX commands:

CREATE INDEX  "CUSTOMERS_IDX1" ON  "CUSTOMERS" ("TENANT_ID");

CREATE INDEX  "USERS_IDX1" ON  "USERS" ("TENANT_ID");

-- The CRM_PKG specification declares the package's API.
CREATE OR REPLACE PACKAGE  "CRM_PKG" AS
 g_tenant_id NUMBER;
 FUNCTION get_tenant_id (p_username IN VARCHAR2)
  RETURN NUMBER;
 FUNCTION get_report_columns (
  p_row_source IN VARCHAR2,
  p_tenant_id IN NUMBER DEFAULT g_tenant_id)
  RETURN VARCHAR2;
 FUNCTION get_form_columns (
  p_row_source IN VARCHAR2,
  p_tenant_id IN NUMBER DEFAULT g_tenant_id)
  RETURN VARCHAR2;
END;
/

-- The CRM_PKG body defines all package constructs.
CREATE OR REPLACE PACKAGE BODY  "CRM_PKG" AS

  FUNCTION get_tenant_id(p_username IN VARCHAR2)
  RETURN NUMBER IS
  BEGIN
    SELECT tenant_id
    INTO g_tenant_id
    FROM users
    WHERE username = p_username;
    RETURN g_tenant_id;
  END;

  FUNCTION get_report_columns(
   p_row_source IN VARCHAR2,
   p_tenant_id IN NUMBER DEFAULT g_tenant_id)
  RETURN VARCHAR2 IS

  l_columns VARCHAR(1000) := 'Edit:';

  BEGIN
    FOR rec IN
      (SELECT column_id, column_name
       FROM tenant_columns
       WHERE tenant_id = p_tenant_id
       AND TABLE_NAME = p_row_source
       ORDER BY column_id)
    LOOP
      IF(LENGTH(l_columns) > 0) THEN
        l_columns := l_columns || ':';
      END IF;
      l_columns := l_columns || rec.column_name;
    END LOOP;
    RETURN l_columns;
  END;

  FUNCTION get_form_columns(
   p_row_source IN VARCHAR2,
   p_tenant_id IN NUMBER DEFAULT g_tenant_id)
  RETURN VARCHAR2 IS

  l_columns VARCHAR(1000) := 'ID';

  BEGIN
    FOR rec IN
      (SELECT column_id, column_name
       FROM tenant_columns
       WHERE tenant_id = p_tenant_id
       AND TABLE_NAME = p_row_source
       ORDER BY column_id)
    LOOP
      IF(LENGTH(l_columns) > 0) THEN
        l_columns := l_columns || ':';
      END IF;
      l_columns := l_columns || rec.column_name;
    END LOOP;
    RETURN l_columns;
  END;

  -- initialization block
  BEGIN
    SELECT tenant_id
    INTO g_tenant_id
    FROM users
    WHERE UPPER(username) =
     htmldb_custom_auth.get_username;
  END;
/

• The CRM_PKG package specification declares a public global variable, G_TENANT_ID. The package body has an initialization block that initializes G_TENANT_ID with a tenant ID, based on the value returned by a query of the USERS table. Oracle Database maintains session-specific package state, including the contents of global variables, for each session that uses a package. For example, if you connected to the application with the username MIKE, Oracle would query the USERS table and initialize the G_TENANT_ID package variable with the TENANT_ID 1.
  
  
• Provided with a username from the USERS table, the public package function GET_TENANT_ID returns a tenant ID. For example, if you connected to the application with the username JOE and called the CRM_PKG.GET_TENANT_ID function, Oracle would query the USERS table and return the TENANT_ID 2.
  
  
• Provided with a row source name, such as a table or view name, registered in the TENANT_COLUMNS table, the public package function GET_REPORT_COLUMNS returns a colon-delimited set of column names from the TENANT_COLUMNS table. For example, if you connected to the application with the username MIKE and then called the CRM_PKG.GET_REPORT_COLUMNS function with the P_ROW_SOURCE argument set to CUSTOMERS, Oracle would query the TENANT_COLUMNS table and return the string Edit:Last Name:First Name:Address:City:State:Zip Code.
  
  
• Provided with a row source name, such as a table or view name, registered in the TENANT_COLUMNS table, the public package function GET_FORM_COLUMNS returns a colon-delimited set of column names from the TENANT_COLUMNS table. For example, if you connected to the application with the username JOE and then called the CRM_PKG.GET_FORM_COLUMNS function with the P_ROW_SOURCE argument set to CUSTOMERS, Oracle would query the TENANT_COLUMNS table and return the string ID:Last Name:First Name:Email.

Add Some Data

-- TENANTS
INSERT INTO tenants (company)
VALUES ('Mike''s Tires');
INSERT INTO tenants (company)
VALUES ('Joe''s Tires');
COMMIT;

-- CUSTOMERS
INSERT INTO customers (tenant_id, col_2, col_3, col_4, col_5, col_6, col_7)
VALUES (1,'Bobrowski','Steve','123 Maple Street','Springfield','MA','02001');
INSERT INTO customers (tenant_id, col_2, col_3, col_4, col_5, col_6, col_7)
VALUES (1,'Kyte','Tom','456 Elm Street','Reston','VA','01566');
INSERT INTO customers (tenant_id, col_2, col_3, col_4)
VALUES (2,'Ellison','Larry','lellison@aol.com');
INSERT INTO customers (tenant_id, col_2, col_3, col_4)
VALUES (2,'Gates','Bill','bgates@hotmail.com');
COMMIT;

-- TENANT_COLUMNS
INSERT INTO tenant_columns (tenant_id, table_name, column_id, column_name)
VALUES (1,'CUSTOMERS',2,'Last Name');
INSERT INTO tenant_columns (tenant_id, table_name, column_id, column_name)
VALUES (1,'CUSTOMERS',3,'First Name');
INSERT INTO tenant_columns (tenant_id, table_name, column_id, column_name)
VALUES (1,'CUSTOMERS',4,'Address');
INSERT INTO tenant_columns (tenant_id, table_name, column_id, column_name)
VALUES (1,'CUSTOMERS',5,'City');
INSERT INTO tenant_columns (tenant_id, table_name, column_id, column_name)
VALUES (1,'CUSTOMERS',6,'State');
INSERT INTO tenant_columns (tenant_id, table_name, column_id, column_name)
VALUES (1,'CUSTOMERS',7,'Zip Code');
INSERT INTO tenant_columns (tenant_id, table_name, column_id, column_name)
VALUES (2,'CUSTOMERS',2,'Last Name');
INSERT INTO tenant_columns (tenant_id, table_name, column_id, column_name)
VALUES (2,'CUSTOMERS',3,'First Name');
INSERT INTO tenant_columns (tenant_id, table_name, column_id, column_name)
VALUES (2,'CUSTOMERS',4,'Email');
COMMIT;

-- USERS
INSERT INTO users (username, tenant_id)
VALUES ('MIKE',1);
INSERT INTO users (username, tenant_id)
VALUES ('JOE',2);
COMMIT;

Create the CRM Application

To start the Create Application wizard, click Application Builder -> Create and then carry out the following steps:

1. Click Create Application.
   
   
2. Specify the name of the application as CRM, select From Scratch, and then click Next.
   
   
3. The simple CRM application will have only two pages: a report and a form. To build both of these pages in one step, select Report and Form, specify Table Name as CUSTOMERS, click Add Page, and then click Next.
   
   
4. Because the CRM application has only two pages, no navigation tabs are necessary, so click No Tabs.
   
   
5. The CRM application does not need to copy any shared components from other applications, so select No and click Next to continue.
   
   
6. The CRM application will use the built-in Oracle Application Express authentication scheme for users, so select Application Express, specify English for Language, and then click Next to continue.
   
   
7. Themes control the look and feel of the application interface. To match the figures in this article, click Theme 2.
   
   
8. That's it. Just click Create, and Oracle Application Express builds the application.

Figure 2. The first cut of the Customers report page in the CRM SaaS application

Provide Tenant-Specific Data Security

1. Register Oracle Application Express end users who correspond to the usernames in the USERS table.
   
   
2. Add an application item (application-side global variable) that the CRM application can use to maintain each application user's tenant ID, and then modify the application's authentication scheme to initialize the application item.
   
   
3. Modify the Customer report's query so that it shows only those records that correspond to the current user's tenant ID.

Create Oracle Application Express Users

1. Click Application #####, where ##### is the application number that corresponds to your application (in the Developer toolbar links toward the bottom of the page).
   
   
2. Click Home.
   
   
3. Click Manage Application Express Users.
   
   
4. Click Create End User.
   
   
5. Specify the User Name as MIKE, a case-sensitive password, and your email address.
   
   
6. Click Create and Create Another.
   
   
7. Specify the User Name as JOE, a case-sensitive password, and your email address.
   
8. Click Create User.

Create an Application Item

1. Click Application Builder.
   
   
2. Click the application you are building, CRM.
   
   
3. Click Shared Components.
   
   
4. Click Application Items.
   
   
5. Click Create.
   
   
6. Specify the new item's name as APP_TENANT_ID.
   
   
7. In Session State Protection, select Restricted— May not be set from browser.
   
   
8. Click Create.

1. In the "breadcrumb menu" on the current page, click Shared Components.
   
   
2. Click Authentication Schemes.
   
   
3. Click Application Express—Current.
   
   
4. In the Login Processing section, specify the following assignment statement in the Post-Authentication Process field. This assignment calls the CRM_PKG.GET_TENANT_ID function to initialize the APP_TENANT_ID application item, based on the current user, by referring to the Oracle Application Express built-in application item :APP_USER.

   :APP_TENANT_ID := crm_pkg.get_tenant_id(:APP_USER);
   

5. Click Apply Changes.

Modify the Customer Report's Query

1. In the breadcrumb menu, click Application #####.
   
   
2. Click 1- CUSTOMERS to modify the Customers report page.
   
   
3. In the Regions section, click CUSTOMERS.
   
   
4. In the Source section, append the following to the report's query:

   AND tenant_id = :APP_TENANT_ID
   

5. Click Apply Changes, in the upper right corner of the current page.

Test the Tenant-Specific Report

1. Click Run.
   
   
2. Click Logout.
   
   
3. Log in, using the username MIKE and this Oracle Application Express user's case-sensitive password.
   
   
4. Note that the report displays only the customers for Tenant No. 1.
   
   
5. Click Logout to log out as MIKE.
   
   
6. Log in, using the username JOE and this Oracle Application Express user's case-sensitive password.
   
   
7. Note that the report displays only the customers for Tenant No. 2.

   For example, when you establish a connection as JOE, the report should appear similar to Figure 3.

   

   Figure 3. The Customers report page now displays rows that specifically correspond to each user.

Present Tenant-Specific Customers Report Columns

1. Click Edit Page 1 (in the Developer toolbar).
   
   
2. In the Regions section, click Report (adjacent to CUSTOMERS).
   
   
3. For each generic column (COL_2 ... COL_9), click the column's edit icon, click Conditions, click [exists] to set Condition Type as Exists (SQL query returns at least one row), and then specify an EXISTS query in the Expression 1 field. For example, for COL_2, specify the following query:

   SELECT NULL FROM TENANT_COLUMNS
   WHERE TABLE_NAME = 'CUSTOMERS'
   AND tenant_id = :APP_TENANT_ID
   AND COLUMN_ID = 2
   

   And for COL_3 you would specify the following query:

   SELECT NULL FROM TENANT_COLUMNS
   WHERE TABLE_NAME = 'CUSTOMERS'
   AND tenant_id = :APP_TENANT_ID
   AND COLUMN_ID = 3
   

   Repeat this step for each generic column. Note that the only part of the query that changes for each generic column's display condition is the COLUMN_ID selection criterion (boldfaced in each query above).
   
   
4. Rather than click Apply Changes and click the edit icon for each column, you can simply click the convenient < and > icons to move among the column attributes pages. After you modify all of the generic columns, click Apply Changes.

1. In the Headings Type list, click PL/SQL.
   
   
2. In the Function returning colon-delimited headings field, which appears, specify the following PL/SQL block to call the CRM_PKG.GET_REPORT_COLUMNS function and return a tenant-specific list of column headings. Note in the call below that there's no need to specify a P_TENANT_ID argument for the function, because it has a default value, the global package variable G_TENANT_ID—this API design of using a default value was specifically included to help reduce the possibility of programming errors that might otherwise result in data leakage among tenants.

   begin
   return crm_pkg.get_report_columns('CUSTOMERS');
   end;
   

3. The report should not display the TENANT_ID column and reveal that other tenants share this application. Therefore, unselect the Show check box for this column.
   
4. Click Apply Changes.

1. Click Run.
   
   
2. Note in the bottom left corner of the page the footer that identifies your connection's username, which should be JOE, according to your most recent login. The report should appear similar to Figure 4. Note that the report now shows only those rows and the proper column headings for a Tenant No. 2 user.

   

   Figure 4. How the Customers report appears to users who correspond to Tenant No. 2
   

3. Click Logout.
   
   
4. Log in as MIKE.
   
   
5. Note that the report now shows only those rows and the proper column headings for a Tenant No. 1 user, similar to Figure 5.

   

   Figure 5. How the Customers report appears to users who correspond to Tenant No. 1

Present a Tenant-Specific Customers Form

Figure 6. Unmodified version of the Customers form

1. Click Edit Page 2 (in the Developer toolbar).
   
   
2. In the Items section, click P2_TENANT_ID.
   
   
3. In the Name section, click [Hidden] to hide the P2_TENANT_ID field.
   
   
4. In the Default section, specify Default Value as :APP_TENANT_ID and set Default Value Type as PL/SQL Expression so that all new records that the form inserts use the tenant user's tenant ID.
   
   
5. At the top of the page, click the > button to move to the page for the next form item, P2_COL_2.
   
   
6. To focus on the Conditions section for the current item, click Conditions.
   
   
7. You can use an item's Conditions section to specify a condition that must be true if Oracle Application Express is going to display the item at runtime. First click [exists] to indicate that you want to specify an EXISTS query. Then, in the Expression 1 field, enter the following query that uses the metadata corresponding to COL_2:

   SELECT NULL FROM TENANT_COLUMNS
   WHERE TABLE_NAME = 'CUSTOMERS'
   AND tenant_id = :APP_TENANT_ID
   AND COLUMN_ID = 2
   

8. Click > to move to the next generic column, and repeat Step 7 for all remaining items that correspond to the generic columns COL_3 ... COL_9. For each item, change only the COLUMN_ID selection criteria (highlighted above). For example, for COL_3, the query would be as follows:

   SELECT NULL FROM TENANT_COLUMNS
   WHERE TABLE_NAME = 'CUSTOMERS'
   AND tenant_id = :APP_TENANT_ID
   AND COLUMN_ID = 3

9. Once you specify the condition for displaying the form item P2_COL_9, click Apply Changes.
   
   
10. Click Run to view the new version of the form, which should appear similar to Figure 7.

    

    Figure 7. The modified Customers form with tenant-specific fields

Note how the P2_TENANT_ID, P2_COL_8, and P2_COL_9 form items no longer appear in the form, due to the metadata-based conditional display queries for each form item.

The final thing to adjust on the form is the set of labels for the Customer form fields. Unlike with report column labels, you need to do bit of magic to solve this problem. First you need to add a new hidden field to the form that you can use to store a colon-delimited set of tenant-specific column labels retrieved by a call to the CRM_PKG.GET_FORM_COLUMNS function.

1. Click Edit Page 2. In the Items section, click the Create icon to launch the Create Item wizard.
   
   
2. Click Hidden.
   
   
3. Set Item Name to P2_LABELS, Sequence to 0, and Region to Breadcrumbs(1), and then click Next to continue.
Set Item Source to PL/SQL Expression or Function and Item Source Value to crm_pkg.get_form_columns('CUSTOMERS'), and then click Create Item.

1. In the Regions section, click CUSTOMERS.
   
   
2. In the Header and Footer section, specify the following JavaScript in the Region Header field, and then click Apply Changes.

   <script type="text/javascript">
    var all_labels = document.getElementById('P2_LABELS').value;
    var aLabels=all_labels.split(":");
    function Label(pNum) {document.write(aLabels[pNum-1]);}
   </script>

1. In the Items section, click P2_COL_2.
   
   
2. In the Label section, specify the following in the Label field:

   <script>Label(2);</script>

3. Click > to move to the next item for the generic column P2_COL_3.
   
   
4. In the Label section, specify the following in the Label field:

   <script>Label(3);</script>
   

   Note that the parameter in each call to the Label function matches the generic column number.
   
   
5. Repeat Steps 3 and 4 for each item that corresponds to a generic column, changing the parameter in the call to the Label function.
   
   
6. After modifying P2_COL_9, click Apply Changes.
   
   
7. To view the final version of the Customers form, click Run.

Figure 8. Final version of the Customers form with tenant-specific column labels for Tenant No. 1

Figure 9. Final version of the Customers form with tenant-specific column labels for Tenant No. 2

Practicum Summary

• Oracle Application Express is a wizard-based application development tool that makes it easy to build and maintain Web-based database applications.
  
  
• Oracle Application Express creates a parametric application that generates Web pages dynamically at runtime by using PL/SQL routines that read metadata stored in an Oracle database.
  
  
• By extending an application's data model with custom metadata, you can leverage Oracle Application Express' metadata-driven approach to create polymorphic applications that dynamically adapt to specific conditions such as the currently connected user. The use of metadata is key to developing robust SaaS applications.
  
  
• From the perspective of an application developer/vendor, a SaaS application must be easy to maintain. The single version of the demonstration CRM application satisfies the needs of all tenants, and the application relies on a single database schema and set of application data structures that store commingled data for all tenants.
  
  
• A SaaS application must also meet the varying needs of each tenant. The CRM application's authentication and security design ensures that tenants can view only their own data, and the application has full support for tenant-specific customizations without any custom application code.
  
  
• Across the SaaS application development process, application efficiency and tuning are key considerations you must consider up front. The use of bind variables, proper data access structures (indexes, index-organized tables, and so on), application-side global variables, and other means to reduce the overall overhead of application functionality help make SaaS applications scale as more and more users subscribe to the service.

SaaS Application Hosting and Maintenance

Availability

The uptime to which a SaaS provider can commit depends primarily on the hardware and database systems the provider decides to use. Figure 10 illustrates some key features of the Oracle technology stack that a serious SaaS provider might typically use to help guarantee service availability.

Figure 10. Final version of the Customers form with tenant-specific column labels for Tenant No. 2

• Redundant array of independent disks (RAID) storage architectures are commonplace in mission-critical systems. By striping and mirroring data blocks across multiple disks, RAID helps decrease data access times and eliminate single points of failure with respect to physical storage. Oracle Database's Automatic Storage Management (ASM) feature is a database-centric file system and volume manager that Oracle shops can rely on to more easily manage and help improve the availability of Oracle-related database files.
  
  
• High-availability clusters, or failover clusters, are hardware and software implementations that can help ensure the availability of dependent services by providing multiple, redundant system components. For example, Oracle Real Application Clusters (Oracle RAC) is a feature of Oracle Database that enables multiple nodes executing Oracle Database software to concurrently access the same shared database; if one node were to fail because of an operating system, software, or hardware problem, the physical database would still be accessible via the remaining intact nodes. Oracle RAC also provides support for rolling upgrades to help reduce planned downtime. With a rolling upgrade, you can take down one node at a time in an Oracle RAC configuration and upgrade or patch it, all while other nodes in the cluster continue to provide access to the underlying database.
  
  
• SaaS providers typically need to design and implement a disaster recovery plan that encompasses the data, hardware, and software necessary to support normal business operations in the event of a complete site disaster due to catastrophic events such as a fire, earthquake, or worse. Disaster recovery strategies typically configure one or more remote standby systems and maintain them in real time to allow for immediate failover should a complete site disaster render the primary site unavailable. Oracle Data Guard is a feature of Oracle Database that supports the configuration and automatic maintenance of standby databases to support disaster recovery plans.

Advanced database features such as ASM, Oracle RAC, and Oracle Data Guard are not the only features of a database system that can help maintain high availability. At a more basic level, routine data backups cannot noticeably detract from the availability or performance of a SaaS applications on which customers depend to manage their daily business operations. Oracle Database and its companion Recovery Manager (RMAN) utility provide a comprehensive set of database backup features perfect for SaaS vendors, including online (hot), incremental, and parallelized database backups. Complementary RMAN database recovery features such as online tablespace, datafile, and block-level recovery can also help ensure the general availability of SaaS applications when physical media failures somehow evade even the best-planned redundancy defenses.

But perhaps the most probable and trying data availability circumstances a SaaS vendor must prepare for are tenant-specific data problems. For example, what will you do when a tenant of your CRM application mistakenly deletes all customers and requests an immediate data recovery operation? It is certainly not reasonable to expect that, due to the misfortunes of one tenant, all other tenants of the SaaS system should have to suffer downtime for a database recovery and lose the work of their committed transactions. Oracle Database has a vast array of logical data recovery features that can help SaaS providers recover from inevitable events such as user errors, faulty batch jobs, and erroneous transactions. For example, in the scenario above, you could use Oracle Database's easy-to-use Flashback Transaction Query feature to undo the effects of an erroneous transaction and recover tenant-specific rows in a table while other tenants continue to use the application and its underlying tables without any interruption whatsoever.

Scalability

Many features great and small in the stack of products used for a SaaS implementation can contribute significantly to the scalability of the system as a whole. For example, consider how something as simple and well documented as the use of bind variables in SQL statements can help improve a SaaS application's ability to scale for high transaction rates. When a SQL statement's WHERE clause uses bind variables (parameters) and runtime value substitution, the database system parses, optimizes, and then caches the compiled statement in memory to minimize the amount of CPU and memory necessary to execute the statement for repeated calls. Without bind variables, the database system is constantly wasting a significant percentage of CPU time and server-side memory on recompiling virtually identical SQL statements again and again on behalf of standard transactions.

Data partitioning is another database feature that substantial SaaS applications will no doubt make use of extensively to improve scalability. Partitioning is a storage option that physically separates data within one table. For example, in our demonstration CRM application, you can use Oracle Database to create the CUSTOMERS table so that the database stores the rows of each tenant in separate partitions. From the perspective of performance and scalability, good partitioning layouts can help reduce or altogether eliminate physical contention for data access among tenants. From the application developer's perspective, the logical structure of partitioned and nonpartitioned versions of a table are identical, so no additional skills are needed to take advantage of partitioning.

Another unique scalability consideration in a SaaS environment is figuring out how to govern the resource usage for each tenant sharing an application instance. For example, one very active tenant should not be able to hoard a database server's CPU time to the extent that it hampers the experience for other tenants sharing the same application instance. Oracle Database's Resource Manager feature provides an elegant way to fairly partition access to systemwide resources such as CPU among tenants of an application.

Once you are sure the underlying mechanisms of an application are efficient and scalable, load balancing and grid computing are hardware-oriented approaches you can leverage to make even the most subscribed SaaS applications scale seemingly infinitely. For example, you might use a virtual HTTP server that all tenant users reference to connect to a SaaS application. Unbeknownst to the user, a load balancer redirects page requests to a midtier Oracle Application Server to handle the requests. Each application server, in turn, is tied to a specific instance in an Oracle RAC configuration. If the requests of a specific tenant are associated with a specific application server/database instance avenue for database access, each tenant's data will remain in the buffer cache of a particular database instance, which will further improve the scalability of the application among multiple tenants. As more and more tenants subscribe to a service, you can add more application servers and database instances to the load balancing configuration.

Data and Application Administration

• A tenant wants a staging instance of an application, commonly called a sandbox, for testing new customizations before moving them to the production application instance.
• A tenant with extraordinary processing loads requires that you migrate its data from a shared application instance to a dedicated instance in exchange for higher subscription fees.
• A tenant wants daily data dumps of its data in XML format to ease its concerns over the loss of data control.

The provisioning and maintenance of applications are also key topics a SaaS vendor must anticipate. With Oracle Application Express, many common application maintenance steps are straightforward. For example, once you've built an Oracle Application Express application, you can package its definition and supporting objects into one file, upload the file to another server, and use a wizard to install the application in the new instance. A future version of Oracle Application Express will also ship with a supporting objects feature that will make it simple to distribute and apply updates to existing applications.

Security

To start with, all data transmission into and out of an on-demand software service managing critical customer data must be encrypted to protect the integrity of the data. All Web browser traffic should use the secure HTTPS protocol rather than HTTP, Telnet and FTP access should be disabled in favor of SSH and SFTP, and any direct Oracle database connections should use encrypted Oracle Net connections (enabled with the Oracle Advanced Security option).

The physical storage of confidential business information (CBI) in an encrypted format can help protect sensitive data in the event of media theft. For example, suppose a SaaS vendor encrypts sensitive data in its CRM database, backs up the database, and stores its backup tapes (but not the keys for decrypting the data) remotely at a third-party storage facility. Because all business-critical data is encrypted, a data thief cannot use a stolen backup tape to restore the database and view confidential business information. Oracle Database's Advanced Security option has a feature called Transparent Data Encryption (TDE) that you can use to encrypt selected columns in a database and protect CBI from prying eyes in all downstream components (backups, redo logs, and the like).

The possibility of a data breach from within a hosting facility is definitely something that every SaaS vendor must consider and address. Once a vendor implements, debugs, and validates a SaaS application, there is no good reason that justifies continued data access for developers and administrators of the production service—in other words, only customers should be able to see their data once the vendor deploys a SaaS application. A SaaS vendor can use Oracle Database Vault to restrict data access to database administrators, application administrators, and application developers.

Conclusion

• How SaaS applications can save money for software consumers and generate more revenues for application vendors
• Some basic SaaS concepts, including multitenancy and parametric applications
• How efficient SaaS applications use metadata to dynamically adjust their runtime behavior
• Some basic application development skills with Oracle Application Express, including the use of wizards, command-line tools, utilities, PL/SQL and JavaScript coding, and much more
• Many of the features of Oracle Database an independent software vendor can use to deliver reliable, scalable, maintainable, and secure SaaS applications