Hello all,
I'm running into an odd error when trying to process a newly created dimension, I've looked at the 2 postings with a similar error, but they don't appear to pertain. Here is the error:
Error 1 Errors in the high-level relational engine. The 'dbo_Company_Address' table that is required for a join cannot be reached based on the relationships in the data source view.
Error 2 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Company', Name of 'Company-Location' was being processed.
Error 3 Errors in the OLAP storage engine: An error occurred while the 'Company' attribute of the 'Company-Location' dimension from the 'datawarehouse' database was being processed.
... processing then fails.
I was running into the same error using a data view with the full database schema and working cubes, so I started over, trying to simplify: I created a new project for a new database, created a new DSV, reduced the number of tables and confirmed that all relationships were correct (I did need to add some relationships where the wizard did not pick up on some links). I confirmed that the DSV did show the relation between dbo.Company and dbo.Company_Address. This was discovered by the DSV wizard. The PK and FK columns both show as int(4) in the DSV, as they should... Next, I created a single dimension using the dbo.Company table. Interestingly, the Dimension wizard did NOT pick up on the FK relation to dbo.Company_Address, though it picked up on two other relations, and added tables and hierarchies. I then added the dbo.Company_Address table to the Dimension (without adding any attributes or attribute relations), and processed the project. This processed OK. Next, I Added the PK (Company_Address_RECID) of the dbo.Company_Address table to the dimension attributes, created an attribute relation for that attribute under the Company attribute, and reprocessed the project. I got the above error again...The database I am trying to process comes from a third-party .NET application, which means that I can't easily change the underlying DB schema. For reference, the source DB is running under MSSQL 2000 (sp4).
I've also done numerous reports against this database (most using these tables and this pretty basic relation), so I don't think there is an issue with the underlying DB, but you never know.
... Anyone have any ideas?
It is hard to say what is going on.
The error indicates specifically problem with DSV. Not with database , not with the way you define your PK FK relationships in the SQL Server database. It is DVS.
I have seen such errors in the past. And in every case I would first look and will think everything is okay. But eventually this got to be DSV.
Try to simplify your case even more. Have only two tables, create only 2 attributes based on each table.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Thanks for answering.
> The error indicates specifically problem with DSV.
That's the impression I was left with, glad to be able to start narrowing issues...
I went ahead and tried narrowing down possibilities. Here are the results:
Changed DSV to contain ONLY the tables for the dimension (Company, Company_Address, Company_Type, Company_Status). Wizard picked up on relations from all but Company_Address.
|||
Feel free to contact me by removing 'noreply.online.' from my Display E-mail.
I will try take a look at your problem.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
--Jason
> --Original Message--
> From: Jpiterak
> Sent: Friday, June 09, 2006 9:29 AM
> To: Edward Melomed
> Subject: Error: 'table that is required for a join cannot be reached'
>
> Hello Edward,
> Again, thanks for taking the time to help with this...
>
> I'm attaching the contents of the .dsv and .dim xml to this email... Let
> me know if there is anything else I can send to help track this down (do
> you need the full project? -- I'd just need to cleanse any login
> info...).
>
> Thanks again,
> --Jason
Edward Melomed wrote:
> Are you having a case when a single company can have several addresses?
>
> One way to deal with this problem is to define single "primary" company
> address. So company and address have one-to-one relationship.
> Create a named query in DSV joining Company and Company address tables.
> Make sure you resolve ambiguity with addresses.
>
> If you absolutely need to maintain several addresses for company, you
> can create separate many-to-many dimension for Company address.
>
> Take a look at the AdventureWorks sample project shipped with the AS
> 2005. It has example of many-to-many dimensions.
>
>
> Edward.
> Analysis Services team. Microsoft Corp.
>
Hi Edward,
Thanks again for getting back to me...
The thing is, the relation between Company and Company_Address isn't
many to many, it's one to many (a company has several addresses, each of
which belong to one and only one company...)
All my other one to many relations seem to work fine, this one is just
plain stubbornly refusing to work.
Is there something in the xml schema files that presents the
co->co_addr relationship as something other than one to many, perhaps?
Anyway, thanks again for taking the time.
Take care,
--Jason
|||
Let me try to take another stab at it and explain it in a different way:
In the relational database you have one-to-many relationship between your company address and your company.
But the way you've defined your dimension, is you've marked your company as a key of the dimension and company address as a related attribute. This is just opposite to how you want to define your dimension.
The key attribute should be most granular attribute in your dimension and all other attributes are less granular.
So many examples... Take a look at the AdventureWorks database. You have Customer dimension there.
Any dimension you look, you see the relationships are one-to-many between attributes and the key attribute in the dimension.
Makes sense?
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
No comments:
Post a Comment