Reporting tool for OLAP, *not* OLTP!

Question

I'm looking for a control that I can put on top of an already existing OLAP star schema to allow the user to define their own "queries" and generate reports. Right now I have some predefined reports built on top of the cubes, but I'd like to allow the user to define their own criteria based on the cubes that I've created. I've found lots of products that will allow you to treat a transactional table like an OLAP cube, but nothing specifically for pre-existing cubes.

EDIT: Let me be clear, I know there are countless reporting tools out there that claim to report on OLAP cubes. The problem is they all assume they are looking at transactional data and try to create their own cubes. I have tables that contain tens, if not hundreds of millions of records. Most tools crash when handling this much data, the others just run incredible slowly. I don't want a tool that is targeting the business people.

I want a tool that understands what a star and snowflake schema is. I want to be able to tell it what the fact tables are and what the dimension tables are, and then creates a UI on top of them. This is an easier problem to solve for the tool vendor because I am spoon feeding them the cubes. I want to rely on the fact that cubes are a standardized pattern and I want a tool that takes advantage of this fact. I want a tool that targets developers and starts with the assumption that I actually know how to manage my data, it just needs to build pretty reports for me and not crumble under the weight of my data.

Answer 1

Stefan, if your end-goal is for reporting I would suggest a tool like Tableau. It is very simply to learn for the average user and it allows them to make reports on the fly using the interface or type custom queries against the database. If you are looking for something more advanced I would suggest a product like CockPit (I know...) developed by a German based company that is also really neat and starting to take off.

Answer 2

Well SSRS for SQL Server is designed to be used with cubes (we query our data warehouses with this all the time), but that is a vendor specific implementation and if you don't have SQL server you can't use it as it comes with SQL Server. You can write MDX queries and not just straight t-sql. I know you can reference other databases in queries, but I have never tried to reference a different vendors' tables becasue our data warehous in a SQL Server data warehouse.

Answer 3

I've used various reporting tools, but by far the most familiar to the end users is MS Excel Pivot Tables. Please follow this link to further information.

Answer 4

If you want a full enterprise-level software, you're looking for a tool like the Cognos(IBM) reporting suite. Their tools have a single query framework for reporting against both relational and OLAP sources, depending on how you model it. That means you can do multidimensional reporting against SQL server and MSAS with the same tool if it's modeled properly. They support their own cubes, TM1, ORacle, Microsoft, SAPBW, etc... There's an sdk you can use to write your own front-end tool as well. Not cheap, but nothing enterprise is.

Competitors in this field (I haven't used) are Business Objects(SAP) and Hyperion(Oracle). And maybe JasperSoft (open source).

Also check out the answer to this question. He mentions BO and a Microsoft solution.

Answer 5

You could try Dundas solution which allows end users to create their own reports based on dimensions and measures created in the cube.

Answer 6

Are you after something like Mondrian? We used it on a previous project, and I seem to recall its OLAP reporting was pretty good. Can't remember if it had a gui, though; that wasn't my area. I only ever messed with the config files...

Answer 7

Hi you can use SSRS where user can build reports just browsing the cube or u can write custom mdx queries using the OLAP advantage. You can use SQL Server Report Builder And last not least Microsoft Excel 2007 has good capability of browsing a cube You can even expose your cube in SharePoint via Microsoft excel services 2007

Answer 8

I hold my hands up and freely-admit that I'm not an expert in this domain, but you could have a look at InfoBright. I understand it will still go off and index your existing data (I'm not sure if it forms its own cubes), but I'm led to believe it can handle massive amounts of data quickly.

There's a community edition and a commercial edition too. I've never used the software, but from what I've read about it before, it seems impressive.