Problem:
This linked-table query takes almost 6 hours to run, and we need it to
run quickly:

select *
from openquery( LABOR, 'select
{[Labor_Periods].[Total Year].[Accounts_Name],
[Labor_Periods].[Total Year].[Version_Name],
[Labor_Periods].[Total Year].[Year_Name],
[Labor_Periods].[Total Year].[Employee_Key],
[Labor_Periods].[Total Year].[Qtr1].[Apr],
[Labor_Periods].[Total Year].[Qtr1].[May],
[Labor_Periods].[Total Year].[Qtr1].[Jun],
[Labor_Periods].[Total Year].[Qtr2].[Jul],
[Labor_Periods].[Total Year].[Qtr2].[Aug],
[Labor_Periods].[Total Year].[Qtr2].[Sep],
[Labor_Periods].[Total Year].[Qtr3].[Oct],
[Labor_Periods].[Total Year].[Qtr3].[Nov],
[Labor_Periods].[Total Year].[Qtr3].[Dec],
[Labor_Periods].[Total Year].[Qtr4].[Jan],
[Labor_Periods].[Total Year].[Qtr4].[Feb],
[Labor_Periods].[Total Year].[Qtr4].[Mar]
} on columns,
non empty
Crossjoin(Crossjoin(Filter(Descendants([Labor_Employees].[Total xxx
Inc.], , Leaves),
[Labor_Employees].CurrentMember.Properties("Employee")="Y"),
{[Labor_Accounts].[cost1-],
[Labor_Accounts].[cost2-],
[Labor_Accounts].[cost3-],
[Labor_Accounts].[cost4-]}),
{[Labor_Year].[FY 2007],[Labor_Year].[FY 2003],[Labor_Year].[FY
2004]}) on rows
from Labor
where([Labor_Departments].[No Department],
[Measures].[Amount],
[Labor_Version].[a])')

The result set is 38,832 rows with relatively small data per row. Each
column in the result set is a relatively small data size (e.g., 30-40
characters or less)
The cube contains some calculated cells.
The cube size = 3.91MB only.
Cube includes 6 dimensions
fact table size: 300,000 rows
The MOLAP cube is write-back enabled. The back end tables are linked
to Oracle.

Software Environment:
SQL Server 2000
Analysis Services VERSION 8

Any help is greatly appreciated.

RT

verify your aggregations in the cube. (use the usage based optimization
wizard)

but regarding the number of rows in the source and what you do,
why you don't access the Oracle database himself instead of the cube?


"RT" <robert.talbott (AT) maritz (DOT) com> wrote

Jeje,

Thanks for the response.

The problem turned out to be an issue with our writeback table. The
writeback table exited on the Oracle side, which was causing
performance issues. Once we moved the writeback table to SQL Server,
performance greatly improved.

Robert

Jeje wrote: