Hi

I have an order table with columns:
- order_id
- create_date
- accepted_date
- completed_date

I need some suggestion on cube model and mdx definition preparing such
a report layout:

Layout:
created date
time measure 2005-01-01 2005-01-02 ...
2005-01-01 crated count 5 X
accepted count 4 X
completed count 4 X
2005-01-02 crated count X 6
accepted count 1 5
completed count 1 4
....

TIA,
Kamel

Hi Kamel,

With AS 2005, a single date dimension can be added to a cube in multiple
roles (as in Adventure Works) - in this case, Created, Accepted and
Completed. But can you explain how the "createdcount" measure should
work with the "createddate" - and is time on rows another dimension?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

Yes. Time is another dimension on rows. This is general time dimension
under which count of 3 dates is made.

Dimensions:
"General Time" and "Measures" on rows
"Created Date Time" on columns

Sample Table:
"Order ID" "Created date" "Accepted date" "Completed date"
1 2005-01-01 2005-01-01 2005-01-02
2 2005-01-02 2005-01-02 2005-01-02

For this sample recordset report should look like this:

created date
time measure 2005-01-01 2005-01-02 ...
2005-01-01 created count 1 0
accepted count 1 0
completed count 0 0
2005-01-02 created count 0 1
accepted count 0 1
completed count 1 1

Any ideas,
Kamel

Hi Kamel,

Here's a high-level approach in AS 2005, based on my understanding of
your scenario:

- Create 4 cube time dimensions: General, Created, Accepted and
Completed, based on a single dimension playing multiple roles. [General
Time] does not directly relate to a Measure Group, but is for
analysis/reporting purposes.

- [OrderCount] is a cube measure with "Count" aggregation on order_id,
then there are 3 calculated measures:

[Measures].[CreatedCount]:
LinkMember([General Time].[Time].CurrentMember,
[CreatedTime].[Time]))
[Measures].[AcceptedCount]:
LinkMember([General Time].[Time].CurrentMember,
[AcceptedTime].[Time]))
[Measures].[CompletedCount]:
LinkMember([General Time].[Time].CurrentMember,
[CompletedTime].[Time]))
- The MDX query with desired layout will look like:

CrossJoin([GeneralTime].[Time].[Day].Members,
{[Measures].[CreatedCount],
[Measures].[AcceptedCount],
[Measures].[CompletedCount]}) on 1
from [OrderCube]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thank you very much Deepak!