MDX Query Help
I'm new to MDX and apparently still thinking with an SQL mind.
Is there a synonymous technique to correlated sub querying in MDX?
I've populated a test click stream cube ( Pseudo Schema at end of message-
other dimensions excluded for clarity-)
The question I'm trying to answer with the query is

What are the stats(agg sum) of the second clicks in the stream where the
first click is the home page (Id 184)

In the relational world I'd do something like this - SQL Query 1 - (at end
of message).

and the next step would be, of those give me click 3 - Sql Query 2 -.

I can put -MDX Query 1- in a pivot table (OWC), but it just gives me all the
totals
how can I restrict to show click2 only when click1 is a certain value.

Any help would be greatly appricated. I'm sure this must be possible in MDX.
Or is there a better cube schema that would give me this information.

Thanks



--------
Sql Query 1
---------
select EventKey,count(EventKey) from EventFact Fact1
Where EventSequenceKey = 2
and exists
( Select 1 from EventFact Fact2
where
EventSequenceKey = 1 and
EventKey = 184 and
EventSessionKey = Fact1.EventSessionKey
)
group by EventKey
order by count(EventKey) desc
---------



---------
SQL Query 2
---------
select EventKey,count(EventKey) from EventFact Fact1
Where EventSequenceKey = 3
and exists
( Select 1 from EventFact Fact2
where
EventSequenceKey = 2 and
EventKey = 773 and
EventSessionKey = Fact1.EventSessionKey
And Exists
( Select 1 from EventFact Fact3
where
Fact3.EventSequenceKey = 1 and
Fact3.EventKey = 184 and
Fact3.EventSessionKey = Fact2.EventSessionKey
)

)
group by EventKey
order by count(EventKey) desc



---------
MDX Query 1
---------
SELECT NON EMPTY {[EventSequence].[All EventSequence] ,
AddCalculatedMembers([EventSequence].[Sequence Name].MEMBERS)}
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
NON EMPTY {[EventPage].[All EventPage] ,
AddCalculatedMembers([EventPage].[Event Name].MEMBERS)}
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON ROWS,
{
[Measures].[Event Fact Id]
}
ON PAGES
FROM [EventClick]
---------





CUBE PSEDUO SCHEMA

Fact Table (EventFact)
---------------------
RowId
EventKey - ie Site Page Key
EventSequenceKey - ie Order of this Click
EventSessionKey - ie UsersSession
DateKey - Standard Date Dimension Key
TimeKey - Standard Time Dimension Key

Event Table
---------------------
EventKey - ie Id
EventName - ie Page Name
etc...

EventSequence Table
---------------------
EventSequenceKey - ie Id
SequenceName - ie Click1 Click2 etc...
EventSequence - ie 1,2,3,4,etc...
etc...

EventSessionKey Table
---------------------
EventSessionKey - ie. Id
EventSessionId = ie. AspSessionId from logs

Based on prior discussions of a similar scenario, a few ideas come to
mind (don't know how well each would work):

- Add the Event dimension in multiple roles (which is easy in AS 2005),
like Page-Click1, Page-Click2, etc. Then, if you select Home Page on
Page-Click1, you can look at the stats for Page-Click2, and so on. But
this requires Event to be added in a role for each Click# of interest.

- Create a Session dimension on EventSessionKey (feasible if number of
sessions isn't huge), in which case MDX could be used to count the
sessions where there is fact data for each relevant combination of Event
and Event Sequence. But this might be slow, since it doesn't leverage
aggregations.

- If Event and EventSequence dimensions are small in size, create a
compound dimension table with all valid combinations, where these are
the 2 attributes. Then create a "path table" with an entry for each
navigation sequence of interest; and a "bridge table" which associates
an entry in the path table to all relevant entries in the compound
Event/Sequence table. The compound dimension can then be modelled in AS
2005 as a Many-to-Many, with the bridge table as the Intermediate
Measure Group.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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