I need a dimension that mimics a file system. Meaning that it has a folder
structure that then contain Projects that are in the FACT table.

So I have a FACT table that contains these rows

ProjectID, data

A Project look-up table that contains

ProjectID, ProjectName, FolderID

A Folder look-up table that contains

FolderID, FolderName, FullPathToRFolderName, ParentID



So I would join the FACT table and the Project table on ProjectID and the
Project Table and the Folder table on FolderID

Now I would like the Dimension to list

+Folderlevel1

Project

+FolderLevel2

Project

+FolderLevel3

Project

I do not know ahead of time the number or depth of the folder structure or
while folders will contain projects. Exactly like a file system.

Any Ideas on how to accomplish this? I am open to any suggestion.

The Folder level would appear to be a Parent-Child Dimension but it doesn't
appear to allow for the addition of the project level.

SQL Server 2000 with Analysis Server

If I have not explained myself well, or you need more information, please
let me know.

Thanks for the help,

$cott