Calendar dimension with multiple Fiscal calendars

I am building a data warehouse using the dimensional model -- i.e. fact and
dimensional tables in a star schema.
For my Calendar dimension, I want to include Fiscal periods, but the data
warehouse has data for different clients that have different fiscal calendars.
How do I handle that ?
If I understand well, you have:
Customer A - 15/10/2006 - CY 2006 FY 2007
Customer A - 30/09/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
You want to see FY 2006 data where FY is the one specific for each
customer.
If this is the case, I think the right model is to have one Fiscal
Calendar dimension (with FY Year, FY MonthNumber, FY QuarterNumber)
that is linked to the fact table.
Of course this model doesn't support you if you want to change the FY
of a Customer without changing the data already loaded into the fact
table.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Craig HB wrote:
> I am building a data warehouse using the dimensional model -- i.e. fact and
> dimensional tables in a star schema.
> For my Calendar dimension, I want to include Fiscal periods, but the data
> warehouse has data for different clients that have different fiscal calendars.
> How do I handle that ?

Calendar dimension with multiple Fiscal calendars

I am building a data warehouse using the dimensional model -- i.e. fact and
dimensional tables in a star schema.
For my Calendar dimension, I want to include Fiscal periods, but the data
warehouse has data for different clients that have different fiscal calendar
s.
How do I handle that ?If I understand well, you have:
Customer A - 15/10/2006 - CY 2006 FY 2007
Customer A - 30/09/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
You want to see FY 2006 data where FY is the one specific for each
customer.
If this is the case, I think the right model is to have one Fiscal
Calendar dimension (with FY Year, FY MonthNumber, FY QuarterNumber)
that is linked to the fact table.
Of course this model doesn't support you if you want to change the FY
of a Customer without changing the data already loaded into the fact
table.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Craig HB wrote:
> I am building a data warehouse using the dimensional model -- i.e. fact an
d
> dimensional tables in a star schema.
> For my Calendar dimension, I want to include Fiscal periods, but the data
> warehouse has data for different clients that have different fiscal calend
ars.
> How do I handle that ?

calculation query

Hi all,
I need your help of building the following query.
I have the table "CountersTbl".
The table's fields are.
Date\Time Counter1 Counter2
The tables holds counters in different dates ant time.e.g:
19/11/04 06:00 am 10 20
19/11/04 15:00 pm 15 25
19/11/04 11:00 pm 35 90
...

In the above table we have the counters in three shifts in one day. Each day I hae the same shifts readings.

My task is:
I want to build a query that calculate the difference between the shifts counters in a givven day.
So, the output of the query of the 19/11/04 day is:

From 06:00 am - 15:00 pm 5 5
From 15:00 pm - 11:00 pm 20 65

I you please help me to build the code of this query.
Best regards...select date(three.datetimecol) as ShiftDate
, 'From 06:00 - 15:00' as Shift
, three.Counter1
-six.Counter1 as Counter1Diff
, three.Counter2
-six.Counter2 as Counter2Diff
from CountersTbl as three
inner
join CountersTbl as six
on date(three.datetimecol)
= date(six.datetimecol)
where time(three.datetimecol) = '15:00'
and time(six.datetimecol) = '06:00'
union all
select date(eleven.datetimecol) as ShiftDate
, 'From 15:00 - 23:00' as Shift
, eleven.Counter1
-three.Counter1 as Counter1Diff
, eleven.Counter2
-three.Counter2 as Counter2Diff
from CountersTbl as eleven
inner
join CountersTbl as three
on date(eleven.datetimecol)
= date(three.datetimecol)
where time(eleven.datetimecol) = '23:00'
and date(three.datetimecol) = '15:00'
order
by ShiftDate
, Shifthere datetimecol is the name of your "Date\Time" column

also, please note, date and time represent whatever functions are available in your particular database system for extracting the date only and time only portions of the datetime values

i was going to write them using the standard sql EXTRACT function but the "standard sql" book which i own is pretty crappy and does not give enough decent examples for me to know how to extract dates and times

and anyway, most common database systems don't support EXTRACT, they have their own proprietary date functions

Calculating length of time a Ticket was suspended

I'm trying to find a solution to for a report i am building and was hoping that some of you will share your expertise with me. Basically, I need to work out how long an Incident Ticket was suspended (ie it's status is "on hold").

The data is stored something similar to the following...

