Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

Time intelligence - still failing at the lowest level

6 views

Skip to first unread message

Vimas

unread,

Nov 27, 2006, 2:32:31 PM11/27/06

Hello,

I was wondering if there is an update to microsoft bug (or feature) that
time intelligence calculation does not work at the lowest level.
Today I re-read all articles related to this problem and I re-tested
functionality and was able easily to reproduce problem (SSAS 2005, SP1).

I read this article http://support.microsoft.com/Default.aspx?kbid=912136,
second part of this kind of implies that this should fix problem, but it
does not.

To test I did following:

Create Date dimension with levels Day, Month, Year. And created hierarchy
[Date].[Date] that consist of Year->Month->Day
I created simple cube and used just one single measure - count and one
dimension Date.
I run code to add time intelligence, and added YTD calculation and latter
remove that calculation and replaced it with simple calculation: I want
value at every cell to be 100. I know this is meaningless, but I want to
prove my point. I included my complete code below.
Now this code does not work at the Day level! I would have to say it works
when Date dimension is in filter area - you can select day level member, and
results are as expected. But as soon as you put day dimension in row are,
put [Date].[Date Date Calculation] in filter area, at the day level code
ALLWAYS returns NA.

I know that other people confirmed this behaviour, but I would like somebody
from Microsoft try to reproduce it and maybe suggest workaround.
Or there is allways a big chance that I am doing something wrong, could you
please point me what?

Thank you,

This is my complete code:
CALCULATE;

Begin Time Intelligence script for the [Date].[Date] hierarchy.

Create Member

CurrentCube.[Date].[Date Date Calculations].[End Of Period]

As "NA" ;

Scope(

{

[Measures].[Claim Count]

}

) ;

(

[Date].[Date Date Calculations].[End Of Period]

,[Date].[Date].Members

, [Date].[Day].Members

) = 100;

End Time Intelligence script for the [Date].[Date] hierarchy.

Peter Yang [MSFT]

unread,

Nov 28, 2006, 2:49:15 AM11/28/06

Hello Vimas,

I understand that you encountered issues for lowest level of time
dimension. I have forwarded this feedback to the product team to see if
there is any explaination for the behavior. If there is any update, I'll
let you know as soon as possible. Thank you for your patience.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Peter Yang [MSFT]

unread,

Nov 29, 2006, 3:35:39 AM11/29/06

Hello vimas,

I tried to reproduce on my side to no available.

I added the following caluated member in sample AW standard edtion to test
and I was not able to reproduce the issue:

=======

Create Member CurrentCube.[Date].[Fiscal Date Calculations].[test2] AS "NA"
;

Scope(
{
[Measures].[Internet Sales Amount]
}
);

( [Date].[Fiscal Date Calculations].[test2],

[Date].[Fiscal Year].[Fiscal Year].Members,

[Date].[Date].Members
) =100
;
End Scope ;

=======

I added [Fiscal Date Calculations] to row, added [Date].[Fiscal] to column
and [Internet Sales Amount]
to Details. After exapanding the tree of [Date].[Fiscal] to date, 100 is
displayed to the lowest level.

If I did not add [Date].[Date].Members in above script, I will see NA for
all cells that is not on Year level.

Also I created a new project/database with a single time dimension and
simple measure, and I also was not able to reproduce the issue.

Create Member CurrentCube.[Due Date].[DateH Due Date Calculations].[test1]
AS "NA" ;

Scope(
{
[Measures].[Sales Amount]
}
);

( [Due Date].[DateH Due Date Calculations].[test1],
[Due Date].[DateH].Members,
[Due Date].[Date].Members
)=110

;
End Scope ;

=======

I'd like to confirm if you have SQL 2005 SP1 installed. Also, if you try
above method, did you see different behavior? If so, please let me know the
exact reproduce steps so that I could check on my side. I look forward to
your update. Thank you.
Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA

Microsoft Online Partner Support

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

======================================================

Vimas

unread,

Dec 1, 2006, 10:20:58 AM12/1/06

Hi Peter,

Thank you very much for helping me.
I belive I am doing exactly same thing as you, but I am getting different
results. Is it possible for you to test this on 64bit machine with Developer
Edition SQL Server 2005?

There is more info about our environment:

From the msmdsrv.exe file properties
Product version : 9.0.2047.0
File version: 2005.090.2047.00
Platform: NT AMD64 (Note - we actually have Intel 64bit, not sure why it
says here AMD)

I created SQL SErver database with 2 tables: Date and Fact. I attached
script bellow for that.
Then I created analysis services project and create dimension [Date] and
measure group [Time Test]

I added time intelligence calculation and then changed it to:

Create Member CurrentCube.[Date].[Date Date Calculations].[Allways100] As
"NA" ;
Scope({[Measures].[Amount]}) ;
([Date].[Date Date Calculations].[Allways100],[Date].[Day].Members) = 100;
End Scope ;

I execute following query:

SELECT {[Measures].[Amount]} ON 0
, {[Date].[Date].Members} ON 1
FROM [Time Test]
WHERE ([Date].[Date Date Calculations].[Allways100])

Results are:
Amount
All 100
2006 100
200611 100
20061129 NA
20061130 NA
200612 100
20061201 NA
20061202 NA
20061203 NA

So, from what I see, we are both doing the same thing, but getting different
results. Is it possible for you to test my code on 64bit machine?

Thank you again for helping me.

There is a script I am using for test:
To create and laod SQL Server database:
CREATE DATABASE TimeTest
go
USE TimeTest
go
CREATE TABLE dbo.Date (DayKey int NOT NULL, MonthKey int NOT NULL, YearKey
smallint NOT NULL)
go
ALTER TABLE dbo.Date ADD CONSTRAINT PK_Date PRIMARY KEY(DayKey)
go
INSERT INTO dbo.Date(DayKey, MonthKey, YearKey) VALUES(20061129, 200611,
2006)
INSERT INTO dbo.Date(DayKey, MonthKey, YearKey) VALUES(20061130, 200611,
2006)

INSERT INTO dbo.Date(DayKey, MonthKey, YearKey) VALUES(20061201, 200612,
2006)
INSERT INTO dbo.Date(DayKey, MonthKey, YearKey) VALUES(20061202, 200612,
2006)
INSERT INTO dbo.Date(DayKey, MonthKey, YearKey) VALUES(20061203, 200612,
2006)
go

CREATE TABLE dbo.Fact (DayKey int NOT NULL, amount int NOT NULL)
go

INSERT INTO dbo.Fact(DayKey, Amount) VALUES(20061129, 1)
INSERT INTO dbo.Fact(DayKey, Amount) VALUES(20061130, 2)
INSERT INTO dbo.Fact(DayKey, Amount) VALUES(20061201, 3)
INSERT INTO dbo.Fact(DayKey, Amount) VALUES(20061202, 4)
INSERT INTO dbo.Fact(DayKey, Amount) VALUES(20061203, 5)
INSERT INTO dbo.Fact(DayKey, Amount) VALUES(20061203, 6)
go

ALTER TABLE dbo.Fact ADD CONSTRAINT FK_Fact_Date FOREIGN KEY(DayKey)
REFERENCES dbo.Date(DayKey)
go

<ID>TimeTestForMS</ID>

<Name>TimeTestForMS</Name>

<Collation>Latin1_General_CI_AS</Collation>

<ImpersonationMode>Default</ImpersonationMode>

</DataSourceImpersonationInfo>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramLayout</Name>

<Value>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</dds>

</Value>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:ShowFriendlyNames</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:ShowRelationshipNames</Name>

<Value>false</Value>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:UseDiagramDefaultLayout</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramViewPortLeft</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramViewPortTop</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramBoundingLeft</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramBoundingTop</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramZoom</Name>

</Annotation>

</Annotations>

</Source>

<Collation>Latin1_General_CI_AS</Collation>

<UnknownMemberName>Unknown</UnknownMemberName>

<Type>Years</Type>

<DataType>SmallInt</DataType>

<ColumnID>YearKey</ColumnID>

</Source>

</KeyColumn>

</KeyColumns>

<DataType>WChar</DataType>

<ColumnID>YearKey</ColumnID>

</Source>

</NameColumn>

</Attribute>

<ID>Month Key</ID>

<Name>Month</Name>

<Type>Months</Type>

<DataType>Integer</DataType>

<ColumnID>MonthKey</ColumnID>

</Source>

</KeyColumn>

</KeyColumns>

<DataType>WChar</DataType>

<ColumnID>MonthKey</ColumnID>

</Source>

</NameColumn>

</AttributeRelationship>

</AttributeRelationships>

</Attribute>

<DataType>Integer</DataType>

<ColumnID>DayKey</ColumnID>

</Source>

</KeyColumn>

</KeyColumns>

<DataType>WChar</DataType>

<ColumnID>DayKey</ColumnID>

</Source>

</NameColumn>

<AttributeID>Month Key</AttributeID>

<Name>Month</Name>

</AttributeRelationship>

<AttributeID>Date Date Calculations</AttributeID>

<Name>Date Date Calculations</Name>

</AttributeRelationship>

</AttributeRelationships>

</Attribute>

<ID>Date Date Calculations</ID>

<Name>Date Date Calculations</Name>

<DataType>WChar</DataType>

<ColumnID>Date_x0020_Date_x0020_Calculations</ColumnID>

</Source>

</KeyColumn>

</KeyColumns>

<DataType>WChar</DataType>

<ColumnID>Date_x0020_Date_x0020_Calculations</ColumnID>

</Source>

</NameColumn>

<DefaultMember>[Date Date Calculations].[Current Date]</DefaultMember>

<IsAggregatable>false</IsAggregatable>

</Attribute>

</Attributes>

<ID>Year Key - Month Key - Day Key</ID>

<Level>

</Level>

<Level>

<ID>Month Key</ID>

<Name>Month</Name>

<SourceAttributeID>Month Key</SourceAttributeID>

</Level>

<Level>

</Level>

</Levels>

</Hierarchy>

</Hierarchies>

</ProactiveCaching>

</Dimension>

</Dimensions>

<Cubes>

<Cube>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramLayout</Name>

<Value>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</ddsxmlobj>

</control>

</ddsxmlobj>

</layoutobject>

</connector>

</ddscontrol>

</dds>

</Value>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:ShowFriendlyNames</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:ShowRelationshipNames</Name>

<Value>false</Value>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:UseDiagramDefaultLayout</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramViewPortLeft</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramViewPortTop</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramBoundingLeft</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramBoundingTop</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramZoom</Name>

</Annotation>

</Annotations>

<Collation>Latin1_General_CI_AS</Collation>

</Attribute>

<AttributeID>Month Key</AttributeID>

</Attribute>

</Attribute>

<AttributeID>Date Date Calculations</AttributeID>

</Attribute>

</Attributes>

<HierarchyID>Year Key - Month Key - Day Key</HierarchyID>

</Hierarchy>

</Hierarchies>

</Dimension>

</Dimensions>

<ID>Amount</ID>

<Name>Amount</Name>

<DataType>Integer</DataType>

<DataType>Integer</DataType>

<ColumnID>amount</ColumnID>

</Source>

</Measure>

</Measures>

<StorageMode>Molap</StorageMode>

<ProcessingMode>Regular</ProcessingMode>

<DataType>SmallInt</DataType>

</KeyColumn>

</KeyColumns>

</Attribute>

<AttributeID>Month Key</AttributeID>

<DataType>Integer</DataType>

</KeyColumn>

</KeyColumns>

</Attribute>

<DataType>Integer</DataType>

<ColumnID>DayKey</ColumnID>

</Source>

</KeyColumn>

</KeyColumns>

<Type>Granularity</Type>

</Attribute>

<AttributeID>Date Date Calculations</AttributeID>

<DataType>WChar</DataType>

</KeyColumn>

</KeyColumns>

</Attribute>

</Attributes>

</Dimension>

</Dimensions>

</Source>

<StorageMode>Molap</StorageMode>

<ProcessingMode>Regular</ProcessingMode>

</ProactiveCaching>

</Partition>

</Partitions>

</ProactiveCaching>

</MeasureGroup>

</MeasureGroups>

</Source>

<ID>MdxScript</ID>

<Name>MdxScript</Name>

<Text>/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If deleted or modified, the data within the cube will be affected.

This command should only be edited if you intend on manually specifying how
the cube will be aggregated.

CALCULATE;

Create Member CurrentCube.[Date].[Date Date Calculations].[Allways100] As
"NA" ;

Scope({[Measures].[Amount]}) ;

([Date].[Date Date Calculations].[Allways100]

,[Date].[Day].Members) = 100;

End Scope ;

</Text>

</Command>

</Commands>

</MdxScript>

</MdxScripts>

</ProactiveCaching>

</Cube>

</Cubes>

<ConnectionString>Provider=SQLNCLI.1;Data Source=IDS-DB4;Integrated
Security=SSPI;Initial Catalog=TimeTest</ConnectionString>

<ImpersonationMode>ImpersonateServiceAccount</ImpersonationMode>

</ImpersonationInfo>

</DataSource>

</DataSources>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:ShowFriendlyNames</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:CurrentLayout</Name>

<Value>_ALL_TABLES_</Value>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:SchemaRestriction</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:RetrieveRelationships</Name>

</Annotation>

<Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:Layouts</Name>

<Value>

<Name>_ALL_TABLES_</Name>

<dds>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</ddsxmlobj>

</control>

</ddsxmlobj>

</layoutobject>

</connector>

</ddscontrol>

</dds>

</DiagramLayout>

<ShowRelationshipNames>False</ShowRelationshipNames>

</Diagram>

</Layouts>

</Value>

</Annotation>

</Annotations>

<xs:schema id="Time_x0020_Test" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:msprop="urn:schemas-microsoft-com:xml-msprop">

<xs:element name="Time_x0020_Test" msdata:IsDataSet="true"
msdata:UseCurrentLocale="true">

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element name="dbo_Date" msprop:FriendlyName="Date"
msprop:DbSchemaName="dbo" msprop:DbTableName="Date"
msprop:TableType="Table">

<xs:complexType>

<xs:sequence>

<xs:element name="DayKey" msprop:FriendlyName="DayKey"
msprop:DbColumnName="DayKey" type="xs:int" />

<xs:element name="MonthKey" msprop:FriendlyName="MonthKey"
msprop:DbColumnName="MonthKey" type="xs:int" />

<xs:element name="YearKey" msprop:FriendlyName="YearKey"
msprop:DbColumnName="YearKey" type="xs:short" />

<xs:element name="Date_x0020_Date_x0020_Calculations"
msprop:ComputedColumnExpression="N'Current Date'" msprop:Description="This
column was created by the Business Intelligence Wizard. An attribute in the
Time dimension has been created that is based on this column. If deleted,
the time intelligence calculations that have been defined in the calculation
script will no longer be valid." msprop:IsLogical="True" minOccurs="0">

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:maxLength value="15" />

</xs:restriction>

</xs:simpleType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element name="dbo_Fact" msprop:FriendlyName="Fact"
msprop:DbSchemaName="dbo" msprop:DbTableName="Fact"
msprop:TableType="Table">

<xs:complexType>

<xs:sequence>

<xs:element name="DayKey" msprop:FriendlyName="DayKey"
msprop:DbColumnName="DayKey" type="xs:int" />

<xs:element name="amount" msprop:FriendlyName="amount"
msprop:DbColumnName="amount" type="xs:int" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

<xs:unique name="Constraint1" msdata:PrimaryKey="true">

<xs:selector xpath=".//dbo_Date" />

<xs:field xpath="DayKey" />

</xs:unique>

<xs:keyref name="FK_Fact_Date" refer="Constraint1">

<xs:selector xpath=".//dbo_Fact" />

<xs:field xpath="DayKey" />

</xs:keyref>

</xs:element>

</xs:schema>

<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1" />

</Schema>

</DataSourceView>

</DataSourceViews>

</Database>

</ObjectDefinition>

</Create>

yongli

unread,

Dec 2, 2006, 4:20:00 PM12/2/06

My issue is similar not but exactly the same. The calcualted member (like
growth, growth%) will disppear when working with some of the other
dimensions. I found out that because some of dimensions have either unknown
member or blank member. By filterring out those unknown or blank member, the
calculated member will display values again. Not sure this is the root
cause, but just very wierd behaviors

Peter Yang [MSFT]

unread,

Dec 4, 2006, 4:21:40 AM12/4/06

Hello Vimas,

Thank you for your reply. From the database you provided I was able to
reproduce the issue on my side. With the following MDX query I saw the same
result as you do:

SELECT {[Measures].[Amount]} ON 0
, {[Date].[Date].Members} ON 1
FROM [Time Test]
WHERE ([Date].[Date Date Calculations].[Allways100])

However, if I use the following to query the result, it gets the proper
result:

SELECT {([Date].[Date Date Calculations].[Allways100])}ON 0

, {[Date].[Date].Members} ON 1
FROM [Time Test]

I also tested on SP2 CTP version (3027) with the same result. It seems this
is a product limitation or issue when this calculated member is in where
clause. I will report this issue to the product team. In the meantime, and
I encourage you submit via the link below

http://lab.msdn.microsoft.com/productfeedback/default.aspx

If there is any update on the issue, we will let you know. Thanks.

Vimas

unread,

Dec 4, 2006, 9:02:44 AM12/4/06

Peter,

Thank you. This functionality is very important to us and right now I am
using workaround were I created extra level for day level. So in my
dimension I have DayKey level, then Day level, month level, etc. As problem
is jus on key level, I hide this level from end users.
I believe that this workaround can cause performance impact, so I was trying
to optimize it, but I'll have to wait for a fix from Microsoft.

Thanks again

Regards

"Peter Yang [MSFT]" <pet...@online.microsoft.com> wrote in message
news:YGkjdW4F...@TK2MSFTNGHUB02.phx.gbl...

Peter Yang [MSFT]

unread,

Dec 6, 2006, 3:00:03 AM12/6/06

Hello Vimas,

Thank you for feedback and sharing your workaround on this issue! This
issue/bug is filed in our internal database and product team will evaluate
the impact and they will consider a fix or workaround if necessary. If
there is any update, we will let you know. Thanks again for your input and
we appreciate your patience.

0 new messages