Parent-Child dimension but with multiple levels

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

Parent-Child dimension but with multiple levels

Author

Topic

Joozh
Posting Yak Master

145 Posts

Posted - 2006-04-16 : 03:53:41

Hi,

This is about the example Foodmart database that comes with Analysis Servcies... The approach used for creating the accounts dimension (in the example database) is basically what I am referring to but the difference is that in my case I have multiple levels rather than just 2 levels (as in the example). Let me explain this more:

The example database has 2 levels in the underlying table and each "non-root" record points to a parent. I want to use the same approach but in a case where I have more than 2 levels... A chart of accounts (used in Accounting systems) is the best example that I can think of.

Can someone please guide me how I can achieve this and be able to use the parent-child dimension making apporach. Really urgent and am posting this after applying all that I could think of and after going through many hours of research on the web

Please let me know if this requires more elaboration. Many thanks in advance for your help.

Thanks & Regards.

-J

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-04-17 : 08:27:05

You need a tree structure:
http://www.thesitedoctor.co.uk/seventhnight/treestructs/

And here is a huge thread about them.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15499

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Joozh
Posting Yak Master

145 Posts

Posted - 2006-04-17 : 09:06:26

Thanks Thanks Thanks..... I thougjt I will never get a reply from anyone.

I will go tgrough the URLs and hope to find the solution :0

Thanks a million Corey

Thanks & Regards.

-J

Joozh
Posting Yak Master

145 Posts

Posted - 2006-04-18 : 00:39:37

Ooppssss. I have gone through the URLs and I probably need a PhD before I can absorb that stuff. I thouht there would be some easier solution :(

Anyway..really appreciate your post.

Thanks & Regards.

-J

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-18 : 01:54:30

>>I thouht there would be some easier solution

It cant be done in a single query. You need to use While loop or recursive procedure to retrieve that. Here are other related links
http://www.nigelrivett.net/RetrieveTreeHierarchy.html
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

Madhivanan

Failing to plan is Planning to fail

Joozh
Posting Yak Master

145 Posts

Posted - 2006-04-18 : 04:55:09

Thanks Madhivanan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources