Post reply

Creating Data Source Views on a Database Lacking Primary Keys.

  • September 9, 2010 at 8:19 am

    #225949

    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?

  • September 9, 2010 at 11:12 am

    #1218811

    Can you add views with keys and indexes to your production db? If you can they use the views as your source instead of the tables.

  • September 9, 2010 at 11:15 am

    #1218812

    If I did that, I would violate our licensing agreement, but I may do it anyway.

    Any other options?

  • September 9, 2010 at 2:09 pm

    #1218879

    Unless you are looking for live data consider making a report database that you load at regular intervals (daily is common, some people do less often, some more often) from the source system. You can mirror the source tables exactly if you want, and then have your way with them regarding keys, indexes and views. As long as you are working with a copy of the data, I don't see how that could violate a license agreement. The data is yours afterall.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply