如何在 Power BI 中使用 SQL 临时表 [英] How to use SQL temporary tables in Power BI

问题描述

我正在构建一个使用一组 SQL 临时表作为源的 Power BI 报表.我想为用户提供从日期切片器中选择月份的机会，并根据该选择，报告将显示该日期的有效数据.

I am building a Power BI report that uses a set of SQL temporary tables as source. I would like to provide the user the opportunity of selecting a month from a date slicer and based on that selection, the report would show the valid data AS OF that date.

SQL 通过使用允许查询的系统版本表来实现这一点，例如

SQL allows this by using system versioned tables that allow queries such as

SELECT * from table FOR SYSTEM_TIME BETWEEN 'date1' and 'date2'

这将返回表中的所有有效值，就好像查询在这些日期之间运行一样.

which would give back all valid values in the table as if the query was run between those dates.

我找不到任何有关如何在 Power BI 中使用临时表的文档.

I couldn't find any documentation on how to work with temporary tables in Power BI.

在 Power BI 中执行此操作的最佳方法是什么?

What's the best way of doing this in power BI?

推荐答案

我最近刚做了这个.@RADO 是正确的；对于导入模式，Power BI 表中的每个日期(或月份，在您的情况下)都有行.所有工作都在 SQL 中.

I have just done this recently. @RADO is correct; For import mode, you have rows for each date (or month, in your case) in a Power BI table. All the work is in SQL.

首先，我创建了一个 inline(-able) 表值函数，该函数接受 SYSTEM_TIME AS OF 子句的 datetime2 并根据需要返回所需的带有 in 连接的结果集.

First, I created a inline(-able) table-valued function that accepts a datetime2 for the SYSTEM_TIME AS OF clause and returns the desired resultset with in joins as needed.

SELECT 
    header.*, 
    detail.*
FROM header FOR SYSTEM_TIME AS OF @utcTimeStamp AS header
LEFT JOIN detail FOR SYSTEM_TIME AS OF @utcTimeStamp AS detail ON detail.header_id = header.id ;

您必须调整 @utcTimeStamp 以便它为您的日期或月份的含义提供适当的瞬间.

You would have to adjust @utcTimeStamp so it gives the appropriate instant for your meaning of the date or the month.

--- Ajust to match your time period datatype
DECLARE @utcTimeStamp datetime2(3) = DATEADD(ms, -1, DATEADD(DAY, 1, CAST(@date AS datetime2(3)))) AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC';

然后，我创建了一个视图，将函数与所需日期交叉应用.

Then, I created a view to cross apply the function against the dates needed.

当我发现查询视图需要很长时间时，我创建了一个缓存表和 SQL 代理作业来插入从最后一个日期到昨天的结果(在确保昨天在我们所有时区中结束的时间).如果您只需要每月结果，则可能不需要此步骤.

When I found that it took a long time to query the view, I created a caching table and SQL Agent job to insert results from the last date through yesterday (at a time that ensures yesterday is over in all of our timezones). If you only need monthly results, you probably wouldn't need this step.

这篇关于如何在 Power BI 中使用 SQL 临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！