Post reply

Missing data from dimension - sometimes

  • May 22, 2012 at 4:01 am

    #147147

    Hi All,

    Sql Server 2005 SP2.

    I have a dimension which when I process it says that the number of rows read is different from those in the underlying table that it is reading from. As an example the table it reads from has 1427308 rows in it yet when the dimension is processed it can say it has read 1179649 rows. If I run the sql shown during processing it returns the correct number of rows, 1427308. If I process the dimension again it may say it has read 1179649 or 1427308 or 1674967 rows.

    I have ran profiler against the database during the dimension processing and it always shows a rowcount being returned of 1427308.

    If the rows read is less than the number of rows in the table then I am actually missing data from the dimension and cube it feeds.

    Now I know the underlying data is not changing during these process runs, so has anyone seen activity like this, or could suggest how to troubleshoot the issue.

    Thanks

    Ronnie

  • May 24, 2012 at 1:22 pm

    #1492760

    Just a guess, but check your datasourceview to see that it is in fact collecting the entire table. We sometimes use a query to leave out fields we want in our DW but not our cubes, so maybe there is criteria there that is causing the problem by restricting rows?

  • May 24, 2012 at 2:08 pm

    #1492799

    Considering your getting a random number after each process, have you check what the processing option is?

    Any filters in the DSV would make a difference.also have you checked the uniqueness of the dimension key and the handling of nulls.

    Jayanth Kurup[/url]

  • May 29, 2012 at 1:16 am

    #1493987

    All,

    There is no differences, I process it once it gets 1.1 million records, as soon as it finished I process it again I get 1.4 million records, then I process it again it could be 1.1 million records, or 1.6 million records. If I check the base table it is constant at 1.4 million rows and if I run sql profiler it shows 1.4 million being returned each time, no matter what the process output is showing.

    Thanks

    Ronnie

  • June 4, 2012 at 4:12 am

    #1496401

    I am not so sure about ur exact reason of problem but I faced similar situation wher some data was misssing, and after doing some resarch I have found there is a problem with data having square braces.

    If memebre has ']' in member name that will create problem with query, so in this case ..].&[Shangai Project[External]],..

    to be replaced by ..].&[Shangai Project[External]]],..

    adding this last square bracket works as escape character. and query executes and give right result.

Viewing 5 posts - 1 through 4 (of 4 total)

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