Suppressing dimension members when missing value


    Sponsored Links


  • 1. Will Analysis Services allow loading data from my custom OLE DB data provider
    Hello, I am planning to develop a custom OLE DB provider for my buffered data. I tried loading data to Analysis Services from a CSV file using "Microsoft Text Provider".But i have found that such data loading is officailly not supported in Analysis services. So i was wondering if Analysis Services will allow me to load data from my custom OLE DB provider. Any pointers in this regard are welcome. Thanks in advance, Ritu
    Apart from SQL Server AS, any cube editor (free 3rd party) out there ??? Pls help
  • 3. and data types
    Hi there I am executing an mdx query using as follows: AdomdDataAdapter adapter = new AdomdDataAdapter(query, connection.Value); adapter.Fill(data); I get the data back correctly, but all the columns have DataType of 'System.Object ' and not as expected. Why is this happening?
  • 4. Error on saving a dimension: "Processing error [Object does not exists]"
    Hi all, After installing SP4 on a MSAS server, it raises an error: "Processing error[Objects does not exists]" when tring to save/process a dimension. The dimension is pretty large (100000+ members on the last level ) and it also has virtual dimensions depending on it. The same dimension on MSAS SP3 is working fine. Do you have any clues ? TIA, Rud P.S. Is there a way to uninstall SQL SP4 and MSAS SP4 ?

Suppressing dimension members when missing value

Postby ricocali » Fri, 10 Sep 2004 14:52:14 GMT

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?

Re: Suppressing dimension members when missing value

Postby Dave Wickert [MSFT] » Sat, 11 Sep 2004 03:33:20 GMT

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]
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)

Re: Suppressing dimension members when missing value

Postby PCL » Sat, 11 Sep 2004 16:32:29 GMT

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...



Re: Suppressing dimension members when missing value

Postby ricocali » Sun, 12 Sep 2004 01:42:02 GMT

I apologize Dave but can you elaborate?

Re: Suppressing dimension members when missing value

Postby Dave Wickert [MSFT] » Sun, 12 Sep 2004 07:58:44 GMT

s 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

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

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]
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
"Rico Cali" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
news:<# XXXX@XXXXX.COM >...

Re: Suppressing dimension members when missing value

Postby ricocali » Tue, 14 Sep 2004 11:37:04 GMT

> And in your case, you have a situation where the dependency changes over

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.

Re: Suppressing dimension members when missing value

Postby Dave Wickert [MSFT] » Wed, 15 Sep 2004 01:18:29 GMT

Yes, there is. Most client tools have a "don't show empty cells" option.
Dave Wickert [MSFT]
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)


Similar Threads:

1.Using one Dimension member to get the Value of another member in the same dimension

2.Suppressing the display of dimension members with facts

When drilling down along a dimension, all the dimension members are visible eventhough there are no measures associated with them. How can such dimension members be suppressed from the display?

3.Suppress All member in attribute dimension (AS 2005)

I have an attribute dimension for Year 2002 through 2005. I do not want the 
All member on the dimension - it will be used as report parameter and I want 
to list just the years.

From my understanding, to not include an All member in an attribute 
dimension you set IsAggregatable property to False. That does indeed suppress 
All, but my cube now only has values for Year 2002, which is not the desired 
result. Is there a way to set IsAggregatable to False and still aggregate 
across the members?

Is there another way to suppress the All member in an attribute dimension?

4.Define dimensions members from other members of the same dimension


I have a geography dimension containing a number of countries without
any hierarchy. Lets say the members are:

Country A
Country B
Country C
Country D
Country E

Now I want the users to be able to choose for instance "CountryGroup1"
and "CountryGroup2" instead of a specific country and then they would
see all values for that group. "CountryGroup1" would sum Country A - C
and while group 2 would be the rest.

How do I do this the easiest way?

5.SOLVED: Get max member from one dimension by members from another dimension, no measures involved

Here is a solution. Maybe not optimal.
    [Contract Number].[Contract Number].members,
        {[Contract Number].CurrentMember},
                    {[Contract Number].CurrentMember},
                    {[Date Loaded].[date_loaded].members}
                [Date Loaded]
FROM SourceData


6. Get max member from one dimension by members from another dimension, no measures involved

7. Set measure value=0 for dimension member value

8. Getting Dimension members based on other Dimension members



Who is online

Users browsing this forum: No registered users and 64 guest