LucidDbMondrianReplication

From Eigenpedia

Jump to: navigation, search

Contents

Overview

NOTE: The procedure described in this page is available starting with the 0.7.1 release of LucidDB.

If you are already using Mondrian with another DBMS, and would like to test out the Mondrian/LucidDB combination with your data, you can use the automatic replication procedure described in this page to save a lot of time.

Here's the picture initially:

Image:MondrianReplicationBefore.png

Replication will copy both metadata and data from your old DBMS into LucidDB (guided by your Mondrian schema .xml file), creating a logically identical structure:

Image:MondrianReplicationDuring.png

Once replication is complete, you can edit your Mondrian configuration to source from the data in LucidDB (instead of the old DBMS). Callers to Mondrian should see the exact same MDX query results (the Mondrian schema .xml does not need to be modified at all, only the JDBC connection information):

The replication is a one-time transfer. If the data changes in your old DBMS, you'll need to rerun the replication (or at least the data copy portion) in order to refresh LucidDB.

Usage Instructions

This section explains the steps required. As an example, we'll use the Derby FOODMART database which comes with Mondrian; details may vary according to DBMS. The procedure has been tested on 32-bit Linux with both Derby and MySQL using FOODMART; if you have trouble with a different DBMS, OS, or schema, send details to the luciddb-users mailing list @lists.sf.net.

Create Link from LucidDB to Old DBMS

Assuming you've already connected to LucidDB as the sa account, the first step is to tell LucidDB where your old DBMS is so that metadata and data can be fetched via JDBC during replication. This is done via the CREATE SERVER DDL command. Here's how it looks for Derby (change the path to wherever you unpacked Mondrian):

CREATE SERVER DERBY_FOODMART
FOREIGN DATA WRAPPER SYS_JDBC
OPTIONS(
    DRIVER_CLASS 'org.apache.derby.jdbc.EmbeddedDriver',
    URL 'jdbc:derby:/path/to/mondrian/demo/derby/foodmart',
    SCHEMA_NAME 'DEFAULT',
    USER_NAME 'sa',
    PASSWORD 'sa');

And MySQL:

CREATE SERVER MYSQL_FOODMART
FOREIGN DATA WRAPPER sys_jdbc 
OPTIONS ( 
    driver_class 'com.mysql.jdbc.Driver', 
    url 'jdbc:mysql://localhost/foodmart', 
    user_name 'foodmart', 
    password 'foodmart', 
    login_timeout '10',
    validation_query 'select 1',
    fetch_size '-2147483648',
    table_types 'TABLE', 
    schema_name 'DEFAULT');

(Before doing this, add the location of your JDBC driver, e.g. derby.jar or mysql-connector-java-x.y.z-bin.jar, to the luciddb/bin/classpath.gen file so that LucidDB can find it. This requires restarting LucidDB.)

If your tables are qualified by a real schema in the old DBMS, omit the SCHEMA_NAME parameter above (DEFAULT is just an artificial name to work around the fact that the FOODMART tables are not qualified by a real schema in Derby and MySQL).

Before continuing, test to make sure that you can access one of your tables via SQL/MED:

0: jdbc:luciddb:> select count(*) from derby_foodmart."DEFAULT"."time_by_day";
+---------+
| EXPR$0  |
+---------+
| 730     |
+---------+
1 row selected (1.986 seconds)

For MySQL:

select count(*) from mysql_foodmart."DEFAULT"."time_by_day";

Create User and Schema

Create a LucidDB schema which will contain the replicated tables:

CREATE SCHEMA FOODMART;

Then create a LucidDB user which Mondrian will authenticate as when it connects to LucidDB:

CREATE USER FOODMART AUTHORIZATION 'Unused' DEFAULT SCHEMA FOODMART;

Finally, grant the new user rights needed for executing the replication process:

GRANT EXECUTE 
ON SPECIFIC PROCEDURE APPLIB.REPLICATE_MONDRIAN
TO FOODMART;
GRANT EXECUTE 
ON SPECIFIC PROCEDURE APPLIB.COMPUTE_STATISTICS_FOR_SCHEMA
TO FOODMART;

Run Replication Procedure

Before continuing, you must disconnect from LucidDB and reconnect as the new user (e.g. FOODMART in our running example). If you're using sqlline, specify this via the -n command, e.g. sqllineClient -n FOODMART.

