Member.FetchAllProperties Error

I get

"System.NotSupportedException: The method specified is not supported by the

current provider."

AdomdClient v8, VS 2003, SQL Server 2000

What's the cause of this and is there any way around it?

hello,

it looks like the error might be caused by an older version of msolap80 provider. I'd suggest to check on it, and if indeed - update.

hope this helps,

|||Hi Mary, I have msolap80 version 8.00.760. I can't find anything more recent than that.|||

hello Kevin,

i think 760 is not the latest one... did you install sp4?

i think you can try the PTS from here: http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en
(search for "Microsoft SQL Server 2000 PivotTable Services" section)

or here is the link to sql server sp4: http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en

hope this helps,

|||Hi Mary,

Thanks. I applied SP4 and that did the trick. However, what I now find is that after calling FetchAllProperties I still get no member properties. Is there something that needs to be called or configured even earlier? Here's a code snippet.

For Each position As AdomdClient.Position In positions

Dim member As AdomdClient.Member = position.Members(0)

member.FetchAllProperties()

Dim properties As MemberPropertyCollection = member.MemberProperties

Debug.WriteLine(String.Format("No. of member properties = {0}", properties.Count))

For Each p As AdomdClient.MemberProperty In properties
Debug.WriteLine(String.Format("Property = {0}, {1}", p.Name, p.Value))
Next

Next|||

hello Kevin,

member.FetchAllProperties() call populates the member.Properties collection. and the member.MemberProperties collection should contain the properties explicitly requested in the mdx query (in “DIMENSION PROPERTIES” clause).

depending on whether you need those properties for all/many members or not, you can either request them in the query (and access them with .MemberProperties collection), or request properties for a specific(few) members afterwards by calling .FetchAllProperties() and accessing properties from the .Properties collection.

hope this helps,

|||Hi Mary. Thanks for this. It worked. We used the second technique because we need to be generic, i.e., we don't know in advance what user-defined properties will be available. But here's another question. Calling Member.Properties gives e.g.,

Property Name: MEMBER_KEY, Property Value: 13
Property Name: IS_PLACEHOLDERMEMBER, Property Value: False
Property Name: IS_DATAMEMBER, Property Value: False
...
Property Name: Allow Input, Property Value: 0

The last one here is one of our user-defined properties. The previous ones are the intrinsic properties. Is there a way of telling the difference? At the moment I've noticed that the intrinsic names are all upper case, while our UDPs are mixed case. As a first stab I'm just testing for all upper case and excluding. But of course this is not the most rigorous of methods!

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:
>