No data returned for basic query in Adventure Works

[Database Administrator]

Hi,

I've been having odd issues running some tests against the Adventure
Works data where I send a query for 45 members and only get 27 back.
After breaking down the mdx query into smaller chunks. I found that
any member from [Date].[Calendar Quarter of Year], when crossjoin or
Tupled with any other Date hierarchy, returned as if the member didn't
exist.

Easy to replicate


SELECT
{
[Date].[Week of Year].&[1]
}
*
{
[Date].[Calendar Quarter of Year].&[CY Q2]
}
ON 0
FROM [Finance]

Should return me a 2 members and a single cell. The message I get is

Executing the query ...
Obtained object of type:
Microsoft.AnalysisServices.AdomdClient.CellSet
Formatting.
Cell set consists of 1 rows and 0 columns.
Done formatting.
Execution complete

I'm running SSAS 2005 SP2 on Windows Vista with latest SP/Patches
installed


Can anyone explain why?

TIA

[Database Administrator]

On Sep 1, 12:47*pm, gilly1...@gmail.com wrote:
> Hi,
>
> I've been having odd issues running some tests against the Adventure
> Works data where I send a query for 45 members and only get 27 back.
> After breaking down the mdx query into smaller chunks. I found that
> any member from [Date].[Calendar Quarter of Year], when crossjoin or
> Tupled with any other Date hierarchy, returned as if the member didn't
> exist.
>
> Easy to replicate
>
> SELECT
> {
> [Date].[Week of Year].&[1]}
>
> *
> {
> [Date].[Calendar Quarter of Year].&[CY Q2]}
>
> ON 0
> FROM [Finance]
>
> Should return me a 2 members and a single cell. The message I get is
>
> Executing the query ...
> Obtained object of type:
> Microsoft.AnalysisServices.AdomdClient.CellSet
> Formatting.
> Cell set consists of 1 rows and 0 columns.
> Done formatting.
> Execution complete
>
> I'm running SSAS 2005 SP2 on Windows Vista with latest SP/Patches
> installed
>
> Can anyone explain why?
>
> TIA

I've just recently discovered that the issue is not related to the
entire Hierarchy, but only to members
Qy C2, Qy C3 and Qy C4

[Database Administrator]

I think it is the result of Autoexists behavior.
http://msdn.microsoft.com/en-us/library/ms345116.aspx

Actually, DimTime table has no data mapped to the tuple, ([CY Q2], [1]).

select * from DimTime
where WeekNumberOfYear = 1 and CalendarQuarter = 2

Ohjoo

wrote in message
news:1f5a6cae-8473-4935-8fe1-471c83dc18b7@i20g2000prf.googlegroups.com...
On Sep 1, 12:47 pm, gilly1...@gmail.com wrote:
> Hi,
>
> I've been having odd issues running some tests against the Adventure
> Works data where I send a query for 45 members and only get 27 back.
> After breaking down the mdx query into smaller chunks. I found that
> any member from [Date].[Calendar Quarter of Year], when crossjoin or
> Tupled with any other Date hierarchy, returned as if the member didn't
> exist.
>
> Easy to replicate
>
> SELECT
> {
> [Date].[Week of Year].&[1]}
>
> *
> {
> [Date].[Calendar Quarter of Year].&[CY Q2]}
>
> ON 0
> FROM [Finance]
>
> Should return me a 2 members and a single cell. The message I get is
>
> Executing the query ...
> Obtained object of type:
> Microsoft.AnalysisServices.AdomdClient.CellSet
> Formatting.
> Cell set consists of 1 rows and 0 columns.
> Done formatting.
> Execution complete
>
> I'm running SSAS 2005 SP2 on Windows Vista with latest SP/Patches
> installed
>
> Can anyone explain why?
>
> TIA

I've just recently discovered that the issue is not related to the
entire Hierarchy, but only to members
Qy C2, Qy C3 and Qy C4