支持分钟时间段的Power BI和SSAS表格模型中的时间维度

2018-05-23 12_58_48-Symbols(在Visio中打开).vsdx-Visio Professional

日期维度已在Internet上进行了很多讨论,您可以在此找到很多有价值的文章。但是,如果您需要在时间级别上分析数据怎么办?客户需要在分钟级别分析其数据。这意味着事实表的粒度将处于分钟级别。因此,如果他们以秒为单位将数据存储在事务数据库中,那么我们需要将该数据聚合为分钟级。我不想去那里,只是要记住,事实表的粒度是您一开始就必须考虑的问题。在大多数情况下,即使不是所有情况,最好还是有一个单独的时间维度。然后,您需要在事实表中有一个TimeID或“时间”列,以便能够在“时间”维度和事实表之间创建关系。在本文中,我向您展示了两种在Power BI中创建时间维度的方法:

  • 使用DAX创建时间维度
  • 使用Power Query创建时间维度(M)

另外,您可以在SQL Server等源系统中处理“时间”维度。继续阅读,你’ll找到一个T-SQL代码作为补充。

我在这里说明的技术也可以在SSAS表格模型和Azure分析服务中完成。

要求:

要遵循构建测试模型的步骤,您需要:

  • Power BI桌面:从此处下载最新版本
  • 包含时间或日期时间的示例事实表。我修改了AdventureWorksDW的FactInternetSales,并使其可以Excel格式下载(在文章底部找到下载链接)

这个怎么运作

首先,您需要查看“ FactInternetSales_withTime.xlsx”文件的表结构。

如您所见,该表包含DateTime格式的“ OrderDateTime”列。我们需要做的是将该列拆分为两列,一列保存“ OrderDate”数据,另一列保存“ OrderTime”数据。然后,您可以使用DAX或Power Query(M)创建“时间”维度,或者如果喜欢,也可以同时创建两者。然后,您将在“时间”维和事实表之间创建一个关系。

开始吧。

  • 打开Power BI桌面
  • 从Excel获取数据并从“ FactInternetSales_WithTime” 电子表格文件加载数据
  • 点击“编辑查询”
  • 在查询编辑器页面中,单击“ FactInternetSales_WithTime”
  • 滚动到表格的最后,找到“ OrderDateTime”列。如您所见,数据类型为DateTime
  • 单击“添加列”选项卡,然后单击“自定义列”以添加新列。我们将添加“ OrderDate”列
  • 输入“ OrderDate”作为“新列名”
  • 键入以下超级查询功能以获取OrderDateTime的日期部分,然后单击“确定”。
=Date.From([OrderDateTime])

 

  • 现在,使用相同的方法添加另一列,并使用以下Power Query函数将其命名为“ OrderTime”
=Time.From([OrderDateTime])

 

  • 现在我们需要将新列的数据类型分别转换为日期和时间。为此,请选择两个列,然后从“转换”选项卡中单击“检测数据类型”

  • 关& Apply

在接下来的步骤中,我们使用DAX和Power Query(M)创建一个时间维度。然后,我们在“ FactInternetSales_WithTime”和“时间”维度之间创建一个关系。

达克斯的时间维度

如果您愿意使用DAX创建“时间”维度,请执行以下操作:

  • 在Power BI Desktop中,从功能区的“建模”选项卡中单击“新建表”
  • 复制并粘贴以下DAX代码,然后按Enter
