Hi, all experts here,
Would any of you give me any ideas for how could we optimize the report on data mining models? (as we know, for the data mining report, we have to select the mining model and the case table)
Hope it is clear for your advices and help.
Thanks a lot in advance and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi, all here,
I am wondering where can I store my mining results in data mining engine? For example, I got mining results like accuracy chart, decision trees, and other formats of results based on different mining algorithms I used for my data mining, so where can I actually store the results for reporting service use later? Is it possible to do that in SQL Server 2005?
Thanks a lot for any help and guidance in advance.
After I make a data mining, how can I put it on a report to see on the Internet Explorer?
View 9 Replies View RelatedHello Everybody,
I have a small tricky problem here...need help of all you experts.
Let me explain in detail. I have three tables
1. Emp Table: Columns-> EMPID and DeptID
2. Dept Table: Columns-> DeptName and DeptID
3. Team table : Columns -> Date, EmpID1, EmpID2, DeptNo.
There is a stored procedure which runs every day, and for "EVERY" deptID that exists in the dept table, selects two employee from emp table and puts them in the team table. Now assuming that there are several thousands of departments in the dept table, the amount of data entered in Team table is tremendous every day.
If I continue to run the stored proc for 1 month, the team table will have lots of rows in it and I have to retain all the records.
The real problem is when I want to retrive data for a employee(empid1 or empid2) from Team table and view the related details like date, deptno and empid1 or empid2 from emp table.
HOw do we optimise the data retrieval and storage for the table Team. I cannot use partitions as I have SQL server 2005 standard edition.
Please help me to optimize the query and data retrieval time from Team table.
Thanks,
Ganesh
I followed the tutorial posted at [URL] ...
Everything was ok until the last step where I had to process the mining structure which resulted in a warning
"Informational (Data mining): Decision Trees found no splits for model, Tbl Decision Tree Example."
What does this error mean? How do I resolve it? Also, I only see the first level in the Mining Model Viewer, I don't see the levels 2 and 3.
Hi,
I have recently taken up performance optimization activity for our database. Can any one suggest a really good source for articles/tutorials/guides etc. on Performance optimization for SQL server 2005.
Regards
Yogi
Hi, all experts here,
I would like to know if there is any way to migrate third-party data mining packages with SQL Server 2005 data mining algorithms together then we can have a comparison among all of them to get the best results for training models.
I am looking forward to hearing from you.
Thanks a lot.
With best regards,
Yours sincerely,
Hoping someone will have a solution for this error
Errors in the metadata manager. The data type of the '~CaseDetail ~MG-Fact Voic~6' measure must be the same as its source data type. This is because the aggregate function is not set to count or distinct count.
Is the problem due to the data type of the column used in the mining structure is Long, and the underlying field in the cube has a type of BigInt,or am I barking up the wrong tree?
I'm a beginner with SQL 2012 SSDT & SSMS. I get this error message when I try to deploy my project:Â
"Error 6
Error (Data mining): KEY SEQUENCE columns are not supported at the case level. The 'Customer Key' column of the 'TK448 Ch09 Cube Clustering' mining structure contains content that is not valid.
0 0
"
I am finding it hard to locate the content that is not valid. I've been trying to find a answer for this problem but can't seem to find anything. How can I locate the content that is not valid and change or delete it so that I can deploy this solution?
Having successfully created :
- a data mining structure with about 80 columns.
- a data mining model using Microsoft_Decision_Trees with 2 prediction columns.Â
I thought I would then explore the possibility of have more than 2 prediction columns, in this case 20.
I get an error message and I can't work out :
a) if this is because there's a limit to the maximum number of prediction columns and where that maximum is stated.
b) if something else has become corrupted
c) there's a know bug and if the error message is either meaningful or not.
Either way, I'm unable to complete the data mining wizardÂ
The error message is :Errors in the metadata manager. Either the mining structure with the ID of '[my model Structure]' does not exist in the database with the ID of 'DMAddinsDB', or the user does not have permissions to access the object.
Does anyone know any good references (web, books etc) about optimizing performance for MSSQL Server 6.5, not seen from a developers perspective but more the admin of the SQL Server?
I`m using a very data comprehensive application and I have the feeling things would run a lot smoother if the database/server was optimized once in a while (like you can do with Access).
Anyone?
Hi,
our package have design like this,
OLEDBSource à Derived Column à Lookup
|
Matching Records Un Matched Records
| |
OLEDBCommand OLEDBDestination
(Update) (Insert)
and our source & destination table are oracle. when we execute the package the performance is very low and some times its showing like processing ( yellow color) even for 1 hrs .what could be the problem.can any one help us.is there any reason like when we use orcale database this will slow down the performance of package
Jegan
I am able to get this to work by using nested loops but they are very inefficient and with the size of my tables I cannot afford to use them. There must be a more efficient solution?
I have two tables...
Initial_Procedure
ID
Person_ID
Completed_DTTM
Procedure_2
ID
Person_ID
Completed_DTTM
I need the result to be...
Tbl_Final_Result
Initial_Procedure.ID
Initial_Procedure. Person_ID
Initial_Procedure. Completed_DTTM
Procedure_2.ID
Procedure_2.Person_ID
Procedure_2.Completed_DTTM
Some general rules...
(Hope these are clear enough)
- Each person has at least one Initial_Procedure.
- There may be zero, one, or more Procedure_2 for each Initial_Procedure.
- If there is more than one Procedure_2 for an Initial_Procedure get the most recent.
- To link Procedure_2 to Initial_Procedure the Initial_Procedure.Completed_DTTM < Procedure_2.Completed_DTTM and Initial_Procedure.Person_ID = Procedure_2.Person_ID
- If there is more than one Initial_Procedure where Initial_Procedure.Completed_DTTM < Procedure_2.Completed_DTTM:
Procedure_2.Completed_DTTM Between row 1: Initial_Procedure.Completed_DTTM and
row 2: Initial_Procedure.Completed_DTTM --(assuming Initial_Procedure is in order)
AND Initial_Procedure.Person_ID = Procedure_2.Person_ID
Some example data.....
Declare @Initial_Procedure table (ID int, Person_ID int, Completed_DTTM datetime)
Insert into @Initial_Procedure
Select 1, 1, '01/10/2007' union all
Select 1, 1, '02/15/2007' union all
Select 1, 1, '02/20/2007' union all
Select 1, 2, '01/02/2007' union all
Select 1, 3, '06/26/2007' union all
Select 1, 4, '03/14/2006' union all
Select 1, 4, '10/10/2006' union all
Select 1, 4, '08/27/2007'
Declare @Procedure_2 table( ID int, Person_ID int, Completed_DTTM datetime)
Insert into @Procedure_2
Select 2, 1, '01/09/2007' union all
Select 2, 1, '01/15/2007' union all
Select 2, 1, '01/16/2007' union all
Select 2, 1, '01/17/2007' union all
Select 2, 1, '02/19/2007' union all
Select 2, 1, '07/25/2007' union all
Select 2, 1, '09/02/2007' union all
Select 2, 2, '01/01/2007' union all
Select 2, 2, '01/14/2007' union all
Select 2, 2, '01/20/2007' union all
Select 2, 3, '05/04/2007' union all
Select 2, 3, '06/27/2007' union all
Select 2, 4, '11/06/2006'
The final result should be...
Tbl_Final_Result
IP.ID IP.Person_ID IP.Completed_DTTM P2.ID P2.Person_ID P2.Completed_DTTM
1 1 01/10/2007 2 1 01/17/2007
1 1 02/15/2007 2 1 02/19/2007
1 1 02/20/2007 2 1 09/02/2007
1 2 01/02/2007 2 2 01/20/2007
1 3 06/26/2007 2 3 06/27/2007
1 4 03/14/2006 NULL NULL NULL
1 4 10/10/2006 2 4 11/06/2006
1 4 08/27/2007 NULL NULL NULL
Hi all,
I am using Microsoft_Time_Series and have set HISTORIC_MODEL_GAP to various values (from 1 to 21). I always get this error:
Error (Data mining): The 'HISTORIC_MODEL_GAP' data mining parameter is not valid for the 'My Time Series' model.
In Algorithm Parameters window, this parameters is not there by default, so I have to add it.
Any tip will be greatly appreciated.
Implementing data mining Add-in in an academic setting? We need to handle over 150 new students a semester and have their connection to Analysis Services survive for their four years at the college. We are introducing data mining to every freshman business student as a unit within their Intro to Excel class (close to a month of work to give them a sense of what is possible). Other courses later in their curriculum will expand on that introduction.Â
Once implemented, we would have as many as 900 connections to manage (four years from now). It is possible that multiple sections will be running at the same time, so 40 students may be accessing the data mining tools concurrently. Â
Is there a way to "bulk establish" the access credentials and establish those databases?
Hello,I have a question regarding stored procedure desing that provides theoptimal performance. Let's say we have a table Products that consists ofthree columns: Name, Status, RegistrationTime. All columns are indexed andusers should be able to lookup data by any of the columns. We have two mainoptions to design stored procedures for data retrieval:1. Design separate stored procedures for each search criteria:LookupProductsByName, LookupProductsByStatus, LookupProductsByTime.2. Write a generic stored procedure that will fit any search criteria:CREATE PROCEDURE GetProducts (@Name varchar(20),@Status int = NULL,@FromTime datetime = NULL,@ToTime datetime = NULL)AS BEGINSELECT[Name],[Status],[RegistrationTime]FROM [Products]WHERE [Name]=CASEWHEN @Name<>NULL THEN @NameELSE [Name]ENDAND [Status]=CASEWHEN @Status<>NULL THEN @StatusELSE [Status]ENDAND [RegistrationTime]>=CASEWHEN @FromTimestamp<>NULL THEN @FromTimestampELSE [RegistrationTime]ENDAND [RegistrationTime]<=CASEWHEN @ToTimestamp<>NULL THEN @ToTimestampELSE [RegistrationTime]ENDORDER BY [RegistrationTime]END;The second option is very attractive, because it is obviously easier tomaintain such code. However, I am a little concerned about performance ofsuch stored procedure. It is not possible to foresee what index should beused, index can only be selected each during procedure execution, becausesearch criteria can include either Name, Status or RegistrationTime. Will itmake this SP inefficient? Or perormance difference in such case is not big(if any) and we should choose the second option because of its significantcode reduction?Thanks in advanceVagif AbilovJoin Bytes!
View 3 Replies View RelatedI am facing some performance issues in a Stored Procedure. The procedure
needs to return a resultset based on some search criteria. There are around
20 possible search criteria. Below is the SQL query used in my Stored
procedure. Any help to optimize the search will be great:
--get LOV details in table variables
INSERT INTO @tblLov (LovCode, LovDesc, ParamCode)
SELECT LovCode, LovDesc, ParamCode FROM tp_Lov WITH (NOLOCK)
WHERE ParamCode IN('FileSrc', 'CommTrailInd', 'CommTxnStatus',
'AgencyPrincipalInd','ProdSubType','AuditTransStatus')
--get commission transaction according to the search criteria
INSERT INTO @tblSearchResults
SELECT l1.LovDesc AS TransSource,
l2.LOVDesc AS CommTrailInd,
r.RemitCode as RemitNumber,
t.IntTransId as TransNumber,
CONVERT(VARCHAR, t.TrdDt, 110) AS TradeDate,
CONVERT(VARCHAR, t.SettlementDt, 110) AS SettlementDate,
rp.RepCode,
(ISNULL(rp.LstNm,'') + ', ' + ISNULL(rp.FstNm,'')) AS RepName,
(CASE WHEN ISNULL(t.IntClntId,0)=0
THEN ISNULL(t.ClntShortNM, '') +
(CASE WHEN (t.TransSrc = 'NSM' OR (t.TransSrc = 'MCE' AND
ISNULL(t.ProdType,'') <> 'VA')) AND ISNULL(t.FundAcctNum,'')<>'' THEN ' - ' +
ISNULL(t.FundAcctNum,'')
WHEN (t.TransSrc = 'NSV' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'')
= 'VA')) AND ISNULL(t.PolicyNum,'')<>'' THEN ' - ' + ISNULL(t.PolicyNum,'')
WHEN t.TransSrc IN('PSH','MSR') AND ISNULL(t.ClrHouseAcctNum,'')<>'' THEN
' - ' + ISNULL(t.ClrHouseAcctNum,'')
ELSE '' END)
ELSE dev.udf_COMM_PCD_GetClientName(t.IntClntId, t.IntTransId)
END) AS Client,
(CASE WHEN ISNULL(t.CUSIP,'')='' THEN t.ProdName ELSE p.ProdNm END) AS
[Product],
t.InvAmt AS InvestmentAmt,
t.GDC AS GDC,
t.ClrChrg AS ClearingCharge,
t.NetComm AS NetCommission,
(CASE WHEN t.Status IN(@strLov_TxnStatus_Tobepaid, @strLov_TxnStatus_Paid)
THEN dev.udf_COMM_PCD_GetPayoutRateString(t.IntTransId) ELSE '' END) AS
PayoutRate,
(CASE WHEN t.Status IN(@strLov_TxnStatus_Tobepaid, @strLov_TxnStatus_Paid)
THEN dev.udf_COMM_PCD_GetPayoutAmountString(t.IntTransId) ELSE '' END) AS
Payout,
l3.LOVDesc AS TransStatus,
t.Comments,
t.OrderMarkup AS BDMarkup,
t.IntTransId,
rp.IntRepId,
sch.SchCode,
t.IntClntId,
t.CUSIP,
t.RepIdValue AS RepAlias,
t.RepIdType,
t.SplitInd,
l4.LOVDesc AS AgencyPrincipalInd,
t.AgencyPrincipalFee,
t.EmployeeTradeInd,
t.ShareMarkup,
t.UnitsTraded,
s.SponsorNm,
CASE WHEN t.TransSrc = 'NSM' OR (t.TransSrc = 'MCE' AND
ISNULL(t.ProdType,'') <> 'VA') THEN ISNULL(t.FundAcctNum,'') --Production
Defect #873 & 877
WHEN t.TransSrc = 'NSV' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'') =
'VA') THEN ISNULL(t.PolicyNum,'')
ELSE t.ClrHouseAcctNum END,
CASE WHEN ISNULL(t.ProdSubType,'') IN ('', 'Z') THEN 'Not Defined'
ELSE l6.LovDesc END AS ProdSubType, --t.ProdSubType,
l5.LOVDesc AS TransAuditStatus, --t.TransAuditStatus,
t.TransAuditStatus AS TransAuditStatusCode,
t.OriginalTransId,
t.RowId,
t.Status,
t.intParentTransId,
t.CancelTrdInd,
t.ClrChrgOverrideInd,
9999 AS AuditKey
FROM tr_CommTrans t WITH (NOLOCK)
INNER JOIN @tblLov l1 ON t.TransSrc = l1.LOVCode and l1.ParamCode = 'FileSrc'
INNER JOIN @tblLov l2 ON t.CommTrailInd = l2.LOVCode and l2.ParamCode =
'CommTrailInd'
INNER JOIN @tblLov l3 ON t.Status = l3.LOVCode and l3.ParamCode =
'CommTxnStatus'
INNER JOIN td_Remit r WITH (NOLOCK) ON t.IntRemitId = r.IntRemitId
LEFT OUTER JOIN @tblLov l4 ON t.AgencyPrincipalInd = l4.LOVCode and
l4.ParamCode = 'AgencyPrincipalInd'
LEFT OUTER JOIN @tblLov l5 ON t.TransAuditStatus = l5.LOVCode AND
l5.ParamCode = 'AuditTransStatus'
LEFT OUTER JOIN @tblLov l6 ON t.ProdSubType = l6.LOVCode AND l6.ParamCode =
'ProdSubType'
LEFT OUTER JOIN tm_BDProd p WITH (NOLOCK) ON t.CUSIP = p.CUSIP
LEFT OUTER JOIN tm_BDSponsors s WITH (NOLOCK) ON t.IntBDSponsorId =
s.IntBDSponsorId
LEFT OUTER JOIN tm_Reps rp WITH (NOLOCK) ON t.IntRepId = rp.IntRepId
LEFT OUTER JOIN tm_PayoutSch sch WITH (NOLOCK) ON t.IntSchId = sch.IntSchId
WHERE t.IntTransId = (CASE WHEN @intTransId IS NULL THEN t.intTransId ELSE
@intTransId END) AND
t.TransSrc = @strTransSrc AND
r.RemitCode = (CASE WHEN ISNULL(@strRemitCode,'')='' THEN r.RemitCode ELSE
@strRemitCode END) AND
ISNULL(t.SettlementDt,'01-01-1900') BETWEEN @dtmFromSettlementDt AND
@dtmToSettlementDt AND
ISNULL(t.TrdDt,'01-01-1900') BETWEEN @dtmFromTradeDt AND @dtmToTradeDt AND
t.CommTrailInd = (CASE WHEN @chrShowTrails='Y' THEN t.CommTrailInd ELSE 'C'
END) AND
t.Status = (CASE WHEN ISNULL(@strStatus,'')='' THEN t.Status ELSE
@strStatus END) AND
ISNULL(t.ClrHouseAcctNum,'') LIKE (CASE WHEN ISNULL(@strAccountId,'')=''
THEN ISNULL(t.ClrHouseAcctNum,'')
WHEN (@strTransSrc = 'PSH' OR @strTransSrc = 'MSR' OR @strTransSrc
= 'MSA') THEN @strAccountId
ELSE ISNULL(t.ClrHouseAcctNum,'') END) AND
ISNULL(t.FundAcctNum,'') LIKE (CASE WHEN ISNULL(@strAccountId,'')='' THEN
ISNULL(t.FundAcctNum,'')
WHEN @strTransSrc = 'NSM' THEN @strAccountId
WHEN @strTransSrc = 'MCE' AND ISNULL(t.ProdType,'')<>'VA' THEN
@strAccountId
ELSE ISNULL(t.FundAcctNum,'') END) AND
ISNULL(t.PolicyNum,'') LIKE (CASE WHEN ISNULL(@strAccountId,'')='' THEN
ISNULL(t.PolicyNum,'')
WHEN @strTransSrc = 'NSV' THEN @strAccountId
WHEN @strTransSrc = 'MCE' AND ISNULL(t.ProdType,'')='VA' THEN
@strAccountId
ELSE ISNULL(t.PolicyNum,'') END) AND
ISNULL(t.IntBDSponsorId,-1) = (CASE WHEN @intSponsorId IS NULL THEN
ISNULL(t.IntBDSponsorId,-1) ELSE @intSponsorId END) AND
ISNULL(t.ProdType,'') = (CASE WHEN ISNULL(@strProdType,'')='' THEN
ISNULL(t.ProdType,'') ELSE @strProdType END) AND
ISNULL(t.ProdSubType,'') = (CASE WHEN ISNULL(@strProdSubType,'') ='' THEN
ISNULL(t.ProdSubType,'') ELSE @strProdSubType END) AND
ISNULL(t.CUSIP,'') = (CASE WHEN ISNULL(@strCUSIP,'')='' THEN
ISNULL(t.CUSIP,'') ELSE @strCUSIP END) AND
ISNULL(rp.SSN, 0) = (CASE WHEN @numRepSSN IS NULL THEN ISNULL(rp.SSN, 0)
ELSE @numRepSSN END) AND
ISNULL(rp.RepCode,'') = (CASE WHEN ISNULL(@strRepCode,'')='' THEN
ISNULL(rp.RepCode,'') ELSE @strRepCode END) AND
ISNULL(rp.LstNm, '') = (CASE WHEN ISNULL(@strRepLstNm,'')='' THEN
ISNULL(rp.LstNm,'') ELSE @strRepLstNm END) AND
ISNULL(rp.FstNm, '') = (CASE WHEN ISNULL(@strRepFstNm,'')='' THEN
ISNULL(rp.FstNm,'') ELSE @strRepFstNm END) AND
ISNULL(rp.RepStatus,'') <> (CASE WHEN @chrIncludeTerminated='Y' THEN 'Z'
ELSE 'T' END) AND
ISNULL(t.IntClntId,-1) = (CASE WHEN @intClientId IS NULL THEN
ISNULL(t.IntClntId,-1) ELSE @intClientId END) AND
( (@chrAuditReportFlag = 'N' AND
t.Status NOT IN(@strLov_TxnStatus_Loaded, @strLov_TxnStatus_Cancelled) AND
ISNULL(TransAuditStatus,@strLov_TransAuditStatus_Active) =
@strLov_TransAuditStatus_Active
)
OR
(@chrAuditReportFlag = 'Y' AND
t.Status NOT IN(@strLov_TxnStatus_Loaded)
DefectID# 880,895
IN(@strLov_TransAuditStatus_Active, @strLov_TransAuditStatus_Cancelled)
)
)
With SASS Database i have created Data mining Structure Using Time series algorithm, while processing the SSAS db, Data mining  taking long time to process, so how we can  reduce processing time ???
View 2 Replies View RelatedHi,
I have just run a simple data set through a model to predict a simple true or false value (i.e. binary output)
The Lift Chart/Mining Legend in Analysis Services shows three results €“ Score, Population Correct (%), and Predict Probability (%)
Population Correct I beleive is the percentage of predictions it got right out of the total number of predictions it tried to make. Is this correct?
However, I can€™t work out how the other two are derived in particular the 'SCORE'. To give a live example the scores were as follows:
Model Score Pop Correct Pred Probability
Decision Trees 0.83 76.59% 54.28%
Neural Network 0.75 67.63% 50.05%
Ideal Model 100.00%
Can anyone help with this and give a detailed explanation?
Many thanks,
S Rajput
Hi,
I am trying to model data in analysis services with the Advance Create Mining Model function in the excel addin. I am having trouble creating an association model that works like the Associate button above the Advanced button.
The format of my data is like this
OrderID Product
100 Bike
100 Helmet
100 Shoes
200 Helmet
200 basketball
200 Bat
300 Shoes
300 Socks
The associate button works perfectly since it asks me which column is the transaction id (orderid) and which column I am trying to predict (product). The advanced create mining model asks me to determine what the columns are...
OrderID=key Product=Input+Predict?
When I run the advance create mining model associate, I get a browser that gives me no rules and the support for only one item itemset (each product but no combination of products).
Does anyone know what I have to do to get it to work like the associate button?
I perform data mining on all products and a specific product category.
Do I need to create 2 data source views, one for all products and the other one for the specific product category?
Afterward, I run the Data Mining Wizard 2 times to create 2 mining structures.
I also need to add the same mining model (e.g. Bayes, Cluster) to each of these mining structures.
Is there any simple way to do it?
Thanks.
Joe.
In order to setup my forecasting mining model I have created a special view that runs against my fact table and creates time series on the level I need.
Code Snippet
Select DFUKEY, DATE, QTY from Dim_FACT where DFKEY like '020%'
So I get the following input fr my model:
time series key (e.g. DFUKEY)
date (time key)
QTY (to be predicted)
For testing purposes I created a small view (similar to AdventureWorks) that only contained a few time series. The model was created and processed in ~2 minutes or less. The viewer came up almost immediately and I was able to see results.
Now my real view has about 25000 time series I need a forecast for and that I also like to review in the viewer. If I create a mining model against that bigger view the processing takes ~15m or so and the viewer is likely to time out.
The worst part thought is when I try to get the forecast for a time series (see query below) it takes minutes before the answers come back.
Code Snippet
SELECT FLATTENED [DFUKEY], PredictTimeSeries([QTY], 12)
FROM [TimeSeries]
WHERE [DFUKEY] = '02078147AG'
I would understand if without the WHERE clause it would take long but as is?
I am pretty sure I created the model the right way so I wonder if somebody can give me recommendations as to how forecast larger numbers.
Finally I intend to call the foarcast for a particular series using an Action from within a cube.
Thanks in advance.
Dirk
Hi, all here,
Thank you very much for your kind attention.
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
Thank you very much in advance for any help.
With best regards,
Yours sincerely,
Hi, everyone,
I have a SQL2000(SP1)/Windows2000, and a database - 4GB. The server physical memory is 1GB, and about 900MB is allocated to the SQL2000 through the memory dynamical configuration screen.
A scheduled database maintenance job was created using the standard maintenance planner tool. In this job, only Updata Data Optimization was included. Everytime, this job freezed the whole server - all screens, SQL client accesses, and, even the keyboard. A server machine reboot had to be made.
My understanding about Optimizatoin is that it does reindex for each index of each table in the database. So I picked the largest table in the database - about 1.5 million rows - to run DBCC DBREINDEX manually against each index one at a time, using the Query Analyzer. They were all running through, without causing server lockup. However, I noticed that each time, the DBREINDEX statement made the CPU and Physical I/O climbed extreamlly high. I suspected that, since the Optimization job put all tables together to DBREINDEX, it might exhaused the system resource that caused the server problem, but I am not 100% sure, neither do I know where to confirm this.
Confusing enough, other databases on the same server didn't have the problem. They all have maintance plan and run ok. I did all sorts of DBCC checks against this database which I had problem with, they were no error reported. I even loaded the database, using the backup, to another SQL 2000 server, and run Optimization there, and it had no problem.
I am not sure it is a server issue? or a database issue? or a SQL bug? Anybody had same experience?
Appreicate!
Regards! Yan Li
In SQL 2000, working with Maintenance plan wizard, what would be best settings and values should i choose in "Update Data Optimization information" window,
Thanks,
I am trying to delete tables from data where the ModifiedDates older than 9 years in AdventureWorks2012 database . I get console notified that foreign keys are dropped but the delete statement is throwing errors. I am sure that somewhere the key constraints are not getting altered, but i'm not able to figure it out as i'm a relative beginner to T-SQL. The error and code:
The DELETE statement conflicted with the REFERENCE constraint "FK_SalesOrderHeaderSalesReason_SalesReason_SalesReasonID". The conflict
occurred in database "AdventureWorks2012", table "Sales.SalesOrderHeader
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$option_drop = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions;
$option_drop.ScriptDrops = $true;
[Code] ....
Hi, all experts here,
I am wondering is there any way to select only a portion of a data set to train the mining model? In this case, I mean we dont need to split the dataset in advance, what I want to do is being able to select any random portion of a selected dataset to train a mining model. Any advices?
I am looking forward to hearing from you and thanks a lot in advance for your advices and help.
With best regards,
Yours sincerely,
hi
I am new at MSSQL 2000 DBA thing. and trying to learn more about analysis service/data warehouse/data mining. so is any expert out there can Recommend some good books or web link article to read? Thanks
Could I ask how to spit the data into training and validation sets when doing data mining?
Thanks
I'm stucked in a problem and I thought if you would be so kind as to helping me to resolve it.
I'm implementing a clustering algorithm plugin for text mining. I've already read the tutorials and sample codes provided by the MSDN Library.
Well... My problem is: I can't go through the data when the Predict method is called. I've read that this method implements the "core" of the custom algorithms. Here is a small snippet of my code for you to understand my doubt:
STDMETHODIMP ALGORITHM::Predict(/* [in] */ IDMContextServices* in_pContext,/* [in] */ DM_PREDICTION_FLAGS in_Flags,/* [in] */ IDMAttributeGroup* in_pPredAttGroup,/* [in] */ DM_CaseID in_CaseID, /* [in] */ ULONG in_ulCaseValues,/* [in] */ DM_ATTRIBUTE_VALUE* in_rgValues,/* [in] */ ULONG in_ulMaxPredictions,/* [in] */ ULONG in_ulMaxStates, /* [out] */ DM_ATTRIBUTE_STAT** io_prgPredictions,/* [out] */ ULONG* out_pulPredictions) {
for(UINT i=0;i<in_ulCaseValues;i++) {
DM_ATTRIBUTE_VALUE& dmattributevalue = in_rgValues;
ULONG iAttribute = dmattributevalue.Attribute;
if (iAttribute == DM_UNSPECIFIED)
continue;
double dblValue = ::DblContinuous(dmattributevalue.Value);
char buffer[129];
sprintf(buffer,"%f ",dblValue);
RENAN_Log::log(buffer);
}
return S_OK;
}
As you can see, I'm going through in_rgValues to get its values, but i'm only obtaining the first register of the table on the database. I need to roll over a kind of resultset so I could access all the registers I need. Is there any way to do so ?
I expected Predict() received a matrix containing all my data, but the only thing I noticed that could represent the data is that in_rgValues vector. So I can go through this vector, but it holds only the first register of the table in the database (that's what's being saved on my log). I need all of the registers in order to pre-process the data and implement my clustering algorithm.
Well... That's it... I would be very pleased if you could help me.
I have very little experience with programming and data mining, but I am working on a project where I need to take data from one spreadsheet and place it in another. Since it is hard to describe what I would like to do, I will provide an example:
SPREADSHEET 1
Column 1, Column 2
100, ?
101, ?
102, ?
103, ?
SPREADSHEET 2
Column 1, Column 2
102, 202
100, 200
103, 203
101, 201
In this example, the data in Column 1 is always tied to the data in Column 2 (i.e., 100 in Column 1 means 200 in Column 2, etc.) However, the data for Column 2 is only available in SPREADSHEET 2; moreover, the data is not in the same order in both spreadsheets.
My question is how can I create some sort of program where I can transfer the data from SPREADSHEET 2 into SPREADSHEET 1?
Hi my friends,
I do have a problem with results of clustering algorithm with my categorical data.
In reality I have a big table with one's and zero's and I try to cluster according to 60 attributes.
I tries to cluster to two categories many times but I get only one cluster. What do you suggest. Which values can I change to the algorithm? Is there anything particular for categorical data ?
Thank for your help in advance.
Manolis
Thank you very much for your previous answers conserning clustering algorithm.
I change a bit my data and now I can get two and three clusters as I wish.
But now I have a different question. I wish to keep my Structure and Model for further trials with different algorithm values, like cardinality. I tried the following
I selected in excel Data Mining and then the option cluster button.
Then in the cluster wizard I chose analysis service data source and I reform the query as I need adding only at the end
where "STAFF_YES" = 1. Then I choose the needed columns and target value. Finally I select browse model, enable drill through only and I finish the wizard and get some results, rather good. But i need to try again with different parameters.
I tried Business intelligent Studio to check my Analysis service. The structure were here but when I changed any parameter and I tried to reprocess I got the following messages
Processing Mining Structure 'try Structure_1' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Processing Dimension 'try Structure_1 ~MC-__RowIndex' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Processing Dimension Attribute '(All)' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Processing Dimension Attribute 'EPI_CELL_SINO_GOOD' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Processing Dimension Attribute 'EPI_CELL_SINO_LOOSE' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Errors and Warnings from Response
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: An unexpected error occurred (file 'pcprocbinding.cpp', line 6645, function 'PCDBProcBinding:electOrChangeCartridge').
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'try Structure_1 ~MC-__RowIndex', Name of 'try Structure_1 ~MC-__RowIndex' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'EPI_CELL_SINO_LOOSE' attribute of the 'try Structure_1 ~MC-__RowIndex' dimension from the 'DMAddinsDB2' database was being processed.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: An unexpected error occurred (file 'pcprocbinding.cpp', line 6645, function 'PCDBProcBinding:electOrChangeCartridge').
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'try Structure_1 ~MC-__RowIndex', Name of 'try Structure_1 ~MC-__RowIndex' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'EPI_CELL_SINO_GOOD' attribute of the 'try Structure_1 ~MC-__RowIndex' dimension from the 'DMAddinsDB2' database was being processed.
And no change to the structure take place.
Please help me if you can
Thank you in advnce
Best regards.
Manolis