KPI Goals from dimension attributes.

Hi,

Currently I'm busy creating a Cube and I'd like to use dimension attributes as goals in my KPI's. I'm creating a cube that has facts that are related to agreements. In the agreements dimension I've stored the goals that are defind within the actual agreements with the customers. So when creating a KPI I'd like to use those goals from the dimension.

I've tried something like this: [Agreement].[Norm Percentage].CURRENTMEMBER as my Goal Expression, but it's not working as expected.

Really hope that one of you can explain me how to do it.

Thanks in advance,

The .CurrentMember function will return a member, what you should be looking at is using the .MemberValue function

eg.

[Agreement].[Norm Percentage].MemberValue

The thing you will need to think about is what you what to see when multiple or All "Norm Percentages" are in context. I don't know enough about your data to know if an average would make sense, but If it did, something like the following may be a more robust expression.

AVG(EXISTING [Agreement].[Norm Percentage].[Norm Percentage], [Aggrement].[Norm Percentage].MemberValue)

|||

Darren thanks for your reply.

I've tested you're solution and it is looking better then what I got the first time. But unfortunately the averages are not what I expected.

I'll look into it today. So I'll post a new reply at the end the day with my results.

KPI Goals from dimension attributes.

Hi,

Currently I'm busy creating a Cube and I'd like to use dimension attributes as goals in my KPI's. I'm creating a cube that has facts that are related to agreements. In the agreements dimension I've stored the goals that are defind within the actual agreements with the customers. So when creating a KPI I'd like to use those goals from the dimension.

I've tried something like this: [Agreement].[Norm Percentage].CURRENTMEMBER as my Goal Expression, but it's not working as expected.

Really hope that one of you can explain me how to do it.

Thanks in advance,

The .CurrentMember function will return a member, what you should be looking at is using the .MemberValue function

eg.

[Agreement].[Norm Percentage].MemberValue

The thing you will need to think about is what you what to see when multiple or All "Norm Percentages" are in context. I don't know enough about your data to know if an average would make sense, but If it did, something like the following may be a more robust expression.

AVG(EXISTING [Agreement].[Norm Percentage].[Norm Percentage], [Aggrement].[Norm Percentage].MemberValue)

|||

Darren thanks for your reply.

I've tested you're solution and it is looking better then what I got the first time. But unfortunately the averages are not what I expected.

I'll look into it today. So I'll post a new reply at the end the day with my results.

key violation, general sql error, connectin busy with another hstmt

We are getting this error periodically in a large app we are converting
from Access to SQL Server 2000. It uses BDE and ODBC for data access
and TTable/TQuery as well as TwwTable/TwwQuery components (from
woll2woll) under Delphi 6. It appears to happen when we are executing
TQuery.Open.
From what I've read, this is caused by ODBC not completing a result set
before processing another request. Which might be caused by having
queries run in multiple threads, but this is not the case."Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:%23OgCxf7FGHA.1396@.TK2MSFTNGP11.phx.gbl...
> We are getting this error periodically in a large app we are
> converting
> from Access to SQL Server 2000. It uses BDE and ODBC for data
> access
> and TTable/TQuery as well as TwwTable/TwwQuery components (from
> woll2woll) under Delphi 6. It appears to happen when we are
> executing
> TQuery.Open.
> From what I've read, this is caused by ODBC not completing a
> result set
> before processing another request. Which might be caused by
> having
> queries run in multiple threads, but this is not the case.
Basically, you can't have two open (SELECT) statements on the
same connection. For example,
Open query 1
Get some info from a record
Use that to set params in query 2
Open query 2 -- This will fail
This assumes both statements are attached to the same hDBC. It
is a result of using a client-side cursor for the hStmt's.
You'll have to use server-side cursors for the statements to make
this work. See MSDN.
This is one way to force a server-side cursor:
SQLSetStmtAttr( m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE,
(SQLPOINTER) SQL_SCROLLABLE, 0 );
Good luck,
- Arnie|||So you can never have 2 open queries? I do that all the time, in one
thread. Maybe I'm not understanding. For example, this code works
fine, which creates 101 open queries:
--
procedure TForm1.ManyQueriesClick(Sender: TObject);
var qs : array[0..100] of TQuery ;
var q : Tquery;
var i : Integer;
begin
for i := 0 to 100 do begin
qs[i] := TQuery.Create(self);
q := qs[i];
q.databasename := 'WM';
q.SQL.Clear;
q.SQL.Add('SELECT * FROM CUSTOMERFIELDS');
statusbar1.SimpleText := 'Query ' + IntToStr(i);
q.active := true;
end;
for i := 0 to 100 do
qs[i].Free;
end;|||Arnie wrote:

