The problem I've had can be easily reproduced on FoodMart, though
reproduction on FoorMart doesn't help too much with the context I'm
afraid.

I started with an unadultered FoodMart.
Edited the Customer dimension, and added Filter on Male customers
only.
Checked it worked as expected, then reprocessed the dimension.
Sample data view of the Sales cube appears to work.
Attempted to reprocess the Sales Cube. Failed.

The failure is that the fact table contains rows that reference
dimension values not in the dimension - so about 50% (sensible guess)
of the fact table fails to conform and MAS gives up.

How can I solve this without pre-filtering, changing or transforming
the source data-mart?

Thanks

- Richard

You could use the "Source Table Filter" property of the Cube to filter out
the "offending" records. You could also create a view of the fact table, but
maybe that's excluded by your "no pre-filtering" rule.
HTH,
Brian
www.geocities.com/brianaltmann/olap.html

"Richard Corfield" wrote:

Could you explain your real requirement/scenario, because the example
from Foodmart isn't obvious, in terms of approach? If there's a Customer
dimension whose lowest members have interesting properties (such as
Gender), then one way of conveniently exposing these properties for
analysis in a cube is via virtual dimensions. So a Gender virtual
dimension (it is actually implemented as a regular dimension in
Foodmart) would permit selection of data pertaining to just Male
customers. Why would filtering of the dimension or fact table be
necessary in that case?


- Deepak

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

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

I'm finding out more about the world I'm working in. What I have in
front of me is a huge hypercube, where each Dimension contains a
ragged hierarchy with the top level of the hierarchy being the parts
of the hypercube I can select from. It doesn't make sense to look at
more than one value at this level at a time (values tend to be things
like "Richard's way of looking at this data today" - basicly user
defined report templates presented in the levels below).

The fact table links to the element_id in the dimension tables, which
can repeat, one for each "reporting template" that exists. This causes
duplication of facts if the whole hypercube is read in at once as one
fact row can produce a number of query result rows.

Current plan is to filter things down at the cube level to make
mini-cubes. I have an alternative for making the hypercube with a set
of dimensions that will allow the user to choose which mini-cube
section to look at, but am worried about performance and storage with
a big space with many dimensions and clustering that will cause the
"edges", and therefore the aggregations to be very dense.

- Richard

Will alternate dimension hierarchies (customized for different reporting
templates) fit your scenario? No duplicated fact rows or unnecessary
aggregations:

http://www.microsoft.com/technet/pro...ntain/analysis.
mspx
Representing Multiple Hierarchies in a Dimension

You can create multiple hierarchies for a dimension to provide
alternative views of dimension members. For example, a time dimension
may have a calendar year view and a fiscal year view. These dimensions
can have different level structures.

A retail calendar may divide the year into thirteen "months" of four
weeks each. This hierarchy rolls up into retail seasons that do not map
the standard calendar quarters. Provided that the dimension tables
contain data that fully describes how the hierarchy is organized, you
can create an additional hierarchy to model the retail calendar.

Another example is a customer dimension that contains two hierarchies,
one based on states, the other based on sales regions. In this example,
the states do not roll up into sales regions.

In Analysis Services, a dimension with multiple hierarchies is
implemented as a collection of dimensions that share the same relational
source data. If you define a dimension with multiple hierarchies rather
than build separate dimensions, Analysis Services can choose a set of
aggregations to build so that they are useful to both hierarchies.

Even though you could model an alternate hierarchy by creating a new
dimension or a virtual dimension based on the original data columns, you
should build multiple hierarchies of a dimension rather than use
separate dimensions. The most important reason for this is the savings
in determining a set of useful aggregations. In addition, many client
tools understand the concept of multiple hierarchies and can expose this
to users to enhance analysis.
...

- Deepak

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

On Fri, 17 Sep 2004 15:31:43 -0700, Deepak Puri wrote:

That article discusses Dimension filters, which I was looking at using. I
found that I had to duplicate my Filter on the cube as well, rather than
having it included implicitly, although the article tells me that a
Dimension Table Filter applies to both Dimension and Cube.

It would have also been interesting to specify the filter in making a
set of virtual dimensions, one for each "hierarchy" from a single master
dimension. I can't seem to specify a filter in making a virtual dimension,
although there is the MDX Member Function which may be interesting there.

- Richard