Wir benutzen hier bewusst einen getrennten Ansatz, erst die anzeigenden Datumswerte zu ermitteln und dann dazu die Attribute zu erzeugen. Dadurch erreichen wir die h\u00f6chst m\u00f6gliche Flexibilit\u00e4t. Man k\u00f6nnte zum Beispiel folgendes machen: <\/p>\n\n\n\n
- Wir nehmen alle Datumswerte vom Minimum der auftretenden Werte bis zum Maximum<\/li>
- Da aber Fehleingaben enthalten sind, und wir nicht alle Datumswerte vom 1.1.2000 bis 1.1.3000 in unserer Dimension haben wollen, nehmen wir nur die Datumswerte vom Minimum bis zum Ende n\u00e4chsten Jahres und alle weiteren DISTINCTen Werte, die vorkommen.<\/li><\/ul>\n\n\n\n
Wir gehen so vor:<\/p>\n\n\n\n
Als erstes legen wir einen Type an, der eine Liste von Datumswerten halten kann:<\/p>\n\n\n\n
CREATE TYPE [dbo].[typ_Datumswerte] AS TABLE(\n\t[datum] [date] NOT NULL,\n\tPRIMARY KEY CLUSTERED \n(\n\t[datum] ASC\n)WITH (IGNORE_DUP_KEY = OFF)\n)\nGO<\/pre>\n\n\n\n
In eine Variable dieses Datentyps k\u00f6nnen wir nun beliebige Datumswerte speichern. Wenn man zum Beispiel eine distinkte Menge an Datumswerten aus einer Faktentabelle speichern will, kann man das so machen:<\/p>\n\n\n\n
DECLARE @d AS typ_Datumswerte\nINSERT INTO @d SELECT DISTINCT Datum FROM Fakt_Auftragsbestand<\/pre>\n\n\n\n
Meistens ben\u00f6tigt man aber einen ganzen Zeitraum an allen m\u00f6glichen Datumswerten. Daf\u00fcr habe ich eine triviale Stored Function geschrieben:<\/p>\n\n\n\n
-- =============================================\n-- Author:\t\tMartin Cremer\n-- Create date: 07.07.2015\n-- Description:\tliefert alle Tage eines bestimmten Zeitraums\n-- =============================================\nCREATE FUNCTION [dbo].[f_alle_Datumswerte] \n(\n\t@von date, \n\t@bis date\n)\nRETURNS \n@tab TABLE \n(\n\tdatum date\t \n)\nAS\nBEGIN\n\twhile @von <= @bis\n\tbegin\n\t\tinsert into @tab select @von\n\n\t\tset @von = dateadd(d, 1, @von)\n\tend\n\t\n\tRETURN \nEND\nGO<\/pre>\n\n\n\n
Um einen ganzen Zeitraum nun in eine Variable des oben definierten Typs zu schreiben, geht man so vor:<\/p>\n\n\n\n
DECLARE @d AS typ_Datumswerte\nINSERT INTO @d \nSELECT * FROM dbo.f_alle_Datumswerte( \n\t(SELECT min(Datum) FROM Fakt_Auftragsbestand),\n\t(SELECT max(Datum) FROM Fakt_Auftragsbestand)\n)<\/pre>\n\n\n\n
Damit h\u00e4tten wir den ersten Punkt erledigt. Kommen wir nun dazu, alle ben\u00f6tigten Attribute zu ermitteln. Daf\u00fcr gibt es ein paar Hilfsfunktionen, deren 1. Version ich sogar am 1.1.2009 hier schon im Blog ver\u00f6ffentlicht hatte, und eine Funktion, die alles zusammenfasst. Zun\u00e4chst die Hilfsfunktionen (auf die heutige Zeit angepasst, in der der SQL Server nativ die deutsche ISO-Woche ermitteln kann):<\/p>\n\n\n\n
-- =============================================\n-- Author:\t\tMartin Cremer\n-- Create date: 16.8.2006\n-- Description:\termittelt aus dem Datum den Wochentag (unabh\u00e4ngig von SET DATEFIRST): 1=Montag - 7=Sonntag\n-- =============================================\nCREATE FUNCTION [dbo].[getWochentag] \n(\n\t@dat date\n)\nRETURNS int\nAS\nBEGIN\n\tDECLARE @Result int\n\tSELECT @Result = (datepart(dw, @dat) - 1 + @@datefirst -1) % 7 +1\n\tRETURN @Result\nEND\nGO\nCREATE FUNCTION [dbo].[getKW_Jahr](@h as date)\nreturns int\nas\nbegin\n return case \n\t\twhen datepart(isowk, @h)>50 and month(@h) = 1 then year(@h)-1 \n\t\twhen datepart(isowk, @h)=1 and month(@h) = 12 then year(@h)+1\n\t\telse year(@h)\n\tend \nend\nGO\nCREATE FUNCTION [dbo].[getKW_Woche](@h as date)\nreturns int\nas\nbegin\n\treturn datepart(isowk, @h)\nend\nGO<\/pre>\n\n\n\n
Die geradlinige zusammenfassende Funtkion sieht dann so aus:<\/p>\n\n\n\n
-- =============================================\n-- Author:\t\tMartin Cremer\n-- Create date: 07.07.2015\n-- Description:\tliefert zu den \u00fcbergebenen Datumswerten alle Attribute\n-- =============================================\nCREATE FUNCTION [dbo].[f_Datumsattribute] \n(\n\t@datumswerte as dbo.typ_Datumswerte readonly\n)\nRETURNS \n@Ergebnis TABLE \n(\n\tDatum date NOT NULL, \n\t[KW_ID] INT NOT NULL, \n\t[KW_Jahr] SMALLINT NOT NULL,\n\t[KW] NVARCHAR(10) NOT NULL,\n\t[KW_Nr] SMALLINT NOT NULL,\n\t[Monat_ID] INT NOT NULL,\n\t[Monat_OhneJahr_ID] TINYINT NOT NULL,\n\t[Monat] NVARCHAR(8) NOT NULL,\n\t[Monat_OhneJahr] NVARCHAR(3) NOT NULL,\n\t[Jahr] SMALLINT NOT NULL,\n\t[Quartal_ID] SMALLINT NOT NULL,\n\t[Quartal_OhneJahr_ID] TINYINT NOT NULL,\n\t[Quartal_OhneJahr] NVARCHAR(50) NOT NULL,\n\t[Quartal] NVARCHAR(50) NOT NULL,\n\t[Wochentag_ID] int NOT NULL,\n\t[Wochentag] nvarchar(20) NOT NULL,\n\t[Wochentag_K\u00fcrzel] nvarchar(2) NOT NULL\n)\nAS\nBEGIN\n\tINSERT INTO @Ergebnis\n\t(Datum, [KW_ID], [KW_Jahr], [KW], [KW_Nr], [Monat_ID], [Monat_OhneJahr_ID], [Monat], [Monat_OhneJahr], [Jahr], \n\t\t [Quartal_ID], [Quartal_OhneJahr_ID], [Quartal_OhneJahr], [Quartal], [Wochentag_ID], [Wochentag], [Wochentag_K\u00fcrzel])\n\tSELECT \n\t\t x.datum,\n\t\t x.KW_Jahr * 100 + x.KW \/* 201501 f\u00fcr KW 01\/2015*\/,\n\t\t x.KW_Jahr \/*2015*\/,\n\t\t 'KW ' + RIGHT('0' + CONVERT(NVARCHAR(2), x.KW), 2) + '\/' + CONVERT(NVARCHAR(4), x.KW_Jahr) \/* KW 01\/2015*\/,\n\t\t x.KW \/*1*\/,\n\t\t x.jahr * 100 + x.Monat \/* 201501 f\u00fcr Jan 2015 *\/,\n\t\t x.monat \/* 1 *\/,\n\t\t monate.monatsname + ' ' + CONVERT(NVARCHAR(4), x.jahr) \/* Jan 2015 *\/,\n\t\t monate.monatsname \/* Jan *\/,\n\t\t x.jahr,\n\t\t x.jahr * 10 + x.quartal \/* 20151 f\u00fcr Q1 2015 *\/,\n\t\t x.quartal \/* 1 *\/,\n\t\t 'Q' + CONVERT(NVARCHAR(1), x.quartal) \/* Q1 *\/,\n\t\t 'Q' + CONVERT(NVARCHAR(1), x.quartal) + ' ' + CONVERT(NVARCHAR(4), x.jahr),\n\t\t x.wochentagID,\n\t\t Wochentage.Wochentagname, \n\t\t Wochentage.Wochentagkurz\n\t\tFROM\n\t\t\t(SELECT [dbo].[getKW_Jahr](d.datum) AS KW_Jahr, [dbo].[getKW_Woche](d.datum) AS KW, \n\t\t\tMONTH(d.datum) AS monat,\n\t\t\tdatepart(QUARTER, d.datum) AS quartal,\n\t\t\tyear(d.datum) AS jahr,\n\t\t\t[dbo].[getWochentag](d.datum) as wochentagID,\n\t\t\td.datum\n\t\t\tFROM @datumswerte as d) AS x\n\t\tLEFT JOIN \n\t\t\t(SELECT 1 AS monat, 'Jan' AS Monatsname UNION ALL\n\t\t\t SELECT 2, 'Feb' UNION ALL\n\t\t\t SELECT 3, 'M\u00e4r' UNION ALL\n\t\t\t SELECT 4, 'Apr' UNION ALL\n\t\t\t SELECT 5, 'Mai' UNION ALL\n\t\t\t SELECT 6, 'Jun' UNION ALL\n\t\t\t SELECT 7, 'Jul' UNION ALL\n\t\t\t SELECT 8, 'Aug' UNION ALL\n\t\t\t SELECT 9, 'Sep' UNION ALL\n\t\t\t SELECT 10, 'Okt' UNION ALL\n\t\t\t SELECT 11, 'Nov' UNION ALL\n\t\t\t SELECT 12, 'Dez' ) AS monate\n\t\tON x.monat = monate.monat\n\t\tLEFT JOIN \n\t\t\t(SELECT 1 as WochentagID, 'Montag' Wochentagname, 'Mo' Wochentagkurz UNION ALL\n\t\t\t SELECT 2, 'Dienstag', 'Di' UNION ALL\n\t\t\t SELECT 3, 'Mittwoch', 'Mi' UNION ALL\n\t\t\t SELECT 4, 'Donnerstag', 'Do' UNION ALL\n\t\t\t SELECT 5, 'Freitag', 'Fr' UNION ALL\n\t\t\t SELECT 6, 'Samstag', 'Sa' UNION ALL\n\t\t\t SELECT 7, 'Sonntag', 'So' ) as Wochentage\n\t\tON x.wochentagID = Wochentage.WochentagID\n\tRETURN \nEND\nGO<\/pre>\n\n\n\nSomit kann man nun obige Ermittlung der gew\u00fcnschten Datumswerte um die Ausgabe der Attribute erweitern, also zum Beispiel so:<\/p>\n\n\n\n
declare @tage as [dbo].[typ_Datumswerte]\nINSERT INTO @tage select * from dbo.f_alle_Datumswerte(convert(date, '1.1.2020', 104), convert(date, '31.12.' + convert(nvarchar(4), year(getdate())), 104))\nselect * from dbo.f_Datumsattribute(@tage)<\/pre>\n\n\n\n
Das Ergebnis sieht dann so aus:<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.csopro.de\/biblog\/wp-content\/uploads\/2020\/08\/grafik-1024x272.png\")
<\/a>Ergebnis Datumswerte<\/figcaption><\/figure>\n\n\n\n Nun m\u00fcssen wir dieses SQL nur noch im SSAS ausf\u00fchren lassen. Seit viele Neuerungen aus PowerBI in das Produkt SSAS einflie\u00dfen, hat sich auch die Art und Weise ge\u00e4ndert, wie man das Ergebnis von SQL-Statements im SSAS einbinden kann. Fr\u00fcher war es ja ganz einfach ein SQL-Statement anstelle einer Tabelle (bzw. View) zu verwenden, heute muss man wie folgt vorgehen [Die Idee dazu fand ich in Chris Webbs sehr gutem BI-Blog (hier<\/a>).]:<\/p>\n\n\n\n
Wenn man in Visual Studio eine neue Tabelle hinzuf\u00fcgt, wird im Hintergrund M-Code erzeugt, der in etwa so aussieht:<\/p>\n\n\n\n
let\n Source = #\"SQL\/<host>;<Datenbank>\",\n meineTabelle = Source{[Schema=\"dbo\",Item=\"Beispiel\"]}[Data]\nin\n meineTabelle<\/pre>\n\n\n\n\u00dcber die Properties der Tabelle > Quelldaten > (Zum Bearbeiten klicken) kann man diesen Code einsehen.<\/p>\n\n\n\n
Zu verstehen ist der Code ja ganz leicht: \u00dcber Source wird die SQL-Connection auf dem Host <host> und Datenbank <Datenbank> ge\u00f6ffnet. Daraus wird im obigen Beispiel die Tabelle dbo.Beispiel geladen.<\/p>\n\n\n\n
Wenn wir nun obiges SQL ausf\u00fchren wollen, f\u00fcgen wir zun\u00e4chst auf den normalen Weg im UI eine neue Tabelle hinzu (welche ist vollkommen egal). Danach bearbeiten wir dieses M-Statement zu<\/p>\n\n\n\n
let\n Source = #\"SQL\/<Host>;<Datenbank>\",\n Entlassungsdatum = Value.NativeQuery(\n Source,\n \"declare @tage as [dbo].[typ_Datumswerte]\nINSERT INTO @tage select * from dbo.f_alle_Datumswerte(convert(date, '1.1.2020', 104), convert(date, '31.12.' + convert(nvarchar(4), year(getdate())), 104))\nselect * from dbo.f_Datumsattribute(@tage)\"\n )\nin\n Entlassungsdatum<\/pre>\n\n\n\n
Entscheidend ist also die \u00c4nderung von Source{}[Data] zu Value.NativeQuery().<\/p>\n","protected":false},"excerpt":{"rendered":"
In meinen Projekten kommt es nat\u00fcrlich oft vor, dass man Datumsdimensionen ben\u00f6tigt. Dazu verwende ich Stored Functions im SQL Server, um die Datumswerte mit allen gew\u00fcnschten Attributen (wie Wochentag, Monat, Kalenderwoche, etc.) an den Analysis Services-Dienst weiter zu geben. Ich will das hier beschreiben, weil seit SSAS 2017 der Aufruf von Stored Functions gar nicht … Datumsdimension in SSAS (ab 2016) \/ Azure<\/span> weiterlesen
![\"\"](\"https:\/\/www.csopro.de\/biblog\/wp-content\/uploads\/2020\/08\/grafik.png\")
<\/a>