RE: Problem when using DateDiff and slicing
- From: changliw@xxxxxxxxxxxxxxxxxxxx (Charles Wang[MSFT])
- Date: Tue, 08 Apr 2008 13:15:56 GMT
Hi Henrik,
The issue of your sample 3 was actually caused by the CurrentMember.
Apparently the iteration should happen on Employee level, though you use
the organization slice, actually the statistics are for all employees in
the slice cells. For your requirement, it is not easy to implement if you
do not create a new cube or a new measure group. The reason is that you are
trying to perform an aggregation based on multiple dimension analysis,
however you do not have matched measures in your underlying fact table. I
recommend that you create a dynamic view in your database, for example, you
can name it as FactEmployeeServiceLenView:
CREATE VIEW FactEmployeeServiceLenView
as
SELECT DimEmployee.EmployeeKey,
DimOrganization.OrgnizationKey,DateDiff(year,DimEmployee.[HireDate],getdate(
)) as ServiceLength
FROM DimEmployee JOIN DimOrganization ON DimEmployee.OrgnizationKey =
DimOrganization .OrgnizationKey
After that, create a new cube, add the view into your data source view, and
the related dimensions DimEmployee and DimOrganization. After that, you can
process and deploy the cube and run the MDX query as following:
SELECT [Measures].[Service Length] ON COLUMNS,
[Organization].[Organization] ON ROWS
FROM [The cube name]
If you do not want to create a new cube, you can also create a measure
group to your existing cube. You still need to add the view to your data
source view and define the relationships among the new measure group and
the dimensions employee and organization on the Dimension Usage pane. After
process and deploy your cube, you can run the same MDX query as the above
statement.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
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.
=========================================================
.
- Follow-Ups:
- Re: Problem when using DateDiff and slicing
- From: Henrik Skak Pedersen
- Re: Problem when using DateDiff and slicing
- References:
- Problem when using DateDiff and slicing
- From: Henrik Skak Pedersen
- Problem when using DateDiff and slicing
- Prev by Date: Forward cover stock measure using cumulative sum
- Next by Date: Re: MDX Problems with ParallelPeriod
- Previous by thread: Problem when using DateDiff and slicing
- Next by thread: Re: Problem when using DateDiff and slicing
- Index(es):
Relevant Pages
|
