Analysis services

I am doing a data mining project using the census bureau data set. The data set is a pdf, so I saved it as a text file, I imported as a flat source file and am trying to create my data source? But am having trouble with this? Please help?

Can you post details of the errors you're seeing? This might be a question for the Integration Services forum.|||i fixed the error. but thanks. Perhaps, you could help me with Analysis services, Data source views. Under the Data source views tab select tables and views, if I only have one file of data with columns and rows, is it possible to data mine this one file. or should i be incorporating more files?|||

There are no specific restrictions on the number of tables you need to use to perform data mining. You can certainly mine data in a single file/table if that contains the attributes you're interesting in mining.

For example, you could have a single customer table with demographic information like age, income and so forth and you could apply several of the available algorithms to it to derive interesting patterns - clustering to group together similar customers, decision trees to classify them based on how they behave with respect to a predictable attribute or naive-bayes to explore dependencies and differences between attributes for customers.

|||

How would you implement the MS Time series algorithm to predict population patterns? or would you suggest I use MS decision trees?

Thanks

|||Create a table with date and columns for each series you want to analyze

An issue with Full Text Search against PDF blobs...

My client is trying to use SQL Server Full Text Search to search PDFs stored
in a varbinary(max) column. Full text search works fine for other Microsoft
Office documents stored in the same table but it does NOT give back any
results from the PDF documents.
Following is a high-level view of what the client did:
1. Installed Adobe PDF IFilter 6.0
2. Ran the stored procedure sp_fulltext_service (as documented)
3. Restarted the server
4. Verified that the filter got properly installed by querying the system
view
sys.fulltext_document_types
5. Created a full text index on the table with the documents
6. Started a full population of the index
7. Ran a sample query with a string he knows is in the PDF file like the
following:
select * from documents where freetext(document, ‘Review’) and got no
results
back
8. Ran the same sample query with a string he knows is in some Word files
like the
following: select * from documents where freetext(document, ‘SQL’) the
query
returned several rows back as expected.
Does anybody know what might be happening here?
Thank you!
Camilo Leon
Camilo,
Are you using 64-bit Windows and SQL Server? If so, last time I looked the
Adobe PDF IFilter was only 32-bit.
RLF
"Camilo" <Camilo@.discussions.microsoft.com> wrote in message
news:92BEFB18-89BF-4C7E-8B11-769B0C329B7B@.microsoft.com...
> My client is trying to use SQL Server Full Text Search to search PDFs
> stored
> in a varbinary(max) column. Full text search works fine for other
> Microsoft
> Office documents stored in the same table but it does NOT give back any
> results from the PDF documents.
> Following is a high-level view of what the client did:
> 1. Installed Adobe PDF IFilter 6.0
> 2. Ran the stored procedure sp_fulltext_service (as documented)
> 3. Restarted the server
> 4. Verified that the filter got properly installed by querying the system
> view
> sys.fulltext_document_types
> 5. Created a full text index on the table with the documents
> 6. Started a full population of the index
> 7. Ran a sample query with a string he knows is in the PDF file like the
> following:
> select * from documents where freetext(document, 'Review') and got no
> results
> back
> 8. Ran the same sample query with a string he knows is in some Word files
> like the
> following: select * from documents where freetext(document, 'SQL') the
> query
> returned several rows back as expected.
> Does anybody know what might be happening here?
> Thank you!
> Camilo Leon
>
|||Russell,
No, we are using a 32-bit machine in this case.
I did make FTS work with PDF files by creating a new table, importing the
data, creating a new catalog and populating it again.
Thanks!
Camilo
"Russell Fields" wrote:

> Camilo,
> Are you using 64-bit Windows and SQL Server? If so, last time I looked the
> Adobe PDF IFilter was only 32-bit.
> RLF
> "Camilo" <Camilo@.discussions.microsoft.com> wrote in message
> news:92BEFB18-89BF-4C7E-8B11-769B0C329B7B@.microsoft.com...
>
>
|||Check the gatherer logs to see what the status of your population was.
relevantNoise - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Camilo" <Camilo@.discussions.microsoft.com> wrote in message
news:92BEFB18-89BF-4C7E-8B11-769B0C329B7B@.microsoft.com...
> My client is trying to use SQL Server Full Text Search to search PDFs
> stored
> in a varbinary(max) column. Full text search works fine for other
> Microsoft
> Office documents stored in the same table but it does NOT give back any
> results from the PDF documents.
> Following is a high-level view of what the client did:
> 1. Installed Adobe PDF IFilter 6.0
> 2. Ran the stored procedure sp_fulltext_service (as documented)
> 3. Restarted the server
> 4. Verified that the filter got properly installed by querying the system
> view
> sys.fulltext_document_types
> 5. Created a full text index on the table with the documents
> 6. Started a full population of the index
> 7. Ran a sample query with a string he knows is in the PDF file like the
> following:
> select * from documents where freetext(document, 'Review') and got no
> results
> back
> 8. Ran the same sample query with a string he knows is in some Word files
> like the
> following: select * from documents where freetext(document, 'SQL') the
> query
> returned several rows back as expected.
> Does anybody know what might be happening here?
> Thank you!
> Camilo Leon
>

Alternative to IIF ? Case ?

