Nesting dis-similar hierarchies

I would like to build an xml structure similar to this:
<Bracket>
<Teams>
<Team>Something</Team>
</Teams>
<Games>
<Game>Something Else</Game>
</Games>
</Bracket>
My attempt follows:
select 1 AS Tag,
NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element] FROM Teams
UNION
SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
UNION
SELECT 1 AS TAG, NULL AS Parent,
NULL AS [Games!1!GameGroup!element],
NULL AS [Game!2!BracketNumber],
NULL AS [Game!2!GameNumber],
NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
UNION
SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
BracketNumber = 1
I get back a nasty error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any thoughts,
TIA
Tom
What are the column types?
With the following test data, I get the following error in SQL Server 2005:
create table Teams( TeamIndex int, ReqTeamName varchar(5) , TeamNumber int)
Insert into Teams VALUES (1, 'a', 5)
create table stGames ( BracketNumber int, GameNumber int, [Time]
varchar(5) )
insert into stGames VALUES (1, 1, 'late')
-- running the query below
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting a value of type varchar to type int.
Ensure that all values of the expression being converted can be converted to
the target type, or modify query to avoid this type conversion.
The reason is that the universal table format requires that you have a
column for every element or attribute that you want to create. In your query
below, you overlay the teams and Games.
Try the following query instead (worked on SQL 2005):
select 1 AS Tag, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL, NULL
from Teams
UNION
SELECT 3 AS TAG, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM stGames WHERE BracketNumber=1
UNION
SELECT 4,3,
NULL, NULL, NULL, NULL,
NULL, BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
Also, I wonder whether you really need the Teams and Games wrapper elements.
Unless you need to provide group specific properties, I find these elements
useless and they actually make processing of the documents more expensive in
most cases. I would recommend the following instead:
select 1 AS Tag, NULL AS Parent,
NULL AS [Bracket!1!dummy!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Game!3!BracketNumber],
NULL AS [Game!3!GameNumber],
NULL AS [Game!3!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL
from Teams
UNION
SELECT 3,1,
NULL, NULL, NULL, NULL,
BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
And here is the query (for your original example) using SQL Server 2005's
capabilities:
select
(select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
from Teams
for xml path('Team'), root('Teams'), type),
(select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
[Time] as "@.Time"
from stGames
where BracketNumber = 1
for xml path('Game'), root('Games'), type)
for xml path('')
HTH
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>I would like to build an xml structure similar to this:
> <Bracket>
> <Teams>
> <Team>Something</Team>
> </Teams>
> <Games>
> <Game>Something Else</Game>
> </Games>
> </Bracket>
> My attempt follows:
> select 1 AS Tag,
> NULL AS Parent,
> NULL AS [Teams!1!TeamGroup!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element] FROM Teams
> UNION
> SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
> UNION
> SELECT 1 AS TAG, NULL AS Parent,
> NULL AS [Games!1!GameGroup!element],
> NULL AS [Game!2!BracketNumber],
> NULL AS [Game!2!GameNumber],
> NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
> UNION
> SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
> BracketNumber = 1
> I get back a nasty error:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> Any thoughts,
> TIA
> Tom
|||Michael,
Thank you for your help. I am working on understanding all the implications
now. Regarding this input, my (possibly uninformed) reason for these tags are
to maintain a hierarchical grouping and provide first level containers of a
group of similar elements. When I open a document like this in XMLSPY there
is a tag that groups teams and games. My thought is that I can go directly to
the grouping that I want and not have to loop through nodes to find the last
team and the first game. The whole thought behind making this one XML
document versus two is to provide more efficient handling of the information.
Would it be better to make this multiple documents?
"Michael Rys [MSFT]" wrote:

> Also, I wonder whether you really need the Teams and Games wrapper elements.
> Unless you need to provide group specific properties, I find these elements
> useless and they actually make processing of the documents more expensive in
> most cases. I would recommend the following instead:
> select 1 AS Tag, NULL AS Parent,
> NULL AS [Bracket!1!dummy!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element],
> NULL AS [Game!3!BracketNumber],
> NULL AS [Game!3!GameNumber],
> NULL AS [Game!3!Time]
> FROM Teams
> UNION
> SELECT 2, 1,
> NULL,
> TeamIndex , ReqTeamName , TeamNumber,
> NULL, NULL, NULL
> from Teams
> UNION
> SELECT 3,1,
> NULL, NULL, NULL, NULL,
> BracketNumber, GameNumber, [Time]
> FROM stGames WHERE
> BracketNumber = 1
> FOR XML EXPLICIT
> And here is the query (for your original example) using SQL Server 2005's
> capabilities:
> select
> (select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
> from Teams
> for xml path('Team'), root('Teams'), type),
> (select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
> [Time] as "@.Time"
> from stGames
> where BracketNumber = 1
> for xml path('Game'), root('Games'), type)
> for xml path('')
>
> HTH
> Michael
> "Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
> news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>
>
|||Having one document is ok. But you can use the root property of the provider
interface instead of using the Bracket (although your solution for that is
ok). The problem with the other wrapping elements is that you add additional
nodes to the document. While this may look nice in a tool like XML spy, it
does not communicate more semantics, makes your queries longer (and
potentially less efficient) and the XML documents larger.
But if you prefer them, by all means, feel free to add them.
Best regards
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:B2ED3C62-C98D-40F2-ACB1-02A3473368B4@.microsoft.com...[vbcol=seagreen]
> Michael,
> Thank you for your help. I am working on understanding all the
> implications
> now. Regarding this input, my (possibly uninformed) reason for these tags
> are
> to maintain a hierarchical grouping and provide first level containers of
> a
> group of similar elements. When I open a document like this in XMLSPY
> there
> is a tag that groups teams and games. My thought is that I can go directly
> to
> the grouping that I want and not have to loop through nodes to find the
> last
> team and the first game. The whole thought behind making this one XML
> document versus two is to provide more efficient handling of the
> information.
> Would it be better to make this multiple documents?
> "Michael Rys [MSFT]" wrote:

Nesting dis-similar hierarchies

I would like to build an xml structure similar to this:
<Bracket>
<Teams>
<Team>Something</Team>
</Teams>
<Games>
<Game>Something Else</Game>
</Games>
</Bracket>
My attempt follows:
select 1 AS Tag,
NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element] FROM Teams
UNION
SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
UNION
SELECT 1 AS TAG, NULL AS Parent,
NULL AS [Games!1!GameGroup!element],
NULL AS [Game!2!BracketNumber],
NULL AS [Game!2!GameNumber],
NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
UNION
SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
BracketNumber = 1
I get back a nasty error:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any thoughts,
TIA
TomWhat are the column types?
With the following test data, I get the following error in SQL Server 2005:
create table Teams( TeamIndex int, ReqTeamName varchar(5) , TeamNumber int)
Insert into Teams VALUES (1, 'a', 5)
create table stGames ( BracketNumber int, GameNumber int, [Time]
varchar(5) )
insert into stGames VALUES (1, 1, 'late')
-- running the query below
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting a value of type varchar to type int.
Ensure that all values of the expression being converted can be converted to
the target type, or modify query to avoid this type conversion.
The reason is that the universal table format requires that you have a
column for every element or attribute that you want to create. In your query
below, you overlay the teams and Games.
Try the following query instead (worked on SQL 2005):
select 1 AS Tag, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL, NULL
from Teams
UNION
SELECT 3 AS TAG, NULL AS Parent,
NULL AS [Teams!1!TeamGroup!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Games!3!GameGroup!element],
NULL AS [Game!4!BracketNumber],
NULL AS [Game!4!GameNumber],
NULL AS [Game!4!Time]
FROM stGames WHERE BracketNumber=1
UNION
SELECT 4,3,
NULL, NULL, NULL, NULL,
NULL, BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
Also, I wonder whether you really need the Teams and Games wrapper elements.
Unless you need to provide group specific properties, I find these elements
useless and they actually make processing of the documents more expensive in
most cases. I would recommend the following instead:
select 1 AS Tag, NULL AS Parent,
NULL AS [Bracket!1!dummy!element],
NULL AS [Team!2!TeamIndex],
NULL AS [Team!2!TeamName!element],
NULL AS [Team!2!TeamNumber!element],
NULL AS [Game!3!BracketNumber],
NULL AS [Game!3!GameNumber],
NULL AS [Game!3!Time]
FROM Teams
UNION
SELECT 2, 1,
NULL,
TeamIndex , ReqTeamName , TeamNumber,
NULL, NULL, NULL
from Teams
UNION
SELECT 3,1,
NULL, NULL, NULL, NULL,
BracketNumber, GameNumber, [Time]
FROM stGames WHERE
BracketNumber = 1
FOR XML EXPLICIT
And here is the query (for your original example) using SQL Server 2005's
capabilities:
select
(select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
from Teams
for xml path('Team'), root('Teams'), type),
(select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
[Time] as "@.Time"
from stGames
where BracketNumber = 1
for xml path('Game'), root('Games'), type)
for xml path('')
HTH
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>I would like to build an xml structure similar to this:
> <Bracket>
> <Teams>
> <Team>Something</Team>
> </Teams>
> <Games>
> <Game>Something Else</Game>
> </Games>
> </Bracket>
> My attempt follows:
> select 1 AS Tag,
> NULL AS Parent,
> NULL AS [Teams!1!TeamGroup!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element] FROM Teams
> UNION
> SELECT 2, 1, NULL, TeamIndex , ReqTeamName , TeamNumber from Teams
> UNION
> SELECT 1 AS TAG, NULL AS Parent,
> NULL AS [Games!1!GameGroup!element],
> NULL AS [Game!2!BracketNumber],
> NULL AS [Game!2!GameNumber],
> NULL AS [Game!2!Time] FROM stGames WHERE BracketNumber=1
> UNION
> SELECT 2,1, NULL, BracketNumber, GameNumber, [Time] FROM stGames WHERE
> BracketNumber = 1
> I get back a nasty error:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> Any thoughts,
> TIA
> Tom|||Michael,
Thank you for your help. I am working on understanding all the implications
now. Regarding this input, my (possibly uninformed) reason for these tags ar
e
to maintain a hierarchical grouping and provide first level containers of a
group of similar elements. When I open a document like this in XMLSPY there
is a tag that groups teams and games. My thought is that I can go directly t
o
the grouping that I want and not have to loop through nodes to find the last
team and the first game. The whole thought behind making this one XML
document versus two is to provide more efficient handling of the information
.
Would it be better to make this multiple documents?
"Michael Rys [MSFT]" wrote:

> Also, I wonder whether you really need the Teams and Games wrapper element
s.
> Unless you need to provide group specific properties, I find these element
s
> useless and they actually make processing of the documents more expensive
in
> most cases. I would recommend the following instead:
> select 1 AS Tag, NULL AS Parent,
> NULL AS [Bracket!1!dummy!element],
> NULL AS [Team!2!TeamIndex],
> NULL AS [Team!2!TeamName!element],
> NULL AS [Team!2!TeamNumber!element],
> NULL AS [Game!3!BracketNumber],
> NULL AS [Game!3!GameNumber],
> NULL AS [Game!3!Time]
> FROM Teams
> UNION
> SELECT 2, 1,
> NULL,
> TeamIndex , ReqTeamName , TeamNumber,
> NULL, NULL, NULL
> from Teams
> UNION
> SELECT 3,1,
> NULL, NULL, NULL, NULL,
> BracketNumber, GameNumber, [Time]
> FROM stGames WHERE
> BracketNumber = 1
> FOR XML EXPLICIT
> And here is the query (for your original example) using SQL Server 2005's
> capabilities:
> select
> (select TeamIndex as "@.TeamIndex", ReqTeamName, TeamNumber
> from Teams
> for xml path('Team'), root('Teams'), type),
> (select BracketNumber as "@.BracketNumber", GameNumber as "@.GameNumber",
> [Time] as "@.Time"
> from stGames
> where BracketNumber = 1
> for xml path('Game'), root('Games'), type)
> for xml path('')
>
> HTH
> Michael
> "Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
> news:DF7E937F-CF69-46C2-BB60-9649A5734528@.microsoft.com...
>
>|||Having one document is ok. But you can use the root property of the provider
interface instead of using the Bracket (although your solution for that is
ok). The problem with the other wrapping elements is that you add additional
nodes to the document. While this may look nice in a tool like XML spy, it
does not communicate more semantics, makes your queries longer (and
potentially less efficient) and the XML documents larger.
But if you prefer them, by all means, feel free to add them.
Best regards
Michael
"Tom Heavey" <TomHeavey@.discussions.microsoft.com> wrote in message
news:B2ED3C62-C98D-40F2-ACB1-02A3473368B4@.microsoft.com...
> Michael,
> Thank you for your help. I am working on understanding all the
> implications
> now. Regarding this input, my (possibly uninformed) reason for these tags
> are
> to maintain a hierarchical grouping and provide first level containers of
> a
> group of similar elements. When I open a document like this in XMLSPY
> there
> is a tag that groups teams and games. My thought is that I can go directly
> to
> the grouping that I want and not have to loop through nodes to find the
> last
> team and the first game. The whole thought behind making this one XML
> document versus two is to provide more efficient handling of the
> information.
> Would it be better to make this multiple documents?
> "Michael Rys [MSFT]" wrote:
>

Nested sets in SQL - inventor?

Who invented the nested sets algorithm for SQL? Was it Kamfonas or Celko?
The algorithms are so similar that clearly Celko must have copied from
Kamfonas, or Kamfonas from Celko .. or both from someone else.
(If you don't know what I'm on about, it's this:
http://www.dbpd.com/vault/9811/kamfn.shtml
versus this:
http://www.dbmsmag.com/9604d06.html )
Any ideas? ThanksAn earlier article by Celko on nested sets appeared in the March 1996:
http://www.dbmsmag.com/9603d06.html
whereas the Kamfonas article is dated 1998.
However, Joe Celko wrote (about nested sets model for hierarchies) "I
made it popular by filling in some holes, but the idea was not mine."
in the following message:
http://groups-beta.google.com/group...
517df0172eba8
Razvan|||Appeo Allkam wrote:
> http://www.dbpd.com/vault/9811/kamfn.shtml
I don't quite understand the following passage in Kamfonas article
<quote>
You may ask: "Why don't we use a hierarchical keyword to achieve the
same result?" For example, an IP address-like scheme enumerates the
nodes of a tree as 1,1.1,1.2,1.3,1.1.1, and so on. The problem with
this scheme is that you have to start all qualifications from the top.
Otherwise, you'll have to scan the whole index or table. With the L and
R enumeration however, you can anchor your search at any level, relate
any node to any other one, and still employ matching index scans.
</quote>
Any interpretations?|||Mikito Harakiri wrote:
> Appeo Allkam wrote:
> I don't quite understand the following passage in Kamfonas article
> <quote>
> You may ask: "Why don't we use a hierarchical keyword to achieve the
> same result?" For example, an IP address-like scheme enumerates the
> nodes of a tree as 1,1.1,1.2,1.3,1.1.1, and so on. The problem with
> this scheme is that you have to start all qualifications from the top.
> Otherwise, you'll have to scan the whole index or table. With the L and
> R enumeration however, you can anchor your search at any level, relate
> any node to any other one, and still employ matching index scans.
> </quote>
> Any interpretations?
Well, I'm reading further and the next paragraphs are partially true
and completely wrong:
<quote>
Descendent-sing queries, such as the one in Listing 1's Case 2, are
the most common and the most efficient. The optimizer will use a
matching index scan to find the qualifying D.L values that lie between
the A.L and A.R constants. With this query plan, the cost of
descendent- sing queries is one sweep through the index reading in a
number of contiguous pages proportional to the answer set's size. In
ancestor-sing queries, such as Listing 1's case 1, the between
predicate restricts a constant, D.L, between the two columns A.L and
A.R. A combined index on L (descending) and R (ascending) helps these
ancestor searches. The best plan we can expect for these queries is a
matching lookup of D.L on the combined index, and a scan to the end of
the index using index only access. Consequently, the average cost for
ancestor-sing queries is half an index-only scan.
</quote>
The cost estimation for descendant looking queries is correct. What is
the efficiency of ancestor search? My understanding is that with
combined index, or bitmap, or even spatial it still sucks.
With Matrialized Path/Nested Intervals you *calculate* the chain of
ancestors (doesn't really matter on the client, or server) and
construct a dynamic SQL query
select * from tree where path in ('1','1.2', 1.2.1')
which as a concatenation uf 3 index unique scans is extremely fast.
<quote>
The L and R method has a level of magnitude performance advantage over
any recursive SQL method, such as the SQL recursive union or Connect By
clause. The node enumeration captures the nodes' topological ordering
once, thus enabling transitive closure in one simple step, as opposed
to multiple invocations. Detecting whether two nodes have an
ancestor-descendent relationship normally requires the path traversal
from one node to the other. Using the L and R numbers, however, you can
test any two nodes using a simple between clause--without traversing
the graph. Because the L and R method doesn't need to traverse the
structure, more selective predicates may filter down the qualifying
rows before applying the ancestor-descendent qualification. In
recursive SQL, path traversal has to happen on unconstrained node sets,
postponing highly filtering predicates until after the paths are
traversed exhaustively.
</quote>
This is entirely wrong. Traversing adjacency list is fast. Each next
node is found by index unique scan. Multiple invokations are not evil,
as long as their calls are not exposed (over a network connection
between client and server). This is why it makes sence supporting
recursive SQL on server, instead of client querying hierarchy in
multiple dynamically generated nonrecursive SQL queries.|||Mikito Harakiri wrote:
> Appeo Allkam wrote:
> I don't quite understand the following passage in Kamfonas article
<quote>
The "prestored transitive closure" approach involves an intermediate
table that contains X's descendents, and a join to the org table. The
intermediate table is either temporary, generated every time you issue
a query, or permanent, containing the transitive closure of the
"reports to" relationship.
There are divantages to each of these solutions. The first actually
"walks the structure," requiring multiple requests that let you extract
children sets for each node you retrieve. The advantage of this
approach is that it doesn't introduce any update anomalies because you
don't maintain redundant data. The second solution uses set processing,
but it requires that you maintain a very large redundant table and deal
with the associated update anomalies. For example, a tree five layers
deep with a fan-out of 10 children per node has a total of 11,111 nodes
and 11,110 parent-child relationships. But there are more than
11,000,000 ancestor-descendent relationships in the transitive closure.
A single maintenance operation may affect more than one million of
these ancestor-descendent relationships. The cost of this approach
grows geometrically as the fanout and depth grow. This option is
undesirable only because of maintenance complications and the lack of
scalability.>
</quote>
Where this 11,000,000 number came from? Each of 11,111 nodes has no
more than 5 ancestors so that the size of transitive closure is
certainly less than 5*11,111.
Just for the record, the size of Materialized path/Nested
Intervals.Nested Sets encoding is about the same. It is true, there are
only 11,111 records, but each encoding grows in size with the number of
nodes in the tree increasing.
I guess I'll stop reading, unless convinced that there is a single not
entirelly wrong idea in this article.|||Razvan Socol wrote:
> An earlier article by Celko on nested sets appeared in the March 1996:
> http://www.dbmsmag.com/9603d06.html
> whereas the Kamfonas article is dated 1998.
>
I heard a presentation on the nested set structure back in 1995 by
two Norwegian guys, Leif Morten Kofoed and H=E5kon Erdal. It seemed
to me that they figured out the idea on their own. They used
it in a large system involving the Central Bank of Norway.
Lauri Pietarinen|||Mikito Harakiri wrote:
> This is entirely wrong. Traversing adjacency list is fast. Each next
> node is found by index unique scan. Multiple invokations are not evil,
> as long as their calls are not exposed (over a network connection
> between client and server). This is why it makes sence supporting
> recursive SQL on server, instead of client querying hierarchy in
> multiple dynamically generated nonrecursive SQL queries.
Isn't it the case that a recursive query that uses an index is
roughly comparable to an indexed nested loop join? That is to say,
it will perform quite well.
I'm still trying to wrap my head around recursive queries; they
are a fairly new thing to think about, and I don't have a good
model for how they are implemented. The 'recursive with' mentioned
recently makes me think it's going to build the whole recursively
defined set in advance of the select, but I expect that's probably
not right.
I completely agree with your conclusion. Client code executing
multiple queries over the network is bad in so many ways.
Marshall|||"Marshall Spight" <marshall.spight@.gmail.com> wrote in message
news:1122529011.244856.250850@.g43g2000cwa.googlegroups.com...

> I'm still trying to wrap my head around recursive queries; they
> are a fairly new thing to think about, and I don't have a good
> model for how they are implemented. The 'recursive with' mentioned
> recently makes me think it's going to build the whole recursively
> defined set in advance of the select, but I expect that's probably
> not right.
Take advantage of this moment of ignorance. It won't come again. Once you
wrap your head around the "how" your vision of the "what" will be more
cloudy than it is now.
One of the consistent failures we all make is to deal with the "what rather
than how".
Once you have a workable model of how recursive joins are implemented, that
model will begin
to displace the model you now have of what recursive joins really are.
This has happened to me several times in my long career. It begin to think
of the "what" stated in a program
as being shorthand for the "how" that I or a code generator might use to
carry it out. It's an illusion, albeit a useful one.|||David Cressey wrote:
> "Marshall Spight" <marshall.spight@.gmail.com> wrote in message
> news:1122529011.244856.250850@.g43g2000cwa.googlegroups.com...
>
> Take advantage of this moment of ignorance. It won't come again. Once yo
u
> wrap your head around the "how" your vision of the "what" will be more
> cloudy than it is now.
> One of the consistent failures we all make is to deal with the "what rathe
r
> than how".
Argh! This is plainly excellent advice. It's quite ironic for me to
get it, because these days I'm working with a lot of less experienced
engineers, and I'm always chiding them for applying all their mental
energy to implementation and nothing into thinking about interface
or model or concept or whatever.
How funny to receive the exact advice one is dispensing on a daily
basis, and how amusing, in a self-deprecating way, to realize that
one wasn't following one's own best practices.
Thanks! I hereby resolve to build a full conceptual model of recursive
queries before moving on to implementation.
Marshall|||"Mikito Harakiri" <mikharakiri_nospaum@.yahoo.com> wrote in message
news:1122488079.859136.9130@.o13g2000cwo.googlegroups.com...
> Appeo Allkam wrote:
> I don't quite understand the following passage in Kamfonas article
> <quote>
> You may ask: "Why don't we use a hierarchical keyword to achieve the
> same result?" For example, an IP address-like scheme enumerates the
> nodes of a tree as 1,1.1,1.2,1.3,1.1.1, and so on. The problem with
> this scheme is that you have to start all qualifications from the top.
> Otherwise, you'll have to scan the whole index or table. With the L and
> R enumeration however, you can anchor your search at any level, relate
> any node to any other one, and still employ matching index scans.
> </quote>
> Any interpretations?
>
It's not clear to me from your post whether you are interested in who
invented the nested sets method.
I don't know, but I can tell you this: years before I saw Joe Celko's
description of nested sets, I saw a magazine article entitled, "Taming the
dreaded hierarchy" by, I think, John Baugh. In this article he outlined a
method that's very similar to nested sets.

Need urgent MDX help on using Aggreate funtion in SSAS 2005

Hi,

I need a functionality similar to Named Member in ProClarity in SSAS 2005.

In Proclarity I can define a named memeber by selecting a few levels of a dimension. The underlying MDX uses the Aggregate function something like this

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

Once the named member is selected the calculated measures show the aggreated (sum) results based on the named member and the underlying selected levels in the aggreate function.

How can I do something like this in SSAS 2005. I dont want a named set. And I tired created a calculated member (which i knew will not work but still gave a shot)

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

I also tried specifying the measure in the second parameter

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] }

, [Measures].[Orders Received Local])

Is it possible to use the aggreate function dynamically as its used in ProClarity?

Thanks in advance for help

The aggregate function should work fine, can you tell us exactly what error you are getting. One thing about the aggregate function is that you would need to create the calculated member on a dimension other than the measures dimension.

Something like the following should report on a dynamically created aggregate member:

Code Snippet

WITH

MEMBER [Channel].[Channel].[AggTest]

as Aggregate({

[Channel].[Channel].[CON - Contractor]

, [Channel].[Channel].[DIS - Distributor]

, [Channel].[Channel].[END - End-User]

, [Channel].[Channel].[GRP - Group]

, [Channel].[Channel].[OEM - OEM]

, [Channel].[Channel].[PLA - Private Label]

, [Channel].[Channel].[SER - Service Provider]

, [Channel].[Channel].[SYS - System builder]

})

SELECT

{[Channel].[Channel].[AggTest]} ON COLUMNS

FROM <Cube>

|||Thanks. yes it worked this way. i made the mistake of creating it on the measures dimension. after changing the dimension to Channel it works fine.

Need urgent help on Aggregate Function

Hi,

I need to do something similar to Proclarity in SSAS 2005. In Proclarity i can create a named member by selecting a few members from a dimension. The underlying mdx looks like this

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

Once i select the named member all the calculated measures reflect the result based on the members selected in the aggregate function.

How can I do something similar in SSAS 2005. I dont want a named set and calculated member does not work if i use the plain mdx as above. But i also tried using

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] }, [Measures].Orders Received Local)

just to try to to see teh behaviour but to no avail. after processing the cube and selecting this meausre shows up empty cells.

How can I solve this problem.

thanks in adavance for helping

Could you explain why "calculated member does not work if i use the plain mdx as above" - what dimension/hiererchy did you create the member on, and what results did you get?|||

Thanks for asking this question. I think i made a mistake by leaving the parent hierarchy as default "Measure" and Parent Member as empty. I have modifed the Parent hirerachy to Channel.Channel and Parent Member to Channel.All Channels. I am processing the cube now and would get back to you soon with the results.

Thanks once again

|||

after i put the correct hierarchy and parent member the mdx works just fine.

Thanks a million for your help.