SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IS ii madatory to make relationship while creating DSV (Data Source View) in SSAS?


IS ii madatory to make relationship while creating DSV (Data Source View) in SSAS?

Author
Message
harsimranjeetsinghwasson
harsimranjeetsinghwasson
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 238
I am creating a dsv (Data Source View) from our datawarehouse database which has no relationship in dim and fact tables.

So is it mandatory to create such relation while creating DSV.

As i guess for while going for next step i.e. creating a cube
on that DSV
SSAS wizard can not distinguish which one to take measure and which one to take facts.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28161 Visits: 13268
EDIT: SSC flipped and accidentally double posted my reply


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28161 Visits: 13268
It is not mandatory, but if you don't specify any relations, you must define them yourself in the dimension usage tab of the cube.
In my opinion it is easier in the DSV.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
harsimranjeetsinghwasson
harsimranjeetsinghwasson
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 238
So, it should be define at any one stage.

Thanks koen,appreciate your feedback
sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2510 Visits: 487
A DSV will auto-detect foreign key constraints between a fact and a dimension, but foreign keys add unncessary overhead to an ETL process.

The approach to create the relationships in the DSV is, in my opinion, the more performant way. The data modeler on your team (if not you) may not be too happy with you though ;-)....
RonKyle
RonKyle
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2852 Visits: 3505
foreign keys add unncessary overhead to an ETL process.


This is true on the face of it, although whether that overhead is significant depends on the number of rows being loaded. I don't consider my loads to be low, although I'm sure there are many processes that feed in a far larger number of rows that mine. If the keys are causing unacceptable slowdowns, then the solution is to disable them during the ETL process and re-enabling them with the WITH CHECK option on completion of the process, not removing them from the design solution. Without them, no matter how good the ETL process is, errors will eventually get in. In addition to the integrity guarentees that this provides, the optimizer can use these keys to produce a better execution plan. Not having them may also require you to turn off the error checking during processing. This was intended to support the development of prototypes. For a production solution this should be considered unacceptable.



sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2510 Visits: 487
That is certainly another path you can take.
RonKyle
RonKyle
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2852 Visits: 3505
Just because there are multiple paths doesn't mean that they are all equally valid. Just as in other things there are accepted practices that exist for good reason, so it is in database and data warehouse design. These "rules" exist for a reason. I'm dealing with a warehouse where the designer took the "other patch" and we are having some data integrity issues. And if the design does not allow the processing to run with all the error checking turned on, it is unacceptable as a production solution.



sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2510 Visits: 487
Correct. It all depends on the situation and the environment in which you work.
sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2510 Visits: 487
In my last two situations we had an intense data quality/validation schema and dedicated data stewards (rare, I know).

The compromise would be, as you stated, to disable a FK constraint during a load, and also schedule/re-enable constraints periodically/daily to check for violations. I am from the school of thought that one should manage FK constraints in the staging area.....on a workbench--so to speak.

However, let me make it clear that I do not feel strongly about one way or the other, and if my dba felt that strongly about FK constraints in a fact table, then my all means have them there. I certainly understand the hesitance of a dba on not including them--some scars run deeper than others. At the same time, I would expect all project team members to keep an open mind that ANY design solution should measure the pros against the cons.....evaluate the ETL and query workload and TEST, TEST, TEST.

Hopefully, the initial forum poster got the answers he/she was seeking, and my apologies to Koen for adding my $.02 when I probably did not need to :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search