Best practices on on implementing pivot tables on top of Oracle DB with large datasets
my team is currently looking at how to best implement slice & dice pivot tables for a .NET app that is built on top of an Oracle DB (12c). We are dealing with 3+ million transaction records but don’t yet have any data-warehouse and/or OLAP cube in place. Will the simple “optimized CSV” approach of Flexmonster scale with a dataset this large? In general, what are the best practices you guys would recommend for such a setup?
Thanks in advance,
Thank you for your questions. We have collected all our experience and shared it in the following article – http://www.flexmonster.com/blog/blog-perform-faster/. It is a little bit outdated but the key ideas are still relevant. Speaking about your case, you need to choose between two options.
- First one is the SQL database as a storage (for example your Oracle DB) and our Flexmonster Compressor tool. This tool was specially created by our developers to give the better performance using SQL databases. Please find information about it in our documentation – http://www.flexmonster.com/doc/connecting-to-relational-database/. Also please have a look at this article – http://www.flexmonster.com/blog/sql-pivot-table-best-performance/. We tried to sum up there the best practices when using such approach.
- The other option is the OLAP cube and our Flexmonster Accelerator. We recommend such approach when it is not possible to achieve good performance using SQL database and Flexmonster Compressor. Please find more information about the accelerator here – http://www.flexmonster.com/blog/flexmonster-data-speed-accelerator-for-olap-cubes-we-show-multidimensional-data-10-times-faster/. We have all the documentation of how to use our Flexmonster Accelerator with Microsoft Analysis Services and Pentaho Mondrian available on our website.(http://www.flexmonster.com/doc/connecting-to-microsoft-analysis-services/, http://www.flexmonster.com/doc/connecting-to-pentaho-mondrian/)
Please let us know if you have additional questions here.