Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Location
    CA
    Posts
    41

    Unanswered: Analysis Services help - Dim builds, but cube fails.

    If there are any AS gurus out there, I could use some help. I've been having some problems with particular AS dimension and cubes and it's driving me crazy! It doesn't matter what I do, nothing seems to work.

    Anyway, here's what I'm trying to do. I've got a fairly simple dimension. There is a date stored in the dimension that is formatted as an int. The dimension needs to only display AGE, so I cast the int as a date and do a datediff to get AGE. The dimension builds just fine and I get the results I want. My problem is when I add the dimension to the cube. The cube fails to build and I get an error message - "Data source provider error: The column prefix 'MY TABLE' does not match with a table name or alias used in the query." Basically, AS is telling me that the table that is used for the dimension doesn't exist, even though the fact and the dimension are joined properly and I've validated the structure.

    I've run a number of queries in QA on the two tables and everything works fine. No funky data issues. I've run the service packs a few times, but that didn't work either. I've tried making a cube that only has the just fact table and the one dimension table, and it still fails.

    Basically, I'm out of ideas. Any help that anyone has is greatly appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first suggestion would be to examine Brett's excellent sticky with suggestions for getting quick answers. You certainly appear to have done your homework in understanding the problem, but haven't done much to help me understand it!

    If that error message is literally what AS reports, I suspect that the problem is the space in the 'MY TABLE' name. If you use characters outside of letters, digits, and underscores in your object names (like your space), then you need to use square brackets [] to surround that name.

    -PatP

  3. #3
    Join Date
    Sep 2004
    Location
    CA
    Posts
    41
    Quote Originally Posted by Pat Phelan
    My first suggestion would be to examine Brett's excellent sticky with suggestions for getting quick answers. You certainly appear to have done your homework in understanding the problem, but haven't done much to help me understand it!

    If that error message is literally what AS reports, I suspect that the problem is the space in the 'MY TABLE' name. If you use characters outside of letters, digits, and underscores in your object names (like your space), then you need to use square brackets [] to surround that name.

    -PatP
    Sorry. The table isn't actually called 'MY TABLE'. I was just using that as a generic reference. The real table name is 'F_LOAN'.

    Pat, do you have any specifc questions I can answer? Are you pretty familiar with AS? In a nutshell, I have a dimesion that builds just fine. When I add it to the cube, the cube fails to build. I'm guessing that I'm completely missing something very basic or I've got a bug.

    I've added some screen shots of the cube, the error and the dimension. A pic is worth a thousand words, right?
    Attached Thumbnails Attached Thumbnails AS1.jpg   AS2.jpg   AS3.jpg  

  4. #4
    Join Date
    Sep 2004
    Location
    CA
    Posts
    41
    A little more info...

    If I remove the dimension logic that calcs the Loan Age, the cube will build. However, I don't understand how the logic could be a problem when the dim builds fine with the logic. I would expect the dimension to fail if there was a problem with the Loan Age calc logic.

  5. #5
    Join Date
    Sep 2004
    Location
    CA
    Posts
    41
    One more pic. This query is doing what I want the dimension to do.
    Attached Thumbnails Attached Thumbnails AS4.jpg  

  6. #6
    Join Date
    Sep 2004
    Location
    CA
    Posts
    41
    Well, I think I've got it solved. I know everyone was waiting with bated breath!

    Evidently, the cube didn't like having a dim where the dim converted the date, stored as an int in the DB, to a date. Even though the dim was working just fine, the cube somehow couldn't handle it. So, I made a view based on my dim table and did the int-to-date conversion there. Then I used the view as the source for my dim. And...everything worked just fine. I guess I was just asking too much from AS.

    Thanks.

Posting Permissions

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