SSRS 报告生成器 - 经验中的问题？
我正在研究向最终用户推出基于 Web 的 SSRS 报告生成器的想法，以允许他们根据我们的生产应用程序数据库创建自己的报告。 从我到目前为止所看到的来看，这个工具比 VS Biz Intel Studio 报表设计器更容易使用，而且它更容易安装，并且部署报表对于最终用户来说更容易理解（加上最大的事情是没有 SQL我猜）。
对于给予用户这种权力的陷阱，有人有什么想法或经验吗？ 现在，我们收到很多将数据导出到平面文件的请求，以便他们可以读取数据，然后在 Access 中针对它构建报告，所以我认为 SSRS 会比 Accesss 更好...
I'm looking into the idea of rolling out the web-based SSRS Report Builder to our end users to allow them to create their own reports against our production application databases. From what I have seen so far, this tool is easier to use than the VS Biz Intel Studio report designer, plus it is easier to install, and deploying the reports is much more understandable for an end user (plus the biggest thing is no SQL I guess).
Does anyone have any thoughts or experience on the pitfalls of giving users this kind of power? Right now, we get a lot of requests to export the data to a flat file so they can read it in and then build reports in Access against it, so I figure SSRS would be better than Accesss...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
报表模型设计的一些技巧：
1. 构建数据集市
有多种工具，例如 Report Builder：Business Objects、Oracle Discoverer 等。 它们都具有元数据层，可以让您以某种方式使用最终用户报告工具，但是它们仍然需要以合适的格式填入数据才能产生有效的解决方案。 这意味着您确实需要考虑构建某种数据集市。
如果没有干净的数据，这些工具将暴露生产数据库中的所有问题，因此用户必须理解这些问题才能获得正确的结果。 这意味着报告确实应该来自干净的数据源。
您对这些工具生成的 SQL 的控制几乎为零，因此它们非常有能力生成会影响您的生产数据库的查询。 这意味着您的报告应该在单独的服务器上进行。 对临时工具友好的模式（例如星型模式）将缓解最严重的潜在性能问题。
2. 清理数据
没有开发人员使用临时工具，因此用户会天真地使用该工具，而不知道数据问题是什么。 不准确的查询结果将始终被视为工具的故障。 为了提高可信度，需要从工具上游的数据集中消除这些陷阱。
3. 使导航功能强大且防白痴
报表生成器可以设置从一个实体移动到另一个实体的限制。 如果没有这些，就可以以 am:m 关系将多个表连接在一起。 这称为粉丝陷阱，并且将返回不正确的总计。 您需要设置模型，以便各个事实表在公共维度上聚合 - 即在连接之前进行汇总。 正确地做到这一点可以消除一类错误。 大多数工具都有一些机制来防止这种情况发生。
4. 进行数据聚合
您可以从 Business Objects 免费获得此数据，但您必须使用报表生成器在每个基本度量上显式放置聚合度量。 隐藏基本度量并暴露聚合。 这意味着系统会将数据汇总到用户选择的维度粒度。
结论
将临时工具直接放置在生产数据库上不太可能发挥良好作用。 数据会有太多陷阱，而且模式不适合报告。 这意味着您需要构建一个数据集市来清理数据并为该工具做好准备。 如果您花费大量时间构建临时数据提取，那么可能只是在开发人员时间中存在一个业务案例，这将在以后节省时间。
编辑：报表模型向导（像大多数此类东西一样）在运行时会变得非常混乱。 您必须调整设置，例如限制不相关聚合的生成。 过去，我通过生成总和、隐藏所有基本度量并公开聚合（就好像它们是基本度量一样）获得了相当好的结果。 这使得行为非常类似于 Business Objects。 在特定情况下，您可能还想公开计数、最小/最大或平均值。
我想到的特定实例是一个相当大的报告模型，其中包含大约 1,500 个字段，因此向导生成的聚合盛宴难以管理，总共有 10,000 多个字段。 您还可以设置类似于 Analysis Services 的文件夹结构，并使用它们来组织字段。 最后，如果输入了字段上的描述，则当您将鼠标悬停在最终用户工具中的字段上时，该描述将显示为工具提示。
Some tips for report model design:
1. Build a data mart
There are several tools like Report Builder: Business Objects, Oracle Discoverer to name a couple. They all have metadata layers that get you some of the way to an end-user reporting tool, however they still really need to be spoon-fed data in a suitable format in order to produce an effective solution. This means that you really need to think in terms of building some sort of data-mart as well.
Without clean data, the tools will expose all of the gotchas in the production database, so users will have to understand these to get correct results out. This means that the reporting should really come off a clean data source.
You have approximately zero control over the SQL that these tools produce, so they are quite capable of producing queries that will herniate your production database. This means that your reporting should take place on a separate server. A schema that is friendly to ad-hoc tools (such as a star schema) will mitigate the worst of the potential issues with performance.
2. Clean the data
There is no developer in the loop with ad-hoc tools, so users will naively use the tool without knowing what the data issues are. Inaccurate query results will always be viewed as the fault of the tool. For credibility, these pitfalls need to be eliminated from the data set upstream of the tool.
3. Make the navigation robust and idiot-proof
Report builder can set up restrictions on moving from one entity to another. Without these, it's possible to join multiple tables together in a m:m relationship. This is called a Fan Trap and will return incorrect totals. You need to set up the model so that individual fact tables are aggregated on common dimensions - i.e. rolled up before they are joined. Getting this right eliminates a class of errors. Most tools have some mechanism for preventing this.
4. Make the data aggregate
You get this for free from Business Objects, but you will have to put an aggregate measure over each base measure explicitly with Report Builder. Hide the base measures and expose the aggregates. This means that the system will roll up the data to the grain of the dimensions the user has chosen.
Conclusion
Placing an ad-hoc tool directly over a production database is not likely to work well. The data will have too many pitfalls and the schema will not lend itself to reporting. This means that you are up for some work building a data mart to scrub the data and prep it for the tool. If you are spending significant time building ad-hoc extracts, there might be a business case simply in the developer time this would save later on.
EDIT: The Report Model Wizard (like most such things) makes quite a mess when run. You'll have to tweak the settings such as restricting the generation of irrelevant aggregates. In the past I've had quite good results by generating sums, hiding all of the base measures and exposing the aggregates as if they were base measures. This gave behaviour much like Business Objects. On specific instances you might also want to expose count, min/max or averages as well.
The particular instance I'm thinking of was quite a large report model with about 1,500 fields in it, so the aggregate-fest generated from the wizard was un-manageable with 10,000+ fields in total. You can also set up folder structures a bit like Analysis Services and use these to organise the fields. Finally, if entered the description on the field will show up as a tooltip if you hover over it in the end user tool.
Just a few comments on the previous answer:
1. The semantic query model used by SQL Server Reporting Services Report Builder was designed with the explicit intent of preventing Fan Traps/incorrect totals on m:m relationships. No extra effort is required to enable this functionality; it is inherent in the structure of queries generated by Report Builder.
2. The model wizard creates aggregate measures over numeric fields by default, so no extra effort is required to expose aggregates. You may customize the model by adding or removing aggregate calculations as appropriate.
总的来说，“垃圾进垃圾出”这句老话确实适用。 如果您的数据不干净，那么报表生成器或其他临时报告工具只会使这一点变得更加明显。
Aaron Meyers
Software Development Engineer, SQL Server Reporting Services
Just a few comments on the previous answer:
1. The semantic query model used by SQL Server Reporting Services Report Builder was designed with the explicit intent of preventing Fan Traps/incorrect totals on m:m relationships. No extra effort is required to enable this functionality; it is inherent in the structure of queries generated by Report Builder.
2. The model wizard creates aggregate measures over numeric fields by default, so no extra effort is required to expose aggregates. You may customize the model by adding or removing aggregate calculations as appropriate.
Overall, the old adage "garbage in garbage out" certainly applies. If your data is not clean then Report Builder or other ad hoc reporting tools will just make that more apparent.
Aaron Meyers
Software Development Engineer, SQL Server Reporting Services