analysis services 2000 (data mining)

i create a model mining

CREATE MINING MODEL [a'S]
( [Cusid] LONG KEY ,
[Orderdetails] TABLE PREDICT ([Productid] LONG KEY )
) USING Microsoft_Decision_Trees

i can show for customer some products other when he choise a product.

ect: he choise A, i show he B, C, D ....

but for that, i must know his cusid.

now i want show B,C,D when one man choise A ( i don't know his cusid). how i do that.

( i'm using sql server and analysis service 2000)

You don't need to supply the customer id for prediction. The customer ID is only ysed to identify records and has no impact on the patterns in the model.|||

i can't do.

i use select query:

SELECT FLATTENED
[t1].[cusid],TopCount( Predict(.[Orderdetails], INCLUDE_STATISTICS, EXCLUSIVE), $ADJUSTEDPROBABILITY, 3)
FROM

PREDICTION JOIN
SHAPE
{
OPENROWSET
(
'SQLOLEDB.1',
'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SIT;Data Source=(local)',
'SELECT "cusid" AS "Cusid" FROM "customers" ORDER BY "cusid"'
)

}
APPEND
(
{
OPENROWSET
(
'SQLOLEDB.1',
'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SIT;Data Source=(local)',
'SELECT "cusid" AS "Cusid_1", "productid" AS "Productid" FROM "vwAllAgeSex" ORDER BY "cusid"'
)
}
RELATE [Cusid] TO [Cusid_1]
)
AS [Orderdetails]
AS [T1]
ON
.[Cusid] = [T1].[Cusid] AND
.[Orderdetails].[Productid] = [T1].[Orderdetails].[Productid]

the result:

cusid productid $support $adjustedprobability

1 12 119 0.92

1 320 40 0.34

1 300 23 0.31

3 12 54 0.76

3 19 54 0.76

.......

so when i select product with productid = 12. how i show other relation products

( examples: when i choose a t-shirt product, i want show relation products: shoes, jeans trousers.... )

sql

Analysis Service Deployment Error

Hi,

I created a SQL Server 2005 Analysis Service project. When I tried to deploy a standard Decision Tree model, it gave me errors (see below). Clearly, I can't use ntext data type with DISTINCT, but how can I change the SQL command since it was automatically created? What was the final impact of removing the DISTINCT word from the SQL command?

SQL queries 1
SELECT DISTINCT [dbo_Training_x0020_Data].[Hellos] AS [dbo_Training_x0020_DataHellos0_0]

FROM [dbo].[Training Data] AS [dbo_Training_x0020_Data]

Error Messages 1
OLE DB error: OLE DB or ODBC error: The ntext data type cannot be selected as DISTINCT because it is not comparable.; 42000.

Please assist!

MaryYou can change the data type in the DSV, or add a calculated DSV column that casts the ntext to text

Ambiguous column name error

Hi all,
I am a newbee at SQl and replication. But I have setup a Transactional
replication model, the Source server is both publisher and Distributor, and
SQL 2000 SP4 is used.
The snapshot is created, but the distribution fails. And the following error
is generated, 'ambiguous column name' with a column specified. Is there
anything that we can do about this or did the developer used the wrong naming
conventions in his desgin?
Help would be greatly appreciated!!!
Remco
Anyone?

Am I takin this Normalization too far?

Does this show "poor" design? It has been suggested to me to do a "Logical Model" of my data base and that will make it easier to "normalize" the tables. I tried this and come up with the following but I don't know if I am stretching it too thin. One rule of the 2NF is to ensure all tables have a primary key, and as you can see, my tbProjectTeam has a primary key, but that is made up of the entire row. Same goes for the tbDepartmentActivities.

tbEstimatedProjects
Reference (PK) | Name | City | Postal |...
------------------
1 | Some Project | Niagra Falls | N8E7J5 | ...

tbAwardedProjects
Project (PK) | Reference
--------
1001 | 1

tbProjectTeam
Project (PK)| Login (PK) | Activity (PK)
-------------
1001 | jsmith | Detailer

tbEmployees
Login (PK) | First | Last |.....
-----------
jsmith | Jim | Smith |....

tbDepartmentListing
Login | DeptCode
-------
jsmith | ENG

tbDepartments
Code | Department
--------
ENG | Engineering

tblDepartmentActivities
Code (PK) | Activity (PK)
-------
ENG | Engineering
ENG | Detailer

Am I taking this too far or is the above structure something to be expected by a "good" normalized table structure?

Mike BThere is nothing inherentyly wrong with an entire row being a primary key, especially if the table contains only two or three columns. I do suspect that your design needs some tweaking.

It looks like you are dealing with the following entities: Projects, Employees, Departments, DepartmentActivities. Other tables should establish relationships between these primary entities, such as ProjectMembers.

Do ProjectTeams exist as persistent entities to which projects are assigned, or do they merely represent the individual employees assigned to work on a given project. If they exist as teams, then you will need an entity table for them as well.

Can an employee work on more than one team?

Can more than one team work on a project?

These questions determine whether you need additional tables for establishing many-to-many relationships.|||[SIZE=1]Originally posted by blindman
It looks like you are dealing with the following entities: Projects, Employees, Departments, DepartmentActivities. Other tables should establish relationships between these primary entities, such as ProjectMembers.

ProjectMembers would be the ProjectTeams table.

Do ProjectTeams exist as persistent entities to which projects are assigned, or do they merely represent the individual employees assigned to work on a given project. If they exist as teams, then you will need an entity table for them as well.

Represents the individual employees assigned to an activity on a given project within their respected department.
Example:

Project | Login | Activity
----------
1001 | jsmith | Engineer
1001 | jsmith | Detailer
1001 | mblack | Manager
....
....
1926 | jsmith | Manager // Must have been promoted!

There are no set teams, so a team entity would not exist. Any thoughts?

Mike B|||Then what is tbAwardedProjects for? Does it just indicate whether a project's status has changed from "Estimated" to "Awarded"? If so, this should be a field in the project table, and not a separate table.|||Originally posted by blindman
Then what is tbAwardedProjects for? Does it just indicate whether a project's status has changed from "Estimated" to "Awarded"? If so, this should be a field in the project table, and not a separate table.

Point well taken. This I did because the "project number (eg.1001)" is only assigned if the project is awarded. This project number must be unique. Since it must be unique and a unique value cannot be null, awarded projects need to be their own entity. This system is 2 parts (Cost Control / Cost Estimate). The cost estimate uses the reference as a key and the cost control uses the project number as a key. Make sence?

Mike B|||I'd recommend creating an internal ID that is assigned to every project (either Identity or GUID) and then make your ProjectNumber a separate field. ProjectNumber would be defined as Unique and indexed, but would not be the primary key and could thus allow NULL values. Then you can dispense with the "Awarded" column, because the mere presence of a ProjectNumber value would indicate that the project has been awarded.|||Originally posted by blindman
I'd recommend creating an internal ID that is assigned to every project (either Identity or GUID) and then make your ProjectNumber a separate field. ProjectNumber would be defined as Unique and indexed, but would not be the primary key and could thus allow NULL values. Then you can dispense with the "Awarded" column, because the mere presence of a ProjectNumber value would indicate that the project has been awarded.

Hmmm, that worked, thank you. I tried that in the SQL Server Diagram Editor and it wouldn't save the table as it at first, but then I tried a fresh database and it worked! Thanks...

Mike B

alternatives to adjacency model? (hierarchical data)

Hello Gurus,
I am looking for a hierarchical modeling technique other than
adjacency.
Simplicity is the goal. Updates and inserts must be reasonably easy.
For example, I'd like to see some modeling options other other this:
CREATE TABLE [Hier] (
[ID] [int] NOT NULL ,
[PARENT_ID] [int] NULL ,
CONSTRAINT [PK] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Parent] FOREIGN KEY
(
[PARENT_ID]
) REFERENCES [Hier] (
[ID]
)
)
Anyone know of any (links are often helpful)?
Thank you,
-KJ<n_o_s_p_a__m@.mail.com> wrote in message
news:1123788975.065397.6950@.g47g2000cwa.googlegroups.com...
> Hello Gurus,
> I am looking for a hierarchical modeling technique other than
> adjacency.
> Simplicity is the goal. Updates and inserts must be reasonably easy.
>
Well, there's the nested set model. But it simplifies nested searching and
complicates inserts and updates.
For instance in this example
http://groups-beta.google.com/group...b60b7151?hl=en&
This is required to insert a node
BEGIN
DECLARE right_most_sibling INTEGER;
SET right_most_sibling
= (SELECT rgt
FROM Personnel
WHERE emp = :your_boss);
UPDATE Personnel
SET lft = CASE WHEN lft > right_most_sibling
THEN lft + 2
ELSE lft END,
rgt = CASE WHEN rgt >= right_most_sibling
THEN rgt + 2
ELSE rgt END
WHERE rgt >= right_most_sibling;
INSERT INTO Personnel (emp, lft, rgt)
VALUES ('New Guy', right_most_sibling, (right_most_sibling + 1))
END;
And you probably should introduce a transaction and take a TABLOCKX on
Personnel to control concurrency.
David|||Your model lacks circular reference prevention. Consider something like the
solution suggested in this thread:
http://msdn.microsoft.com/newsgroup...891d&sloc=en-us
ML|||Trees in SQL: Nested Sets and Materialized Path
http://www.dbazine.com/oracle/or-articles/tropashko4
SQL Lessons
http://www.dbmsmag.com/9604d06.html
Maintaining Hierarchies
http://www.windowsitpro.com/Article...=glance&s=books
AMB
"n_o_s_p_a__m@.mail.com" wrote:

> Hello Gurus,
> I am looking for a hierarchical modeling technique other than
> adjacency.
> Simplicity is the goal. Updates and inserts must be reasonably easy.
> For example, I'd like to see some modeling options other other this:
> CREATE TABLE [Hier] (
> [ID] [int] NOT NULL ,
> [PARENT_ID] [int] NULL ,
> CONSTRAINT [PK] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Parent] FOREIGN KEY
> (
> [PARENT_ID]
> ) REFERENCES [Hier] (
> [ID]
> )
> )
> Anyone know of any (links are often helpful)?
> Thank you,
> -KJ
>|||Go out and buy a copy of TREES & HIERARCHIES IN SQL. It will save you
a lot of trouble and pay my mortgage.