Ticket Date Status
-
333 14/03/2005 10:24:19 "to on hold"
333 14/03/2005 15:23:01 "from on hold"
334 14/03/2005 11:14:11 "to on hold"
334 14/03/2005 16:26:15 "from on hold"
335 15/03/2005 10:10:15 "to on hold"
335 15/03/2005 11:15:35 "from on hold"
335 15/03/2005 13:26:10 "to on hold"
335 15/03/2005 14:30:59 "from on hold"
335 16/03/2005 14:00:05 "to on hold"
335 16/03/2005 16:45:15 "from on hold"
336 16/03/2005 10:10:15 "to on hold"
336 16/03/2005 12:45:12 "from on hold"

This is the result i'd like to see....

Ticket Start Hold End Hold Time Suspended
- -- --
333 14/03/2005 10:24:19 14/03/2005 15:23:01 datediff(...
334 14/03/2005 11:14:11 14/03/2005 16:26:15 datediff(...
335 15/03/2005 10:10:15 15/03/2005 11:15:35 datediff(...
335 15/03/2005 13:26:10 15/03/2005 14:30:59 datediff(...
335 16/03/2005 14:00:05 16/03/2005 16:45:15 datediff(...
336 16/03/2005 10:10:15 16/03/2005 12:45:12 datediff(...

Has anybody done something along the same lines? If so, could you point me in the right direction?

Thanks in anticipation

Jon

Hello Jon,

This will give you the TicketID and the Start and End Date for Hold status. From your report, you can get the time difference for 'Time Suspended' (or you could do the datediff directly in this query).

select tl.TicketID,
(select min(date) from TicketLog tl1 where TicketID = tl.TicketID and tl1.Status = 'To On Hold') as StartHold,
(select max(date) from TicketLog tl2 where TicketID = tl.TicketID and tl2.Status = 'From On Hold') as EndHold,
from TicketLog tl

Here is another way, the min and max date are returned for any log that has 'On Hold' in the status. So, if it is required that each ticket can be on hold only once and you're reporting tickets that are no longer on hold, this would work.

select tl.TicketID, min(tl.date), max(tl.date)
from TicketLog tl
where tl.Status like '% on Hold'
group by tl.TicketID
having count(*) > 1

Hope this helps.

Jarret

|||

Hi Jarret

Thanks for your input.

I was able to get the same results as the ones your code generates... but unfortunately it's not quite what I need.

The above code generates only one row for each ticket with the first date a ticket went into suspend status and the last date a ticket left suspend status. A Ticket can enter and leave suspend status many times (or never) during it's life. I need to see a row for each time a ticket entered and left suspend status. I can then do a datediff and sum up all the times in suspend status. Then I can take this time off the total length of time the ticket has been open.

Cheers

Jon

|||

I have now got a view with data something like the following...

Ticket Clock HoldStart HoldStop

331 Start 2007-02-01 10:00:00 NULL
331 Stop NULL 2007-02-01 11:00:00
332 Start 2007-02-15 12:01:00 NULL
332 Stop NULL 2007-02-15 13:45:11
333 Start 2007-02-17 10:00:06 NULL
333 Stop NULL 2007-02-17 11:07:00
333 Start 2007-02-18 11:11:12 NULL
333 Stop NULL 2007-02-18 16:01:00
333 Start 2007-02-18 18:07:00 NULL
333 Stop NULL 2007-02-19 17:00:00
334 Start 2007-02-20 10:00:00 NULL
334 Stop NULL 2007-02-20 11:00:00
334 Start 2007-02-20 14:00:00 NULL
334 Stop NULL 2007-02-21 17:00:00

A little bit closer, but still not what I need... from this, I'd to see...

Ticket Clock HoldStart HoldStop

331 Start 2007-02-01 10:00:00 2007-02-01 11:00:00
332 Start 2007-02-15 12:01:00 2007-02-15 13:45:11
333 Start 2007-02-17 10:00:06 2007-02-17 11:07:00
333 Start 2007-02-18 11:11:12 2007-02-18 16:01:00
333 Start 2007-02-18 18:07:00 2007-02-19 17:00:00
334 Start 2007-02-20 10:00:00 2007-02-20 11:00:00
334 Start 2007-02-20 14:00:00 2007-02-21 17:00:00

any ideas?

|||

I think you are going to need to loop through your dataset with a cursor (or similar) to get the values you're looking for.

Jarret