Hi,
Need to display a value in a text box based upon perhaps one of four
values a parameter could have. This example is great for two values, = iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
Is there an alternative , case... anything if we need to test for over
parameter values i.e. HHH1, or HHH2.
Many thanks, Steve.Try Switch:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctSwitch.asp
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> Hi,
> Need to display a value in a text box based upon perhaps one of four
> values a parameter could have. This example is great for two values, => iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> Is there an alternative , case... anything if we need to test for over
> parameter values i.e. HHH1, or HHH2.
> Many thanks, Steve.|||Report parameters have a Value and a Label property. You could use the Label
property for this purpose.
Regarding alternatives for IIF please check the following MSDN links:
* =Choose(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
* =Switch(...)
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> Hi,
> Need to display a value in a text box based upon perhaps one of four
> values a parameter could have. This example is great for two values, => iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> Is there an alternative , case... anything if we need to test for over
> parameter values i.e. HHH1, or HHH2.
> Many thanks, Steve.|||Thank you Robert !
"Robert Bruckner [MSFT]" wrote:
> Report parameters have a Value and a Label property. You could use the Label
> property for this purpose.
> Regarding alternatives for IIF please check the following MSDN links:
> * =Choose(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctchoose.asp
> * =Switch(...)
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctswitch.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> > Hi,
> > Need to display a value in a text box based upon perhaps one of four
> > values a parameter could have. This example is great for two values, => > iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> >
> > Is there an alternative , case... anything if we need to test for over
> > parameter values i.e. HHH1, or HHH2.
> >
> > Many thanks, Steve.
>
>|||Switch rocks !!! Thanks Jeff. Happy New year.
"Jeff A. Stucker" wrote:
> Try Switch:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctSwitch.asp
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:77DEB13A-6813-415E-A4A2-58190F3A7CF4@.microsoft.com...
> > Hi,
> > Need to display a value in a text box based upon perhaps one of four
> > values a parameter could have. This example is great for two values, => > iif(UCASE(Parameters!SUPPID.Value) ="HHH", "Defense & Space", "Defense")
> >
> > Is there an alternative , case... anything if we need to test for over
> > parameter values i.e. HHH1, or HHH2.
> >
> > Many thanks, Steve.
>
>

Alternative to Full Text Search ?

I'm aiming to provide a Ranked multiple keyword search for a varchar(3175) column on my database but Full Text Search is not an option and there could be as many as 15,000 rows in a result list.

I see that I have two choices.

1. Do everything on the Database server.
Get all results that match all submitted keywords and then count how many times each keyword is found for each record. Total each score for each record.

2. Do all of this nasty processor intenstive work on the web server
Get all results that match all submitted keywords from the database and place in a DataTable. For each record in the DataTable perform some C# based match and count loop. Place result of each loop in extra 'Result' column of DataTable and the sort DataTable according to score.

I can confidently code up option 2 however option 1 seems like a real headache in terms of the necessary SQL (my SQL is not that good). Also I think with option two I can use Caching to save the reordered datatable primary key with cache parameters based on the submitted keywords so the search should eventually get quite fast.

Does this all sound like nonsense ?
Has anyone tried to provide a Ranked multiple keyword search without using a Full Text Indexing ?

Thanks.Full Text Indexing would obviously be the right tool for this job.

What comes to mind for the next best solution would be to write a UDF which will take a keyword and a string and return the number of occurrences of the keyword in the string. Then you could sort by the number of occurrences.

The resulting query would look like this:

SELECT
 Description,
 OccurrenceCount
FROM
 (SELECT Description, dbo.fnOccurrenceCount(@.SearchFor, Description) AS OccurrenceCount FROM myTable ) A
WHERE
 OccurrenceCount > 0
ORDER BY
 OccurrenceCount DESC,
 Description

And the UDF would look like this:

CREATE FUNCTION dbo.fnOccurrenceCount (@.SearchNeedle varchar(8000), @.SearchHaystack varchar(8000))
RETURNS integer AS
BEGIN
DECLARE @.OccurrenceCount int
DECLARE @.FoundPosition int
DECLARE @.SearchHaystackPart varchar(1000)
DECLARE @.SearchNeedleLength int
SET @.OccurrenceCount = 0
SET @.FoundPosition = 0
SET @.SearchHaystackPart = @.SearchHaystack
SET @.SearchNeedleLength = DATALENGTH(@.SearchNeedle)
SET @.FoundPosition = CHARINDEX(@.SearchNeedle, @.SearchHayStackPart)
WHILE @.FoundPosition > 0
BEGIN
SET @.OccurrenceCount = @.OccurrenceCount + 1
SET @.SearchHaystackPart = SUBSTRING(@.SearchHaystackPart,@.FoundPosition+@.SearchNeedleLength,8000)
SET @.FoundPosition = CHARINDEX(@.SearchNeedle, @.SearchHayStackPart)
END
RETURN @.OccurrenceCount
END

Terri|||[sorry if this appears as a double post my last reply must have timed out]

Thanks Terri - that works really well ! - without trying to sound too dramtic - your're a life saver !

As I said in my first post my C# is OK but my T-SQL is not great. Can you recommend any books or article links I can read which could help me to work out solutions like this for myself ?

Thanks again,

TheDr|||Cool, I'm glad you were able to make use of that method!

Personally I have found that participating in forums such as this and those on aspadvice.com to be the best way to increase my knowledge. Reading other people's problems and then trying out different ways to solve them has helped me immensely -- especially when others recommend methods I hadn't even thought of. Following links that the experts post on the forums also helps.

For reading material, I would have to recommend Ken Henderson's The Guru's Guide to Transact-SQL as a must-read.

Terri|||The only question I have is from the following line of your Proc what does the 'A' represent ? If I try any run the Proc without it I'm given an error. Is it an alias for the parenthesised result ?

<snip>
(SELECT Description, dbo.fnOccurrenceCount(@.SearchFor, Description) AS OccurrenceCount FROM myTable ) A
</snip
Thanks for your book suggestion. I'll look out for that one.
Cheers.|||Yes, that's exactly what it is; an alias for the derived table. A lazy alias at that. When using derived tables a "correlation name alias" (as they call it) is required.

Terri

Alternative for LEFT() and CHARINDEX() that works with text Data Type

I have a stored procedure that receives a list of ids, to get the emails of each of those ids. The problem that I'm having is that I'm using a char data type that is max length is 8000, but the contact lists are getting bigger and soon they might reach the variable max length. The contact list that I receive will look something like this "1234,67523,67875,789687,", I'm using "," as a separator. Right now what I do is this

@.array_value=LEFT(@.ContactList, @.separator_position- 1)

The LEFT function doesn't work with data types text and ntext. But I'm in need of a string data type with a max length bigger than 8000. So I will apreciate if anyone knows of another function that does the same or similar and works with text data type, or any other ideas will be welcome.

Thanks,

Erick

what database & version are you using?

|||

SQL Server 2005

|||

Use varchar(max) instead of varchar(8000) then.

|||

Can you change the datatype? Try using varchar(MAX) or nvarchar(MAX). MSDN says built-in string functions have been built to work with the new large object types (MAX).

http://msdn2.microsoft.com/en-us/library/ms178158.aspx

|||

I just changed, I didn't know that worked. I just read thats new in sql 2005, that didn't work on 2000.

Thanks

|||

That worked but, Can I give max value to varchar in asp.net vb?

|||

http://msdn2.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

|||

It looks like you just set the type to the normal DBType, i.e. use varchar for varchar(MAX), then set the length to -1 (this tells the parameter to use whatever length the data is, rather than specifying how much of the data to use).