Adventure Works Cube

Hi all,

I try to browser the Adventure Work Cube from the SQL Server Business Intelligence Development Sudio, but it give me the wrong result. Fisrt I have paste the Date.Fiscal hierachy in the Filter Filter and select July 1 2001 and July 2 2001, then I paste the Sales Amount measure in the Data Field. The result is $517,737.44. Now I paste the Date.Fiscal Year attribute in the Row Fields, but the result is change to $503,80592. The result is wrong. The sales amount has been excluded July 2 2001 data. Why this happen?

Thanks,

Thomas,

I've recreated the steps you described. I get the same $517,737.44 value you described. When I drag the Date.[Fiscal Year] attribute hierarchy to the rows, I continue to get $517,737.44.

Do you know what service pack you are working with?

Thanks,
Bryan

|||

Hi Bryan

I am using AS 9.00.3.042. When you filter the day, where you drag the Date.Fiscal hierarchy. If I put the Date.Fiscal hierarchy in the dimension filter panel, then the result is correct. However, if I put the Date.Fiscal hierarchy in the Drop Filter Fields, then the result is not correct.

Thanks,

|||

Sorry, but I'm just not getting the same behavior and I'm using the same SP level. Could you possibly trace the MDX that is submitted for the query (using Profiler) and post that to this thread? That might help determine what's going on.

Thanks,
Bryan

|||

Hi Bryan,

I am using 64bit, do you think that cause the problem. Here is what the MDX looklike:

WITH
SET [Filtered] AS
'
DISTINCT(
{
[Date].[Fiscal].[Date].&[2],
[Date].[Fiscal].[Date].&[1]
}
)
'
SET [Tree] AS
'
HIERARCHIZE(
GENERATE(
[Filtered],
{
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Year]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Semester]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Fiscal Quarter]),
ANCESTOR([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Month]),
[Date].[Fiscal].CURRENTMEMBER
}
)
)
'
SELECT [Tree] ON Columns FROM [Adventure Works]

<PropertyList xmlns="urnchemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>SchemaData</Content><Format>Multidimensional</Format><AxisFormat>TupleFormat</AxisFormat><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

CREATE SESSION
MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS
'
AGGREGATE({
[Date].[Fiscal].[Date].&[1],
[Date].[Fiscal].[Date].&[2]
})
'

<PropertyList xmlns="urnchemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Content>None</Content><Format>Tabular</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

SELECT
NON EMPTY [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
{
[Measures].[Sales Amount]
}
ON ROWS
FROM [Adventure Works]
WHERE
(
[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__]
)
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

<PropertyList xmlns="urnchemas-microsoft-com:xml-analysis" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/"><Catalog>Adventure Works DW</Catalog><Timeout>3600</Timeout><Format>Native</Format><ShowHiddenCubes>true</ShowHiddenCubes><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><ClientProcessID>3696</ClientProcessID><LocaleIdentifier>1033</LocaleIdentifier></PropertyList>

Thanks,

|||

Hi Bryan,

Here is the result:

Date.Fiscal

(Multiple Items)

Fiscal Year Sales Amount

FY2002 $503,805.92

Grand Total $503,805.92

|||

I'm not seeing the set definition for [{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0].

B.

|||

Sorry Bryan,

I forgot to include this:

CREATE SESSION
SET [Adventure Works].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Axis1Set0] AS
'
{
{ [Date].[Fiscal Year].[All Periods] },
AddCalculatedMembers([Date].[Fiscal Year].[Fiscal Year].MEMBERS)
}
'

|||

Here's what I've been able to figure out.

You're dropping the Fiscal Hierarchy on to the "Drop Filters Here" section of the interface. This is what generates this session member:

Code Snippet

CREATE SESSION

MEMBER [Adventure Works].[Date].[Fiscal].[{222C0EA1-AF54-4BB3-BDCD-6A91E47F112E}Pivot17Hier33MultiFilter__] AS

'

AGGREGATE({

[Date].[Fiscal].[Date].&[1],

[Date].[Fiscal].[Date].&[2]

})

'

The AGGREGATE function is causing you to select the first member of the set. If you change the members to &[2] and &[3], you'll get the value for just July 2, 2001. If you replace AGGREGATE with SUM, you'll get the right result but that's not really an option here.

I can't really say why the AGGREGATE function is behaving this way, but would suggest you use the other filtering option in the browser window. If you look just above the "Drop Filters Here" section, you'll see a grid with an entry that looks like "Select Dimension". Apply your filter here.

What his does that is different is that it creates a subcube which limits the available values. This will give you the results you are looking for:

Code Snippet

CREATE SUBCUBE [Adventure Works] AS ( SELECT ( { [Date].[Fiscal].[Date].&[1], [Date].[Fiscal].[Date].&[2] } ) ON COLUMNS FROM [Adventure Works])

Hope that helps,
Bryan

|||

Hi Bryan,

The reason I drag the Fiscal Hierarchy in the "Drop Filters Here" is I want it to act like Excel Pivot Table. When I browser the cube though Excel Pivot Table, it has the same error. Most of our end user use Excel Pivot Table to brower the cube, but AS2005 give the wrong answer for this case.

Thanks

Thanks

|||

I'm at a bit of a loss to explain this beahviour.

One possible work around is to hide the [Date].Fiscal Year] attribute. I find that having [Date].[Fiscal].[Fiscal Year] and [Date].[Fiscal Year] in the same cube just confuses things,especially when using OWC as it is not really mutliple hierarchy and attribute aware.

What your users would need to do then is to drag [Date].[Fiscal] onto the column and filter it in place. If you start from your example that is showing the incorrect figures and drag [Date].[Fiscal Year] off the grid and drag the [Date].[Fiscal] from the filter to the columns does that give you a grid that looks how you want?

|||

I asked a friend of mine on the product team and it is possible that this issue was fixed in the hotfix rollup that was released for SP2 http://support.microsoft.com/kb/939537 I could not find a specific reference, but there are a couple of broader issues which it might come under. It might be worth testing this in a Virtual PC to see if it corrects the issue.

As with all hotfixes, it is only recommended to install this if it does address a particular issue. Otherwise they recommend waiting for the next service pack, but I have not heard when another service pack is likely to be released. It would not surprise me to see the next SP come out after Windows Server 2008 is released (which should be Q1 next year), but this is just a guess on my behalf.