Click here to monitor SSC
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
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2683 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
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3563 Visits: 3104
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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3791 Visits: 901
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16443 Visits: 13202
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?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Lempster
Lempster
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2683 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