KPI List Web Part in MOSS 2007 - How Do I Display an 'As of Date' For Indicators?

I have created an SQL 2005 SSAS cube with many different KPIs defined. In MOSS 2007 (SP2) I have created a site to display these various KPIs in several KPI Lists. Some of the indicators in the underlying cube are updated daily, others weekly, others quarterly, etc.

What are some ways to display to the user what date or time slice each specific indicator is "as of"?

Thanks in advance for any help you can provide.

-Steve

Hello. I do not think that there is a property in SSAS2005 that you can use for this.

The easy way is to create separate KPI:s depending on the time slice /or update window and simply name the KPI:s in SSAS2005 in this way.

I can think of ActSalesBudSalesMonth and ActSalesBudSalesYearToDate. I usually use shorter names.

Else I recommend you to find a property/information field in MOSS 2007

HTH

Thomas Ivarsson

KPI List Web Part in MOSS 2007 - How Do I Display an 'As of Date' For Indicators?

I have created an SQL 2005 SSAS cube with many different KPIs defined. In MOSS 2007 (SP2) I have created a site to display these various KPIs in several KPI Lists. Some of the indicators in the underlying cube are updated daily, others weekly, others quarterly, etc.

What are some ways to display to the user what date or time slice each specific indicator is "as of"?

Thanks in advance for any help you can provide.

-Steve

Hello. I do not think that there is a property in SSAS2005 that you can use for this.

The easy way is to create separate KPI:s depending on the time slice /or update window and simply name the KPI:s in SSAS2005 in this way.

I can think of ActSalesBudSalesMonth and ActSalesBudSalesYearToDate. I usually use shorter names.

Else I recommend you to find a property/information field in MOSS 2007

HTH

Thomas Ivarsson

KPI Goal Value doesn't change after filtering the date

Hello everyone,

i'm new to Analysis Services and trying to build a Data Warehouse especially for using KPIs in it. Watching the famous AdventureWorksDW example i try to use the MDX Statements likewise. I want to use a KPI just like the first in the list "Growth in Customer Base". But when using a MDX Statement for the Goal Expression like this:

Case
When [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Fiscal Year]
Then .30
When [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Fiscal Semester]
Then .15
When [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Fiscal Quarter]
Then .075
When [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Month]
Then .025
Else "NA"
End

after filtering the resultset by the "Date" Dimension -> "Fiscal" Hierarchy -> equals "FY 2004" in the KPI-Browser it just shows "NA" like it does in the AdventureWorksDW, too. Is there a way to use a similar example in AdventureWorksDW that changes the goal dependend on the Filter like my description? All other KPI examples in AdventureWorksDW the goals depend on other values already contained in the DB and not dependend on the Filter Expression used.

Unfortunately, I think that the KPI browser may be misleading because, "filtering the resultset by the "Date" Dimension -> "Fiscal" Hierarchy -> equals "FY 2004" in the KPI-Browser" is probably generating a subselect, rather than applying the condition in the where clause. Try an MDX query directly, like:

>>

select {KPIGoal("Growth in Customer Base")} on 0

from [Adventure Works]

where [Date].[Fiscal].[Fiscal Year].&[2004]

-

Growth in Customer Base Goal
0.3

>>

|||Hi Deepak,

thank's a lot for your help! That works perfectly. But my goal is to visualize the KPIs with the Business Scorecard Manager and hoped I just have to give him the cube and he visualizes it. Does the Business Scorecard Manager filter with subselects or in a where clause?

I hope you or someone else can help me out with this second point and I will be happy ...

Claudio|||

Hi Claudio,

My guess is that BSM 2005 filters with where clause, since I think that it works with AS 2000 cube as well. But you could find out for sure by tracing the MDX query from BSM to AS 2005, using SQL Profiler.

KPI - Sales Trend

Hi,

I am trying to create a Sales Trend KPI, where the value expression is last month sales (Identify last month based on current system date) and target expression is last month previous year sales amount times 1.04.

Is there a way to accomplish this using MDX in KPI.

Thanks,

Ravi

Identifying the lastest month of data is the trickiest part. One technique to do this is to create a calculated member named CurrentMonth that uses the VBA!Date() and VBA!DatePart() to construct a member reference that can then be resolved with StrToMember. Another techinique is to again create a CurrentMonth calculation with a hard coded reference to a date and then update the definition of this calculation each time a new month of data is loaded. (See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/pullingpiecesapart.asp for some help on the VBA functions.)

Once you have this CurrentMonth member, you can use the ParallelPeriod MDX function (http://msdn2.microsoft.com/en-us/library/ms145500(SQL.90).aspx) to calculate the lat month of the previous year.

|||Thanks Matt.

Kill Old Sessions

I've got a few databases which users access using Terminal Server. I
noticed today I had several (20+) sessions which had a Last Batch date
which were days even weeks old.
I want to kill these old sessions if the Last Batch date is greater
than 5 hours.
I also noticed there are several background sessions being run by the
sa account on master db and they are several days old. I don't believe
I should kill these sessions.
Does anyone have a script they currently use to manage these old
sessions?
IzzyI forgot to list, I'm using SQL Server 2000.
Thanks,
Izzy wrote:
> I've got a few databases which users access using Terminal Server. I
> noticed today I had several (20+) sessions which had a Last Batch date
> which were days even weeks old.
> I want to kill these old sessions if the Last Batch date is greater
> than 5 hours.
> I also noticed there are several background sessions being run by the
> sa account on master db and they are several days old. I don't believe
> I should kill these sessions.
> Does anyone have a script they currently use to manage these old
> sessions?
> Izzy|||It is just a matter of writing a cursor on the sysprocesses table. You can use
http://www.dbmaint.com/download/util_proc/sp_dbm_kill_users.sql as a starter for your script.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Izzy" <israel.richner@.gmail.com> wrote in message
news:1160062917.135174.41640@.h48g2000cwc.googlegroups.com...
>I forgot to list, I'm using SQL Server 2000.
> Thanks,
>
> Izzy wrote:
>> I've got a few databases which users access using Terminal Server. I
>> noticed today I had several (20+) sessions which had a Last Batch date
>> which were days even weeks old.
>> I want to kill these old sessions if the Last Batch date is greater
>> than 5 hours.
>> I also noticed there are several background sessions being run by the
>> sa account on master db and they are several days old. I don't believe
>> I should kill these sessions.
>> Does anyone have a script they currently use to manage these old
>> sessions?
>> Izzy
>|||Execellent!
Thanks a bunch.
Izzy
Tibor Karaszi wrote:
> It is just a matter of writing a cursor on the sysprocesses table. You can use
> http://www.dbmaint.com/download/util_proc/sp_dbm_kill_users.sql as a starter for your script.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Izzy" <israel.richner@.gmail.com> wrote in message
> news:1160062917.135174.41640@.h48g2000cwc.googlegroups.com...
> >I forgot to list, I'm using SQL Server 2000.
> >
> > Thanks,
> >
> >
> > Izzy wrote:
> >> I've got a few databases which users access using Terminal Server. I
> >> noticed today I had several (20+) sessions which had a Last Batch date
> >> which were days even weeks old.
> >>
> >> I want to kill these old sessions if the Last Batch date is greater
> >> than 5 hours.
> >>
> >> I also noticed there are several background sessions being run by the
> >> sa account on master db and they are several days old. I don't believe
> >> I should kill these sessions.
> >>
> >> Does anyone have a script they currently use to manage these old
> >> sessions?
> >>
> >> Izzy
> >|||It seems to me that you are treating the symptop, not the problem.
The symptom is the old sessions.
The problem is that people do not exit Terminal Server correctly. Can you
encourage users to log out of the Terminal Server correctly? Can you
remotely log the users out (and end their database connection in the
process)?
--
Keith Kratochvil
"Izzy" <israel.richner@.gmail.com> wrote in message
news:1160061806.706762.53740@.m7g2000cwm.googlegroups.com...
> I've got a few databases which users access using Terminal Server. I
> noticed today I had several (20+) sessions which had a Last Batch date
> which were days even weeks old.
> I want to kill these old sessions if the Last Batch date is greater
> than 5 hours.
> I also noticed there are several background sessions being run by the
> sa account on master db and they are several days old. I don't believe
> I should kill these sessions.
> Does anyone have a script they currently use to manage these old
> sessions?
> Izzy
>|||That is exactly the problem, most users are set up to be logged out of
terminal server at midnight if they are not already logged out.
BUT, I have users who work in our shop on 3rd shift who use the same
account as users on first shift.
I've explained too them they need to log out correctly, but of course
users do whatever they want anyway, and just give you lip service while
your in front of them.
Question:
In the example you sent, your query does not eliminate some sessions
from being killed. For instance, I have 4 which have this listed in the
"cmd" line:
LAZY WRITER
LOG WRITER
LOCK MONITOR
CHECKPOINT SLEEP
Is there going to be any negative or unexpected behavior if these get
killed?
Is there something I should query on to eliminate system processes?
Izzy
Keith Kratochvil wrote:
> It seems to me that you are treating the symptop, not the problem.
> The symptom is the old sessions.
> The problem is that people do not exit Terminal Server correctly. Can you
> encourage users to log out of the Terminal Server correctly? Can you
> remotely log the users out (and end their database connection in the
> process)?
> --
> Keith Kratochvil
>
> "Izzy" <israel.richner@.gmail.com> wrote in message
> news:1160061806.706762.53740@.m7g2000cwm.googlegroups.com...
> > I've got a few databases which users access using Terminal Server. I
> > noticed today I had several (20+) sessions which had a Last Batch date
> > which were days even weeks old.
> >
> > I want to kill these old sessions if the Last Batch date is greater
> > than 5 hours.
> >
> > I also noticed there are several background sessions being run by the
> > sa account on master db and they are several days old. I don't believe
> > I should kill these sessions.
> >
> > Does anyone have a script they currently use to manage these old
> > sessions?
> >
> > Izzy
> >|||> In the example you sent, your query does not eliminate some sessions
> from being killed. For instance, I have 4 which have this listed in the
> "cmd" line:
> LAZY WRITER
> LOG WRITER
> LOCK MONITOR
> CHECKPOINT SLEEP
> Is there going to be any negative or unexpected behavior if these get
> killed?
These are system connections, and I'm pretty certain they can't be killed even if you try to (else
MS wouldn't done a good job protecting the system processes). You should add a filter to the SELECT
statement, like spid > 50.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Izzy" <israel.richner@.gmail.com> wrote in message
news:1160066738.477635.116660@.h48g2000cwc.googlegroups.com...
> That is exactly the problem, most users are set up to be logged out of
> terminal server at midnight if they are not already logged out.
> BUT, I have users who work in our shop on 3rd shift who use the same
> account as users on first shift.
> I've explained too them they need to log out correctly, but of course
> users do whatever they want anyway, and just give you lip service while
> your in front of them.
> Question:
> In the example you sent, your query does not eliminate some sessions
> from being killed. For instance, I have 4 which have this listed in the
> "cmd" line:
> LAZY WRITER
> LOG WRITER
> LOCK MONITOR
> CHECKPOINT SLEEP
> Is there going to be any negative or unexpected behavior if these get
> killed?
> Is there something I should query on to eliminate system processes?
> Izzy
>
> Keith Kratochvil wrote:
>> It seems to me that you are treating the symptop, not the problem.
>> The symptom is the old sessions.
>> The problem is that people do not exit Terminal Server correctly. Can you
>> encourage users to log out of the Terminal Server correctly? Can you
>> remotely log the users out (and end their database connection in the
>> process)?
>> --
>> Keith Kratochvil
>>
>> "Izzy" <israel.richner@.gmail.com> wrote in message
>> news:1160061806.706762.53740@.m7g2000cwm.googlegroups.com...
>> > I've got a few databases which users access using Terminal Server. I
>> > noticed today I had several (20+) sessions which had a Last Batch date
>> > which were days even weeks old.
>> >
>> > I want to kill these old sessions if the Last Batch date is greater
>> > than 5 hours.
>> >
>> > I also noticed there are several background sessions being run by the
>> > sa account on master db and they are several days old. I don't believe
>> > I should kill these sessions.
>> >
>> > Does anyone have a script they currently use to manage these old
>> > sessions?
>> >
>> > Izzy
>> >
>|||You've been very helpful Tibor, many thanks!
Izzy
Tibor Karaszi wrote:
> > In the example you sent, your query does not eliminate some sessions
> > from being killed. For instance, I have 4 which have this listed in the
> > "cmd" line:
> >
> > LAZY WRITER
> > LOG WRITER
> > LOCK MONITOR
> > CHECKPOINT SLEEP
> >
> > Is there going to be any negative or unexpected behavior if these get
> > killed?
> These are system connections, and I'm pretty certain they can't be killed even if you try to (else
> MS wouldn't done a good job protecting the system processes). You should add a filter to the SELECT
> statement, like spid > 50.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Izzy" <israel.richner@.gmail.com> wrote in message
> news:1160066738.477635.116660@.h48g2000cwc.googlegroups.com...
> > That is exactly the problem, most users are set up to be logged out of
> > terminal server at midnight if they are not already logged out.
> >
> > BUT, I have users who work in our shop on 3rd shift who use the same
> > account as users on first shift.
> >
> > I've explained too them they need to log out correctly, but of course
> > users do whatever they want anyway, and just give you lip service while
> > your in front of them.
> >
> > Question:
> >
> > In the example you sent, your query does not eliminate some sessions
> > from being killed. For instance, I have 4 which have this listed in the
> > "cmd" line:
> >
> > LAZY WRITER
> > LOG WRITER
> > LOCK MONITOR
> > CHECKPOINT SLEEP
> >
> > Is there going to be any negative or unexpected behavior if these get
> > killed?
> >
> > Is there something I should query on to eliminate system processes?
> >
> > Izzy
> >
> >
> > Keith Kratochvil wrote:
> >> It seems to me that you are treating the symptop, not the problem.
> >>
> >> The symptom is the old sessions.
> >> The problem is that people do not exit Terminal Server correctly. Can you
> >> encourage users to log out of the Terminal Server correctly? Can you
> >> remotely log the users out (and end their database connection in the
> >> process)?
> >>
> >> --
> >> Keith Kratochvil
> >>
> >>
> >> "Izzy" <israel.richner@.gmail.com> wrote in message
> >> news:1160061806.706762.53740@.m7g2000cwm.googlegroups.com...
> >> > I've got a few databases which users access using Terminal Server. I
> >> > noticed today I had several (20+) sessions which had a Last Batch date
> >> > which were days even weeks old.
> >> >
> >> > I want to kill these old sessions if the Last Batch date is greater
> >> > than 5 hours.
> >> >
> >> > I also noticed there are several background sessions being run by the
> >> > sa account on master db and they are several days old. I don't believe
> >> > I should kill these sessions.
> >> >
> >> > Does anyone have a script they currently use to manage these old
> >> > sessions?
> >> >
> >> > Izzy
> >> >
> >sql

Keyboard keys used to enter a null into db table field

Hey All,

Once upon a time I knew which keyboard keys were used when entering a null value into a field. For example, say there is a value in a date column and I want to change it back to null. I can't seem to remember what the key combination on the keyboard is..I think it involve the + and a combonation of two others...It's a small detail but now that's it's on the brain I'd like to know what it is again - If you know, please remind me - ThanksIts ctrl+0|||That's great - Thanks.

Key portion of datetime 'UniqueName' varies between Trees

I have 2 trees that use the attribute Calendar date.

In the first tree, the unique name is in this format:

..[2004-02-08T00:00:00]

in the second, it does not have the T:

..[2004-02-08 00:00:00]

I guess I'm wondering why this is happening and also, how to make it so that both trees have the same format. It wasn't always this way, but something might have changed with the cube definition - I just can't see anything that would cause this. It's currently causing an issue in reporting because I convert between the tree types by slapping on the key portion of the UniqueName to the tree name. Having the T or absence of a T causes issues in this transformation.

This was found to be caused by relationship changes between attributes of one of the hierarchies.

keeping track of modification date of a row

timestamp type seems to be the design to keep tracking of the modification
date (at least it's convertible to datetime) of a row; but is there a better
way to keep track it with a datetime type? I hope to avoid doing the
conversion everytime I need to look at the data (many rows at a time.)
Creating trigger is obviously possible but I hope for something simpler.
thanks!
TIMESTAMP has absolutely nothing to do with date or time! Can you show how
you are converting it to a datetime value, and demonstrate a case where
TIMESTAMP is accurately tracking the last date/time a row was updated?
Use a LastUpdatedDate column and update it with a trigger or, if you control
access to the table via stored procedures, you can use the stored procedure
to include an update to that column whenever any other value in the row is
touched.
"Zester" <zeze@.nottospam.com> wrote in message
news:eteRVyfWIHA.5716@.TK2MSFTNGP05.phx.gbl...
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a
> better way to keep track it with a datetime type? I hope to avoid doing
> the conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>
|||Hi Zester,
If you are working either with SQL Server 2000 or 2005, you have no other
chance than triggers.
But if you can wait until SQL Server 2008 arrives, things will be different.
You will have tracking features included on the server, along with two new
datatypes: DATE and TIME. (Separated at last!!!)
Hope this would be helpful. Please, rate this post. Thanks!
May the bytes be with you!!!
Pedro López-Belmonte Eraso
MCAD, MCT
"Zester" wrote:

> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a better
> way to keep track it with a datetime type? I hope to avoid doing the
> conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>
>

keeping track of modification date of a row

timestamp type seems to be the design to keep tracking of the modification
date (at least it's convertible to datetime) of a row; but is there a better
way to keep track it with a datetime type? I hope to avoid doing the
conversion everytime I need to look at the data (many rows at a time.)
Creating trigger is obviously possible but I hope for something simpler.
thanks!TIMESTAMP has absolutely nothing to do with date or time! Can you show how
you are converting it to a datetime value, and demonstrate a case where
TIMESTAMP is accurately tracking the last date/time a row was updated?
Use a LastUpdatedDate column and update it with a trigger or, if you control
access to the table via stored procedures, you can use the stored procedure
to include an update to that column whenever any other value in the row is
touched.
"Zester" <zeze@.nottospam.com> wrote in message
news:eteRVyfWIHA.5716@.TK2MSFTNGP05.phx.gbl...
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a
> better way to keep track it with a datetime type? I hope to avoid doing
> the conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>|||Hi Zester,
If you are working either with SQL Server 2000 or 2005, you have no other
chance than triggers.
But if you can wait until SQL Server 2008 arrives, things will be different.
You will have tracking features included on the server, along with two new
datatypes: DATE and TIME. (Separated at last!!!)
Hope this would be helpful. Please, rate this post. Thanks!
--
May the bytes be with you!!!
Pedro López-Belmonte Eraso
MCAD, MCT
"Zester" wrote:
> timestamp type seems to be the design to keep tracking of the modification
> date (at least it's convertible to datetime) of a row; but is there a better
> way to keep track it with a datetime type? I hope to avoid doing the
> conversion everytime I need to look at the data (many rows at a time.)
> Creating trigger is obviously possible but I hope for something simpler.
> thanks!
>
>