SSAS创建数据源全解：模拟信息与Provider选型避坑指南

SSAS 项目开发有一半的坑其实都埋在第一步取数上。这说的就是创建数据源。很多人觉得这步简单开个向导、选个连接、点两下“下一步”完事。但实际项目里你会发现开发环境测试连接一路绿灯一部署到服务器上跑处理Process就报“无法连接数据源”或者明明有权限却在处理时提示“登录失败”。这类问题我在过去几年里遇到太多次了根源基本都是数据源这一步没做透。这篇就专门把“SSAS - 步骤二创建数据源”拆开讲清楚包括数据源在整个 SSAS 架构里的位置、向导操作的每个选项到底在说什么、模拟信息Impersonation怎么选才稳以及部署后、换环境后的注意事项。适合正在学 SSAS 多维模型开发、或者已经上手但被各种连接问题折腾过的同学参考。1. 从全局看数据源它到底在SSAS里扮演什么角色1.1 SSAS 取数流程与数据源的关系很多人容易把“数据源”理解成一个普通的数据库连接字符串用完拉倒。但在 SSAS 项目里这一步的含义比表面看到的要重得多。SSAS 本身不存储业务系统的明细数据多维模型里存的是经过设计后的度量值、维度和聚合结果真正的主数据、明细记录都在源数据库里。模型在部署之后需要使用方触发“处理”时SSAS 引擎才会按照模型定义去访问源数据库、读取数据、聚合计算、写入多维结构或表格模型的列存储。这个“按照模型定义访问源库”的通道就是数据源。换句话说数据源是 SSAS 项目里所有后续步骤——数据源视图DSV、维度、多维数据集、分区、增量处理——共同依赖的取数基础。后面每一个步骤里的表、视图、命名查询最终都要通过数据源去执行。选错 Provider、填错服务器名、配错模拟账户哪怕只是环境切换后忘记更新连接串影响都是全局性的。我的习惯是在动手创建数据源之前先确认三件事源数据库是什么类型、什么版本。SQL Server 还是 Oracle64 位还是 32 位这决定了选哪个 Provider。当前开发机的身份验证方式。Windows 域账户还是 SQL Server 登录域账户和模拟信息的选择直接相关。SSAS 服务账户在源库上的权限。很多人在开发环境一切正常是因为 Visual Studio 里跑的是你本机账户部署到服务器后处理作业使用的是 SSAS 服务账户或模拟账户权限情况完全不一样。这三件事确认完创建数据源这个步骤就不会走偏。1.2 数据源与数据源视图DSV到底怎么分工新手经常把数据源和数据源视图混为一谈甚至在同一个项目里反复创建。这里用一个日常类比来说明。数据源好比是你给后厨定的“供应商”指定从哪家进货、联系人是哪个、送货条款是什么。数据源视图则是后厨的“菜品设计图”你从这批原料里挑出哪些菜、怎么切配、哪些菜之间什么关系、能不能加一些“现成的半成品”命名查询来简化做菜过程。供应商选错了后厨再厉害也拿不到好食材食材供应商没问题但菜品设计图乱画做出来的菜照样会走样。在 SSAS 项目结构上这两者也确实是两个独立节点数据源Data Source只包含连接信息、Provider 类型、模拟信息。数据源视图DSV基于数据源创建的逻辑数据模型包含挑选出来的表/视图、命名查询、计算列、命名的关系Named Relationship等。创建数据源视图时SSAS 需要连接数据源获取元数据。这意味着如果数据源本身配置有问题连接不了、权限不够、Provider 不支持那么“创建 Data Source View”这个步骤就会直接卡住。我在实际工作中见过的错误提示大概长这样无法检索表或视图列表。请验证数据库连接、权限以及 OLEDB 提供程序是否已正确安装。这种报错十有八九不是 DSV 的问题而是数据源这层就没弄干净。2. 手把手创建数据源从向导到 XML2.1 开发环境准备与前置条件创建一个 SSAS 数据源前提是你已经建好了一个 SSAS 项目。项目类型根据模型形态不同而不同多维模型OLAP在 SSDT 里新建“Analysis Services 多维和数据挖掘项目”。表格模型新建“Analysis Services 表格项目”。这两种项目里的“数据源”节点都叫 Data Sources创建向导的每一步也基本一致不过表格模型后续导入数据的方式更多样比如可以直接在“表导入向导”里选源、配置模拟信息、预览表数据。这篇文章以多维模型为主线表格模型的差异会顺带提一下。开发机上的环境要求常见的有SQL Server Data ToolsSSDT或 Visual Studio 里安装了 Analysis Services 项目模板。本机装有至少一个能够连接目标源数据库的客户端组件比如 SQL Server Native Client、ODBC Driver、OLE DB Provider for Oracle 等。开发机能够通过网络访问源数据库端口。默认 SQL Server 是 1433命名实例是动态端口Oracle 通常是 1521。顺手提一个小经验很多时候“测试连接失败”不是账户密码问题而是防火墙、命名实例端口解析、或者 TCP/IP 协议没启用。排错的时候先把网络层打通再抱怨 SSAS。2.2 一步一步走完数据源向导先说正常流程。在解决方案资源管理器里右键“数据源”节点选择“新建数据源”会弹出“数据源向导”。第 1 步欢迎页直接下一步。第 2 步选择“基于现有连接或新建连接”来定义连接。这里有两个选项比较容易让人疑惑“基于现有连接”指当前解决方案里已经存在的连接通常是同项目其他数据源用过、或从服务器 explorer 里拖过来的。“新建连接”自己手填服务器名、数据库名、身份验证。大多数人都会选“新建连接”。点“新建”按钮之后会弹出一个连接管理器对话框这个地方是第一个容易埋坑的点。注意顶部的“提供程序Provider”下拉框默认值未必是你想要的。如果你源库是 SQL Server建议明确选择 SQL Server Native Client 或 Microsoft OLE DB Provider for SQL Server如果源是 Oracle就选 Oracle Provider for OLE DB。具体怎么选我单独放在第 4 部分讲。接下来填写服务器名和数据库名右侧有“测试连接”。测试通过之后点确定向导回到数据源定义页。连接串会显示成类似这样ProviderSQLNCLI11;Data SourceCHEN-PC\SQL2019;Initial CatalogAdventureWorksDW;Integrated SecuritySSPI;到这里还没有结束。下一步会进入“模拟信息”配置页这是整个数据源向导里信息量最大的环节我单独放到第 3 部分展开。第 3 步选择模拟信息后第 4 步可以修改数据源名称默认是源数据库名比如 AdventureWorksDW。点击“完成”项目里就会多出一个 .ds 文件也就是数据源定义文件。如果只是想快速搭一个开发环境原型整个向导不到一分钟就能走完。但要想让这个数据源在生产环境里跑得稳、权限可控通常还需要在后面进一步修改 XML 定义。2.3 向导生成的数据源定义看懂 XML很多教程在这里就结束了但我想多说一步右键项目里的数据源文件选择“查看代码”会看到真正的数据源定义。它不只是给 Visual Studio 看的部署到 SSAS 服务器后这个 XML 会被编译到数据库元数据里。理解它你才能真正控制数据源的行为。一个典型的多维模型数据源 XML 长这样DataSource xmlns:xsihttp://www.w3.org/2001/XMLSchema-instance xmlns:xsdhttp://www.w3.org/2001/XMLSchema xsi:typeRelationalDataSource IDAdventureWorksDW/ID NameAdventureWorksDW/Name ConnectionString ProviderSQLNCLI11.1;Data SourceCHEN-PC\SQL2019;Initial CatalogAdventureWorksDW; Integrated SecuritySSPI;Persist Security InfoTrue; /ConnectionString ImpersonationInfo xsi:typeImpersonationInfo ImpersonationModeImpersonateServiceAccount/ImpersonationMode /ImpersonationInfo TimeoutPT2M/Timeout /DataSource在 XML 里你会看到几个关键节点ConnectionString连接字符串部署后可被覆盖。ImpersonationInfo模拟模式对应向导里的那页选项。Timeout处理时的查询超时时间以 ISO 8601 格式表示。PT2M 就是 2 分钟。默认可能没有这个节点但生产环境我强烈建议手动加上避免处理大型数据源时执行时间过长被 SSAS 的默认超时中断。表格模型的数据源 XML 长得类似也是带 ConnectionString 和 ImpersonationInfo 的。这里给一个建议把数据源命名、ConnectionString、ImpersonationMode 这三样东西当作一个“最小配置单元”来管理。凡是涉及环境切换开发→测试→生产优先核对这三个值。3. 模拟信息选型SSAS 数据源最容易翻车的设置3.1 三种模拟选项的机制与适用场景模拟信息Impersonation Information是 SSAS 数据源特有的概念普通报表系统的数据源一般没有这个设置。它解决的核心问题是当 SSAS 服务器在处理数据或执行查询时以哪个 Windows 身份去连接底层数据库。如果理解不了可以用一个生活场景来套——门禁卡。你给保洁阿姨一张门禁卡她只能进公共区域给电工另一张卡他能进设备间。SSAS 这个“处理机器人”也会拿一张门禁卡去刷源数据库卡的权限决定它能读到什么。数据源向导里常见四种模式使用服务账户ImpersonateServiceAccount表示 SSAS 用自己这个 Windows 服务账户去连接数据库。SQL Server Analysis Services 服务默认账户是 NT Service\MSSQLServerOLAPService如果源数据库在这台机器或同一域环境里且给这个服务账户授了源库读取权限那就能连通。优点不用管理额外的密码服务怎么启动它就怎么跑。缺点如果 SSAS 服务账户和源数据库不在同一个信任域里或源库只开了 SQL Server 身份验证那么这种方式会失败。使用特定用户名和密码ImpersonateSpecificAccount在向导里填写一个域账户和密码。SSAS 处理时会用这个账户的身份去连接源库。这是生产环境里我最推荐的方式。优点权限可控数据库管理员可以精确地给这个账户最小读取权限和 SSAS 服务账户解耦重新配置服务账户时不影响取数权限。缺点密码会加密存储在 SSAS 数据库中密码过期时处理任务会突然失败。使用当前用户的凭据ImpersonateCurrentUser在 Visual Studio 中调试时这是最顺滑的选项因为当前用户就是你自己你在源库上的权限决定了能不能取数。但要注意一旦部署到服务器SSAS 服务进程里并没有你的“当前登录会话”处理时极大概率报错。这个选项只在开发环境图省事时用不适合上线。默认值Default向导里选了“默认”实际部署后的行为通常等价于“使用服务账户”。如果项目在多人协作每个人开发机环境不一致建议明确选择一种模式不要留“默认”否则排查时你根本不知道服务器上是用谁的身份在跑。3.2 我的推荐配置与权限最小化实践基于以往的项目经验不同环境下的模拟信息应该这样配环境推荐模拟模式原因个人开发环境使用当前用户的凭据源码表权限直接用你本机 AD 账户简单不折腾团队测试环境使用服务账户或专用测试域账户多人在同一测试 SSAS 实例上部署避免绑定个人账户生产环境使用特定用户名和密码专用只读账户权限最小化、可审计、不依赖某一个人的账户状态生产环境的实践细节我记录一下在 Active Directory 里创建一个专用账户比如 svc_ssas_read仅用于 SSAS 取数。在源数据库里只给这个账户授予 db_datareader 角色绝不给 db_owner。如果表结构信息要被 SSAS 用于“处理时验证元数据”可能还需要授予视图定义权限VIEW DEFINITION但这往往不是必须的。在这个账户的密码策略上建议设置为“密码永不过期”同时严格限制该账户只能访问数据库服务器不能登录应用服务器。这样避免“密码过期导致每天跑批失败”的低级事故。如果安全规范不允许永不过期那就必须在运维日历里固定一个“密码轮换窗口”提前把新密码同步到 SSAS 数据源的 ImpersonationInfo 里。我踩过的一个真实事故是这样的客户生产环境的 SSAS 服务账户默认是本地系统账户而源数据库在另一台服务器上数据库服务器与该 SSAS 服务器属于不同域。开发时大家用 SQL 身份验证测通了但 SSAS 处理时用的是 Windows 身份验证根本进不了源库。后来改成“特定账户 域只读账户”才解决。所以环境一复杂模拟信息绝不能靠默认。4. 连接细节与 Provider 选型从开发期到生产期的过渡4.1 连接字符串里的每个参数都在说什么连接字符串是数据源的灵魂。很多报错现场DSV 设计得再漂亮也没用连接字符串只要错一个属性就要重新折腾半天。我习惯把连接字符串拆成几段来看ProviderSQLNCLI11.1; Data SourceCHEN-PC\SQL2019; Initial CatalogAdventureWorksDW; Integrated SecuritySSPI; Persist Security InfoTrue;Provider数据访问接口。选错 Provider 可能导致某些数据类型读不出来、事务行为异常。Data Source服务器名。这里可以写 IP、机器名、机器名\实例名。命名实例要注意端口解析问题有时候需要配置 SQL Browser 服务。Initial Catalog数据库名。SSAS 访问的是整个库而不是某一个表。后面数据源视图再挑具体表。Integrated SecuritySSPI表示使用 Windows 身份验证。如果想用 SQL 身份验证一般写成 User IDsa;Passwordxxx; 的形式。Persist Security Info决定连接字符串里是否保留密码等敏感信息。当使用 SQL 身份验证时这个值设成 False 或 True 在不同场景下各有取舍。如果直接用字符串配置建议保留为 True因为 SSAS 需要重新读取这些凭据去连接。如果连接字符串里没有写 ProviderSSAS 可能会默认使用旧版 OLE DB 提供程序偶发兼容问题。建议总是显式写出 Provider。4.2 Provider 选型SQLNCLI、SQLOLEDB、MSOLEDBSQL连接 SQL Server 源库时常见的 Provider 有以下几种SQLNCLI11SQL Server Native Client 11.0随 SQL Server 2012 安装经典稳定。如果你项目是 SQL Server 2012 到 2019 时代经常会遇到。SQLNCLI旧版 Native Client更早的版本建议避免。SQLOLEDBMicrosoft OLE DB Provider for SQL ServerWindows 自带无需额外安装但从 SQL Server 2012 起微软不再推荐。MSOLEDBSQLMicrosoft OLE DB Driver for SQL Server新版驱动微软推荐用于新项目支持 TLS 加密、Always Encrypted性能和兼容性更好。我的经验是新项目直接用 MSOLEDBSQL连接字符串大约长这样ProviderMSOLEDBSQL;Data SourceCHEN-PC\SQL2019;Initial CatalogAdventureWorksDW;Integrated SecuritySSPI;老项目维护时保持原有 Provider 不动尽量不要在巡检时顺手换驱动。因为你不知道生产环境的 SSAS 服务器上有没有装对应驱动换了驱动后旧的加密策略、登录参数可能完全不同。值得单独提醒的是SSAS 服务器上必须安装对应 Client 组件。很多人只在开发机装了 SQL Server Native Client部署到生产服务器的 SSAS 上处理时报“未找到指定的 Provider”就是因为服务器上缺驱动。这个错误的报文不一定明确指 Provider有时候会包一层“数据源初始化失败”。4.3 开发期到生产期的配置切换开发环境的数据源连的是一台开发库生产环境要连生产库这看起来是常识但在实际项目里经常出乱子。常见场景是开发同学把项目文件打包给运维部署运维直接用开发配置部署上去结果生产 SSAS 处理的全是开发库数据。要避免这个问题建议采用“环境化配置”的思路项目里数据源的连接字符串始终指向开发库。这没毛病。部署到测试/生产环境之后在 SQL Server Management StudioSSMS里右键已部署的 SSAS 数据库进入属性 → 数据源修改连接字符串和模拟信息。这种方式能保证项目文件干净部署行为可预测。另一种思路是部署时使用 XMLA 脚本或 PowerShell 来覆盖连接字符串。如果你的团队有 CI/CD这个方式更合适。简单起见维护一个环境对照表环境服务器名数据库名模拟账户开发DEV-SQL01AdventureWorksDW当前用户测试TEST-SQL01AdventureWorksDWsvc_ssas_read生产PROD-SQL01AdventureWorksDWsvc_ssas_read每次部署后第一步就是核对数据源连接串的服务器名而不是直接触发处理任务。这一步省下的排障时间非常可观。5. 常见问题与排查技巧实录5.1 测试连接通过的处理时却报登录失败这是 SSAS 数据源最常见的一种“怪病”在向导里点击测试连接显示成功部署后触发处理报“无法使用当前用户凭据连接到数据源”或者“登录超时”。原因大概率是模拟信息用了“当前用户的凭据”。向导测试连接时Visual Studio 进程就是你的登录会话自然能通过但部署后处理任务由 SSAS 服务执行它拿不到你的交互式会话于是连接失败。处理方式很简单生产环境切到“特定用户名和密码”或“服务账户”然后确认对应账户在源库有读取权限。排查时先看数据源 XML 里的 ImpersonationMode直接就知道是不是这个坑。5.2 密码轮换后处理失败使用“特定用户名和密码”模式时密码是加密存储在 SSAS 数据库里的。如果域密码到期运维在 AD 里重置了密码但 SSAS 里存的还是旧密码那么处理任务很稳定地每天失败。普通用户可能不知道这种模型下密码还要“双写”既要在 AD 里改又要在 SSAS 数据源配置里改。更新方法有两种在 SSMS 里找到已部署数据库打开数据源属性重新输入密码。使用 XMLA 脚本更新 ImpersonationInfo 的账号密码。我更倾向维护 XMLA 脚本因为可以纳入版本管理和变更记录避免不知道谁在哪一天改了什么。脚本大意Alter ObjectExpansionExpandFull Object DatabaseIDAdventureWorksDW/DatabaseID DataSourceIDAdventureWorksDW/DataSourceID /Object ObjectDefinition DataSource xmlns:xsihttp://www.w3.org/2001/XMLSchema-instance xmlns:xsdhttp://www.w3.org/2001/XMLSchema xsi:typeRelationalDataSource IDAdventureWorksDW/ID NameAdventureWorksDW/Name ConnectionStringProviderMSOLEDBSQL;Data SourcePROD-SQL01;Initial CatalogAdventureWorksDW;Integrated SecuritySSPI;/ConnectionString ImpersonationInfo xsi:typeImpersonationInfo ImpersonationModeImpersonateSpecificAccount/ImpersonationMode Accountdomain\svc_ssas_read/Account PasswordNewPassword/Password /ImpersonationInfo /DataSource /ObjectDefinition /Alter执行前务必在测试环境验证一遍。5.3 数据库重命名、服务器迁移后的数据源修复项目上线久了经常会遇到源数据库迁移到新服务器、实例升级、数据库改名这类操作。做完这些改动SSAS 里的数据源不会自动跟随必须要同步更新。更新数据源连接串之后建议按顺序进行先处理数据源实例不是直接处理所有对象确认连接正常然后处理相关维度最后处理度量值分组或分区。很多时候连接串改完后立刻处理全部对象如果某个维度的绑定有问题报错会把之前的错误信息覆盖掉排查反而更难。分步处理的逻辑也很简单数据源是底层依赖先验证依赖本身再验证上层结构最后再跑完整流程。这个思路和写程序时先测底层库、再测接口、再测页面一样。5.4 一个容易忽视的问题查询超时大型数据源一次读取几百万行并不罕见。SSAS 处理时如果执行某个大型查询刚好超过默认超时值就会中断处理报“查询超时”。这种问题一般不会被怀疑到数据源头上因为很多人根本不知道数据源里还有 Timeout 配置。解决办法是在数据源 XML 里增加 Timeout 节点TimeoutPT10M/TimeoutPT10M 表示 10 分钟可以按实际查询长度调整。如果数据量真的很大还可以配合分区来处理把一次性大查询拆成多个小范围查询。超时设置只是兜底不应该当成性能优化的替代方案。这里也提一句后面用分区做的增量处理能显著降低每次处理的数据量从而降低查询时长。6. 最后再分享一个我自己常用的检查习惯数据源定义写完部署前我习惯做最后一次“三看”检查看 Provider 有没有显式写出看模拟信息是不是明确模式看连接字符串里的服务器名和数据库名是否与当前环境匹配。把这个习惯固定下来之后因为数据源引起的故障明显变少了。往往部署后半夜被电话叫醒的事故都是这些“看起来没必要确认”的小细节造成的。如果你接下来要创建数据源视图请务必先把数据源这一步验证扎实。别急着往下冲数据源通了后面的维度、度量值才能谈得上。我实际做项目时一个用户场景处理失败有七成概率回去查数据源配置另外三成才轮到 DSV 的表绑定和维度逻辑。把这个基础打好后面的路会顺畅很多。