Time in 达克斯 =
SELECTCOLUMNS (
ADDCOLUMNS (
GENERATESERIES ( 1, 1440, 1 )
, "时间值", 时间  ( 0, [Value], 0 )
)
, "ID", [Value]
, "Time", [TimeValue]
, "Hour", HOUR ( [TimeValue] )
, "Minute", MINUTE ( [TimeValue] )
, "5 Min Lower Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 5 ), 1 ) * 5, 0 ), "hh:mm:ss")
, "15 Min Lower Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 15 ), 1 ) * 15, 0 ), "hh:mm:ss")
, "30 Min Lower Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 30 ), 1 ) * 30, 0 ), "hh:mm:ss")
, "45 Min Lower Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 45 ), 1 ) * 45, 0 ), "hh:mm:ss")
, "60 Min Lower Band", FORMAT (TIME ( 0, FLOOR ( DIVIDE ( [Value], 60 ), 1 ) * 60, 0 ), "hh:mm:ss")
, "5 Min Upper Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 5 ), 1 ) * 5, 0 ), "hh:mm:ss")
, "15 Min Upper Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 15 ), 1 ) * 15, 0 ), "hh:mm:ss")
, "30 Min Upper Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 30 ), 1 ) * 30, 0 ), "hh:mm:ss")
, "45 Min Upper Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 45 ), 1 ) * 45, 0 ), "hh:mm:ss")
, "60 Min Upper Band", FORMAT (TIME ( 0, CEILING ( DIVIDE ( [Value], 60 ), 1 ) * 60, 0 ), "hh:mm:ss")
)

上面的代码根据1到1440之间的数字列表(间隔为1)创建一个表。该整数显示每天的分钟数。因此,如果您希望将时间维度设置为第二级,则需要创建一个从1到86,400的秒数列表。

单击“数据”选项卡以查看数据。如果您查看“ Lower Band”和“ Upper Band”列,则会注意到“ Lower Band”列的值从0开始,而“ Upper Band”列的值以波段号开始。当客户希望从0开始时,我创建了这两个列以涵盖不同的场景,然后您只需删除“ Upper Band”列即可,反之亦然。

您可能已经注意到“时间”列的数据类型为DateTime,这是不正确的。要解决此问题,只需单击“时间”列,然后将数据类型从“建模”选项卡更改为“时间”

为了确保添加到视觉效果中的时间以正确的顺序显示,我将格式更改为“ HH:mm:ss”。

您需要对所有其他时间列执行相同的操作。结果应类似于以下屏幕截图:

现在,您可以通过将事实表中的“ OrderTime”连接到构成“时间”维度的“时间”列,从而在“时间”维度和“ FactInternetSales_WithTime”之间建立关系。

使用功率查询创建时间维度(M)

您只需要在“查询编辑器”中创建一个空白查询,然后复制/粘贴以下Power Query代码。

let
Source = Table.FromList({1..1440}, Splitter.SplitByNothing()),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
#"Time Column Added" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0,0,[ID],0))),
#"5 Min Lower Band Added" = Table.AddColumn(#"Time Column Added", "5 Min Lower Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/5) * 5, 0))),
#"15 Min Lower Band Added" = Table.AddColumn(#"5 Min Lower Band Added", "15 Min Lower Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/15) * 15, 0))),
#"30 Min Lower Band Added" = Table.AddColumn(#"15 Min Lower Band Added", "30 Min Lower Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/30) * 30, 0))),
#"45 Min Lower Band Added" = Table.AddColumn(#"30 Min Lower Band Added", "45 Min Lower Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/45) * 45, 0))),
#"60 Min Lower Band Added" = Table.AddColumn(#"45 Min Lower Band Added", "60 Min Lower Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundDown([ID]/60) * 60, 0))),
#"5 Min Upper Band Added" = Table.AddColumn(#"60 Min Lower Band Added", "5 Min Upper Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/5) * 5, 0))),
#"15 Min Upper Band Added" = Table.AddColumn(#"5 Min Upper Band Added", "15 Min Upper Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/15) * 15, 0))),
#"30 Min Upper Band Added" = Table.AddColumn(#"15 Min Upper Band Added", "30 Min Upper Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/30) * 30, 0))),
#"45 Min Upper Band Added" = Table.AddColumn(#"30 Min Upper Band Added", "45 Min Upper Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/45) * 45, 0))),
#"60 Min Upper Band Added" = Table.AddColumn(#"45 Min Upper Band Added", "60 Min Upper Band", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0, 0, Number.RoundUp([ID]/60) * 60, 0))),
#"Changed Type" = Table.TransformColumnTypes(#"60 Min Upper Band Added",{{"Time", type time}, {"5 Min Lower Band", type time}, {"15 Min Lower Band", type time}, {"30 Min Lower Band", type time}, {"45 Min Lower Band", type time}, {"60 Min Lower Band", type time}, {"5 Min Upper Band", type time}, {"15 Min Upper Band", type time}, {"30 Min Upper Band", type time}, {"45 Min Upper Band", type time}, {"60 Min Upper Band", type time}})
in
#"Changed Type"

