On pages 123, 124 of Data Mining with SQL Server 2005, I can't finish the example. When I add 'Monthly' to the end of the expression "[Home Owners].[Theater Freq]" in the Criteria/Argument column, I usually get an error message saying the syntax is incorrect when I switch to the Results view. If I place a dot after the original expression, like this: [Home Owners].[Theater Freq].Monthly, I get an error message that "The dot expression is not allowed in the context at line 4, column 23. Use sub-SELECT instead." What am I doing wrong?
I wrote a data mining plug-in algorithm in SSAS and now I am trying to view the lift chart in Management Studio. The studio gives the following error:
"The Data Mining Query builder control cannot be initialized due to the following problem: Deserialization failed: Multiple definition of element 'Column12' causes the content model to become ambigious. A content model must be formed such that during validation of an element information item sequence, the particle contained directly, indirectly or implicitly therein with which to attempt to validate each item in the sequence in turn can be uniquely determined without examining the content or attributes of that item, and without any information about the items in the remainder of the sequence."
I really do not understand what i did wrong. Can anybody help me?
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.
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 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 know if there are any good MDX query builder tools out there. The one that is shipped with AS does not suffice in that it does not support more than 2 axes. Also if there are any are there any that make life a little easier in that they are drag and drop and all but create the statement for you?
I need to create a matrix in SSRS using cubes and in doing so need to write MDX queries, not my strong point. Are there any tools that can help me "auto create" the statement with drag and drop functionality?
Hi people!I'm looking for an SQL query builder application that helps me constructSELECT queries with full respect of underlaying relational model. It wouldbe nice if the app is database independant because my work covers MSSQL,mySQL and Oracle DB.OpenSource and/or comercial suggestions are welcome!*** thanx ***
HI, in a lookup component, when I want to use the query builder, it will show tables from a different connection manager than the one I picked for being the lookup source. E.g. connection manager A points to a schema that contains table1 and table2 and connection manager B points to a schema that contains table 5 and table 6.
When I configure the lookup component to use connection manager B, I still see connection manager A tablles; isntead of seeing table 5 and 6 I see table 1 and 2.
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 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.
hi,i can do"select * from products where name = @name" kind of statements in query builder but"select * from products where name LIKE @name" dosen't work!" any ideas? i'm using sql server express. thanks
Hi All, For one of our projects, we are supposed to write a query builder to display reports in tabular format. For the filter criteria ( i.e where clause ) user can select from a list of predefined columns and assign conditions to it. For example, user can select 'CustomerName' as the column and specify condition where name begines with 'A'. Similarly he can also select ouput columns Problem The columns that user selects can come from different tables. We are planning to store mapping between tables and columns and also their primary and foreign keys in an xml file. But I would want to understand how to build the join clause. For example, say there are 4 tables customer, customer address, order, and items. Say user wants the number of items purchased by each customer who stays in 'Delhi'. 1. In output column he selects customer.name and count(items.id) 2. In filter criteria he selects address.city = 'delhi' How would the query builder know that to join customer and items, order also has to be joined. Any pointers would be helpful.
I hope you mind a questions from an inexperienced SQL 2000 user.
I don't have much experience with building queries for SQL2000. So I am lookinf for a software tool that will help me create simple queries. I'm also looking for an online (either on the web or a downloadable program) query reference to help remine me how to make these queries.
I am trying to emulate the "Find unmatched Records" Access Query using DTS. I have a small table with a list of codes that I need to use to exclude records in my text export.
I have created a left outer join and set the criteria for the joined field in the 'right' table to be null. I am repeatedly getting no records as a result.
Wouldn't the joined field in the right table for unmatched records by default be null? I'm stumped as to why this seamingly simple process won't work for me. I have attached the select statement for your review.
Either edit the query from Dataset edit dialog or in the query builder (switch the design mode to show the query string), I can't save the query and always get the following error:
TITLE: Microsoft Visual Studio ------------------------------
The query cannot be retrieved from the query builder. Check the query for syntax errors. Reporting Services will continue to use the most recent valid query.
NOTE: I am using Brian Larsons book Delivering Business Intelligence with SQL Server 2005. I am stuck on page 588 - step 20. I am using Windows Vista Ultimate
Once all the Dimensions & attributes I have selected are dragged into the query area in MDX query builder no data is displayed. There is a message: "No rows found. Click to execute the query." - clicking does nothing.
I created a Market Basket Data Mining Model with Association Rules, which I want to query and show in a Report. Everything works fine, when I preview the result in the Reporting Services Data tab I see some sort of table which I can expand and then see the related products.
Unfortunately this result seems to be an ADOMD Datareader which I cannot place in a table, matrix or textfield.
If anyone knows how I can make this Informationen available in my Report please let me know.
We have SQL Server 6.5 with an Access 8.0 front-end. We are about to re-write our front-end in VB6 using RDO. Obviously Access has a nice fast and easy to use query builder. My question therefore is - is there any such query builder that can be used within VB6 to simplify query writing ? Or do I have to write all query's and and stored procedures using straight SQL and/or stored procedures. I do not want to use Visdata as it doesn't appear up to the task.
Hi, I am trying to generate a report and I am using the query builder feature in SQL Server Reporting Services.
I just entered a simple query (actually the query builder generated it for me):
SELECT ID, pname FROM jira393.project
and it seems to run fine but when I click "OK" to go to the next step, I keep getting an invalid object name error: 'jira393.project'
I don't know what is wrong right now since when I run it, it seems to display the correct information but trying to go to the next step displays that invalid object error.
I have created a model from the underlying database. Now I want the capability to write SQL Queries when creating reports instead of drag and drop the reports. Is there anyway we can achieve it in MS Report Builder. I would like to know the options available in MS world on this.
Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.
My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.
In my reports I want Date attribute to be added only in the data set and not in the query builder, As if I am adding it in the query builder report is taking very long time to generate and I am getting around 7000 pages of data which is absurd.So, if I can add it only in the data set then I can use filters for my reports and get only the required data. So, is there any way where in which I can add the Date attribute only in the dataset and not in the query builder????
I have a question about using the query builder, or just straight sql, to create expressions. My first example caused the query builder to generate a query configuration error:
Expression: EIM_REQUISITION_DETAILS.EST_UNIT_COST * EIM_REQUISITION_DETAILS.REQRD_QTY AS COST
Syntax error: Expecting "," or "FROM" after column alias or "*"
Initially I thought I was dead in the water but then I decided to just run it anyway and it worked! I got my cost. OK so fine the query tool doesn't recognize some sql expressions. Because I am trying to make a large flat file I thought I'd concatenate 2 fields to generate a recognizable requisition, which everyone knows is the year followed by a hyphen then the req number:
My old Access query had this: Req: Right([REQ_YR],2) & "-" & [REQ_NO]
and I tried to change it to this: substring (EIM_REQUISITIONS.REQ_YR from 2 for 2) || '-' || EIM_REQUISITIONS.REQ_NO AS REQ
So apparently my older sql book doesn't have the syntax right. Since I plan to do more of this could someone point me to a reliable book or site that could help me with sql syntax?
I have problem using Data mining query task: when I want to set connection to Analysis services DB I don't see connections created in connection manager (they are not listed in the dropdown). (I can click on "New" and create new one but I already have one created in connection manager.)
Also, I want to make connections created in the connection manager flexible, easy to change after transfering package(s) to another server. How to do that?
Hey gang, I have ran into an installation issue that has been bugging me all weekend. I'm trying to run the preditive analytics query from a web based application (ASP.NET). I have installed a data pump as directed by this article "Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003" because this isn't set up under a domain. I also set appropriate roles in Data Mining Database (set it to everyone for testing purpose).
ConnectionString is "MSOLAP.3;Persist Security Info=True;Data Source=http://1.0.0.1/olap/msmdpump.dll;Initial Catalog=DataMiningDB"
Try Dim connection As AdomdConnection = Me.Connect Dim command As New AdomdCommand(txtQuery, connection) Dim adapter As New AdomdDataAdapter(command) Dim dataset As New DataSet
' Fill the dataset adapter.Fill(dataset)
' Bind to grid Return dataset Catch ex As Exception Throw ex End Try Return Nothing
Exception Details: Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Errors in the high-level relational engine. A connection could not be made to the data source specified in the query. Errors in the high-level relational engine. A connection could not be made to the data source specified in the query. OLE DB error: OLE DB or ODBC error: Login failed for user 'VMSQL2006IUSR_VMSQL2006'.; 28000.
It happens on the adapter.Fill(dataset). Any ideas?
I have also tried to set the olap application pool to use different identity instead of "Network Services", but I always get the same error message (but with user I specified instead of I_USR).
Hi i have a database with several tables and i want to create a Insert query. and i do this by adding the tables to a dataset and create the relations between them and now i click on the table i want to create a query for and select Add Query. and then the TableAdapter Query configuration Wizard pops-up. but the problem is that in the query builder i can add ONLY ONE table, why? i need to add more than one table in order to make my query. This is how this problem accured: At first i had to two databases, the first database i created myself and there i had all my tables and the second database was the one that contained all the membership info(this database is the one that visual studio creates automaticly for you when u are using the membership thing ASPNETDB.MDF). so i decided to only have one database and i re-created all my tables in the aspnetdb.mfd database, that is where this problem accured, that i can add only one table in the query builder. appreciate any help :) tnx
I am sure there will be a simple answer to this but it has got me stumped.
Having to move over to Vista with my new machine so I am having to switch to 2005 version for my development but still upload to a 2000 server.
I have had a look at 2005, like the new Management Studio, however I ahve a couple of problems which I can not find the answer to.
Firstly, the SQL Query Builder, where has it gone? I often have to import/export data from Excel files and used to use the SQL query builder to create my queries. If I want to copy all columns it is fine but if I want to import select columns I find it easier to view a list and then just add the ones I want.
Am I missing something here?
Secondly, copying stored procedures, before when running DTS ther were three options, Copy Tables/Views, Data Using Query and Copy Objects.
I used the copy opbjects a lot as it was a very quick way of transfering a group of tables and stored prcoedures that I had created. This appears to have now been replaced with Copy Database, which copies everthing, can can not be used to copy from SQL2005 to SQL2000.
If I want to copy multiple stored procedures from SQL2005 to SQL2000 how is it done now? I have tried finding out but have not been sucessful.
I'm having a heck of a time with named queries. At this point I just want a proof of concept in my DSV. I have a bunch of tables added to the DSV, and I would like to add a named query to it so the user can pick from the name query in report builder. What has to be done to make it appear in the report builder?
