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 query to filter the cube

I have to populate a pivot table. I am using source of the pivot table as an analysis cube. My code is similar to this :

pivotCache.CommandText = "<name of the cube>";
pivotCache.CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdCube;

Now

my requirement is to show a filtered cube, not the whole cube. I am

using SQL server 2000 Analysis Services to prepare and store the cube.

I

think an MDX query as commandText can do this. But I am not being able

to write the suitable MDX that can give a filtered cube which I can

use to populate pivot table?

I have used MDX query as "select

from <cube name> where <filter condition(s)>" . But it is

showing that no column found that excel can use.

What would be the currect MDX?

I dont think writing custom MDX is the way to solve your problem with OWC. You should look at the ways to use OWC built in functionality to restrict your data.

Try creating PivotTable report in Excel and then save it as a web page with interactivity. You will see Excel creating a web page with OWC built in it.

That should give you an idea how to control OWC.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

If you want to restrict access to the cube in pivottable with OWC 11, you have to do this with permissions of the SSAS. I am using SSAS 2005 and you can create a Role and set exactly which dimensions and which members within a level the customer is able to access. I thought at the beginning that I have to write mdx and set it as CommantText or Mdx property of the pivottable, but this is not the case. You have to do it with access security of the Analysis Services.

There is something more that I found about filtering data in pivottable, but this is not what you need. You can set which fields could be included or excluded programatically, but the user still can change these values (at least I think so). The code is like this:

Code Snippet

varArray = Array("Bikes")

pivotTable.ActiveView.FieldSets("Category").Fields("Category").IncludedMembers = varArray

MDX query to filter the cube

I have to populate a pivot table. I am using source of the pivot table as an analysis cube. My code is similar to this :

pivotCache.CommandText = "<name of the cube>";
pivotCache.CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdCube;

Now my requirement is to show a filtered cube, not the whole cube. I am using SQL server 2000 Analysis Services to prepare and store the cube.

I think an MDX query as commandText can do this. But I am not being able to write the suitable MDX that can give a filtered cube which I can use to populate pivot table?

I have used MDX query as "select from <cube name> where <filter condition(s)>" . But it is showing that no column found that excel can use.

What would be the currect MDX?

I dont think writing custom MDX is the way to solve your problem with OWC. You should look at the ways to use OWC built in functionality to restrict your data.

Try creating PivotTable report in Excel and then save it as a web page with interactivity. You will see Excel creating a web page with OWC built in it.

That should give you an idea how to control OWC.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

If you want to restrict access to the cube in pivottable with OWC 11, you have to do this with permissions of the SSAS. I am using SSAS 2005 and you can create a Role and set exactly which dimensions and which members within a level the customer is able to access. I thought at the beginning that I have to write mdx and set it as CommantText or Mdx property of the pivottable, but this is not the case. You have to do it with access security of the Analysis Services.

There is something more that I found about filtering data in pivottable, but this is not what you need. You can set which fields could be included or excluded programatically, but the user still can change these values (at least I think so). The code is like this:

Code Snippet

varArray = Array("Bikes")

pivotTable.ActiveView.FieldSets("Category").Fields("Category").IncludedMembers = varArray