Showing posts with label relationships. Show all posts
Showing posts with label relationships. Show all posts

Sunday, March 11, 2012

AS2005... Using Attribute Relationships

I assume defining attribute relationships can be advantageous for performance. However, I don't quite understand them.

I know by default, the dimension is created with all attributes having an attribute relationship under the key -- with all having a cardnality of Many.

My Customer dimension points to the following named query:

SELECT customer_key, customer_id, customer_name, city, post_code, region_code, country_code
FROM Customer C
LEFT OUTER JOIN [Customer Address] CA
ON C.customer_key = CA.customer_Key
AND CA.[Address Type] = 'MAIN'

Right now, here's what I have done for the attribute relations in additon the ones created by default:

I dragged Country Code under Region code, Region Code under Post Code, and Post Code under City. The cardinality for all of these relationships is Many (not sure I understand this property). Does that sound correct?

So what do I do for Customer Id and Customer Name? I assume there should be a relationship between the two, but which field do I drag to which? Customer Name under Customer Id or vice-versa? How does it make a difference in this case, if it does at all? Cardinality?

And finally, do I want to delete the relationships that were created by default under customer_key? By the way, the customer_key attribute is not visible.

Sorry for so many questions, but I definitely appreciate your help!

There is an extensive discussion of attribute relationships and their importance in the Project REAL Analysis Services Technical Drilldown white paper.

It is an EXTREMELY important topic (in my estimation probably *the* most important to understanding the basics for how AS2K5 is different from AS2K

AS2K5 is an attribute-based system; AS2K is a hierarchy-based system.

And the core of that is attribute relationships.

See http://www.microsoft.com/sql/bi/ProjectREAL

_-_-_ Dave

Thursday, February 9, 2012

Are Relationships Necessary

My hosting servers have "myLittleAdmin" installed. I am new to ms sql I have some experience with Access but I have never set up Relationships inside the database server. I have always just relied on my application logic to call the relations. What is the benefit to setting up relationships in sql, if any?The closer you can put business rules to the hardware itself, the fewer things can go worng ;) enforcing them. If you rely on applications to enforce your business rules, the first application that forgets to apply a rule, or worse yet mis-applies a rule will cause problems. If the database server enforces your rules, you won't store any data that violates the constraints, which means LOTS fewer hours spent trying to figure out "what created the mess that we're in" and more hours on the beach with a margarita in hand!

-PatP|||i assume that you are referring to foriegn key / primary key relationships.
if this is the case, the hands down winner is referential integrity.
this type of integrity from the trinity( domain entity referential) is in many ways the star of relational databases.
it prohibits the modification of a 'PARENT' key value if there are 'CHILD' values present in the referring column.
It also Restricts the entry of 'CHILD' values in a foriegn key column if an appropriate value does not already exist in the 'PARENT' Table.

check out UNDERSTANDING RELATIONAL DATABASES by fabian pascal.
if you can get through the vendor attacks, it's a good book built on sound concepts. (although pat may disagree. he and fabian dont see eye to neck)
also database design for the mere mortal will help you with the process of becoming normal|||scott, the examples of relational constraints you gave are merely the defaults

don't forget you can also define ones like "if you change the value of a primary key, the database automatically takes care of updating all related foreign keys"

;)

let me take a guess at what CreativeLeaf was asking

in access, there's a menu option, Relationships, which, um, creates relationships between access tables

what happens when you do this is that this relationship information will be used not only to enforce RI but also when you build queries

when you drag and drop tables into the workspace in Query Design View, access automatically generates the JOIN syntax for you!

in other words, you (the person building a query) don't have to know a thing about how the tables could or should be joined, because you (the table designer) have already defined that and it's built into the tables

neat, eh?

oh, and of course it does enforce RI too, including the ON UPDATE CASCADE that i alluded to earlier

(see my article Relational Integrity (http://r937.com/relationalintegrity.html) for other types of RI)

fast forward to the day you want to do the same in sql server

RI is declarative, so you set up your primary and foreign keys properly using CREATEs, and you get the same benefits (i.e. relational integrity, your tables will never be out of sync with each other)

but does this help you when building queries? nup

you still have to know the relationships if you want to write queries against the database

access is such a great database system

i love it!!

:cool:|||scott, the examples of relational constraints you gave are merely the defaults

don't forget you can also define ones like "if you change the value of a primary key, the database automatically takes care of updating all related foreign keys"
We all know access, I use it quite a bit to build the erd and work out issues with queries.

But why should i mention cascading updates or deletes?
his question was [What is the benefit to setting up relationships in SQL, if any? I figure the required protection of the enities is much more important than cascades. So, I wouldnt say the benefit of relations is to cascade their changes. however, in the spirit of fairplay, i am willing to meet you halfway on this, so....I do use cascade , and it doesnt leave spots on my glasses. :eek:

"Thats my story and i'm stickin' to it.." Franz Kafka