Searching for an Oracle Discoverer like tool

Anyone know of an ad-hoc query tool that makes querying a star schema possible for people who just can't do SQL?

Oracle Discoverer does a fine job for many analysts at our company. We also have analysts who can write SQL. But some prefer the old Discoverer desktop application. Problem is Discoverer desktop is dead (now Oracle is saying go with OBIEE). Instead, we use Microsoft BI. But there's really nothing that provides that simple ad-hoc query functionality in the Microsoft BI stack.

For those not familiar with Disco, all the tool does is walk the user through a 4 step wizard. Step 1 selects columns from tables (dims and a single fact table); in Step 2 you to place the columns as rows or columns - the measures default to sum() data points (ala excel pivot table); Step 3 allows you to add filters; and Step 4 allows you to add calculations. It then fires a query at the database that looks much like the famous standard template query from edition 1 of Ralph's DW Toolkit:

SELECT p.brand, sum(f.dollar), sum(f.units)
FROM salesfact f, product p, time t
WHERE f.prductkey = p.productkey
AND f.timekey = t.timekey
AND t.quarter = ’1Q1995’
GROUP BY p.brand
ORDER BY p.brand

We have star schema data marts in Oracle, and load that into SSAS cubes upon which our Microsoft Performance Point dashboards rest. We also use SSRS reports which query the Oracle star schemas directly. The excel drill down functionality against an SSAS cube is the closest thing to Disco that we use in the Microsoft BI stack. But some analysts still like using Disco to go directly against the Oracle data. Once those analysts move to Windows 7, getting them Disco Desktop becomes a real problem.

Any suggestions welcome.

smollig wrote:Anyone know of an ad-hoc query tool that makes querying a star schema possible for people who just can't do SQL?

There are a lot of tools out there. How much do you want to spend? The big commercial tools include Cognos, Mictrostratgy and Business Objects, there are a lot of commercial 2nd tier products as well a many more freeware tools.

ngalemmo wrote:How much do you want to spend?

Yeah - not that much. Our major BI tool investment is in the Microsoft BI stack. So a Microstrategy or BO would be out of the question. But we wouldn't have too much trouble coming up with $2k per user or so. Really looking for something for a small number of users - under 20. The majority of the business is served well by the Microsoft BI tools we've invested in.

Microstrategy is free up to 100 users.

BoxesAndLines wrote:Microstrategy is free up to 100 users.

Good point - that is an option...

Good example of the strategic importance of the technology selection process.

If ad-hoc reporting was an anticipated functional requirement, then the decision to invest in microsoft may have been different.

The equivalent tool to Disco in the MS BI stack would be Report Builder:
http://technet.microsoft.com/en-us/library/dd220460

If you are on SQL 2012 and SharePoint 2010 then Power View is also an option:
http://technet.microsoft.com/en-us/library/hh213579

Personally I prefer to push Excel Pivot Tables on SSAS as far as I can, by building rich detailed dimensions (lowest granularity possible for the key, with many attributes). For a detail report they add filters and measures first, then add the "Key" attribute e.g. to rows, then add detail attributes as properties. Learning curve for report authors is very low.

In my experience this is a safer environment as with all the SQL-generating tools (e.g. Report Builder, Disco, Cognos, Microstrategy, BO etc etc) it's very very difficult to stop them generating SQL queries that kill your servers and/or return incorrect results. With SSAS you can correctly model complex relationships and avoid aggregation issues etc - with fast performance that scales.

Good luck!
Mike