sampledata.script

A complete business intelligence platform that includes reporting, analysis (OLAP), dashboards, data

CREATE MEMORY TABLE QUADRANT_ACTUALS(REGION VARCHAR(50) NOT NULL,DEPARTMENT VARCHAR(50) NOT NULL,POSITIONTITLE VARCHAR(50) NOT NULL,ACTUAL DECIMAL(18,4),BUDGET DECIMAL(18,4),VARIANCE DECIMAL(18,4))
CREATE MEMORY TABLE DEPARTMENT_MANAGERS(REGION VARCHAR(50) NOT NULL,MANAGER_NAME VARCHAR(50) NOT NULL,EMAIL VARCHAR(50))
CREATE MEMORY TABLE CUSTOMERS(CUSTOMERNUMBER INTEGER NOT NULL PRIMARY KEY,CUSTOMERNAME VARCHAR(50) NOT NULL,CONTACTLASTNAME VARCHAR(50) NOT NULL,CONTACTFIRSTNAME VARCHAR(50) NOT NULL,PHONE VARCHAR(50) NOT NULL,ADDRESSLINE1 VARCHAR(50) NOT NULL,ADDRESSLINE2 VARCHAR(50),CITY VARCHAR(50) NOT NULL,STATE VARCHAR(50),POSTALCODE VARCHAR(15),COUNTRY VARCHAR(50) NOT NULL,SALESREPEMPLOYEENUMBER INTEGER,CREDITLIMIT NUMERIC(17))
CREATE MEMORY TABLE EMPLOYEES(EMPLOYEENUMBER INTEGER NOT NULL PRIMARY KEY,LASTNAME VARCHAR(50) NOT NULL,FIRSTNAME VARCHAR(50) NOT NULL,EXTENSION VARCHAR(10) NOT NULL,EMAIL VARCHAR(100) NOT NULL,OFFICECODE VARCHAR(20) NOT NULL,REPORTSTO INTEGER,JOBTITLE VARCHAR(50) NOT NULL)
CREATE MEMORY TABLE OFFICES(OFFICECODE VARCHAR(50) NOT NULL PRIMARY KEY,CITY VARCHAR(50) NOT NULL,PHONE VARCHAR(50) NOT NULL,ADDRESSLINE1 VARCHAR(50) NOT NULL,ADDRESSLINE2 VARCHAR(50),STATE VARCHAR(50),COUNTRY VARCHAR(50) NOT NULL,POSTALCODE VARCHAR(10) NOT NULL,TERRITORY VARCHAR(10) NOT NULL)
CREATE MEMORY TABLE ORDERDETAILS(ORDERNUMBER INTEGER NOT NULL,PRODUCTCODE VARCHAR(50) NOT NULL,QUANTITYORDERED INTEGER NOT NULL,PRICEEACH NUMERIC(17) NOT NULL,ORDERLINENUMBER SMALLINT NOT NULL,CONSTRAINT SYS_PK_ORDERDETAILS PRIMARY KEY(ORDERNUMBER,PRODUCTCODE))
CREATE MEMORY TABLE ORDERS(ORDERNUMBER INTEGER NOT NULL PRIMARY KEY,ORDERDATE TIMESTAMP NOT NULL,REQUIREDDATE TIMESTAMP NOT NULL,SHIPPEDDATE TIMESTAMP,STATUS VARCHAR(15) NOT NULL,COMMENTS LONGVARCHAR,CUSTOMERNUMBER INTEGER NOT NULL)
CREATE MEMORY TABLE PAYMENTS(CUSTOMERNUMBER INTEGER NOT NULL,CHECKNUMBER VARCHAR(50) NOT NULL,PAYMENTDATE TIMESTAMP NOT NULL,AMOUNT NUMERIC(17) NOT NULL,CONSTRAINT SYS_PK_PAYMENTS PRIMARY KEY(CUSTOMERNUMBER,CHECKNUMBER))
CREATE MEMORY TABLE PRODUCTS(PRODUCTCODE VARCHAR(50) NOT NULL PRIMARY KEY,PRODUCTNAME VARCHAR(70) NOT NULL,PRODUCTLINE VARCHAR(50) NOT NULL,PRODUCTSCALE VARCHAR(10) NOT NULL,PRODUCTVENDOR VARCHAR(50) NOT NULL,PRODUCTDESCRIPTION LONGVARCHAR NOT NULL,QUANTITYINSTOCK SMALLINT NOT NULL,BUYPRICE NUMERIC(17) NOT NULL,MSRP NUMERIC(17) NOT NULL)
CREATE MEMORY TABLE TRIAL_BALANCE("Type" CHAR(1),"Account_Num" INTEGER,"Category" VARCHAR(57),"Category2" VARCHAR(57),"Detail" VARCHAR(57),"Amount" INTEGER)
CREATE MEMORY TABLE ORDERFACT(ORDERNUMBER INTEGER,PRODUCTCODE VARCHAR(50),QUANTITYORDERED INTEGER,PRICEEACH DECIMAL(17,30),ORDERLINENUMBER INTEGER,TOTALPRICE REAL,ORDERDATE TIMESTAMP,REQUIREDDATE TIMESTAMP,SHIPPEDDATE TIMESTAMP,STATUS VARCHAR(15),COMMENTS LONGVARCHAR,CUSTOMERNUMBER INTEGER,TIME_ID VARCHAR(10),QTR_ID BIGINT,MONTH_ID BIGINT,YEAR_ID BIGINT,CONSTRAINT SYS_FK_1 FOREIGN KEY(PRODUCTCODE) REFERENCES PRODUCTS(PRODUCTCODE))
CREATE MEMORY TABLE TIME(TIME_ID VARCHAR(10),MONTH_ID INTEGER,QTR_ID INTEGER,YEAR_ID INTEGER,MONTH_NAME CHAR(3),MONTH_DESC VARCHAR(9),QTR_NAME VARCHAR(4),QTR_DESC VARCHAR(9))
CREATE MEMORY TABLE CUSTOMER_W_TER(CUSTOMERNUMBER INTEGER,CUSTOMERNAME VARCHAR(50),CONTACTLASTNAME VARCHAR(50),CONTACTFIRSTNAME VARCHAR(50),PHONE VARCHAR(50),ADDRESSLINE1 VARCHAR(50),ADDRESSLINE2 VARCHAR(50),CITY VARCHAR(50),STATE VARCHAR(50),POSTALCODE VARCHAR(15),COUNTRY VARCHAR(50),EMPLOYEENUMBER INTEGER,CREDITLIMIT DECIMAL(17,30),TERRITORY VARCHAR(10))
CREATE UNIQUE INDEX "index1" ON CUSTOMER_W_TER(CUSTOMERNUMBER)
CREATE USER SA PASSWORD "" ADMIN
CREATE USER PENTAHO_ADMIN PASSWORD "PASSWORD" ADMIN
CREATE USER PENTAHO_USER PASSWORD "PASSWORD"
GRANT SELECT ON QUADRANT_ACTUALS TO PENTAHO_USER
GRANT SELECT ON DEPARTMENT_MANAGERS TO PENTAHO_USER
GRANT SELECT ON CUSTOMERS TO PENTAHO_USER
GRANT SELECT ON EMPLOYEES TO PENTAHO_USER
GRANT SELECT ON OFFICES TO PENTAHO_USER
GRANT SELECT ON ORDERDETAILS TO PENTAHO_USER
GRANT SELECT ON ORDERS TO PENTAHO_USER
GRANT SELECT ON PAYMENTS TO PENTAHO_USER
GRANT SELECT ON PRODUCTS TO PENTAHO_USER
GRANT SELECT ON TRIAL_BALANCE TO PENTAHO_USER
GRANT SELECT ON ORDERFACT TO PENTAHO_USER
GRANT SELECT ON TIME TO PENTAHO_USER
GRANT SELECT ON CUSTOMER_W_TER TO PENTAHO_USER
SET WRITE_DELAY 20
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Sales','District Manager',682625,617250,-65375)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Sales','Senior Sales Rep',497223,484820,-12403)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Sales','Sales Rep',675975,612500,-63475)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Sales','Account Executive',409975,422500,12525)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Sales','Pre-Sales',649375,593500,-55875)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Executive Management','CEO',549625,522250,-27375)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Executive Management','SVP WW Operations',476000,725887,249887)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Executive Management','SVP Strategic Development',383242,403405,20163)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Executive Management','SVP Partnerships',367415,392100,24685)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Finance','CFO',770272,719855,-50417)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Finance','Administrative Assistant',827861,760990,-66871)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Finance','IS',570759,577346,6587)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Finance','Controller',570373,577070,6697)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Finance','Payroll',367415,432100,64685)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Human Resource','Administration',549625,552250,2625)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Human Resource','SVP HR',574895,570300,-4595)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Human Resource','HR Generalists',856190,771225,-84965)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Human Resource','HR Training',397473,443570,46097)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Human Resource','Sexual Harassment',530473,538570,8097)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Human Resource','EOE',530207,538380,8173)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Marketing & Communication','CMO',827861,760990,-66871)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Marketing & Communication','Product Marketing Mgr',693531,665040,-28491)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Marketing & Communication','Analyst Relations',383375,443500,60125)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Marketing & Communication','Press Relations',497296,524872,27576)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Marketing & Communication','Graphics',782375,728500,-53875)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Marketing & Communication','Writer',405985,459650,53665)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Product Development','CTO',405985,459650,53665)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Product Development','VP Engineering',383242,443405,60163)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Product Development','Senior Engineer',770272,719855,-50417)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Product Development','Engineer',695925,666750,-29175)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Product Development','QA Manager',374863,437420,62557)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Product Development','QA Engineer',367415,432100,64685)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Professional Services','SVP Services',4042220,4180000,137780)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Professional Services','Services Mgr',4401586,4680000,278414)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Professional Services','Senior Consultant',3994859,3980000,-14859)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Professional Services','Staff Consultant',3824426,4180000,355574)
INSERT INTO QUADRANT_ACTUALS VALUES('Central','Professional Services','Trainer',3804948,3380000,-424948)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Sales','District Manager',700000,700250,250)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Sales','Senior Sales Rep',421200,433299,12099)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Sales','Sales Rep',690000,690675,675)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Sales','Account Executive',290000,307675,17675)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Sales','Pre-Sales',650000,652375,2375)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Executive Management','CEO',500000,488750,-11250)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Executive Management','SVP WW Operations',249800,249184,-616)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Executive Management','SVP Strategic Development',226000,226395,395)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Executive Management','SVP Partnerships',531780,519179,-12601)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Finance','CFO',831800,816449,-15351)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Finance','Administrative Assistant',918400,899368,-19032)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Finance','IS',531780,529179,-2601)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Finance','Controller',531200,528624,-2576)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Finance','Payroll',226000,236395,10395)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Human Resource','Administration',500000,498750,-1250)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Human Resource','SVP HR',538000,535135,-2865)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Human Resource','HR Generalists',961000,940158,-20842)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Human Resource','HR Training',271200,279674,8474)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Human Resource','Sexual Harassment',471200,471174,-26)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Human Resource','EOE',470800,470791,-9)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Marketing & Communication','CMO',918400,894368,-24032)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Marketing & Communication','Product Marketing Mgr',716400,700953,-15447)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Marketing & Communication','Analyst Relations',250000,254375,4375)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Marketing & Communication','Press Relations',421310,418404,-2906)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Marketing & Communication','Graphics',850000,828875,-21125)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Marketing & Communication','Writer',284000,286930,2930)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Product Development','CTO',284000,286930,2930)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Product Development','VP Engineering',249800,254184,4384)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Product Development','Senior Engineer',831800,811449,-20351)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Product Development','Engineer',720000,704400,-15600)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Product Development','QA Manager',237200,242119,4919)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Product Development','QA Engineer',226000,231395,5395)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Professional Services','SVP Services',3793000,3900000,107000)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Professional Services','Services Mgr',4333400,4500000,166600)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Professional Services','Senior Consultant',3721780,3900000,178220)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Professional Services','Staff Consultant',3465490,3500000,34510)
INSERT INTO QUADRANT_ACTUALS VALUES('Eastern','Professional Services','Trainer',3436200,3300000,-136200)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Sales','District Manager',700000,690250,-9750)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Sales','Senior Sales Rep',421200,423299,2099)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Sales','Sales Rep',690000,680675,-9325)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Sales','Account Executive',290000,297675,7675)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Sales','Pre-Sales',650000,642375,-7625)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Executive Management','CEO',500000,498750,-1250)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Executive Management','SVP WW Operations',249800,259184,9384)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Executive Management','SVP Strategic Development',226000,236395,10395)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Executive Management','SVP Partnerships',531780,529179,-2601)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Finance','CFO',831800,816449,-15351)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Finance','Administrative Assistant',918400,899368,-19032)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Finance','IS',531780,529179,-2601)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Finance','Controller',531200,528624,-2576)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Finance','Payroll',226000,236395,10395)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Human Resource','Administration',500000,498750,-1250)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Human Resource','SVP HR',538000,535135,-2865)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Human Resource','HR Generalists',961000,940158,-20842)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Human Resource','HR Training',271200,279674,8474)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Human Resource','Sexual Harassment',471200,471174,-26)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Human Resource','EOE',470800,470791,-9)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Marketing & Communication','CMO',918400,897368,-21032)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Marketing & Communication','Product Marketing Mgr',716400,703953,-12447)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Marketing & Communication','Analyst Relations',250000,257375,7375)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Marketing & Communication','Press Relations',421310,421404,94)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Marketing & Communication','Graphics',850000,831875,-18125)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Marketing & Communication','Writer',284000,289930,5930)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Product Development','CTO',284000,289930,5930)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Product Development','VP Engineering',249800,257184,7384)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Product Development','Senior Engineer',831800,814449,-17351)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Product Development','Engineer',720000,707400,-12600)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Product Development','QA Manager',237200,245119,7919)
INSERT INTO QUADRANT_ACTUALS VALUES('Southern','Product Development','QA Engineer',226000,234395,8395)