Now, execute the replication command. For a big database, this may take a while (Foodmart takes only a minute or so). Read the notes below first to decide whether to pass TRUE or FALSE for the last parameter.

-- Run the replication
CALL APPLIB.REPLICATE_MONDRIAN(
    '/path/to/mondrian/demo/FoodMart.xml',
    'DERBY_FOODMART',
    'DEFAULT',
    'FOODMART',
    '/path/to/write/script.sql',
    TRUE);
-- ANALYZE the replicated data in all tables so that LucidDB's optimizer will have statistics
CALL APPLIB.COMPUTE_STATISTICS_FOR_SCHEMA('FOODMART');

Notes on the parameters to APPLIB.REPLICATE_MONDRIAN:

  1. /path/to/mondrian/demo/FoodMart.xml: use the location of your Mondrian schema .xml
  2. DERBY_FOODMART: supply whatever name you used in CREATE SERVER earlier
  3. DEFAULT: use the name of the schema in the old DBMS, or (if the old DBMS doesn't support schemas) whatever you specified for SCHEMA_NAME earlier in CREATE SERVER
  4. FOODMART: use the name of the LucidDB schema created above
  5. /path/to/write/script.sql: specify the name of a file where LucidDB can save a SQL script of the replication steps for future use; make sure the containing /path/to/write directory exists
  6. TRUE: this means copy the data, not just the metadata

Since copying the data can take a long time, and errors are always possible, an option is to pass FALSE for the last parameter. This will just replicate the table definitions and generate the .sql script without executing the INSERT statements. Then, run just the INSERT statements from the script.

If you get an error, check the last command in the generated script to see which step it was on. If you are using sqlline, issue these commands:

!verbose on
!set shownestederrs on

and then re-execute the failed command to get more diagnostics. TODO: link to general troubleshooting guide.

Reconfigure Mondrian

Once replication has been successfully performed, follow the Mondrian setup instructions to reconfigure Mondrian, using

  • JDBC driver jar: luciddb/plugin/LucidDbClient.jar
  • JDBC driver class: com.lucidera.jdbc.LucidDbRmiDriver
  • JDBC URL: jdbc:luciddb:rmi://localhost (or wherever your LucidDB server is if not on the same machine as Mondrian)
  • JDBC user: FOODMART (or whatever LucidDB user you created above)

You should now be able to test an MDX query with Mondrian fetching data from LucidDB.

How It Works

This section explains the steps performed by the automatic replication procedure in detail, using examples from the generated .sql script to see what it is doing.

Mondrian Schema Parse

The first step of the replication is to parse the Mondrian schema .xml file. The procedure looks for tables referenced by hierarchies and cubes and collects the names of those tables. Only those referenced tables will replicated. Currently, aggregate tables are never replicated.

If tables are qualified by schemas, all referenced tables must be in the same schema, and that schema qualifier must match the foreignSchemaName argument passed to the procedure.

Besides tables, the parser also looks for references to the following kinds of columns (their effect on replication is explained further on):

  • Hierarchy primaryKey
  • DimensionUsage foreignKey
  • Level column

Table Definition Copy

The next step is to copy the definition of each replicated table from the old DBMS into LucidDB. This is accomplished via LucidDB's SQL/MED metadata extraction capability. For each referenced table, a corresponding "linked" foreign table is created in the LucidDB schema, with column definitions derived from those in the old DBMS via JDBC metadata supplied by the old DBMS's driver. (In some cases, datatypes may be adjusted to match LucidDB's conventions and limits.) The name of this table is "OriginalTableName_link".

Here's an example from FOODMART:

CREATE FOREIGN TABLE "FOODMART"."promotion_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'promotion');

Next, a logically identical column-store table is created in LucidDB with the name "OriginalTableName". (The names must be preserved exactly to avoid needing to modify the Mondrian schema .xml.) The column definitions are identical to the extracted foreign table. If a primaryKey column was found while parsing the Mondrian schema, a corresponding constraint is generated:

CREATE TABLE "FOODMART"."promotion" (
   "promotion_id" INTEGER NOT NULL,
   "promotion_district_id" INTEGER,
   "promotion_name" VARCHAR(30),
   "media_type" VARCHAR(30),
   "cost" DECIMAL(10,4),
   "start_date" TIMESTAMP(0),
   "end_date" TIMESTAMP(0),
   PRIMARY KEY ("promotion_id")
 );

Indexing

The replication process does not copy the physical indexing from the old DBMS. Instead, it chooses indexes appropriate to LucidDB based on DimensionUsage and Level column references:

CREATE INDEX "promotion_media_type_idx"
ON "FOODMART"."promotion"("media_type");
CREATE INDEX "promotion_promotion_name_idx"
ON "FOODMART"."promotion"("promotion_name");
CREATE INDEX "sales_fact_1997_store_id_idx"
ON "FOODMART"."sales_fact_1997"("store_id");

No UNIQUE constraints are replicated.

Data Copy

In LucidDB, copying data from a foreign server into a column-store table is very simple:

INSERT INTO "FOODMART"."promotion" SELECT * FROM "FOODMART"."promotion_link";

The column order is the same in both tables, allowing the procedure to omit the column names here.

Foreign Table Drop

Finally, after the data has been copied, the foreign table link is no longer needed, so it is dropped:

DROP TABLE "FOODMART"."promotion_link";

Example Output

Here's an example of the script generated for Foodmart from Derby. Note that the script might come out slightly different when another DBMS is used; for example, when replicated from MySQL, BOOLEAN columns come out as SMALLINT, and DOUBLE columns come out as REAL.

CREATE FOREIGN TABLE "FOODMART"."store_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'store');
CREATE TABLE "FOODMART"."store" (
   "store_id" INTEGER NOT NULL,
   "store_type" VARCHAR(30),
   "region_id" INTEGER,
   "store_name" VARCHAR(30),
   "store_number" INTEGER,
   "store_street_address" VARCHAR(30),
   "store_city" VARCHAR(30),
   "store_state" VARCHAR(30),
   "store_postal_code" VARCHAR(30),
   "store_country" VARCHAR(30),
   "store_manager" VARCHAR(30),
   "store_phone" VARCHAR(30),
   "store_fax" VARCHAR(30),
   "first_opened_date" TIMESTAMP(0),
   "last_remodel_date" TIMESTAMP(0),
   "store_sqft" INTEGER,
   "grocery_sqft" INTEGER,
   "frozen_sqft" INTEGER,
   "meat_sqft" INTEGER,
   "coffee_bar" SMALLINT,
   "video_store" SMALLINT,
   "salad_bar" SMALLINT,
   "prepared_food" SMALLINT,
   "florist" SMALLINT,
   PRIMARY KEY ("store_id")
 );
CREATE INDEX "store_store_country_idx"
ON "FOODMART"."store"("store_country");
CREATE INDEX "store_store_state_idx"
ON "FOODMART"."store"("store_state");
CREATE INDEX "store_store_city_idx"
ON "FOODMART"."store"("store_city");
CREATE INDEX "store_store_name_idx"
ON "FOODMART"."store"("store_name");
CREATE INDEX "store_store_sqft_idx"
ON "FOODMART"."store"("store_sqft");
CREATE INDEX "store_store_type_idx"
ON "FOODMART"."store"("store_type");
CREATE INDEX "store_coffee_bar_idx"
ON "FOODMART"."store"("coffee_bar");
INSERT INTO "FOODMART"."store" SELECT * FROM "FOODMART"."store_link";
-- Initial load:  25 rows
DROP TABLE "FOODMART"."store_link";
CREATE FOREIGN TABLE "FOODMART"."time_by_day_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'time_by_day');
CREATE TABLE "FOODMART"."time_by_day" (
   "time_id" INTEGER NOT NULL,
   "the_date" TIMESTAMP(0),
   "the_day" VARCHAR(30),
   "the_month" VARCHAR(30),
   "the_year" SMALLINT,
   "day_of_month" SMALLINT,
   "week_of_year" INTEGER,
   "month_of_year" SMALLINT,
   "quarter" VARCHAR(30),
   "fiscal_period" VARCHAR(30),
   PRIMARY KEY ("time_id","the_date")
 );
CREATE INDEX "time_by_day_the_year_idx"
ON "FOODMART"."time_by_day"("the_year");
CREATE INDEX "time_by_day_quarter_idx"
ON "FOODMART"."time_by_day"("quarter");
CREATE INDEX "time_by_day_month_of_year_idx"
ON "FOODMART"."time_by_day"("month_of_year");
CREATE INDEX "time_by_day_week_of_year_idx"
ON "FOODMART"."time_by_day"("week_of_year");
CREATE INDEX "time_by_day_day_of_month_idx"
ON "FOODMART"."time_by_day"("day_of_month");
INSERT INTO "FOODMART"."time_by_day" SELECT * FROM "FOODMART"."time_by_day_link";
-- Initial load:  730 rows
DROP TABLE "FOODMART"."time_by_day_link";
CREATE FOREIGN TABLE "FOODMART"."product_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'product');
CREATE TABLE "FOODMART"."product" (
   "product_class_id" INTEGER NOT NULL,
   "product_id" INTEGER NOT NULL,
   "brand_name" VARCHAR(60),
   "product_name" VARCHAR(60) NOT NULL,
   "SKU" BIGINT NOT NULL,
   "SRP" DECIMAL(10,4),
   "gross_weight" REAL,
   "net_weight" REAL,
   "recyclable_package" SMALLINT,
   "low_fat" SMALLINT,
   "units_per_case" SMALLINT,
   "cases_per_pallet" SMALLINT,
   "shelf_width" REAL,
   "shelf_height" REAL,
   "shelf_depth" REAL,
   PRIMARY KEY ("product_id")
 );
CREATE INDEX "product_brand_name_idx"
ON "FOODMART"."product"("brand_name");
CREATE INDEX "product_product_name_idx"
ON "FOODMART"."product"("product_name");
INSERT INTO "FOODMART"."product" SELECT * FROM "FOODMART"."product_link";
-- Initial load:  1560 rows
DROP TABLE "FOODMART"."product_link";
CREATE FOREIGN TABLE "FOODMART"."product_class_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'product_class');
CREATE TABLE "FOODMART"."product_class" (
   "product_class_id" INTEGER NOT NULL,
   "product_subcategory" VARCHAR(30),
   "product_category" VARCHAR(30),
   "product_department" VARCHAR(30),
   "product_family" VARCHAR(30)
 );
CREATE INDEX "product_class_product_family_idx"
ON "FOODMART"."product_class"("product_family");
CREATE INDEX "product_class_product_department_idx"
ON "FOODMART"."product_class"("product_department");
CREATE INDEX "product_class_product_category_idx"
ON "FOODMART"."product_class"("product_category");
CREATE INDEX "product_class_product_subcategory_idx"
ON "FOODMART"."product_class"("product_subcategory");
INSERT INTO "FOODMART"."product_class" SELECT * FROM "FOODMART"."product_class_link";
-- Initial load:  110 rows
DROP TABLE "FOODMART"."product_class_link";
CREATE FOREIGN TABLE "FOODMART"."warehouse_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'warehouse');
CREATE TABLE "FOODMART"."warehouse" (
   "warehouse_id" INTEGER NOT NULL,
   "warehouse_class_id" INTEGER,
   "stores_id" INTEGER,
   "warehouse_name" VARCHAR(60),
   "wa_address1" VARCHAR(30),
   "wa_address2" VARCHAR(30),
   "wa_address3" VARCHAR(30),
   "wa_address4" VARCHAR(30),
   "warehouse_city" VARCHAR(30),
   "warehouse_state_province" VARCHAR(30),
   "warehouse_postal_code" VARCHAR(30),
   "warehouse_country" VARCHAR(30),
   "warehouse_owner_name" VARCHAR(30),
   "warehouse_phone" VARCHAR(30),
   "warehouse_fax" VARCHAR(30),
   PRIMARY KEY ("warehouse_id")
 );
CREATE INDEX "warehouse_warehouse_country_idx"
ON "FOODMART"."warehouse"("warehouse_country");
CREATE INDEX "warehouse_warehouse_state_province_idx"
ON "FOODMART"."warehouse"("warehouse_state_province");
CREATE INDEX "warehouse_warehouse_city_idx"
ON "FOODMART"."warehouse"("warehouse_city");
CREATE INDEX "warehouse_warehouse_name_idx"
ON "FOODMART"."warehouse"("warehouse_name");
INSERT INTO "FOODMART"."warehouse" SELECT * FROM "FOODMART"."warehouse_link";
-- Initial load:  24 rows
DROP TABLE "FOODMART"."warehouse_link";
CREATE FOREIGN TABLE "FOODMART"."sales_fact_1997_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'sales_fact_1997');
CREATE TABLE "FOODMART"."sales_fact_1997" (
   "product_id" INTEGER NOT NULL,
   "time_id" INTEGER NOT NULL,
   "customer_id" INTEGER NOT NULL,
   "promotion_id" INTEGER NOT NULL,
   "store_id" INTEGER NOT NULL,
   "store_sales" DECIMAL(10,4) NOT NULL,
   "store_cost" DECIMAL(10,4) NOT NULL,
   "unit_sales" DECIMAL(10,4) NOT NULL
 );
CREATE INDEX "sales_fact_1997_store_id_idx"
ON "FOODMART"."sales_fact_1997"("store_id");
CREATE INDEX "sales_fact_1997_time_id_idx"
ON "FOODMART"."sales_fact_1997"("time_id");
CREATE INDEX "sales_fact_1997_product_id_idx"
ON "FOODMART"."sales_fact_1997"("product_id");
INSERT INTO "FOODMART"."sales_fact_1997" SELECT * FROM "FOODMART"."sales_fact_1997_link";
-- Initial load:  86837 rows
DROP TABLE "FOODMART"."sales_fact_1997_link";
CREATE FOREIGN TABLE "FOODMART"."promotion_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'promotion');
CREATE TABLE "FOODMART"."promotion" (
   "promotion_id" INTEGER NOT NULL,
   "promotion_district_id" INTEGER,
   "promotion_name" VARCHAR(30),
   "media_type" VARCHAR(30),
   "cost" DECIMAL(10,4),
   "start_date" TIMESTAMP(0),
   "end_date" TIMESTAMP(0),
   PRIMARY KEY ("promotion_id")
 );
CREATE INDEX "promotion_media_type_idx"
ON "FOODMART"."promotion"("media_type");
CREATE INDEX "promotion_promotion_name_idx"
ON "FOODMART"."promotion"("promotion_name");
INSERT INTO "FOODMART"."promotion" SELECT * FROM "FOODMART"."promotion_link";
-- Initial load:  1864 rows
DROP TABLE "FOODMART"."promotion_link";
CREATE FOREIGN TABLE "FOODMART"."customer_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'customer');
CREATE TABLE "FOODMART"."customer" (
   "customer_id" INTEGER NOT NULL,
   "account_num" BIGINT NOT NULL,
   "lname" VARCHAR(30) NOT NULL,
   "fname" VARCHAR(30) NOT NULL,
   "mi" VARCHAR(30),
   "address1" VARCHAR(30),
   "address2" VARCHAR(30),
   "address3" VARCHAR(30),
   "address4" VARCHAR(30),
   "city" VARCHAR(30),
   "state_province" VARCHAR(30),
   "postal_code" VARCHAR(30) NOT NULL,
   "country" VARCHAR(30) NOT NULL,
   "customer_region_id" INTEGER NOT NULL,
   "phone1" VARCHAR(30) NOT NULL,
   "phone2" VARCHAR(30) NOT NULL,
   "birthdate" DATE NOT NULL,
   "marital_status" VARCHAR(30) NOT NULL,
   "yearly_income" VARCHAR(30) NOT NULL,
   "gender" VARCHAR(30) NOT NULL,
   "total_children" SMALLINT NOT NULL,
   "num_children_at_home" SMALLINT NOT NULL,
   "education" VARCHAR(30) NOT NULL,
   "date_accnt_opened" DATE NOT NULL,
   "member_card" VARCHAR(30),
   "occupation" VARCHAR(30),
   "houseowner" VARCHAR(30),
   "num_cars_owned" INTEGER,
   "fullname" VARCHAR(60) NOT NULL,
   PRIMARY KEY ("customer_id")
 );
CREATE INDEX "customer_country_idx"
ON "FOODMART"."customer"("country");
CREATE INDEX "customer_state_province_idx"
ON "FOODMART"."customer"("state_province");
CREATE INDEX "customer_city_idx"
ON "FOODMART"."customer"("city");
CREATE INDEX "customer_education_idx"
ON "FOODMART"."customer"("education");
CREATE INDEX "customer_gender_idx"
ON "FOODMART"."customer"("gender");
CREATE INDEX "customer_marital_status_idx"
ON "FOODMART"."customer"("marital_status");
CREATE INDEX "customer_yearly_income_idx"
ON "FOODMART"."customer"("yearly_income");
INSERT INTO "FOODMART"."customer" SELECT * FROM "FOODMART"."customer_link";
-- Initial load:  10281 rows
DROP TABLE "FOODMART"."customer_link";
CREATE FOREIGN TABLE "FOODMART"."inventory_fact_1997_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'inventory_fact_1997');
CREATE TABLE "FOODMART"."inventory_fact_1997" (
   "product_id" INTEGER NOT NULL,
   "time_id" INTEGER,
   "warehouse_id" INTEGER,
   "store_id" INTEGER,
   "units_ordered" INTEGER,
   "units_shipped" INTEGER,
   "warehouse_sales" DECIMAL(10,4),
   "warehouse_cost" DECIMAL(10,4),
   "supply_time" SMALLINT,
   "store_invoice" DECIMAL(10,4)
 );
CREATE INDEX "inventory_fact_1997_store_id_idx"
ON "FOODMART"."inventory_fact_1997"("store_id");
CREATE INDEX "inventory_fact_1997_time_id_idx"
ON "FOODMART"."inventory_fact_1997"("time_id");
CREATE INDEX "inventory_fact_1997_product_id_idx"
ON "FOODMART"."inventory_fact_1997"("product_id");
CREATE INDEX "inventory_fact_1997_warehouse_id_idx"
ON "FOODMART"."inventory_fact_1997"("warehouse_id");
INSERT INTO "FOODMART"."inventory_fact_1997" SELECT * FROM "FOODMART"."inventory_fact_1997_link";
-- Initial load:  4070 rows
DROP TABLE "FOODMART"."inventory_fact_1997_link";
CREATE FOREIGN TABLE "FOODMART"."salary_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'salary');
CREATE TABLE "FOODMART"."salary" (
   "pay_date" TIMESTAMP(0) NOT NULL,
   "employee_id" INTEGER NOT NULL,
   "department_id" INTEGER NOT NULL,
   "currency_id" INTEGER NOT NULL,
   "salary_paid" DECIMAL(10,4) NOT NULL,
   "overtime_paid" DECIMAL(10,4) NOT NULL,
   "vacation_accrued" REAL NOT NULL,
   "vacation_used" REAL NOT NULL
 );
INSERT INTO "FOODMART"."salary" SELECT * FROM "FOODMART"."salary_link";
-- Initial load:  21252 rows
DROP TABLE "FOODMART"."salary_link";
CREATE FOREIGN TABLE "FOODMART"."employee_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'employee');
CREATE TABLE "FOODMART"."employee" (
   "employee_id" INTEGER NOT NULL,
   "full_name" VARCHAR(30) NOT NULL,
   "first_name" VARCHAR(30) NOT NULL,
   "last_name" VARCHAR(30) NOT NULL,
   "position_id" INTEGER,
   "position_title" VARCHAR(30),
   "store_id" INTEGER NOT NULL,
   "department_id" INTEGER NOT NULL,
   "birth_date" DATE NOT NULL,
   "hire_date" TIMESTAMP(0),
   "end_date" TIMESTAMP(0),
   "salary" DECIMAL(10,4) NOT NULL,
   "supervisor_id" INTEGER,
   "education_level" VARCHAR(30) NOT NULL,
   "marital_status" VARCHAR(30) NOT NULL,
   "gender" VARCHAR(30) NOT NULL,
   "management_role" VARCHAR(30)
 );
CREATE INDEX "employee_employee_id_idx"
ON "FOODMART"."employee"("employee_id");
CREATE INDEX "employee_management_role_idx"
ON "FOODMART"."employee"("management_role");
CREATE INDEX "employee_position_title_idx"
ON "FOODMART"."employee"("position_title");
INSERT INTO "FOODMART"."employee" SELECT * FROM "FOODMART"."employee_link";
-- Initial load:  1155 rows
DROP TABLE "FOODMART"."employee_link";
CREATE FOREIGN TABLE "FOODMART"."position_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'position');
CREATE TABLE "FOODMART"."position" (
   "position_id" INTEGER NOT NULL,
   "position_title" VARCHAR(30) NOT NULL,
   "pay_type" VARCHAR(30) NOT NULL,
   "min_scale" DECIMAL(10,4) NOT NULL,
   "max_scale" DECIMAL(10,4) NOT NULL,
   "management_role" VARCHAR(30) NOT NULL
 );
CREATE INDEX "position_pay_type_idx"
ON "FOODMART"."position"("pay_type");
INSERT INTO "FOODMART"."position" SELECT * FROM "FOODMART"."position_link";
-- Initial load:  18 rows
DROP TABLE "FOODMART"."position_link";
CREATE FOREIGN TABLE "FOODMART"."department_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'department');
CREATE TABLE "FOODMART"."department" (
   "department_id" INTEGER NOT NULL,
   "department_description" VARCHAR(30) NOT NULL
 );
CREATE INDEX "department_department_id_idx"
ON "FOODMART"."department"("department_id");
INSERT INTO "FOODMART"."department" SELECT * FROM "FOODMART"."department_link";
-- Initial load:  12 rows
DROP TABLE "FOODMART"."department_link";
CREATE FOREIGN TABLE "FOODMART"."employee_closure_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'employee_closure');
CREATE TABLE "FOODMART"."employee_closure" (
   "employee_id" INTEGER NOT NULL,
   "supervisor_id" INTEGER NOT NULL,
   "distance" INTEGER
 );
INSERT INTO "FOODMART"."employee_closure" SELECT * FROM "FOODMART"."employee_closure_link";
-- Initial load:  7179 rows
DROP TABLE "FOODMART"."employee_closure_link";
CREATE FOREIGN TABLE "FOODMART"."store_ragged_link"
SERVER "DERBY_FOODMART" OPTIONS(SCHEMA_NAME 'DEFAULT', TABLE_NAME 'store_ragged');
CREATE TABLE "FOODMART"."store_ragged" (
   "store_id" INTEGER NOT NULL,
   "store_type" VARCHAR(30),
   "region_id" INTEGER,
   "store_name" VARCHAR(30),
   "store_number" INTEGER,
   "store_street_address" VARCHAR(30),
   "store_city" VARCHAR(30),
   "store_state" VARCHAR(30),
   "store_postal_code" VARCHAR(30),
   "store_country" VARCHAR(30),
   "store_manager" VARCHAR(30),
   "store_phone" VARCHAR(30),
   "store_fax" VARCHAR(30),
   "first_opened_date" TIMESTAMP(0),
   "last_remodel_date" TIMESTAMP(0),
   "store_sqft" INTEGER,
   "grocery_sqft" INTEGER,
   "frozen_sqft" INTEGER,
   "meat_sqft" INTEGER,
   "coffee_bar" SMALLINT,
   "video_store" SMALLINT,
   "salad_bar" SMALLINT,
   "prepared_food" SMALLINT,
   "florist" SMALLINT,
   PRIMARY KEY ("store_id")
 );
CREATE INDEX "store_ragged_store_country_idx"
ON "FOODMART"."store_ragged"("store_country");
CREATE INDEX "store_ragged_store_state_idx"
ON "FOODMART"."store_ragged"("store_state");
CREATE INDEX "store_ragged_store_city_idx"
ON "FOODMART"."store_ragged"("store_city");
CREATE INDEX "store_ragged_store_name_idx"
ON "FOODMART"."store_ragged"("store_name");
INSERT INTO "FOODMART"."store_ragged" SELECT * FROM "FOODMART"."store_ragged_link";
-- Initial load:  25 rows
DROP TABLE "FOODMART"."store_ragged_link";

Enhancements Needed

  • Aggregate tables: Currently, these are omitted by replication, meaning that if you're relying on them in your old DBMS, you'll need to disable them in your Mondrian property settings after replication. Besides just replicating any existing ones, it would be useful for LucidDB to be able to create and load new ones automatically.
  • Incremental refresh: Currently, a refresh requires dropping the LucidDB schema and repeating the entire procedure, moving all data. An incremental procedure would make use of LucidDB's upsert capabilities together with SQL/MED filter-pushdown to only move and update new/modified rows. Of course, eventually you might instead want to take advantage of LucidDB's built-in ETL capabilities as an alternative to replication.
  • Indexes for Join columns: Currently no indexes are generated for join columns in snowflake schemas.
Personal tools