can OLAP/cubes do this?

[BoulderRidge]

I've been tasked to rebuild an existing reporting tool on an ASP.NET/SQL Server 2000 platform. The existing tool has the data and user interface in Access 2000 and the reports in Excel 2000. I'm trying to determine whether I can utilize Analysis Services here to improve performance or flexibility. Some complexity to consider:

1) The Access interface allows selection of up to 3 nested row display items (ie. show states and fiscal quarters as rows) (this looks easy in AS)

2) The Access interface allows multi-select filtering on up to 15 different data elements. Filters are multi-select list boxes, so you could pick to include 3 states, 5 facilities, and 10 Diagnosis codes as an example combination. Filtering is independent of the row display items selected. Access builds a dynamic query based on 1) and 2) selections and Excel grabs the query and formats the data.

3) ***The Excel report layout is NOT a pivot table style. In a report with 9 columns of numbers, each column is a different measure, sometimes from different tables.***

4) The Excel report contains custom "tools" that switch the user between counts, percents, rates per thousand, charts, etc.

I am new to OLAP but in my research it seems everything is based on the pivot table functionality, which does not match my needs. I also have trouble seeing how to put the power of Analysis Services behind a controlled user interface with pre-defined report layouts. Also can ASP.NET read directly from a cube?

Sorry if these are dumb questions, thanks for any advice!!

--BoulderRidge

 

[Flybridge]

It is possible to have data from multiple sources that can be combined into a cube, so point 3 is not a problem with the right planning and design.

The other points are more points for the reporting tool, rather than AS. IMHO it is not worth spending time developing your own reporting tool when there are so many out there. Your time might be better spent evaluating what tools can do what and choosing the one that enables you to do the above.

The last I knew you are looking at over 80 such tools - so plenty choice

Flybridge

 

[BoulderRidge]

Thanks for the advice Flybridge.

We are evaluating Crystal Reports as a possible tool. Part of my confusion is over the number of layers here. What does Analysis Services do for me if I have to put another reporting tool on top of it anyway? Do these reporting tools directly access the cubes created by AS using MDX queries? Or what do they do?

Does anyone know a reporting tool that would fit this situation and would specifically give me interactive functionality beyond just cube drill-downs, like ways to click and sort or view rates or percents vs counts on the same report layout? I also need to control how certain columns calculate subtotals and grand totals depending on my data. These requirements are what drove us to use Excel for the initial development.

Thanks again! --BoulderRidge

 

[Flybridge]

As it happens, I have just started using Crystal Reports myself, so I think I can answer some of your questions.

Analysis Services creates an OLAP cube from your source data. This means you can then use an OLAP tool, such as Crystal Analysis or Cognos PowerPlay or ProClarity etc etc to perform slice and dice type analysis.

One area where these tool sets differ is the ability of the reporting tool to access OLAP cubes. The last I knew Cognos Impromptu couldn't read an OLAP cube (but I'm not familiar with ReportNet, the latest incarnation of their reporting tool) but Crystal Reports can. Therefore, you could use Crystal Reports to either report from your source directly or read preaggregated data from the OLAP cube. This has the advantage of allowing you to insert irregular sub totals, such as those on a Profit & Loss account. I believe Crystal Reports uses SQL via ODBC to do this.

The OLAP tools generally allow you to sort as well as drill down etc, but check for this in the product spec. As regards showing different measure types, most (all?) OLAP tools allow the presentation of more than 1 measure at once but they all do it differently. Most also allow you to create measures, such as percentages, that don't exist in the underlying cube.

I think the best thing is to get a few vendors in to demo their products. Then you will get a feel for what the market can offer and which product is more suited to the way you want to work.

In particular, you need to know how you want the users to interact with the reports. Are you going to provide all the authoring support or will you allow them to create their own ad hoc reports &/or develop/maintain the regular reports?

 

[BoulderRidge]

Thanks, that is helpful. We will explore Crystal more carefully. We don't really have time to do a full-fledged product search but if you know of anything that seems like a really good fit I will look into it.

The users interact with the reports in a very controlled way. They use a list-driven user interface to make their report, row, and filter selections (currently in Access, moving to ASP.NET?). Then the data is queried and the report is displayed per their selections. Once the report is on screen, the user can interact with it by sorting, switching between counts and percents and rates, and graphing selected columns. None of this requires requerying the data. If they want to see different rows or filters they go back to the selection interface and change selections and a new query and report runs with the new selections. In the Access/Excel environment they can have many reports open at the same time and switch between them.

We do not currently want to give the user the ability to change which measures are presented on a given report, just which way they are sliced. The audience is not necessarily familiar enough with all the data to safely/accurately create their own report layouts at this time. Part of the value we provide is showing them what numbers to look at and how to look at them.

Appreciate your insight and feedback! --BoulderRidge

 

[JohnEck]

I didn't particularly like crystals OLAP tool. I thought it offered very little power or flexibility. I di find an add-in for excel cal XLCubed that is very useful if you are developing your cubes in AS. It is very low cost also. Some licenses as low as $150. That is a stripped down version of their $500 per seat product. The beauty is you can build the report but those viewing it do not need the tool to view it.