I have a situation where I have a product dimension and through time
some members of the product dimension get discontinued. How do I
setup the cube so that if there are missing values for that measure
the dimension member for products get suppressed?

Have a member property with Active and Inactive.
As you have found, you cannot delete a member so long as it has fact records
associated with it. However, you can incrementally process the dimension and
change the member property from Active to Inactive -- and then setup a
virtual dimension against the member property with Active as the default
member. This will automatically slice the cube for Active data.

Hope that helps.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Rico Cali" <ricocali (AT) hotmail (DOT) com> wrote

I do not agree completely with you: it's a partial solution, because the
memory increases indefinitely when you add new members...

Why Analysis Services does not authorize the removal of "orphan members"
when the data do not exist any more ?
(example: Cube partitionned by day --> suppression of a partition -->
several members become useless, for example in the Day Dimension --> )

I prefer to forget Changing Dimensions to remove members, because on large
cube, the calculation of aggregations is not realistic...

There is a true evolution of the OAP engine to bring...

Jean


"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

I apologize Dave but can you elaborate?

"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> wrote

As you can see, you have tripped into a religious topic with OLAP systems.
Proponents fall into two camps:
Dependent dimensions:
(only show me those dimension member which have data associated with them)
Independent dimensions:
(show me dimension members even if they don't have data because sometimes
the analysis of "missing" data is just as important as having numbers
present)

Analsyis Services attempts to do both by having server code which will
return empty or non-empty cells; and some client-side tools (such as OWC)
have a setting of "show empty members; or not".

And in your case, you have a situation where the dependency changes over
time -- and you did not indicate to us how important retaining history of
the discontinued products was to you . . .

The basic restriction in Analysis Services is that you cannot delete a
dimension member without a full process. And even then you cannot remove a
member if there is data associated with it in the fact table. You can update
member properties; but you cannot remove the member itself. You generally
don't want to do full processing because it takes a considerable period of
time and this impacts the scalability of your system.

What your original posting asked for was how to handle products which became
"discontinued". Thus they should never have fact rows which appear for them
any more -- and you wanted to remove those members from the dimension.

What I suggested was that you instead have a member property called "Status"
and have each member be either Active or Inactive. If you then place a
virtual dimension against that member property, you can perform your
analysis with either ALL PRODUCTS (i.e. include the history of the
discontinued products); or slice the dimension by only looking at Active
products. If you set Active to be the default member of the virtual
dimension, then by-default, if you don't ask specify what Status you want,
you would only get the Active products. If by-default, you want to see all
history (i.e. even those facts associated with sales of discontinued
products), then set the default member to be the ALL member of the virtual
dimension.

PCL is right -- I am not totally complete in the approve approach. You don't
have a mechanism for clearing out history, either from the fact table or the
dimension table. He suggested a rolling "N" months technique for the fact
table and I am prefectly OK with that. It is a good technique. There are
many others -- it depends on what your requirement is. At some point in
time, you also need to clear out the dimension as well. How long you carry
discontinued items, whether or not it is longer than your fact table
retention is a question of requirements. I have seen systems which NEVER
clear out dimension updates; I have seen systems which NEVER clear out
dimension and fact table updates. Like many things in life, it depends on
the requirements. :-)

I wasn't attempting to expose some great mystery of Analysis Services; this
is just an interesting technique that I've used from time to time for
handling discontinued products. Use it or not; whatever makes sense for you.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Rico Cali" <ricocali (AT) hotmail (DOT) com> wrote

All I want to do is not have the member "appear" (I still want the
member to "physically" exist) when the data is missing for a
particular date member in the cube. When I change the date member
where it did exist and there are values for it in the cube I want
those members to "appear".

No, I just don't want those members to "appear" in the cube if the
date member value for it does not exist for that time. I still want
the member values to "physically" exist.

That will not work because I will be looking at that product at
different time dimensions and properties can only be dimension by its
member. Member properties has no coorelation with other dimensions
such as the "time" dimension.

What I want the cube to do is this: If the product exist until March 3
2001, I don't want the members to "appear" on March 3 2001 and the
future since the measures will be missing. Of course I want the
member value for the time dimension to exist because I will still need
to look at the measures for that product when it did exist.

Maybe I'm missing something here. Maybe this might be just a function
of the "Client Tool"? If it is, which tools will optionally allow me
to "not show" members if the measures don't exist for that row.

Yes, there is. Most client tools have a "don't show empty cells" option.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Rico Cali" <ricocali (AT) hotmail (DOT) com> wrote