Cube design question

I have a Fact table which stores details about each revision made on the document.

There is a one to many relationship between a document and the revisions

I need to display information based on the last revision made by a particular UserType.

How do I select records from the fact table based on a particular UserType and the maximum revision number for that usertype?

Hi Reena,

Are you using AS 2000 or AS 2005?

So you have RevisionFact table and also Dim_Dt, Dim_UserType, Dim_Users, Dim_Doc etc. and you store every revisions in fact table along with UserTypeID, Date_ID, Doc_ID, and User_ID.

It would be easy if you tell what measures you store in fact table. You can use "Count" Aggregate Function in measure and query using UserType , Date and measure count on the field you store for each revisions in fact table.

-Ashok

|||

I am using AS 2005

I need to display the count of documents that were written within the target time as well as the count of documents that were not written within the target time set for each user.

The user list has to be displayed along the rows and the count of documents along the columns ( 2 columns)

I have measures that would display time taken to write a document and target time for the document.

Using COUNT function I can get the count of documents written by a user.

But how do I break that count up into two separate columns displaying number of documents written within target time and number of documents written outside target time - for each user?

|||

There are a few ways you could do this, but here's one suggestion. Its a very simple pattern which csn be used again and again for similar problems.

You have a table or view with the time taken and target time in as columns.

Add another column (this is easier if you have a view) and define it as:

case when TimeTaken>TargetTime then 0 else 1 end as TargetMetId

create a lookup table as TargetMet with values

0,"Target Not Met"

1,"Target Met"

You can then link the 2 together and create a nice TargetMet Dimension. This will give you all of the counts you require.

You can expand this by changing the case statment, and building a lookup table like this

1,"Finished Same Day"

2,"Finished Early"

3,"On Time"

4,"A bit late"

5,"Do they still work here?"

etc

|||Thanks for the reply.