Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: SSAS 2005 cube ERROR

  1. #1
    Join Date
    Mar 2007
    Location
    US
    Posts
    16

    SSAS 2005 cube ERROR

    The cube I'm building has a fact table and filedescdim table.
    They both are linked by fileid. The 'filedesc' table has just few
    rows - 'closed','open', bad','unknown' which have a fileid of 0,1,2,3 respectively.
    The fact table has a count of approximately 50,000 'bad' and
    about 100,0000 'closed' rows.

    after deployment, for some reason in the browser panel of
    BIDS I can only see the 'closed' attibute' values.
    whenever I select 'bad' from the drop down menu, I don't get
    any values in return.

    Can anyone enlighten me on what I'm doing wrong???
    Last edited by prok; 03-27-2007 at 05:29 PM.

  2. #2
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Sure about your keys?

    It sounds like you're not getting a join where the "missing" dims are concerned ... Can't imagine much else to be the problem, but then we know very little about the specific situation.

    Try running a regular SQL query against the source database to get the same information, and then compare it to what you think you have on the induction side of AS ...

    Let us know how it goes ...

    Good Luck.

    Bill is

  3. #3
    Join Date
    Mar 2007
    Location
    US
    Posts
    16
    Thanks for the response Bill.

    First let me correct the previous post. Fileid for closed is
    '1' and fileid for bad is '0'.

    The joins are perfectly ok. I've already tested that. What I think is happening is for some reason BIDS is identifying the value of '0' as an empty and is trying to exclude all the rows with that value.

    I'm trying to test that and will let you know.

  4. #4
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Keep Us Posted ...

    I'll do what I can to assist, if you need further help.

    Good Luck!

    Bill

  5. #5
    Join Date
    Mar 2007
    Location
    US
    Posts
    16
    The main table in my warehouse has about 1045304 rows, while I deploy in bids I just get record count of 550,945 rows.

    Could you tell what am I doing wrong with the missing rows.
    any suggestion will greatly help!


    prok

  6. #6
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Use the Explore option in the DSV ...

    Try right-clicking the table and selecting Explore Data ... this will only return a sample of the rows for a large table, but the viewer offers several tabs that might assist you in locating patterns - which, in turn, might lead to why rows are being omitted.

    While you're in the DSV, check those joins again, as well ... and look closely at data types for the joined columns ... you say that the data pulls fine in a query, so something is happening on the induction side of the cube that is limiting intake to a subset ...

    Let us know how it goes ...

    Bill

  7. #7
    Join Date
    Mar 2007
    Location
    US
    Posts
    16
    Bill -- if I customize the error configuration on the property of a measure group, to 'convert to unknown' on the key error action and then set the key error limit to [any number], will that be the cause for my missing rows?

  8. #8
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    It's Worth a Shot ...

    Not sure on that one, Buddy. I'd try it, if I were you, after backing up, or with a clone of the same DB. (Sorry I don't have the model here, or I'd give it a shot for you...)

    I would be very interested if this resolved the issue. Keep us posted so I can add that to my growing bag of tricks / things to check - can't say I've run across it before, but "the night is still young" with an application as new as AS 2k5!

    Good Luck, again ...

    B*

  9. #9
    Join Date
    Mar 2007
    Location
    US
    Posts
    16
    Bill, How do I link my date tables to server time table. If not,
    I have created a time table in my warehouse, and in the dsv I'm linking the dimensional(date) tables to this time table but its lacking the precise timestamp-- meaning the best I could create was '01/01/1900 00:00:00' I couldn't increment the timestamp to something like this '01/01/1900 01:09:00', because of this BIDS is skipping records that has any timestamps variance.

    Am I taking a wrong approach?
    prok.

  10. #10
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Not Using Surrogate Keys?

    Am I understanding that you're joining on timestamps and not surrogate keys? The latter is a better approach. A quick and easy table can also be generated through the "push down" approach I demo in my article at:

    http://www.databasejournal.com/featu...le.php/3658391

    --- even if you run through the wizard and create a simple cube / use a sample cube just to generate the time dim table (and scrap the rest) - I do this all the time to generate the myriad rows (it really gets myriad when you're using time units less than single days ...) quickly.

    Yes, I can see how the time stamp joins might cause issues - if I'm understanding you (forgive me if not ...). Let us know if you can approach it another way, such as the aforementioned.

    Good Luck.

    Bill

  11. #11
    Join Date
    Mar 2007
    Location
    US
    Posts
    16
    Bill,
    As per our discussion I've linked the fact(master)tbl with surrogate keys on the time table. I've even casted the date field which had timestamps to datetime for eg:
    CAST(FLOOR(CAST([ClosedDate] AS float)) AS datetime) AS closeddate--- I did manage to retreive 30,000 rows after this implementation.But, still I'm getting errors

    Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_MASTER, Column: ClosedDate, Value: 12:00:00 AM. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: Time of Dimension: TIME from Database: justintime, Cube: Warehouse, Measure Group: MASTER, Partition: MASTER, Record: 243.

    I've checked both tables and none of them have either just
    12:00:00 Am or 00:00:00 values. They both have values like this 04-13-2007 00:00:00.000 only.

    can you give me any suggestions ? This "time" is really killing me.

    Thanks!

    Prok.

  12. #12
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    What are You Using as Surrogates for Time Dim?

    What are your surrogate keys - integers?

    Thanks.

    Bill

  13. #13
    Join Date
    Mar 2007
    Location
    US
    Posts
    16
    In the Fact Table(master) it is bigint, and in the Dim(time) table it is Int.

  14. #14
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Differing Data Types on the Surrogate Keys?

    Not saying that it's the core problem, by any means, but why would you have surrogate keys with differing data types across tables of the star?

    Bill

  15. #15
    Join Date
    Mar 2007
    Location
    US
    Posts
    16
    Bill;

    I was thinking so, because, the fact table is enormous. It grows significantly and thought bigint is necessary for the Fact table. I will change the Dims to bigint to see any changes.

    thanks.

    Prok

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •