SQL Server Performance

Right Data, No Data ???

Discussion in 'Analysis Services/Data Warehousing' started by Joozh, Mar 7, 2005.

  1. Joozh New Member

    Hi,<br /><br />This problem has been driving me crazy for the past few days and I really have no clue at all about how to solve the problem and where to look.<br /><br />With 1 refresh of the cube the following MDX returns correct data but after the next update of the cube, the same MDX does not display an data at all [<img src='/community/emoticons/emotion-6.gif' alt=':(' />][V] <br /><br />SELECT <br />{[Period].[All Period].[2005].[Quarter 1].Children} ON COLUMNS, <br />{[SalesType].[All SalesType].[Local],[SalesType].[All SalesType].[Exports]} ON ROWS <br />FROM Sales<br />WHERE ([Measures].[NetSales])<br /><br /><br />Desparately calling experts for some clues. <br /><br />Thanks.
  2. wildh New Member

    Joozh,

    How are you updating the cube, incramental/ refresh or full? does this problem happen on successive full or refresh cubde reprocesses?

    Howard.
  3. Joozh New Member

    At last a reply. Thank you thank you just for the reply itself <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Here is what I am doing in what I am referring to the "cube update"<br /><br />1) I rebuild all my dimensions<br />2) I do a full FULL process<br /><br />The funny thing is that, after one update the MDX works and then on the next update,the MDX does not work ??? I am into Day 4 of the problem and am and the verge of a break-down <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />I have tried the little knowldge that I have in trying to go through all the underlying tables (which make up the fact and the dimensions)but everything seems right. <br /><br />Regards.
  4. Raulie New Member

    When the cube processes both times is there any change to the source data? You should revisit the source data if there is.

    Raulie
    @hp

    All postings are provided “AS IS” with no warranties for accuracy.
  5. Joozh New Member

    Raulie I have already made sure of this. No changes in the underlying/source data <img src='/community/emoticons/emotion-6.gif' alt=':(' />
  6. wildh New Member

    Are the dimensions built from the fact table? What I'm trying to get at is probably the same thing as Raulie. More often than not these types of errors are due to the underlying data, either dimension or fact. The reason for this is the MDX statement (I assume) hasn't changed but you have changed your dimensions and possibly fact table. If a dimension is built from the fact table and a member, that is selected within the MDX, is no longer present, the MDX will fail.

    I suggest you make a copy of the cube and process it in a development environment, then you will have two identical cubes. Make sure the MDX succeeds against both cubes. Update one of them as you would normally and when the MDX fails agains the updated cube you can compare the two cubes.

    If at this point you are unable to find a reason, and I'd be supprised if you couldn't, then it could point to an internal problem within AS itself.

  7. Joozh New Member

    Hi wildh,<br /><br />Please bear with me in case you get a feeling that I am not clearly answering your questions but let me try:<br /><br />The underlying data really does not change (trust me) and neither did I make any changes to the dimension. But yes I have one dimension that is based on the Fact table (I know the right way to do it is that dimension tables should have their own underlying tables). However how this 'fact based' dimension is setup is that it is based on a column of my fact table called "Sale Category". Each record in the fact table has either 'Local' or 'Exports' in this field. I know that maybe this is a bad approach but other than this, do you think that this could bethe cause? Nevertheless, and though my knowledge and experience is limited in this area, i too feel that the reason for this is the underlying data. I wish I could only find out where <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />I have just come across the following KB article and could this be it???<br /<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=kb;en-us;829050>http://support.microsoft.com/default.aspx?scid=kb;en-us;829050</a><br /><br />Please do share your feedback on my post.<br /><br />P.S.<br />Really appreciate your help (goes for all everyone who have been of great help via reply to my posts).<br /><br />Regards.
  8. wildh New Member

    Joozh,

    As you know, in general building dimensions from fact data is a bad approach, but yes in this case seen as there are only 2 members it is almost acceptable and it should't effect the MDX.

    So you have a NonEmptyCrossjoin (NECJ) in you MDX do you? Is the cube a virtual cube?

    I'm not a particularly good MDXer but here's what I think - NECJ seem to ignore sets containing measures. NECJ's work by filtering out tuples that don't exist in the fact table, since it's not possible for one real measure to be null in a cube and another not to be null, AS thinks it can ignore any measures you specify.

    I've experienced a lot of query result problems with NECJ's but none quite like this one, it works, it dosn't, it does kind of thing.

    If the cube is virtual SP3 should fix it if not, Mosha Pasumansky, the writer of NECJ's appears to have fixed it in SP4 -http://support.microsoft.com/kb/888800.

    If this is still not the problem, can you attach your MDX.

Share This Page