使用T-SQL创建时间维度

在SSMS中复制/粘贴以下T-SQL,以在SQL Server中获取“时间”维度。您可以创建一个DimTime表,以取消注释行并运行代码。

WITH cte 
AS (SELECT 0 ID 
UNION ALL 
SELECT ID + 1 
FROM cte 
WHERE ID < 1439) 
SELECT ID 
, CONVERT(CHAR(5), Dateadd(minute, ID, '1900-01-01'), 108) [Time] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 5 ) * 5, '1900-01-01'), 108) [5 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 15 ) * 15, '1900-01-01'), 108) [15 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 30 ) * 30, '1900-01-01'), 108) [30 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 45 ) * 45, '1900-01-01'), 108) [45 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, ( ID / 60 ) * 60, '1900-01-01'), 108) [60 Minutes Upper Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Cast(ID AS FLOAT) / 5) * 5, '1900-01-01'), 108) [5 Minutes Lower Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Cast(ID AS FLOAT) / 15) * 15, '1900-01-01'), 108) [15 Minutes Lower Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Cast(ID AS FLOAT) / 30) * 30, '1900-01-01'), 108) [30 Minutes Lower Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Cast(ID AS FLOAT) / 45) * 45, '1900-01-01'), 108) [45 Minutes Lower Band] 
, CONVERT(CHAR(5), Dateadd(minute, Ceiling (Cast(ID AS FLOAT) / 60) * 60, '1900-01-01'), 108) [60 Minutes Lower Band] 
--INTO 点心时间
FROM cte 
OPTION (maxrecursion 0)

 

