SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Creating Data Source Views
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DavidChel
Constraint Violating Yak Guru

USA
474 Posts

Posted - 09/09/2010 :  14:19:07  Show Profile  Reply with Quote
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

USA
36674 Posts

Posted - 09/09/2010 :  14:24:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Posts

Posted - 12/04/2013 :  17:59:22  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000