Can someone please give me an example of a cross-tab type
query in MDX where the pivot data is not a measurement?
I have a cross tab set up in Access and I am wanting to
migrate it to MSAS but I don't know how.

My problem is that I need one of the non-measurement
dimensions to be the Measurement.

The flattened rowset looks like:
column row row value
----------------------------------------------------------
products channels $50RevenueBands $50MarginBands

The problem is that 50MarginBands is a dimension itself
(not a true measurement like revenue, qty, margin, etc...)

Any ideas are much appreciated.

I am pasting this from previous thread , may be you can make something out
of it.
<BEGIN>
I'm interested in views on storing non numeric measures; if this isn't a
contradiction in terms!

Here are a couple of examples:
1. When someone takes a slice of a cube, I want them to be able to read an
explanation of the number(s). So the Finance Director, might have a slice of
sales
income (actual, forecast & variance) by product; because someone has already
looked into the figures, the shortfall in Motor insurance income is due to
prices
for comprehensive policies being put up. I want this explanation to be in a
non-numeric measures box. Sure this "measure" is suppose to be a
SUM/MAX/MIN/COUNT
of the measures that comprise it, & this explanation is likely to be at an
aggregated level.

2. At the leaf level along with real measures, non-numeric comments are
wanted to be
stored. Therefore, these "measures" are not aggregated & are meaningless
when
aggregated slices are produced, but when they drill-down to the leaf-level
they
can read the comments.

SQL Server/Analysis Services doesn't allow non-numeric measures. Do other
systems?
Are there serious design issues here?

How have you dealt with this problem?

Regards,

John


Mosha Pasumansky
Jun 12 2002 6:12 There are couple more ways to do it with Analysis
Services. Reposting my answer to
the microsoft.public.sqlserver.olap newsgroup

================================================

There is a way to do it in Analysis Services by using calculated cells.
Since
Analysis Services 2000 doesn't allow custom cell properties, you can hijack
one of
the rarely used predefined cell properties, for example FONT_NAME.

Here is how your example will look like:

CREATE CELL CALCULATION Sales.Comment4 FOR
'({[Time].[1998].[Q1].[3]},{[Product].[All Products]})' AS '
CalculationPassValue(Measures.CurrentMember, -1, RELATIVE) ', FONT_NAME = '
"We put
prices up too much" ', CALCULATION_PASS_NUMBER='2'

Now, during query you need to ask for the FONT_NAME like following:

SELECT {[Time].[1998].[Q1].[3]} ON 0, {[Product].[All Products]} ON 1 FROM
Sales
CELL PROPERTIES VALUE, FORMATTED_VALUE, FONT_NAME

If you execute this query in MDX Sample, and double click on the cell, you
will see
"We put prices up too much" as FONT_NAME cell property.

Another approach would be to use actions. Actually this might be a better
way,
because then you don't need to modify queries, and there are several 3rd
party tools
which support actions built-in. Since you seem to be Excel Add-In fan, you
can add
support for actions into Excel XP. Basically you can create cell action for
the
interesting cell, and set the action type to HTML. Then the text of the
action can
be HTML fragment, not just plain text.

--
==================================================
Mosha Pasumansky (moshap at microsoft dot com) Development Lead in the
Analysis
Server team All you need is love (John Lennon) Disclaimer : This posting is
provided
"AS IS" with no warranties, and confers no rights.
==================================================


Nigel Pendse
Jun 12 2002 6:12 "John Keeley" wrote in message
news:7454b5cb.0206110147.125ddf19 (AT) posting (DOT) google.com...
Yes, some other OLAP servers do allow text to be stored in cells. As I'm
sure you
know, TM1 allows short text strings to be stored and retrieved in a fairly
simple
way, and Express supports more elaborate methods that allows lengthy blocks
of text
as well as short strings.

With Analysis Services, you'd have to engineer it using SQL Server
relational tables
-- possible, but more complex, and the average off-the-shelf client tool
couldn't
integrate it anyway. But some pre-built apps deal with this themselves, so
you can
enter explanatory text that's entered and displayed along with the numbers.
There are
also, of course, many other advantages of using pre-built apps rather than
the DIY
approach (and you don't have to give up Excel as a front-end either).

Nigel Pendse OLAP Solutions http://www.olapreport.com


John Keeley
Jun 13 2002 6:13 Thank you Nigel & Mosha,

The long term answer I would like to hear is that non-numeric measures will
be
available in the next release of AS. Any chance?

In the meantime I could write the comments into the static reports.
Preferable to
buying another application just for this.

It's good to see TM1 still have some advantages. Someone please buy it &
make it a
success!!! It's a shame Microsoft didn't buy it instead of AS.

Regards,

John

"Mosha Pasumansky [MS]" wrote in message
news:<3d06c421$1 (AT) news (DOT) microsoft.com>...
</BEGIN>
"jason" <jmcguire_online (AT) hotmail (DOT) com> wrote

One option here would be to use ACTIONS. Store the comments data in a
relational database somewhere and build an action in Analysis Services that
will display the commented data.

Sean


--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

"akshayk" <akshay349 (AT) hotmail (DOT) com> wrote