Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Creating Data Source Views

Author  Topic 

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2010-09-09 : 14:19:07
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?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-09 : 14:24:09
Why can't you modify production to set a primary key on Identity_Column and make it non-clustered (so you don't have to worry about the existing clustered one)?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

rddamulira
Starting Member

1 Post

Posted - 2013-12-04 : 17:59:22
I have the same problem as DavidChel. Has anybody found a solution to this without modifying the underlying tables?

Thanks

quote:
Originally posted by tkizer

Why can't you modify production to set a primary key on Identity_Column and make it non-clustered (so you don't have to worry about the existing clustered one)?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Go to Top of Page
   

- Advertisement -