Which OLAP tools allow attributes as row headers

[jnicho02]

Most OLAP tools produce tables with dimensions on the x and y axis and measures in the body. If I have a star schema, my outrigger tables generally hold an id, a short name, a long name, and various attributes such as groups, pack sizes, colour.<br>
Most tools let me filter on attributes but don't allow them as part of the table.<br>
In 'The Data Warehouse Toolkit' by Ralph Kimball he talks about using attributes as row headers.<br>
<br>
So far I have got Business Objects to do it. Any others?

 

[SASMAN]

Buddy, Have I got good news for you.<br>
If you think about multidimensional databases and OLAP what you have is a bunch of dimensions or qualitative attributes. These dimensions define your crossings in the MDDB (MultiDimensional DataBase). Generally these attributes are of character or date data types. Further, there may be a hierarchy associated with a group of these attributes e.g. day, month, quarter, year. or employee, team, section, department, division, company <br>
<br>
Then you have the quanititative attributes or measures which as you have probably guessed are of a numeric data type. Now I dont want to complicate things too much at this stage, but I have to mention that some of these numeric measures may be what is known as "additive" and some may not not. Ask me if you want to know more ( loginjames@extra.co.nz)<br>
<br>
When it comes to viewers for slicing and dicing this type of info ( we're not just talking about text here, we're talking about graphs, charts, maps, org charts and tables) where traffic lighting, drilldown, expansion, subsetting by dimension and many other customisations are available there are few if any vendors that can compete with SAS Institute in this regard. See

http://www.sas.com.

Sorry did I forget to mention that this functionality is also WEB enabled?

 

[jnicho02]

Can I take it that you like SAS ;-)<br>
<br>
I've been thinking about my question and have further thoughts...<br>
<br>
If I want to have something as a row header then it needs to be either a dimension or a hierarchy of a dimension. Therefore, products that allow me to have qualitative attributes (e.g. Oracle Express), but don't treat them as hierarchies, can only filter on them - this is because they haven't rolled-up the data on that attribute.<br>
<br>
if the normal rollup is....<br>
<br>
SELECT d1.Name, d2.Name, count(*)<br>
FROM Fact_table f, Dim1 d1, Dim2 d2<br>
GROUP BY d1.Name, d2.Name<br>
<br>
(2 dimensions)<br>
<br>
to use an attribute of PackSize as a row header the tool has to generate...<br>
<br>
SELECT d1.Name, d1.PackSize, d2.Name, d1.PackSize, count(*)<br>
FROM Fact_table f, Dim1 d1, Dim2 d2<br>
GROUP BY d1.Name, d1.PackSize, d2.Name, d1.PackSize<br>
<br>
(4 dimensions)<br>
<br>
I expect that MOLAP tools such as Oracle Express aren't keen on me adding 2 dimensions to the rollup query as they carry out one rollup operation with all the dimensions. Adding 2 dimensions makes it becomes a much larger query.<br>
<br>
ROLAP tools such as Business Objects carry out many rollups on demand. The user will only be asking for the dimensions that they want and may also limit the rollup with a WHERE clause, making the result set and calculation manageable.<br>
<br>
This is why Business Objects allowed me to use the attributes as row headers and Oracle Express didn't. It's a MOLAP/ROLAP thing...