Descending Sort on index

I have a unique index based on the following columns:
ProjectID (int)
MaterialCatalogID (int)

Material catalogues are pretty much static but projects are dynamic and
people are most likely to be working on the latest project so would
using a descending sort on the ProjectID in the index gain any
performance?You'll probably have to give some more details of what your data looks
like, what your most frequent queries and data modifications are etc.
But changing the order of the index is probably only really useful when
you have a lot of queries which return results in that particular
order. You could always try it out on a test server, of course.

Simon|||An index can be traversed both ascending and descending. This means that
changing the index order (in the index definition) is never useful for
single column indexes.

If you have compound indexes, then the order can influence performance
of some very specific queries. Generally I would not worry about the
index order.

Gert-Jan

Trevor Best wrote:
> I have a unique index based on the following columns:
> ProjectID (int)
> MaterialCatalogID (int)
> Material catalogues are pretty much static but projects are dynamic and
> people are most likely to be working on the latest project so would
> using a descending sort on the ProjectID in the index gain any
> performance?

Derived Column or script

Dear All,

I am pretty new to SQL2005 technologies.

I have a table like this:

Name

Code

Peter

10

Eric

20

I am interested in translation of the “code” to “job role”:

Name

Code

Job role

Peter

10

Director

Eric

20

Consultant

I have tried to use Derived Column and also script – but I am unable to get it to work.

Any hints/solution would greatly appreciate.

Best Regards, T

A simple example without NULL checking etc

In the 'Derived Column' task add a new column 'Job Role' and set the expression to be something like: -

[Code] == 10 ? "Director" : [Code] == 20 : "Consultant"

|||

Sagestore wrote:

A simple example without NULL checking etc

In the 'Derived Column' task add a new column 'Job Role' and set the expression to be something like: -

[Code] == 10 ? "Director" : [Code] == 20 : "Consultant"

Does not seem like the correct syntax, I think it should be:

[Code] == 10 ? "Director" : ( [Code] == 20 ? "Consultant" : "Unknown" )

|||

Do you have the translations stored in a table somewhere? If so, the LOOKUP component can be used.

-Jamie

|||

The translation is hardcoded which is fine.

However the number of options are more than 2 "consultan" and "director" - at least up to 5 different options.

So I need someting like:

Case 10 => Director

Case 20 => Consultant

Case 30 => Admin

Etc.

Thanks for input so far - but still im waiting for a soluion.

T

|||

Michael gave you the solution - you just need to extend it to cover all eventualities:

[Code] == 9999 ? "Something else!" : ([Code] == 30 ? "Admin" : ([Code] == 10 ? "Director" : ( [Code] == 20 ? "Consultant" : "Unknown" )))

-Jamie

|||

Thanks all of you - now I finally got it to work.

I am extremely happy with this kind of support.

Thanks again and take care, T

Deploy / process cube

Hi

When I deploy and process my cube in analysis services the server just runs and runs.

Several hours later it is still running - I am pretty sure that it hangs.

The cube is based on tables which max. have 2000 rows so it is not a large cube.

I have verified that it deploys to the correct server instance.

Can anyone give some advice?

Regards,

Dennis

Do you know what part of the processing it hands on (specific dimension, measure group, partition, etc.)? If the SSAS solution is based on a SQL Server database, can you run Profiler and monitor the queries that are being generated by SSAS and submitted to SQL Server?

Dave Fackler