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

  • 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.

  • EDIT: SSC flipped and accidentally double posted my reply

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • So, it should be define at any one stage.

    Thanks koen,appreciate your feedback

  • 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 ;-)....

  • 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.

  • That is certainly another path you can take.

  • 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.

  • Correct. It all depends on the situation and the environment in which you work.

  • 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 🙂

  • sneumersky (1/20/2014)


    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 🙂

    No problem 🙂 Healthy discussions are always appreciated.

    Personally I almost never use FKs in the data warehouse. They stand in the way of my truncate table statements 🙂

    But, the ETL has the responsability though to make sure data integrity is intact. Lookups are done before rows are inserted into a fact table. If the lookup fails, the row has to be dealt with before it goes into the fact table.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

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

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