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

Add column to View or use Named Query in DSV? Expand / Collapse
Author
Message
Posted Friday, July 11, 2014 3:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:18 AM
Points: 2,048, Visits: 1,403
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
Post #1591515
Posted Sunday, July 13, 2014 10:05 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:03 PM
Points: 2,967, Visits: 2,569
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.



Post #1592050
Posted Tuesday, July 15, 2014 7:35 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:37 AM
Points: 3,090, Visits: 783
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/
Post #1592560
Posted Tuesday, July 15, 2014 7:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 13,639, Visits: 10,531
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1592561
Posted Wednesday, July 16, 2014 6:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:18 AM
Points: 2,048, Visits: 1,403
Thanks All. So the View it is then. Luckily I defaulted to the 'right' choice!

Regards
Lempster
Post #1593016
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse