User defined types in SQL Server

Zuletzt hatte ich das Problem, dass ich temporäre Daten in einer TABLE-Variable hatte und diese in einer Tabellenwert Funktion weiter verarbeiten musste. Problem dabei war, wie gebe ich meine Tabelle an die Funktion weiter?

Die erste Idee war, dass ich eine temporäre Tabelle erstelle und die Daten dort platziere und dann in der Funktion lese – tsja, dass geht leider nicht, bzw. dies lässt der SQL Server nicht zu.

Okay, und nun? Da diese Funktionalität ständig zur Verfügung (zur Verwendung in einem SSRS Bericht) stehen musste, brauchte ich eine stabile und multiuser-fähige Lösung.

Nächste Idee wäre, eine physische Tabelle zu erstellen und darin die Daten zu speichern. Für die multiuser-Fähigkeit könnte ich Prozess- oder die Benutzer ID aufnehmen… ist aber irgendwie umständlich, da einerseits Berechtigungen auf die Tabelle erteilt werden müssen, Indizierung, Speicher etc …

Die Lösung war nun, benutzerdefinierte Typen im SQL Server zu erstellen. Bisher kannte ich das mehr von CLR’s, aber der SQL Server bietet bereits seit Version 2008 die Möglichkeit, eigene Typen für Tabellenwert-Variablen zu erstellen.

Via CREATE TYPE wird der Typ mit der entsprechenden Definition erstellt, bspw.:

CREATE TYPE tmpDataType As Table (
 data VARCHAR(255));
GO

Aus diesem Type kann dann ein entsprechendes Objekt erstellt werden:

DECLARE @tmpDataTable As tmpDataType;

Dieses Objekt kann dann wie eine bisherige Tabellenwert Variable mit Daten befüllt werden. Das Verhalten ist völlig identisch mit den bisherigen Tabellewert Variablen, die Daten können darin manipuliert, ergänzt oder gelöscht werden.  Der eigentliche Vorteil ist nun, dass ich dieses Objekt an eine Funktion weitergeben kann:

CREATE FUNCTION dbo.testFunc(@tmpDataTable tmpDataType READONLY)
RETURNS TABLE
RETURN (
 -- return the data or do somethin else ...
 SELECT data FROM @tmpDataTable
);
GO
DECLARE @tmpDataTable As tmpDataType;
INSERT INTO @tmpDataTable
SELECT data FROM dbo.data;
SELECT * FROM dbo.testFunc(@tmpDataTable);
GO

 

Das war’s schon!

 

 

Vollständiges Demo-Script (Verwendung auf eigene Gefahr!)

-- create a tmp.-DB
CREATE DATABASE TYPETEST;
GO
USE TYPETEST;
GO
-- create a demo table
CREATE TABLE dbo.data (
 id INT not null primary key identity(1,1)
 , data VARCHAR(255));
