In diesem Blogbeitrag möchte ich über ein sehr interessantes Feature von MS SQL Server berichten: Volltextsuche (Full-Text Search) und wie man es beim Erstellen von Berichten in SSRS einsetzen kann.
Ein mögliches Anwendung-Szenario für den Einsatz von Volltextsuche in einem Bericht ist die Suche in der Datenbank nach einem bestimmten Attribut anhand eines anderen Attributs – Z.B. man sucht anhand eines Titels nach der ISBN-Nummer eines Buchs.
Man gibt den Titel ein und bekommt als Auswahlliste die am meisten übereinstimmenden Ergebnisse (z.B. top 30). Nachdem diese Nummer ausgewählt wurde, kann sie als Parameter dynamisch im Bericht verwendet werden.
Ohne den Einsatz von Volltextsuche, z.B. durch Einschränkung der Ergebnisse mit dem Operator LIKE:
WHERE BUCHTITEL LIKE ‚%Business Intelligence%‘
stößt man schnell auf folgende Probleme:
1) die Ergebnisse sind ziemlich ungenau und
2) die Abfragezeiten bei großen Datenmengen können sehr lang sein.
Einen Ausweg könnte ein selbst geschriebener Algorithmus (als gespeicherte Prozedur auf dem SQL Server) anbieten, der eine genauere Suche ermöglicht. Im Netz findet man auch Beispiele für solche Search Engines [1]
Bei so einem Algorithmus muss man z.B. selbst definieren, welche Wörter ignoriert werden sollen, s.g. Stopplisten (z.B. ‚der‘, ‚die‘, ‚das‘ etc.) und wie Suche-Rankings vergeben werden. Obwohl die Suchergebnisse mit solchen Algorithmen genauer sind, haben sie trotzdem den Nachteil, dass so ein Algorithmus zunächst überhaupt geschrieben werden muss und die Abfragezeiten bei großen Datenmengen weiterhin lang bleiben.
Die Volltextsuche von MS SQL Server bietet dagegen eine elegantere und v.a. schnellere Lösung an. Volltextsuche– oder Fulltext-Search-Funktion ist eine optionale Komponente des SQL Server-Datenbankmoduls, die linguistische Suchvorgänge (einfache Wörter und Ausdrücke) anhand von Regeln einer bestimmten Sprache ermöglicht [2].
Der Unterschied in den Abfragezeiten im Vergleich zu den Abfragen mit LIKE-Operator für großen Datenmengen (Millionen von Zeilen) ist beträchtlich (Sekunden vs. Minuten).
Anhand eines kleinen Beispiels wird nun verdeutlicht, wie die Volltextsuche-Funktion in einem SSRS-Bericht verwendet wird.
Als Datengrundlage wird eine Tabelle verwendet, wo Informationen zu PRODUKT_ID, ISBN-Nummer, Titel und Autor gespeichert sind:
SELECT [PRODUKT_ID]
,[ISBN]
,[AUTOR]
,[TITEL]
FROM [dbo].[MEDIA_PRODUCTS]
Anhand der Suche nach dem Titel sollen dem Nutzer des Berichts die ISBN und der Autor vorgeschlagen.
Zunächst muss der Volltextsuche-Katalog (fulltext catalog) für die Datenbank angelegt werden. Dabei handelt es sich um ein logisches Konzept, das auf eine Gruppe von FULLTEXT-Indizes verweist [3]:
CREATE FULLTEXT CATALOG ft AS DEFAULT;
Danach wird der Volltext-Index für die Tabelle [dbo].[MEDIA_PRODUCTS] angelegt:
CREATE FULLTEXT INDEX
ON [dbo].[MEDIA_PRODUCTS] ([TITEL] Language 1031)
KEY INDEX [PK_Product]
WITH STOPLIST = SYSTEM;
GO
Pro Tabelle kann nur 1 Volltextindex für eine oder mehrere Spalten angelegt werden.
An dieser Stelle ist es wichtig zu erwähnen, dass man bei der Option LANGUAGE die Sprache wählen sollte, die die komplexesten Worttrennmodule enthält [4]: für Englisch, Spanisch und Deutsch ist es Deutsch (1031). KEY INDEX [PK_Product] verweist auf den Primär-Schlüssel in der Tabelle [dbo].[MEDIA_PRODUCTS]. Für die Volltextsuche wird außerdem in dem oberen Beispiel die Systemstoppliste (WITH STOPLIST = SYSTEM) verwendet (für die komplette Syntax fürs Anlegen der Volltextsuche-Indizes siehe [5]).
Interessant bei der Volltextsuche kann auch die Semantische Suche sein – man sucht z.B. nach Personennamen, die ähnlich sind (Option STATISTICAL_SEMANTICS [6]).
Nachdem der Volltextsuche-Index angelegt wurde, können entweder Volltextprädikate FREETEXT [7] und CONTAINS [8] oder Volltextsuche-Funktionen FREETEXTTABLE [9] und CONTAINSTABLE [10] verwendet werden.
Volltextprädikat CONTAINS sucht nach genauen (oder auch ungenauen, je nach Optionen des Prädikats) Übereinstimmungen der einzelnen Wörter oder Satzteile:
SELECT PRODUKT_ID, ISBN, AUTOR, TITEL
FROM [dbo].[MEDIA_PRODUCTS]
WHERE CONTAINS([TITEL], ‚“OLAP und Business Intelligence“‚);
Volltextprädikat FREETEXT dagegen sucht nach Bedeutung ohne exakte Übereinstimmungen des Wortlauts, angegebenen Wörtern, Ausdrücken oder Sätzen:
SELECT PRODUKT_ID, ISBN, AUTOR, TITEL
FROM [dbo].[MEDIA_PRODUCTS]
WHERE FREETEXT([TITEL], ‚OLAP und Business Intelligence‘);
Volltextsuche-Funktionen CONTAINSTABLE und FREETEXTTABLE sind ähnlich zu den Volltextprädikaten CONTAINS bzw. FREETEXT, liefern aber eine Tabelle als Ergebnis zurück mit solchen Spalten wie Volltextschlüssel (KEY) und Relevanz-Ranking(RANK). Das Ranking kann eingesetzt werden, um z.B. nur die 30 besten Ergebnisse sich anzeigen zu lassen (je höher das Ranking, desto besser die Übereinstimmung):
SELECT TOP 30
[dbo].[MEDIA_PRODUCTS].[PRODUKT_ID]
,[dbo].[MEDIA_PRODUCTS].[ISBN]
,[dbo].[MEDIA_PRODUCTS].[AUTOR]
,[dbo].[MEDIA_PRODUCTS].[TITEL]
,VolltextSucheTabelle.[KEY] AS VOLLTEXTSUCHE_KEY
,VolltextSucheTabelle.[Rank]
FROM [dbo].[MEDIA_PRODUCTS]
INNER
JOIN CONTAINSTABLE ([dbo].[MEDIA_PRODUCTS],[TITEL], ‚“OLAP und Business Intelligence“‚)
AS VolltextSucheTabelle
ON VolltextSucheTabelle.[KEY] = [dbo].[MEDIA_PRODUCTS].PRODUKT_ID
ORDER
BY VolltextSucheTabelle.[Rank] DESC;
Die Volltextsuche-Funktion FreeTextTable liefert für den Begriff „OLAP und Business Intelligence“ schon viel mehr Ergebnisse, die vielleicht nicht immer übereinstimmen, aber von der Bedeutung her ziemlich gut passen. Das Ranking gibt einen Hinweis, wie gut die Übereinstimmung ist:
SELECT TOP 30
[dbo].[MEDIA_PRODUCTS].[PRODUKT_ID]
,VolltextSucheTabelle.[KEY]
,[dbo].[MEDIA_PRODUCTS].[TITEL]
,VolltextSucheTabelle.[Rank]
FROM [dbo].[MEDIA_PRODUCTS]
INNER
JOIN FreeTextTable([dbo].[MEDIA_PRODUCTS],[TITEL],N’OLAP und Business Intelligence‘) AS VolltextSucheTabelle
ON VolltextSucheTabelle.[KEY] = [dbo].[MEDIA_PRODUCTS].PRODUKT_ID
ORDER BY VolltextSucheTabelle.[Rank] DESC;
Bevor mit dem Erstellen des Berichts angefangen wird, muss zunächst eine gespeicherte Prozedur (Stored Procedure) auf dem SQL SERVER angelegt werden, welche für die Volltextsuche verwendet wird. Diese wird benötigt, da sie im Dataset mit den SSRS Berichtsparametern aufgerufen wird und die Volltextsuche-Funktion FREETEXTTABLE eingesetzt wird. Ohne diese Prozedur ist der Aufruf von den Volltextsuche-Funktionen in dem Bericht nicht möglich.
CREATE PROCEDURE [dbo].[full_text_search_isbn]
@titel nvarchar(100)
,@top_n INT
AS
BEGIN
SELECT
[dbo].[MEDIA_PRODUCTS].ISBN
,[dbo].[MEDIA_PRODUCTS].TITEL
,[dbo].[MEDIA_PRODUCTS].AUTOR
,[dbo].[MEDIA_PRODUCTS].PRODUKT_ID
,[dbo].[MEDIA_PRODUCTS].ISBN +‚ — ‚ + [dbo].[MEDIA_PRODUCTS].AUTOR + ‚ — ‚ + [dbo].[MEDIA_PRODUCTS].TITEL AS ISBN_CAPTION
,fulltextSearch.[Rank]
FROM [dbo].[MEDIA_PRODUCTS]
INNER
JOIN FreeTextTable([dbo].[MEDIA_PRODUCTS],[TITEL],@titel)
AS fulltextSearch
ON fulltextSearch.[KEY] = [dbo].[MEDIA_PRODUCTS].PRODUKT_ID
ORDER
BY fulltextSearch.[Rank] DESC OFFSET 0 ROWS FETCH NEXT @top_n ROWS ONLY –top50 Werte
END
GO
In dem Bericht wird also nicht nach einer genauen Übereinstimmung gesucht, sondern nach der Bedeutung mit der FREETEXTTABLE-Funktion.
Zunächst wird ein Dataset angelegt, das die Informationen zu einer bestimmten ISBN-Nummer liefert:
SELECT [PRODUKT_ID]
,[ISBN]
,[AUTOR]
,[TITEL]
FROM [dbo].[MEDIA_PRODUCTS]
WHERE ISBN=@ISBN
Dabei wird der Parameter @ISBN verwendet. Da die Eingabe von diesem Parameter fehleranfällig und umständlich ist, werden die möglichen ISBN-Werte anhand der Volltextsuche nach Titel eines Buchs dynamisch ermittelt. Dafür wird ein weiteres Dataset benötigt. Dieses zweite Dataset (ISBN_Werte) nutzt die zuvor angelegte gespeicherte Prozedur [dbo].[full_text_search_isbn].
Es müssen zunächst die Parameter für die Prozedur angegeben werden, damit das Dataset erfolgreich generiert werden kann:
SSRS generiert danach automatisch 2 Berichtsparameter (@titel und @top_n).
Im nächsten Schritt wird dem Parameter @top_n der Standard-Wert 30 zugewiesen und der Parameter wird ausgeblendet (die 30 besten Übereinstimmungen sollten im Normallfall genug sein). Für den Parameter @ISBN werden die Werte aus dem Dataset ISBN_Values ermittelt. Dabei werden die Bezeichnungsfelder aus der Spalte ISBN_CAPTION ermittelt:
Nun ist alles fürs Anlegen einer Tabelle vorbereitet. Die Daten für die Tabelle werden aus dem Dataset „ISBN_Infos“ geladen:
Im nächsten Schritt wird der Bericht auf dem Report-Server bereitgestellt und kann verwendet werden. Die ISBN-Werte werden dynamisch ermittelt und dem Benutzer als Auswahlliste zur Verfügung gestellt:
Da Bezeichnungsfeld aus 3 Attributen (ISBN, Autor und Titel) zusammengesetzt wurde, erleichtert es dem Nutzer die Auswahl:
Anmerkung: Beim Berichtsentwurf kann eine Fehlermeldung vorkommen, dass ISBN-Berichtsparameter ein DefaultValue aufweist, das vom @titel-Berichtsparameter abhängt. Um diesen Fehler zu beseitigen, muss der @ISBN-Parameter im Dialogfenster Berichtsdaten einfach nach unten verschoben werden (hinter dem Berichtsparameter @titel).