I will be grateful if you could answer a few more questions around Analyzing Key Influencers
1. When specifying the training data for Decision Tree, there is a SUGGEST button (Recommend inputs for currently set predictable) which recommends which input are related to the predictable attribute. It also gives a €˜Score€™ for each recommended inputs. What algorithm does the SUGGEST button use? Does it use simple entropy/correlation based algorithm OR sophisticated feature selection algorithms?
2. Can I access this €˜Score€™ and recommended inputs above programmatically?
3. What feature selection algorithms are used in SQL Server 2005? Can they be invoked programmatically?
5. In Logistic Regression mining model viewer, we get a chart which clearly shows what attributes favor which state of the predictable attribute. For example, income level < 23000 favors BikeBuyer = 0 (does not buy) with a score of 89.00. What algorithm is used to calculate the €˜Score€™? Can LR be used as a feature selector in case where the predicted attribute is binary (select the attributes that favor one state or the other with a score of, say, greater than some threshold)?
6. You suggested using Naive Bayes to find AKIs. What if the input attributes are all continuous (predicted attribute binary)? Shouldnt I be going for LR?
Excuse the elementary question; I am new to this feature.
No matter what dataset I use, I get the following error: "The task was not able to detect any key influencers for the 'xxx' column. The values of 'xxx' seem unrelated to values of other columns."
I have installed the excel DM addin and am trying to work through the tutorials -
When I run the 'Analyze Key Influencers' tool against the sample data through a remote AS server I get: The task was not able to detect any key influencers for the 'Purchased Bike' column. The values of 'Purchased Bike' seem unrelated to values of other columns.
however when I run it against a local AS server I get the expected results.
I can see no differences in settings or setup between the AS instances I am trying to use - perhaps a permissions issue? Thank you
Hello, friends! sorry for the stupid question: I created a new index on table and I'm looking to a command equal to "Analyze table ", "Compute statistics "in Oracle to check if that index is usefull.
Dear All, i've used the DBCC showcontig command against my table table103 but i dont know how to analyze the results of fragmentation levels. please give me some explanations or some good links.....
Anybody nows a tool to analyze LDF files in MS SQL Server 2000?I mean, a tool that converts a LDF file in a set of SQL transactions?(similar to dbtran in sybase)thanks!
Hi, the other day, some data was deleted by mistake, the data that we wanted to delete was in just 1 table, and we deleted the related data in a couple tables more...
We do full backups every Sunday and a Differential every day, my question is:
Is there any way to analyze the backup file to compare the backed up data with the data that the table has now, and by automatic means restore just some rows to the table, or at least see the data to insert it manually?
Msg 245, Level 16, State 1, Procedure CompactL1RecordsFromfirstINTRAD, Line 177 Conversion failed when converting the varchar value 'DECLARE rows_cursor CURSOR FOR SELECT ask, dateTimed FROM iDay_Compr_GOOG WHERE DAY (dateTimed) = ' to data type int.
I get this message while executing a stored procedure. It works "half way" but skips this statement and the whole block as a result. In the table @table_name ask is declared as float and dateTimed as DateTime. Similar statements work happily in other stored procedures with no problem. Even in the same stored procedure I have places where I use DAY (dateTimed) and they seem to work as far as I can see although it is a very branched out code with many IF ... ELSE's.
SET @SQL = 'DECLARE rows_cursor CURSOR FOR SELECT ask, dateTimed FROM '+@table_name+' WHERE DAY (dateTimed) = '+@day+' AND NOT ask = 0' exec sp_sqlexec @SQL OPEN rows_cursor FETCH LAST FROM rows_cursor INTO @askQ, @lastTableDateTime SET @SQL = 'UPDATE '+@table_name+' SET askSize = askSize + '+@askSize+' WHERE dateTimed = '+@lastTableDateTime+' AND ask = '+@askQ exec sp_sqlexec @SQL -- <== this is line 177 CLOSE rows_cursor DEALLOCATE rows_cursor
Another puzzle for me is the line number. I used Edit-->GO TO--> 177 to single the line out and it seems to point to a different exec sp_execsql @SQL statement, the one that is down the road.
I can make neither head nor tail out of it. I am sure Jens, Cetin or Andrea or whoever stumble on this post will be able to figure this out. Anyone else's help will also be appreciated.
I have installed SP2 on my laptop and I have installed the latest Data Mining Add-Ins.
When I open the sample spreadsheet and select the table in a worksheet called "Table Analysis Tools Sample", I dont get Analyze ribbon under Table Tools. Is ther any reson for it?
I dont get Data Mining option either! Is there something I have to do before getting those menus to appear in the ribbon?
I have been through "Getting Started" and set the AS connection to the local AS Server.
Edit 2007-8-9: Added code to show database file sizes. Not really closely related to tables sizes, but a lot of the people who need this want to know why their database it so large, so it may help to know which files, especially the logs, are so large, and if the files have empty space in them.
-- Script to analyze table space usage using the -- output from the sp_spaceused stored procedure -- Works with SQL 7.0, 2000, and 2005
set nocount on
print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'
select [FileSizeMB]= convert(numeric(10,2),sum(round(a.size/128.,2))), [UsedSpaceMB]= convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) , [UnusedSpaceMB]= convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) , [Type] = case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end, [DBFileName]= isnull(a.name,'*** Total for all files ***') from sysfiles a group by groupid, a.name with rollup having a.groupid is null or a.name is not null order by case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end, a.groupid, case when a.name is null then 99 else 0 end, a.name
create table #TABLE_SPACE_WORK ( TABLE_NAME sysnamenot null , TABLE_ROWS numeric(18,0)not null , RESERVED varchar(50) not null , DATA varchar(50) not null , INDEX_SIZE varchar(50) not null , UNUSED varchar(50) not null , )
create table #TABLE_SPACE_USED ( Seqintnot null identity(1,1)primary key clustered, TABLE_NAME sysnamenot null , TABLE_ROWS numeric(18,0)not null , RESERVED varchar(50) not null , DATA varchar(50) not null , INDEX_SIZE varchar(50) not null , UNUSED varchar(50) not null , )
create table #TABLE_SPACE ( Seqintnot null identity(1,1)primary key clustered, TABLE_NAME SYSNAME not null , TABLE_ROWS int not null , RESERVED int not null , DATA int not null , INDEX_SIZE int not null , UNUSED int not null , USED_MBnumeric(18,4)not null, USED_GBnumeric(18,4)not null, AVERAGE_BYTES_PER_ROWnumeric(18,5)null, AVERAGE_DATA_BYTES_PER_ROWnumeric(18,5)null, AVERAGE_INDEX_BYTES_PER_ROWnumeric(18,5)null, AVERAGE_UNUSED_BYTES_PER_ROWnumeric(18,5)null, )
declare Cur_Cursor cursor local for select TABLE_NAME= rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE= 'BASE TABLE' order by 1
) select TABLE_NAME, TABLE_ROWS, RESERVED, DATA, INDEX_SIZE, UNUSED, USED_MB= round(convert(numeric(25,10),RESERVED)/ convert(numeric(25,10),1024),4), USED_GB= round(convert(numeric(25,10),RESERVED)/ convert(numeric(25,10),1024*1024),4), AVERAGE_BYTES_PER_ROW= case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),RESERVED))/ convert(numeric(25,10),TABLE_ROWS),5) else null end, AVERAGE_DATA_BYTES_PER_ROW= case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),DATA))/ convert(numeric(25,10),TABLE_ROWS),5) else null end, AVERAGE_INDEX_BYTES_PER_ROW= case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),INDEX_SIZE))/ convert(numeric(25,10),TABLE_ROWS),5) else null end, AVERAGE_UNUSED_BYTES_PER_ROW= case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),UNUSED))/ convert(numeric(25,10),TABLE_ROWS),5) else null end from ( select TABLE_NAME, TABLE_ROWS, RESERVED= convert(int,rtrim(replace(RESERVED,'KB',''))), DATA= convert(int,rtrim(replace(DATA,'KB',''))), INDEX_SIZE= convert(int,rtrim(replace(INDEX_SIZE,'KB',''))), UNUSED= convert(int,rtrim(replace(UNUSED,'KB',''))) from #TABLE_SPACE_USED aa ) a order by TABLE_NAME
print 'Show results in descending order by size in MB'
select * from #TABLE_SPACE order by USED_MB desc go
drop table #TABLE_SPACE_WORK drop table #TABLE_SPACE_USED drop table #TABLE_SPACE
Here is a script I wrote that analyzes datasets and returns all the minimal composite and unary keys that uniquely identify records. I wrote it because I frequently have to analyze client spreadsheets and non-normalized data tables.
On my desktop server it took about two minutes to analyze 2000 permutations of a table with 50 columns and 5000 records.
Please try it out for me and let me know if it chokes on anything, or if you see any ways it could be improved!
I want to analyze procedure cache, to find inefficient plans and parameter issues.
I do it trow DMV But my requests to DMV are very slow and demand resources because procedure cache is about several GB Actually I dont need on-line analysis.
Is it possible to have fast snapshot of procedure cache?
We are using the whole BI-package from Microsoft - from SQL, DTS-package,Raporting Service and Analyze Service.
It should be very helpful to be able to create a metadata databases where you could find all releations between different objects (tables,views,reports,cubes,DTS-package,Databases.
Just to get answer for: 'where is view xxx used', 'what are Report xxx depending upon'.
While everything exists in different SQL databases it should possible to do.
1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??
2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??
I have some tasks that I need to accomplish within T-SQL but cannot find a means to accomplish them.
They are..
1. Check for the existance of an external text file.
2. Count the number of rows in an external text file.
3. Be able to run the BCP command from within T-SQL. I am currently using the BULK INSERT command which works fine but it does not allow the following..
I am tying to call BCP to output the contents of a table to a text file from with in a stored procedure. The procedure will be called from an ASP page ... My question is were does the file get created ??? I want to create the text file on server ONE and SQL server is running on server TWO and IIS is running on server THREE... do i have to have a drive letter mapped to server ONE and if so is it mapped on the SQl server or the IIS server ....
ie: exec master..xp_cmdshell bcp db..table out h:est.out -Uxx -Pxx -Sx
I have some tasks that I need to accomplish within T-SQL but cannot find a means to accomplish them.
They are..
1. Check for the existance of an external text file.
2. Count the number of rows in an external text file.
3. Be able to run the BCP command from within T-SQL. I am currently using the BULK INSERT command which works fine but it does not allow the following..
I have two questions, 1) Could anyone please point me in the right direction concerning information pertaining to NT Server Enterprise Edition verses NT Server Workstations. We are having problems running SQL Server 7.0 and the Enterprise Edition together on the same machine and was wanting to find information about compatability issues, if there are any, 2) I, on a SQL Server 6.5 database shrunk it by 2 Gb. When I looked to see if SQL Server released those 2 Gb back to the hard drive, I was amazed that it didn't! Did I miss something or will SQL Server 6.5 not release the space because of the initial set-up. And why did SQL Server 6.5 automatically take the space from the Transaction logs when neither of the devices were specified? Is the Transaction Log the default area for shrinkage?
Thanks in Advance! Daimon Russell daimon_r@hotmail.com
1. When we create DTS in SQL Server through DTS designer, where are they stored physically? 2. What would be the best way to modify a DTS without using DTS designer? 3. Is there any other way to create DTS apart from DTS designer and Visual Basic? 4. Is there any website which has detailed information for DTS? (which has more FAQs like above?)
In our production environment, we keep changing the servers frequently, and everytime that happens, I have to change the connection properties in all the DTS going to them one by one.
I am not too familiar with SQL Server, but my supervisor gave me the task of finding out the difference between SQL Enterprise and SQL Standard. He also asked me to research the difference between processor licences and client access licences. I will use the Internet as a resource, but I would also like to hear the opinions of someone who uses these programs or is knowledgable about them. So please any suggestions or any useful links would be very helpful.
I have two questions. 1) If a database is suspect we can have that trace from sysdatabases.There is a column named status.My question is in case of suspect datatbase what will be value in the field status of sysdatabases? 2) The password of an user login(created by using sp_addlogin stored procedure or any other way) is stored in the table sysxlogins of master database.The password is stored in a varbinary format.How can I get the actual password(means in a char format)?I mean how can I convert the varbinary value to a readable format?
Hi All, I am new in SQL SERVER 2000.I have few questions - 1) WHAT WILL I DO TO TRUNCATE THE SIZE OF A TRANSACTION LOG? 2) WHAT WILL BE THE STEPS OF BUILDING THE MASTER DATABASE? 3) WHAT WOULD BE THE PLAN OF ACTION WHEN SQL DOES NOT STARTS UP? 4) WHAT WOULD BE MY PLAN OF ACTION WHEN SQL DB GETS CORUPTED OR STARTS IN A SUSPECT MODE?
Could I do periodicity backups to another computer(mediaserver) using VDI??
I mean , Could I config a Virtual Device so that I can do backups like disk or tape, I can use 'backup database ...to virtual_device='...' ' to do backup to another computer(mediaserver)? suppose that I have finished the interface of mediaserver.
If this is impossible,how can I do periodicity backups to another computer??
I have finished a program using VDI that can do backup to another computer,and I know how to do periodicity backups to disk or tape. but I am puzzled about the periodicity backup using VDI.
I am putting together a proposal for my church, the current DB software (Access) has been outgrown. One proponent of a no name brand software insists that to implement SQL could take a year and a team of programmers, is this true??
Hi Could any one tell me the answers for these questions.
1. how do we troubleshoot a datbase if it is in suspect mode.What is the reason for a database to be in suspect mode. 2. how can we move a file from C drive to D drive, so that the file location in C drive is completely moved . 3.In a particular primary file group there are many objects. How can we move some of the objects from this primary file group in to another file group. 4. can we install a sql server on a remote server so that it will not ask any inputs like domain name, authentication modes. . 5.If we want to implement clustering , can we use the virtual ip address on clustering as the ip address of our system or we have to use another ip address for clustering 6. In 2000 we have DTs package.can we run the same DTS package in 2005.
Hi Im relatively new to ASP/SQL and have been thrown into the deep end by work. Ive got courses to go on, but not for another 3-6months.
Ive got an ASP file calling a database using SQL. Once you hit the "go" button, it puts this data into an Excel file, under a new window (still showing the asp file in the address bar).
It currently shows:
1) Item numbers that end in "0" i.e. 3.10, 12.20 appear as 3.1 and 12.2 respectively in the Bill of Materials … i.e. being treated as decimals … I need them to show-up as text. ************************************************** ******************* 2) When saving the spreadsheet … it would be good to get "X" to set the default file name to: <Quote ID>_<Customer>_<Platform>_<version>.[xls|pdf] ************************************************** ******************* 3) When a spreadsheet is displayed, it is in an editable Excel format. Is it possible to lock the file automatically when its opened or password protect it? ************************************************** ******************* 4) If a spreadsheet window is open and you try and open a new one, the old one pops-up/is still there. Need to be able to close the old one and re-open a new one automatically.
If necessary i will post the code. Any takers? Thanks
