Fuzzy lookup transform row scores 'inconsistent' with individual column scores

I am trying to interpret some of the results I observe when trying to match similar records using a fuzzy lookup transform, but it's not entirely clear how the overall row similarity score is calculated. In particular, sometimes rows with lower individual column similarity scores will achieve a higher similarity and confidence score than a matching row with higher individual column scores.

The transform is configured with 6 text fields set to fuzzy mapping and a minimum similarity of 0, and 3 additional numeric fields with an exact mapping. It is set to return a maximum of 2 matches per lookup and to do an exhaustive search of the reference table.

For example, from the following matching pair of records Match 1 is picked over Match 2 even though it's individual scores are lower.

Match 1 Match 2
-- --
_similarity_author 1.0 1.0
_similarity_title 0.85344648 1.0
_similarity_headline 0.0125 0.0125
_similarity_summary 0.0125 0.0125
_similarity_picture 1.0 1.0
_similarity_caption 1.0 1.0

_similarity 7.8429267E-2 7.3196657E-2
_confidence 0.55728668 0.44271332

In another case both matching records have *identical* scores for every mapped column and yet their similarity and confidence scores are different.

Clearly there are other factors involved in calculating the overall row score. Anybody know what these are?

Fernando Tubio

Can't even begin to describe it in my own words. This article describes the Fuzzy Math real well. Don't know if you've seen it.

http://msdn.microsoft.com/msdnmag/issues/05/09/SQLServer2005/

|||

Thank you Martin.

I've read the article and it explains the lookup process well. Unfortunately it doesn't answer my question. Specifically, having found two matches, why does the matching algorithm discard what appears to be a better match, at least judging from individual column similarity scores.

I am trying to understand the mechanism to determine if there is anything I can tweak in order to force the algorithm to make a better choice.

Fuzzy Grouping error when progress: Finding similar records

I get this errors when progress is : Finding similar records

[DTS.PIPELINE] Information: The buffer manager detected that the system was low on virtual mermory, but was unable to swap out any buffers. 6 buffers where considered and 6 where locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

I'm triying to fuzzy match in a table with 1.000.000 records.

My computer RAM is 1GB.

How can I resolve the problem?

Tanks,

Eusebio.

There was a known memory issue in FuzzyLookup which will become more evident in certain circumstance(long running pkg against a large reference table) - FuzzyGrouping uses FuzzyLookup underneath, so it shares the problem. This was fixed for SP1.

For now you can try the possilbe work arounds of setting BufferTempStoragePath or/and inreasing virtual memory size which were discussed in this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=122391&SiteID=1

Thanks

Wenyang

Function to find character in string

Hi:
I was looking through BOL for a function that would return the
numerical place where a string first occurs (similar to Crystal
Report's 'instr' function):
so
FUNKYFUNCTION('abcdefgd','d',1)
would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
argument is the starting place in the string.
Basically, what I want to do is trim everything after the first space
is found. I might have certain field values that end up as:
5445 UNWANTEDSTRINGETXT
I want this field to be changed to '5445' when it finds unwanted text.
So having this function would make this easy. Or maybe there is another
way in the absence of such a function?
Thanks for the help,
Kaydadeclare @.str varchar(80)
set @.str = '5445 UNWANTEDSTRINGETXT'
SELECT CHARINDEX(' ', @.str),
CASE WHEN CHARINDEX(' ', @.str) = 0
THEN @.str
ELSE SUBSTRING(@.str,1,CHARINDEX(' ', @.str)-1)
END
On 16 Feb 2006 16:13:03 -0800, "Kayda" <blairjee@.gmail.com> wrote:

>Hi:
>I was looking through BOL for a function that would return the
>numerical place where a string first occurs (similar to Crystal
>Report's 'instr' function):
>so
>FUNKYFUNCTION('abcdefgd','d',1)
>would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
>argument is the starting place in the string.
>Basically, what I want to do is trim everything after the first space
>is found. I might have certain field values that end up as:
>5445 UNWANTEDSTRINGETXT
>I want this field to be changed to '5445' when it finds unwanted text.
>So having this function would make this easy. Or maybe there is another
>way in the absence of such a function?
>Thanks for the help,
>Kayda|||The equivalent of instr in MS SQL is CHARINDEX(stringtofind,thewholestring)
with the parameter reversed from that of instr.
Regards,
Willson
"Kayda" wrote:

> Hi:
> I was looking through BOL for a function that would return the
> numerical place where a string first occurs (similar to Crystal
> Report's 'instr' function):
> so
> FUNKYFUNCTION('abcdefgd','d',1)
> would return 4, the first occurrence of the 2nd argument: 'd'. The 3rd
> argument is the starting place in the string.
> Basically, what I want to do is trim everything after the first space
> is found. I might have certain field values that end up as:
> 5445 UNWANTEDSTRINGETXT
> I want this field to be changed to '5445' when it finds unwanted text.
> So having this function would make this easy. Or maybe there is another
> way in the absence of such a function?
> Thanks for the help,
> Kayda
>

Function similar to ISNULL()

I'm constructing a single string of several counts with concatenated labels using SQL and want to not show zeros (or their labels). Is there a function within an SQL statement that will let me do this? ISNULL() sort of does this, but I really need to test for zero instead of NULL to eliminate noise data from the string.You could write your own User Defined Function. Check out Books On Line for more info.
|||

Try the links below you may find T-SQL funtion to do what you need, but remember ISNULL can give just plain wrong numbers with OUTER JOIN in SQL Server, it is a known issue. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_nos-nz_3uhy.asp

http://www.samspublishing.com/articles/article.asp?p=31283&seqNum=3&rl=1

function index

I have implemented function based indexes on tables in Oracle database. I want to implement same in SQL Server. Is there something similar to function index in SQL Server also? If yes, please explain as how I can do this.
Thank,
dcExplain more clearly what you want to do.|||You have to create a computed column with the function you want indexed, then index the computed column. Then you have to change all of your applications to query the new column. Simple, eh?

In short, Function Based Indexes don't really exist in SQL Server, but they are pretty nice things to have.

create index ind on table (upper(name))|||Function based indicies (where the indexed value is the result of one or more function calls) were the most dearly missed feature of Clipper when I converted to SQL Server. They are lovely things when you don't have to deal with most of the complexities that a real relational database engine is intended to solve!

Unfortunately, they don't scale, introduce all kinds of "functional dependancies", lead to really interesting collation questions, etc. A Function Based Index is certainly convenient in many ways, but it is a scaling/supportability nightmare in search of its next victim!

-PatP

function does not exist for decision tree when running tutorial

When I run the Microsoft tutorial for data mining I get this error when I get to the decision tree part.
I get a similar error for clustering in the same tutorial.
However, The Naive Bayes demo seems fine.
The messages said the project was built and deployed without errors.

Does anyone know how to fix the error:

TITLE: Microsoft Visual Studio

The tree graph cannot be created because of the following error:

'Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[DecisionTrees].[GetTreeScores]' function does not exist.'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.AnalysisServices.Viewers.SR&EvtID=ErrorCreateGraphFailed&LinkId=20476

ADDITIONAL INFORMATION:

Query (1, 6) The '[System].[Microsoft].[AnalysisServices].[System].[DataMining].[DecisionTrees].[GetTreeScores]' function does not exist. (Microsoft OLE DB Provider for Analysis Services 2005)

BUTTONS:

OK

Please follow my answer in a previous thread to fix this problem:

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

Please let me know whether this works.

Good luck,

|||Thank-you that did work - however, it did not seem to work the first time I tried it. I rebooted the server and then it worked - though I don't know if that is related.

I have an OLAP directory in two places MSSQL.2 and MSSQL.5.
The programs mentioned were in mssql.5 but the system entry was pointing to mssql.2
I wondering if I should also copy the subdirectories that are under olap/bin to mssql.2

I'm also wondering how this happened in the first place.
I am fairly certain the files were not deleted from mssql.2 by accident.

Could this be an installation issue?

I started out with the trial version and 'upgraded' to a fully licensed version.

Thanks again.|||

I think it is an installation issue.

I had the same problem when upgrading from "Standard Edition" to "Enterprise Edition".

Couldn't change the setting to MSSQL.5 in the path string. Needed to copy all the files into MSSQL.2. Now I've got them in both places, which seems a little redundant and weird.