I have SQL schema as follows:

Incidents table OpenIncidents table
----------------- ------------------------
OpenID BigInt <-------------------- OpenID BigInt
Location Varchar(20) IncidentID BigInt
StartDtTm DateTime AsOf AsOf
Age BigInt

The "AsOf" time is at midnight each day. The Age is the diff between AsOf
and StartDtTm of the original Incident.
There could be millions of Incidents and 2 to 3 times as many OpenIncidents.

I want to get the details (Location, Age) of the worst 10 incidents open at
any time during a specific period. So for all time, the SQL query would be:

select top 10 Location, oi.OpenID, Max(Age)
from Incidents i join OpenIncidents oi on i.OpenID = oi.OpenID
group by Location, oi.OpenID order by Max(Age) desc

How would I set up a cube to get this info from OLAP?

Thanks,

LMcPhee

Do you need to get the individual OpenID values for the worst 10
incidents from the cube - in that case, setting up an Incident dimesnion
with millions of members may be a stretch with AS 2000 32-bit, but
should work with 64-bit or with AS 2005. If you don't need OpenID
values, how many locations are there; and can the 10 worst incidents
include more than 1 OpenIncident for the same Incident?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

For now, I am stuck with AS 2000 32-bit.

There would typically be between 5-10K members in the Location dimension.
But, as you can see from the original SQL query, for each Incident I want to
analyze only the oldest OpenIncident. So the answer to your last question is
no - one Incident cannot contribute > once to the Top 10.

A couple of other factors I neglected to mention in first posting:
1. The Incident table also has a "Closed" DateTime field for when the
Incident is closed, and so no longer is counted for the OpenIncident table at
the end of each day.
2. At any given time there can never be more that one open Incident for a
Location.

For SQL this could use a different query - base on Incidents with Closed
time in the analysis time-range union with Incidents with NULL Closed value.
The latter's Age would be (End of analysis period - StartDtTm). For OLAP,
maybe have an Age2 calc-member that uses IIF() to return Age where Age is
non-NULL, and DateDiff(StartDtTm, End-of_Period) when it is NULL?

"Deepak Puri" wrote: