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


Add column to View or use Named Query in DSV?


Add column to View or use Named Query in DSV?

Author
Message
Lempster
Lempster
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5216 Visits: 1657
I have a Fact table based on a View. Rather than create a Calculated Measure in the front-end BI application (Tableau), I decided to alter the View to include an extra column based on a CASE statement.
It occurred to me subsequently that I could have left the View untouched and instead replaced the View with a Named Query in the Data Source View.

My question is: which method is preferred? Does it depend on data volumes?
I know that you can't use Named Queries in a DSV if the cube storage mode is ROLAP, but that's not an issue because I'm using MOLAP.

Thanks
Lempster
happycat59
happycat59
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8959 Visits: 3281
Lempster (7/11/2014)
I have a Fact table based on a View. Rather than create a Calculated Measure in the front-end BI application (Tableau), I decided to alter the View to include an extra column based on a CASE statement.
It occurred to me subsequently that I could have left the View untouched and instead replaced the View with a Named Query in the Data Source View.

My question is: which method is preferred? Does it depend on data volumes?
I know that you can't use Named Queries in a DSV if the cube storage mode is ROLAP, but that's not an issue because I'm using MOLAP.

Thanks
Lempster


Personally, I prefer to create the columns in the view. This means that all applications using the view can use the same definition. Otherwise you may need to create an equivalent value in every application that needs it.

Another reason is that the DSV editor is not real special and will reformat your named query to suit itself. This doesn't help with readability of the query.



Thomas LeBlanc
Thomas LeBlanc
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4631 Visits: 915
I agree with HappyCat, place it in the view. This gives you (or a DBA) more flexibility in performance tuning the query in the view than a column added in the DSV.

Thomas

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65076 Visits: 13298
Another vote for the view.
The DSV has the tendency to hide things for you, while a view is more straightforward.
Also, if the database is in source control it is more obvious to other people that a change took place and that it was the addition of a column.


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
Lempster
Lempster
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5216 Visits: 1657
Thanks All. So the View it is then. Luckily I defaulted to the 'right' choice! :-)

Regards
Lempster
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