> "Doug Stephens" <dougstephens@.rogers.com> wrote in message
> news:%23OgCxf7FGHA.1396@.TK2MSFTNGP11.phx.gbl...
> Basically, you can't have two open (SELECT) statements on the same
> connection. For example,
> Open query 1
> Get some info from a record
> Use that to set params in query 2
> Open query 2 -- This will fail
> This assumes both statements are attached to the same hDBC. It is a
> result of using a client-side cursor for the hStmt's. You'll have to
> use server-side cursors for the statements to make this work. See
> MSDN.
> This is one way to force a server-side cursor:
> SQLSetStmtAttr( m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER)
> SQL_SCROLLABLE, 0 );
>
> Good luck,
> - Arnie
How can I run SQLSetStmtAttr from Delphi?|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:uVMztRHGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> How can I run SQLSetStmtAttr from Delphi?
You can't. It's an ODBC statement. The equivalent in Delphi
would be CursorLocation := clUseServer for TADO components.
Sorry, but I've totally forgotten about the BDE. It may use
server-side cursors by default.
The problem occurs in ODBC when nesting queries that use the same
connection handle. Open q1, read some field values, use these to
set params in q2 and then open q2. This last open will cause the
error. In theory, SQL Server 2005 has 'fixed' this 'feature',
though I haven't tried it yet.
- Arnie|||I found a way to call this function in ODBC32.DLL from Delphi but what
is the hstmt?|||We have same problem with 2005.|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:e3UtnUqGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> We have same problem with 2005.
What DB objects are you using with 2005?
- Arnie|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:OfWhHUqGGHA.2000@.TK2MSFTNGP15.phx.gbl...
>I found a way to call this function in ODBC32.DLL from Delphi
>but what
> is the hstmt?
As far as I know, you'd have to be using ODBC directly rather
than the BDE. hStmt is the ODBC statement handle.
- Arnie|||Uh, tables and indexes. No stored procs. Is that what you mean?

key violation, general sql error, connectin busy with another hstmt

We are getting this error periodically in a large app we are converting
from Access to SQL Server 2000. It uses BDE and ODBC for data access
and TTable/TQuery as well as TwwTable/TwwQuery components (from
woll2woll) under Delphi 6. It appears to happen when we are executing
TQuery.Open.
From what I've read, this is caused by ODBC not completing a result set
before processing another request. Which might be caused by having
queries run in multiple threads, but this is not the case.
"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:%23OgCxf7FGHA.1396@.TK2MSFTNGP11.phx.gbl...
> We are getting this error periodically in a large app we are
> converting
> from Access to SQL Server 2000. It uses BDE and ODBC for data
> access
> and TTable/TQuery as well as TwwTable/TwwQuery components (from
> woll2woll) under Delphi 6. It appears to happen when we are
> executing
> TQuery.Open.
> From what I've read, this is caused by ODBC not completing a
> result set
> before processing another request. Which might be caused by
> having
> queries run in multiple threads, but this is not the case.
Basically, you can't have two open (SELECT) statements on the
same connection. For example,
Open query 1
Get some info from a record
Use that to set params in query 2
Open query 2 -- This will fail
This assumes both statements are attached to the same hDBC. It
is a result of using a client-side cursor for the hStmt's.
You'll have to use server-side cursors for the statements to make
this work. See MSDN.
This is one way to force a server-side cursor:
SQLSetStmtAttr( m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE,
(SQLPOINTER) SQL_SCROLLABLE, 0 );
Good luck,
- Arnie
|||So you can never have 2 open queries? I do that all the time, in one
thread. Maybe I'm not understanding. For example, this code works
fine, which creates 101 open queries:
procedure TForm1.ManyQueriesClick(Sender: TObject);
var qs : array[0..100] of TQuery ;
var q : Tquery;
var i : Integer;
begin
for i := 0 to 100 do begin
qs[i] := TQuery.Create(self);
q := qs[i];
q.databasename := 'WM';
q.SQL.Clear;
q.SQL.Add('SELECT * FROM CUSTOMERFIELDS');
statusbar1.SimpleText := 'Query ' + IntToStr(i);
q.active := true;
end;
for i := 0 to 100 do
qs[i].Free;
end;
|||Arnie wrote:

> "Doug Stephens" <dougstephens@.rogers.com> wrote in message
> news:%23OgCxf7FGHA.1396@.TK2MSFTNGP11.phx.gbl...
> Basically, you can't have two open (SELECT) statements on the same
> connection. For example,
> Open query 1
> Get some info from a record
> Use that to set params in query 2
> Open query 2 -- This will fail
> This assumes both statements are attached to the same hDBC. It is a
> result of using a client-side cursor for the hStmt's. You'll have to
> use server-side cursors for the statements to make this work. See
> MSDN.
> This is one way to force a server-side cursor:
> SQLSetStmtAttr( m_hStmt, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER)
> SQL_SCROLLABLE, 0 );
>
> Good luck,
> - Arnie
How can I run SQLSetStmtAttr from Delphi?
|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:uVMztRHGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> How can I run SQLSetStmtAttr from Delphi?
You can't. It's an ODBC statement. The equivalent in Delphi
would be CursorLocation := clUseServer for TADO components.
Sorry, but I've totally forgotten about the BDE. It may use
server-side cursors by default.
The problem occurs in ODBC when nesting queries that use the same
connection handle. Open q1, read some field values, use these to
set params in q2 and then open q2. This last open will cause the
error. In theory, SQL Server 2005 has 'fixed' this 'feature',
though I haven't tried it yet.
- Arnie
|||I found a way to call this function in ODBC32.DLL from Delphi but what
is the hstmt?
|||We have same problem with 2005.
|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:e3UtnUqGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> We have same problem with 2005.
What DB objects are you using with 2005?
- Arnie
|||"Doug Stephens" <dougstephens@.rogers.com> wrote in message
news:OfWhHUqGGHA.2000@.TK2MSFTNGP15.phx.gbl...
>I found a way to call this function in ODBC32.DLL from Delphi
>but what
> is the hstmt?
As far as I know, you'd have to be using ODBC directly rather
than the BDE. hStmt is the ODBC statement handle.
- Arnie
|||Uh, tables and indexes. No stored procs. Is that what you mean?