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.
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 allows me to set a logical primary key. However, I cannot modify the production database that way. Is there another way I can create a DSV to use with Report Models?
Almost every single table has a field called Identity_Column which is a unique clustered index. Does this mean I cannot use Data Source Views and therefore cannot build Report Models for this database?
When I research using a "Named Query" almost all the links come back for SSAS, not SSRS. I have tested the Named Query approach and that seems to work. However, if I create a DSV with a named query that includes 30 fields, but the user only pulls back 5 of them with Report Builder or something similar, do all fields get pulled anyway? That would be grossly inefficient.
If I used a named query approach, is there any real benefit to using a DSV instead of a regular database view other than a regular view is added to the actual database whereas I presume that a DSV is external?