Help With Celko's Tree Model
Jul 20, 2005
hello i have implemented joe celko's model to store heirarchy and it
works well and nice
i have a question about SQL
this is the actual table
member side left right
------------------------------------------
nancy L 1 36
andrew L 4 21
steven R 5 12
ina L 6 7
david R 10 11
margaret L 13 20
ann R 14 15
laura L 18 19
janet R 24 35
michael L 25 30
dan R 26 27
ron L 28 29
robert R 33 34
the Side column is to tell its left, or right. this is a binary
heirarcy.
i have this problem i have to solve, im still banging my head. If
given the member
'Nancy' , i need to find left-most(Laura) and right-most(Robert)
'Janet' = left most is ron, right most is robert
'Andrew = left most is laura, right most is David
Hope u get my plan. could u help me with the sql ?
View 3 Replies
ADVERTISEMENT
Jul 3, 2006
Hi,
With reference to http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427
I want the "sql stmt" which wud give the lft and rgt col values..
i am reading his book but cant understand :eek: where he explains
wat lft and rgt cols are..
"The root is always (lft,rgt) (1, 2*(Select count(*)from table) and leaft nodes are (lft+1=rgt)" :S
View 3 Replies
View Related
Feb 19, 2007
Hi
Can anyone tell me the steps involved in retrieving a model's (decision tree) pmml and use the model content to devleop a web based interface. I am using SQL Server 2005.
Thanks,
Nathan
View 5 Replies
View Related
Oct 21, 2007
I have two questions about the regression tree of Microsoft Decision Trees algorithm.
1. The mining legend window has a column named Histogram showing a bar for each coefficient. What does this bar mean?
2. Since each node of a regression tree corresponds to a linear regression, how can I find the regression coefficient of each node? I mean the coefficient that tells how good the regression is.
Any tip will be greatly appreciated.
View 1 Replies
View Related
Sep 15, 2006
Hi,
I used a decision-tree mining-model to describe and predict fraud. The table contains 1039 records with 775 distinct value of A-number (the calling party). I used 9 columns in the model. SQL Server reports that only 3 columns are significant in predicting the fraud
- BPN_is_too_short (called party-number is too short)
- Duration_is_zero
- Invalid_area_code
The key-column in A-number, and the predicted column is Is_Fraud with the range of values are only 0 and 1. There's no record with NULL (missing-value) in the column Is_Fraud.
Mining Legend shows in the first split
[-] 625 cases of fraud
[-] 150 cases of non-fraud
[-] 0 cases of missing
In addition to that, Mining Legend shows
[-] 79.69% of fraud
[-] 19.64% of non-fraud
[-] 0.67% Missing
Now when I compare those values, they don't match.
(A) 625/775 is 80.645%, not 79.69%
(B) 150/775 is 19.355%, not 19.64%
(C) 0 cases of NULL (missing value) should imply 0% of missing, not 0.67% of missing
Furthermore in one node (with the split on duration_is_zero), there are 541 cases of fraud and 0 cases of non-fraud. This implies the node is leaf-node. However, Mining Legend shows
514 cases of fraud, 99.35%
0 cases of non-fraud, 0.33%
[F] 0 cases of missing, 0.33%
My questions
(1) Why the values don't match like in cases A through C ?
(2) Why the values don't match even in cases D through F when we have no subtree at all ?
I've searched explanation by reading the mathematical reasoning, entropy, Gini index; but it does not answer the discrepancies of those values and percentages in the Mining Legend.
Regards,
Bernaridho
View 3 Replies
View Related
May 18, 2007
We've successfully processed a large decision tree model in SQL Server 2005. When I try to view the tree in the mining model viewer, I get the following error:
TITLE: Microsoft Visual Studio
------------------------------
The tree graph cannot be created because of the following error:
'Exception of type 'System.OutOfMemoryException' was thrown.'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Viewers.SR&EvtID=ErrorCreateGraphFailed&LinkId=20476
The link provides no other documentaiton on the error.
We're using 64-bit SQL on a Dell Workstation running XP-64 with 16GB of memory. From my view of things we aren't close to running out of memory. Since the model processed and the error occurs when viewing the model, is this a problem with Visual Studio and nont necessarily Anlaysis Services?
Thanks in advance.
Nick
View 4 Replies
View Related
Sep 4, 2007
Hello,
I have a table (in Access) with about 30 fields and 1,700,000 records.
I had created a mining model in AS2005 with only one key (the autonum column called ID)
and other attributes marked as Input and/or predict.
When processing the model, it finish (after 15 min.) with an error: 3183
"Not enough space in temporal disk"
After some search , I encountered that is close related to the memory asigned to the tempdb.
I tried to increase the size of tempdb but it is imposible, moreover, it starts
with 8MB but it is autosized when needed.
I don't know how to solve this issue. Or, if it is a question of memory/disk space management (I have 100GB of free space in disk).
I tried the same model changing the KEY (I assign StudyID as key) then with the same data but 60,000 StudyIDs it is ok, so the mining model is ok (no nested tables, no case, too easy for getting a memory error)...
Please, can anyone recommend a possible solution for this issue?.
Many Thanks.
View 2 Replies
View Related
Jun 15, 2007
Can I ask how to split the dataset into training and validation when running descision tree model?
View 3 Replies
View Related
Jul 20, 2005
Greetings,I think I saw an article yesterday in which you told someone not to makean Identity column a primary key. I seem to remember you sayingsomething like "It undermines the entire foundation of the relationalmodel."I've been thinking about that all night and I think I understand whatyou're saying, but I have some questions.I think you're saying that if the real key in a 3 attribute (heh, heh.didn't say fields. heh heh) tuple (heh heh) is a combination of thethree attributes, then that's what you should use as a primary key.Do you then advocate never using an Identity attribute? Or is itacceptable (in the relational model) to have an Identity attribute touse as a handle to the row, and for attributes in other tables to use asthe target for a foreign key?Thank you,-- Rick
View 18 Replies
View Related
Apr 6, 2015
I got assignment, how to make it appear in the right order .
/* DROP TABLE EMP
SELECT * INTO Emp FROM (
SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL
SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL
SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL
SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b
*/
[code]....
View 2 Replies
View Related
Feb 9, 2004
Hi,
I am trying to revamp our product database with a view to making it search-optmised and would like some guidance (or confirmation of method, if you will!!). We currently use a three table structure (Product, Brand, Cat(egory)) along the lines of :
create table product
(prod_id int not null,
brand_id int not null,
cat_id int not null,
other stuff e.g. tech. specs, displayed text on web page, etc....
)
with corresponding brand_id and cat_id in the other tables. While this seems relationally sound I see it as being inefficient for searching, particularly after reading the theory behind nested sets.
A new function I am building will enable users to drill down through the product list or runs searches against all or part of the db :
e.g. all products from one category,
all products fitting certain search criteria,
products from several selected brands fitting certain criteria,
and any combination of the above you can think of!
The problem is, not all products have the same criteria list (in fact I would be surprised if any did) and may also be of more than one category (a digital camera with movie mode might easily fit into the digital camcorder search). I think I am correct in that a nested set would make the structure fit the requirement - things like criteria, displayable text, etc. could be nodes in their own right and each logical level might have its own criteria. For example, if a category is selected then certain text must be displayed and could list further categories or products. The next level down would then require its own displayable text - I am mainly thinking about SEO tags here. Also, I am not precious about retaining the current table structure and would like an open ended solution where I can add further data/functionality in a dynamic fashion, which nested sets seem to embody.
Does this make sense to anybody coz I think I've confused myself even more!!
Thanks in advance,
G
View 14 Replies
View Related
Dec 5, 2005
Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:
View 9 Replies
View Related
Oct 19, 2015
I need to develop a language specific dwh, meaning that descriptions of products are available from a SAP system in multiple languages. English is the most important language and that is the standard. But, there are also requirements of countries that wants productdescriptions in their language.
Productnr Productdesc Language
1 product EN
1 produkt DE
One option is to column the descriptions, but that is not very elegantly. I was thinking of using bridge tables to model this but you have to always select a language in a filter (I think)..
I'm thinking of a technical solution, such that when a user logs on, the language is determined and a view determines whether to pick a certain product table specific for a certain language. But then I don't have the opportunity to interchange the different language specific fields in a report (or in my case PowerPivot).
View 2 Replies
View Related
Apr 25, 2008
We have the following scenario,
We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.
SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.
Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.
It it possible, if yes how can we achieve this.
If not what could be another DR solution in this scenario.
Thanks Much
Tejinder
View 6 Replies
View Related
Oct 27, 2007
Hi all,
I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?
View 1 Replies
View Related
Aug 10, 2005
hi !!i have a question about the connected and disconnected model to access the Sql server DB.......i know that there is better to choose one rather than the other in some situantions and there is no better model in all cases...... os i hope you can help me to decide what shall i choose...i will use the DB to connect to Web services and read data from the DB and wrtie some data back.......i do not know that to use ..... i hope you advise me and tell me about the rules that will allow me to choose what model to choose .... i appreciate your help!!Thanks !!!
View 2 Replies
View Related
Nov 15, 2006
I get the following error when I try to load the mining model in the mining model viewer
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[NeuralNet].[GetAttributeValues]' function does not exist.
I get a similar error when I try to load the Load Mining Accuracy Chart
Failed to execute the query due to the following error:
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[AllOther].[GenerateLiftTableUsingDatasource]' function does not exist.
I have OWC 11 installed. What am I missing here?
Thanks
View 7 Replies
View Related
May 20, 2004
Dear all,
I would like to know how to create a single level hierachy structure in SQL.
Example, I have a single parent record in table A that may later on spawn one or child record in table B that relates back to the parent. It only needs a single level.
that means, one to many.
thanks and rgds.
Loke HC
View 1 Replies
View Related
Jan 2, 2008
I have two tables.
table1 has 3 fields a , b and c. field "a" is a primary key.
table2 has 2 fields x and y. Fields x and y are nothing but the value of "a". Also, y is the child of x.
Therefore, x and y can never have same value. It means value of "a" either be child or parent.
But there is possibility that parent has no child.
Now, i wanted to write Select/Insert query for parent, b , c and child.
This tree is no a binary tree but N-Ary Tree.
Thanks,
sha
View 9 Replies
View Related
May 18, 2007
Hi,
I'm having a major brain-failure moment here.
Using T-SQL I want to be able to get all of the leaf nodes (e.g. nodes at the furthest end of a tree from the root) in a hierarchical relationship where the table structure is such NodeID|ParentID|NodeName.
Basically if I had the following tree structure:
Root
Child1
Grandchild1
Child2
Grandchild2
I want to get all of the Grandchild nodes. Number of levels will vary and I haven't got any kind of HasChilds column.
I know this is possible because I remember having done it on a course years ago but I can't for the life of me figure it out on this sunny Friday afternoon. I know it's going to involve either recursion, a while loop or cursors but my mind is currently jelly. Can anyone help?
View 8 Replies
View Related
Sep 10, 2007
hi. I am working on Multiline Marketing Project.I have to calculate all the childens and display the whole tree..I have save records in tree format...But not able to calculate the all childrens.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '16%')
This query works properly.Problem is that it is not working in the project because in the like I have to pass a variable.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '@idl%')
Please Suggest me...
View 1 Replies
View Related
Jun 2, 2008
HI,
I am working on a Family tree portal which need tree functionality to display family members in tree structure. on click on any node the adding option should be displayed
for this i need a table and procedure to complete family tree
Thanks
@mbi
View 5 Replies
View Related
Jan 3, 2006
Consider the following SQL query:
SELECT ENAME,SAL
FROM EMP,ASG,PAY,PROJ
WHERE EMP.ENO=ASG.ENO
AND PAY.TITLE=EMP.TITLE
AND ASG.PNO=PROJ.PNO
AND ASG.DUR=48 AND BUDGET>200000
Give the possible operator trees:right-deep,left-deep and bush
Tank you very much!
View 2 Replies
View Related
Jun 11, 2006
Hi,
i'm writing a app in c# and have to store Trees in a Database.
I'm working with Datasets for the exchange between the DB and the App.
The trees have the same options like the windows folders. If u delete a node, all subnodes should be deleted too.
But something a Foreign Key from ParentID references (Id) with the delete-Rule on cascade seems not to be possible, because of multiple cascade Paths or cycles. Do i have to add some xtra constarins:
Not Possible:
create Table tree (
Id varchar Not null,
ParentId varchar Not null,
Constraint pk1 Primary Key (Id),
Constraint fk1 Foreign Key (ParentId) references tree(Id)
On Update Cascade
On delete CAscade
)
Do i have to write triggers, which delete The subnodes too and set the Update-/deleterulr on NO Action
Greetz
View 1 Replies
View Related
Oct 24, 2006
Does anyone know any good links for SQL tree structures and example queries and stuff... I cant really find anything part from the standard example of emplyee, boss, salary which explains how to create the tree table...(dun dis bit) I did notice a book but I live in a little village so cant go get it till wekend?
I'm desperate, reli need to work out how too do this.....
View 14 Replies
View Related
Jun 13, 2008
SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description,
d.Security_Type_Description, a.Security_Value
FROM Company_Hierarchy_Security a
JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID
JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code
JOIN Company_Security_Type d on a.Security_Type = d.Security_Type
inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description,
f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description,
i.Team_Description
from Company_Hierarchy_Security a.
Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID
Inner JOIN Company f on a.Security_Value = f.Company_ID
Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5))
Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID
Inner JOIN Team i on a.Security_Value = i.Team_ID
Ok. I have the Security Value located in Company_Hierarchy_Security table. All those values are divided into 5 other tables that I need to join together. I found all of them separate but I have not been able to figue it out how to put it together with the rest of the querie.
Thanks for the help!!!!!
View 3 Replies
View Related
Jun 16, 2008
Thank You visakh16 --- The following section of my querie works but doesn't give the exact information that I need. This is what is giving me:
NetworkId Full Name Sec Class Desc Sec Type Desc SecValue
tte Test Scenario Accounting Budget Center 142- ?
SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description,
d.Security_Type_Description, a.Security_Value
FROM Company_Hierarchy_Security a
JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID
JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code
JOIN Company_Security_Type d on a.Security_Type = d.Security_Type
------------------
inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description,
f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description,
i.Team_Description
from Company_Hierarchy_Security a.
Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID
Inner JOIN Company f on a.Security_Value = f.Company_ID
Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5))
Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID
Inner JOIN Team i on a.Security_Value = i.Team_ID
_____________________________________________________________________
What I need is the Description that are located in 4 other different tables that matches the Security Value from my first querie.
The result should look like this...
Network Id full Name Sec Class Desc Sec Type Desc Security Value
tst , Test Example , Accounting ,Budget Center , 142-Accountig dept
Thank you very much,
View 4 Replies
View Related
Oct 14, 2005
Any recomendations on how to store organization trees on a database withlots of paths and branches? Any white papers out there that explain this?Thanks
View 2 Replies
View Related
Sep 12, 2006
Hello,I have a "tree" table:Id - primary keyParrentId - (foreign key) related to IdTitle.....when I delete some record I want to delete it with all childs (cascadedeleting). I can't set cascade deleting on the same table :(. Is thereany easy way in the MSSQL 2005 to do this ? There is one idea - usingcursors + recursive functions but I think this solution is not easyand elegant.Thakns for any help and sugestions.Regards.Andy
View 4 Replies
View Related
Mar 5, 2008
Hi:
Is there a resource or an object in Reporting Services that I could make a tree view in a report file .rdl and see the result on it?
If not, is there a software of MS that a could make a component for it, and connect this component in the report file .rdl ?
View 6 Replies
View Related
Apr 16, 2007
I'm having this problem.....
I wanted to use the Decision Tree to show a result..... after i configure the Mining Structures..... and set all the input.... my decision tree shows only until level 2..... i have 3 input and one PredictOnly column.....where is the other input?
Say.... i have House Owner, Marital Status, Num Cars Owned and Number Of Children(PredictOnly)
my Tree only shows All ---- > Marital Status when i input all 3 together...... the other 2 doesn't seems to show.
wat should i do?? my database in SQL Server and the other keys are all correct and deploying finely.....why is this happening.....?
i'm a newbie in this software.......so any pro here can plz help me if there's actually something that i might have missed out along the way.......
Thank you again.........
View 1 Replies
View Related
Feb 28, 2008
Alright what im trying to do is build a classic tree, i have id's like this set up in my table referencing itself in sql 2000
cmponent_prt_no parent_part_no
--------------- --------------
112837A2A L115100-1
114379A1A L115100-1
115623A1A L115100-1
203604A L115100-1
203790A L115100-1
203791A L115100-1
115623A 115623A1A
M010137 115623A1A
115623A 115623A1A
20766CR 115623A1A
DRAWINGS 20766CR
M010137 20766CR
i have tried 3 different solutions all coming to a crash at some point, if ANYONE can help me out i would appreciate it, i know the logic will be recursive, i just dont know how to implement
View 8 Replies
View Related
Aug 21, 2007
Hi..I have a table register..in this fields are username,parent id,downline ; I have to determine all the child of a particular parent.
suppose table is like this. username parentid downline
B A left
C A right
D B left
E B right....
I have to also determine the level in the tree....please help...
View 1 Replies
View Related
