My client has purchased the adapter for MS SQL Server as well as the adapter for MS SQL Server Analysis Services (cubes).

Besides the basic install manuals, I can find precious little in the way of guidelines or "best practices" to determine when I should direct my requests to a cube as the source or when I should point to the base sql tables/views. If someone can locate an ibi doc to this affect, TIA.

This seems to me a very timely topic with cubes all the rage these days. Since there doesn't appear to be a thread on this one let's get this discussion going here.

I would sure appreciate any input in the way of analysis/benchmarking or just general experiences in the area of cubes vs tables/views. I'm particularly interested in performance considerations, but if you know of any pitfalls, war stories, or just general advice you can share, please do.

Kindly,
Commando Dave

This message has been edited. Last edited by: Commando Dave,

The principle reason for using cubes is because the business logic is already built in if you use
Properly constructed queries, additionally the data is normally aggregated to the required reporting levels.

As a general rule use cubes for summary reports and source data for detail transaction type reports.

OK this is amazing! I have found a topic where you usually talkative people have nothing to say. I am guessing this is indicative of nobody having any experience to share...

JG, are you speaking theoretically or have you actually done some work with cubes vs tables.

Once I get things set up here next week, I plan to report back with some empirical results and I'll let you know if I run into any snags as my work progresses.

-Commando Dave

Our experience using MSSQL Analysis Services was mixed. AS JG states, the business logic built into the cubes and the aggregation "pre-performed" was useful and pretty efficient. However, the financial data we were reporting from (Walker Financials which has been bought and sold a couple of times since) was for company financial statement and required the use of WebFOCUS' FML functionality. We could NOT get it to work properly based on how Walker was telling us to use the cubes. So we ended up flattening out all the data required for the needed account hierarchies, then joining back to the cubes for the finacial data

For other reasons, we ended up dumping the Walker Financials project altogether so all my work was for naught. My understanding of the cubes was only from what Walker was telling me (which didn't always seems to be very clear or consistent) so I don't know if our problem were from lack of proper design, lack of understanding of the design, or if there was some real issue with the cubes and FML functionality. Most like one of the first two.

I have some limited experience with Essbase and I have found that you have to provide a WHERE clause for every dimension. Kind of a pain.

From experience, I've done a little bit with the MS Analysis server and MSSQL
and a great deal with SAP BW and R/3

quote:

Originally posted by Commando Dave:
My client has purchased the adapter for MS SQL Server as well as the adapter for MS SQL Server Analysis Services (cubes).

Besides the basic install manuals, I can find precious little in the way of guidelines or "best practices" to determine when I should direct my requests to a cube as the source or when I should point to the base sql tables/views. If someone can locate an ibi doc to this affect, TIA.

This seems to me a very timely topic with cubes all the rage these days. Since there doesn't appear to be a thread on this one let's get this discussion going here.

I would sure appreciate any input in the way of analysis/benchmarking or just general experiences in the area of cubes vs tables/views. I'm particularly interested in performance considerations, but if you know of any pitfalls, war stories, or just general advice you can share, please do.

Kindly,
Commando Dave

from my limited knowledge what you need to think about is how the data will be assembled and accessed- if its lots of infrequently queried historical data then there are certain ways you should store it cubewise, like JG says you want cubes for summary data and tables/views for details but there are different storage options (HOLAP, ROLAP, MOLAP) for different data requirements and i'd seriously consider whether you actually need to use a cube at all before anything else...

do you have loads of data that is accessed at the summary level regularly?

is it slow to access this summary detail?

how often is the detail drilled into?

cubes to me often mean you have so much data that its too slow to simply summarize it as its read from the table- this is generally where cubes come in, again i feel you may get a better reponse from a MSDN or SQL forum...