GO
-- insert demo data
INSERT INTO dbo.data (data) VALUES ('VAL1'), ('VAL2'), ('VAL3');
GO
-- create a temp table
CREATE TABLE ##tmpData (data VARCHAR(255));
GO
-- fill the demo table
INSERT INTO ##tmpData
SELECT data FROM dbo.data;
GO
-- create a function to read the demo data from the temp table
--CREATE FUNCTION dbo.testFunc()
--RETURNS TABLE
--RETURN (
-- SELECT data FROM ##tmpData
--);
--GO
-- Does not work, in functions you do not have the option to read from temporaly tables ...
-- and now?!
-- but how can i move my temporally data to my function?
-- one option is to create a pyhsically table with the process id ... but do you need this for sure?!
-- NO!
-- create a user type table variable in the database (be carefull, this is in the database a global type!!
CREATE TYPE tmpDataType As Table (
 data VARCHAR(255));
GO
-- create a function with a parameter from the user defined typ
CREATE FUNCTION dbo.testFunc(@tmpDataTable tmpDataType READONLY)
RETURNS TABLE
RETURN (
 -- return the data or do somethin else ...
 SELECT data FROM @tmpDataTable
);
GO
-- create a object from our new type
DECLARE @tmpDataTable As tmpDataType;
-- fill our object with demo data
INSERT INTO @tmpDataTable
SELECT data FROM dbo.data;
-- execute our new function
SELECT * FROM dbo.testFunc(@tmpDataTable);
GO
-- clean up
DROP TABLE ##tmpData;
GO
USE tempdb;
GO
DROP DATABASE TYPETEST;

Stichtagsinformationen zu einem Datum

uhr(english Version below)

Durch einen Eintrag auf http://sqlmag.com/t-sql/cheat-sheet-calculating-important-dates habe ich mich inspirieren lassen, eine kleine Funktion aus den Statements zu erstellen, die zu einem Datum Informationen liefert. Ganz praktisch bei Reporting Services oder im PowerBI Umfeld, wenn man in einem Bericht verschiedenen Zeit Informationen zu den Daten anzeigen will, bspw. aus welcher Zeit-Periode abgefragt wurde wenn ein Report bspw. nur einen Stichtag aufnimmt, aber alle Daten aus dem aktuellen Jahr selektiert oder dem Quartal …

 

By an entry on http://sqlmag.com/t-sql/cheat-sheet-Calculating-important-dates I’ve inspired me to create a small function of the statements that delivers information to a date. Useful in Reporting Services or in PowerBI environment, if you will, in a report several times to display information about the data. For example, if a report only takes a date as a parameter, but selects all data from the current year or the last quarter …

Funktion / Function:

CREATE FUNCTION dbo.InformationForADate(@QualifyingDate DATETIME)
RETURNS TABLE 
RETURN (
	SELECT
	(SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) - 1 , '19000101')) AS [FIRST DAY OF LAST YEAR], 
	(SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate), '19000101')) AS [FIRST DAY OF This YEAR],
	(SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) + 1 , '19000101')) AS [FIRST DAY OF NEXT YEAR],
	(SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate), '19000101'))) AS [LAST DAY OF Last YEAR],
	(SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) + 1 , '19000101')))  AS [LAST DAY OF This YEAR],
	(SELECT DATEADD(d, -1, DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @QualifyingDate) + 2 , '19000101'))) AS [LAST DAY OF NEXT YEAR],
	(SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) - 1, '19000101')) AS [FIRST DAY Previous MONTH],
	(SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate), '19000101')) AS [FIRST DAY CURRENT MONTH],
	(SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) + 1, '19000101')) AS [FIRST DAY NEXT MONTH],
	(SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate), '19000101'))) AS [LAST DAY Previous MONTH],
	(SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) + 1, '19000101'))) AS [LAST DAY This MONTH],
	(SELECT DATEADD(D, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @QualifyingDate) + 2, '19000101'))) AS [LAST DAY NEXT MONTH],
	(SELECT DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate))) AS [Midnight Yesterday],
	(SELECT DATEADD(d, -0, DATEDIFF(d, 0, @QualifyingDate))) AS [Midnight Today],
	(SELECT DATEADD(d, 1, DATEDIFF(d, 0, @QualifyingDate))) AS [Midnight Tomorrow],
	(SELECT DATEADD(ss, (60*60*24)-1, DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate)))) AS [11:59:59 Yesterday],
	(SELECT DATEADD(hh, 12, DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate)))) AS [Noon Yesterday],
	(SELECT DATEADD(ms, (1000*60*60*24)-2, DATEADD(d, -1, DATEDIFF(d, 0, @QualifyingDate)))) AS [11:59:59.997 Yesterday]);

Abfrage / Query:

DECLARE @QualifyingDate DATETIME = GETDATE();
SELECT * FROM dbo.InformationForADate(@QualifyingDate)

Time Dimension

uhr

In diesem Artikel will ich kurz beschreiben wie man eine Zeit Dimension erstellt und in PowerPivot verwendet. Für eine Datums-Dimension siehe hier.

(English Verison)

Als erstes brauchen wir eine Zeit Tabelle:

CREATE TABLE [dbo].[Dim_Time]
(
[Time] TIME NOT NULL,
[am_pm] CHAR(2) NOT NULL,
CONSTRAINT [PK_Table] PRIMARY KEY ([Time])
)

Mit folgendem Script befallen wir die Tabelle:

DECLARE @time TIME = '00:00:01';
DECLARE @ampm CHAR(2);
DECLARE @Stop BIT = 0;

WHILE @Stop = 0
BEGIN
IF @time < '12:00:00'
BEGIN
SET @ampm = 'am';
END
ELSE
BEGIN
SET @ampm = 'pm';
END

INSERT INTO Dim_Time (Time, am_pm)
VALUES (@time, @ampm);

SET @time = DATEADD(ss, 1, @time);
IF @time = '23:59:59' SET @stop = 1;

END

Nun können wir die Tabelle mit Daten in PowerPivot (oder was auch immer ..) verwenden:

2014-07-27_15-53-28 2014-07-27_15-59-51

 

English Version

In this article I will describe, how to create and use a time dimension in a powerpivot model– how to create and populate a date dimension table take a look here.
First we must create the table with the time data:

CREATE TABLE [dbo].[Dim_Time]
(
[Time] TIME NOT NULL,
[am_pm] CHAR(2) NOT NULL,
CONSTRAINT [PK_Table] PRIMARY KEY ([Time])
)

