Example: Refresh data mart data in partitions and tables

This example shows how to refresh data mart data in a fact table partition and in a dimension table. It also shows how to refresh data mart data by adding or dropping a fact table partition.

This example uses a data mart that has three database tables:

Dimension table customer contains data for the customer ID, the customer name, and the customer's age. The customer table is not fragmented.
Dimension table store contains data for the store ID and the city where the store is located. The store table is not fragmented.
Fact table sales contains data for the revenue volume that a customer made in a store. The sales table is fragmented by country where the store is located.

In this example, you run a query on the data mart that lists per store, the average age of the customers and the total volume of the store. The list is sorted by the total volume of the stores in descending order.

You refresh the data in the data mart for the following changes:

Adding new data for a specific store to the fact table
Updating a dimension table
Adding a store
Removing a store

Prerequisite

$ dbaccess database $INFORMIXDIR/etc/boot1170XC5iwa.sql

Create the tables, insert the data, create the data mart, and run the query

Create the tables in the database:

create table customer(
	customer_id int primary key,
	customer_name varchar(32),
	customer_age int);
create table store(
	store_id int primary key,
	store_city varchar(18));
create table sales(
	customer int references customer(customer_id),
	store int references store(store_id),
	volume decimal(9,2))
	fragment by expression
		partition us      (store = 101) in dbs1,
		partition india   (store = 102) in dbs2,
		partition germany (store = 103) in dbs3;

Insert data into the three tables:

insert into customer values (1, "John Smith", 22);
insert into customer values (2, "Joe Smith", 32);
insert into customer values (3, "John Doe", 57);

insert into store values (101, "San Jose");
insert into store values (102, "New Delhi");
insert into store values (103, "Munich");

insert into sales values (1, 101, 20.22);
insert into sales values (2, 101, 80.24);
insert into sales values (3, 102, 34.34);
insert into sales values (1, 103, 23.28);
insert into sales values (3, 101, 20.22);
insert into sales values (3, 102, 80.24);
insert into sales values (1, 103, 34.34);
insert into sales values (2, 101, 23.28);

Create a data mart called salesmart on accelerator myAccelerator. Use the following schema for the data mart:

customer  -->  sales  <--  store
relationships:
(sales.customer = customer.customer_id) and (sales.store = store.store)

Run the query:

set environment use_dwa 'accelerate on';
select
    store_city as city,
    avg(customer_age)::integer as avg_age,
    sum(volume) as total_volume
    from sales,customer,store
    where sales.customer = customer.customer_id
      and sales.store = store.store_id
    group by store_city
    order by 3 desc;

Query results:

city                   avg_age     total_volume 
San Jose                    35           143.96
New Delhi                   57           114.58
Munich                      22            57.62

Refresh the data in a fact table partition

Add new data for a specific store to the fact table.

Load new data for New Delhi store:

insert into sales values (1, 102, 98.23);
insert into sales values (2, 102, 13.23);
insert into sales values (3, 102, 87.23);

Update the data mart fact table partition:

execute function dropPartMart('myAccelerator','salesmart','table_owner',
'sales','india');
execute function loadPartMart('myAccelerator','salesmart','table_owner',
'sales','india');

Run the query:

select
    store_city as city,
    avg(customer_age)::integer as avg_age,
    sum(volume) as total_volume
    from sales,customer,store
    where sales.customer = customer.customer_id
      and sales.store = store.store_id
    group by store_city
    order by 3 desc;

The data for New Delhi is updated in the query results.

city                   avg_age     total_volume 
New Delhi                   45           313.27
San Jose                    35           143.96
Munich                      22            57.62

Refresh the data in a dimension table

Update a dimension table.

Update the data in the table customer:

update customer
	set customer_age = customer_age + 1
	where customer_name = 'John Smith';

Update the data mart dimension table:

execute function dropPartMart('myAccelerator','salesmart','table_owner',
'customer','customer');
execute function loadPartMart('myAccelerator','salesmart','table_owner',
'customer','customer');

Run the query:

select
    store_city as city,
    avg(customer_age)::integer as avg_age,
    sum(volume) as total_volume
    from sales,customer,store
    where sales.customer = customer.customer_id
      and sales.store = store.store_id
    group by store_city
    order by 3 desc;

The data for the avg_age column is updated in the query results.

city                   avg_age     total_volume 
New Delhi                   45           313.27
San Jose                    36           143.96
Munich                      23            57.62

Add a fact table partition

Add a store for Sydney, Australia.

Create a stand-alone table, sales_australia that has the new data. Attach the sales_australia table to the sales fact table as a fragment.

insert into store values(104, "Sydney");
create table sales_australia(
	customer int references customer(customer_id),
	store int references store(store_id),
	volume decimal(9,2)) in dbs4;
alter table sales_australia add constraint check (store=104);
insert into sales_australia values (1, 104, 28.17);
insert into sales_australia values (3, 104, 47.88);
insert into sales_australia values (1, 104, 19.12);
insert into sales_australia values (2, 104, 66.09);
alter fragment on table sales
	attach sales_australia
	as partition australia (store = 104)
	after germany;

Update the data mart dimension table store:

execute function dropPartMart('myAccelerator','salesmart','table_owner',
'store','store');
execute function loadPartMart('myAccelerator','salesmart','table_owner',
'store','store');

Load the data mart fact table partition australia to the fact table sales:

execute function loadPartMart('myAccelerator','salesmart','table_owner',
'sales','australia');

Run the query:

select
    store_city as city,
    avg(customer_age)::integer as avg_age,
    sum(volume) as total_volume
    from sales,customer,store
    where sales.customer = customer.customer_id
      and sales.store = store.store_id
    group by store_city
    order by 3 desc;

The data for the city Sydney is included in the query results.

city                   avg_age     total_volume 
New Delhi                   45           313.27
Sydney                      33           161.26
San Jose                    36           143.96
Munich                      23            57.62

Remove a fact table partition

Remove the store in Munich, Germany.

Drop the data mart germany partition from the sales fact table:

execute function dropPartMart('myAccelerator','salesmart','table_owner',
'sales','germany');

Detach the corresponding fragment from the sales fact table in the database and delete the germany data:

alter fragment on table sales detach germany sales_germany;
delete from store where store_city='Munich';

Update the data mart dimension table store:

execute function dropPartMart('myAccelerator','salesmart','table_owner',
'store','store');
execute function loadPartMart('myAccelerator','salesmart','table_owner',
'store','store');

Run the query:

select
    store_city as city,
    avg(customer_age)::integer as avg_age,
    sum(volume) as total_volume
    from sales,customer,store
    where sales.customer = customer.customer_id
      and sales.store = store.store_id
    group by store_city
    order by 3 desc;

The data for the city Munich is removed from the query results.

city                   avg_age     total_volume 
New Delhi                   45           313.27
Sydney                      33           161.26
San Jose                    36           143.96