Category Archives: PowerBI

Power BI 一日營

https://onedrive.live.com/?authkey=%21AM58hx2vOseWkhM&id=BF14192BD27975CB%21946764&cid=BF14192BD27975CB

他鄉遇故知

Power BI/AS Tabular Model 的 Formula Engine 為 DAX Query 產生執行計畫,交給 Storage Engine(VertiPaq Engine)時,VertiPaq 執行的依然是 SQL 語法,太有趣了…

image

但這不是標準的 SQL,是 VertiPag Engine 自創的 xmSQL

image

雖然我要做的是 Average

evaluate
ADDCOLUMNS(values(Categories[CategoryName]),"avgSalesAmount",CALCULATE(AVERAGEX(‘Order Details’,’Order Details'[Quantity]*’Order Details'[UnitPrice])))

但 xmSQL 取回的是 Sum 和 Count,且沒有 Group By,根據 The Definitive Guide to DAX Ch15 所言,是 xmSQL 內含 Group By

SET DC_KIND="DENSE";
WITH
    $Expr0 := ( PFCAST ( ‘Order Details'[Quantity] AS  INT ) * PFCAST ( ‘Order Details'[UnitPrice] AS  INT )  )
SELECT
‘Categories'[CategoryName],
SUM ( @$Expr0 ), SUM (  ( @$Expr0 = @$Expr0 )  ), COUNT ()
FROM ‘Order Details’
    LEFT OUTER JOIN ‘Products’ ON ‘Order Details'[ProductID]=’Products'[ProductID]
    LEFT OUTER JOIN ‘Categories’ ON ‘Products'[CategoryID]=’Categories'[CategoryID]
WHERE
    NOT @$Expr0 IS NULL;

另外,附帶一提,終於在 Win10 可以用以往的 blog writer(MS Live Writer)寫部落格(變成 Open Live Writer 了):http://openlivewriter.org/

Calculate is the queen

https://youtu.be/6ncHnWMEdic?t=2204

演講者: Calculate 是 DAX 函數中的女王。為何不是男王(King)呢?因為她太強大了,但沒人能完全了解她。

 

我告訴老婆這位演講者的幽默。

老婆:那位作者是男的對吧

我:對

老婆:男人的笨在於 … 不是女人難理解,是男人懶得理解。

我:子非魚安知魚之樂,我是惠子…

 

 

 

 

 

…且慢,我不是暗喻女生是魚

…但,女生會不會誤解

 

 

老婆:你好辯…趕快去唸書

Power BI Direct Query 呼叫預存程序

參考

https://community.powerbi.com/t5/Desktop/Use-SQL-Store-Procedure-in-Power-BI/td-p/20269

image

image

image

從 Profiler 可以看到,第一次 Mashup Engine 傳回資料,呈現驗證預存程序的資料內容,但第二次,真正要載入到 Power BI 時,會加上 select from ( ) where 1=2

image

換句話說,若是在 Power Query Editor 直接以呼叫預存程序的方式執行沒有問題,但以 Direct Query 載入 Power BI 會有錯誤,但以 Import 載入沒問題。

我猜原因是 Power BI Direct Query Mode 第一次是要載入資料結構,供使用者拖拉後,再立刻產生新的查詢語法,這時才需載入資料,所以故意加上 where 1=2

解法是

select * from openquery(BYRON16C,’exec northwind.dbo.sp “usa"‘)

利用 DAX 建立 Time Table

Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

image

透過 OData feed 以 Reporting Services Shared Dataset 提供資料給 PowerBI

https://docs.microsoft.com/en-us/power-bi/report-server/access-dataset-odata

REST API 的定義:

https://msdn.microsoft.com/en-us/library/mt704264(v=sql.105).aspx

REST API 的格式約略如下:

http://localhost/ReportS/api/v2.0/datasets(479DDB25-5D68-4EE3-A2E0-10E22C525BA7)/data

image

若要查 dataset 的 ID,可以透過

select Name,ItemID from ReportServer.dbo.Catalog
where type=8

選取資料源為 OData feed

image

image

取得紀錄

image

點選 More Columns 取得其他欄位

image

點選設定,去掉 Default column name prefix

image

image

企業內透過 RS 整合 Power BI 的解決方案可用了

2017年 11/1 的更新可能標誌著 Power BI 可以用在企業內 Solution 了,幾個期待已久的功能都做進來

以下是相關的連結:

https://powerbi.microsoft.com/en-us/blog/new-version-of-power-bi-report-server-now-available/

使用 Power BI 報表伺服器的內部部署報表 https://powerbi.microsoft.com/zh-tw/report-server/

image

Microsoft Power BI Desktop (最佳化後十分適合搭配 Power BI 報表伺服器 – 2017 年 10 月 使用) https://www.microsoft.com/zh-tw/download/details.aspx?id=56136

除了以往 Power BI 友善的互動外,這一版新增且企業非常需要的功能:

鑽研篩選

多種 Data Source

  • 非侷限 AS
  • 以 OData  吃 RS 的 Shared DataSet
  • 排程更新 Data Source

image

以 REST API 跟 RS 溝通

透過 URL 內嵌 PBI 時,可以帶過濾條件,例如:https://reportserver/reports/powerbi/Store Sales?rs:Embed=true&filter= Store/Territory eq ‘NC’ and Store/Chain eq ‘Fashions Direct’

 

 

 

 

另外,註記一下,一台機器現在偷偷跑起來的 AS 服務真多

image

從上到下,透過使用者名稱可以得知分別為獨立的 AS、Power BI Desktop 使用的 AS 和 RS 使用的 AS,後兩者是 Power BI Desktop 和 RS 利用來建立與解釋 Model,取得與更新資料用。

取得 Power BI 後的 Model

感謝微軟 Egg 告知,Power BI Desktop 是在背後起一個 AS 服務,以如下的 Power BI Desktop 為例:

image

透過 PowerShell 查該 msmdsrv 的 Processid 後,透過 netstat –o 查聽的 port

image

而後透過 SSMS 連結 AS

imageP

就可以透過物件總管觀察該 Tabular Model

image

透過 SSDT 從 Server 讀回該 Model 定義會有如下的錯誤

image

可以產生 TMSL Script

image

而後再到 AS Tabular 貼上語法執行時,因為會多出 .tables[1].isPrivate、 model.tables[2].columns[3].variations…等屬性而無法部署到獨立的 AS 上,得到的錯誤訊息如下:

正在執行查詢 …
JSON DDL 要求失敗,並出現下列錯誤: Unrecognized JSON property: isPrivate. Check path ‘model.tables[1].isPrivate’, line 251, position 25.。
執行完成

Power BI 2017/9 月版:子報表

Power BI 9 月版終於可以報表跳報表了

https://powerbi.microsoft.com/zh-tw/blog/power-bi-desktop-september-2017-feature-summary/

有設 Drillthrough filter 欄位的,就自動是其他用到該欄位的物件之子報表

image

可以跳至有使用該欄位當 drillthrough filter 的報表

image

另外,若要啟用網頁中 video 說明的 Preview features,需勾選才會出現在上方工具列

image