Excel催化剂开源第48波-Excel与PowerBIDeskTop互通互联之第二篇

Excel催化剂

发布于 2021-08-19 15:55:15

8910

发布于 2021-08-19 15:55:15

文章被收录于专栏：Excel催化剂

前一篇的分享中，主要谈到Excel透视表连接PowerBIDeskTop的技术，在访问SSAS模型时，不止可以使用透视表的方式访问，更可以发数据模型发起DAX或MDX查询，返回一个结果表数据，较透视表的方式更为灵活。此篇再次带出Excel向PowerBIDeskTop发出DAX查询的技术要点。

此篇对应功能实现出自：第5波-使用DAX查询从PowerbiDeskTop中获取数据源https://www.jianshu.com/p/21b2ca8fd2b8

原理性分析

和上一篇类似，同样是使用OLEDB的数据连接，和SSAS分析服务进行连接，但此时使用编程的方式，向SSAS服务发起OleDbCommand，将DAX查询作为参数，传给OleDbCommand，最终模型将返回数据结果。

核对代码如下：

   public static DataTable GetDAXQueryTableFromPbid(string queryString, DataGridViewRow row)
        {
            Entity.PbidFileInfo pbidFileInfo = Common.GetPbidFileInfo(row);

            string cnnString = $"Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhost:{pbidFileInfo.Port};Initial Catalog={pbidFileInfo.DbName};";
            using (OleDbConnection cnn = new OleDbConnection(cnnString))
            {
                cnn.Open();
                OleDbCommand cmd = new OleDbCommand(queryString, cnn);

                OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
                System.Data.DataSet ds = new System.Data.DataSet();
                DataTable dt = new DataTable();
                adp.Fill(ds);
                return ds.Tables[0];
            }
        }

以上发起查询后，数据结构由OleDbDataAdapter的Fill方法存入到DataSet中，再从DataSet取出首张表，即可拿到最终的结果。

同样的代码亦可以使用在Sqlserver上SSAS服务。只是需要将连接字符串带上有权限访问模型的用户名、密码。

DAX查询自动化

看完上述方法，已经蠢蠢欲动，下面再来个高潮，DAX查询全自动化生成。

因为PowerBIDeskTop开启的SSAS服务是本地化服务，本地用户对其有全部的权限访问，并且PowerBIDeskTop界面上拖拉控件，实际上是转化为一段DAX查询向SSAS服务发出查询并返回的数据结构，经控件渲染后产生的可视化效果。

其发出的查询，会保留着最后的几条查询，使用DMV查询可以访问到，所以使用下面的代码，即可拿到PowerBIDeskTop所发出的DAX查询语句，将此段语句再用上面所说的OLEDB的查询向SSAS服务再次请求，就实现了零代码写DAX查询的用户体验。

        private void GetDAXQueryStringFromPbidAndBandingListbox()
        {
            string queryString = "select COMMAND_TEXT from $SYSTEM.DISCOVER_COMMANDS";
            DataTable dt = Common.GetDAXQueryTableFromPbid(queryString, this.dataGridView1.SelectedRows[0]);
            this.listBox1.DataSource = dt.AsEnumerable().Where(s => Regex.IsMatch(s.Field<string>(0), @"EVALUATE\s+TOPN|DEFINE\s+VAR")).Select(s => s.Field<string>(0)).ToArray();
        }