Hi everyone,
i have 2 questions on olap and mdx:
1) i rean the same query in mdx and in sql (in sql format) on the same
server on got back exactly the same result and on the same time. shouldnt
olap be faster the sql ?
2) i have this mdx query:
WITH
MEMBER
[Measures].[CampaignID]
As ' [CampaignName].CurrentMember.Properties ( "Key" ) '
MEMBER
[Date].DateRange
As ' Aggregate ( { [Date].[All Date].[2005].[7].[12]:[Date].[All
Date].[2005].[7].[13] } ) '
MEMBER
[Sector].SectorOp
As ' Aggregate ( { [Sector].[All Sector].[English],
[Sector].[All Sector].[Spanish] } ) '
SELECT
Non Empty
{ [Measures].allmembers } ON COLUMNS,
Non Empty
{ [CampaignName].[CampaignName].members } ON ROWS
FROM
[ADV_SMALL]
WHERE
( [Date].DateRange,
[Sector].SectorOp,
[Product].[All Product].[Food],
[Program].[All Program].[TV] )

the query should return all the Campaign in spanish and english between the
12-13/7/2005 from tv on food product.
it work fine when i drop the member [Measures].[CampaignID] and get exactly
the Campaigns answering to the where (about 140). but with that member i get
all the Campaigns members (about 243,000) with no connection to the where
(the Measures of some of the Campaigns are indeed empty not including the
CampaignID that all Campaigns have).
what is the problem and how do i fix it??
thanks

On Question #1, not sure what you mean by "the same query in mdx and in
sql (in sql format)" - is that the special SQL dialect for Analysis
Services?

On Question #2, [CampaignID] should be made null
whenever[CampaignName].CurrentMember is empty, like:

MEMBER
[Measures].[CampaignKey] As
'[CampaignName].CurrentMember.Properties ( "Key" )'
MEMBER
[Measures].[CampaignID] As
'iif(IsEmpty([Measures].DefaultMember), Null,
[Measures].[CampaignKey])'
...


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks for your replay.
what i did is re-write the query so it run on sql server (not olap) since my
olap datasource is the same sql server so both queries gave me the same
result/ my question was should the time of returning the data be the same (i
thought olap is faster).
thanks again

"Deepak Puri" wrote:

Hi Ruby,
you are using two dynamic aggregations:

MEMBER
[Date].DateRange
As ' Aggregate ( { [Date].[All
Date].[2005].[7].[12]:[Date].[*All
Date].[2005].[7].[13] } ) '
MEMBER
[Sector].SectorOp
As ' Aggregate ( { [Sector].[All Sector].[English],
[Sector].[All Sector].[Spanish] } ) '

For these aggregations the analysis server cannot create aggregations
in advance.
Hence the server has to calculate just as the sql server (In fact, I
think sql server is better at calculating values). When you query
preaggregated data the analysis server should be faster.

HTH Lutz Morrien

THANKS,
in case of creating the query dynamicly i cant use preaggregated, can i?

"Lutz.Morrien" wrote:

PreAggregations are created based on the number of dimensions and their
levels in the cube and the performance option selected in the Storage
Design Wizard..
If you want to still improve the performance of the AS.Please look at
the option of Usage based Optimization where you can add the
aggregations to the existing aggregations based on the users querying
the cube.
Please check in BOL about "Usage-based Optimization"

Regards,
Prasanna