{"id":434,"date":"2014-07-27T16:21:03","date_gmt":"2014-07-27T14:21:03","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=434"},"modified":"2014-07-27T16:27:15","modified_gmt":"2014-07-27T14:27:15","slug":"time-dimension","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=434","title":{"rendered":"Time Dimension"},"content":{"rendered":"

\"uhr\"<\/a><\/p>\n

In diesem Artikel will ich kurz beschreiben wie man eine Zeit Dimension erstellt und in PowerPivot verwendet. F\u00fcr eine Datums-Dimension siehe hier<\/a>.<\/p>\n

(English Verison)<\/a><\/strong><\/em><\/p>\n

Als erstes brauchen wir eine Zeit Tabelle:<\/p>\n

CREATE TABLE [dbo].[Dim_Time]\r\n(\r\n[Time] TIME NOT NULL,\r\n[am_pm] CHAR(2) NOT NULL,\r\nCONSTRAINT [PK_Table] PRIMARY KEY ([Time])\r\n)<\/pre>\n
Mit folgendem Script befallen wir die Tabelle:<\/p>\n
DECLARE @time TIME = '00:00:01';\r\nDECLARE @ampm CHAR(2);\r\nDECLARE @Stop BIT = 0;\r\n\r\nWHILE @Stop = 0\r\nBEGIN\r\nIF @time < '12:00:00'\r\nBEGIN\r\nSET @ampm = 'am';\r\nEND\r\nELSE\r\nBEGIN\r\nSET @ampm = 'pm';\r\nEND\r\n\r\nINSERT INTO Dim_Time (Time, am_pm)\r\nVALUES (@time, @ampm);\r\n\r\nSET @time = DATEADD(ss, 1, @time);\r\nIF @time = '23:59:59' SET @stop = 1;\r\n\r\nEND<\/pre>\n
Nun k\u00f6nnen wir die Tabelle mit Daten in PowerPivot (oder was auch immer ..) verwenden:<\/p>\n
\"2014-07-27_15-53-28\"<\/a> \"2014-07-27_15-59-51\"<\/a><\/p>\n
 <\/p>\n
English Version<\/b><\/em><\/span><\/a><\/p>\n
In this article I will describe, how to create and use a time dimension in a powerpivot model\u2013 how to create and populate a date dimension table take a look here<\/a>.
\nFirst we must create the table with the time data:<\/p>\n
CREATE TABLE [dbo].[Dim_Time]\r\n(\r\n[Time] TIME NOT NULL,\r\n[am_pm] CHAR(2) NOT NULL,\r\nCONSTRAINT [PK_Table] PRIMARY KEY ([Time])\r\n)<\/pre>\n
Now we must populate the table with data:<\/p>\n
DECLARE @time TIME = '00:00:01';\r\nDECLARE @ampm CHAR(2);\r\nDECLARE @Stop BIT = 0;\r\n\r\nWHILE @Stop = 0\r\nBEGIN\r\nIF @time < '12:00:00'\r\nBEGIN\r\nSET @ampm = 'am';\r\nEND\r\nELSE\r\nBEGIN\r\nSET @ampm = 'pm';\r\nEND\r\n\r\nINSERT INTO Dim_Time (Time, am_pm)\r\nVALUES (@time, @ampm);\r\n\r\nSET @time = DATEADD(ss, 1, @time);\r\nIF @time = '23:59:59' SET @stop = 1;\r\n\r\nEND<\/pre>\n
Now we can use the data in PowerPivot (or something else):<\/p>\n
\"2014-07-27_15-53-28\"<\/a> \"2014-07-27_15-59-51\"<\/a><\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
In diesem Artikel will ich kurz beschreiben wie man eine Zeit Dimension erstellt und in PowerPivot verwendet. F\u00fcr 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] … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":435,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[30,31,26,37,3],"tags":[44,39],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/434"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=434"}],"version-history":[{"count":4,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/434\/revisions"}],"predecessor-version":[{"id":441,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/434\/revisions\/441"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/media\/435"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=434"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=434"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=434"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}