SSAS: Named Queries or Database Views?

I was recently asked about the issue of whether it is better to use views in the database or named queries in the data source view (dsv). I don't think there is any one correct answer. I think it comes down to the following 2 issues:

  1. Consistency: If you already have logic in database views, I would continue to use them. As long as you know that you go to one spot to view/change the logic. Putting the logic in 2 different spots could lead to confusion.
  2. Security Permissions: often you may not have permission to alter the source databases, in this case you have no choice but to setup named queries in the dsv.

Ultimately, I am not aware of any significant performance differences, they both result in SQL query being sent to the source system.

Print | posted on Tuesday, September 5, 2006 8:28 PM

Comments on this post

# re: SSAS: Named Queries or Database Views?

Requesting Gravatar...
Named queries should work as well as database views, if they are against SQL Server. If they are against Oracle, or another vendors database, SQL has to use linked servers to execute the queries, which incurs some overhead. If you mix several data sources in the same DSV, this can cause even more performance degredation.

Also, a DSV cannot be used by Reporting Services as a data source (that I am aware of), so having the database views makes designing your reports more consistent.

Also, database views can be tuned and optimized and include hints, if necessary. I don't believe you have this granular of control over a Named Query.
Left by Kory on Sep 06, 2006 12:41 AM

# re: SSAS: Named Queries or Database Views?

Requesting Gravatar...
Your first statement is not entirely correct. Analysis Services does not use linked servers to connect to other vendors databases. You will need to have what ever providers are necessary installed on your Analysis Services server and it will connect directly to the RDBMS. You do not even need to have the SQL Server relational engine installed to run SSAS. Accessing multiple data sources through the DSV should not be significantly slower that any other method of access these data sources.

I think you are right about the DSV not being available for use with Reporting Services and this is probably a good argument for putting logic in views as opposed to the DSV. But something else to consider is that you can point Reporting Services at the cube and report off that. The DSV is not really meant to be an entity that is queried by end users.

I have not tried to put hints in a named query, but a named query is basically an SQL query so unless SSAS tries to do it's own parsing it should be possible to tune the queries in the DSV the same as you would with a view. I usually try to do most of my complicated queries during the ETL process, so the data that the cube has query generally pretty simple so I have not come across this issue yet.
Left by Darren Gosbell on Sep 06, 2006 7:44 AM

# re: SSAS: Named Queries or Database Views?

Requesting Gravatar...
One comment on the topic... I generally prefer calculated columns instead of named queries. The reason is that if you use calculated columns, refreshing the DSV will still pick up new columns. Named queries, on the other hand, can't say "select *" as they're translated into a hardcoded list of columns. So refreshing the DSV won't pick up new columns in the tables used in a named query. So if the logic is complex enough to required joins and such, I generally go with a view instead of a named query... because you can do "select *" in a view. Then, you simply rerun all your alter view statements to refresh the column list periodically... then the DSV will pick up the new columns.

Just my 2 cents.
Left by furmangg on Sep 06, 2006 7:10 PM

# re: SSAS: Named Queries or Database Views?

Requesting Gravatar...
furmangg - I agree with most of what you said. The only thing that I am not sure about is using "Select *". 99% of the time it probably will not cause you too many issues, but I tend to avoid using "select *" in production systems. There are some occasions (re-ordering columns in the base tables is one example) where "select *" in views and stored procs can return incorrect results.
Left by Darren Gosbell on Sep 06, 2006 8:34 PM

# re: SSAS: Named Queries or Database Views?

Requesting Gravatar...
I just read this post and I posted some days ago my point of view on my blog: http://www.sqljunkies.com/WebLog/sqlbi/archive/2006/10/03/23768.aspx
I tried to use all these tools in some production environment and my conclusions are based on my experience. In short: VIEWS for logic, NAMED QUERIES for cosmetic changes.
Left by Marco Russo on Oct 07, 2006 7:44 AM

# re: SSAS: Named Queries or Database Views?

Requesting Gravatar...
Hi , i am doing my final project as data mining of BCSC i am in a firm where i cant modify or change the data and there i am using named queries as my fact table by choosing selective columns..;p i think DSV are bound to logical tables
Left by SAQYG on Feb 18, 2011 7:48 AM

Your comment:

 (will show your gravatar)