|
|
|
date: Sun, 22 Jun 2008 18:21:44 -0700 (PDT),
group: microsoft.public.sqlserver.olap
back
Re: SSAS 2005 - fact table with 2 time stamps - count for any point in time
1 option is to do this:
create a view in the database like:
select starttime, rentalid, +1 as Delta
from <table>
union all
select endtime, rentalid, -1 as Delta
from <table>
now create a cube based on this view, with 1 measure:
Delta (a sum)
and create a calculated measure:
pointintimerental = sum(null:calendar.currentmember, measures.delta)
(works only at the day level)
there is other options to achieve the same result, like using the many-many
feature or just using calculations.
good luck
"vmic" wrote in message
news:3a38e86b-813d-45fa-988a-62f684fcaa0a@n19g2000prg.googlegroups.com...
> Hi there,
>
> I've got a fact table (car rental episode) that has start and end date/
> time stamp:
> RentalId
> StartTime
> EndTime
>
> Start and end time link to Time dimension table (via StartTimeId,
> EndTimeId) that has Year, Month, Day, Hour, Minute columns. I've got
> time dimension created from that table
>
> I want to be able to find out, how many cars were in rent at any
> particular point of time. What would be the best way to achieve this?
>
> I.e, for the following records:
>
> RentalId StartTime EndTime
> -------------------------------------------------------
> 1 2008-05-22 14:55 2008-05-26 11:12
> 2 2008-05-23 08:22 2008-05-29 09:13
> 3 2008-05-25 17:19 2008-05-27 12:22
>
> I need to be able to say that on the 24th of May 2008 12:00 2 cars
> were in rental.
>
>
> I'm using SSAS 2005
>
> Thanks in advance
date: Sun, 22 Jun 2008 22:57:43 -0400
author: Jeje
Re: SSAS 2005 - fact table with 2 time stamps - count for any point
in time
Thanks Jeje,
What sort of MDX functions do I need look into for using calculations?
Thanks again
On Jun 23, 12:57 pm, "Jeje" wrote:
> 1 option is to do this:
> create a view in the database like:
>
> select starttime, rentalid, +1 as Delta
> from <table>
> union all
> select endtime, rentalid, -1 as Delta
> from <table>
>
> now create a cube based on this view, with 1 measure:
> Delta (a sum)
> and create a calculated measure:
> pointintimerental = sum(null:calendar.currentmember, measures.delta)
> (works only at the day level)
>
> there is other options to achieve the same result, like using the many-many
> feature or just using calculations.
>
> good luck
>
> "vmic" wrote in message
>
> news:3a38e86b-813d-45fa-988a-62f684fcaa0a@n19g2000prg.googlegroups.com...
>
> > Hi there,
>
> > I've got a fact table (car rental episode) that has start and end date/
> > time stamp:
> > RentalId
> > StartTime
> > EndTime
>
> > Start and end time link to Time dimension table (via StartTimeId,
> > EndTimeId) that has Year, Month, Day, Hour, Minute columns. I've got
> > time dimension created from that table
>
> > I want to be able to find out, how many cars were in rent at any
> > particular point of time. What would be the best way to achieve this?
>
> > I.e, for the following records:
>
> > RentalId StartTime EndTime
> > -------------------------------------------------------
> > 1 2008-05-22 14:55 2008-05-26 11:12
> > 2 2008-05-23 08:22 2008-05-29 09:13
> > 3 2008-05-25 17:19 2008-05-27 12:22
>
> > I need to be able to say that on the 24th of May 2008 12:00 2 cars
> > were in rental.
>
> > I'm using SSAS 2005
>
> > Thanks in advance
date: Sun, 22 Jun 2008 21:57:33 -0700 (PDT)
author: vmic
RE: SSAS 2005 - fact table with 2 time stamps - count for any point in
Create a View FactRental as below joining Rental with a Time Dimension Table.
You can use AS2005 to create the Time Dimension Table for you. Use the
distinct rentalId count measure to get the result you need.
I know this option is bit of an overkill, but will work for you.
SELECT
rt.PK_Date,cr.RentalId
FROM
CarRental cr
INNER JOIN DimTime dt ON
dt.PK_Date BETWEEN cr.CarRentalStartDate AND cr.CarRentalEndDate
- Sha Anand
"vmic" wrote:
> Hi there,
>
> I've got a fact table (car rental episode) that has start and end date/
> time stamp:
> RentalId
> StartTime
> EndTime
>
> Start and end time link to Time dimension table (via StartTimeId,
> EndTimeId) that has Year, Month, Day, Hour, Minute columns. I've got
> time dimension created from that table
>
> I want to be able to find out, how many cars were in rent at any
> particular point of time. What would be the best way to achieve this?
>
> I.e, for the following records:
>
> RentalId StartTime EndTime
> -------------------------------------------------------
> 1 2008-05-22 14:55 2008-05-26 11:12
> 2 2008-05-23 08:22 2008-05-29 09:13
> 3 2008-05-25 17:19 2008-05-27 12:22
>
> I need to be able to say that on the 24th of May 2008 12:00 2 cars
> were in rental.
>
>
> I'm using SSAS 2005
>
> Thanks in advance
>
date: Sun, 22 Jun 2008 23:51:01 -0700
author: Sha Anand
Re: SSAS 2005 - fact table with 2 time stamps - count for any point
in
On Jun 23, 4:51 pm, Sha Anand
wrote:
> Create a View FactRental as below joining Rental with a Time Dimension Table.
> You can use AS2005 to create the Time Dimension Table for you. Use the
> distinct rentalId count measure to get the result you need.
>
> I know this option is bit of an overkill, but will work for you.
>
> SELECT
> rt.PK_Date,cr.RentalId
> FROM
> CarRental cr
> INNER JOIN DimTime dt ON
> dt.PK_Date BETWEEN cr.CarRentalStartDate AND cr.CarRentalEndDate
>
> - Sha Anand
>
> "vmic" wrote:
> > Hi there,
>
> > I've got a fact table (car rental episode) that has start and end date/
> > time stamp:
> > RentalId
> > StartTime
> > EndTime
>
> > Start and end time link to Time dimension table (via StartTimeId,
> > EndTimeId) that has Year, Month, Day, Hour, Minute columns. I've got
> > time dimension created from that table
>
> > I want to be able to find out, how many cars were in rent at any
> > particular point of time. What would be the best way to achieve this?
>
> > I.e, for the following records:
>
> > RentalId StartTime EndTime
> > -------------------------------------------------------
> > 1 2008-05-22 14:55 2008-05-26 11:12
> > 2 2008-05-23 08:22 2008-05-29 09:13
> > 3 2008-05-25 17:19 2008-05-27 12:22
>
> > I need to be able to say that on the 24th of May 2008 12:00 2 cars
> > were in rental.
>
> > I'm using SSAS 2005
>
> > Thanks in advance
Thanks Sha,
Yes, that's what I'm currently at - I've created a view similar to
described by you - bu t I was just wondering if there's a way to avoid
it - as I'm just a bit worried about performance - so I was curious if
any calculation possible to get these results from inside the cube?
date: Tue, 24 Jun 2008 00:47:09 -0700 (PDT)
author: vmic
Re: SSAS 2005 - fact table with 2 time stamps - count for any point in
try to use the many many feature:
create a view with 3 dates like
SELECT distinct
rt.PK_Date, cr.CarRentalStartDate , cr.CarRentalEndDate
FROM
CarRental cr
INNER JOIN DimTime dt ON
dt.PK_Date BETWEEN cr.CarRentalStartDate AND cr.CarRentalEndDate
use this view as a new measure group and associate the 3 time dimensions you
have:
Standard calendar
start rental date
end rental date
and finally associate the standard calendar to your current rental measure
group using the many many option and the newly created measure group.
now when you select a date in the calendar, this will select all the rentals
which starts before and end after the date.
(I presume that you currently have a measure group based on your rental
table and you already have 2 time dimension in the cube associated to the
start and end time of the rental table)
"vmic" wrote in message
news:3594b792-a79f-44d0-bacd-f7c13e5cca72@z24g2000prf.googlegroups.com...
> On Jun 23, 4:51 pm, Sha Anand
> wrote:
>> Create a View FactRental as below joining Rental with a Time Dimension
>> Table.
>> You can use AS2005 to create the Time Dimension Table for you. Use the
>> distinct rentalId count measure to get the result you need.
>>
>> I know this option is bit of an overkill, but will work for you.
>>
>> SELECT
>> rt.PK_Date,cr.RentalId
>> FROM
>> CarRental cr
>> INNER JOIN DimTime dt ON
>> dt.PK_Date BETWEEN cr.CarRentalStartDate AND cr.CarRentalEndDate
>>
>> - Sha Anand
>>
>> "vmic" wrote:
>> > Hi there,
>>
>> > I've got a fact table (car rental episode) that has start and end date/
>> > time stamp:
>> > RentalId
>> > StartTime
>> > EndTime
>>
>> > Start and end time link to Time dimension table (via StartTimeId,
>> > EndTimeId) that has Year, Month, Day, Hour, Minute columns. I've got
>> > time dimension created from that table
>>
>> > I want to be able to find out, how many cars were in rent at any
>> > particular point of time. What would be the best way to achieve this?
>>
>> > I.e, for the following records:
>>
>> > RentalId StartTime EndTime
>> > -------------------------------------------------------
>> > 1 2008-05-22 14:55 2008-05-26 11:12
>> > 2 2008-05-23 08:22 2008-05-29 09:13
>> > 3 2008-05-25 17:19 2008-05-27 12:22
>>
>> > I need to be able to say that on the 24th of May 2008 12:00 2 cars
>> > were in rental.
>>
>> > I'm using SSAS 2005
>>
>> > Thanks in advance
>
> Thanks Sha,
>
> Yes, that's what I'm currently at - I've created a view similar to
> described by you - bu t I was just wondering if there's a way to avoid
> it - as I'm just a bit worried about performance - so I was curious if
> any calculation possible to get these results from inside the cube?
date: Tue, 24 Jun 2008 08:39:10 -0400
author: Jeje
Re: SSAS 2005 - fact table with 2 time stamps - count for any point
in
On Jun 24, 10:39 pm, "Jeje" wrote:
> try to use the many many feature:
> create a view with 3 dates like
> SELECT distinct
> rt.PK_Date, cr.CarRentalStartDate , cr.CarRentalEndDate
>
> FROM
> CarRental cr
> INNER JOIN DimTime dt ON
> dt.PK_Date BETWEEN cr.CarRentalStartDate AND cr.CarRentalEndDate
>
> use this view as a new measure group and associate the 3timedimensions you
> have:
> Standard calendar
> start rental date
> end rental date
> and finally associate the standard calendar to your current rental measure
> group using the many many option and the newly created measure group.
> now when you select a date in the calendar, this will select all the rentals
> which starts before and end after the date.
> (I presume that you currently have a measure group based on your rental
> table and you already have 2timedimension in the cube associated to the
> start and endtimeof the rental table)
>
> "vmic" wrote in message
>
> news:3594b792-a79f-44d0-bacd-f7c13e5cca72@z24g2000prf.googlegroups.com...
>
> > On Jun 23, 4:51 pm, Sha Anand
> > wrote:
> >> Create a View FactRental as below joining Rental with aTimeDimension
> >> Table.
> >> You can use AS2005 to create theTimeDimension Table for you. Use the
> >> distinct rentalId count measure to get the result you need.
>
> >> I know this option is bit of an overkill, but will work for you.
>
> >> SELECT
> >> rt.PK_Date,cr.RentalId
> >> FROM
> >> CarRental cr
> >> INNER JOIN DimTime dt ON
> >> dt.PK_Date BETWEEN cr.CarRentalStartDate AND cr.CarRentalEndDate
>
> >> - Sha Anand
>
> >> "vmic" wrote:
> >> > Hi there,
>
> >> > I've got a fact table (car rental episode) that has start and end date/
> >> >timestamp:
> >> > RentalId
> >> > StartTime
> >> > EndTime
>
> >> > Start and endtimelink toTimedimension table (via StartTimeId,
> >> > EndTimeId) that has Year, Month, Day, Hour, Minute columns. I've got
> >> >timedimension created from that table
>
> >> > I want to be able to find out, how many cars were in rent at any
> >> > particular point oftime. What would be the best way to achieve this?
>
> >> > I.e, for the following records:
>
> >> > RentalId StartTime EndTime
> >> > -------------------------------------------------------
> >> > 1 2008-05-22 14:55 2008-05-26 11:12
> >> > 2 2008-05-23 08:22 2008-05-29 09:13
> >> > 3 2008-05-25 17:19 2008-05-27 12:22
>
> >> > I need to be able to say that on the 24th of May 2008 12:00 2 cars
> >> > were in rental.
>
> >> > I'm using SSAS 2005
>
> >> > Thanks in advance
>
> > Thanks Sha,
>
> > Yes, that's what I'm currently at - I've created a view similar to
> > described by you - bu t I was just wondering if there's a way to avoid
> > it - as I'm just a bit worried about performance - so I was curious if
> > any calculation possible to get these results from inside the cube?
Thanks Jeje,
Your suggestion worked really well.
date: Thu, 24 Jul 2008 19:31:08 -0700 (PDT)
author: vmic
|
|
