Tree utilities in SQL Server 2000 and 2005, and OLAP implementations

/**************** STEP 1: FIRST CUBE PREPARATION ******************************/

-- ADD A HIERARCHY INTO THE TREE TABLE

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(1, NULL, 'Tranquilicity')


	INSERT INTO TREE(ID, P_ID, NAME) VALUES(2, 1, 'Miracle dizziacle')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(3, 2, 'Emruad')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(4, 2, 'Geoges')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(5, 4, 'Chill ville')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(6, 4, 'Carystopia')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(7, 4, 'Gosberry village')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(8, 6, 'Icing drip drop')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(9, 6, 'Vlairbor')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(10, 6, 'Loneliness-Ville')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(11, 6, 'Asira')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(12, 6, 'Wird oh just weird')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(13, 6, 'Veridian')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(14, 11, 'Nocity')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(15, 11, 'Aneril')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(16, 11, 'Frozen objects')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(17, 16, 'Lantasia')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(18, 16, 'Boricua')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(19, 16, 'Aribasci')

	INSERT INTO TREE(ID, P_ID, NAME) VALUES(20, 16, 'Tylersville')
GO



-- INSERT RECORDS IN TIME_BY_DAYE TABLE
EXEC dbo.TIME_BY_DAY_GEN 2005
EXEC dbo.TIME_BY_DAY_GEN 2006
GO

-- TREE_OLAP TABLE CREATION
EXEC TREE_2_OLAP 1
GO

-- INSERT INTO THE SALES TABLE THE RECORDS IN THE FILE sales.txt USING THE IMPORT/EXPORT WIZARD

/******************** STOP ! ! ! CREATE Sales CUBE AND CHECK ITS DATA *************************/


/**************** STEP 2: OLAP DATA CHANGES ******************************/

-- ADD A NEW RECORD IN TREE TABLE
INSERT INTO TREE(ID, P_ID, NAME) VALUES(21, 20, 'MamaW')
GO

-- INSERT NEW RECORD ON SALES TABLE BASED ON NEW ADDED RECORD IN TREE TABLE AND A DATE IN 2006
DECLARE @TIME_ID INT
SELECT @TIME_ID = ID FROM TIME_BY_DAY WHERE T_DATE = '2006-07-18'
INSERT INTO SALES VALUES(21, @TIME_ID, 1000)
GO


-- RE-CREATE THE TREE_OLAP TABLE  
EXEC TREE_2_OLAP 1
GO

-- REFRESH TREE_OLAP DIMENSION AND Sales CUBE PROGRAMMATICALLY AND CHECK THE RESULTS IN ANALYSIS SERVICES