2018-05-21 18_07_01-SQLQuery2.sql-(本地)_sql2016.AdventureworksDW2016CTP3(DESKTOP-IOPIJTE_Soheil

然后,您可以将DimTime加载到Power BI Desktop并创建必要的关系。

可视化数据

现在,您可以轻松地在Power BI中分析和可视化数据。如您所见,无论使用何种平台,在“时间”维度的所有不同实现中,您始终具有不同的列以支持不同的时间段。如果要具有动态时间段,则必须取消时间维度。一世’d。要赞扬“帕特里克·勒布朗” from “多维数据集的家伙”谁解释了如何创建 Power BI中的动态轴。这在您希望在不同时间段之间切换并立即查看结果的情况下尤其有用。我不会再对此技术进行解释,因为Patric会逐步详细地讲解它,因此,如果您愿意,我鼓励您观看他的视频’d想了解更多。我只是为那些想知道如何在DAX中取消透视表的人提供了DAX代码。如果您使用的是SSAS Tabular 2016(或更早版本),或者’在纯PowerPivot模型上工作,您不会’不能使用Power Query来利用M中的UNPIVOT功能。这是一个基于各种时间段以分钟级别可视化数据的示例。

Power BI中具有动态时间段的时间维度

在DAX中取消枢纽

在撰写本文时,DAX中没有内置的UNPIVOT功能。因此,我们必须以某种方式伪造它。下面的DAX代码根据我们之前创建的“时间”维度创建了一个计算表。同样,当您下载Power BI示例并查看模型结构时,整个事情变得更加清晰。

Time in 达克斯 Unpivot = UNION(
          SELECTCOLUMNS('Time in 达克斯', "ID", 'Time in 达克斯'[ID], "Time Band", "Time", "Time", 'Time in 达克斯'[Time])
          , SELECTCOLUMNS('Time in 达克斯', "ID", 'Time in 达克斯'[ID],  "Time Band", "Time", "Time", 'Time in 达克斯'[Time])
          , SELECTCOLUMNS('Time in 达克斯', "ID", 'Time in 达克斯'[ID],  "Time Band", "5 Min Lower Band", "5 Min Lower Band", 'Time in 达克斯'[5 Min Lower Band])
          , SELECTCOLUMNS('Time in 达克斯', "ID", 'Time in 达克斯'[ID],  "Time Band", "15 Min Lower Band", "15 Min Lower Band", 'Time in 达克斯'[15 Min Lower Band])
          , SELECTCOLUMNS('Time in 达克斯', "ID", 'Time in 达克斯'[ID],  "Time Band", "30 Min Lower Band", "30 Min Lower Band", 'Time in 达克斯'[30 Min Lower Band])
          , SELECTCOLUMNS('Time in 达克斯', "ID", 'Time in 达克斯'[ID],  "Time Band", "45 Min Lower Band", "45 Min Lower Band", 'Time in 达克斯'[45 Min Lower Band])
          , SELECTCOLUMNS('Time in 达克斯', "ID", 'Time in 达克斯'[ID],  "Time Band", "60 Min Lower Band", "60 Min Lower Band", 'Time in 达克斯'[60 Min Lower Band])
          
          , SELECTCOLUMNS('Time in 达克斯', "ID", 'Time in 达克斯'[ID],  "Time Band", "5 Min Upper Band", "5 Min Upper Band", 'Time in 达克斯'[5 Min Upper Band])
          , SELECTCOLUMNS('Time in 达克斯', "ID", 'Time in 达克斯'[ID],  "Time Band", "15 Min Upper Band", "15 Min Upper Band", 'Time in 达克斯'[15 Min Upper Band])
          , SELECTCOLUMNS('Time in 达克斯', "ID", 'Time in 达克斯'[ID],  "Time Band", "30 Min Upper Band", "30 Min Upper Band", 'Time in 达克斯'[30 Min Upper Band])
          , SELECTCOLUMNS('Time in 达克斯', "ID", 'Time in 达克斯'[ID],  "Time Band", "45 Min Upper Band", "45 Min Upper Band", 'Time in 达克斯'[45 Min Upper Band])
          , SELECTCOLUMNS('Time in 达克斯', "ID", 'Time in 达克斯'[ID],  "Time Band", "60 Min Upper Band", "60 Min Upper Band", 'Time in 达克斯'[60 Min Upper Band])
          
                )

 

单击此处下载Excel,PBIX和SQL文件。

6 thoughts on “支持分钟时间段的Power BI和SSAS表格模型中的时间维度

  1. 你好,
    再一次感谢你。如果要在特定日期的特定时间段内计算总和,该怎么做。感谢您的帮助。

  2. 当更改为86400以在一秒中表示秒时,它显示错误:“函数论证‘TIME’数据类型错误,或者结果太大或太小。”

    加列(
    通用系列(0,86400,1)
    , “TimeValue”, 时间 ( 0 ,0, [Value] )
    )

    我什么’m doing wrong?

    1. 圣地亚哥
      感谢您的反馈意见。
      Apparently 时间 function in 达克斯 doesn’不适用于大于32,767的数字。
      查看下面的屏幕截图,该截图清楚地显示了大于32,767的数字将通过该错误进行显示:“函数论证‘TIME’数据类型错误,或者结果太大或太小。”

      Limitation of 时间  function in 达克斯

      这是从Excel继承的已知限制。看着 这里这里.

      Anyways, I posted a new idea to improve the functionality of the 时间 function, please click 这里 和 vote!

      这是一个改进的解决方案:

      ADDCOLUMNS(GENERATESERIES(1, 86400, 1), "时间值", 时间 (0,0,0) + [Value] / (24 * 60 * 60))

      感谢Jeffrey Wang的解决方案。

      您还可以使用最适合您的方案的Power Query(M)方法:

      时间维度(以秒为单位)

      这是Power Query(M)代码的简化版本。

      let
      Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
      #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
      #"Time Column Added" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From(#datetime(1970,1,1,0,0,0)+#duration(0,0,0,[ID]))),
      #"Changed Type" = Table.TransformColumnTypes(#"Time Column Added",{{"ID", Int64.Type}, {"Time", type time}})
      in
      #"Changed Type"

      将来,我可能会写一篇博客文章。
      干杯。

有什么想法吗?在这里与我们分享:

该网站使用Akismet减少垃圾邮件。 了解如何处理您的评论数据.