Post reply

How can I hide the dimension

  • July 17, 2007 at 10:20 pm

    #117945

    I have a quick question regarding SQL Server 2000 analysis services

     

    How can I hide the  dimension level if values are null in the Cube or Virtual Cube

     

     

    Example:

      Date Dimension        values              product Dimension

      2007-01-31                1000                            1

      2007-02-28                1000                            1

      2007-03-31                1000                            1

      2007-04-30                1000                            1                                             

      2007-05-31                1000                            1

      2007-06-30                1000                            1

     

      2007-07-1                  null                               1

      2007-07-2                  null                               1

      2007-07-3                  null                               1

      2007-07-4                  null                               1

    2007-07-5                                   1000                           1

     

     

    I need to hide the current month data except 2007-07-5

     

    Dimension process Type is Incremental Update

     

  • July 18, 2007 at 9:24 am

    #720409

    What are you using to display cube data?  If writing MDX you can use NON EMPTY.

  • July 18, 2007 at 5:09 pm

    #720580

    Hi David

    Thanks for your reply David

    Crystal Analysis

     

    Kind regards

    Vijay

     

  • July 19, 2007 at 7:41 am

    #720765

    Vijay,

     

    I have to say it's been a very long time since I've used Crystal.  What it comes down to is that there is a NON EMPTY parameter in MDX.  Crystal Analysis writes MDX queries behind the scene.  So, I would hope that there would be an option somewhere in Crystal to hide empty rows.  Not all Cube browsers have this functionality even though it is so simple to implement.

    If you are able to get at the MDX query and modify it, you could simply add the NON EMPTY parameter to the rows.

    There is not a way to do this dynamically in the MSAS cube.  It's all up to the front end / MDX query to take care of it.

    This article shows the use of the parameter: http://www.databasejournal.com/features/mssql/article.php/10894_1594501_3

    I hope that helps a bit!

     

    David

  • July 20, 2007 at 6:18 am

    #721077

    You will save your sanity by figuring a way to not have NULL values in your data mart.  Analysis Services is much happier to deal with non-null values in a data warehouse.

    A null in a fact would typically indicate you have more than one fact column and while there is a value for one, there is not a value for another.  Your sample data does not illustrate this, but I assume that is the case in your situation.  This is typically a sign of a fact that should really be in another fact table.  In AS 2000, to combine these, you may have to create two cubes and a virtual cube, but that is the nature of OLAP.

  • July 20, 2007 at 7:22 am

    #721106

    I believe the problem is in viewing the cube and not the actual data mart table.  Most cube browsers will show every dimension member whether there is data or not.

    You're right in saying that null values should not be stored, but that will not fix this issue for Vijay.

  • July 20, 2007 at 7:34 am

    #721114

    If you have a dimension in a star schema in your data mart, when you set up the cube you ill have two tables joined in your cube editor by the key field.

    If this is not a shared dimension, you can set the key field on the dimension to be the "FactTable"."KeyID" rather than the dimension table key field.  This will actually eliminate dimension members with no facts.

    The problem is that you really do have ID's in the fact table and just a null value in the fact.  This is just not well handled by analysis services.

  • July 20, 2007 at 8:01 am

    #721125

    I see what you're saying...  Very creative.  It would work for a small implementation with just a few dimensions and few rows loaded into the cube.  But, I would never consider building cubes like that for what I do routinely... 800+ million rows and 25+ dimensions.

    Vijay, have you been able to find a hide empty rows feature in Crystal?

     

     

  • July 22, 2007 at 7:50 pm

    #721415

    Hi Guys

    Thanks for your help

    I can hide the empty dimension in Crystal Analysis using Non Empty MDX formula but I need to hide dynamically. That feature seems to be not there or I don't have the expertise to do that

    Kind regards

    Vijay

  • July 23, 2007 at 7:41 am

    #721521

    Vijay,

    If that's the case, you may want to consider Michael Earl's idea.  It will require rebuilding your dimensions and cubes though.  However, if you have a very large cube, performance will be negatively effected.

    One other thing to consider is that Analysis Services drill through (drill to detailed relational data) is not supported with virtual cubes.

    Or, I suppose you could use a better front end tool! 

     

    David

  • July 23, 2007 at 6:53 pm

    #721730

    Thanks David &  Michael Earl's

     

    I can't rebuild cube because we have 10 years data and will take more than 5 hours to process the cube

     

    May be I should provide separate cube for Current month Data with Date Private dimension

     

    Kind regards

    Vijay

     

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply