sql as 2k, sp3a.

I'm sure this is something simple, but I'm not finding how to do it. I have
a dimension called Price. Assume I construct a cube as follows:

row dimension: price
column dimension: system
measure: buys

When I view a cube, I'd like to only show Price if it has data in it. I'm
getting several blank rows showing up and I'd like to suppress them. Can
someone tell me how I do this?

Thanks, Andre

Assuming that you are viewing the cube data using a tool that allows you
to customize the MDX query, then like:

NON EMPTY Price.Members on rows
From MyCube
Where (Measures.Buys)
Most OLAP client tools have options to suppress rows or columns with no
data (default for Excel Pivot Table).


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks. We're actually using Office Web Component (OWC) as our client. Do
you know if OWC allows us to suppress blank rows? If so, do you know how?

Thanks again, Andre


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Here are instructions from OWC Pivot Table XP version:


In the design window, make sure the PivotTable list is activated. For
instructions, see Help for your design program.

Click the title bar of the PivotTable list.

On the toolbar in the PivotTable list, click "Commands and Options" ,
and then click the "Report" tab.

Select or clear the Empty rows and Empty columns check boxes.

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Andre,

Not being an expert in the behavior/configuration of OWC, I would defer
to others on this news group. However, I was wondering whether [Unit
Buys] is a calculated measure, because simple measures in a cube are
only empty (blank) if that cell has no underlying fact table rows. This
should apply to all simple measures in a single physical cube. In a
virtual cube, simple measures from different physical cubes may, of
course, have different empty cells.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Units Buys is not a calculated measure. However, I do have a calculated
measure called Units Net that is the sum of Units Buys - Units Credits. I
was wondering if the fact that I had a calculated measure based on Units
Buys could have anything to do with this issue.

Andre


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

If [Units Buys] and [Units Credits] are both simple measures in the same
physical cube, then one should not be blank/empty unless the other is
empty as well. So I'm not sure what is happening - are there fact table
rows corresponding to cells where [Units Buys] is blank?


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Yes, there are rows where the UnitsBuys column is null, but other columns,
such as UnitsCredits has values.

I know that OWC's "show/hide blank rows" feature is working because when I
put a check in the checkbox, more empty rows get displayed. I just can't
figure out why some are displayed, and some are not.

When you say that there are fact table rows where the UnitBuys column is
null, I suspect that [Measures].[Unit Buys] in the cube is not empty in
those cells. For example, if you defined [Unit Buys] as a "Sum
Aggregation" measure on the "UnitBuys" column in the fact table, [Unit
Buys] would aggregate to 0, not empty, where "UnitBuys" is null.


This earlier post gives you some work-around options:

http://groups.google.com/groups?hl=e...8&selm=3C6D2C7
B.43074146%40dsslab.com
Subject: Re: NULL values in fact table with NUMERIC data type handled
incorrectly
View: Complete Thread (5 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2002-02-15 08:48:36 PST


What you are seeing is a limitation of MS OLAP. (Various resources say
that
Analysis Services doesn't store empty cells; NULL measures is a case
where this
is still true albeit not in the spirit of the statement, because they
convert
NULLs to zeroes.)

There are three workarounds, which will both impact cube size and query
processing time somewhat but probably not in any meaningful way:

1) create two separate cubes, with one measure in one cube and the other
measure
in the other cube. Combine them in a virtual cube. This virtual cube
will have
appropriate NULLs

2) pivot the measures and add a dimension called something like
"measurement
type". This dimension will have two rows/members, for pressure and
temperature.
A single measure called "value" can hold the readings. You can do a
little work
with views and the one new dimension table so that you don't need to
change the
tables you already have.

3) add a measure to the cube whose column expression is the column for
temperature and whose aggregation function is COUNT. My recollection is
that the
COUNT aggregation will only count up non-NULL values. You can take
accurate
averages using this COUNT. Then, add a calculated measure for display
whose
expression is:

iif (
[Measures].[Temperature Count] > 0,
[Measures].[Temperature Count],
NULL
)

and hide the original temperature measure. Now, users will see NULLs in
the
right place.

Not sure which one of these is truly better- my bias would be to opt for
workaround #1.

HTH

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!