TimeSpan Prob

[Greg007]

Hello
I have prob TimeDimensions.

Its about customers in a supermarket.

ROWDATA
CustomerID
VisitStartTime
VisitEndTime
Spending

TimeDimension
Year, Month, Day, Hour; Minute

I know how to calculate Total-Spendings along TimeDimension, no Prob.

Question:
How can I calculate the number of Customers that are in the Supermarket (started shopping but have not finished) along TimeDimension??

Thx
Greg

 

[MDXer]

in the fact table or view I would have a bit column like VisitActive 1 for yes 0 for no when End time > Start time and EndTime Is not null the VisitActive 0 if Visit Endtime Is null then 1. Sum all of the 1s and you should have it.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Greg007]

thx Daffy but - as far I understand you in the right manner - in this case a have to calculate "shopping Active" before running the cube for only one fixed Date. In this case it might be faster to use ms-query ;-))
Greg

 

[MDXer]

it probably would be faster unless you are doing realtime olap. If your using SQL Reporting Services you could probably build the required report in there.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck