Advice on dimension-dependant KPI Goals

We have KPI goals that vary based on the country or state.

For instance for US Stores, the goal is 2, and for stores in NY the goal is 2.8

I can create an SQL table with KPI_Name, Country or State, and KPI_Goal. I can also generate a view that will provide the proper KPI goal for each store.

What is the best practice to implement this kind of KPI ? I just don't know where to start. The examples I've seen are all hardcoded, like CASE when Store = XXX then 2.8

I'd rather avoid hardcoding KPI goals in the cube design as I'm expecting users to adjust goals regularily.

Can you create KPI goal measure group on its own. This is quite common. Then when you build KPIs, for goal you select value from this measure group.|||Good idea. Why didn't I think of that.

Advanced date/time queries

I'm a new member here and love the amount of knowledge available.

1) I have a database schema and it stores repair information for phones, with columns such as ID, Phone manufacturer, model, fault, etc.What query would I have to write to get a query which returns the phone model, its manufacturer (this I know), but also a number of the amount of times that phone model has been raised in the repair table (which is every time a customer hands in a faulty phone). Do I use a count function for the phone model or is there a more efficient way?

2) Another table stores contract information with an expiry date of the contract. How can I write a query that will pick up the contracts due to expire exactly one month for today? I have looked all over the net for some resourced on writing advanced tsql queries, does anyone know of any good websites or even books on the topic?

Thanks

GSS1 wrote:

1) I have a database schema and it stores repair information for phones, with columns such as ID, Phone manufacturer, model, fault, etc.What query would I have to write to get a query which returns the phone model, its manufacturer (this I know), but also a number of the amount of times that phone model has been raised in the repair table (which is every time a customer hands in a faulty phone). Do I use a count function for the phone model or is there a more efficient way?

You could answer this using the query:

Code Snippet

select t.phone_model, t.phone_manuf, count(*) as #faults

from t

group by t.phone_model, t.phone_manuf

GSS1 wrote:

2) Another table stores contract information with an expiry date of the contract. How can I write a query that will pick up the contracts due to expire exactly one month for today? I have looked all over the net for some resourced on writing advanced tsql queries, does anyone know of any good websites or even books on the topic?

This can be done by using query like below:

Code Snippet

select *

from Contracts as c

where c.expiry_date >= dateadd(month, 1, convert(varchar, CURRENT_TIMESTAMP, 112));

The "Inside SQL Server 2005" books are good ones for learning advanced TSQL programming.