Now we must populate the table with data:

DECLARE @time TIME = '00:00:01';
DECLARE @ampm CHAR(2);
DECLARE @Stop BIT = 0;

WHILE @Stop = 0
BEGIN
IF @time < '12:00:00'
BEGIN
SET @ampm = 'am';
END
ELSE
BEGIN
SET @ampm = 'pm';
END

INSERT INTO Dim_Time (Time, am_pm)
VALUES (@time, @ampm);

SET @time = DATEADD(ss, 1, @time);
IF @time = '23:59:59' SET @stop = 1;

END

Now we can use the data in PowerPivot (or something else):

2014-07-27_15-53-28 2014-07-27_15-59-51

 

Automatisiert Adressen in Geo-Daten umwandeln und die Entfernung zu einem Standort berechnen

WeltkugelImmer wieder habe ich das Problem, dass ich haufenweise Adressen habe und diese bspw. in einem Report (Reporting Services) verarbeiten muss. D.h. ich brauche Geo-Daten und will diese natürlich nicht Datensatz für Datensatz in Längen- und Breitengrad übersetzen. In diesem Artikel will ich kurz beschreiben, wie eine CSV Datei mit Adressen automatisch mit den Google Webdiensten in Längen- und Breitengrad übersetzt wird und diese Daten dann im SQL Server wiederverwendet werden können.

Beispiel: Eine CSV Datei mit verschiedenen Imbiss Buden aus Koblenz (via den Gelben Seiten abgefragt):

2014-06-21_15-17-21

Via PHP wurde nun die Datei zeilenweise ausgelesen und die Adresse wurde an Google gesendet:2014-06-21_15-20-19

Die Ausgabe des Scripts ergibt folgendes:2014-06-21_15-22-20

Diese Daten können nun super per SSIS in die Datenbank eingelesen werden – natürlich kann man auch gleich die Daten in den SQL Server, bzw. die Datenbank schreiben. Die Tabelle dazu:
2014-06-21_15-24-48

Und nun brauchen wir noch eine Tabelle wo wir unseren eigenen Standort reinschreiben:
2014-06-21_15-25-29

Nun haben wir eine Tabelle in der die Adressen sind und auch eine Tabelle wo unser Standort enthalten ist. Über je ein Update Statement lassen wir gleich das GEO-Feld in den beiden Tabellen füllen (aus Längen- und Breitengrad) und über die STDistance Funktion können wir dann die jeweilige Entfernung zwischen dem Standort und der jeweiligen Adresse finden – so finden wir den nächsten Imbiss :-)

UPDATE dbo.Orte SET geo = GEOGRAPHY::STPointFromText(‚POINT(‚ + CONVERT(VARCHAR, laengengrad) + ‚ ‚ + CONVERT(VARCHAR, breitengrad) + ‚)‘,4326)

UPDATE meinStandort SET geo = GEOGRAPHY::STPointFromText(‚POINT(‚ + CONVERT(VARCHAR, laengengrad) + ‚ ‚ + CONVERT(VARCHAR, breitengrad) + ‚)‘,4326)

SELECT name, orte.adresse, orte.geo.STDistance(ms.geo) As [Entfernung In Meter] FROM dbo.Orte CROSS JOIN meinStandort ms

Visual Studio Online mit SSDT 2013 – BI Projekte Online verwalten

Bild

vs13onlineLange habe ich gewartet damit ich endlich Visual Studio 2013 ONLINE mit den Data Tools verwenden kann. Endlich gibt es auch wieder ein SSDT 2013 Express zum Download. Damit ist die Kompatibilität gewährleistet. In diesem Beitrag will ich kurz und knapp beschreiben, wie man das SSDT für BI Projekte und SQL Server Datenbank Projekte einrichtet und nutzt.

Der Sinn und Zweck für mich ist darin, Projekte von überall bearbeiten und verwalten zu können, ob beim Kunden, zuhause oder im Büro und das alles im Fokus der Business Intelligence- und Datenbank Projekte. Die durchaus guten Features der Quellcode Verwaltung sind bei mir weniger im Fokus und werden nicht weiter benutzt. Kurz und knapp: Ich brauche eine Online Quellcode Ablage von Projekten die mit dem SSDT erstellt werden ;-)

Folgende Schritte sind notwendig um das Ziel zu erreichen:

Als erstes brauchen wir ein Konto bei Visual Studio Online http://www.visualstudio.com/de-de/products/visual-studio-online-overview-vs.aspx Hier habe ich den Online Basic Plan gewählt – dieser ist für 5 Benutzer kostenlos

Nach dem Registrieren bei Visual Studio online brauchen wir ein Projekt mit dem wir arbeiten können:
2014-05-24_16-29-41

 

