MS SQL Data Warehousing - Measure change updates in Fact tables

Author

Measure change updates in Fact tables

Conrad

2005-12-30, 3:23 am

Is there an industry standard method to populate Fact table columns with the
amount of change of a measure from one row to the next?

I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
column of the price change from the previous week.

I'm wrestling with using tsql to join the Fact to itself offset by one week.
Progress is slowly being made, but I'm saying to myself "There's gotta be an
easier way!"

I at least need some keywords to do further research.

thanks,

Conrad

2006-01-03, 3:23 am

This type of query is usually done in the OLAP structures using MDX. Doing
it in t-sql would result in a poor performance.
If you do need to do it in t-sql please post sample of data and structures.
For example, how is date stored in your tables? As a datetime or you have a
week number or something?

MC

"Conrad" <Conrad@discussions.microsoft.com> wrote in message
news:45C056A5-A015-4517-9CD8- F9D46D868968@microso
ft.com...
> Is there an industry standard method to populate Fact table columns with
> the
> amount of change of a measure from one row to the next?
>
> I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
> column of the price change from the previous week.
>
> I'm wrestling with using tsql to join the Fact to itself offset by one
> week.
> Progress is slowly being made, but I'm saying to myself "There's gotta be
> an
> easier way!"
>
> I at least need some keywords to do further research.
>
> thanks,
>
> Conrad
>

Conrad

2006-01-03, 8:23 pm

1. Could you give me some OLAP/MDX keywords I could research further?

2. I have both WeekID and a date, but the "offset join" is done by WeekID.
I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
data is small, so
performance is not a problem.

Here's a representation of the data and tsql:

Table A
ID WeekID CustID Price PriceChange
1 1 90 100 0
2 2 90 110 10
3 3 90 110 0
4 4 90 110 0
5 1 91 100 0
6 2 91 100 0
7 3 91 120 20
8 4 91 120 0

Insert Table A Into ##TempB [sic]

Select ID, WeekID, CustID
From A
Inner Join ##TempB
On A.WeekID = ##TempB + 1
And A.CustID = ##TempB.CustID

tia,

Conrad

"MC" wrote:

> This type of query is usually done in the OLAP structures using MDX. Doing
> it in t-sql would result in a poor performance.
> If you do need to do it in t-sql please post sample of data and structures.
> For example, how is date stored in your tables? As a datetime or you have a
> week number or something?
>
>
> MC
>
>
> "Conrad" <Conrad@discussions.microsoft.com> wrote in message
> news:45C056A5-A015-4517-9CD8- F9D46D868968@microso
ft.com...
>
>
>

2006-01-04, 3:23 am

You obviously need price change over the weeks for each custID (customer?).
Does this wokr for you?

select
A.price,
isnull((select A2.price from tableA A2 where A2.weekID = A.WeekID - 1
and A2.CustID = A.CustID), A.price) as PreviousPrice,

A.WeekID,
A.CustID

from
tableA A

Alternatively, you can use join as you did but using the same table.

select
A.price,
isnull(A2.Price,A.Price) as PreviousPrice,
A.WeekID,
A.CustID

from
tableA A
left join TableA A2 on A.WeekID = A2.WeekID - 1 ANDA.CustID = A2.CustID

MC

"Conrad" <Conrad@discussions.microsoft.com> wrote in message
news:0E83399D-4B5C-40CE-988F- D58F9898F9E5@microso
ft.com...[color=darkred]
> 1. Could you give me some OLAP/MDX keywords I could research further?
>
> 2. I have both WeekID and a date, but the "offset join" is done by WeekID.
> I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
> data is small, so
> performance is not a problem.
>
> Here's a representation of the data and tsql:
>
> Table A
> ID WeekID CustID Price PriceChange
> 1 1 90 100 0
> 2 2 90 110 10
> 3 3 90 110 0
> 4 4 90 110 0
> 5 1 91 100 0
> 6 2 91 100 0
> 7 3 91 120 20
> 8 4 91 120 0
>
> Insert Table A Into ##TempB [sic]
>
> Select ID, WeekID, CustID
> From A
> Inner Join ##TempB
> On A.WeekID = ##TempB + 1
> And A.CustID = ##TempB.CustID
>
>
> tia,
>
> Conrad
>
>
>
> "MC" wrote:
>

Sponsored Links