I am in the process of creating an Integration Services package to automate the process of training mining models and getting predictions. Until recently, I have been processing the models directly from Business Intelligence Studio without any problems. However, when I try to use the exact same training set as an input to the Data Mining Model Training destination, I get several errors. Here is the output:
[Mining Models [1]] Error: Parser: An error occurred during pipeline processing.
[Mining Models [1]] Error: Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
[Mining Models [1]] Error: Errors in the OLAP storage engine: An error occurred while the 'CPT MODIFIER' attribute of the 'BCCA DMS ~MC-CLAIM LIN~5' dimension from the 'BCCA LRG DMS TEST' database was being processed.
[Mining Models [1]] Error: File system error: The record ID is incorrect. Physical file: . Logical file: .
[Mining Models [1]] Error: Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
[Mining Models [1]] Error: Errors in the OLAP storage engine: An error occurred while the 'BILL TYPE' attribute of the 'BCCA DMS ~MC-CLAIM LIN~5' dimension from the 'BCCA LRG DMS TEST' database was being processed.
[Mining Models [1]] Error: File system error: The record ID is incorrect. Physical file: . Logical file: .
[DTS.Pipeline] Error: The ProcessInput method on component "Mining Models" (1) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
I have not been able to find an answer as to why this is happening. I found a post regarding a similar problem with processing an OLAP cube in SSIS, but it seems that the author of that post never found an answer. Has anyone else here seen similar errors when processing mining models from Integration Services?
Also, if I process the mining models manually then try to run only predictions in SSIS, I get many of the same errors. I'll keep looking into the problem myself, but I would be very grateful if someone in this forum could shed some light on this issue.
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.
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.
Still new to DM and SSIS...anyand all help is greatly appreciated!
In SSIS they say that you can use the Analysis Services Processing Task to process a mining model/mining structure, however, I do not see where you can give it a relational table to work off of. I know that I can use a data flow to do this but I wanted to go a different route if I could to process my models as I don't really necessarily need the data flow as what I am tring to do is pretty simple.
That brings me to a more general question, what is the best method for training your models using SSIS? I am building a new model everytime the package runs using some variables and the DDL task, running a query on it, and destroying it at the end of the package but I am having logistical problems training it outside of the data flow. I tried using the DM Query task but it requires that you output a result set and I am not sure if I can use it to create and process models.
I would think that they would just give you a DMX task similar to the SQL task but that does not seem to be the case. Also, when I browse the AS objects via the processing task I can only see the mining structures and not the mining models.
I can't figure out how to put nested tables into the Data Mining Model Training Transform (SSIS). I can do a simple case table, but how do you get those nested tables with DM Training Transformation? Any ideas? Samples?
Hi, 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?
I just found that I am not able to view the accuracy chart for my mining model. The error message is: no mining models are selected for comparision. Which is quite strange.
Just really wonder what is the good idea to deal with missing values? Should we leave the missing values there in the traning data set ? Or replace it with other values?
What I am really concerned is that if we simply replace those missing values with other values, then how will it really affect the correctness of the training models?
I am looking forward to hearing from you for the above issue and it will be really great if we have any kind of best practices of dealing with this issue.
I've created models with Decision Tree and Neural Network algorithms that predict continous target. But I don't know how to interpret scores that occure under scatter accuracy plot. How should I interpret scores under scatter accuracy plot? How can I estimate occuracy of model created with Time Series? How can I compare accuracy of model created with Time Series with models created with Decision Trees and Neural Network algorithms?
Just want to make things perfectly work and make the most of our fantastic SQL Server 2005 Data Mining Engine. Can any of you here give me some super advices on the validation of the mining models. As we always see, the 3 aspects of a mining model are: Score, Population correct, and Predict Probability. So the question is: How can we combine these three aspects to best judge the mining models by being able to tell which model is the best one? And to what extent can we really trust these mining models?
These are very important before we can actually bring the models into work to convince other people who have no ideas what are going on with these models. Yes, we just want to convince them with the results of these models and make the most of them and best help them getting the most from their business operations etc.
By the way please can you explain a bit details on each of these aspects? Thanks again.
I am looking forward to hearing from you shortly and thanks bunch for your help.
I am having a question about automating data mining models managements. As we know in many businesses, patterns vary very frequently, therefore, the mining models created will need to be created again afterwards according to new rules appearing in the data. But can we make all these process automated like automatically assessing the mining model accuracy and automatically recreate the mining models based on predifined specifications? Would please any one here give me any idea about that?
Again I am confused about the extent of being convinced by the mining models. We can validate the models with accuracy chart, but then to what extent can we trust that? (you never get 100% correct miming models) If we dont trust the results of the models, then the patterns the models discover are meaningless.
Just need some advices from you experts here to help me convince people on what I got from my mining models.
I am looking forward to hearing from you shortly and thank you very much.
I got a strange problem with SQL Server 2005 data mining models though. I have selected the input columns for my mining model (which are different from the input columns for its mining structure, since I ignored some of the columns for the selected model). But the mining model still used all input columns from the mining structure rather than those I chose for the mining model.
Would please any one here give me any guidance and advices for that. Really need help for that.
Hi! If you make an view you can script it in SSMS and get the DDL. How to do something similar with mining models/structures getting DMX? I see you easy can get XMLA, but I would prefer the "dmx-ddl"...
I have been trying to use SQL 2005 data mining for about 8 weeks. I am becoming frustrated because I am not able to make progress nor am I able to exploit the power of the system.
I need a training course! I have asked Microsoft in UK for recommendations but they have been unable to help. I have searched for courses in the UK and US without sucess.
I am coming to the Microsoft BI event in Seattle - will there be any opportunities there to get help or find help? (In Seattle I intend to concentrate on the Excel add ins)
I'll try to explain what I'd like to do. On my SQL Server 2005, SSAS contains a mining model (In fact a cluster one). I'd like to show a detailed diagram build from this model on a web site. This diagram (and this is why I need automation) would depend on the user who's consulting it. For example, a firm A will see the number of its customers in each cluster, and this information will be different for another firm B
So, I thought about several steps to perform: 1) Feed the model with the data for each firm 2) Build a Visio diagram from the previous data using the DM addins for Visio 2007 3) Generate HTML using the Visio export wizard 4) Publish HTML And (important) this should be done automatically.
I made some experiments: Step 1 is easy to perform with SSIS Step 3 is also easy to do with the Visio SDK (using, among others, the exportAsHtml control) and Step 4 is trivial
I failed to perform stage 2, even with the SDK, since creating a diagram from a DM template requires the user to fill a wizard. By code, I'm able to create a document from a DM template, Drop a DM Stencil but when the wizard appears I'm unable to get a handle on it. And even if I was, I dunno if it would be a "clean" way of doing.
So my question is: first of all: isn't there an easier way to generate HTML from Mining Models automatically? And if my approach to this problem is the best (or the "least bad"), how to generate datamining Visio diagrams from code automatically?
Dear All, I have a simple mining structure created by the DMX statement below. Then I tried to insert some data with MDX language by extracting data in OLAP. But I got the following error when I execute the insert statement.
Errors in the high-level relational engine. The 'Customer ID' column in the RELATES clause was not found in the results of the OPENROWSET query.
It seems that the append statement can't really recognize the name of the column which should be Customer ID.
How can I fix this problem?
Thanks
Tony Chun Tung Siu
The source code for create and insert is as below.
create mining model customerMiner ( customerID long key , age long continuous, orders table ( orderID long key, goodsID long discrete predict_only ) )using [Microsoft_decision_trees] with drillthrough;
insert into mining model customerMiner ( customerID, age, orders ( skip, orderID, goodsID ) ) shape { openQuery([Simple SSAS], ' select {[Measures].[Customer ID], [Measures].[Age]} on columns, {[Customer].[Customer].[Customer].members} on rows from fi ') } append ( { openQuery([Simple SSAS], ' select {[Measures].[Customer ID],[Measures].[Order ID2],[Measures].[Goods ID]} on columns, [Goods].[Order ID2].[Order ID2].members on rows from fi ') } relate [Customer ID] to [Customer ID] )as orders
Hi ...I can't figure out how to put nested tables into the Data Mining Model Training Transform (SSIS). Can anybody help me? some example please...!!!?? Diego B.
Another tricky confusion to me is that: many algorithms settings for the native algorithms in SQL Server 2005 Data Mining do not really significantly improve the results of those mining models with settings changes? (Apart from clustering algorithm setting of cluster number, by setting 0 as the number of clusters, the system will automatically cluster the data into clusters which I assume is the best way of mining the model with this method).
Any good advcies on this will be a lot appreciated.
I am looking forward to hearing from you shortly for this confusion and thanks a lot in advance.
I am using BI Dev Studio for SS2005 in a research (as opposed to a production) environment. Often I want to compare the results of multiple models using the same attributes. If I switch to a different model, the Design view completely resets. Is there any way to retain the same field names with different models in the Design view?
My current workaround is to give my models similar names with AR, DT, CL, LOG, NN suffixes and make global changes in the DMX.
I have consulted the following without finding an answer: http://msdn2.microsoft.com/en-us/library/ms178445.aspx http://msdn2.microsoft.com/en-us/library/ms175642.aspx http://msdn2.microsoft.com/en-us/library/ms175678.aspx http://msdn2.microsoft.com/en-us/library/ms175637.aspx
Actor train nested table: ID MovieID Gender 1 1 F 2 1 M 3 1 F 4 1 F 5 2 M 6 2 M 7 2 F 8 3 F 9 3 F 10 4 M 11 4 M 12 4 F 13 4 F 14 5 F 15 5 M
We want to build a classifier model in order to predict the Class of a Movie based on the Gender of movie's actors. To deal with the nested table Analysis Services maps each record of the nested table to an attribute of the case table. These attributes are named Actor(n).Gender with n = 1..15, and so they are dependent on the nested table record numbers. Both Microsoft Decision Trees and Microsoft Naive Bayes algorihms use these attributes without any modification.
We are implementing a Relational Naive Bayes algorithm and we are planning to aggregate such attributes in order to make them independent of the nested table record numbers.
Next step we tried to predict some unseen cases and here we face with a very huge problem.
Lets take more two tables of unseen cases:
Movie test table: ID Class 6 + 7 NULL 8 NULL
Actor test nested table: ID MovieID Gender 1 6 F 2 6 M 3 6 F 4 6 F 16 7 F 17 7 M 18 7 F 19 7 F 20 7 F 21 8 M 22 8 M 23 8 F
Predicting the movie 6 Class is not a problem since the movie actors were included in the training dataset and when the records are mapped to attributes because they already exist in the model. But when you try to predict movies (7 an 8) with unseen actors all new attributes are simply ignored in the ALGORITHM:redict call (in_ulCaseValues is zero!) because they do not exist in the model!
I have a very simple time series model which processing works fine without any problem. However when I run the following query
SELECT
[TimeSeries].[PriceChange],
[TimeSeries].[Symbol],
PredictTimeSeries(PriceChange, -3, 2)
From
[TimeSeries]
WHERE
[TimeSeries].[Symbol] = 'x'
I get the following error:
TITLE: Microsoft SQL Server 2005 Analysis Services ------------------------------ Error (Data mining): A time series prediction was requested with a start time further in the past than the internal models of the mining model, TimeSeries, specified in the HISTORIC_MODEL_GAP and HISTORIC_MODEL_COUNT parameters can process.
The following is the excerpt of the minding model script related to the two parameters:
<AlgorithmParameters>
<AlgorithmParameter>
<Name>MISSING_VALUE_SUBSTITUTION</Name>
<Value xsi:type="xsdtring">Previous</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_GAP</Name>
<Value xsi:type="xsd:int">1</Value>
</AlgorithmParameter>
<AlgorithmParameter>
<Name>HISTORIC_MODEL_COUNT</Name>
<Value xsi:type="xsd:int">10</Value>
</AlgorithmParameter>
</AlgorithmParameters>
These HISTORIC_MODEL_GAP (1) and HISTORIC_MODEL_COUNT (10) should accommodate PredictTimeSeries(PriceChange, -3, 2). Could anyone shed some light on this?
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.
Greetings all, I have a need to quickly spin up on SSIS. Can anyone suggest a class? I did a search on this forum but most of the the related threads were not all that recent.
I've searched all over, but I am having difficult time trying to find instructor led training in a classroom setting for SSIS. I have found several on-line tutotial and e-learning courses, but none in a classroom.
I am not sure if this is a correct forum to discuss on the document posted @ http://www.microsoft.com/downloads/details.aspx?familyid=1c2a7dd2-3ec3-4641-9407-a5a337bea7d3&displaylang=en on SQL Server Integration Services (SSIS) Hands on Training - Creating Custom Components.
I am assuming Microsoft Developers are constantly monitoring this forum.
In the document - SSIS Creating a Custom Transformation Component .doc on Page 2 - Exercise 1 - Writing the no-op data flow transformation component - Task 1 - Create a new C# Class Library Project
The textual description talks about creating a new Visual C# Class Library project in VS 2005 but the screenshot accompanying it shows the creation of new "Integration Service Project" in VS 2005.
Please change the screenshot appropriately to avoid confusions.
