快速提示: 时间 Dimension with 时间 Bands at Seconds Granularity in Power BI 和 SSAS 表格

时间 Dimension with 时间 Bands at Seconds Granularity in  Power BI  和  SSAS 表格

I wrote some other posts on this topic in the past, you can find 他们 这里 这里 。在第一篇文章中,我解释了如何创建“Time”时间带的维度 分钟 粒度。然后,我的一位客户要求“Time”几秒钟内的尺寸,这鼓励了我写第二篇博客文章。在第二篇博文中,虽然我没有’t做时间带,所以我在这里写第三篇文章,这是第二篇文章支持5分钟,15分钟,30分钟,45分钟和60分钟的时间带的变体。“Time”尺寸降至秒。在这篇快速文章中,我直接跳到重点,向您展示如何生成“Time”维度以三种不同的方式,即在SQL Server中使用T-SQL,使用Power Query(M)和DAX。然后是这里:

时间 Dimension at Second Grain with 功率查询 (M) Supporting 时间 Bands:

在查询编辑器中复制/粘贴以下代码’s Advanced Editor to generate 时间 dimension in 功率查询:

let
Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
    #"时间 Column Added" = Table.AddColumn(#"Renamed Columns", "时间", each 时间.From(#datetime(1970,1,1,0,0,0) +  #duration (0,0,0,[ID]))),
    #"Hour Added" = Table.AddColumn(#"时间 Column Added", "Hour", each 时间.Hour([Time])),
    #"Minute Added" = Table.AddColumn(#"Hour Added", "Minute", each 时间.Minute([Time])),
    #"5 Min Band Added" = Table.AddColumn(#"Minute Added", "5 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) +  #duration (0, 0, Number.RoundDown(Time.Minute([Time])/5) * 5, 0))  +   #duration (0, 0, 5, 0)),
    #"15 Min Band Added" = Table.AddColumn(#"5 Min Band Added", "15 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) +  #duration (0, 0, Number.RoundDown(Time.Minute([Time])/15) * 15, 0))  +   #duration (0, 0, 15, 0)),
    #"30 Min Band Added" = Table.AddColumn(#"15 Min Band Added", "30 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) +  #duration (0, 0, Number.RoundDown(Time.Minute([Time])/30) * 30, 0))  +   #duration (0, 0, 30, 0)),
    #"45 Min Band Added" = Table.AddColumn(#"30 Min Band Added", "45 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) +  #duration (0, 0, Number.RoundDown(Time.Minute([Time])/45) * 45, 0))  +   #duration (0, 0, 45, 0)),
    #"60 Min Band Added" = Table.AddColumn(#"45 Min Band Added", "60 Min Band", each 时间.From(#datetime(1970,1,1,0,0,0) +  #duration (0, 0, Number.RoundDown(Time.Minute([Time])/60) * 60, 0))  +   #duration (0, 0, 60, 0)),
    #"Removed Other Columns" = Table.SelectColumns(#"60 Min Band Added",{"时间", "Hour", "Minute", "5 Min Band", "15 Min Band", "30 Min Band", "45 Min Band", "60 Min Band"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"时间", type time}, {"Hour", Int64.Type}, {"Minute", Int64.Type}, {"5 Min Band", type time}, {"15 Min Band", type time}, {"30 Min Band", type time}, {"45 Min Band", type time}, {"60 Min Band", type time}})
in
#"Changed Type"
继续阅读 “Quick Tips: 时间 Dimension with 时间 Bands at Seconds Granularity in Power BI 和 SSAS 表格”

Power BI 治理, 良好做法, Part 1: Multiple Environments, 数据 Source Certification 和 Documentation

 Power BI 治理, 良好做法, Part 1: Multiple Environments,  数据  Source Certification 和 Documentation

Power BI 正在迅速发展,并且正在迅速成为市场上最受欢迎的商业智能平台。轻松进行互动并快速获得专业成果,使其成为希望增强BI实力并在整个企业范围内做出以数据为依据的决策的组织的理想工具。

Gartner 2020 Magic Quadrant for Analytics 和 商业情报Platforms
Did you know 那 Gartner named Microsoft as the 2020 leader in their Magic Quadrant for Analytics 和 商业情报Platforms?

In this post we’re going to look at three good practices for implementation 和 give you the tips you need to make sure you avoid common pitfalls so you are on the fast track to success with Power BI on your organisation.

1.设置多个环境

什么时候 从事Power BI实施项目, it’s 拥有多个环境是明智的 to manage BI资产的生命周期。 下面我们列出了几个 environments that should be 考虑取决于 complexity of the project and your organisation’s needs.

开发(又名Dev)

能够掌握许多报告 testing, and 有能力 跟踪发生的变化, is essential 当您进行设置时。没有特定的 Dev environment, 您的生产环境将很快成为 overwhelmed with assets, making it hard to maintain 和 manage.  

在开发环境中工作时,请确保您具有专门用于开发的数据源。 We’ve seen production data used in dev on many occasions which can lead to serious privacy 和 data sovereignty issues. 您的开发数据源不应包含敏感数据。 

的se development 环境 can be on your local network or in cloud storage (like OneDrive for Business or GitHub). It is recommended to have separate Workspaces in Power BI 服务 for each environment.

Tip: 的 data sources of all published reports to Power BI 服务 must be sufficient for development use only 和 应该 avoid including confidential data.

用户验收测试(aka UAT) 

每天将使用报告的人员应进行测试 them – 他们知道生意 最好,并且能够确定 机会与差距 that the development team may not be able to identify 他们selves. By making sure the user is 提早进入流程 it 使业务增值最大化。

用户验收测试 is the last phase of testing. The UAT environment 仅应在解决方案完成后创建 fully tested in Dev 和 由高级Power BI开发人员批准。

继续阅读 “Power BI 管治, 良好做法, Part 1: Multiple Environments, 数据 Source Certification 和 Documentation”