Nun muss der Download der SQL Server Data Tools (SSDT BI) für Visual Studio 2013 durchgeführt werden http://www.microsoft.com/en-us/download/details.aspx?id=42313
Nach der Installation ist die Shell mit den BI Projektvorlagen verfügbar

Nun brauchen wir noch die Shell mit den Projektvorlagen um SQL Server Datenbank Projekte erstellen zu können. Hierfür muss man ein Visual Studio Express installieren. Hier nehme ich die Web Edition: http://www.visualstudio.com/downloads/download-visual-studio-vs – das ist m.E. völlig unsinnig da es bei 2012 noch die Vorlage als eigenständiges Setup zum Download gab – so habe ich nicht nur die Data Tools installiert sondern ein VS Express Web was ich eigentlich gar nicht brauche, nur einige Referenzen und Tools die darin unbedingt integriert werden mussten …. Nun gut.

 

Nun öffnet man das Projekt über die Weboberfläche:
2014-05-24_17-57-10Hier startet nun das Visual Studio (SSDT) und man meldet sich mit dem Microsoft Konto an, so wird das Visual Studio mit der Quellcode Verwaltung von Visual Studio online verbunden. So können die Objekte ein und ausgecheckt werden und alle Funktionen die man normalerweise von einem TFS kennt, sind nun auch verfügbar – und das in einer Express Editionen und einem „TFS“ der Online für „lau“ verfügbar ist.

 

Nun können die Projekte in der Projektmappe erstellt werden:
2014-05-24_18-00-352014-05-24_18-02-51

 

 

 

 

 

 

 

 

 

Nun fügen wir noch ein weiteres Projekt der Mappe hinzu:
2014-05-24_18-05-42

Nun füge ich den Projekten noch jeweils ein paar Objekte hinzu wie Tabellen, Views aber auch im Reporting Projekt einen Bericht.
2014-05-24_18-14-25

 

 

 

 

 

 

 

Über einen Rechtsklick auf die Projektmappe checke ich nun die Projekte komplett ein
2014-05-24_18-15-06

Nun sind alle Projekte und Dateien in Visual Studio online eingecheckt und dort verfügbar – von dort aus kann ich entweder das Projekt wieder auschecken, weiter bearbeiten oder auch als ZIP downloaden um so eigene Versionen extern ablegen zu können oder diese in Umgebungen integrieren zu können, wo es ggf. kein Online Zugriff auf Visual Studio online gibt.

Fazit:
Mit Visual Studio online kann ich meine BI Projekte mit den Microsoft Tools online verwalten und verfügbar halten. Den Transfer mit USB Sticks, Mail etc. fällt somit flach und da ich mit den Express Versionen hier komplett auskomme, kann ich die Tools überall installieren ohne Lizenzen erwerben zu müssen. Mal schauen ob hier noch ein Haken kommt? :-)
 

Reporting Services: Weitergabe von „Multi Value“-Parametern

Immer wieder kommt es vor, dass man Parameter von einem Bericht an den anderen weitergeben möchte. Bei einem Parameter der lediglich einen Wert aufnimmt, ist das ziemlich simpel. Was aber, wenn der Parameter mehrere Werte (Multi Value Parameter) aufnehmen kann und auch alle diese Werte an einen weiteren Bericht weitergegeben werden sollen?

Hier die Lösung:

Ausgangslage ist ein Bericht, der 4 verschiedene Werte als Multi Value Parameter aufnehmen kann und diese im Bericht darstellt.

Dieser Parameter nutzt ein Dataset für die verfügbaren Werte:
2014-05-02_20-42-39

 

2014-05-02_20-42-55
Ergebnis:
2014-05-02_20-47-32

 

 

 

 

 

Die Ausgabe ist folgendermaßen realisiert.2014-05-02_20-47-51

Der Text hier wird verlinkt auf einen weiteren Bericht der das gleiche Dataset nutzt. Hier wird erwartet, dass der übergebene Parameter die Werte vor ausfüllt und aufnimmt, die vom aufrufenden Bericht übergeben werden. Also das hier in diesem Beispiel die Werte für Nord, Süd, Ost und West übergeben werden.

Das Textfeld für die Ausgabe wird nun verlinkt auf den anderen Bericht:
2014-05-02_20-52-00

Als Ausdruck für den Parameter ist folgender Code notwendig:

2014-05-02_20-52-52Nun werden die entsprechenden Werte aus dem Multi Value Parameter an den Unterbericht weitergegeben. An dem Unterbericht muss nicht geändert werden, lediglich muss er den Parameter aufnehmen und auch als Multi Value Parameter definiert werden.