Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

IS ii madatory to make relationship while creating DSV (Data Source View) in SSAS? Expand / Collapse
Author
Message
Posted Tuesday, January 14, 2014 7:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:51 AM
Points: 49, Visits: 176
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.
Post #1530657
Posted Tuesday, January 14, 2014 7:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 13,275, Visits: 10,152
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1530679
Posted Tuesday, January 14, 2014 7:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 13,275, Visits: 10,152
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1530680
Posted Wednesday, January 15, 2014 12:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:51 AM
Points: 49, Visits: 176
So, it should be define at any one stage.

Thanks koen,appreciate your feedback
Post #1530959
Posted Thursday, January 16, 2014 10:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
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 ....
Post #1531675
Posted Friday, January 17, 2014 12:14 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:49 PM
Points: 804, Visits: 1,989
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.



Post #1532227
Posted Monday, January 20, 2014 8:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
That is certainly another path you can take.
Post #1532668
Posted Monday, January 20, 2014 8:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:49 PM
Points: 804, Visits: 1,989
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.


Post #1532684
Posted Monday, January 20, 2014 8:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
Correct. It all depends on the situation and the environment in which you work.
Post #1532688
Posted Monday, January 20, 2014 9:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
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
Post #1532716
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse