Hi,

I'm using MSAS 2000 and I'm building a cube based on a View as the fact
table. The View calls a stored function that I have created that processes a
datetime column.

Having introduced this function the process time of the cube has gone from 4
minutes to 58 minutes. Using SQL Profiler it appears that MSAS is issuing
the same select statement (i.e. the View) for every row in the target table
(800,000 in this case). Even if I reduce the functionality of the stored
function to simply return the number 1 the processing time is still as long.

Has anyone else found this problem with processing cubes with stored
functions and is there a workaround besides saving the view output to a new
table?

Are you saying that you are seeing AS issue a SQL statement for every row in
the partition being processed? If so, then something is big time wrong. In
over 7 years working with the product, I have never seen such a thing. My
first guess would be to look on the RDBMS-side. Turn on the system-wide
processing log file (bring up Analysis Manager, then right-click on the
server and select "properties" -- then go to the logging tab). In it will be
the interactive SQL query that AS issues. There should only be one. Cut it
out and paste in to QA and try running it interactively.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Gary Wise" <Gary Wise (AT) discussions (DOT) microsoft.com> wrote

Thanks for the feedback. I did what you suggested and AS is only issuing the
single statement. The problem is with the use of the stored function itself.
I've created a little test function and this shows the seemingly anomalous
behaviour too when run from QA, i.e. the Profiler shows the statement being
issued for each row in the table. If you run the following script and then
profile the select statement you will see that it gets issued 3 times, once
for each row:

use tempdb;

create table Names
(
name_id char(1) not null primary key,
name varchar(50) not null
);

insert into Names values ('a', 'aaaa')
insert into Names values ('b', 'bbbb')
insert into Names values ('c', 'cccc')

go

create function ThreeChars(@name as varchar(50))
returns char(3)
as
begin
return left(@name,3);
end

go

select *, dbo.ThreeChars(name) from names

Am I missing something with the design and use of stored functions?

"Dave Wickert [MSFT]" wrote:

I'd try on one of the SQL RDBMS newsgroups.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Gary Wise" <GaryWise (AT) discussions (DOT) microsoft.com> wrote

Hi,

I think the problem is that unfortunately when you use a UDF SQL Server will
always apply it row by row. I.e., is not able to apply it to all rows.

If you can, try to implement the real UDF (not the example) as a "case
statement" and check the performance.

"Dave Wickert [MSFT]" wrote:

Thanks Dave and Tiago, I understand more about UDFs now and how they are
processed. I will look for an alternative solution.

Cheers,
Gary.

"Tiago Rente" wrote: