Outer Join in Data-Source-View

I have 3 tables:

Master, Dynamic, Summary

which are joined as follows:

- Master-Dynamic on e_id

- Dynamic-Summary on p_id + g_no

- Master-Summary on p_id + g_no

Now my Summary table is missing some p_id, hence the Cube deployment fails with these Errors:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: DYNAMIC, Column: p_id, Value: AB2.

Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute SUMMARY of Dimension: SUMMARY from Database: Cohort1, Cube: Analytics, Measure Group: DYNAMIC, Partition: DYNAMIC, Record: 11665.

I tried reversing the table relationships, but that does not help. My constraint is that I am fetching data from a (large) read-only DB, hence I cannot create a view on that DB. My Data source view has these 3 tables and 2 named queries. I don't think I can put the outer join SQL clause anywhere. Also my dynamic table is quite large (189m rows) and takes 8hours to query. Hence I can't add another view to it as it will increase the read-time.

Can anyone suggest how to add instructions for outer join in the .dsv file in the <msdata:Relationship> tag? Is it possible to do it there?

Thanks a lot.

If you cannot modify your relational database your only option is to try and use named queries in DSV to fix problem.

Trying to modify .dsv file directly doesnt help. All functionality avaliable in DSV is exposed to you in DSV editior. Dont think you will get anything by trying to modify it directly.

You can tell Analysis Services to ignore referential integrity errors caught during processing. But that practice is going to slow your processing performance and might lead to later problems with processing partitions. That in case if ignored dimension members have some data assosiated in fact table.

I would say you need to fight for fixing referential integrity errors in relational data base. Analysis Services could go as far as to help reporting them.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.