I am a newbie to datamining, but have nearly a decade of solid database experience with the last 6 years in SQL Server 2000. We are moving our accounting system to SQL Server 2005 and I have been asked to explore the possibilities of mining an inventory table. I'd like to get some opinions prior to spending too much time potentially barking up the wrong tree!
We have an inventory table with approximately 10 million serialized records. Each row contains the serial number of the individual unit and its manufacturer/model designation. We have no control over the assigning of the serial numbers as they come from multiple manufacturers and some of the manufacturers correlate serial numbers to model and some don't.
My thought was to use a cluster model to try to predict the model of a new serial number as it is entered into the database. Is this thought feasibile? Is the mining model choice appropriate? If pointed in the right direction, I'm sure that I can run with this.
Hi, I am not getting Mining Accuracy Chart and Min ing Model Prediction Plz tel me how to do.And how to use the filter input data used to generate the lift chart and select predictable mining model columns to show in the lift chart
I get the following error when I try to load the mining model in the mining model viewer
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[NeuralNet].[GetAttributeValues]' function does not exist.
I get a similar error when I try to load the Load Mining Accuracy Chart
Failed to execute the query due to the following error:
Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[AllOther].[GenerateLiftTableUsingDatasource]' function does not exist.
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 would like to develop an application that can create Data Mining structures and a mining model in SQL Server 2005 with VB.NET. I tried the code from book Data Mining with SQL server 2005 in chapter 14 but did not work. Any good idea?
Thank you very much for your help. The errors that I can see in the code that you gave in your answer are the following and they are more or less the same as I had previously
I tried the code but initially I have encounter the following problems.
1. In any line that have the declaration As Server, As Database like in Public Function CreateDatabase(ByVal srv As Server, ByVal databaseName As String) As Database gives me the problem that type Database is not declared the same type Server is not declared and it does not give me any option.
2. In addition to that for As DataSource, As RelationalDataSource, As RelationalDataSourceView, As ScalarMiningStructureColumn, As DataSourceViewBinding, gives me the problem that type is not declared.
3. Finally in mc = New MiningModelColumn("Yearly income", Utils.GetSyntacticallyValidID("Yearly income", Type.GetType(MiningModelColumn))) is not accesible in this context because it is 'Private'. I have some more problems but I thing that by solving the above that I referred I will solve the rest.
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?
I have a database table which has all the inputs, key and the result. In visual studio, I created a decision tree model which has exactly the same fields as in the table. However the visual studio automatically add space preceding the capital letters. As the field name in the Datamining model and those in the database table are slightly different. I cannot use NATURAL prediction join. Is there anyway to told the visual studio not to add the spaces in the variable names?
I have MS Time Seeries model using a database of over a thousand products each of which has hundreds of cases. It amazingly takes only a few minutes to finish processing the model, but when I click Mining Model Viewer to view the models, it takes many hours to show up. Once the window is open, I can choose model for different products almost instantly. Is this normal?
I've tried those two operations in the Management Studio. Though we can create a mining structure and mining model in Management Studion, but we cannot process the analysis-service database.
(1) I create only a mining structure through CREATE MINING STRUCTURE. No error reported. But if I process the analysis-service database in Management Studio I always get error
'Error : The '<mining_structure__name>' structure does not contain bindings to data (or contain bindings that are not valid) and cannot be processed.
I then tried to create it by creating and running an XMLA script. It was successful. However, it's much harder to learn XMLA.
If any of you created an analysis-service database in Mgt Studio, and create a mining structure in the same place using DMX script, can you process the database?
(2) Is there any use of CREATE MINING STRUCTURE operation without binding to any table? Examples I saw so far did not show relating it to do. In my experience processing the analysis-service database with that mining structure is doomed to fail.
(3) Is there any way we can create mining structure through CREATE MINING
STRUCTURE operation in Management Studio and use RELATED TO clause
to bind it to any Relationship to an attribute column (mandatory only if it applies), indicated by the RELATED TO clause
(4) If this is the fact, is there any use of CREATE MINING STRUCTURE operation? If we use BI Dev Studio, it's much easier to use the wizard.
(5) I found I cannot create a mining model inside a mining structure through operation CREATE MINING MODEL. If you call that operation, you end up having a mining model and a mining structure with the same name. I found that in order to create a mining model inside a mining structure you have to call operation ALTER MINING STRUCTURE ADD MINING MODEL. Is it true this is the only way?
I'm working on a project to create a mining model.I have one "flattened" table with a productID (key) and related attributes. Example columns are weight, color, price, units sold last year, product category, product rating (top seller, etc) and similar columns, about 40 in total. There are about 15,000 products and same number of rows in the table. The objective is to pass a ProductID (that also exists in the table) and get back the top N products that most resemble the source product based on all the attributes.
Any advice on which models I should test and how to set up the models would be much appreciated. Also if there is a similar example/sample out there, please let me know. I downloaded the MovieClick sample, but that doesn't work in my case as I only have the equivalent of the Customers table.
I newly Installed my SQL 2005.When I try to train my Model its giving me "Key not valid for use in specified state." Can anyone help me how to figure it out?
i have a problem, because i have 3 tables. One name "CallPair" contain: id- identity number, from - phone number, to - phone number. Second name "Talk" contain: id - identity number, callPairId - id from "CallPair" table, date Third name "Transaction" contain: id - identity number is a number of transaction, idTalk - id from Talk table, callNr - number of talk in transaction
I want mining model to sequence clustering, but i don't know how build case and nested table
I have one main mining structure and several mining models that are using the MS Decision Trees algorithm. I have a Reporting Services report that displays the model using a table. I want to set it up so that in the DMX query, I can use a parameter to specify which model to use. That way I just have one report that can reference multiple models. For example I would like to do something like this:
SELECT FLATTENED * FROM [@model].CONTENT
This doesn't work because I get the error message:
Preparation of DMX query failed. Error (Data mining): Either the user, user, does not have permission to access the referenced mining model, @model, or the object does not exist. (Microsoft SQL Server 2005 Analysis Services)
We are running SSAS 2005 SP2. Is there a way to accomplish this?
i try to process datamining with DMX first i create new mining: CREATE MINING model ForexData ( timeseri DATE KEY, Xhnow TEXT DISCRETE, Muccl TEXT DISCRETE, Change TEXT DISCRETE, XH5be TEXT DISCRETE, XH3be TEXT DISCRETE, XH1be TEXT DISCRETE , XH1ne TEXT DISCRETE PREDICT_only, XH3ne TEXT DISCRETE PREDICT_only, XH5ne TEXT DISCRETE PREDICT_only)Using Microsoft_Association_Rules (Minimum_Probability = 0.4, Minimum_Support = 0.01) after that i use insert into statement:
INSERT INTO mining model ForexData(Change,Muccl,Timeseri ,XH1be,XH1ne,XH3be,XH3ne,XH5be,XH5ne)OPENQUERY([Forex DB],'Select Change,Muccl,Timeseri ,XH1be,XH1ne,XH3be,XH3ne,XH5be,XH5ne FROM dbo.dataprice')
there is the fisrt time i deploy ForexData so, it return an error:
Errors in the OLAP storage engine: An error occurred while the 'timeseri' attribute of the 'ForexData_Structure ~MC-timeseri' dimension from the 'Analysis Services Project1' database was being processed.
i dont know about it, i have read some docs and comments on web but can solve this problem, can you help me? as soon as posible, please
i have create a data source and data source view by BI and it return database is 'Analysis Services Project1'. and data source name: 'Forex DB' ;data source VIEW name: 'Forex DB' ;
nick yahoo: remember_somebody; contact to me if you can, i really need these infomation
Here is my problem, I developed a forecasting mining model(forecasting.dmm) in SQL Server using Analysis Services Tool. I want to Access the mining model viewer chart(prediction chart) by using localhost in InternetExplorer.
I want to know How to access the charts using Report Server of Sql Server 2005(not the data part, the prediction chart)
I've been playing around with the association mining model in SQL server 2005 and built a market-basket analysis of my data that I'm pretty happy with. The next task for me is figuring out how to run DMX queries against the data that I've just mined, so we may possibly use it in a web based application. This wouldn't necessarily be a difficult problem (and still may not be), but every example I've seen for the Mining Model Prediction Designer uses relational databases and I built my mining model off OLAP. Therefore, my predictable attribute is nested and when relating the mining model structure to the relational database that the cube was built off always gives me an error:
"Errors in the high-level relational engine. The 'CompanyName' column could not be found in the top-level clause of the SHAPE statement."
What I would like to do, and I'm not really even sure how I should structure any of my queries, is feed the model a product and have it return a listing of all the products it predicts. Currently, I've only been able to get the designer mode to process a singleton query, and even that didn't return any useful data. I know that this probably can be done pretty easily so any advice you may be able to offer would be greatly appreciated!!
So you may better understand my question, my association mining structure hierarchy looks as this..
[Model] ProductRecommend
[Case][Key]CustomerList
[Case][Attribute]CompanyName
[NestedTable]Product
[Nested][Key]PRODUCTCLASSID
[Nested][Attribute]PRODUCT
With that in mind, I'm trying to perform a query simliar to this:
SELECT
PredictProbability([ProductRecommend].[Product].[PRODUCTCLASSID]), <---- Throws Error for PredictProbability syntax no matter what I try to get to [PRODUCTCLASSID]
(SELECT [PRODUCT] FROM [ProductRecommend].[Product])
and I received the following error message. Any Ideas? What is wrong with this? Thank you in advance
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Parallel> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <Object> <DatabaseID>test27</DatabaseID> <MiningStructureID>test27 MiningStructureName</MiningStructureID> <MiningModelID>test27 miningmodelName</MiningModelID> </Object> <Type>ProcessFull</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel> </Batch> Processing Mining Structure 'test27 MiningStructureName' completed successfully. Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00 Processing Dimension 'test27 MiningStructureName ~MC-SUMCODE' failed. Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00 Processing Dimension Attribute '(All)' completed successfully. Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00 Processing Dimension Attribute 'OZOS NUM MANY' failed. Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00 SQL queries 1 SELECT DISTINCT [VISIT].[OZOS_NUM_MANY] AS [VISITOZOS_NUM_MANY0_0] FROM [VISIT] AS [VISIT] Error Messages 1 OLE DB error: OLE DB or ODBC error: Operation canceled; HY008. Processing Dimension Attribute 'STAFF_YES' failed. 1 rows have been read. Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00 SQL queries 1 SELECT [VISIT].[STAFF_YES] AS [VISITSTAFF_YES0_0] FROM [VISIT] AS [VISIT] Error Messages 1 OLE DB error: OLE DB or ODBC error: Requested conversion is not supported.. Errors in the OLAP storage engine: An error occurred while the 'STAFF_YES' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed. Processing Dimension Attribute 'D_G_OZODIS_IPER' completed successfully. Start time: 26/3/2007 11:36:18 μμ; End time: 26/3/2007 11:36:18 μμ; Duration: 0:00:00 Errors and Warnings from Response OLE DB error: OLE DB or ODBC error: Requested conversion is not supported.. Errors in the OLAP storage engine: An error occurred while the 'STAFF_YES' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed. 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. OLE DB error: OLE DB or ODBC error: Operation canceled; HY008. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'test27 MiningStructureName ~MC-SUMCODE', Name of 'test27 MiningStructureName ~MC-SUMCODE' was being processed. Errors in the OLAP storage engine: An error occurred while the 'OZOS NUM MANY' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed. Errors in the high-level relational engine. The database operation was cancelled because of an earlier failure. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'test27 MiningStructureName ~MC-SUMCODE', Name of 'test27 MiningStructureName ~MC-SUMCODE' was being processed. Errors in the OLAP storage engine: An error occurred while the 'D_G_OZODIS_IPER' attribute of the 'test27 MiningStructureName ~MC-SUMCODE' dimension from the 'test27' database was being processed. Manolis
Hello, I've just created a Sales Forecasting Mining Model. How do i export the predicted time series values into a table?? Is there any tutorial explaining this.
I am wondering why the loading of mining model for MTS failed in my case, the model has successfully processed and deployed? But when I clicked on the model viewer, the error message saying: failed to load model. Why is that? Is it because too many records for the table? Or why is that? (in my case, I got a case table and a nested table with over 1000,000 records)) Thank you very much and I am looking forward to hearing from you.
Is there a way to display the actual predicted value for an output attribute for a particular model. For example, say I am trying to predict if a particular customer is going to take advantage of a promotion (0=no, 1=yes) and I use neural networks. I know that I can use "Predict" to give me the prediction "yes" or "no" for each customer. However, the neural network actually spits out a number as a result. For example, a 0.997 would be interpreted as a "yes" for life insurance promotion. I do not want the probability that the prediction is correct. I want the actual output for the network.
The reason being is that I want to compute an error rate between the predicted value and the acutal value (root mean squared error or some other measure). Is there a way to compute this using the mining model prediction tab design view? I do not want to write the actual query as I teach a course in data mining using SQL Server and my students do not know DMX queries.
Why I got different results for the same attribute value displayed in my mining model? Any suggestions on what I may have missed for that?
In my case, the mining structure is with case table which is the fact table, within this mining structure, I dragged other attributes from its related dimensional tables as well. E.g The schema of the mining structure is as below:
Then in my training model (using Microsoft Clustering algorithm), the content contained within the training model is very strange, e.g there are different results for the same value of attribute 'Agent Level' . Why did that happen and how can I figure it out? There should only one result for each value of each attribute within one mining model?
Please shed me some light on this issue and I am looking forward to hearing from you shortly for your kind advices and thanks a lot in advance.
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.
I have a problem getting information about accuracy (percentage of the right predictions) of the model using DMX. Is it possible to get information about accuracy of the model using DMX? I didn't find any useful function... My second idea was to build and process the model. And then compare states of the predictable columns of the test data to states that the model predicts on the same data. And count them. That would be the way to get percantage of the right predictions... The problem is that usage of the function COUNT is not allowed??? I tried:SELECT COUNT(*) FROM [My Model Name].Cases and it didn't work like in standard SQL... Is it possible to count rows in DMX? Any idea how to get accuracy (percentage) of the model? I would need this information in my application... Thanx for any idea, Ziga
I am trying to write some DMX queries to create and populate a mining model as a SSMS analysis services project. I followed the following steps:
1. Create the mining model using a CREATE MINING MODEL ... query.
2. Followed by the - INSERT INTO MINING MODEL ... query, which fetches prediction data from another mining model to populate the mining model.
3. I now want to use this new model for prediction, which requires processing the mining model first. When I process the model, it throws the following error:
Error (Data mining): The 'XYZ_Structure' structure does not contain bindings to data (or contains bindings that are not valid) and cannot be processed.
Please suggest if I am making a mistake in the above procedure. I will appreciate all help in overcoming this issue.
Auxilliary question: How do I process the mining model programmatically?