MS SQL Server-Tabelle mit Pivot drehen (z.B. für Zeitreihenanalyse)

Problemfeldbeschreibung

Beim Arbeiten mit Daten im Microsoft SQL Server kommt man gelegentlich an den Punkt, an dem man sich das „Drehen“ von Tabellendaten als nützlich entpuppt. Anwendungsgebiete sind u.a. die Prognoserechnung, Zeitreihenanalyse oder einfach Matrizenmultiplikation.

Nehmen wir z.B. den folgenden Datensatz, er soll gedreht werden:

200901     1    2009    0    30
200902     2    2009    58    16
200903     3    2009    520    15
200906     6    2009    780    15
200809     9    2008    0    7
200909     9    2009    260    8
200810    10    2008    0    

Der unerfahrene Datenbankentwickler beginnt dann häufig mit der Implementierung von langsamen Cursorskripten á la

DECLARE @compcode varchar(255)
DECLARE @market varchar(255)
DECLARE @salesdiv varchar(255)
DECLARE @material varchar(255)
DECLARE @month int
DECLARE @year int
DECLARE @bruttoumsatz varchar(255)
DECLARE @amount varchar(255)
DECLARE @spalte varchar(255)

DECLARE mmg_cursor CURSOR FOR
SELECT compcode, market, salesdiv, material, month, year, bruttoumsatz, amount
FROM cube WHERE CAST(CAST(year as varchar)+(CASE WHEN month<10 THEN ('0'+CAST(month as varchar)) ELSE CAST(@month as varchar) END ) as int) >=200610

OPEN mmg_cursor

FETCH NEXT FROM mmg_cursor INTO @compcode,@market, @salesdiv, @material,
@month, @year, @bruttoumsatz, @amount

WHILE @@FETCH_STATUS = 0
BEGIN

SET @spalte=CAST(@year as varchar)+(CASE WHEN @month<10 THEN ('0'+CAST(@month as varchar)) ELSE CAST(@month as varchar) END ) EXEC('UPDATE hypercube SET ['+@spalte+']='+@amount+' WHERE [compcode]='''+@compcode+''' AND [salesdiv]='''+@salesdiv+''' AND [market]='''+@market+''' AND [material]='''+@material+''' ') FETCH NEXT FROM mmg_cursor INTO @compcode,@market, @salesdiv, @material, @month, @year, @bruttoumsatz, @amount END DEALLOCATE mmg_cursor [/javascript]

Ansatz

Es bedarf keines Benchmarks oder einer Messung um festzustellen, dass diese Methode sehr zeitaufwändig und unzeitgemäß ist. Microsoft rät von solchen Cursor-Skripten ab und rät von der Nutzung der PIVOT-Funktion, die an Performance und Handhabung kaum zu überbieten ist.

Lösung

Das folgende SQL-Skript dreht die Tabelle mit PIVOT (Die MS SQL Server-Datenbank muss hierzu mind. auf MSSQL2005-Kompatibilität gestellt werden.

declare @columns varchar(max)
declare @columnsoben varchar(max)
declare @count int
declare @max int
declare @current int
declare @sql nvarchar(max)
declare @t table(id int identity(1,1), datekey int)
insert into @t select distinct datekey from vglobe2 order by 1
set @columns = “
set @columnsoben = “
set @count = 1
set @max = (select max(id) from @t)
while (@count <= @max) begin set @current = (select datekey from @t where id = @count) set @columns = @columns + '[' + cast(@current as varchar(6)) +'], ' set @columnsOben = @columnsOben + '[' + cast(@current as varchar(6)) +'],' + '0 as [' + cast(@current as varchar(6)) +'stat],' set @count = @count+1 end set @columns = substring(@columns, 1, len(@columns)-1) -- letztes komma set @columnsOben = substring(@columnsOben, 1, len(@columnsOben)-1) -- letztes komma set @sql = 'SELECT Artikel, ' + @columnsoben + N' FROM (SELECT artikel, bruttoumsatz , datekey FROM vglobe2) p PIVOT ( sum(bruttoumsatz) FOR datekey in (' + @columns + N') ) AS pvt ORDER BY 1' print @sql exec sp_executesql @sql [/sql]

Kostenloses Tool um SAP Rollen zu vergleichen / SAP Role Compare

Problemfeldbeschreibung

In großen Unternehmen sind die einzelnen Businessabteilungen so organisiert, dass die IT-Schnittstelle über eine Person mit IT-Background realisiert wird, diese jedoch nicht zwangsläufig von der IT gestellt wird. Hierbei kann es sich um eine „ehrenamtliche“ Tätigkeit handeln, die nichts mit der Hauptaufgabe des IT-Koordinators zu tun hat.

Die Koordinatoren besitzen besondere Rechte für die Vergabe von Rollen in der Transaktion SU01, welche auf bestimmte Länder oder Anwendungen begrenzt wird und den jeweiligen Businessbereich abdeckt.

Bei einem großem unübersichtlichen Rollenaufkommen bleibt den IT-Koordinatoren nur der Vergleich von Rollen, um herauszufinden welche Berechtigungen jemand benötigt oder welche ihm weggenommen werden sollen.

Aufgabenstellungen

Die Sachbearbeiter kommen zu den IT-Koordinatoren und diese bekommen dann die folgenden Frage- und Aufgabenstellungen

  • Die Person x benötigt die gleichen Berechtigungen auf Transaktion y wie die Person z hat.
  • Die Person x benötigt zusätzlich (alte Rechte bleiben erhalten) alle Berechtigungen im Testsystem, die sie im Produktivsystem hat
  • Die Person x braucht zusätzlich (alte Rechte bleiben erhalten) die gleichen Berechtigungen wie Person z.
  • u.v.m….

Anhand der Vielfalt von Fragestellungen stellen sich jetzt dem IT-Koordinator die folgenden Fragen

  • Welche Rollen hat die Person z an der Transaktion y, die Person x nicht hat?
  • Welche Rollen hat die Person x im Testsystem, die sie nicht im Produktivsystem hat?
  • Welche Rollen hat Person x die Person z nicht hat?
  • u.v.m. …

Ansatz

Die einzige Möglichkeit die dem Koordinator bleibt ist der Vergleich von Rollen zu Transaktionen über die Transaktion SUIM (Infosystem). Damit lassen sich die o.g. Anwendungsfälle alleine nicht abdecken. Vielmehr bietet die Transaktion die Möglichkeit eine Liste von Rollen

  • … nach Transaktionszuordnung
  • … nach Benutzerzuordnung
  • u.v.m. …

abzufragen. Diese Rollen müssten „händisch“ verglichen werden. Bei kryptischen Rollennamen nach dem Prinzip „CA_XXXX_LE44_MB“ und einer Anzahl von 200 Rollen pro Person oder Transaktion, artet das für eine „ehrenamtliche“ Tätigkeit in viel Arbeit aus.

Lösung

Vorgehensweise

Um diesen Vorgang wesentlich effizienter und effektiver zu gestalten, kann man die kostenlose Web-Anwendung (Flash) „Sap Role Compare“ benutzen. Hierbei ist die Vorgehensweise die folgende.

1.) Transkation SUIM aufrufen
2.) Die gewünschte Liste (z.B. Rollen nach Transaktionszuordnung) wählen
3.) Benutzer/Transaktion auswählen und auf die Uhr mit dem grünen Häkchen links oben klicken
4.) Wichtig: In der Ergebnisliste mit der Scrollleiste rechts GANZ RUNTER scrollen (sonst kommen die Daten nur teilweise in die Zwischenablage)
5.) Auf die Kopfzeile der Spalte klicken, in der die Rollennamen stehen, so dass ALLE Rollennamen selektiert sind.
6.) Rechte Maustaste->kopieren und im ersten Feld links der Webanwendung (Rollen (x) ) mit rechte Maustaste->einfügen (jetzt hätten wir alle Rollen der Transaktion, falls nach Transaktionname ausgewählt wurde).
7.) Wieder in SUIM den Benutzer/ die Transaktion auswählen, mit der verglichen werden soll (wir hätten jetzt die Rollen der Transaktion im ersten Feld (Rollen (x)) und kopieren ins zweite Feld (Rollen (z) )  die Rollen des Benutzers)
8.) Wieder ganz runter scrollen (nie vergessen), Spalte markieren, Rechte Maustaste->Kopieren und in Rollen(z) einfügen
9.) Den Button mit dem GLEICH-Zeichen klicken

Ergebnis

Wir erhalten jetzt 3 Ergebnismengen:

1.) Die Rollen, die in Rollen (x) und in Rollen (z) gleich sind
2.) Die Rollen, die in Rollen (x) und NICHT in Rollen (z)  sind
3.) Die Rollen, die in Rollen (z) und NICHT in Rollen (x)  sind

Mit diesen Mengen lassen sich ALLE Anwendungsfälle abdecken, die nicht durch die Transaktion SUIM unterstützt werden.

Download

Die Flash-Anwendung lässt sich hier herunterladen (rechte Maustaste -> Ziel speichern unter) …

Massenimport von Daten über MS SQL Server Management Studio

Problem

Eine große CSV Datei soll in eine Tabelle importiert werden. Hierbei sollen nicht alle Felder importiert werden.

Ansatz

Die Verwendung des Kommandos BULK INSERT wurde von Microsoft für sehr große Dateien vorgesehen, um den SQL Server ohne die Verwendung von langsamen Cursor-Iterationen zu füllen

Lösung

Vorgehensweise

Erstellen einer Tabelle mit CREATE TABLE in die importiert wird
Zunächst erstellt man eine Tabelle in die importiert werden soll, hier wird eine fiktive Tabelle RAWBEIL erstellt.

CREATE TABLE [dbo].[rawbeil](
	[gruppe] [varchar](255) NULL,
	[bezeichnung] [varchar](255) NULL,
	[feldname] [varchar](255) NULL,
	[sapcolumn] [varchar](50) NULL,
	[saptable] [varchar](50) NULL
)

Erstellen einer IMPORT-Datei für die Feldzuweisung

Der RECORD-Tag muss den kompletten Aufbau der CSV-Datei enthalten; der ROW-Teil darf enthält die Tabellenspalten, in die importiert wird. FIELD ID wird auf COLUMN SOURCE gemappt. ROW/COLUMN SOURCE kann auch Lücken enthalten, wenn man Felder aus der CSV Datei weglassen möchte..

&lt;?xml version="1.0" ?>
&lt;BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	&lt;RECORD>
		&lt;FIELD ID="1" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255"/>
		&lt;FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		&lt;FIELD ID="3" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		&lt;FIELD ID="4" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		&lt;FIELD ID="5" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		&lt;FIELD ID="6" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		&lt;FIELD ID="7" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		&lt;FIELD ID="8" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		&lt;FIELD ID="9" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		&lt;FIELD ID="10" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		&lt;FIELD ID="11" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		&lt;FIELD ID="12" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		&lt;FIELD ID="13" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		&lt;FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\r\n" 
                  MAX_LENGTH="255" />
	&lt;/RECORD>
	&lt;ROW>
		&lt;COLUMN SOURCE="1" NAME="gruppe" xsi:type="SQLVARYCHAR" />
		&lt;COLUMN SOURCE="2" NAME="bezeichnung" xsi:type="SQLVARYCHAR" />
		&lt;COLUMN SOURCE="3" NAME="feldname" xsi:type="SQLVARYCHAR" />
		&lt;COLUMN SOURCE="4" NAME="sapcolumn" xsi:type="SQLVARYCHAR" />
		&lt;COLUMN SOURCE="5" NAME="saptable" xsi:type="SQLVARYCHAR" />
	&lt;/ROW>
&lt;/BCPFORMAT>

SQL Anweisung

CODEPAGE wird verwendet um das Umlaute-Problem zu beheben, FIRSTROW ist die erste Spalte ab der aus CSV importiert wird (meistens will man den Header ausschließen).

DECLARE @sql nvarchar(4000)
SET @sql =
'BULK INSERT rawbeil FROM ''D:\web\karpbjde\import\db.csv''
WITH (
FORMATFILE = ''D:\web\karpbjde\import\beilmade.fmt'',
CODEPAGE=''raw'',
FIRSTROW = 2
)'
EXEC(@sql)

BULK INSERT mit Typecast in einen bestimmten Datentyp

/* Bulk-Import von einer .CSV-Datei in eine Tabelle incl. der Datentypen
* es werden Zahlen im Vormat
* xxx,yy
* xxx.yy
* -xxx,yy
* -xxx.yy
* xxx,yy-
* xxx.yy
* unterstützt. Diese werden als Money-Datentyp convertiert
* Bulk-Import
*
* gewidmet an Björn K.von Nico Gerbig
*/ 

-- Zuerst als Varchar lesen, dann richtig
create table ordersbulk (col1 varchar(50), col2 varchar(50), col3 varchar(50));
create table ordersbulk2 (col1 varchar(50), col2 money, col3 money) 

-- Eintragen in Tabelle mit Varchars
BULK INSERT OrdersBulk
    FROM 'c:\test.csv.txt'
    WITH
    (
        --FIRSTROW = 2,
        FIELDTERMINATOR = ';',
        ROWTERMINATOR = '\n'
    ) 

-- und Kopieren in die Tabelle mit den richtigen Datentypen
insert into ordersbulk2 (col1, col2, col3)
    select col1,
          dbo.ConvertVarcharToMoney(col2),
          dbo.ConvertVarcharToMoney(col3)
    from ordersbulk
go
-- testen
select * from ordersbulk2
go 

-- dafür notwendige funktion (c)  -=e!ninio=-
create function ConvertVarcharToMoney(@abc varchar(255))
    returns money
as begin
    declare @invert money;
    declare @index int;
    declare @newabc money;
    set @invert = 1;
    set @abc = Replace(@abc,',','.')
    set @abc = Replace(@abc,' ','')
    set @index = CHARINDEX('-',@abc)
    if @index &gt; 0
    begin
        set @invert = -1;
        set @abc = Replace(@abc, '-', '')
    end
    set @newabc = CAST(@abc as money) * @invert
    return @newabc
end
go 

-- Test für die Funktion selbst
select dbo.ConvertVarcharToMoney('13,41')
select dbo.ConvertVarcharToMoney('13.41')
select dbo.ConvertVarcharToMoney('-13,41')
select dbo.ConvertVarcharToMoney('13,41-')
select dbo.ConvertVarcharToMoney('- 13,41')
select dbo.ConvertVarcharToMoney('13,41 -')