Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » SQL Server » Downloads
 
Add your own
alternative version

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

, 19 Jul 2006 CPOL
This article describes how to efficiently store and access tree structures in a SQL Server database, and how to use them in OLAP implementations.
refreshsalescube.zip
RefreshSalesCube
App.ico
bin
Release
Interop.DSO.dll
Interop.MSOLAPADMINLib2.dll
Interop.POMInterfaces.dll
Interop.VBA.dll
RefreshSalesCube.exe
RefreshSalesCube.csproj.user
sales.zip
sqlscripts.zip
tree_util.zip
tree_util
bin
tree_util.dll
Global.asax
tree.xsl
tree_util.csproj.webinfo
/**************** 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


By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Dan Radu
Web Developer Telstra Internet
Australia Australia
I live and work in Bucharest, Romania. I am programmer since 1998, when I have developed a "good taste" application for a catering company. Now I develop .NET applications (windows and ASP.NET) for large SQL Server database systems, with tens of millions of records.
I like to develop also in other languages like Object Pascal (Delphi), PHP, C++, VB, scripting. I enjoy the XML power, both on client side and server side.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 19 Jul 2006
Article Copyright 2006 by Dan Radu
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid