报告工具/查看器大数据集 [英] reporting tool/viewer for large datasets

问题描述

我有一个数据处理系统，可以对其处理的数据生成非常大的报告。 大我的意思是这个系统的小执行会在转储到CSV文件中时产生大约30 MB的报告数据，大数据集约为130-150 MB（我相信有一个人有更大的想法的大，但这不是要点...;）

Excel具有以数据列表形式的报告消费者的理想界面：用户可以过滤和细分数据即时查看他们感兴趣的具体细节（因为他们对数千行并不感兴趣，他们知道如何应用多个过滤器来获取他们想要的数据） - 他们可以还向报告添加笔记和标记，创建图表，图表等...他们知道如何做到这一点，如果我们给他们的数据，让它们更容易实现。

Excel对于小型测试数据集非常有用，但无法处理这些大型数据集。有没有人知道可以提供与Excel数据列表类似的界面的工具 - 能够动态创建和更改多个字段的过滤器;但可以处理更大的文件？

我尝试使用的下一个工具是MS Access，发现Access文件非常庞大（30 MB输入文件导致大约70 MB的Access文件，当我打开文件，运行报告并关闭它的文件在120-150 MB！），导入过程很慢，非常手动（目前，CSV文件由同一个plsql脚本创建，运行主进程，所以没有干预对我而言）。我还尝试了一个Access数据库，其中包含存储报告数据的数据库表的链接表，并且速度慢了许多倍（由于某种原因，sqlplus可以在一分钟内查询和生成报告文件，而Access将从2- 5分钟的相同数据）

（如果有帮助，则数据处理系统以PL / SQL编写，并在Oracle 10g上运行。）

解决方案

在这种情况下，访问将是一个很好的工具，因为它与excel不同，它没有实际的行限制。当他们习惯于定制过滤器的功能时，很难断绝人们的擅长。非常有可能获得近似的东西，但是除非您将Excel控件嵌入到表单中，否则它将永远不会完全相同。

至于手册部分，您可以使用VBA将数据库脚本导入文件。例如，让我们说，这个主要任务是将文件过夜转储到每天晚上有新文件的文件夹。您可以创建一个看门狗访问数据库，其表单打开，OnTimer事件将在几分钟内查找该文件夹，当它找到一个新的文件，它开始导入。当您的用户在早晨工作时，数据已经加载。

至于腹胀，是的，这可能是一个问题，但您需要做的只是一个快速压缩和修复文件，它会收缩。

编辑：

您可以通过选项设置要紧凑的访问数据库。我不能确切地记住它在哪里，在工作中我们只能访问97（但是奇怪的是Office 2003）。另一个选择是通过代码进行压缩。这是一个链接，以解释如何

http://forums.devarticles.com/microsoft-access-development-49/compact-database-via-vba-24958.html

I have a data processing system that generates very large reports on the data it processes. By "large" I mean that a "small" execution of this system produces about 30 MB of reporting data when dumped into a CSV file and a large dataset is about 130-150 MB (I'm sure someone out there has a bigger idea of "large" but that's not the point... ;)

Excel has the ideal interface for the report consumers in the form of its Data Lists: users can filter and segment the data on-the-fly to see the specific details that they are interested in (because they're not really interested in the many thousands of rows, they know how to apply multiple filters to get the data they want) - they can also add notes and markup to the reports, create charts, graphs, etc... They know how to do all this and it's much easier to let them do it if we just give them the data.

Excel was great for the small test datasets, but it cannot handle these large ones. Does anyone know of a tool that can provide a similar interface as Excel data lists - the ability to dynamically create and change filters on multiple fields; but can handle much larger files?

The next tool I tried was MS Access, and found that the Access file bloats hugely (30 MB input file leads to about 70 MB Access file, and when I open the file, run a report and close it the file's at 120-150 MB!), the import process is slow and very manual (currently, the CSV files are created by the same plsql script that runs the main process so there's next to no intervention on my part). I also tried an Access database with linked tables to the database tables that store the report data and that was many times slower (for some reason, sqlplus could query and generate the report file in a minute or soe while Access would take anywhere from 2-5 minutes for the same data)

(If it helps, the data processing system is written in PL/SQL and runs on Oracle 10g.)

解决方案

Access would be a good tool to use in this case as it has no practical row limit unlike excel. The hard part is weaning people off excel when they are used to the power of custom filters. It is very possible in access to get something that approximates this but its never going to be exactly the same unless you embed an excel control into your forms.

As for the manual part, you can script the database to import files in using VBA. For example lets say this main task of your dumps the files in overnight to a folder with a new file each night. You could make a "watchdog" access database that has a form open with an "OnTimer" event that looks at that folder every few minutes, when it finds a new file it starts the import. When your users get to work in the morning the data is already loaded.

As for the bloating, yes it can be a problem however all you need to do is a quick compact and repair on the file and it will shrink it down.

EDIT:

You can set an access db to be compacted on close through the options. I cant remember exactly where it is and at work we only have access 97 (but oddly enough office 2003). The other option is to compact through code. Here is a link to explain how

http://forums.devarticles.com/microsoft-access-development-49/compact-database-via-vba-24958.html

这篇关于报告工具/查看器大数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！