Power BI桌面查询参数,第2部分,动态数据屏蔽和查询参数

Power BI桌面和SQL Server动态数据屏蔽

如我所承诺 我以前的帖子,在本文中,我将向您展示如何在SQL Server 2016动态数据屏蔽(DDM)之上使用查询参数来利用Power BI Desktop模型。我还将非常简要地说明如何从AdventureWorksDW2016CTP3数据库的DimCustomer表上启用DDM。然后,我们将在DimCustomer表顶部创建带有查询参数的Power BI Desktop模型。您还将学习如何创建Power BI模板,以便将来在部署时使用它。

注意: 如果您想了解有关使用列表输出的信息 Power BI Desktop的“查询参数”中的内容可以看一下这些系列的下一篇文章“Power BI桌面查询参数,第3部分,列表输出“.

用例

在里面 以前的帖子 我解释了如何使用查询参数创建动态数据源。您还学习了如何在过滤器行中使用查询参数。在这篇文章中,您将学习:

  1. 在SQL Server动态数据屏蔽(DDM)之上使用查询参数
  2. Power BI模板中的查询参数

要求

就像 Power BI查询参数的第1部分,您需要满足以下要求才能关注此职位:

  1. 最新版本 Power BI桌面 (版本:2.34.4372.322 64位(2016年4月)或更高版本)
  2. SQL服务器 2016(您可以免费下载SQL Server 2016 Developer Edition)
  3. AdventureWorksDW

定义

我不会提供有关DDM的太多详细信息,因为您会发现很多信息 这里。但是,为了让您稍微熟悉一下动态数据屏蔽,我将对其进行简要说明。

动态数据屏蔽(DDM)

动态数据屏蔽(DDM)是SQL Server 2016和Azure SQL数据库中可用的一项新功能。 DDM基本上是一种防止敏感数据暴露给非特权用户的方法。它是一项数据保护功能,可将敏感数据隐藏在查询的结果集中。您可以轻松地在现有表上启用DDM,也可以在要创建的新表上启用DDM。假设您的零售数据库中有两组用户。销售人员和销售经理。您有一张客户表,在此帖子中,它是AdventureWorksDW2016CTP3的DimCustomer。该表包含敏感数据,例如客户的电子邮件地址,电话号码及其居住地址。根据您的公司政策,“销售人员”组的成员应该不能查看敏感数据,但是应该可以查看所有其他数据。另一方面,“销售经理”组的成员可以查看所有客户的数据。为了防止销售人员看到敏感数据,您可以在DimCustomer表的敏感列上启用动态数据屏蔽。在这种情况下,当销售人员查询表时,他/她将看到被屏蔽的数据。例如,他看到 [email protected] 而不是 [email protected].

在某些列上使用DDM创建表

很简单,只需将“屏蔽方式(功能=“屏蔽功能”)列定义中的“”。所以它应该看起来像这样:

创建表Table_Name  (ID int IDENTITY主键,   Masked_Column1 varchar(100)屏蔽有(FUNCTION =‘Mask_Function’),   Masked_Column2 varchar(100)屏蔽有(FUNCTION =‘Mask_Function’),

 

)

更改现有表并在所需列上启用DDM

如您所料,您必须使用“更改表“ 然后 ”更改列”。您的T-SQL应该如下所示:

更改表 Table_Name 更改列 Column_Name1添加了屏蔽功能(功能=“屏蔽功能”);

更改表 Table_Name

更改列 Column_Name2添加了掩码(FUNCTION ='Mask_Function');

有关更多信息,请参阅 MSDN.

Power BI模板

模板基本上是Power BI文件,它代表预定义Power BI Desktop的实例,该实例包括数据模型,报表,查询和参数的所有定义,但不包括任何数据。创建Power BI模板是简化现有模型部署的好方法。创建模板非常容易,您只需单击文件-> Export –>Power BI模板。我们将通过本文更详细地介绍这一点。

情境

要求您对客户的数据(AdventureWorksDW2016CTP3数据库上的DimCustomer)实施更高级别的安全性,以便只有特权用户才能看到客户的电子邮件,电话号码和居住地址。特权用户是“ 销售经理”数据库角色的所有成员。还要求您阻止“ 销售人员”数据库角色查看敏感数据。但是,“ 销售经理”和“ 销售人员”数据库角色的所有成员都可以查询DimCustomer表。用户不应具有SQL Server登录名。

  • 在DimCustomer中,应屏蔽“ 电子邮件地址”,“ 电话”和“ 地址第一行”
  • 销售经理数据库角色有权查看未屏蔽的数据
  • 销售人员数据库角色有权查看仅被屏蔽的数据
  • SQL服务器数据库用户“ user1_nologin”是“ 销售经理”的成员
  • SQL服务器数据库用户“ user2_nologin”是“ 销售人员”的成员

最重要的是,您必须在Power BI Desktop中实施报告 对于销售经理和销售人员。该报告查询DimCustomer。您需要创建一个Power BI模板,以便它满足安全需求。

为了能够实现上述方案,您必须执行以下步骤:

  • 创建“ 销售经理”和“ 销售人员”数据库角色(如果不存在)
  • 创建两个没有登录名的新用户(user1_nologin和user2_nologin)
  • 将user1_nologin添加为SalesManager数据库角色的成员
  • 将user2_nologin添加为SalesPerson数据库角色的成员
  • 授予对两个数据库角色的选择访问权限
  • 屏蔽DimCustomer中的“ 电子邮件地址”,“ 电话”和“ 地址第一行”列
  • 授予SalesManager数据库角色以查看未屏蔽的数据
  • 创建Power BI桌面报告
  • 将模型导出到Power BI模板

实作

让我们先在SQL Server和Power BI Desktop中开发上述方案。

SQL服务器实施

我将使用T-SQL完成整个SQL Server开发部分。但是,您可以使用SQL Server管理StudioUI来完成很多工作。如果您想使用用户界面来完成这项工作,我将把这部分留给您。

  • 打开SQL Server Management Studio(SSMS)
  • 连接到您的SQL Server 2016实例
  • 为AdventureworksDW2016CTP3打开新查询
  • 复制并粘贴下面的代码片段到查询编辑器中,然后运行它

使用 [AdventureworksDW2016CTP3]

 

—创建数据库角色(如果不存在)

如果 存在 (选择 * 系统.database_principals 哪里 名称 = N’SalesManager’ 类型 = ‘R’)

创建 角色 [销售经理]

 

如果 存在 (选择 * 系统.database_principals 哪里 名称 = N’SalesPerson’ 类型 = ‘R’)

创建 角色 [销售人员]

 

—授予对两个数据库角色的选择访问权限

格兰特 选择 DimCustomer [销售经理]

 

格兰特 选择 DimCustomer [销售人员]

 

—创建用户(如果不存在)

如果 存在 (选择 * 系统.database_principals 哪里 名称 = N’user1_nologin’)

创建 用户 [user1_nologin] 没有 登录

 

如果 存在 (选择 * 系统.database_principals 哪里 名称 = N’user2_nologin’)

创建 用户 [user2_nologin] 没有 登录 DEFAULT_SCHEMA=[dbo]

 

—将user1_nologin添加到SalesManager

改变 角色 [销售经理] 成员[user1_nologin]

 

—将user2_nologin添加到SalesPerson

改变 角色 [销售人员] 成员[user2_nologin]

 

—屏蔽敏感列

改变 DimCustomer

改变 电子邮件地址 面膜 (功能 = ’email()’)

 

改变 DimCustomer

改变 电话 面膜 (功能 = ‘partial(6,”XXXXXXX”,0)’);

 

改变 DimCustomer

改变 地址第一行 面膜 (功能 = ‘default()’);

 

—授予SalesManager以查看未屏蔽的数据

格兰特 UNMASK 销售经理

Power BI桌面实施
  • 打开Power BI桌面
  • 从SQL Server数据库获取数据
  • 键入服务器名称和数据库名称
  • 点击“高级选项”
  • 复制并粘贴下面“ SQL语句”框中的代码,然后单击“确定”

执行 用户 = ‘user2_nologin’

选择 * DimCustomer

还原

Power BI桌面从SQL Server获取数据

  • 如果您使用的是Windows凭据,则单击“连接”,否则单击“数据库”并输入凭据,然后单击“连接”Power BI桌面访问SQL Server
  • 点击“加载”Power BI桌面负载数据
  • 您会收到以下错误消息,请放心,只需关闭错误消息

“ DataSource.Error:Microsoft SQL:由于会话处于终止状态,因此无法继续执行。
当前命令发生严重错误。 结果,如果有的话,应该丢弃。”

DataSource.Error:Microsoft SQL:由于会话处于终止状态,因此无法继续执行。

  • 点击“应用更改”

Power BI桌面应用更改

  • 现在您应该在模型中看到“ Query1”

Power BI桌面查询

  • 单击功能区中的“编辑查询”
  • 如果向右滚动,则会看到“ 电子邮件地址”,“ 电话”和“ 地址第一行”的被屏蔽数据

Power BI桌面查询编辑器

  • 将查询重命名为DimCustomer

现在,您需要为用户创建一个参数。稍后将在数据源中引用此参数

  • 单击功能区中的“管理参数”
  • 点击“新建”
  • 输入名称和描述
  • 选择“文本”作为类型,选择“值列表”作为允许值
  • 在值列表中键入“ user1_nologin”和“ user2_nologin”
  • 在默认值和当前值中选择“ user2_nologin”,然后单击“确定”

Power BI桌面查询参数

您需要在DimCustomer数据源中引用DBUser参数。

  • 在“查询”窗格中,单击DimCustomer。
  • 点击“高级编辑器”
  • 将“ user2_nologin”替换为“”&DBUser&””

Power BI桌面参数化数据源

注意: 请注意引号放在何处。

Power BI桌面参数化数据源

  • 点击“关闭”&应用”从功能区

看来我们完成了。现在该切换用户以查看发生了什么。为了简化操作,让我们在报表页面上放置一个包含“名字”,“姓氏”,“电子邮件地址”,“电话”和“地址行1”列的表格。

Power BI中的动态数据屏蔽(DDM)

  • 单击功能区中的“编辑参数”
  • 从列表中选择“ user1_nologin”,然后单击“确定”。

Power BI桌面选择参数

  • 确认运行本机数据库查询

Power BI桌面本机数据库查询

糟糕!您再次收到该讨厌的错误消息。当然,您可以关闭该消息并单击“应用更改”,但是,无论何时切换用户,获取该错误消息似乎都不现实。

我们编写的查询到底有什么问题?

答案是查询确实没有错。收到错误消息的原因是查询的第一行。我们以用户身份执行查询,但是,我们已经使用了另一个凭据来连接到数据库(在本示例中为Windows用户)。这称为“上下文切换”。基本上,Power BI Desktop希望重置当前连接的状态,然后将其重新用于其他用户。重置当前会话会导致问题。

顺便说一句,让我们关闭错误消息,然后单击“应用更改”以确保切换用户后我们可以看到未屏蔽的数据。

Power BI桌面应用更改

Power BI桌面中的动态数据屏蔽(DDM)

如您所见,该过程运行正常,但是,我们需要对此采取补救措施,以消除该讨厌的错误消息。

解决方案是将查询封装在SQL Server端的存储过程中。在这种情况下,Power BI Desktop将不会重置连接。为每个用户创建存储过程之后,我们需要在Power BI Desktop中创建一个新参数,以将存储过程名称传递给数据源而不是用户。

注意: 您只能创建一个存储过程。在这种情况下,您需要为SQL数据库用户定义一个参数,然后构造存储过程以编写动态SQL。但是,为了使操作尽可能简单,我为每个用户创建了两个单独的存储过程。

  • 返回SSMS并运行以下SQL脚本来创建两个新的存储过程

创建 程序 [dbo].[DimCustomerMasked]

执行 用户 = ‘user2_nologin’

选择 * DimCustomer

还原

创建 程序 [dbo].[DimCustomerUnMasked]

执行 用户 = ‘user1_nologin’

选择 * DimCustomer

还原

  • 在Power BI Desktop中,单击“编辑查询”
  • 在查询编辑器中,单击功能区中的“管理参数”
  • 将现有值替换为存储过程名称
  • 选择“ DimCustomerMasked”作为默认值和当前值,然后单击“确定”

Power BI桌面编辑查询参数

  • 从“查询”窗格中选择DimCustomer,然后从功能区中单击“高级编辑器”
  • 将整个查询替换为以下内容

“ EXEC“&DBUser”

Power BI桌面参数化数据源

Power BI桌面参数化数据源

注意: 请注意引号。

Power BI桌面参数化数据源

  • 单击“编辑权限”,然后单击运行

Power BI桌面本机数据库查询

  • 点击“关闭”&应用”从功能区

Power BI桌面中的动态数据屏蔽(DDM)

  • 现在看起来好多了
  • 单击功能区中的“编辑参数”,然后将存储过程切换为“ DimCustomerUnmasked”

Power BI桌面输入查询参数

  • 点击运行

Power BI桌面本机数据库查询

Power BI桌面中的动态数据屏蔽(DDM)

嗯,看起来不错。 微笑

Power BI模板

如前所述,创建Power BI模板非常容易。只需保存当前模型,然后保存文件–> Export –> Power BI模板.

Power BI桌面导出模板

编写一些描述,然后单击“确定”。

Power BI桌面导出模板

保存模板。

Power BI桌面导出模板pbit

关闭Power BI Desktop。现在,双击模板文件将其打开。打开模板文件后发生的第一件事是它要求输入参数。您可能会注意到,Power BI Desktop会加载新的无标题模型。

Power BI桌面导入模板

Power BI桌面中的动态数据屏蔽(DDM)

如果您切换参数值,则会看到不再要求您确认运行本机数据库查询的信息。

Power BI桌面中的动态数据屏蔽(DDM)

遗言

您可以将参数的数据加载到模型中,这确实很棒。我将其传递给您进行进一步调查。

示例模板已准备好下载

您可以在此处下载我在AdventureWorksDW2016和Dynamic Data Masking之上创建的示例模板。它包含 以前的帖子 样本以及您在当前文章中学到的知识。

2 thoughts on “Power BI桌面查询参数,第2部分,动态数据屏蔽和查询参数

    1. 嗨,阿什,

      欢迎来到BIInsight.com。
      查询参数在当前版本的PBI REPORT SERVER(2020年1月)中不可用(与Power BI Service不同)。
      因此,唯一的方法是在Power BI Desktop RS中打开报表,修改参数’值并将报表保存回报表服务器。
      希望有帮助。

      干杯。

有什么想法吗?在这里与我们分享:

该网站使用Akismet减少垃圾邮件。 了解如何处理您的评论数据.