Power BI 系列 - DAX Studio 工具监控使用DMV 监控 POWER BI及 Tabular abular 内存

DMV ：

Select * from $System.discover_object_memory_usage

通过此语句可以查询看当前实例下各种内存使用情况

使用DAX  连接当前打开Powerbi owerbi 文件,在新窗口运行查询语句

 

 

 同样这此查询可以SSAS TABULAR 数据库中查询, TABULAR 与POWER BI 都是同一种运擎

 查询出来结果太多一下子应该看不出所之然. 故使用表格分组对其展示.

展示效果 ：

  可以看出打开一个POWERBI , 有许多地方都要消费内存,

 

 

POWER BI 使用M语言整理数据 ：

下面代码中  localhost:55201  d4cc18d8-eb0f-42a0-975a-52288e54de04  分别是指POWER BI的实例地址,及库名. 可以通过DAX获得,DAX截图, 

let
MemUsage = AnalysisServices.Database("localhost:55201", "d4cc18d8-eb0f-42a0-975a-52288e54de04", [Query="select * from $system.Discover_object_memory_usage", Implementation="2.0"]),
#"Added ObjectPath" = Table.AddColumn(MemUsage, "ObjectPath", each if [OBJECT_PARENT_PATH] <> null then [OBJECT_PARENT_PATH]&"."&[OBJECT_ID] else [OBJECT_ID]),
    复制的列 = Table.DuplicateColumn(#"Added ObjectPath", "ObjectPath", "ObjectPath全路径"),


#"Split Column by Delimiter" = Table.SplitColumn(复制的列, "ObjectPath", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"ObjectPath.1", "ObjectPath.2", "ObjectPath.3", "ObjectPath.4", "ObjectPath.5", "ObjectPath.6", "ObjectPath.7", "ObjectPath.8", "ObjectPath.9", "ObjectPath.10", "ObjectPath.11"}),

#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ObjectPath.1", type text}, {"ObjectPath.2", type text}, {"ObjectPath.3", type text}, {"ObjectPath.4", type text}, {"ObjectPath.5", type text}, {"ObjectPath.6", type text}, {"ObjectPath.7", type text}, {"ObjectPath.8", type text}, 
{"ObjectPath.9", type text}, {"ObjectPath.10", type text}, {"ObjectPath.11", type text}}),

#"Added Database" = Table.AddColumn(#"Changed Type", "Database", each if [ObjectPath.2] = "Databases" then [ObjectPath.3] else null, type text),
    已添加自定义 = Table.AddColumn(#"Added Database", "ModelRelated", each if [ObjectPath.1]<>"Gloabal" and Text.Contains([ObjectPath全路径],"Databases") then true else false),
#"Added Table" = Table.AddColumn(已添加自定义, "Table", each if [ObjectPath.4] = "Dimensions" then [ObjectPath.5] else null, type text)
in
    #"Added Table"

 

下载地址 

https://github.com/zengxinwen/PowerBI/blob/master/7_POWER%20BI%20MemoryReport.pbix

 参考 blog

https://www.kasperonbi.com/what-is-using-all-that-memory-on-my-analysis-server-instance/