Showing posts with label based. Show all posts
Showing posts with label based. Show all posts

Friday, March 30, 2012

Predicting from a clustering model

Hi,

I have built a Clustering model that captures customer demographic information

and identify various hidden clusters based on the information.

What kind of predictions can I make using the above model?

You can do multiple types of queries with a clustering model:

Get the most likely cluster for each case (or a new singleton case) - SELECT Cluster() FROM [model]|||

Thanks Raman,

The 1st, 2nd and the 4th points are clear but I have certain doubts

regarding the 3rd point you mentioned

Firstly as the attributes are customer characteristics like

age, gender and other demographic information and we suppose that the values

for these always exist for all the cases (new/old). What will be the point of

making them predictable and predicting their values again.

Secondly if I want to predict an attribute(s) like

MovieBuyer (determines whether a customer has bought a movie(s) or no) for new

cases, wouldn’t it be better if I use ‘decision trees’ or ‘neural networks’

algorithm rather than clustering.

Basically I cannot imagine a scenario in which clustering

stands out as the best suited algorithm for prediction.

It seems that Clustering is best suited for exploring and

understanding the data rather than for prediction.

I hope I have expressed my problem adequately and clearly.

Any help would be most appreciated.

|||

Your assessment is correct in that clustering is more suitable for data exploration/understanding - I was just pointing out that it is *possible* to do prediction with the SQL Server DM Clustering algorithm (which was your original question).

|||Thanks ramansql

Predicting from a clustering model

Hi,

I have built a Clustering model that captures customer demographic information

and identify various hidden clusters based on the information.

What kind of predictions can I make using the above model?

You can do multiple types of queries with a clustering model:

Get the most likely cluster for each case (or a new singleton case) - SELECT Cluster() FROM [model]|||

Thanks Raman,

The 1st, 2nd and the 4th points are clear but I have certain doubts

regarding the 3rd point you mentioned

Firstly as the attributes are customer characteristics like

age, gender and other demographic information and we suppose that the values

for these always exist for all the cases (new/old). What will be the point of

making them predictable and predicting their values again.

Secondly if I want to predict an attribute(s) like

MovieBuyer (determines whether a customer has bought a movie(s) or no) for new

cases, wouldn’t it be better if I use ‘decision trees’ or ‘neural networks’

algorithm rather than clustering.

Basically I cannot imagine a scenario in which clustering

stands out as the best suited algorithm for prediction.

It seems that Clustering is best suited for exploring and

understanding the data rather than for prediction.

I hope I have expressed my problem adequately and clearly.

Any help would be most appreciated.

|||

Your assessment is correct in that clustering is more suitable for data exploration/understanding - I was just pointing out that it is *possible* to do prediction with the SQL Server DM Clustering algorithm (which was your original question).

|||Thanks raman

Wednesday, March 28, 2012

Precedence Constraints and Sequence Containers

I have about 12 sequence containers mapped out to execute separately based on a precedence contraint and an expression. These lie at the same level and order in my sequence. Only one of these will execute based on an expression. After any of these executes, I have a consecutive sequence container that I'm attempting to execute. I've set the precedence contraint from all twelve of the prior sequence containers to this single sequence container that I would like to run after any of the 12. My problem is that the package will only allow one of these twelve sequence containers to become a precedence of this second single sequence container at a time. The package will let me graphically attach the precedence constraint from all 12 to this single sequence container, but when the package runs, it fails to follow through to this single sequence container. I'm trying to figure out why this is the case and how I can get what I would like to work -- work. Thanks.

Double-click on one of the precendence constraints going into the single sequence container. Near the bottom you will see a drop down that allows you to set it to an OR condition (The constraints will turn into dotted lines)

This means that that will execute if ANY of its precedence cnstraints are satisfied. If you leave it set to AND, then ALL of the constraints have to be satisfied, which will never happen in your case since the other 11 sequence containers will never execute.

|||

Thanks Dave!

I saw that but wasn't sure how that worked.

Monday, March 12, 2012

Possible to Update a table based on Excel?

Hi all
I was wondering if it was possible to update a table based off of information from Excel. here is what I though would have worked.

update MyTest Set acctNumber='111' FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\MyFile.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') where [ProductGroup]='Hal Butts'

with 'Update MyTest' being the table name. It does have the same name as the excel file. Just to rule that out.

It gives me this error

Ambiguous column name 'ProductGroup'.

If it is possible what is the correct syntax??

tiborupdate MyTest Set acctNumber='111'
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'
, 'Excel 8.0;Database=C:\MyFile.xls;HDR=YES'
, 'SELECT * FROM [Sheet1$]') WHERE [ProductGroup]='Hal Butts'Isn't complete. Your problem may be in the code that is missing from your post.

-PatP|||Well that is all the code I have.|||update mt
set mt.acctNumber = '111'
from MyTest mt
inner join OPENROWSET('Microsoft.Jet.OLEDB.4.0'
, 'Excel 8.0;Database=C:\MyFile.xls;HDR=YES'
, 'SELECT * FROM [Sheet1$]') ors ON mt.joincolumn = ors.joincolumn
WHERE mt.[ProductGroup]='Hal Butts'

The above isn't going to work, because I have no idea how you are trying to find the right columns to update based on the OPENROWSET. You need to figure it out though. You have to do something like what I have shown in the example, so you have an executable piece of code that makes sense.

Since ProductGroup is in both the MyTest table AND the spreadsheet, the UPDATE statement can't figure out which column you are referring to when you reference it.|||great thanks. its seems to be a bit long but works nicely. thank you so much.

tibor|||However....you would be better served by loading the excel file into a staging table and the perform your dml operations...excel is very unpredictable and is open to be modified by end users that could break you process...

If you do the load, only the load may fail, or if it does load, may fail a data audit the you would write...

I've come to hate excel|||Well i am still quite newbish to sql. how exactly would i load it to a staging table?|||Use defalt DTS import facility to transfer rows from excel to the table (create this table after assessing the columns on the excel sheet). Then use SELECT INTO to get the required data from staging to main table.

Friday, March 9, 2012

Possible to OPENXML() from URL?

Is it possible to get the XML Document for a call to OPENXML() from a URL
directly?
Example: I have a .NET web service that returns XML based on parameters
submitted to the URL. I'd like to be able to write a TSQL batch that gets
XML documents from this server and processes them using OPENXML to update
rows in a SQL table.
Anyone ever done something like this?
TIA
Paul
Have you considered using an XML template? Templates accept parameters, much in the same way as stored procedures.
I've written an article on this topic, which you can find at the following URL:
http://www.sqljunkies.com/Article/53...3C8ECACDC.scuk
Hope this helps!
Denise.
Denise E. White
Technical Director
The Next Version UK
www.thenextversion.com
www.denisewhite.co.uk

Wednesday, March 7, 2012

Possible to Create Report model - mySQL

Is it possible to create a Report Model based on a mySQL server? i am getting
errors: ERROR [42000] [MySQL][ODBC 3.51 when connecting. Ihave been able to
write standard reports but am running into this issue. Also I was looking
into using the DotNet Data connector but cannot figure out how to use it...Microsoft Support told me that they are not supporting any Report
Models based on non-SQL Server data sources. They suggested using a
linked server and creating a view to the linked server tables in a SQL
Server database. I wish they would have been more straight forward
about this in the documentation. It contradicts what I found in there
documentation "Data Sources Supported by Reporting Services":
http://msdn2.microsoft.com/en-us/library/ms159219.aspx.

possible to connect to SQL Server Mobile database

Is it possible to have a Windows applications (based on .NET Framework 2.0) connect to a SQL Server Mobile database?

I manually added the the System.Data.SqlServerCe.dll (retrieved from C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\\SQL Server\Mobile\v3.0\System.Data.SqlServerCe.dll) to my Windows Form project. When I start the app, it gives me an error message:

{"Could not load file or assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)":"System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"}

TIA

K. I got this to work.

My problem was that I did not reference the dll from the correct location. The correct location of the SqlServerCe dll to reference is "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\System.Data.SqlServerCe.dll"

|||

Thank god i found this, i tried what was said in

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=284483&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=367019&SiteID=1

that didnt work for we, some how it worked for other ppl it seems.

anyway, Thanks

|||

Dear Stormin,

I took the dll from the right location as you specified. But now I get another error Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E).

|||

Hi all,

I have been searching for weeks now trying to solve the same problem. I wrote a desktop application- and I waqnt this desktop application to access a sdf database. The correct system.data.SqlServerCe.dll is correctly referenced and it still gives me a "Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)" error. If I can open the database from within the Dev environment and edit it from there, why can no one come up with a solution to this problem?

|||Have you installed the SQL CE runtime components on the machine? Have you tried to include all the SQL CE dlls with your app?|||

Copy all seven sqlce...dll files from C:\Program Files\Microsoft Visual Studio 8\Common7\IDE over to SYSTEM32 folder. This solved the problem for me. Check out the following link for details.

http://blogs.msdn.com/smartclientdata/archive/2005/07/15/439008.aspx

possible to connect to SQL Server Mobile database

Is it possible to have a Windows applications (based on .NET Framework 2.0) connect to a SQL Server Mobile database?

I manually added the the System.Data.SqlServerCe.dll (retrieved from C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\\SQL Server\Mobile\v3.0\System.Data.SqlServerCe.dll) to my Windows Form project. When I start the app, it gives me an error message:

{"Could not load file or assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)":"System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"}

TIA

K. I got this to work.

My problem was that I did not reference the dll from the correct location. The correct location of the SqlServerCe dll to reference is "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\System.Data.SqlServerCe.dll"

|||

Thank god i found this, i tried what was said in

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=284483&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=367019&SiteID=1

that didnt work for we, some how it worked for other ppl it seems.

anyway, Thanks

|||

Dear Stormin,

I took the dll from the right location as you specified. But now I get another error Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E).

|||

Hi all,

I have been searching for weeks now trying to solve the same problem. I wrote a desktop application- and I waqnt this desktop application to access a sdf database. The correct system.data.SqlServerCe.dll is correctly referenced and it still gives me a "Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)" error. If I can open the database from within the Dev environment and edit it from there, why can no one come up with a solution to this problem?

|||Have you installed the SQL CE runtime components on the machine? Have you tried to include all the SQL CE dlls with your app?|||

Copy all seven sqlce...dll files from C:\Program Files\Microsoft Visual Studio 8\Common7\IDE over to SYSTEM32 folder. This solved the problem for me. Check out the following link for details.

http://blogs.msdn.com/smartclientdata/archive/2005/07/15/439008.aspx

possible to connect to SQL Server Mobile database

Is it possible to have a Windows applications (based on .NET Framework 2.0) connect to a SQL Server Mobile database?

I manually added the the System.Data.SqlServerCe.dll (retrieved from C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\\SQL Server\Mobile\v3.0\System.Data.SqlServerCe.dll) to my Windows Form project. When I start the app, it gives me an error message:

{"Could not load file or assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)":"System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"}

TIA

K. I got this to work.

My problem was that I did not reference the dll from the correct location. The correct location of the SqlServerCe dll to reference is "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\System.Data.SqlServerCe.dll"

|||

Thank god i found this, i tried what was said in

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=284483&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=367019&SiteID=1

that didnt work for we, some how it worked for other ppl it seems.

anyway, Thanks

|||

Dear Stormin,

I took the dll from the right location as you specified. But now I get another error Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E).

|||

Hi all,

I have been searching for weeks now trying to solve the same problem. I wrote a desktop application- and I waqnt this desktop application to access a sdf database. The correct system.data.SqlServerCe.dll is correctly referenced and it still gives me a "Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)" error. If I can open the database from within the Dev environment and edit it from there, why can no one come up with a solution to this problem?

|||Have you installed the SQL CE runtime components on the machine? Have you tried to include all the SQL CE dlls with your app?|||

Copy all seven sqlce...dll files from C:\Program Files\Microsoft Visual Studio 8\Common7\IDE over to SYSTEM32 folder. This solved the problem for me. Check out the following link for details.

http://blogs.msdn.com/smartclientdata/archive/2005/07/15/439008.aspx

possible to connect to SQL Server Mobile database

Is it possible to have a Windows applications (based on .NET Framework 2.0) connect to a SQL Server Mobile database?

I manually added the the System.Data.SqlServerCe.dll (retrieved from C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\\SQL Server\Mobile\v3.0\System.Data.SqlServerCe.dll) to my Windows Form project. When I start the app, it gives me an error message:

{"Could not load file or assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)":"System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"}

TIA

K. I got this to work.

My problem was that I did not reference the dll from the correct location. The correct location of the SqlServerCe dll to reference is "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\System.Data.SqlServerCe.dll"

|||

Thank god i found this, i tried what was said in

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=284483&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=367019&SiteID=1

that didnt work for we, some how it worked for other ppl it seems.

anyway, Thanks

|||

Dear Stormin,

I took the dll from the right location as you specified. But now I get another error Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E).

|||

Hi all,

I have been searching for weeks now trying to solve the same problem. I wrote a desktop application- and I waqnt this desktop application to access a sdf database. The correct system.data.SqlServerCe.dll is correctly referenced and it still gives me a "Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)" error. If I can open the database from within the Dev environment and edit it from there, why can no one come up with a solution to this problem?

|||Have you installed the SQL CE runtime components on the machine? Have you tried to include all the SQL CE dlls with your app?|||

Copy all seven sqlce...dll files from C:\Program Files\Microsoft Visual Studio 8\Common7\IDE over to SYSTEM32 folder. This solved the problem for me. Check out the following link for details.

http://blogs.msdn.com/smartclientdata/archive/2005/07/15/439008.aspx