cluster discrimination scores

Dear all here:

I have a question about the cluster mining result.

In the discrimination tab shows the different between group 1 and group 2.

I just wonder how to calculate the discremination scores.

Do any expertise can answer the question?

Regards!

Jerry

This is calculated with statistical method. We assume each cluster constitutes a Gaussian/Multinomial distribution on a continuous/discrete attribute. The parameters of these distributions are then calculated for each cluster. For each specific attribute value, we can then evaluate its probability by combining the cluster distributions, the given attribute value (all other attribute are set to unknown). The discrimination score for each attribute value is calculated based on the above probability. Anyway, the math formulas used to calculate the final scores are very complicated and undocumented yet. So I won’t go into every detail here.

Thanks,

---

Yimin Wu, SQL Server Data Mining

CLR Stored Procedure in Reporting Services

Hi

I have created CLR Stored Procedure and my CLR stored procedures are not appearing in the Stored Procedures drop-down list located on the Data tab within the VS2005 Business Intelligence Development Studio.

After Creating the dll I have registered the dll like that

CREATE ASSEMBLY MY_SP_NAME from 'C:\MY_DLL_PATH\MY_DLL_NAME.dll'
WITH PERMISSION_SET = SAFE


After registering I have deployed the dll :


CREATE PROCEDURE [dbo].[MY_SP_NAME]
@.dbname [nvarchar](4000),
@.varTable [nvarchar](4000),
..............
..............
..............

AS
EXTERNAL NAME [MY_DLL_NAME].[MY_CLASS_NAME].[MY_SP_NAME]
GO
EXEC sys.sp_addextendedproperty @.name=N'AutoDeployed', @.value=N'yes' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFile', @.value=N'EXAMINER_WORKSHEET_F_hmz4_Ex.cs' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFileLine', @.value=10 , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO



The Stored Procedure is deployed successfully to SQL Server 2005, but its not showing in the drop-down list located on the data tab while I am desiging my RDL layout.



All of the non-CLR stored procedures are present. Is there something I need to do to add these CLR stored procedures?

Please please ASAP.

Thanks
Tareqe

Unfortunately, due to an issue with the SQL OLEDB Provider (I think), CLR stored procedures are not returned when the database schema is queried. Instead, on the Data tab change the Command type to Text and use an expression-based query text, e.g.

="EXEC [dbo].[MY_SP_NAME]" & Parameters!dbName.Value & ", " & Parameters!varTable.Value

|||

I had the same issue. To get it to show up in the stored procedure list we created a TSQL Wrapper for the CLR stored Procedure.

wrapper called tsql_my_sp_name looks something like this. then you can pick tsql_my_sp_name from the list in report designer and assign dynamic parameters like any other SP.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

-- =============================================

ALTERPROCEDURE [dbo].[tsql_my_sp_name]

-- Add the parameters for the stored procedure here

@.dbname nvarchar(4000),

@.varTable nvarcar(4000),

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

-- Insert statements for procedure here

EXEC my_sp_name @.dbname, @.varTable

END

CLR Stored Procedure in Reporting Services

Hi

I have created CLR Stored Procedure and my CLR stored procedures are not appearing in the Stored Procedures drop-down list located on the Data tab within the VS2005 Business Intelligence Development Studio.

After Creating the dll I have registered the dll like that

CREATE ASSEMBLY MY_SP_NAME from 'C:\MY_DLL_PATH\MY_DLL_NAME.dll'
WITH PERMISSION_SET = SAFE


After registering I have deployed the dll :


CREATE PROCEDURE [dbo].[MY_SP_NAME]
@.dbname [nvarchar](4000),
@.varTable [nvarchar](4000),
..............
..............
..............

AS
EXTERNAL NAME [MY_DLL_NAME].[MY_CLASS_NAME].[MY_SP_NAME]
GO
EXEC sys.sp_addextendedproperty @.name=N'AutoDeployed', @.value=N'yes' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFile', @.value=N'EXAMINER_WORKSHEET_F_hmz4_Ex.cs' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFileLine', @.value=10 , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO



The Stored Procedure is deployed successfully to SQL Server 2005, but its not showing in the drop-down list located on the data tab while I am desiging my RDL layout.



All of the non-CLR stored procedures are present. Is there something I need to do to add these CLR stored procedures?

Please please ASAP.

Thanks
Tareqe

Unfortunately, due to an issue with the SQL OLEDB Provider (I think), CLR stored procedures are not returned when the database schema is queried. Instead, on the Data tab change the Command type to Text and use an expression-based query text, e.g.

="EXEC [dbo].[MY_SP_NAME]" & Parameters!dbName.Value & ", " & Parameters!varTable.Value

|||

I had the same issue. To get it to show up in the stored procedure list we created a TSQL Wrapper for the CLR stored Procedure.

wrapper called tsql_my_sp_name looks something like this. then you can pick tsql_my_sp_name from the list in report designer and assign dynamic parameters like any other SP.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

-- =============================================

ALTERPROCEDURE [dbo].[tsql_my_sp_name]

-- Add the parameters for the stored procedure here

@.dbname nvarchar(4000),

@.varTable nvarcar(4000),

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

-- Insert statements for procedure here

EXEC my_sp_name @.dbname, @.varTable

END

Client Statistics / Network Statistics

I am trying to track down a slowly preforming query and am looking at the client statistics tab; specifically the Network Statistics and Bytes received from server.

I have two servers one running SQL Server 2000 and the other running SQL Server 2005, 64-bit.

The data on the servers is similar, some schema differences, some record differences -- but nearly 32 million records in one table.

The same query is taking longer on the new SQL Server 2005 machine. The main difference that I see on the client statistics tab is the bytes received from server is almost always triple on the new server.

Were there any changes in the information SQL Server 2000 and 2005 reports back? Could the 64-bit be the cause for the increase (might make sense if it were double, but not triple)?

Any other ways I can eliminate this metric from being the cause of the slow running queries?

Thanks, Richard

If I run a simple SELECT * FROM Orders against the Northwind database on SQL Server 2000 with Query Analyzer or on SQL Server 2005 (32-bit) with Management Studio I get 830 rows on each, and 157,065 bytes on 2000 vs 157,097 on 2005. (Get the Northwind database from here http://msdn2.microsoft.com/en-us/library/ms143221.aspx). Try it on your 64-bit server and see what you get. If you also get 157,097 then maybe you're using different data types in your database on the 64-bit server?|||

Hi Richard,

I don't know the answer to the question of client statistics but I would personally check the execution of both of your queries through Profiler. Look at READS and CPU for both versions and see if there is a difference internally to SQL Server before checking to see the results being sent back to the client.

The DURATION counter could be misleading in your situation because the time taken to transfer the data back to the client (and the client to close the resultset) will factor into duration (actually, the DURATION counter is usually misleading due to issues of locking, blocking, client conneciton remaining open, low bandwidth to server, Disk bottlenecks, etc).

In regards to 64-bit showing double the number of characters, everything I've read suggests that 64-bit is the same code base and that your client app should not notice any difference between connecting to a 32-bit SQL Server or a 64-bit SQL Server.

You didn't mention how many rows and columns were returned by each query but if your data and schema is not 100% identical on both servers then I would assume the issue is in the data being returned.

Jared

|||

I started with SQL Profiler and saw that the duration for my complex query was almost always slower on my new server. There are definite differences in the execution plans, but the number of rows returned is 23 and the fields requested are identical -- there are differences in the length of some of the varchars, but the actual data returned is identical.

Schema and rows shouldn't be a factor -- indexes and statistics are the same, execution plans are different and the "Bytes received from server" are different. Time statistics are slower.

I'm in the process on loading Northwind on these two servers so that I can compare with the other respondent. However, a DBA in another department ran a similar test against SQL Server 2000, SQL Server 2005, and SQL Server 2005, 64-bit and found that the Bytes received was greater (double to triple) on SQL Server 2005; reguardless of the 64-bit.

I'll run some more tests and post my results -- I just hope that I'm not chasing some difference in the "Client Statistics" tab between 2000 and 2005.

Thanks for your interest, Richard