MS报表方案.doc

MS报表方案SQL SERVER 提供多种服务来实现BI的功能。1）SQL SERVER Reporting Services2）SQL SERVER Integration Services3）SQL SERVER Analysis Services一安装部署1.1 安装条件Windows Installer 3.1MDacIE 6.01.2 安装组件 如果安装SQL SERVER 数据库引擎：在SQL Server 安装文件里包括：Integration Services Analysis ServicesReporting ServicesBI Development Studio文件路径：C:Program FilesMicrosoft SQL ServerMSSQL.1数据库引擎中的数据库文件，包括Reporting Services用的数据库文件：ReportServer.mdfReportServerTempDB.mdfC:Program FilesMicrosoft SQL ServerMSSQL.2存放分析服务的项目，把分析服务项目部署到分析服务后，放在这个目录。C:Program FilesMicrosoft SQL ServerMSSQL.3报表服务信息，包括Report Server 和Report ManagerD:Program FilesMicrosoft SQL Server90DTSPackages集成服务部署到这个目录。 如果不安装数据库引擎：只安装三个服务（Integration, Analysis, Reporting）和开发工具，不安装SQL SERVER 数据库服务。但：1）Model Designer只能连SQL SERVER2）没有安装Report Server 的Web 服务3）可以用Report Designer设计报表，预览报表，但不能发布到REPORT SERVER上。4）因没有Report Server 的Web服务，不能使用Report Builder。5）可以用SQL Server Managerment Studio管理工具连接分析和集成服务，但不能连报表服务6）可以开发，执行集成项目，但管理功能很少。文件路径：C:Program FilesMicrosoft SQL ServerMSSQL.1存放分析服务的项目，把分析服务项目部署到分析服务后，放在这个目录。C:Program FilesMicrosoft SQL ServerMSSQL.2报表服务信息，包括Report Server 和Report ManagerD:Program FilesMicrosoft SQL Server90DTSPackages集成服务部署到这个目录。1.3 部署二报表服务（Reporting Services）2.1 Report Server报表服务器是 Reporting Services 的主要组件，由以下两部分组成：Web 服务通过Web 服务，报表生成器可访问报表服务器。1处理 Web 服务 API 和 URL 请求。2处理报表，包括运行查询、评估表达式以及生成输出格式。3提供快照和报表高速缓存管理。4支持并强制执行安全策略和授权。Windows 服务Windows 服务则提供了报表计划和传递服务。在设计、保存、运行、管理和发布报表时，将同时使用这两个服务。报表服务器可以处理报表请求并检索报表的属性、格式信息和数据。报表服务器将格式信息与数据相结合，以呈现最终报表。 可以用VB或VC# 程序与Report Server Web Service 通信，用程序管理Report Server中的对象。报表服务器存储文件和文件夹的方式与文件系统类似。但是，报表服务器管理和存储的项与文件系统中的文件是不同的。报表定义的存在形式既可以是文件系统中的文件，也可以是报表服务器上的报表项。在文件系统中，您创建的报表定义的文件扩展名为 .rdl。当您创建报表生成器报表时，您创建的就是 .rdl 文件。当您将报表保存到报表服务器时，您是在发布报表，在这种情况下，报表将成为报表服务器上存储的项。一旦在报表服务器上发布了报表，就可以从相应的报表服务器数据库中检索该报表，并将其以报表服务器文件夹中的项的形式呈现给其他用户和应用程序。2.2 ReportServer DB储存 Report Server 所使用信息的 SQL Server 数据库，包括数据源定义，报表定义、报表元数据、高速缓存的报表、快照和资源。它还存储安全性设置、帐户信息以及日程和交付数据。在安装报表服务时，将建两个数据库reportserver and reportservertempdb所有对这两个数据库的操作都应该由REPORT SERVER 完成，用户不应该自己修改。2.3 Report Manager基于 Web 的报表访问和管理工具，随 Reporting Services 一起分发。此组件安装在 Web 服务器上。需要IE6.0 或以上打开方式：http:/reports用Report Manager可以执行以下任务：创建文件夹组织报表配置角色访问权限查看，查找，订阅报表配置报表执行属性，报表历史，报表参数创建Report Model创建定时和共享数据源，便于数据源的管理数据驱动的报表订阅的功能在Report Manager 中运行 Report Builder2.4 Report Designer2.4.1 数据源Reporting Services 对下列数据源的提供了内置支持：SQL Server 2005 / SQL Server 2000OLE DB 数据源 (包括 SQL Server Analysis Services)开放式数据库连接 (ODBC) 数据源ORACLE（需要安装ORACLE客户端工具）Report Model（需要Report Model Query Designer工具）数据集直接写SQL语句，通过SQL语句分析出输出字段。也可以用图形化的查询设计器一个报表中可以使用多个数据集查询参数可以使用查询参数(见报表参数)计算字段根据字段，函数，运算符，常量构造计算字段2.4.2 Report Model Query Designer如果想在Report Designer中使用Report Model，需要调用工具Report Model Query Designer。步骤：1）在设置数据源时，选择Report Server Model。2）在Connection string 属性中，输入Report Server Model 的URL如：Server=http:/localhost/reportserver; datasource=/models/adress3）查询设计4）设计报表和普通连接一样处理2.4.4报表样式1）Freeform在设计面板上的TextBox设置字段时，只输出第一行记录在LIST里的TextBox中设置输出信息时，可以按布局输出每行记录2）Table一行一行地输出数据。可以设置分组。3）Matrix（交叉表）4）Charts5）带有实时过滤的参数化报表（在报表上加选择框选择参数值，详见报表参数）6）排序、分组与过滤每个循环输出的组件（List,Table,Matrix,Chars）都有排序（Sorting）、分组（Groups）和过滤（Filters）功能。7）Drillthrough8）Hyperlinks9）子报表10）各种元素可以嵌套11）网格线需要自己画通过设置各组件边框风格和边框颜色画出网格线。12）不能做多维报表2.4.4固定格式的报表需要自己画线条，可以借助网格画线直观地调整位置，大小适用于固定格式的报表（如：资产负债表，损益表等）2.4.5 报表参数报表参数可以用来操作数据、连接相关联报表和使报表显示多样化。1、 参数的定义a) 在定义查询语句时，使用” 参数名（如：Test），报表会自动生成参数。b) 选择报表（Report）中的参数（Parameters）自己添加参数。2、 参数的属性a) 参数名和数据类型(Name and DataType)参数名在报表中必须唯一，且默认数据类型为字符串。如果参数来源于查询，确保参数类型与之相符b) 提示(Prompt)在发布的报表中，提示作为参数输入框的标签，同参数一起出现在报表上方。c) 隐藏和内部参数(Hidden and Internal)隐藏和内部参数选项提供了另外两种报表参数显示形式。隐藏参数不会在发布的报表中被显示，而在URL或在订阅报表时进行参数的设置。内部参数则只能在定义报表时设置d) 单值和多值参数(Single Value and MutiValue)参数和设置为多值，如果选上“多值”复选框，否则参数为单值。多值参数允许您在选择参数时同时选取多个值。e) 有效值(Available Values)有效值是定义参数的取值范围，可以定义为从查询字段得来，或自定义静态值。前列的标签（Lable）是定义在发布报表时选择参数的显示值，后列的值（Value）是定义实际传入报表中的参数值。f) 默认值(Default Value)可设置参数默认值，如果一张报表所有参数都有默认值，则在浏览报表时报表会以默认值为参数显示出来，否则要将所有参数都设值后才可运行报表。注意，默认值可以不设，但同时，提示(Prompt)一定要有。3、 参数的使用在定义数据集和报表中的表达式里使用参数，使用时在参数名前加”，（如month）。在报表运行时，如果选了“提示”，你会看到参数标签和选择参数值的下拉框（单值）或复选框（多值），选择后预览报表。4、 多值参数多值参数返回的是一个数组，我们可以在表达式中如此使用他们：Parameters!.Value返回参数值Parameters!.Lable返回参数值转成字符串Parameters!.Count返回参数个数=Join(Parameters!Title.Value, , )返回用“，”连接的多个参数值在查询时使用多值参数要满足一下条件：1)数据源必须是SQL Server 、Oracle 或分析服务（Analysis Services）中的一个。2)当存储过程作为数据源，不支持多值参数。3)查询中一定要用“IN”语句修饰多值参数。如：SELECT FirstName, LastName, e.Title FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.Title IN (Title)5、使用参数时请注意1)参数的数据类型要匹配2)内部参数可作为变量使用3) 如在查询中使用参数，最好自动生成参数。报表会自动定义参数数据类型4) 当定义从查询字段获得参数值是，最好单独为建立数据集，获取字段，避免将复杂查询得到的字段作为参数的有效值。2.4.6 自定义代码和引入程序集你可以添加自定义代码来实现一些自定义功能，添加方式有两种：1)直接在报表内自定义代码2)引入程序集一、 自定义代码在报表属性中的Code里，输入自定义代码块。该代码块可定义多个函数，但是函数必须使用Microsoft Visual Basic语言写，并且必须是基于实例的。在调用方法时，使用“Code.”加在函数名称前调用。（如：Code.GetData()）二、 引入程序集可以引入现有程序集和自定义程序集（DLL文件），下面以引用自定义程序集为例：1、编译类库项目，得到Sampl.dll文件，函数写在其Utility类下2、将文件拷贝到Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies目录下3、在报表属性中的References中，将Sampl.dll添加进去，如果函数是非静态的，需要在下方自定义一个实例名。4、 用时静态的用“Sample.Utility.”函数名，非静态的用“Code.InstanceName.”函数名，如（Code.outFlow.GetData()）。5、 发布之前，再将Sampl.dll拷贝到Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerbin目录下6、 修改ReportServer的rssrvpolicy.config文件，添加上下面的信息：(换成实际路径)2.4.7 发布报表生成报表后以RDL的格式发布到Report Server 上。发布路径在“工程”（Project）菜单下的当前工程属性中设置。设计报表时，在测试机上做，做完后发布到生产机上，发布方式相同。2.4.8转换为其他格式Data rendering extensions: XML, CSVLayout rendering extensions (interactive) HTMLLayout rendering extensions (non-interactive, logical page renderers): Excel, MHTMLLayout rendering extensions (non-interactive, physical page renderers): PDF, Image2.4.9 连接分析服务器1指定服务器2选择分析服务项目名3查询定义4设计报表5部署报表（定义查询）2.5 Report Builder2.5.1 启动方式启动Report Builder ：http:/localhost/reportserver/reportbuilder/reportbuilder.application能否在程序中调用Report Builder，而不是从IE启动2.5.2 设计界面2.5.3 数据源Report Builder 使用预先定义的 Report Models 来访问数据源。一个报表只能使用一个Report Model.Report Model包含多个实体（例如“产品”、“雇员”或“客户”）和文件夹2.5.4报表类型表格报表表格报表布局模板以逐行格式显示数据交叉表图表2.5.5 保存报表报表文件格式为RDL可以保存到REPORT SERVER 上，选择一个目录保存也可以保存到文件中。2.5.6 计算公式可以使用已有的字段，函数和常量定义计算公式，计算公式作为新的字段。2.5.7 分页标题可以设置标题显示在每页的页头分页可以设置根据分组进行分页2.5.8 筛选实现所有常用谓词：大于，等于，所有，任何，无，并非所有 等可以用OR 或 AND 进行连接。2.5.9 排序和分组排序根据字段在报表中的顺序设置排序分组向报表拖放实体时，对每个实体自动创建一个组向组中加聚合字段时，对分组产生小计和总计2.6 Model Designer2.6.1 建模工具有三种工具可以创建Repot Model：Model Designer 只能基于SQL Server Database 建模这个工具最灵活Report Manager能基于SQL Server Database 和Analysis Services CubeManagement Studio 能基于SQL Server Database 和Analysis Services Cube2.6.2 用Model Designer创建Report Model步骤1. 在Business Intelligence Development Studio中选择Report Model Project。2. 创建数据源（只能SQL Server）。3. 创建源视图（Data Source View），选择需要的数据表，设置连接关系。4. 根据规则创建Report Model，即把数据表和字段直接映射为实体和对象。5. 手工创建Report Model6. 把Repot Model 发布到Report Server.数据源和源视图一起发布到SERVER上.7. 模型文件one or more data source (.ds) filesone or more data source view (.dsv) filesone or more report model (.smdl) files.Semantic Model Definition Language (SMDL), which is XML-based2.6.3 数据源只能对SQL Server 数据库建模，在建立连接时，不能选择PROVIDER。怎样连ORACLE.2.6.4 模型对象ENTITY一般对应一个数据表创建新的实体时，需要和一个表或列绑定。FOLDER可以把entities, perspectives 和其他 folders 组织到一起，放在folder中。目的是为了便于用户浏览查看PERSPECTIVE是MODEL的一个子集，放在一个PERSPECTIVE的ITEM也可以放在其他ITEM中，可以在分配用户权限时使用。可以包括entities folders, roles, source fields, and expressionsATTRIBUTE对应源字段ROLE用来指示两个实体的关系，可以是一对一，一对多，多对多。EXPRESSION是用字段，函数，常量，运算符的表示的表达式2.6.5 发布需要发布到REPORT SERVER上。第一次发布时，需要把数据源和源视图发布到REPORT SERVER上。以后可以只发布模型定义文件。2.6.6 使用Report Model可以用在Report Builder中，也可以用在Report Designer中。2.7 发布报表你可以将报表发布到门户上或者通过电子邮件发布给最终用户，也可以给最终用户合适的权限让他们访问基于WEB的应用来获取报表。查找和定位的特性可以帮助用户定位和运行他们需要的报表。个性化的订阅使得用户可以按照他们喜欢的方式选择报表渲染的格式和调度的方式。可以在报表设计器上设计完报表后直接发布到报表服务器上。可以打印预览可以导出到各种格式（PDF，EXCEL，XML，CSV等）2.8 与ERP集成1建模Model Designer只能对SQL SERVER中的数据建立模型不知是否可以扩展其功能，以便支持ORACLE2Report Builder 和Report Designer 都可以使用ReportModel，Report Designer功能更强大一些，支持更多的报表样式。3报表和ReportModel的元数据都必须存在SQL SERVER 中，不知能否放在Oracle中4展示报表支持WEB形式的报表展示在ReportBuilder 和Report Desinger中都可以预览报表5我用的Reporting Services是SQL SERVER中带的，不知单独安装的MS BI系统是否更适用，暂时没找到单独安装的MS BI系统6在WEB方式可以只运行报表，不能修改报表模板。没有其他只显示不能修改报表的工具。三集成服务（Integration Services）3.1 架构Integration Services 是个WINDOWS 服务，用来管理集成服务的任务包。Microsoft SQL Server 2005 Integration Services (SSIS)提供ETL功能，在数据转换方面增加了很多功能，根据一致性维度的模型设计理论，满足数据仓库和数据集市的需要。包括两个核心引擎：run-time engine用来管理和执行任务包。用来设置执行顺序，日志，设置变量，事件处理等。data flow engine 专门设计的高性能的ETL引擎。3.2 功能1从异构数据源提取数据进行合并遗留系统的数据各子公司用不同的方式存储数据对相同的数据可能有不同的SCHEMA，如数据类型不一致。2数据仓库和数据集市应用支持大数据量的提取转换，可以设置CHECKPOINT，从CHECKPOINT 加载数据，可以节省时间对维表和事实表的加载提供良好的支持适合更新数据仓库和数据集市的数据的应用。如渐变维度的处理可以在加载数据前进行聚集计算，如SUM，COUNT等。加载过程中生成代理键等功能（LOOKUP 转换）Analysis Services Execute DDL 和Analysis Services Processing Task 用来处理分析服务。3清洗和标准化处理数据转换数据格式用索引表的数据更新记录。处理重复记录。4自动实现管理功能数据备份和恢复拷贝数据对象加载数据等。5其他功能FTP邮件3.3 数据源1flat files2Excel 3XML documents4ODBC，OLEDB，ADO等5分析服务对象3.4 开发和管理包开发：Business Intelligence Development Studio生成包发布文件：1在PROJECT PROPERTIES 中创建DEPLOYMENT UTILITY，指定输出路径。2在BUILDE PROJECT 时，在指定路径自动创建用于发布的文件。安装包：1 可以发布到文件系统，也可以发布到SQL SERVER （msdb）数据库（管理功能和安全性更强）。在SQL Managerment Studio 中，包括两个文件夹：File System 和 MSDB。2 在目标机的资源管理器中，双击文件.SSISDeploymentManifest3 按照提示进行安装。管理包：只能在SQL Server Management Studio中进行管理，可以设置定时，监控性能，导入导出包等。3.6 与分析服务的集成有两个任务Analysis Services Execute DDL Analysis Services Processing Task 用来处理分析服务，如预先汇总等。3.7不安装SQL SERVER 数据库引擎开发：可以用Oracle Provider for OLEDB 连Oracle，执行ETL操作。发布：可以发布到集成服务的文件系统中管理：在SQL SERVER MANAGER STUDIO中可以查看PACKAGE，但没什么管理功能。3.7 与ERP集成1无法使用数据字典的功能（中文表名，字段名等）2可以直连ORACLE数据库。3不需要SQL SERVER4缺乏任务包的管理功能，四分析服务（Analysis Services）4.1 工具开发和部署：用 Business Intelligence Development Studio 开发和部署分析服务项目。管理分析服务：用SQL Server Management Studio管理这些项目，可以进行处理，备份等。分析服务Analysis Services ，以WINDOWS服务的形式运行。集成服务（Integration Services）在集成服务中可以定义分析服务所需要的数据清洗，加载，更新等功能。4.2 分析服务架构4.2.1 架构分析服务的通讯协议为XML for Analysis (XML/A)，通过TCP或HTTP与服务端通信。4.2.2 服务端组件Analysis Services，程序名为msmdsvr.exe, 以Windows service的形式运行。功能解析查询语句（Parse statements received from clients）管理元数据（Management meta data）处理事务（Handle transactions）处理计算（Process calculations）存储维度和详细数据（Store dimension and cell data）创建聚集（Create aggregations）定时查询（Schedule queries）Cache（Caching objects）管理服务端资源（Manage server resources）维度存储Microsoft SQL Server 2005 Analysis Services中的维度可以是两种类型之一：multidimensional OLAP (MOLAP)relational OLAP (ROLAP)4.2.3 客户端组件在Report Designer 中可以直接连接分析服务，定义报表。Programming Language Data Access Interface C+OLE DB for OLAPVisual Basic 6ADO MD.NET languagesADO MD.NetAny language that supports SOAPXML for Analysis4.2.4 WEB客户4.2.5 分析服务数据库分析服务元数据保存在文件中，不是在关系数据库中。可以在SQL Server Management Studio中进行设置文件目录默认为C:Program FilesMicrosoft SQL ServerMSSQL.2OLAPData一个分析服务数据库对应一个分析服务项目。在数据库中没有对应分析服务的数据库，MOLAP和ROLAP的数据放在什么地方4.3 开发4.3.1 开发步骤1定义数据源可以选择各种数据源，ORACLE，SQL SERVER，ODBC，OLEDB等。2定义源视图选择维表和事实表，可以增加删除表，可以编辑连接关系。可以查看维表和事实表的数据。如：DimCustomerDimGeographyDimProductDimTimeFactInternetSales3定义CUBE定义维度，修改维度属性从事实表选择度量定义层次4部署要查看分析服务中的数据，需要把项目部署到Analysis Services上并进行处理。运行DEPLOY，把项目发布到Analysis Services上可以把部署信息保存到文件上，再把文件部署到生产用机器上（在文件中，不是在数据库中，也不是部署到Reporting Services上）5处理分析服务对象在运行DEPLOY的过程中，将处理分析服务对象。处理维度，对数据表自动生成层次处理CUBE，汇总数据等6查看CUBE数据查看立方体和维度数据，检查运行结果，以便对立方体进行修改，如设置排序，增加删除属性等。4.3.2 定义存储方式（分区Partition）分区是包含数据的一个子集的文件，把数据放在不同的硬盘上，可以提高访问速度。在Cube Designer的Partitions tab上,点 Storage Settings可以设置存储方式。存储选项：注：1到5只适合数据源是SQL SERVER，6和7可以是其他数据源1）Real Time ROLAPOLAP是适时的，明细数据和聚集都以关系数据的方式保存。主要在数据源频繁修改，但查询时需要最新数据时使用，这种方式查询速度最慢。但数据库中的数据改变时，分析报表不用处理（PROCESS），数据会刷新，但只能用在SQL SERVER 数据库上。2）Real Time HOLAP3）Low Latency MOLAP4）Medium Latency MOLAP5）Automatic MOLAP6）Scheduled MOLAP7）MOLAP4.3.3 定义聚集（Aggregations）可以预先定义聚集，提高查询速度。4.3.4 定义计算关键指标4.3.5 定义Drill Through使用关系SQL机制，钻到数据立方体的底层，到后端关系表。4.3.6 定义层次一个CUBE里一个维度可以定义多个层次可以定义父子维度（存储层次结构数据的一种方式）4.3.7 关键绩效指标(KPI)Analysis Services 2005 为服务器端计算定义引入了关键绩效指示 (KPI) 框架，用来衡量您的业务。这些 KPI 将通过数据访问 API 和 Microsoft 与第三方工具，被显示在报告、门户和仪表板中。不同的评论员和供应商用缩写 KPI 指代不同的概念。对于 Microsoft SQL Serv