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.

Keywords

Hi,
I have to build a table for something like 1.000.000 books.
I need to use keywords for each book (to be able to search with the keywords
in an intranet).
I wonder the best solution to achieve this:
*Add a new text field (varchar) and then use Full Text Search index
*Add two tables, one for the keywords and one to join the books' table and
the new keywords one.
Which one of these two solutions is the best with SQL Server ?
Thanks.Hello Lionel,
Disclaimer: I don't have much knowledge on full text search.
I would do the second option. This would allow you to quickly search through
the keywords assuming you have only one word in you table.
Aaron Weiker
http://aaronweiker.com/

> Hi,
> I have to build a table for something like 1.000.000 books.
> I need to use keywords for each book (to be able to search with the
> keywords
> in an intranet).
> I wonder the best solution to achieve this:
> *Add a new text field (varchar) and then use Full Text Search index
> *Add two tables, one for the keywords and one to join the books' table
> and
> the new keywords one.
> Which one of these two solutions is the best with SQL Server ?
> Thanks.
>|||Why don't you get a document management system that can do this job for
a fraction of the cost, 2-3 orders of magnitude faster and which comes
with a query language mean for text searches? SQL was never meant for
this kind of data.|||Well, do you have a name of a programmable document manager under Microsoft
and IIS (for an intranet) ?
Whatever, SQL server should be (as Oracle do) able to index text.
And if my boss wants to keep this solution, i still don't know if my
solution is the best issue: the keywords in a separate table, and then a
third one to join the books with the keywords.
Thanks for answring and taking time.

kb934459 has anyone applied this post sp2 hotfix?

I'm running sql server 2005 servicepack 2 build 3042 (32bit)
has anyone had any problems with this hotfix??
http://support.microsoft.com/kb/934459Wasn't 3052 the bad release of sp2? I'vfe got 3054 on 3 boxes and no issues with them.|||How do I patch up to 3054?

I am having such a time trying to figure out which cummulative hotfix to apply. I applied the hotfix mentioned in my original post and brought my release up to 9.0.3159 - now I read somewhere that this might not be a stable release? Can you suggest what level or release should I patch my 9.0.3042 Production box to?? and how do I find it?|||Here' are links that describe the issue:
http://support.microsoft.com/kb/933508 and
http://www.mssqltips.com/tip.asp?tip=1195

After M$oft issued the first sp2, they pulled it down for a short while then put a corrected version up on the web site. you should be able to go here and get the latest sp2:
http://support.microsoft.com/kb/913089|||Thanks for all your help Tom. Looks like I'll have to install the update to SP2 mentioned in KB933508 because I have the old one - version 9.00.3042.00

Once I do that. can you tell me how to find the latest post sp2 hotfix?|||Hotfixes are usually handled by Microsoft PSM on a per case basis. Many times they require a password to open. You will have to contact Microsoft, which means being prepared to pay for the incident, but if the problem is a M$oft issue, they will not charge you for the hotfix.|||I did not know that. I thought that if someone opened an issue that they fixed with a hotfix - they then made it available to everyone.|||Microsoft does not release hotfixes because they are often very, very specific and only fix one part of a larger problem. The hotfixes do get analyzed and carried forward into service packs (SPs), but in a very different way that has much more design and QA work than a hotfix gets.

Hotfixes are intended to be "one off" solutions to a specific problem for a specific customer. Hotfixes generally should not be shared or applied unless there is a specific, critical need.

-PatP|||At http://www.sqlsecurity.com, they maintain a (pretty current) list of SQL patches and hot fixes.

The ones that are marked "PSS Only" are not intended to be made publicly available. They are available only by calling MS support and getting (from them) a link to the hotfix (which is zipped and protected by a password which changes every 7 days).

There are, however, some hotfixes which are NOT password protected and may be downloaded without calling M$.

Regards,

hmscott

PS. I should note that calling MS for any of these hotfixes does not cost anything.|||Great info. Thank you for everything!