KPI in SQL 2005

Hello,
I search informations or a tutoriel to carry out a KPI (Key Performance
Indicator) in Analysis Services 2005.
Can you help me.
Thanks!!
NicoHi
Look in SQL 2005 Beta 2 books online. Asking this question in the betagroups
for 2005 will also help.
"pralnico" wrote:

> Hello,
> I search informations or a tutoriel to carry out a KPI (Key Performance
> Indicator) in Analysis Services 2005.
> Can you help me.
> Thanks!!
> Nicosql

KPI in SQL 2005

Hello,
I search informations or a tutoriel to carry out a KPI (Key Performance
Indicator) in Analysis Services 2005.
Can you help me.
Thanks!!
Nico
Hi
Look in SQL 2005 Beta 2 books online. Asking this question in the betagroups
for 2005 will also help.
"pralnico" wrote:

> Hello,
> I search informations or a tutoriel to carry out a KPI (Key Performance
> Indicator) in Analysis Services 2005.
> Can you help me.
> Thanks!!
> Nico

KPI in SQL 2005

Hello,
I search informations or a tutoriel to carry out a KPI (Key Performance
Indicator) in Analysis Services 2005.
Can you help me.
Thanks!!
NicoHi
Look in SQL 2005 Beta 2 books online. Asking this question in the betagroups
for 2005 will also help.
"pralnico" wrote:
> Hello,
> I search informations or a tutoriel to carry out a KPI (Key Performance
> Indicator) in Analysis Services 2005.
> Can you help me.
> Thanks!!
> Nico

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 design and implementation

Hi all,

I'm fairly new to analysis services and am encountering some difficulties when it comes to designing KPI's. I want these to be designed so that they may be displayed in a KPI list on Sharepoint 2007 server.

The basic premise for the simple data warehouse pilot was to present usage data for a number of customer sites split up but time etc. These sites also have a potential usage value attached to them. I'd like to implement a KPI that shows the current progress in the current month for there usage against the site's potential. A cylinder or thermometer type diagram i believe would work well to show the progress as a percentage value.

This may be to vague a description and i'm willing to elaborate further if necessary but could someone please help me understand how best to approach this?

Many thanks in advance,

Grant

Hello. First, if you have installed the SQL Server 2005 samples you will find an SSAS2005(Analysis Services) project, Adventure Works, with a lot of KPI:s in the cube editor. This is to get to know the MDX syntax for KPI:s.

To get the KPI:s to Sharepoint is not done directly. You will need a separate solution for this.

You can query and display KPI:s in Reporting Services 2005 but you will only see the values not the graphic/icons.

One option is to use Excel 2007 and construct a KPI spreadsheet report and publish it in Sharepoint. Another option is to use ProClarity Analytics platform and use their webparts for Sharepoint. Both these alternatives are not free of charge.

A third option is Microsofts Business Scorecard Server. It uses Sharepoint directly. This product and ProClarity will be included in the new Performance Point Server that will be on the market this summer.

HTH

Thomas Ivarsson

KPI Aggregation

I am new to using SQL 2005 Analysis Services and am spinning my wheels with something which is seemingly very basic. I have a view which already aggregates data into a comparison for the current month (i.e. Projected Sales, Actual Sales, Type of Sale). When I attempt to use the KPIs to simply use indicators to show which goals have been met, I'm only able to compare the grand totals of all sales and not break it down by specific Sale Type (which the table already does). How can I set things up so that the KPIs will keep the aggregations based on Sale Type instead of combining everything?

Thanks in advance!

Could you provide a more complete picture of your scenario? You mentioned a view (presumably relational?) and KPI's, but what kind of AS 2005 cube do you have in between - and does it have a "Sale Type" dimension? And are the KPI's created in the cube, or defined only in a client tool, like Proclarity or Business Scorecard Manager?|||Thanks for your reply...I have created a relational view which does all the aggregation I need. However, I haven't been able to create the "sale type" dimension successfully, it seems as though it's not a possible option even though this data is clearly pulled in the data source view. I am trying to create the KPIs directly in the cube and not using any client tools.|||So I think that the 1st step you need to take is to create a "Sale Type" dimension for the cube - you can then use it to slice cube data and KPI's by. What problems are preventing you from doing this?