Relationships between views?

Question

Post reply

Relationships between views?

BowlOfCereal

SSCrazy

Points: 2008
More actions
November 3, 2008 at 3:51 pm

#193223

We have followed "best practices" by revoking access to base tables, and only allowing data access via views that select from those tables. Each table currently has a corresponding view that simply selects each column from the table.
This has led to a presentation problem. The tables have foreign key constraints defined, and those constraints provide useful join information to end-user query tools. This information is no longer available to the end users when they work with views.
For example, if you pull up the query designer in SSMS and add in a fact table and a few dimension tables with foreign keys defined, the tables are automatically joined on the correct columns. The same is true for query designers in Access, Crystal, etc. However, replace the tables with simple views on those tables, and the joins are no longer auto-created.
How can I work around this problem? Can I somehow define relationships between views that will benefit the end users?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 1

Are you saying that you've revoked base table rights from users on a reporting database? If so, give it back!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

I am not sure what you can do in Crystal (unless you have the full blown Crystal+Business Objects suite, in which case you can go with a universe which will handle all of your problems), but you can define the relationships in access (since SQL views and tables both get linked in as "tables" and it doesn't know the difference). Yes - you end up having to redefine the relations by hand, but it's a one time cost, which can then be used by all of your reporting users.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

BowlOfCereal SSCrazy Points: 2008 More actions · Answer 3

Thanks, Matt. You're right in that in some cases we can define the relationships within the query tool. My hope, of course, is to find a centralized solution for defining those relationships in the database. That's where they belong, that gives us one place to maintain them, and they're then available to any and all query tools. I'm doubtful that I'll find a solution in the database, but if anyone has ideas, please post them!

Will Riley Ten Centuries Points: 1030 More actions · Answer 4

Why bother with views?

Why not construct a UDM (Unified dimensional model) layer over the reporting database instead and create report models for your report builders to hit. That's one of the reasons the UDM was introduced after all.

Kind Regards, Will

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 5

Will Riley (11/12/2008)
Why bother with views?
Why not construct a UDM (Unified dimensional model) layer over the reporting database instead and create report models for your report builders to hit. That's one of the reasons the UDM was introduced after all.

How are they for performance?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BowlOfCereal SSCrazy Points: 2008 More actions · Answer 6

Thanks for the reply. I'm working with a dimensional data warehouse which has been designed with the intent of meeting data analysis needs. This is not a relational database that requires an additional layer of abstraction, so there's no need for a UDM here (not to mention the fact that we're not an Analysis Services shop). The views are simply a best-practice implementation for security and flexibility, per Kimball, Inmon, and most data warehousing recommendations.

Will Riley Ten Centuries Points: 1030 More actions · Answer 7

Jeff,

Fair point... I have had mixed results with report models, depending on all sorts of factors, but yes, performance can sometimes be an issue.

RedEye,

You missed my point. UDMs are used as the basis for report models as well as AS Cube sources. So what if you're not using AS, the UDM could still have a purpose for report models if that's an acceptable route.

Like Jeff suggested, I don't see how it is following "best practice" to revoke access to reporting server tables - Why would you do that? Surely that's what a reporting server is for, so that users can read the data you have collected for them....

Kind Regards, Will

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Well - depending on the reporting, you may not want to open the door up. For example - my "reporting" data is actually simply a restored version of production, with all of the (confidential) stuff I didn't want them to see over there. So - I don't just open up all of the rights, or else I might as well do that in prod too. I know it's not ideal, but it's also not entirely my call (I'm told not to spend any time on it, so what they have is what they get).

Also - unless you're entirely denaturing your data moving it from one to the other, I just don't feel comfortable giving acceess to tables in the same form as they exist in prod. Just gives anyone with a wandering eye too much of a leg up on guessing what they might find in production.

But - summary tables with nothing confidential, sure, open them up if you wish.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

BowlOfCereal SSCrazy Points: 2008 More actions · Answer 9

Hi Will,

I think you may have missed my point as well. The purpose of a UDM (for example, according to http://msdn.microsoft.com/en-us/library/ms345143(SQL.90).aspx) is to provide a layer of abstraction when you're dealing with confusing or heterogeneous data sources, or when you want aggregate data or apply business rules on top of data sources. In our case, we are designing a data warehouse specifically to address these issues. The data warehouse is a comprehensive solution to these (and other) challenges, and negates the need for a UDM.

As to why we want to grant access to views rather than base tables, there are several reasons why this is considered a best practice for data warehousing. In our case, the primary benefits views provide are the ability for us to make changes to the underlying tables without affecting user results, the ability to easily hide metadata and auditing columns, and the ability to implement row and column level security.

Thanks, RedEye