question

I'm attempting to create data source views on a terribly designed database which lacks primary and foreign keys. I want to be able to use Data Source Views to work with SSRS Report Models. I cannot create them with the wizard because "Add Related Tables" doesn't work and BIDS cannot determine the link. I've read that these relationships are based upon PK and FKs. When I re-open the DSV and attempt to right click the correct field in a table to assign a logical primary key, the option is grayed out (disabled). Here's the problem, there is a key symbol next to an identity column field which is a unique clustered index, but is not a Primary Key. When I deleted the clustered index on a test database it allowed me to set a logical primary key. However, I cannot modify the production database that way. Almost every single table has a field called Identity_Column which is a unique clustered index. So, it would appear that the DSVs use clustered indexes rather than primary keys as those are not always the same thing. Can anyone else confirm this? When I create a DSV from a named query, there are other problems. If I use a query that includes 30 fields, but the user only pulls back 5 of them with Report Builder or something similar, every field seems to get pulled back anyway. Profiler shows that it runs the entire DSV (with every field) and then selects the few fields the user requested from that data set. This is really inefficent. Any suggestions as to how to handle this?