Measure change updates in Fact tables

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
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@.microsoft.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
>
|||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@.microsoft.com...
>
>
|||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@.microsoft.com...[vbcol=seagreen]
> 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:

Measure change updates in Fact tables

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,
ConradThis 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@.microsoft.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
>|||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@.microsoft.com...
>
>|||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@.microsoft.com...[vbcol=seagreen]
> 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:
>

MDX to dynamically insert calculated members into row?

I need some help writing the MDX for selecting Row members in a particular
query. Here's the background:

The query shows sales results by region and office. The row members should
be like this:

OfficeA
OfficeB
OfficeD
Region1
Region1 (SOC)
OfficeE
OfficeF
OfficeG
OfficeJ
Region2
Region2 (SOC)
World
World (SOC)

There are two things to note about this:

1. The set of offices is being filtered to only show the "active" ones (i.e.
offices which are operational - offices C, H & I have closed down)

2. The Region1, Region2 and World members are straightforward sums of their
(active) children. However, the Region1 (SOC), Region2 (SOC) and World (SOC)
members are calculated - the idea is that they represent a "Same Office
Comparison", in other words only sum the offices that were active both last
year and this year.

The dimensions used to generate the row members are:

1. Region with levels Region and Office
2. Active Office with levels Active (True/False) and SOC (True/False)

This is the MDX I used to generate the members EXCEPT for the "SOC" members:

Member [Region].[World] as
' [Region].[All Region] ' , solve_order = 1

Non Empty
{
Filter(
Hierarchize( Except( [Region].Members , [Region].[All Region] } )
, POST ) ,
[Active Office].[All Active Office].[True] > 0) ,
[Region].[World]
}
ON ROWS

Where I am stuck is generating the "SOC" members and including them in the
set of row members in the correct order. I need to do this without hard
coding any references to specific regions or offices (regions and offices
change over time).

Any ideas?

Many thanks

HI Laurence,

As you have no doubt found, it is hard to insert a calculated member in a specific location in a hierarchy. What I would suggest is to create "real" members in your dimension tables for the "(SOC)" members, but don't have any data associated with them. Then use an assignment in your MDX script to calcuate the value for these members

You could hard code a calculation something like the following:

([Region 1 (SOC)]) = Aggregate([Region].[Region].&[Region 1].Chidren,[Region].[SOC].[True])

But if it were me I think I would probably want to make the calc more generic. You could possibly add an attribute or add a "Total" member to the SOC attribute so that you could pull out just the SOC total members and then grab the children of the previous member and filter them by [SOC].[True] - or something like that

|||

I do need to make the solution as generic as possible as the regions do change .... can you explain a bit more what you mean by '.... add an attribute or add a "Total" member to the SOC attribute ....'

|||

Sure. When I said "add an attribute" I was thinking that you could either change your existing [SOC] column to contain another value, so it would have "True", "False" & "Total" or you could add another column to your dimension table. Then what I would do is to add another row into your dimension table at the same level as the "Region 1" member, but called "Region 1 SOC" and tagged with a specific attribute value so that you could filter down to just these members .You would not have any facts associated with these members, they would simply be placeholders at a specific positions in the dimension. You would then use an assignment in the MDX Script to set the values for these members.

You might then be able use an assignment similar to the following (which is more psuedo code, but hopefully it will help illustrate my thought process)

Code Snippet

SCOPE [Region].[SOC].[SOC].[SOC Total] -- grab just the members tagged as "SOC Total"

-- i'm not clear on your exact structures and logic, but assuming

-- that the "SOC total" members are ordered after the real region totals,

-- the code below grabs the real region's children and adds them up,

-- filtering on only those members where [SOC] is true.

this = aggregate({[Region].[Region].[Region].Prevmember.children

, [Region].[SOC].[SOC].&{True]})

END SCOPE;

|||

Ah-ha, I think I got it! I will try it out, thanks very much.

MDX query for displaying all the dimension in a row

I have cube which have 6 dimensions.

I want to display all the dimsion members in a row . Can anyone help me with the MDX query for the same

e.g. suppose the dimension are dim1, dim2, .............

Now I want to retrieve data in the form of table as

Dim1.........Dim2...................Dim3............Dim4...............

Val1..........Val2....................Val3...............Val4................

Is there some way to inner join the data in MDX as in Sql. I mean just like we have inner join in SQL is there any MDX equivalent

e.g suppose we have three tables A,B,C and these three are related through primary foreign key relation ( A conatins the refernce for B and B in tuyrn contains the reference for C).

Now these three table are used to form a Cube and the column in these are tables form a dimension. Nwo can I join these three tables and show the data