Create a time dimension

Hi !
I have a question : my dataBase contains a table which have an attribute startTime.
The startTime give the day and the hour of an event.
I want to 'display' the event for a period time (one day or one week, or between to date...).
But I don't know how to do... Because if I add a dimension with this startTime, and in my report I put parameter with two date, I can only choose in the date exit in the attribut StartTime, but as I have a lot, an with a small range, I can't display all days...

I would like that the user choose a date as 27/03/07 and the report display all event in this day...
but I don't know what to do, because StartTime is as 27/03/07 10:50:42...

Can anybody help me ? Thank you !!!

In a named calculation(data source view in SSAS2005) or in a view/table(AS2000) you can try this: http://blog.mike-obrien.net/PermaLink,guid,f3363145-8753-4604-8314-855012a00400.aspx

This works with TSQL

HTH

Thomas Ivarsson

|||Hi Thomas,
Thank you for your answer...
But I have a problem not with named calculation but with the dimension, I didn't understand how to do !
As I explain last time, I have a table with a dateTime value, so I have a list of a lot of event with all this dateTime value, I want to organize them by day, So i want a time dimension that give just the day (as your answer) and which it is link with my dateTime, for example I have 5 event the 25/03/2007, 14 event the 26/03/2007...
so I don't what to do...
May I have to create a time dimension from server and my TimeDate are linked to this table ?

|||

Hello! Maybe I am wrong but I understand your problem as that you have time fragments on your date columns in the fact table and that you would like to change that?

The link I have sent you have examples that transform dates with 2007-03-29:13:30:06:01 to 2007-03-29:00:00:00 . If you build a time dimension with a datetime-column and you enter 2007-03-29 you will get 2007-03-29:00:00:00 in that column and a match between the time dimension and the fact table on dates.

Regards

Thomas Ivarsson

Counting siblings

I have an "instrument" dimension [aka RIC], and each instrument has an "underlying" attribute (aka RIC 1). I'd like to list all instruments, and for each instrument, show the count of other instruments with the same underlying.

this is what I've got so far - sad to say it's not working - what should I be doing here?

with member [measures].[x] as count(filter([wm instrument].[RIC].[RIC],[wm instrument].[RIC 1].CurrentMember)

select

[measures].[x] on 0,

[wm Instrument].[RIC].[RIC].Members on 1

from [itdev1 hk]

Here's an example from Adventure Works which shows how to count the number of Products in the same Category as the current Product:

with member measures.prodsinsamecat as

count(

exists([Product].[Product].[Product].members,

exists([Product].[Category].[Category].members, [Product].[Product].currentmember)

)

)

select measures.prodsinsamecat on 0,

[Product].[Category].[Category].members

*

[Product].[Product].[Product].members on 1

from [Adventure Works]

What it does is first of all finds the member(s) on the Category attribute that exist with the currentmember on the Product attribute, then finds and counts the members on the Product attribute that exist with these Categories.

HTH,

Chris

|||

And improving a little over Chris's suggestion, given the fact that there is attribute relationship defined between Product and Category, a simpler and more efficient way to do it would be

with member measures.prodsinsamecat as

count(exists([Product].[Product].[Product].members, [Product].[Category].currentmember))

select measures.prodsinsamecat on 0,

[Product].[Category].[Category].members

*

[Product].[Product].[Product].members on 1

from [Adventure Works]

|||

thanks, that helps. any idea how i could order the output by measures.productsinsame cat? I tried this, no joy:

with member measures.prodsinsamecat as

count(exists([WM INSTRUMENT].[RIC].[RIC].members, [WM INSTRUMENT].[UL RIC].currentmember))

select

measures.prodsinsamecat on columns,

order([WM INSTRUMENT].[UL RIC].[UL RIC].members*[WM INSTRUMENT].[RIC].[RIC].members, measures.prodsinsamecat) on rows

from [itdev1 hk]

|||

also, I actually want to show JUST the "products", rather than the crossjoin of products and categories.

however, if I take the category out the crossjoin operation, I get a list of all my "products", with the same number of prodsinsamecat

with member measures.prodsinsamecat as

count(exists([WM INSTRUMENT].[RIC].[RIC].members, [WM INSTRUMENT].[UL RIC].currentmember))

select

measures.prodsinsamecat on columns,

order([WM INSTRUMENT].[RIC].[RIC].members, measures.prodsinsamecat) on rows

from [itdev1 hk]

interestingly, if I take out the product out of the crossjoin operation, I get a list of all my "categories", with the correct number of prodsinsamecat along side each one

with member measures.prodsinsamecat as

count(exists([WM INSTRUMENT].[RIC].[RIC].members, [WM INSTRUMENT].[UL RIC].currentmember))

select

measures.prodsinsamecat on columns,

order([WM INSTRUMENT].[UL RIC].[UL RIC].members, measures.prodsinsamecat) on rows

from [itdev1 hk]

any insights gratefully received.